Home » SQL & PL/SQL » SQL & PL/SQL » How to check Plan table (Oracle 10.2.0.2.0 ,solaris)
How to check Plan table [message #411476] Sat, 04 July 2009 03:28 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



Hi all,


Sometimes i see in explain plan the execution plan will not exist in sql developer tool. (i.e it will be blank in that column).


Note
(in order to use explain plan make sure the plan table exist)


My question is how to check my plan table exist in sql developer tool.


Regards,
Re: How to check Plan table [message #411478 is a reply to message #411476] Sat, 04 July 2009 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
* search it in table list
* desc plan_table
* select 'OK' from plan_table where rownum=1

Regards
Michel
Re: How to check Plan table [message #411480 is a reply to message #411478] Sat, 04 July 2009 04:21 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member




Hi michel,

I tried what you said but i could see no rows when
i executed the query.
(SQL> select 'OK' from plan_table where rownum=1)
no rows selected

What does it mean?

Please find the screenshot below for your reference


Connected to:
Oracle Database 10g Enterprise Edition
Release 10.2.0.4.0 - Produc
With the Partitioning, OLAP,
Data Mining and Real Application Test
ons

SQL> desc plan_table
Name Null? Type
-----------------------------------------
------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)

SQL>
SQL> select 'OK' from plan_table where rownum=1
2 /

no rows selected



Regards
Re: How to check Plan table [message #411487 is a reply to message #411480] Sat, 04 July 2009 04:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Read instructions first about how to post.

Quote:
I tried what you said but i could see no rows when
i executed the query.
(SQL> select 'OK' from plan_table where rownum=1)
no rows selected

What does it mean?


PLAN_TABLE exists in your schema.

regards,
Delna
Re: How to check Plan table [message #411497 is a reply to message #411476] Sat, 04 July 2009 06:14 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Will it help you ??

http://www.orafaq.com/forum/t/147281/96414/
Re: How to check Plan table [message #411513 is a reply to message #411480] Sat, 04 July 2009 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i executed the query.
(SQL> select 'OK' from plan_table where rownum=1)
no rows selected

What does it mean?

You don't have access to a plan_table, so yo uhave to create one.

Regards
Michel
Re: How to check Plan table [message #411517 is a reply to message #411487] Sat, 04 July 2009 09:49 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member


Hi all,


"PLAN_TABLE exists in your schema".


How do you say "PLAN_TABLE exists in your schema".



Regards,
Re: How to check Plan table [message #411520 is a reply to message #411476] Sat, 04 July 2009 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: How to check Plan table [message #411521 is a reply to message #411517] Sat, 04 July 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mohan10g wrote on Sat, 04 July 2009 16:49

Hi all,


"PLAN_TABLE exists in your schema".


How do you say "PLAN_TABLE exists in your schema".



Regards,

It is the opposite, plan_table DOES NOT exist in your schema.

Regards
Michel

Re: How to check Plan table [message #411523 is a reply to message #411476] Sat, 04 July 2009 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>>You don't have access to a plan_table, so yo uhave to create one.

/u01/app/oracle/rdbms/admin/utlxplan.sql

procedure above will create table PLAN_TABLE
Re: How to check Plan table [message #411581 is a reply to message #411521] Sun, 05 July 2009 10:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No rows returned instead of invalid table name, so plan table DOES exist, only contains no rows.
Re: How to check Plan table [message #411582 is a reply to message #411581] Sun, 05 July 2009 11:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/1620/0/ Of course ./fa/1606/0/

Regards
Michel

[Updated on: Sun, 05 July 2009 11:13]

Report message to a moderator

Previous Topic: rebuilding indexes / dbms_stats.gather_schema_stats (merged 5) 10.1.0.5 unix
Next Topic: Query to find parent - child records
Goto Forum:
  


Current Time: Sat Dec 03 13:51:53 CST 2016

Total time taken to generate the page: 0.06845 seconds