Home » RDBMS Server » Performance Tuning » Reg. out put of DBMS_XPLAN.DISPLAY
Reg. out put of DBMS_XPLAN.DISPLAY [message #122148] Fri, 03 June 2005 07:00 Go to next message
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

Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122155 is a reply to message #122148] Fri, 03 June 2005 07:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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 Go to previous messageGo to next message
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
Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122162 is a reply to message #122159] Fri, 03 June 2005 08:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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.
Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122170 is a reply to message #122162] Fri, 03 June 2005 09:17 Go to previous messageGo to next message
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
Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122172 is a reply to message #122170] Fri, 03 June 2005 09:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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.
Re: Reg. out put of DBMS_XPLAN.DISPLAY [message #122299 is a reply to message #122148] Sun, 05 June 2005 07:23 Go to previous message
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?
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:
  


Current Time: Tue Sep 29 07:01:54 CDT 2020