OraFAQ Forum: SQL & PL/SQL » How to check Plan table

Home » SQL & PL/SQL » SQL & PL/SQL » How to check Plan table (Oracle 10.2.0.2.0 ,solaris)

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

How to check Plan table [message #411476]

Sat, 04 July 2009 03:28

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,

Report message to a moderator

Re: How to check Plan table [message #411478 is a reply to message #411476]

Sat, 04 July 2009 03:33

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

* search it in table list
* desc plan_table
* select 'OK' from plan_table where rownum=1

Regards
Michel

Report message to a moderator

Re: How to check Plan table [message #411480 is a reply to message #411478]

Sat, 04 July 2009 04:21

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

Report message to a moderator

Re: How to check Plan table [message #411487 is a reply to message #411480]

Sat, 04 July 2009 04:53

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

Report message to a moderator

Re: How to check Plan table [message #411497 is a reply to message #411476]

Sat, 04 July 2009 06:14

ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore

Senior Member

Will it help you ??

http://www.orafaq.com/forum/t/147281/96414/

Report message to a moderator

Re: How to check Plan table [message #411513 is a reply to message #411480]

Sat, 04 July 2009 09:34

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to check Plan table [message #411517 is a reply to message #411487]

Sat, 04 July 2009 09:49

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,

Report message to a moderator

Re: How to check Plan table [message #411520 is a reply to message #411476]

Sat, 04 July 2009 10:11

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: How to check Plan table [message #411521 is a reply to message #411517]

Sat, 04 July 2009 10:20

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to check Plan table [message #411523 is a reply to message #411476]

Sat, 04 July 2009 10:33

BlackSwan
Messages: 26766
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

Report message to a moderator