Home » RDBMS Server » Performance Tuning » Variance in -Row Source Generation, Explain Plan and statspack
Variance in -Row Source Generation, Explain Plan and statspack [message #269466] Sat, 22 September 2007 08:58 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

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

Re: Variance in -Row Source Generation, Explain Plan and statspack [message #269576 is a reply to message #269466] Sun, 23 September 2007 10:49 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,


Did this link solve this topic?
http://www.orafaq.com/forum/t/89812/110238/#msg_num_5

Please keep your open threads updated whenever possible.
Also I guess it is a good idea writing some explain with the results of your success/fail concerning the posted issue.
This will help the others in the future when searching OraFAQ.

Regards,


mson77
Previous Topic: SQL Sharing Problem (STB_OBJECT_MISMATCH)
Next Topic: how to find code causing redo to generate
Goto Forum:
  


Current Time: Fri Dec 09 01:49:11 CST 2016

Total time taken to generate the page: 0.07876 seconds