EXPLAIN PLAN и AUTOTRACE

EXPLAIN PLAN Usage
DBMS_XPLAN
Очень полезный скрипт explain.sql от товарисчей с oracle-base

cd $oracle_home/rdbms/admin
log into sqlplus as system
run SQL> @utlxplan
run SQL> create public synonym plan_table for plan_table
run SQL> grant all on plan_table to public
exit sqlplus and cd $oracle_home/sqlplus/admin
log into sqlplus as SYS
run SQL> @plustrce
run SQL> grant plustrace to public

Для включения AUTOTRACE (роли plustrace):

To use the AUTOTRACE feature of SQL*Plus, the user must have a PLAN_TABLE in the schema and have PLUSTRACE role granted.

AUTOTRACE configuration is required. Perform the following steps to configure the user for AUTOTRACE:
1. connect sys/change_on_install @$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to username;
2. connect username/password @$ORACLE_HOME/rdbms/admin/utlxplan.sql
set autotrace (run sql statement)