Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Display the execution plan from any RAC instance

Mohamed HouriApr 7 2022 — edited Jan 8 2024

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

Comments
Post Details
Added on Apr 7 2022
2 comments
560 views