explain plan not working - Allround Automations forums

Hi,

I want to display the explain plan for a given select statement in Command Window. I tried:

[quote]Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as system@orcl

SQL> explain plan for select * from hr.employees;
Explained

SQL>[/quote]

so.. where is the plan?

Also I tried with:

SQL> select * from hr.employees;

and then

SQL> select * from table (dbms_xplan.display_cursor);

But I got:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected

I want to display the explain plans in Command window because it's well formatted and I can copy/paste them into OTN forums, for example.

My version of software is 10.0.4.1708.

Thank you.

It is not possible in PLD 'as is'
but
I made the following workaround:

1. Run SQL with unique comment

Code

select /*mycomm*/ * from dual

2. Run XPLAN

Code

select * from table(dbms_xplan.display_cursor(sql_id => 
          (select sql_id from v$sql 
                  where upper(sql_text) like '%/*MYCOMM*/%' and upper(sql_text) not like '%/*MYCOMM1*/%'),
          format => 'ADVANCED')
   )

This works for me:

Code

SQL> explain plan for select * from dual;
Explained

SQL> select * from TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected

Nice

ok
and can we control which columns from explain plan should be displayed in this command window? In a SQL Window, we can press the Settings button and add what information we want.. but in the explain plan above, how can control which information to appear?

One more thing, to the developer: it would great if we would have a parameter, which we set to ON, and after issuing a query, the query plan is automatically displayed (without we issuing "select * from table (......."

And also, why AUTOTRACE cannot be set in PL/SQL Developer, like in the following example (SQL*Plus):

[quote]
SQL> set autotrace on

SQL> select * from dual;

D

-

X

Execution Plan

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

Plan hash value: 272002086

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

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

Statistics

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

5 recursive calls

0 db block gets

5 consistent gets

3 physical reads

0 redo size

331 bytes sent via SQL*Net to client

337 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace off [/quote]

In PL/SQL Developer we get "Cannot set AUTOTRACE"

Thanks.

[quote=Roger25]and can we control which columns from explain plan should be displayed in this command window? In a SQL Window, we can press the Settings button and add what information we want.. but in the explain plan above, how can control which information to appear?[/quote]

There's a documentation for the dbms_xplan package

Thank you.
IF you can implement this :

One more thing, to the developer: it would great if we would have a parameter, which we set to ON, and after issuing a query, the query plan is automatically displayed (without we issuing "select * from table (......."

in future versions, it would be great.

Regards,

my 2 cents as i'm passing by.

We had bad surprises with plan_table synonym in 11g/12c SPM. In some cases baselines were not shown in the notes, causing many headscratches.

here is the queries template I use now:
[quote]explain plan into sys.plan_table$ for
SELECT * FROM DUAL;

select *
from table(dbms_xplan.display(table_name => 'sys.plan_table$', format => 'ADVANCED +peeked_binds ALLSTATS LAST' ));[/quote]

note: generally i remove the "allstats last" format, and if you don't want to many stuff, replace "ADVANCED" by "ALL".

hope this help

In my PL/SQL Developer, the plan appears as :

Code

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Rows
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |    18
|   1 |  SORT ORDER BY                          |                        |    18
|   2 |   NESTED LOOPS OUTER                    |                        |    18
|   3 |    NESTED LOOPS OUTER                   |                        |  4451
|   4 |     NESTED LOOPS OUTER                  |                        |   544
|   5 |      NESTED LOOPS OUTER                 |                        |    66
|   6 |       NESTED LOOPS                      |                        |  8168
|   7 |        FAST DUAL                        |                        |     1
|   8 |        COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |  8168
|   9 |       VIEW                              |                        |  8168
|  10 |        COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |  8168
|  11 |      VIEW                               |                        |  8168
|  12 |       COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |  8168
|  13 |     VIEW                                |                        |  8168
|  14 |      COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |  8168
|  15 |    VIEW                                 |                        |  8168
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  16 |     COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |  8168
--------------------------------------------------------------------------------

Why I cannot see all the columns you see? (e.g. CPU Cost)

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214

alter session set statistics_level=all; - for most info