Carlos Sierra's Tools and Tips

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)