| Explain Plan [message #237668] |
Tue, 15 May 2007 07:44  |
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 #237679 is a reply to message #237669] |
Tue, 15 May 2007 08:04  |
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 |
Frank
Messages: 7901
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
