0

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.

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'));
2
  • Thank you very much dear Mr Papp . Can you tell me a little bit about "Child_number"? What does it mean? Sql_id is pretty obvious but I have no idea what "child_number" really means. How does it relate to the sql statement?
    – Pantea
    Jul 16 at 10:45
  • And another important question , what if there are more than one child_number for a single sql_id? what should I do in that case?
    – Pantea
    Jul 16 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.