Oracle Tuning Tools
EXPLAIN PLAN
-
Create the PLAN_TABLE
SQL> $ORACLE_HOME/rdbms/admin/utlxplan.sql -
Run EXPLAIN PLAN for an SQL
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = ‘example’ FOR
3 SELECT ename,dname
4 FROM emp inner join dept
5 ON ( emp.deptno = dept.deptno )
6 /
Explained. -
DISPLAY Results
SQL> $ORACLE_HOME/rdbms/admin/utlxpls.sql
AUTOTRACE is a SQL*Plus facility that may be enabled in your database. To get this up and running you need to have administration rights to the system and perform the following steps:
* Log into SQL*Plus as SYSDBA
-
Run the script $ORACLE_HOME/sqlplus/admin/plustrce
-
Grant PLUSTRACE to SPATIAL (or to specific users/roles)
To Use:
SQL> SET autotrace ON SQL> SELECT ename,dname 2 FROM emp INNER JOIN dept 3 ON ( emp.deptno = dept.deptno ) 4 / ENAME DNAME ---------- -------------- SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 ROWS selected. Execution Plan ---------------------------------------------------------- SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=252) 1 HASH JOIN (Cost=3 Card=14 Bytes=252) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=44) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=98) Statistics ---------------------------------------------------------- recursive calls 4 db block gets 3 consistent gets physical reads redo SIZE 1132 bytes sent via SQL*Net TO client 503 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client sorts (memory) sorts (disk) 14 ROWS processed |
Courtesy of Beginning Oracle Programming by Sean Dillon et al
