Thanks for the question, kunlun.

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

Last updated: March 01, 2024 - 1:53 am UTC

Version: 11gR2

Viewed 10K+ times! This question is

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 Chris 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/

Rating

(6 ratings)

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

Comments

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

kunlun li, July 06, 2016 - 11:43 am UTC

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?

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

Rajeshwaran, Jeyabal, July 06, 2016 - 1:12 pm UTC

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.

July 06, 2016 - 1:23 pm UTC

Thanks Rajesh

J. Laurindo Chiappa, July 06, 2016 - 1:36 pm UTC

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

July 06, 2016 - 3:17 pm UTC

Thanks for digging that out :)

Still facing error

Suman Panigrahi, February 22, 2018 - 12:52 pm UTC

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

February 22, 2018 - 4:47 pm UTC

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

Problem when trying to get actual Explain Plan

Matt, January 09, 2022 - 7:46 pm UTC

I use Oracle 19c7 Standard with SQL Developer 21.4.1.
In my case such an error happens when extra blank lines are in the query that actual explain plan I need to get. However SQL Develper executes the query with or without those blank lines in opposite to sqlplus.

When I execute query as follows:

SELECT * FROM (
SELECT place, division, someid
FROM places
    )
WHERE someid > 10;

Then I can get its actual explain plan with query generated by SQL Developer ('Explain plan ...' -> 'DBMS_XPLAN').

But if I change the query to:

SELECT * FROM (
SELECT place, division, someid
FROM places
    )
                                                        <-- blank line here
WHERE someid > 10;

and execute query generated by SQL Developer to get explain plan:

select * from table(dbms_xplan.display_cursor(sql_id=>'86e0s2dg4bzt1', format=>'ALLSTATS LAST'));

then it returns an error:
SQL_ID: 86e0s2dg4bzt1, child number: 0 cannot be found

January 11, 2022 - 3:16 am UTC

I've logged bug 33741583 for review from the SQL Dev team

Not able to see results of dbms_xplan.display_cursor

shivani, February 29, 2024 - 1:20 pm UTC

I am running below quries and not able to see output of dbms_xplan.display_cursor as getting below error:

set SERVEROUTPUT off;

explain plan for
SELECT /*+ gather_plan_statistics */ e.employee_id,e.last_name,d.department_id,d.department_name
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id
WHERE d.department_name LIKE 'A%';

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

SQL_ID 54aw8u0rj4u2v, child number 2

explain plan for SELECT /*+ gather_plan_statistics */
e.employee_id,e.last_name,d.department_id,d.department_name FROM
hr.employees e JOIN hr.departments d ON e.department_id =
d.department_id WHERE d.department_name LIKE 'A%'

PLAN_TABLE_OUTPUT
NOTE: cannot fetch plan for SQL_ID: 54aw8u0rj4u2v, 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_plan)

March 01, 2024 - 1:53 am UTC

You need to *run* the SQL not explain it, ie

SELECT /*+ gather_plan_statistics */ e.employee_id,e.last_name,d.department_id,d.department_name
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id
WHERE d.department_name LIKE 'A%';

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

Hope that helps

Breadcrumb

Question and Answer