Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Quick Generation of Execution Plan for a SQL

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Quick Generation of Execution Plan for a SQL

    -- 1 ) Make Sure that a PLAN_TABLE has been created

    @$ORACLE_HOME/rdbms/admin/utlxplan.sql

    Table created.


    -- 2) Generate the execution Plan for the SQL
    -- eg : If the SQL is : SELECT last_name FROM employees;

    EXPLAIN PLAN FOR
    SELECT * FROM scott.emp;

    -- 3) Display Execution Plan


    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    An Example has been given below :


    SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

    Table created.

    SQL> SQL> EXPLAIN PLAN FOR
    SELECT * FROM scott.emp;
    2
    Explained.

    SQL> SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);
    SQL> SQL>
    --------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    --------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | |
    | 1 | TABLE ACCESS FULL | EMP | | | |
    --------------------------------------------------------------------

    Note: rule based optimization

    9 rows selected.

    SQL>

+ Reply to Thread

Similar Threads

  1. Use EXPLAIN PLAN and TKPROF To Tune Your Applications
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 04-25-2012, 03:15 PM
  2. Replies: 0
    Last Post: 01-02-2012, 12:46 AM
  3. ASM Instance Init Parameters - Quick View
    By Hemant in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 11-21-2011, 03:54 PM
  4. How to analyze redo logs generation?
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 2
    Last Post: 11-20-2011, 09:23 AM
  5. Script to analyze redo logs generation
    By Amit in forum Database Scripts
    Replies: 2
    Last Post: 11-20-2011, 09:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics