One single SQL helps you to analyze the performance of a SQL

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2016-03-12 22:16:32

Share to 

This is the script I am using to analyze SQL performance. It major contains one SQL only. It can show below information if available. And you can shoose to show parts of them.   * Monitored/Captured Bind Variables   * SQL Text   * Execution Plan   * PeeKed Binds of the execution plan   * Plan Outline Data   * Plan Predicate   * Plan Lines Loading   * Wait events   * Performance Statistics Data Example: SQL代码 HelloDBA.COM> @showplan 8z91j441gu9n1   Usage: @showplan <SQL_ID> [Plan Hash Value] [Details: [+](B)inds|SQL (T)ext|(Pee(K)ed Binds|(P)lan|(O)utlines|Pre(D)icate|Plan (L)oading|(W)ait events|(S)tatistics]   Description: Show SQL Plan         SQL ID: 8z91j441gu9n1      ------------- Last Monitored Binds --------------      --SID: 258,16699   var ACCEPTDIS VARCHAR2(32)   var BENAME VARCHAR2(32)   var EENAME VARCHAR2(32)   var IMPLTYP VARCHAR2(32)   var PROFILETYP VARCHAR2(32)   var SQLTYP NUMBER   var TID NUMBER      --SID: 258,16699   exec :ACCEPTDIS:='ACCEPTDISABLED';   exec :BENAME:='EXEC_42756';   exec :EENAME:='EXEC_43638';   exec :IMPLTYP:='IMPLEMENTATION';   exec :PROFILETYP:='SQL PROFILE';   exec :SQLTYP:=7   exec :TID:=29631      --SID: 1790,51175   var ACCEPTDIS VARCHAR2(32)   var BENAME VARCHAR2(32)   var EENAME VARCHAR2(32)   var IMPLTYP VARCHAR2(32)   var PROFILETYP VARCHAR2(32)   var SQLTYP NUMBER   var TID NUMBER      --SID: 1790,51175   exec :ACCEPTDIS:='ACCEPTDISABLED';   exec :BENAME:='EXEC_42756';   exec :EENAME:='EXEC_43638';   exec :IMPLTYP:='IMPLEMENTATION';   exec :PROFILETYP:='SQL PROFILE';   exec :SQLTYP:=7   exec :TID:=29631         ------------- SQL Text --------------      SELECT /*+ leading(oe f r l) */                 /* STN_REPT_TOP_PROF */                 xmlelement(                   "top_profiles",                    xmlagg(xmlelement("obj_id", object_id)))          FROM    (SELECT task_id, exec_name, exec_id, exec_start, object_id, sql_id,                     parsing_schema, phv, obj_attr8              FROM   (SELECT /*+ cardinality(o 5999) leading(e o) */                             e.task_id task_id,                             e.execution_name exec_name,                             e.execution_id exec_id,                             e.execution_start exec_start,                             o.id object_id,                             o.attr1 sql_id,                             o.attr3 parsing_schema,                             to_number(nvl(o.attr5, '0'))  phv,                             nvl(o.attr8,0) obj_attr8,                             row_number() over                               (partition by o.attr1                                order by     bitand(o.attr7, 32) asc,                                             e.execution_start desc)                               rn                      FROM    (SELECT e.task_id, e.name execution_name, e.id execution_id,                   e.exec_start execution_start, e.status status#            FROM (SELECT  task_id,                          min(execution_name) keep (dense_rank first order by                            execution_start) bename,                          max(execution_name) keep (dense_rank last order by                            execution_start) eename,                          min(execution_start) bestart,                          max(execution_start) eestart                  FROM    (SELECT task_id, name execution_name,                                  exec_start execution_start                           FROM   wri$_adv_executions                           WHERE  task_id = :tid and                                  name in (:bename, :eename))                  GROUP BY task_id) r,                  wri$_adv_executions e                  WHERE e.task_id = r.task_id and                  e.exec_start >= bestart and                  e.exec_start <= eestart and                  e.status IN (3, 4) and                  (bename <> eename OR e.name = bename)) e  /* e */,                             wri$_adv_objects o                      WHERE  o.task_id = e.task_id AND                             o.exec_name = e.execution_name AND                             o.type = :sqltyp)               WHERE rn = 1) oe  /* oe */,                 wri$_adv_findings f,                 wri$_adv_recommendations r,                 wri$_adv_rationale l          WHERE  oe.task_id = f.task_id AND oe.exec_name = f.exec_name AND                 oe.object_id = f.obj_id AND f.task_id = r.task_id AND                 f.exec_name = r.exec_name AND f.id = r.finding_id AND                 l.task_id = r.task_id AND l.exec_name = r.exec_name AND                 l.rec_id = r.id AND                 r.type = :profiletyp AND l.type = :impltyp AND                 l.attr1 = :acceptdis AND                 NOT EXISTS (SELECT 1                             FROM   dba_sql_profiles p                             WHERE  p.task_id = r.task_id AND                                    p.task_exec_name = r.exec_name AND                                    p.task_obj_id = oe.object_id AND                                    p.task_fnd_id = r.finding_id AND                                    p.task_rec_id = r.id)         ------------- SQL Plan (Plan Hash Value:589376886) --------------           0   ( )SELECT STATEMENT Optimizer=ALL_ROWS        1   (0) SORT (AGGREGATE)        2   (1)  NESTED LOOPS (ANTI) (Cost=30 Card=11 rows Bytes=0/129)        3   (2)   NESTED LOOPS (Cost=26 Card=11 rows Bytes=0/129)        4   (3)    NESTED LOOPS (Cost=11 Card=159 rows Bytes=0/93)        5   (4)     NESTED LOOPS (Cost=9 Card=608 rows Bytes=0/54)       #6   (5)      VIEW (Cost=8 Card=268 rows Bytes=0/31)       #7   (6)       WINDOW (SORT PUSHED RANK) (Cost=8 Card=489 rows Bytes=0/123)        8   (7)        NESTED LOOPS        9   (8)         NESTED LOOPS (Cost=7 Card=1667 rows Bytes=0/123)       10   (9)          NESTED LOOPS (Cost=5 Card=33 rows Bytes=0/84)       11  (10)           VIEW (Cost=3 Card=1 rows Bytes=0/57)       12  (11)            SORT (GROUP BY) (Cost=3 Card=1 rows Bytes=0/24)       13  (12)             TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_EXECUTIONS' (TABLE) (Cost=3 Card=2 rows Bytes=0/24)     *#14  (13)              INDEX (RANGE SCAN) OF 'WRI$_ADV_EXECS_PK' (INDEX (UNIQUE)) (Cost=2 Card=2 rows Bytes=0/)      #15  (10)           TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_EXECUTIONS' (TABLE) (Cost=2 Card=33 rows Bytes=0/27)      *16  (15)            INDEX (RANGE SCAN) OF 'WRI$_ADV_EXECS_IDX_03' (INDEX) (Cost=1 Card=33 rows Bytes=0/)      *17   (9)          INDEX (RANGE SCAN) OF 'WRI$_ADV_OBJECTS_IDX_01' (INDEX (UNIQUE)) (Cost=1 Card=1667 rows Bytes=0/)      #18   (8)         TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_OBJECTS' (TABLE) (Cost=2 Card=1634 rows Bytes=0/273)      *19   (5)      INDEX (RANGE SCAN) OF 'WRI$_ADV_FINDINGS_IDX_02' (INDEX (UNIQUE)) (Cost=1 Card=608 rows Bytes=0/23)      #20   (4)     TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_RECOMMENDATIONS' (TABLE) (Cost=2 Card=159 rows Bytes=0/39)      *21  (20)      INDEX (RANGE SCAN) OF 'WRI$_ADV_RECS_IDX_02' (INDEX (UNIQUE)) (Cost=1 Card=345 rows Bytes=0/)      #22   (3)    TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_RATIONALE' (TABLE) (Cost=15 Card=11 rows Bytes=0/36)      *23  (22)     INDEX (RANGE SCAN) OF 'WRI$_ADV_RATIONALE_PK' (INDEX (UNIQUE)) (Cost=3 Card=27293304 rows Bytes=0/)       24   (2)   VIEW PUSHED PREDICATE OF 'VW_SQ_1' (VIEW) (Cost=4 Card=0 rows Bytes=0/)       25  (24)    NESTED LOOPS (Cost=4 Card=0 rows Bytes=0/240)       26  (25)     NESTED LOOPS (Cost=4 Card=0 rows Bytes=0/204)      *27  (26)      HASH JOIN (Cost=4 Card=0 rows Bytes=0/168)      #28  (27)       TABLE ACCESS (BY INDEX ROWID) OF 'SQLOBJ$AUXDATA' (TABLE) (Cost=2 Card=0 rows Bytes=0/462)      *29  (28)        INDEX (RANGE SCAN) OF 'I_SQLOBJ$AUXDATA_TASK' (INDEX) (Cost=1 Card=0 rows Bytes=0/)     *#30  (27)       INDEX (SKIP SCAN) OF 'SQLOBJ$_PKEY' (INDEX (UNIQUE)) (Cost=1 Card=0 rows Bytes=0/322)      *31  (26)      INDEX (UNIQUE SCAN) OF 'I_SQL$TEXT_PKEY' (INDEX (UNIQUE))      *32  (25)     INDEX (UNIQUE SCAN) OF 'I_SQL$_PKEY' (INDEX (UNIQUE))      ------------- Predicate Information (Plan Hash Value:589376886) --------------        6 Filter: "RN"=1     7 Filter: ROW_NUMBER() OVER ( PARTITION BY "O"."ATTR1" ORDER BY BITAND("O"."ATTR7",32),INTERNAL_FUNCTION("E"."EXEC_START") DESC )<=1    14 Access: "TASK_ID"=:TID    14 Filter: ("NAME"=:BENAME OR "NAME"=:EENAME)    15 Filter: (INTERNAL_FUNCTION("E"."STATUS") AND ("BENAME"<>"EENAME" OR "E"."NAME"="BENAME"))    16 Access: "E"."TASK_ID"="R"."TASK_ID" AND "E"."EXEC_START">="BESTART" AND "E"."EXEC_START"<="EESTART"    17 Access: "O"."TASK_ID"="E"."TASK_ID" AND "O"."EXEC_NAME"="E"."NAME"    18 Filter: "O"."TYPE"=:SQLTYP    19 Access: "TASK_ID"="F"."TASK_ID" AND "EXEC_NAME"="F"."EXEC_NAME" AND "OBJECT_ID"="F"."OBJ_ID"    20 Filter: "R"."TYPE"=:PROFILETYP    21 Access: "F"."TASK_ID"="R"."TASK_ID" AND "F"."EXEC_NAME"="R"."EXEC_NAME" AND "F"."ID"="R"."FINDING_ID"    22 Filter: ("L"."TYPE"=:IMPLTYP AND "L"."ATTR1"=:ACCEPTDIS AND "L"."EXEC_NAME"="R"."EXEC_NAME" AND "L"."REC_ID"="R"."ID")    23 Access: "L"."TASK_ID"="R"."TASK_ID"    27 Access: "SO"."SIGNATURE"="AD"."SIGNATURE" AND "SO"."CATEGORY"="AD"."CATEGORY"    28 Filter: "AD"."OBJ_TYPE"=1    29 Access: "AD"."TASK_ID"="R"."TASK_ID" AND "AD"."TASK_EXEC_NAME"="R"."EXEC_NAME" AND "AD"."TASK_OBJ_ID"="OBJECT_ID" AND "AD"."TASK_FND_ID"="R"."FINDING_ID" AND "AD"."TASK_REC_ID"="R"."ID"    30 Access: "SO"."OBJ_TYPE"=1    30 Filter: "SO"."OBJ_TYPE"=1    31 Access: "SO"."SIGNATURE"="ST"."SIGNATURE"    32 Access: "SO"."SIGNATURE"="SQ"."SIGNATURE"      ------------- Plan Loading (Plan Hash Value:589376886) --------------       22: TABLE ACCESS BY INDEX ROWID                       #############################################(89.47%)    23: INDEX RANGE SCAN                                  #####(10.53%)      ------------- Waits Events (Plan Hash Value:589376886) --------------      ON CPU on SYS.WRI$_ADV_RECS_IDX_02(INDEX)                                  #####(9.65%)      ------------- Statistics Data --------------      Loads: 1   Load Versions: 1   First Load Time: 2014-03-12/22:46:24   Last Load Time: 2014-03-12/22:46:24   User Openings: 0   Parse Calls: 11   Executions: 11   Sorts(Average): 2   Fetches(Average): 1   Disk Reads(Average): .545   Buffer Gets(Average): 1416843.364   Elapsed Time(Average): 10.507 seconds   CPU Time(Average): 10.501 seconds   Run Time Memory(Average): .005M   PGA Size(Maximum): .015G   Temp Space(Maximum): 0G   Note: This version works in 11gR2. You may need to remove the part containing the not existing views/columns in other DB versions, e.g. v$sql_monitor. Download the latest verstion at here: http://www.HelloDBA.com/Download/showplan.zip --- Fuyuncat ---