Most of us know, dbms_xplan.display_cursor utility is use to get plan, outline information for specific SQL from Cursor cache.
During an performance issue analysis identified.
For RAC Database(11g), dbms_xplan.display_cursor will give data if and only if necessary cursor(plan) is present in current connected node.
Lets walk through field work.
Env : 4 Node 11g Database
For a specific SQL “dm6xjbpavar2y” , below were GV$SQL (Child cursor) output.
INST_ID SQL_ID PLAN_HASH_VALUE
———- ————- —————
3 dm6xjbpavar2y 2431269899
2 dm6xjbpavar2y 2431269899
2 dm6xjbpavar2y 2431269899
4 dm6xjbpavar2y 2431269899
Child Cursor Exist only on Node 2,3 and 4.
Through client got connected to Node 1.
INSTANCE_NUMBER INSTANCE_NAME
————— —————-
1 XXXXX1
Let run display_cursor command.
SQL_NODE1>select * from table(dbms_xplan.display_cursor('dm6xjbpavar2y'));
Output :
“SQL_ID: dm6xjbpavar2y, child number: 0 cannot be found ”
Through GV$SQL it is Quite Evident, Child Cursor Exist but not in Instance 1.
Hence when we connected to Node 1 and try to get plan, Oracle will say no child cursor.!!
Let connect to node 3.
INSTANCE_NUMBER INSTANCE_NAME
————— —————-
3 XXXXXX3
SQL_NODE3>select * from table(dbms_xplan.display_cursor('dm6xjbpavar2y'));
Output :
SQL_ID dm6xjbpavar2y, child number 0
-------------------------------------
SELECT DPP.Col1, PCD.Col2FROM
TAB_TEST DPP, TAB_TEST1 PCD WHERE
......
......
Expected Output was resulted when we connected to node where child cursor Exists.
Work Around:
Fount Below Script which give Plan output from All Nodes.
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 = 'dm6xjbpavar2y';
will give detailed level output.
Output :
Inst: 3 Child: 0 Plan hash value: 2431269899
Inst: 3 Child: 0
Inst: 3 Child: 0 ----------------------------------------------------------
Inst: 3 Child: 0 | Id | Operation | Name | E-Rows |
Inst: 3 Child: 0 ----------------------------------------------------------
Inst: 3 Child: 0 | 0 | SELECT STATEMENT | | |
Inst: 3 Child: 0 |* 1 | COUNT STOPKEY | | |
Inst: 3 Child: 0 | 2 | NESTED LOOPS | | |
Inst: 3 Child: 0 | 3 | NESTED LOOPS | | 1 |
..........................................
...........................................
Reference :
http://carlos-sierra.net/2013/06/17/using-dbms_xplan-to-display-cursor-plans-for-a-sql-in-all-rac-nodes/
