OraQA

• Categories

  • Administration
  • APEX (HTMLDB)
  • Concepts
    • Architecture
  • E-Business Suite
  • General
  • Jdeveloper
  • PL/SQL
  • Question
  • Security
  • SQL
  • Tuning
  • XML

Latest Comments

• Laurent Schneider:
  if you like a Base64 format, maybe this… select utl_raw.cast_to_varchar 2(...
  
  
• hsafra:
  You need to give more specs for the question: What letter are acceptable? What letters aren’t? Do you...
  
  
• ragunathansd:
  I am not inserting sequence numbers from database. I need to populate the data in a grid. If a user...
  
  
• gamyers:
  “redo log file gets full” That is the nature of a redo log file. It gets full, switched and...
  
  

• Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

• Links

  • AskTom.oracle.com
  • Oracle doc search
  • Oracle Technology Network
  • OraNA - Oracle News Aggregator

Oracle News

explain plan for select * from dual
/
select * from table (dbms_xplan.display)
/

2 Responses to “How to display the explain plan of a query using dbms_xplan table funtion?”

1. David Aldridge Says:
   January 16th, 2006 at 9:47 pm

   I could see this as a more general point — “How to see the execution plan of a query” followed by a list of this technique, “set autotrace …”, SQL Trace, OEM etc. — but this is a little _too_ specific for my taste

2. stbu Says:
   February 8th, 2006 at 12:32 pm

   A new feature of Oracle 10g Release 2 is that

   set autotrace traceonly explain

   is using DBMS_XPLAN now.

   Quote from http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html

   SQL> set autotrace traceonly explain
   
   SQL> select *
   2  from emp, dept
   3  where emp.deptno = dept.deptno
   4  and emp.job = 'CLERK';
   
   Execution Plan
   - - - - - - - - - - - - - - - - -  - -  - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - -
   
   Plan hash value: 877088642
   
   - - - - - - - - - - - - - - - - -  - -  - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - -
   | Id |   Operation              | Name  | Rows  | Bytes         | Cost (%CPU)           | Time     |
   - - - - - - - - - - - - - - - - -  - -  - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - -
   |  0 |  SELECT STATEMENT        |       |     4 |   468         |     7     (15)        | 00:00:01 |
   |* 1 |  HASH JOIN               |       |     4 |   468         |     7     (15)        | 00:00:01 |
   |* 2 |  TABLE ACCESS FULL       | EMP   |     4 |   348         |     3      (0)        | 00:00:01 |
   |  3 |  TABLE ACCESS FULL       | DEPT  |     4 |   120         |     3      (0)        | 00:00:01 |
   - - - - - - - - - - - - - - - - -  - -  - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - -
   Predicate Information (identified by operation id):
   - - - - - - - - - - - - - - - - -  - -  - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - -
      1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
      2 - filter("EMP"."JOB"='CLERK')
   Note
   - - - - - - - - - - - - - - - - -  - -  - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - -
      - dynamic sampling used for this statement

Leave a Reply

You must be logged in to post a comment.