Oracle equivalent of Postgres EXPLAIN ANALYZE

Question

Similar to this question.

I'd like to get a detailed query plan and actual execution in Oracle (10g) similar to EXPLAIN ANALYZE in PostgreSQL. Is there an equivalent?

Answer 1

The easiest way is autotrace in sql*plus.

SQL> set autotrace on exp
SQL> select count(*) from users ;

  COUNT(*)
----------
    137553


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=66 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C0062362' (INDEX (UNIQUE)
          ) (Cost=66 Card=137553)

Alternately, oracle does have an explain plan statement, that you can execute and then query the various plan tables. Easiest way is using the DBMS_XPLAN package:

SQL> explain plan for select count(*) from users ;

Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    66 |
|   1 |  SORT AGGREGATE       |              |     1 |       |
|   2 |   INDEX FAST FULL SCAN| SYS_C0062362 |   137K|    66 |
--------------------------------------------------------------

If you're old-school, you can query the plan table yourself:

SQL> explain plan set statement_id = 'my_statement' for select count(*) from users;

Explained.

SQL> column "query plan" format a50
SQL> column object_name format a25
SQL> select lpad(' ',2*(level-1))||operation||' '||options "query plan", object_name
from plan_table
start with id=0 and statement_id = '&statement_id'
connect by prior id=parent_id 
  and prior statement_id=statement_id
Enter value for statement_id: my_statement
old   3: start with id=0 and statement_id = '&statement_id'
new   3: start with id=0 and statement_id = 'my_statement'

SELECT STATEMENT
  SORT AGGREGATE
    INDEX FAST FULL SCAN                           SYS_C0062362

Oracle used to ship with a utility file utlxpls.sql that had a more complete version of that query. Check under $ORACLE_HOME/rdbms/admin.

For any of these methods, your DBA must have set up the appropriate plan tables already.