Not getting plan when querying with dbms_xplan.display_cursor

[jking]

Whenever I run the below script inside of a command window:

[size=8pt]set serveroutput off
select * from dual;
select * from table(dbms_xplan.display_cursor);[/size]

I get the below output:

[size=8pt]PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 2
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2
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[/size]

If I run the same script inside of Oracle SQL*Plus, I get the correct output:

[size=8pt]
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------[/size]

Am I doing something wrong in PL/SQL Dev or is there something else wrong?

Thanks,
Joe

 

[Marco Kalter]

Apparently the Command Window checks the transaction status between statements, and this conflicts with your script. We'll see if we can enhance this.

 

[lparker]

We are using version 8 on Oracle 11g. Could you please let us know when the issue of using dbms_xplan.display_cursor will be resolved.

Thanks
Lise

 

[bcoulam]

We're now two versions later and this still isn't fixed. I'm on PSD version 10.0.5.1710

I'm getting the exact same output when I call upon dbms_xplan.display_cursor, either without arguments, or with the sqlid parameter bypassed using named notation. According to the docs if sqlid isn't passed dbms_xplan will automatically grab the sqlid of the last statement executed in the current session. This works fine in SQL*Plus and TOAD, but returns the output seen in the original post above when attempted in PSD's Command window or SQL window.

In my opinion this is a larg-ish bug and ought to be fixed in the next patch. Anytime I'm forced to use TOAD or SQL*Plus to get correct behavior, it ought to be fixed pronto.

 

[SteffenK]

Hello Marco,

we would appreciate a fix/solution for this issue too.
We need this for some schemas which are not allowed to select the base tables of SQL. Thus a normal Explain Plan Window does not work too.

Thanks
Steffen