In many cases it is beneficial to see what indexes are being used in a query,
or if it is running a table scan. Here are the commands which will
cause the internal optimization details to display.
-- show query plan
set autotrace on
-- show query plan, without running the SQL!
set autotrace traceonly explain
Alternate Method: Below is sample syntax for explain plan ( getting output from the optimizer )
delete from plan_table
where statement_id = '9999';
commit;
COL operation FORMAT A30
COL options FORMAT A15
COL object_name FORMAT A20
/* ------ Your SQL here ------*/
EXPLAIN PLAN set statement_id = '9999' for
select count(1) from asia_monthly_pricing_data where order_id > 5000
/
/*----------------------------*/
select operation, options, object_name
from plan_table
where statement_id = '9999'
start with id = 0
connect by prior id=parent_id and prior statement_id = statement_id;
exit
/
|
|
