Forum Stats

  • 3,874,091 Users
  • 2,266,678 Discussions
  • 7,911,723 Comments

Discussions

Display the execution plan from any RAC instance

Mohamed Houri
Mohamed Houri Member Posts: 1,281 Bronze Trophy
edited Apr 7, 2022 12:19PM in Database Ideas - Ideas

In a RAC database, make displaying the execution plan visible from any node.

If I'm on instance #1 and I want to get the plan for an SQL_ID that is located in instance #2, then, I must be able to get this execution plan from instance #1 simply by using this

 select * from table(dbms_xplan.display_cursor(<sql_id>,null)); 

It’s the same type of thing as doing a select * from gv$sql instead of select * from gvsql;

 As a workaround, I am using this

SELECT 
  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
;

 

Best regards

Mohamed Houri

Mohamed Houri
1 votes

Active · Last Updated