OraFAQ Forum: SQL & PL/SQL » Explain plan attached

Home » SQL & PL/SQL » SQL & PL/SQL » Explain plan attached (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Explain plan attached [message #675670]

Thu, 11 April 2019 06:49

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

Attachment: explain_plan.txt
(Size: 7.08KB, Downloaded 1833 times)

Report message to a moderator

Re: Explain plan attached [message #675679 is a reply to message #675670]

Thu, 11 April 2019 09:57

John Watson
Messages: 8997
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'));

Report message to a moderator

Re: Explain plan attached [message #675686 is a reply to message #675679]

Fri, 12 April 2019 01:30

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.

Report message to a moderator

Re: Explain plan attached [message #675687 is a reply to message #675686]

Fri, 12 April 2019 02:03

John Watson
Messages: 8997
Registered: January 2010
Location: Global Village

Senior Member

Please read instructions. I did not say "run explain plan". I said "run the statement".

Report message to a moderator

Previous Topic:	running balance query
Next Topic:	Carriage Return Finding

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 27 13:30:29 CDT 2026