Hi,
I am using Oracle 9iR2 (RAC) on Linux
Regarding sql explain Plan, Row Surce Generation and Statspack report i have some queries as mentioned below
Please refer following query
SELECT NVL(SUM(XX) ,0)
INTO c
FROM (SELECT strclientcd,
strbasedocnbr ,
nclubpmt ,
DECODE(fvh.nclubpmt,0,COUNT(1),1) XX
FROM fin_vou_hdr fvh,
fin_vou_cshbnk fvc
WHERE fvh.strvoutype = fvc.strvoutype
AND fvh.strvouseries = fvc.strvouseries
AND fvh.nfiscalyear = fvc.nfiscalyear
AND fvh.lvounbr = fvc.lvounbr
AND fvh.strbrandcd = fvc.strbrandcd
AND strcashbankcd = b
AND fvh.strvoutype = 'PV'
AND fvc.npmtreftype = 1
AND fvc.strpmtrefnbr IS NULL
AND fvh.strclientcd != '9999999999'
AND nclubpmt IS NOT NULL
AND fvh.dtvou = a
GROUP BY strclientcd,
strbasedocnbr,
nclubpmt);
since last week the query is executing very slow
I traced the session executing the query and got following plan
Rows Row Source Operation
------- ---------------------------------------------------
8 SORT AGGREGATE
967 VIEW
967 SORT GROUP BY
1360 NESTED LOOPS
1700 TABLE ACCESS BY INDEX ROWID FIN_VOU_CSHBNK
1161819 INDEX RANGE SCAN FVC_CBCD_INDX (object id 157210)
1360 TABLE ACCESS BY INDEX ROWID FIN_VOU_HDR
1700 INDEX UNIQUE SCAN XPKFIN_VOU_HDR (object id 156515)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
8 SORT (AGGREGATE)
967 VIEW
967 SORT (GROUP BY)
1360 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'FIN_VOU_CSHBNK'
1700 NESTED LOOPS
1161819 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'FIN_VOU_HDR'
1360 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'INDX_PV_SEARCH' (NON-UNIQUE)
1700 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'XPKFIN_VOU_CSHBNK' (UNIQUE)
Here Row Source Generation and explain are showing different indexes usage
After that I rebuild the index and again traced the session executing the sme query
however now i am missing Explain plan from the trace and got Row Source Generation only
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 VIEW
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID FIN_VOU_CSHBNK
25189 NESTED LOOPS
12594 TABLE ACCESS BY INDEX ROWID FIN_VOU_HDR
27160 INDEX RANGE SCAN INDX_PV_SEARCH (object id 156947)
12594 INDEX RANGE SCAN XPKFIN_VOU_CSHBNK (object id 156513)
Moreover while running the query from anonymous block i am unable to see the 'Executions' count increasing in v$sql
I also tried finding in v$sql, if any new entry has been recorded for the statement becuase of little unnoticed change in query e.g. spcae etc. but i could not found it.
My queries are
1) In first case why Row Source Generation and Explain Plan are showing diffrent access paths?
2) After Index Rebuild why Explain Plan is not Visible in 10046 Trace file?
3)While executing the query from anonymous bloch why execution count is not increasing?
I done Index Rebuild after 18:00 hrs
till that time i got following info from statspack
STATSPACK SQL report for Hash Value: 3267352450
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 166,833 83,416.5 .38
Disk Reads: 33,166 16,583.0 .98
Rows processed: 2 1.0
CPU Time(s/ms): 5 2,585.0
Elapsed Time(s/ms): 130 65,194.3
Sorts: 2 1.0
Parse Calls: 0 .0
Invalidations: 0
Version count: 2
Sharable Mem(K): 80
Executions: 2
First First Plan
Snap Id Snap Time Hash Value Cost
-------- --------------- ------------ ----------
2216 31 Jul 06 12:00 3941780601 13764
2940 08 Aug 06 02:30 3941780601 13760
18981 23 Jan 07 11:30 3941780601 13815
22909 05 Mar 07 00:30 2942513927 28728
23184 07 Mar 07 20:00 3941780601 14843
23842 14 Mar 07 11:30 3941780601 14814
27702 23 Apr 07 01:00 3941780601 47137
31846 03 Jun 07 22:00 3941780601 46335
35992 16 Jul 07 16:00 3941780601 49398
41359 10 Sep 07 12:30 3941780601 50516
41361 10 Sep 07 13:30 2942513927 60302
41981 16 Sep 07 22:30 2206344934 7
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 2206344934 ----| | | 28489 |
|SORT AGGREGATE | | 1 | 15 | |
| VIEW | | 1 | 15 | 28489 |
| SORT GROUP BY | | 1 | 85 | 28489 |
| NESTED LOOPS | | 1 | 85 | 28484 |
| TABLE ACCESS BY INDEX ROWID |FIN_VOU_CSHBNK | 760 | 26K| 26660 |
| INDEX RANGE SCAN |FVC_CBCD_INDX | 397K| | 891 |
| TABLE ACCESS BY INDEX ROWID |FIN_VOU_HDR | 1 | 49 | 3 |
| INDEX UNIQUE SCAN |XPKFIN_VOU_HDR | 1 | | 2 |
--------------------------------------------------------------------------------
However after Index Rebuild I am getting following Access path
Enter value for hash_value: 3267352450
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 59,455 59,455.0 3.85
Disk Reads: 956 956.0 .27
Rows processed: 1 1.0
CPU Time(s/ms): 0 330.0
Elapsed Time(s/ms): 4 3,716.2
Sorts: 1 1.0
Parse Calls: 1 1.0
Invalidations: 0
Version count: 2
Sharable Mem(K): 90
Executions: 1
First First Plan
Snap Id Snap Time Hash Value Cost
-------- --------------- ------------ ----------
376 12 Jul 06 07:45 3941780601 13764
821 17 Jul 06 00:15 3941780601 13760
20254 05 Feb 07 15:45 3941780601 13815
22962 05 Mar 07 12:45 2942513927 28728
23114 07 Mar 07 00:45 3941780601 46691
23201 08 Mar 07 00:15 3941780601 14843
27822 24 Apr 07 00:45 3941780601 47228
31955 05 Jun 07 03:15 3941780601 46425
32576 11 Jun 07 11:45 3941780601 14814
35919 15 Jul 07 22:15 3941780601 49398
41312 10 Sep 07 00:15 3941780601 50516
42049 21 Sep 07 11:02 2206344934 7
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 3941780601 ----| | | 13764 |
|SORT AGGREGATE | | 1 | 15 | |
| VIEW | | 1 | 15 | 13764 |
| SORT GROUP BY | | 1 | 85 | 13764 |
| TABLE ACCESS BY INDEX ROWID |FIN_VOU_CSHBNK | 1 | 36 | 4 |
| NESTED LOOPS | | 1 | 85 | 13759 |
| TABLE ACCESS BY INDEX ROWID|FIN_VOU_HDR | 2K| 110K| 6402 |
| INDEX RANGE SCAN |INDX_PV_SEARCH | 8K| | 44 |
| INDEX RANGE SCAN |XPKFIN_VOU_CSHBNK | 1 | | 3 |
--------------------------------------------------------------------------------
So what is the meaning of cost in statspack PHV table then which always shows 7 cost for alst statement with whatever index path it has?
Also the buffer_gets and disk_reads in v$sql do not at all match with that in statspack as mentioned below
select address,hash_value,disk_reads,buffer_gets,first_load_time,
plan_hash_value,is_obsolete,invalidations
from v$sql
where hash_value= 3267352450
ADDRESS HASH_VALUE DISK_READS BUFFER_GETS IS_OBSOLETE INVALIDATIONS
A65737F0 3267352450 974 117825 N 1
A65737F0 3267352450 1622 59462 N 1
Kindly suggest which information is reliable and queries as osed above?
Thanks and Regards,
Pratap
[Updated on: Sat, 22 September 2007 09:53] by Moderator
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
