Database Tuning (MOSC)

MOSC Banner

A Script I Use That Lets Me See Execution Plan for Whatever is in sqlplus Buffer Without Losing Cont

User502553-Oracle
User502553-Oracle Posts: 21 Blue Ribbon
edited Jan 29, 2009 8:53PM in Database Tuning (MOSC) 2 comments

Comments

  • Why not use dbms_xplan.display in 9 and 10g ?

    SQL> set pages600
    SQL> set linesize132
    SQL> desc my_emp_table;
     Name                                                                     Null?    Type
     ------------------------------------------------------------------------ -------- -------------------------------------------------
     EMPLID                                                                   NOT NULL NUMBER
     EMPNAME                                                                           VARCHAR2(20)
     GENDER                                                                            VARCHAR2(2)

    SQL> select empname, gender from my_emp_table where emplid=3;

    EMPNAME              GE
    -------------------- --
    Larry Ellison        M

    SQL> explain plan for select empname, gender from my_emp_table where emplid=3;

    Explained.

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3612125637

    ----------------------------------------------------------------------------------
    | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |              |     1 |    19 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| MY_EMP_TABLE |     1 |    19 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       1 - filter("EMPLID"=3)

    13 rows selected.

    SQL>

  • Nip-Oracle
    Nip-Oracle Posts: 757 Gold Badge
    Thanks for sharing 'one another' approach.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center