OraFAQ Forum: Performance Tuning » Reg. out put of DBMS

Home » RDBMS Server » Performance Tuning » Reg. out put of DBMS_XPLAN.DISPLAY

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

Reg. out put of DBMS_XPLAN.DISPLAY [message #122148]

Fri, 03 June 2005 07:00

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

hi all,
why my output of the following query is blank:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

before running that i have expalin planed the query.

The out put is:-
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

---------------------------------------------
---------

9 rows selected.

real: 16
SQL>

Please help how to see the output.

Thanks
Dinesh

Report message to a moderator

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122155 is a reply to message #122148]

Fri, 03 June 2005 07:58

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

scott@9i > @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

scott@9i > exec dbms_stats.gather_table_stats('SCOTT','EMP',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

scott@9i > explain plan for select count(*) from emp;

Explained.

scott@9i > select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |       |  1125 |
|   1 |  SORT AGGREGATE      |             |     1 |       |       |
|   2 |   TABLE ACCESS FULL  | EMP         | 14336 |       |  1125 |
--------------------------------------------------------------------

Note: cpu costing is off

10 rows selected.
--
-- IF YOU ARE LOOKING FOR A SPECIFIC PLAN
--
scott@9i >  explain plan set statement_id='magPLANforEMP' for select * from emp;

Explained.

scott@9i > SELECT * FROM table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','magPLANforEMP','ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 14336 |   518K|  1125 |
|   1 |  TABLE ACCESS FULL   | EMP         | 14336 |   518K|  1125 |
--------------------------------------------------------------------

Note: cpu costing is off

9 rows selected.

[Updated on: Fri, 03 June 2005 08:02]

Report message to a moderator

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122159 is a reply to message #122155]

Fri, 03 June 2005 08:09

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

Thanks for ur reply,
i was looking for it 4 a long time.
The whole thing is that i have to analyze the table to have the out put of dbms_xpla.display.I have done the following:-

SQL> select count(*) from plan_table;

COUNT(*)
----------
2

real: 16
SQL> exec dbms_stats.gather_table_stats('argen','mst_gcm',cascade=>true);

PL/SQL procedure successfully completed.

real: 672
SQL> explain plan for
<<my_query>>;

Explained.

real: 16
SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

---------------------------------------------
--------------

9 rows selected.

real: 31
SQL>

So what is the reason now, why i am not having enable to see the result.
Please help.
Thanks a lot.
dinesh

Report message to a moderator

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122162 is a reply to message #122159]

Fri, 03 June 2005 08:35

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

post your oracle version and OS.
look into your environment settings for sqlplus.
I am not sure if this has to do with DBMS_OUTPUT.
I cant reproduce this case.

Report message to a moderator

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122170 is a reply to message #122162]

Fri, 03 June 2005 09:17

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

This is the banner of sql*plus it is 9.2

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

and os is windows 2000 professional.

can u please tell me the name of the sql*plus env. variable that i should look into.

Thanks a lot for ur interest.

With regards
Dinesh

Report message to a moderator

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122172 is a reply to message #122170]

Fri, 03 June 2005 09:30

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I cannot reproduce this.
Talk to oracle support

sql>SELECT * FROM table(DBMS_XPLAN.DISPLAY);

above will display the plan of the LAST EXPLAIN plan.
Either your collection method has some issues or DBMS_XPLAN.
Try recreating the plan_table and startover.
Environment variables have nothing to do with this.

Report message to a moderator

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122299 is a reply to message #122148]

Sun, 05 June 2005 07:23

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

You might have Un-compiled procedures, try to find and and recomplie that? Did you applied any patch recently? When did you start facing it for the first time?

Report message to a moderator

Previous Topic:	Select on same table gives poor performace.
Next Topic:	can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 06:39:19 CST 2025