Explain Plan Options


explain plan for 

select * 

From Customers 

Where cust_city='Los Angeles' 

And  cust_state_province='CA'; 



select * from table(dbms_xplan.display()); 



SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&1'));


   	 SET LONG 10000 

     SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'&plan_name'));

Autotrace Setting Result

SET AUTOTRACE OFF No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle9i SQL Reference. For more information about the roles and the PLAN_TABLE, see the Oracle9i SQL Reference and the AUTOTRACE variable of the SET command in SQL*Plus User's Guide and Reference.

HOME