Home » SQL & PL/SQL » SQL & PL/SQL » Execution Plan of query (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Execution Plan of query [message #684690] Tue, 27 July 2021 03:32 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All

How to get the execution plan of the query specific to one plan hash value .I am using the following query to get the current plan :



select  *  from table(DBMS_XPLAN.display_cursor('SQL_ID'));
But as per history multiple plans are available for the same SQL ID. I Wants to get the plans of the all SQL PLAN HASH VLAUE


select distinct PLAN_HASH_VALUE  from   DBA_HIST_SQL_PLAN where SQL_ID  ='SQL_ID'; 

Is there any way to get the Detailed plans all those plan ids

Thanks
SaiPradyumn

Re: Execution Plan of query [message #684691 is a reply to message #684690] Tue, 27 July 2021 03:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to decide whether you want to extract plans from the library cache with dbms_xplan.display_cursor, in which case the plans are distinguished by specifying the cursor_child_number. Or from the AWR, in which case use dbms_xplan.display_awr and give it the plan_hash_value. Or don't, andyou'll see them all.
It's all in the docs,
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xplan.htm
Re: Execution Plan of query [message #684694 is a reply to message #684691] Tue, 27 July 2021 07:03 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

thanks John Watson for your information. Will try to explore


Thanks
Revathi.T
Previous Topic: Prime Generation Logic using in single Query
Next Topic: QUERY TO DISPLAY EDUCATION
Goto Forum:
  


Current Time: Thu Mar 28 03:35:30 CDT 2024