Home » SQL & PL/SQL » SQL & PL/SQL » Explain plan attached (11g)
Explain plan attached [message #675670] Thu, 11 April 2019 06:49 Go to next message
venkat85
Messages: 3
Registered: July 2012
Junior Member
Hi guys,



i have attached the Explain for the long take query.
please suggest your idea and help me to take require action on it

what should be the approach
Re: Explain plan attached [message #675679 is a reply to message #675670] Thu, 11 April 2019 09:57 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Your PLAN_TABLE is out of date. Drop it, and the current version will be used.

You need to set your line size to something greater, so that the plan is readable.

You need to run the statement and get the plan like this,
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
Re: Explain plan attached [message #675686 is a reply to message #675679] Fri, 12 April 2019 01:30 Go to previous messageGo to next message
venkat85
Messages: 3
Registered: July 2012
Junior Member
Hi

I am getting below error for

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

but iam getting


SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g0r6x2tykv29b, child number 0

explain plan for    SELECT /*+ GATHER_PLAN_STATISTICS */  DISTINCT
HOB.PO_HEADER_ID,OOH.ORDER_NUMBER,POH.ATTRIBUTE13 OC_NO FROM
OE_ORDER_HEADERS OOH,OE_ORDER_LINES OOL,HWKR_OM_B2B_RELATION_V HOB,
PO_HEADERS POH  WHERE OOH.HEADER_ID = OOL.HEADER_ID  AND
HOB.PO_HEADER_ID =   POH.PO_HEADER_ID  AND OOL.FLOW_STATUS_CODE NOT IN
( 'ENTERED','CANCELLED'  )    AND OOL.LINE_ID = HOB.OE_LINE_ID (+)
AND OOH.ORDER_NUMBER = 1067045

NOTE: cannot fetch plan for SQL_ID: g0r6x2tykv29b, CHILD_NUMBER: 0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)



14 rows selected.




Re: Explain plan attached [message #675687 is a reply to message #675686] Fri, 12 April 2019 02:03 Go to previous message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Please read instructions. I did not say "run explain plan". I said "run the statement".
Previous Topic: running balance query
Next Topic: Carriage Return Finding
Goto Forum:
  


Current Time: Mon Jun 17 14:36:09 CDT 2019