explain plan not working - 03/10/14 09:16 PM
Hi,
I want to display the explain plan for a given select statement in Command Window. I tried:
[quote]Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as system@orcl
SQL> explain plan for select * from hr.employees;
Explained
SQL>[/quote]
so.. where is the plan?
Also I tried with:
SQL> select * from hr.employees;
and then
SQL> select * from table (dbms_xplan.display_cursor);
But I got:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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 want to display the explain plans in Command window because it's well formatted and I can copy/paste them into OTN forums, for example.
My version of software is 10.0.4.1708.
Thank you.
I want to display the explain plan for a given select statement in Command Window. I tried:
[quote]Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as system@orcl
SQL> explain plan for select * from hr.employees;
Explained
SQL>[/quote]
so.. where is the plan?
Also I tried with:
SQL> select * from hr.employees;
and then
SQL> select * from table (dbms_xplan.display_cursor);
But I got:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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 want to display the explain plans in Command window because it's well formatted and I can copy/paste them into OTN forums, for example.
My version of software is 10.0.4.1708.
Thank you.