Skip to Main Content
  • Questions
  • cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, kunlun.

Asked: July 06, 2016 - 10:38 am UTC

Answered by: Chris Saxon - Last updated: February 22, 2018 - 4:47 pm UTC

Category: Developer - Version: 11gR2

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Blocks, Messages, Rows

You Asked

I am using PL/SQL in front end and Oracle 11gR2 in back end to developing a db app. In the middle of time, I am going to check hard parsed execution plan of one sql statment. What make me surprised is that the PL/SQL Developer seems has no capability given to developer to analysis the hard parsed plan?

When I type "select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));" after running one sql statment, it just only show me the following:
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 am not very clear wheather or not this problem is due to the "serveroutput" parameter is on, but how to turn serveroutput off in PL/SQL Developer? How can I analysize execution plan within PL/SQL Developer environment?

and we said...

Ensure you have disabled serveroutput "set serveroutput off":

SQL> select * from dual;

D
-
X

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

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

SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, 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_plan)


8 rows selected.

SQL> set serveroutput off
SQL> select * from dual;

D
-
X

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

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

SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|   1 |  TABLE ACCESS FULL| DUAL |      1 |
-------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


https://connormcdonald.wordpress.com/2016/01/29/common-gather_plan_statistic-confusion/

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

You must have not read what my said carfully and don't understand what the problem I faced is

July 06, 2016 - 11:43 am UTC

Reviewer: kunlun li

I exactly know what your suggest, but how to set off the serveroutput in PL/SQL Developer? I hope use this tool to check plan, otherwise, sqlplus' chaos output would not avoid? So, in "PL/SQL Developer", how should I set serveroutput off?
Chris Saxon

Followup  

July 06, 2016 - 1:23 pm UTC

I'm not familiar with PL/SQL Developer. If running

"set serveroutput off"

In the command window doesn't do it, you'll need to speak to Allround Automations.

To Kunlun

July 06, 2016 - 1:12 pm UTC

Reviewer: Rajeshwaran, Jeyabal

I Hope this should be a question for Allround automations team, since they made this tool.

http://forums.allroundautomations.com/ubb//ubbthreads.php?ubb=showflat&Number=53680&#Post53680

Added your questions to their support forum, please watch out the above link for their response.
Chris Saxon

Followup  

July 06, 2016 - 1:23 pm UTC

Thanks Rajesh

July 06, 2016 - 1:36 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

Yes : searching in the right place (ie, http://forums.allroundautomations.com/list.html : obvious, Allround is the owner of the product PL/SQL Developer, it is not an Oracle product) I found topics suggesting that the product supports the SET SERVEROUTPUT command and it could be put on a file called LOGIN.SQL to be executed automatically , such as :

"Re: Serveroutput - Command Window

Robert,

add following into Login.sql located in PL/SQL Developer directory

Code:

-- Autostart Command Window script
set serveroutput on size 999999

"

If it works with SERVEROUTPUT ON, much probably must work with OFF, too... I don´t use this, so I can´t test but appear to be the answer...

Regards,

J. Laurindo Chiappa
Chris Saxon

Followup  

July 06, 2016 - 3:17 pm UTC

Thanks for digging that out :)

Still facing error

February 22, 2018 - 12:52 pm UTC

Reviewer: Suman Panigrahi from India

I set the serveroutput off. I am still getting the same error.
Chris Saxon

Followup  

February 22, 2018 - 4:47 pm UTC

What exactly did you do? Show us all the code you ran!

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here