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 Answer 1

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'));
3
  • 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, 2021 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, 2021 at 10:48
  • There is always one child with #=0. New childs are created, if the conditions for execution of the current cursor are different. E.g. if the same query is executed with different bind parameters causing another execution plan for the very same sql_id. However, this is not the only reason for multiple childs. In case you see multiple childs, you have to select the one fitting to the execution you want to check and to see the PLAN_HASH. It's better to use named parameters when calling dbms_xplan(). Then you're independent from unvisible calls in background. Feb 3, 2022 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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