Home » RDBMS Server » Performance Tuning » Problem in understanding the autotrace output exectution plan and statistics (10.2.0.4, RHEL4)
Problem in understanding the autotrace output exectution plan and statistics [message #435677] Thu, 17 December 2009 05:47 Go to next message
spabolu
Messages: 7
Registered: December 2009
Junior Member
Hi All,

This the query where I am not able to understand, why the no of rows returned as shown by the execution plan and as that shown by the statistics section of autotrace ouput is different.

SELECT NVL(RESEARCH_JOB_ID,0) Research_Job,
  NVL(EXCEPTION_FILE_BLOCK,0) Research_Block,
  NVL(EXCEPTION_FILE_SEQ,0) Research_Seq,
  NVL(EXCEPTION_FILE_REL_SEQ,0) Research_Rel_Seq ,
  a.BLK_NUM Block,
  a.SEQ_NUM Sequence,
  a.REL_SEQ_NUM Relative ,
  NVL(ltrim(rtrim(FLD_6)),'0') Serial,
  NVL(FLD_4,'0') Routing,
  NVL(ltrim(rtrim(FLD_3)),'0') Account,
  NVL(ltrim(rtrim(Old_FLD3)),'0') OldAccount,
  NVL(FLD_2,0) TRAN_CD,
  ltrim(TO_CHAR(pls_util.to_num(NVL(NUMERIC_AMT,'0')),'9999999999999.99')) Amount,
  a.EXCEPTION_CD EXCEPTION ,
  NVL(a.BOFD_ID,'') Bank,
  NVL(a.ENDPOINT_ID,0) Endpoint,
  RETURN_RT_NUM ENDPOINT_RT,
  DECODE(PAID_STATUS,'Y','Paid','Return') Paid,
  NVL(d.CODE_DESC,' ') ExceptionReason,
  DECODE(Initial_PAID_STATUS,'Y','Paid','Return') InitialPaidStatus,
  DECODE(RETURN_STATUS,'U','Unable','S','Suspect','R','Completed') Status,
  NVL(action_id,-1) Action,
  NVL(NOTES,' ') Notes,
  NVL(TO_CHAR(EXCEPTION_FILE_DATE,'mmddyyyy'),'') Research_Bus_Date,
  TO_CHAR(NVL(No_POST_Indicator,1)) POST,
  NVL(AMOUNT_ADJUSTMENT,'N') AMOUNT_ADJUSTMENT,
  NVL(CHARGE_ADJUSTMENT,'N') CHARGE_ADJUSTMENT,
  NVL(CREDIT_ADJUSTMENT,'N') CREDIT_ADJUSTMENT,
  NVL(CR_IND,'D') CREDIT_DEBIT ,
  NVL(BATCH_TRACK_NUMBER,0) BATCH_TRACK_NUMBER ,
  NVL(FIMP,0) FIMP,
  NVL(TO_CHAR(OLOD_ACCOUNT_TYPE),'0') ACCOUNT_TYPE ,
  NVL(TO_CHAR(OLOD_ACCOUNT_CLASS),'0') ACCOUNT_CLASS,
  DECODE(UPPER(NVL(OLOD_POSTING_SYSTEM_IND, 'G')),'C','Consumer (CAS)','S','BP Account','2','Custom','3','GPS','G','Citi Gold','H', 'Hogan','L','Balcon','B','BBS','Citi Gold') POSTING_SYSTEM_INDICATOR ,
  DECODE(UPPER(NVL(OLOD_POSTING_SYSTEM_IND, 'G')),'C',0,'S',1,'2',2,'3',3,'G',4,'H', 5,'L',99,'B',100,4) POSTING_SYSTEM_INDICATOR1 ,
  NVL(STOP_INDICATOR,0) STOP_INDICATOR ,
  NVL(c.STOP_PAYEE_NAME,'-') STOP_PAYEE_NAME,
  NVL(c.STOP_AMOUNT,0) STOP_AMOUNT,
  NVL(c.STOP_CHECK_SERIAL_NUMBER,0) STOP_CHECK_SERIAL_NUMBER,
  NVL(REPOST_ACCOUNT_NUMBER,'0') REPOST_ACCOUNT_NUMBER,
  NVL(REPOST_ACCOUNT_CLASS,0) REPOST_ACCOUNT_CLASS ,
  NVL(overDraft_Indicator,0) ODIND,
  NVL(File_Origin_ID,0) FileOrgInd ,
  NVL(trim(DECODE(NVL(trim(upper(PAID_RETURN_DECISIONER)), OLOD_APPROVER_OPERATOR),'DEFAULT',OLOD_APPROVER_OPERATOR,upper(PAID_RETURN_DECISIONER))), upper(PAID_RETURN_DECISIONER)) operator,
  NVL(OLOD_CURRENT_LEDGER_BALANCE,0) LDGR_BLNC ,
  NVL(OLOD_UNCOLLECTED_USED_AMOUNT,0) UNCOLLECTEDUSEDAMT,
  NVL(OLOD_DEBIT_CARD_HOLD_AMOUNT,0) DBCRDHOLDAMT ,
  NVL(OLOD_HARD_HOLD_AMOUNT,0) HRDHOLDAMT,
  NVL(Rej_cd,1) RejCD,
  NVL(POSTING_TRAN_CODE,'0') POSTINGTRANCODE,
  STOP_REQUEST_ID STOPDATE,
  NVL(DECODE(OLOD_RETURN_FLAG, ' ', 'Y',OLOD_RETURN_FLAG), '-') OLODRETURNFLAG,
  NVL(EXCEPTION_CD_LIST,' ') EXCEPTIONCDLIST,
  NVL(STOP_SEQ_NUM,0) STOPSEQNUM,
  to_number(NVL(ltrim(rtrim(FLD_3)),'0')) Account1,
  NVL(FLD_5,'0') Pos44,
  NVL(RAW_SERIAL,' ') RAW_SERIAL,
  NVL(RAW_ROUTING,' ') RAW_ROUTING,
  NVL(RAW_ONUS,' ') RAW_ONUS ,
  DECODE(NVL(SUB_EXCEPTION_DESCRIPTION,' '),' ',' ',NO_POST_REASON_CODE
  || ' - '
  || SUB_EXCEPTION_DESCRIPTION) NoPostReasonDesc ,
  DECODE(NVL(OLOD_INSERTED_ITEM,'N'),'N',NVL(ERETURN_INSERTED_ITEM,'N'),'Y') AddWindowItem,
  NVL(image_flag,'M') ImgFlg
FROM SUB_EXCEPTIONCODE_MASTER s,
  ITEM_11172009_1 a,
  BOFD_MASTER b,
  STOP_PAYMENT c ,
  APPLICATION_MASTER d ,
  endpoint_master e
WHERE (s.SUB_EXCEPTION_CD(+) = a.NO_POST_REASON_CODE
AND a.EXCEPTION_CD           = s.EXCEPTION_CD(+))
AND a.EXCEPTION_CD           =d.CODE(+)
AND a.BOFD_ID                =b.BOFD_ID(+)
AND a.ENDPOINT_ID            = e.ENDPOINT_ID(+)
AND a.BLK_NUM                =13
AND a.BCH_NUM                = 120051
AND FIMP                     = 41
AND a.BLK_NUM                =c.BLK_NUM(+)
AND a.BCH_NUM                =c.BCH_NUM(+)
AND a.SEQ_NUM                =c.SEQ_NUM(+)
AND a.BUS_DATE               = c.BUS_DATE(+)
AND a.REL_SEQ_NUM            = c.REL_SEQ_NUM(+)
AND (c.stop_seq_num         IS NULL
OR c.stop_seq_num            =
  (SELECT MIN(stop_seq_num)
  FROM stop_payment
  WHERE stop_request_id IN
    (SELECT MAX(STOP_REQUEST_ID)
    FROM STOP_PAYMENT d
    WHERE a.BLK_NUM   =d.BLK_NUM(+)
    AND a.BCH_NUM     =d.BCH_NUM(+)
    AND a.SEQ_NUM     =d.SEQ_NUM(+)
    AND a.BUS_DATE    = d.BUS_DATE(+)
    AND a.REL_SEQ_NUM = d.REL_SEQ_NUM(+)
    )
  ))
ORDER BY POSTING_SYSTEM_INDICATOR1 ASC,
  Account1;


This is the output of my query with autotrace on
SQL> SET AUTOTRACE ON EXP STAT

Execution Plan
----------------------------------------------------------
Plan hash value: 1435219534

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     2 |   722 |   102   (3)| 00:00:02 |
|   1 |  SORT ORDER BY                     |                          |     2 |   722 |   102   (3)| 00:00:02 |
|*  2 |   FILTER                           |                          |       |       |            |       |
|*  3 |    HASH JOIN OUTER                 |                          |     2 |   722 |   101   (2)| 00:00:02 |
|*  4 |     HASH JOIN OUTER                |                          |     2 |   640 |    97   (2)| 00:00:02 |
|*  5 |      HASH JOIN OUTER               |                          |     2 |   586 |    94   (2)| 00:00:02 |
|   6 |       NESTED LOOPS OUTER           |                          |     2 |   580 |    90   (0)| 00:00:02 |
|*  7 |        HASH JOIN RIGHT OUTER       |                          |     2 |   574 |    90   (0)| 00:00:02 |
|   8 |         TABLE ACCESS BY INDEX ROWID| STOP_PAYMENT             |    47 |  3102 |    78   (0)| 00:00:01 |
|*  9 |          INDEX SKIP SCAN           | STOP_PAYMENTINDX         |    47 |       |    44   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| ITEM_11172009_1          |    65 | 14365 |    12   (0)| 00:00:01 |
|* 11 |          INDEX SKIP SCAN           | ITEM_INDEX3              |    66 |       |     8   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN           | PK_BOFD_MASTER           |     1 |     3 |     0   (0)| 00:00:01 |
|  13 |       TABLE ACCESS FULL            | ENDPOINT_MASTER          |     6 |    18 |     3   (0)| 00:00:01 |
|  14 |      TABLE ACCESS FULL             | APPLICATION_MASTER       |    17 |   459 |     3   (0)| 00:00:01 |
|  15 |     TABLE ACCESS FULL              | SUB_EXCEPTIONCODE_MASTER |    39 |  1599 |     3   (0)| 00:00:01 |
|  16 |    SORT AGGREGATE                  |                          |     1 |    16 |            |       |
|* 17 |     FILTER                         |                          |       |       |            |       |
|  18 |      TABLE ACCESS FULL             | STOP_PAYMENT             |   100K|  1572K|   222   (1)| 00:00:03 |
|  19 |      SORT AGGREGATE                |                          |     1 |    35 |            |       |
|* 20 |       TABLE ACCESS BY INDEX ROWID  | STOP_PAYMENT             |     1 |    35 |     4   (0)| 00:00:01 |
|* 21 |        INDEX RANGE SCAN            | STOP_PAYMENTINDX         |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C"."STOP_SEQ_NUM" IS NULL OR "C"."STOP_SEQ_NUM"= (SELECT MIN("STOP_SEQ_NUM") FROM
              "STOP_PAYMENT" "STOP_PAYMENT" WHERE "STOP_REQUEST_ID"= (SELECT MAX("STOP_REQUEST_ID") FROM
              "STOP_PAYMENT" "D" WHERE "D"."SEQ_NUM"(+)=:B1 AND "D"."BCH_NUM"(+)=:B2 AND "D"."BLK_NUM"(+)=:B3 AND
              "D"."BUS_DATE"(+)=:B4 AND "D"."REL_SEQ_NUM"(+)=:B5)))
   3 - access("A"."NO_POST_REASON_CODE"=TO_NUMBER("S"."SUB_EXCEPTION_CD"(+)) AND
              "A"."EXCEPTION_CD"=TO_NUMBER("S"."EXCEPTION_CD"(+)))
   4 - access("A"."EXCEPTION_CD"="D"."CODE"(+))
   5 - access("A"."ENDPOINT_ID"="E"."ENDPOINT_ID"(+))
   7 - access("A"."BLK_NUM"="C"."BLK_NUM"(+) AND "A"."BCH_NUM"="C"."BCH_NUM"(+) AND
              "A"."SEQ_NUM"="C"."SEQ_NUM"(+) AND "A"."BUS_DATE"="C"."BUS_DATE"(+) AND
              "A"."REL_SEQ_NUM"="C"."REL_SEQ_NUM"(+))
   9 - access("C"."BLK_NUM"(+)=13 AND "C"."BCH_NUM"(+)=120051)
       filter("C"."BCH_NUM"(+)=120051 AND "C"."BLK_NUM"(+)=13)
  10 - filter("FIMP"=41)
  11 - access("A"."BLK_NUM"=13 AND "A"."BCH_NUM"=120051)
       filter("A"."BCH_NUM"=120051 AND "A"."BLK_NUM"=13)
  12 - access("A"."BOFD_ID"="B"."BOFD_ID"(+))
  17 - filter("STOP_REQUEST_ID"= (SELECT MAX("STOP_REQUEST_ID") FROM "STOP_PAYMENT" "D" WHERE
              "D"."SEQ_NUM"(+)=:B1 AND "D"."BCH_NUM"(+)=:B2 AND "D"."BLK_NUM"(+)=:B3 AND "D"."BUS_DATE"(+)=:B4 AND
              "D"."REL_SEQ_NUM"(+)=:B5))
  20 - filter("D"."REL_SEQ_NUM"(+)=:B1)
  21 - access("D"."BUS_DATE"(+)=:B1 AND "D"."BLK_NUM"(+)=:B2 AND "D"."BCH_NUM"(+)=:B3 AND
              "D"."SEQ_NUM"(+)=:B4)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      79352  consistent gets
          0  physical reads
          0  redo size
       7589  bytes sent via SQL*Net to client
       2468  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         75  rows processed


Strangely why does the first row in the execution plan
Quote:
| 0 | SELECT STATEMENT | | 2 | 722 | 102 (3)| 00:00:02 |


and the last line in the statistics
Quote:
75 rows processed


are different.

Shouldn't they be equal.

The actual result of the query also returned 75 rows to the client. I had collected statistics of all the involved tables using DBMS_STATS.GATHER_TABLE_STATS procedure.

Please let me know why this is happening.
Re: Problem in understanding the autotrace output exectution plan and statistics [message #435688 is a reply to message #435677] Thu, 17 December 2009 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be because the figures on the explain plan are estimates generated by the CBO as it analyzes the query, and the figures returned by auto-trace are the actual values.

It's possible that you need to generate more recent / more detailed staistics for the tables in the query.
icon7.gif  Re: Problem in understanding the autotrace output exectution plan and statistics [message #435964 is a reply to message #435688] Sun, 20 December 2009 00:43 Go to previous messageGo to next message
spabolu
Messages: 7
Registered: December 2009
Junior Member
Hi JRowbottom,

Thank you for your response.

Quote:
That would be because the figures on the explain plan are estimates generated by the CBO as it analyzes the query, and the figures returned by auto-trace are the actual values.


But AUTOTRACE utility gives the plan which it has executed.

Whereas EXPLAIN PLAN will generate a plan that would be suitable for execution at that particular point of time depending on the Load, concurrency etc. This plan may not be the plan used while execution at some other point of time.

Please let me know if I am right.

Quote:

It's possible that you need to generate more recent / more detailed staistics for the tables in the query.


I had generated the statistics for each any every table involved in the query using

Quote:
DBMS_STATS.GATHER_TABLE_STATS(USER, <table_name>, cascade=> TRUE);


Immediately after the collection, I had executed the query with AUTOTRACE ON. The output which I had presented at the starting of the thread is in the same.

How do I generate more detailed stastistics ?


Re: Problem in understanding the autotrace output exectution plan and statistics [message #435984 is a reply to message #435964] Sun, 20 December 2009 20:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Everyting in the PLAN section of AUTOTRACE is an ESTIMATE. Everything in the STATISTICS section is an ACTUAL.

To get actuals on the plan, use SQL Trace and TKProf.

Ross Leishman
Previous Topic: How to configure Oracle on Two Servers in Hot Redundant mode
Next Topic: Memory Notification: Library Cache Object loaded into SGA
Goto Forum:
  


Current Time: Thu Sep 29 19:15:58 CDT 2016

Total time taken to generate the page: 0.05817 seconds