Home » SQL & PL/SQL » SQL & PL/SQL » Autotrace and explain Plan not found
Autotrace and explain Plan not found [message #219626] Thu, 15 February 2007 03:51 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi

SQL> set autotrace traceonly
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> Explain plan for select * from MST_ITEM;
Explain plan for select * from MST_ITEM
*
ERROR at line 1:
ORA-02402: PLAN_TABLE not found

Thanks
Re: Autotrace and explain Plan not found [message #219629 is a reply to message #219626] Thu, 15 February 2007 03:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
run utlxplan.sql which will be available in bin folder.

By
Vamsi
Re: Autotrace and explain Plan not found [message #219632 is a reply to message #219629] Thu, 15 February 2007 04:03 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi Vamsi,

Its just displaying as explained.

From where can we see the output of expalin plan.

Here is my querry Explain plan for select 8 from dual;

Its just displayed explained.

I guess we have to check plan_table. If so there are so many cols in that particular table. What are the cols we have to check.

ANd why set autotrace on is not working?

Thanks in advance
Re: Autotrace and explain Plan not found [message #219633 is a reply to message #219632] Thu, 15 February 2007 04:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
select plan_table_output 
from table(dbms_xplan.display());
What is the error you are getting while setting autotrace?
By
Vamsi
Re: Autotrace and explain Plan not found [message #219634 is a reply to message #219633] Thu, 15 February 2007 04:15 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi Vamsi,

This is the error. I am getting while setting up autotrace

set autotrace traceonly
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Thanks
Re: Autotrace and explain Plan not found [message #219637 is a reply to message #219634] Thu, 15 February 2007 04:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have to learn how to google
How do I enable AUTOTRACE
By
Vamsi
Re: Autotrace and explain Plan not found [message #219643 is a reply to message #219637] Thu, 15 February 2007 04:43 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Thanks alot vamsi

Can you help me in tuning this querries or any suggestion would really helps me a lot.

This portion of the procedure is taking on an average 1 hour 30 minutes I am sure it’s the second loop which is taking so much time. Can we improve it by either improving the sql or indexing any table etc?

UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 0,AUTO_APPROVE_WINDOW = 7,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 0 ,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE AUTO_APPROVE_WINDOW is null and sys_ent_state = 'ACTIVE';


UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 1,AUTO_APPROVE_WINDOW = 7,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 0 ,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE (SCENARIO_ID,ITEM_ID,LOCATION_ID) =
(SELECT 0,MSTITEM.ITEM_ID,LOCATION_ID FROM MST_LOCATION ,MST_ITEM mstitem WHERE
(CATEGORY = 'SPARES' OR CATEGORY = 'ACCESSORIES') AND
LOCATION_CATEGORY in ('RETAIL','DIRECT','INSURANCE','DHRP','PRDC')

AND LOCATION_ID = OUTER.LOCATION_ID AND
OUTER.ITEM_ID = mstitem.ITEM_ID AND
mstitem.SCENARIO_ID = 0 AND
(
(is_rp_initialized = '0' or is_rp_initialized is null ) OR (is_bod_initialized = '0' or is_bod_initialized is null ))
) and sys_ent_state = 'ACTIVE';

UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 0,AUTO_APPROVE_WINDOW = 1,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 1,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE (SCENARIO_ID,ITEM_ID) in
(SELECT 0,ITEM_ID FROM MST_ITEM WHERE
(CATEGORY = 'HANDSETS' OR CATEGORY = 'REFURBS')) and AUTO_APPROVE_WINDOW != 1 and sys_ent_state = 'ACTIVE';


Thanks in advance
Re: Autotrace and explain Plan not found [message #219648 is a reply to message #219643] Thu, 15 February 2007 04:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't open multiple threads about the same subject.
Continue in your other thread.
Re: Autotrace and explain Plan not found [message #219730 is a reply to message #219648] Thu, 15 February 2007 13:51 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
btw in 10g I would not create a new plan_table, I would make it a public synonym for SYS.PLAN_TABLE$.

Handy script: xplan.sql
Previous Topic: CLOB COLUMNS
Next Topic: PLS-00103: ERROR
Goto Forum:
  


Current Time: Thu Dec 08 16:22:29 CST 2016

Total time taken to generate the page: 0.10884 seconds