Posts Tagged ‘DBMS_XPLAN’
Using DBMS_XPLAN to display Cursor Plans for a SQL in all RAC nodes
Just sharing a lightweight script that uses DBMS_XPLAN.DISPLAY_CURSOR to display Execution Plans for a given SQL_ID out of all RAC nodes. Nothing fancy, just a workaround to the lack of “instance” parameter on this API.
SET ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LIN 2000 NEWP NONE PAGES 0 LONG 2000000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT OFF; COL inst_child FOR A21; BREAK ON inst_child SKIP 2; SPO rac_xplan.txt; PRO Current Execution Plans (last execution) PRO PRO Captured while still in memory. Metrics below are for the last execution of each child cursor. PRO If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated. PRO SELECT RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child, t.plan_table_output FROM gv$sql v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST', 'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t WHERE v.sql_id = '&&sql_id.' AND v.loaded_versions > 0; SPO OFF; SET ECHO OFF FEED 6 VER ON SHOW OFF HEA ON LIN 80 NEWP 1 PAGES 14 LONG 80 LONGC 80 SQLC MIX TAB ON TRIMS OFF TI OFF TIMI OFF ARRAY 15 NUMF "" SQLP SQL> SUF sql BLO . RECSEP WR APPI OFF AUTOT OFF;
Sample Output
Current Execution Plans (last execution) Captured while still in memory. Metrics below are for the last execution of each child cursor. If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated. Inst: 1 Child: 0 Plan hash value: 3724264953 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 220 (100)| | 1 |00:00:00.01 | 784 | | 1 | SORT AGGREGATE | | 1 | 1 | 45 | | | 1 |00:00:00.01 | 784 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 45 | 220 (1)| 00:00:03 | 0 |00:00:00.01 | 784 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("C2"='WHATEVER' AND TRANSLATE(TO_CHAR(SIN("N4")),'1','a')='a')) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] Inst: 1 Child: 1 Plan hash value: 3724264953 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 123 | 1 |00:00:01.88 | 784 | | 1 | SORT AGGREGATE | | 1 | 1 | 45 | | 1 |00:00:01.88 | 784 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 45 | 123 | 0 |00:00:01.88 | 784 | ----------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('_optimizer_cost_model' 'io') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((TRANSLATE(TO_CHAR(SIN("N4")),'1','a')='a' AND "C2"='WHATEVER')) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] Note ----- - cpu costing is off (consider enabling it)