Facing error “cannot fetch plan for SQL_ID” while trying to capture the execution plan

All we need is an easy explanation of the problem, so here it is.

I’m trying to capture the execution plan of my query on PL/SQL developer as you can see below :

select  *
  from vmi_dimcustomer t1
 inner join vmi_factcustomer t2
    on t1.customer_num = t2.customer_num  ;
    
Select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic'));

but I receive this note :

  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_plan)

What am I doing wrong here?
I searched here and the answer I got was to "set serveroutput off" . Put I can not do this on PL/SQL developer.

Thanks in advance.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

PL/SQL Developer implicitly executes additional statements in the background.

dbms_xplan.display_cursor(null,null,'basic') returns information from the previously executed statement. It was begin :id := sys.dbms_transaction.local_transaction_id; end;.

After executing your SQL, find it in V$SQL, example:

select sql_id, child_number, sql_text from v$sql
where sql_text like '%inner join vmi_factcustomer t2%';

Once you found your SQL, using information from above:

Select plan_table_output from
table(dbms_xplan.display_cursor(
'sql_id from above',
'child_number from above',
'basic'));

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply