Re: Query Execution Time

From: raja <dextersunil_at_gmail.com>
Date: Tue, 18 Nov 2008 07:09:28 -0800 (PST)
Message-ID: <6059c1d1-4830-4ebd-9592-b900bf9c2d72@p35g2000prm.googlegroups.com>

Hi,

I am trying to learn the explain plan output.

I have some more doubts regarding the following explain plan. ( i am adding few more doubts including with the above post ) 4. Here, in the below plan, the % CPU is -8. What does that mean (When i executed again, it went off...)?
5. In the first select statement ( id=0), i got cost = 720k and now i got as 23, what is this mean ?
6. Any other observations that can be got from the below plan ( Predicate Information )

Please explain.

PLAN_TABLE_OUTPUT
Plan hash value:
735063380

| Id | Operation |

Name                           | Rows  | Bytes | Cost (%CPU)| Time

| 0 | SELECT STATEMENT
| | 1 | 476 | 23 (-8)|
00:00:01 |
| 1 | NESTED LOOPS
| | 1 | 98 | 2 (0)|
00:00:01 |
| 2 | MAT_VIEW ACCESS BY INDEX ROWID |

CODES                          |     1 |    21 |     1   (0)| 00:00:01


|

|*  3 |    INDEX RANGE SCAN                      |

IDX_TB_CODES_SRTDESC           |     1 |       |     1   (0)| 00:00:01


|

|*  4 |   MAT_VIEW ACCESS BY INDEX ROWID         |

CODE_VALUES                    |     1 |    77 |     1   (0)| 00:00:01


|

|*  5 |    INDEX RANGE SCAN                      |

IDX_TB_CODE_VALUES_SRTTRIM     |     2 |       |     1   (0)| 00:00:01


|

|*  6 |  MAT_VIEW ACCESS BY INDEX ROWID          |

CODES                          |     1 |    21 |     1   (0)| 00:00:01

|
| 7 | NESTED LOOPS
| | 1 | 121 | 3 (0)|
00:00:01 |
| 8 | NESTED LOOPS
| | 1 | 100 | 2 (0)|
00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID |

INTERFACES                     |     1 |    20 |     1   (0)| 00:00:01


|

|* 10 |      INDEX RANGE SCAN                    |

IDX_TB_INTERFACES_SRTDESC      |     1 |       |     1   (0)| 00:00:01


|

|* 11 |     MAT_VIEW ACCESS BY INDEX ROWID       |

CODE_VALUES                    |     1 |    80 |     1   (0)| 00:00:01


|

|* 12 |      INDEX RANGE SCAN                    |

IDX_TB_CODE_VALUES_SRTTRIM     |     2 |       |     1   (0)| 00:00:01


|

|* 13 |    INDEX RANGE SCAN                      |

IDX_TB_CODES_SRTDESC           |     1 |       |     1   (0)| 00:00:01

|
|* 14 | COUNT STOPKEY
| | | |
| |
|* 15 | FILTER
| | | |
| |
|* 16 | FILTER
| | | |
| |
| 17 | NESTED LOOPS
| | 1 | 476 | 22 (0)|
00:00:01 |
| 18 | NESTED LOOPS
| | 1 | 304 | 21 (0)|
00:00:01 |
| 19 | NESTED LOOPS
| | 1 | 296 | 20 (0)|
00:00:01 |
| 20 | NESTED LOOPS
| | 1 | 279 | 19 (0)|
00:00:01 |
| 21 | NESTED LOOPS
| | 2 | 404 | 18 (0)|
00:00:01 |
| 22 | NESTED LOOPS OUTER
| | 1 | 42 | 3 (0)|
00:00:01 |
| 23 | NESTED LOOPS OUTER
| | 1 | 29 | 2 (0)|
00:00:01 |
|* 24 | MAT_VIEW ACCESS BY INDEX ROWID|
PWM_USER_ACCT_GRP_MVIEW | 1 | 20 | 1 (0)| 00:00:01
|
|* 25 | INDEX RANGE SCAN |
IDX_MV_ACCT_GRP_UIN_GID_EID_PC | 15 | | 1 (0)| 00:00:01
|
|* 26 | INDEX RANGE SCAN |
IDX_TB_PAAOD_EID_AOD | 1 | 9 | 1 (0)| 00:00:01
|
| 27 | MAT_VIEW ACCESS BY INDEX ROWID |
PWM_PARTNERSHIP_XREF | 1 | 13 | 1 (0)| 00:00:01
|
|* 28 | INDEX UNIQUE SCAN |

SYS_C0020902                   |     1 |       |     1   (0)| 00:00:01


|

|* 29 |          MAT_VIEW ACCESS BY INDEX ROWID  |

TRADE                          |     2 |   320 |    15   (0)| 00:00:01


|

|* 30 |           INDEX RANGE SCAN               |

IDX_TRD_EN_PDT                 |   468 |       |     1   (0)| 00:00:01


|

|* 31 |         MAT_VIEW ACCESS BY INDEX ROWID   |

TRADE_DETAIL                   |     1 |    77 |     1   (0)| 00:00:01


|

|* 32 |          INDEX UNIQUE SCAN               |

PK_TRADE_DETAIL                |     1 |       |     1   (0)| 00:00:01


|

|* 33 |        INDEX RANGE SCAN                  |

IDX_TB_PAAOD_EID_AOD           |     1 |    17 |     1   (0)| 00:00:01


|   |  34 |       MAT_VIEW ACCESS BY INDEX ROWID     |

PWM_SECURITY_CLASSIFICATION    |     1 |     8 |     1   (0)| 00:00:01


|

|* 35 |        INDEX UNIQUE SCAN                 |

SYS_C0020961                   |     1 |       |     1   (0)| 00:00:01


|

|* 36 |      MAT_VIEW ACCESS BY INDEX ROWID      |

SECURITY_MASTER                |     1 |   172 |     1   (0)| 00:00:01


|

|* 37 |       INDEX UNIQUE SCAN                  |

PK_SECURITY_MASTER             |     1 |       |     1   (0)| 00:00:01

|
| 38 | SORT UNIQUE NOSORT
| | 1 | 60 | 4 (25)|
00:00:01 |
| 39 | NESTED LOOPS
| | 1 | 60 | 3 (0)|
00:00:01 |
| 40 | NESTED LOOPS
| | 1 | 35 | 2 (0)|
00:00:01 |
|* 41 | INDEX RANGE SCAN |
IDX_MV_ACCT_GRP_UIN_GID_EID_PC | 1 | 18 | 1 (0)| 00:00:01
|
|* 42 | INDEX RANGE SCAN |
IDX_TB_PAAOD_EID_AOD | 1 | 17 | 1 (0)| 00:00:01
|
|* 43 | INDEX RANGE SCAN |

SYS_C0025557                   |     1 |    25 |     1   (0)| 00:00:01


|

|* 44 |       MAT_VIEW ACCESS BY INDEX ROWID     |

INTERFACES                     |     1 |    20 |     1   (0)| 00:00:01


|

|* 45 |        INDEX UNIQUE SCAN                 |

PK_INTERFACES                  |     1 |       |     1   (0)| 00:00:01

Predicate Information (identified by operation id):

3 - access("CD"."SHORT_DESC"='Portal
Class')
4 - filter
("CD"."INSTANCE"="CDV"."CODE_INST")
5 - access(TRIM("SHORT_DESC")
=:B1)

   6 - filter
("CDV"."CODE_INST"="CD"."INSTANCE")
  10 - access("INTFC"."SHORT_DESC"='EAGLE PACE')
  11 - filter
("CDV"."INTFC_INST"="INTFC"."INSTANCE")
  12 - access(TRIM("SHORT_DESC")=TRIM
(:B1))
  13 - access
("CD"."SHORT_DESC"='FINAN_TRAN_TYPE')
  14 - filter
(ROWNUM<=1)
  15 - filter(CASE WHEN ("Y"."SECURITY_ALIAS" IS NULL AND "X"."PARTNERSHIP_FLAG"='N') THEN 'SINGLE ACCOUNT' WHEN               ("Y"."SECURITY_ALIAS" IS NULL AND "X"."PARTNERSHIP_FLAG"='Y') THEN 'PARTNERSHIP' WHEN "Y"."SECURITY_ALIAS"=
              (SELECT /*+ */ DISTINCT "A"."SECURITY_ALIAS" FROM "REPADMIN"."PWM_POSITION"
              "A","REPADMIN"."PWM_ACCOUNT_AS_OF_DATE" "AOD1","PWMWI"."PWM_USER_ACCT_GRP_MVIEW" "T" WHERE "T"."GROUP_ID"=:B1
              "T"."USER_INST"=:B2 AND
"T"."ENTITY_ID"="AOD1"."ENTITY_ID" AND "A"."TRADED_SETTLED_IND"='S' AND
              "A"."SECURITY_ALIAS"=:B3 AND "A"."ENTITY_ID"="AOD1"."ENTITY_ID" AND
"A"."EFFECTIVE_DATE"="AOD1"."AS_OF_DATE") N               'PARTNERSHIP EXCLUDE' ELSE 'PARTNERSHIP INCLUDE' END <>'PARTNERSHIP EXCLUDE' AND (("TD"."RECORD_TYPE"='11' OR

              "TD"."RECORD_TYPE"='21') OR EXISTS (SELECT /*+ */ 0 FROM "REPADMIN"."INTERFACES" "INT" WHERE T"."INSTANCE"=:B4               AND ("INT"."SHORT_DESC"='IAS' OR "INT"."SHORT_DESC"='PERSH' OR "INT"."SHORT_DESC"='SEI FINTRAN'))))
  16 - filter(TO_DATE('01-Mar-04')<=TO_DATE('02- Mar-04'))
  24 - filter(CASE "X"."GROUP_ID" WHEN (-100) THEN NULL ELSE "X"."GROUP_ID" END IS NULL OR CASE "X"."GROUP_ID"               WHEN (-100) THEN NULL ELSE "X"."GROUP_ID" END = (-1))
  25 - access
("X"."USER_INST"=3415)
26 - access
("X"."ENTITY_ID"="AOD"."ENTITY_ID"(+))
  28 - access
("X"."ENTITY_ID"="Y"."ENTITY_ID"(+))
  29 - filter(CASE WHEN "TD"."RECORD_TYPE"='11' THEN NULL WHEN "TD"."RECORD_TYPE"='21' THEN NULL ELSE
              "TD"."POST_DATE" END >=TO_DATE('01-Mar-04') AND CASE WHEN "TD"."RECORD_TYPE"='11' THEN NULL WHEN               "TD"."RECORD_TYPE"='21' THEN NULL ELSE "TD"."POST_DATE" END <=TO_DATE('02-Mar-04') OR

              "TD"."TRADE_DATE">=TO_DATE('01-Mar-04') AND "TD"."TRADE_DATE"<=TO_DATE('02-Mar-04'))   30 - access
("TD"."ENTITY_ID"="X"."ENTITY_ID")
  31 - filter(DECODE(CASE WHEN "TD"."RECORD_TYPE"='11' THEN '20' WHEN "TD"."RECORD_TYPE"='21' THEN '10' ELSE
              TRIM("TD"."TRANS_CODE") END ,'10',DECODE(TRIM(CASE WHEN "TD"."RECORD_TYPE"='11' THEN NULL WHEN
              "TD"."RECORD_TYPE"='21' THEN NULL ELSE "SECID_TYPE" END ),'1',0,1),1)<>0 AND DECODE(CASE WHEN

              "TD"."RECORD_TYPE"='11' THEN '20' WHEN "TD"."RECORD_TYPE"='21' THEN '10' ELSE TRIM("TD"."TRANS_CODE") END
              ,'20',DECODE(TRIM(CASE WHEN "TD"."RECORD_TYPE"='11' THEN NULL WHEN "TD"."RECORD_TYPE"='21' THEN NULL ELSE               "SECID_TYPE" END ),'1',0,1),1) <>0)
  32 - access
("TRADE_ID"="TD"."TRADE_ID")
  33 - access
("TD"."ENTITY_ID"="ENTITY_ID")
       filter(CASE WHEN "TD"."UPDATE_SOURCE"='SEITRD' THEN CASE WHEN "CANCEL_FLAG"='0' THEN CASE WHEN
              ("TD"."PRICE"<>(-999999) AND "TD"."PRINCIPAL"<>(-999999) AND "TD"."TRADE_AMOUNT"<>(-999999) AND

              "TD"."TRADE_DATE"<="AS_OF_DATE" AND "TD"."SETTLEMENT_DATE">"AS_OF_DATE") THEN "TD"."TRADE_DATE" ELSE NULL END
              ELSE NULL END ELSE DECODE(INTERNAL_FUNCTION ("TD"."TRADE_DATE"),NULL,"TD"."POST_DATE","TD"."TRADE_DATE") END               >=TO_DATE('01-Mar-04') AND CASE WHEN "TD"."UPDATE_SOURCE"='SEITRD' THEN CASE WHEN "CANCEL_FLAG"='0' THEN CASE
              WHEN ("TD"."PRICE"<>(-999999) AND "TD"."PRINCIPAL"<> (-999999) AND "TD"."TRADE_AMOUNT"<>(-999999) AND

              "TD"."TRADE_DATE"<="AS_OF_DATE" AND "TD"."SETTLEMENT_DATE">"AS_OF_DATE") THEN "TD"."TRADE_DATE" ELSE NULL END
              ELSE NULL END ELSE DECODE(INTERNAL_FUNCTION ("TD"."TRADE_DATE"),NULL,"TD"."POST_DATE","TD"."TRADE_DATE") END               <=TO_DATE('02-
Mar-04'))
  35 - access
("TD"."SECURITY_ALIAS"="SECURITY_ALIAS")   36 - filter
("USER_GROUP_SECTOR12"='A')
  37 - access
("TD"."SECURITY_ALIAS"="SECURITY_ALIAS")   41 - access("T"."USER_INST"=:B1 AND
"T"."GROUP_ID"=:B2)
  42 - access
("T"."ENTITY_ID"="AOD1"."ENTITY_ID")

  43 - access("A"."EFFECTIVE_DATE"="AOD1"."AS_OF_DATE" AND
"A"."ENTITY_ID"="AOD1"."ENTITY_ID" AND
              "A"."SECURITY_ALIAS"=:B1 AND

"A"."TRADED_SETTLED_IND"='S')

  44 - filter("INT"."SHORT_DESC"='IAS' OR "INT"."SHORT_DESC"='PERSH'
OR "INT"."SHORT_DESC"='SEI FINTRAN')
  45 - access("INT"."INSTANCE"=:B1)

Thanks in Advance.
With Regards,
Raja. Received on Tue Nov 18 2008 - 09:09:28 CST

This message: [ Message body ]
Next message: gazzag: "Re: do desc on tables, dump output to text file"
Previous message: Joey.Dantoni_at_gmail.com: "Re: do desc on tables, dump output to text file"
In reply to: raja: "Re: Query Execution Time"
Next in thread: DA Morgan: "Re: Query Execution Time"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message