Forums Active Threads
Print Thread
explain plan not working
#48745 03/10/14 09:16 PM
Joined: Sep 2013
Posts: 11
R
Roger25 Offline OP
Member
OP Offline
Roger25
Member
R
Joined: Sep 2013
Posts: 11
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.
Re: explain plan not working
Roger25 #48748 03/11/14 06:03 AM
Joined: Feb 2013
Posts: 251
UTC+10
I
IvanZ Offline
Member
Offline
IvanZ
Member
I
Joined: Feb 2013
Posts: 251
UTC+10
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')
   )
Re: explain plan not working
IvanZ #48750 03/11/14 01:53 PM
Joined: Jul 2004
Posts: 592
W
Worker Offline
Member
Offline
Worker
Member
W
Joined: Jul 2004
Posts: 592
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

Re: explain plan not working
Worker #48751 03/11/14 04:35 PM
Joined: Sep 2013
Posts: 11
R
Roger25 Offline OP
Member
OP Offline
Roger25
Member
R
Joined: Sep 2013
Posts: 11
Nice smile 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.
Re: explain plan not working
Roger25 #48753 03/12/14 12:31 AM
Joined: Feb 2013
Posts: 251
UTC+10
I
IvanZ Offline
Member
Offline
IvanZ
Member
I
Joined: Feb 2013
Posts: 251
UTC+10
[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
Re: explain plan not working
IvanZ #48756 03/12/14 07:03 AM
Joined: Sep 2013
Posts: 11
R
Roger25 Offline OP
Member
OP Offline
Roger25
Member
R
Joined: Sep 2013
Posts: 11
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,

Last edited by Roger25; 03/12/14 07:03 AM.
Re: explain plan not working
Roger25 #48758 03/12/14 01:24 PM
Joined: Apr 2003
Posts: 32
lausanne, Switzerland
roselan Offline
Member
Offline
roselan
Member
Joined: Apr 2003
Posts: 32
lausanne, Switzerland
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

there is no fork
roselan
Re: explain plan not working
Worker #48801 03/21/14 01:50 PM
Joined: Sep 2013
Posts: 11
R
Roger25 Offline OP
Member
OP Offline
Roger25
Member
R
Joined: Sep 2013
Posts: 11
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)

Last edited by Roger25; 03/21/14 01:59 PM.
Re: explain plan not working
Roger25 #48802 03/23/14 06:34 AM
Joined: Feb 2013
Posts: 251
UTC+10
I
IvanZ Offline
Member
Offline
IvanZ
Member
I
Joined: Feb 2013
Posts: 251
UTC+10
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214

alter session set statistics_level=all; - for most info

Moderated by  support 

Link Copied to Clipboard
Privacy Policy · Forum Rules · Mark All Read Contact Us · Forum Help ·
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.066s Queries: 15 (0.008s) Memory: 2.5462 MB (Peak: 3.0393 MB) Data Comp: Off Server Time: 2024-04-30 01:21:10 UTC
Valid HTML 5 and Valid CSS