6

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?

Community
  • 1
  • 1
case nelson
  • 3,537
  • 3
  • 30
  • 37
  • I think this has to do with querying sys.v$sql_plan or sys.v$sql_plan_statistics but it would be nice to get the actual sql to run in order to get at these views properly. – case nelson Jun 18 '13 at 17:32
  • 1
    I haven't used Oracle in years, but have you checked out the GATHER_PLAN_STATISTICS hint? See pp.10-11 here: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf – Denis de Bernardy Jun 18 '13 at 17:45
  • See here on how to get the actual execution plan: https://forums.oracle.com/message/10153875#10153875 to get the same amount of details that Postgres shows, you'd need to trace the Oracle session and look at the output generated by tkprof. –  Jun 18 '13 at 20:32
  • @a_horse_with_no_name So would you run your query then run "select * from table( dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));" to get close? If you want to provide an answer I'll accept it. (I can't verify it myself as it seems I have insufficient privileges) – case nelson Jun 18 '13 at 21:37

1 Answers1

3

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.

evil otto
  • 10,348
  • 25
  • 38
  • 3
    explain and autotrace will not display the actual plan that was used when running the statement (as Postgres does). –  Jun 18 '13 at 18:38
  • how do you get the actual plan used and not the expected plan? this seems equivalent to explain without analyze. I feel the second alternative is close, are there other plan tables you can query other than the default? – case nelson Jun 18 '13 at 18:58
  • There is no such thing as an expected plan that differs from the actual plan in oracle or in postgresql. The statistics might be different, but the plan is what is going to be run. You can show the statistics with autotrace too - `set autotrace on statistics` - but I haven't used that (and I can't provide an example because the DBA hasn't enabled that feature on my db). See the [Oracle Docs](http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch8.htm#sthref1499) for more on autotrace. – evil otto Jun 18 '13 at 19:19
  • 1
    @evilotto: there can be a difference between `explain`(and `autotrace` as that simply does an explain) and the actual plan. See for example here: http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/ and here: http://iggyfernandez.wordpress.com/2011/12/08/the-twelve-days-of-sql-day-7/ and here: http://hoopercharles.wordpress.com/2010/01/11/explain-plan-lies-autotrace-lies-tkprof-lies-what-is-the-plan/ and here: http://kerryosborne.oracle-guy.com/2010/02/autotrace-lies/ –  Jun 18 '13 at 20:31
  • @a_horse_with_no_name: I was not aware of the shared_cursor + bind_peek behavior. But most of those posts are about bind vars, which postgres handles essentially the same for the most common cases. If anything the question should be about understanding the difference in environment between explain and execution time. – evil otto Jun 18 '13 at 22:27
  • The autotrace completely misses what EXPLAIN ANALYZE does. This shows the difference in estimated and actual cost of a query in order to expose invalid statistics. Also, looking at the plan by way of the plan table or autotrace does NOT always match the actual plan used as per V$SQLPLAN by SQL_ID when it is actually running. – Grant Johnson Sep 30 '15 at 14:20