On Rac dbms_xplan.display_cursor will give plan if cursor is present on node connected.

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/

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s