Home » SQL & PL/SQL » SQL & PL/SQL » Explain Plan
Explain Plan [message #237668] Tue, 15 May 2007 07:44 Go to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Hi all,

I'm reading through the explain plan documentation and am having trouble running the examples. I have the following statement that I would like an explain plan for:
explain plan
 set statement_id = 'HIL:test1' for
 select wdj.wip_supply_type, djd.analyse_shortage
 from wip_discrete_jobs wdj, wip_discrete_jobs_dfv djd
 where wdj.rowid = djd.row_id;


However when I run:
SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','HIL:test1','BASIC'));

I get the following error: Error: cannot fetch plan for statement_id 'HIL:test1'.

However when I run this example:
 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


I do get what looks like an explain plan but for a different sql script.

Anyone have any idea's what I could be doing wrong?? I do run my script before the time so it should be in there somewhere, right??
Re: Explain Plan [message #237669 is a reply to message #237668] Tue, 15 May 2007 07:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the response for your actual explain plan statement?
Re: Explain Plan [message #237679 is a reply to message #237669] Tue, 15 May 2007 08:04 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Quote:
I get the following error: Error: cannot fetch plan for statement_id 'HIL:test1'.


I have been reading more and now see that when I run the following statement:
 SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'HIL:test1','BASIC'));


What would be the difference between the NULL and actually saying that the table must be PLAN_TABLE?? I have actually checked the PLAN_TABLE and the data is there.
Re: Explain Plan [message #237690 is a reply to message #237679] Tue, 15 May 2007 08:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I did not ask you what you got when you queried the plan table, but when you executed the explain plan itself.

Who owns this plan_table?
Re: Explain Plan [message #237705 is a reply to message #237690] Tue, 15 May 2007 08:54 Go to previous message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Firstly I am not sure who the owner is. Is there a way to check??
I changed the statement around and actually got a result but I had to run the statements one after the other:
EXPLAIN PLAN 
set statement_id = 'HIL:test' FOR
select wdj.wip_supply_type, djd.analyse_shortage
 from wip_discrete_jobs wdj, wip_discrete_jobs_dfv djd
 where wdj.rowid = djd.row_id;
SELECT pt.operation || decode(pt.object_name, NULL, '', ' on ') ||
			 pt.object_name description,
			 pt.cardinality,
			 pt.cost
FROM   plan_table pt
START  WITH pt.id = 0
		 AND    pt.statement_id = 'HIL:test'
CONNECT BY PRIOR pt.id = pt.parent_id
		AND    PRIOR pt.statement_id = pt.statement_id;

It seems to be working now so all's well that end well.
Previous Topic: More than one recordset in a procedure
Next Topic: Indexing tables
Goto Forum:
  


Current Time: Sun Dec 04 17:05:46 CST 2016

Total time taken to generate the page: 0.07349 seconds