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.
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
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 🙂