Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, August 15, 2015

Oracle explain Plan & auto trace usage

Explain Plan:


Create PLUSTRACE role:

SQL> show user
USER is "SYS"

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
         *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off

SQL> grant plustrace to hr;

Grant succeeded.


SQL> show user
USER is "HR"




SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';

EMPLOYEE_ID FIRST_NAME        LAST_NAME
----------- -------------------- -------------------------
   144 Peter        Vargas
   150 Peter        Tucker
   152 Peter        Hall

3 rows selected.

SQL> explain plan for
         select employee_id,first_name,last_name from employees where first_name ='Peter';

Explained.

SQL> @?/rdbms/admin/utlxpls.sql
SQL> Rem
SQL> Rem $Header: utlxpls.sql 26-feb-2002.19:49:37 bdagevil Exp $
SQL>-------
SQL> Rem
SQL> Rem Use the display table function from the dbms_xplan package to display the last
SQL> Rem explain plan. Force serial option for backward compatibility
SQL> Rem

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3375806929

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    3 |    57 |    3  (34)| 00:00:01 |
|   1 |  VIEW              | index$_join$_001 |    3 |    57 |    3  (34)| 00:00:01 |
|*  2 |   HASH JOIN           |         |      |      |           |      |
|*  3 |    INDEX FAST FULL SCAN| EMP_NAME_IX      |    3 |    57 |    1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK      |    3 |    57 |    1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

  2 - access(ROWID=ROWID)
  3 - filter("FIRST_NAME"='Peter')

17 rows selected.



Auto trace options:

SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Explain plan with Trace & Statistics:


SQL> set autot on

select employee_id,first_name,last_name from employees where first_name ='Peter';

EMPLOYEE_ID FIRST_NAME        LAST_NAME
----------- -------------------- -------------------------
   144 Peter        Vargas
   150 Peter        Tucker
   152 Peter        Hall


Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    3 |    57 |    3  (34)| 00:00:01 |
|   1 |  VIEW              | index$_join$_001 |    3 |    57 |    3  (34)| 00:00:01 |
|*  2 |   HASH JOIN           |         |      |      |           |      |
|*  3 |    INDEX FAST FULL SCAN| EMP_NAME_IX      |    3 |    57 |    1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK      |    3 |    57 |    1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

  2 - access(ROWID=ROWID)
  3 - filter("FIRST_NAME"='Peter')


Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
     9  consistent gets
     0  physical reads
     0  redo size
   768  bytes sent via SQL*Net to client
   523  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     3  rows processed

Only explain Plan:

SQL> set autot on exp
SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';

EMPLOYEE_ID FIRST_NAME        LAST_NAME
----------- -------------------- -------------------------
   144 Peter        Vargas
   150 Peter        Tucker
   152 Peter        Hall


Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    3 |    57 |    3  (34)| 00:00:01 |
|   1 |  VIEW              | index$_join$_001 |    3 |    57 |    3  (34)| 00:00:01 |
|*  2 |   HASH JOIN           |         |      |      |           |      |
|*  3 |    INDEX FAST FULL SCAN| EMP_NAME_IX      |    3 |    57 |    1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK      |    3 |    57 |    1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

  2 - access(ROWID=ROWID)
  3 - filter("FIRST_NAME"='Peter')



Only Statistics:


SQL> set autot on stat
SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';

EMPLOYEE_ID FIRST_NAME        LAST_NAME
----------- -------------------- -------------------------
   144 Peter        Vargas
   150 Peter        Tucker
   152 Peter        Hall


Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
     9  consistent gets
     0  physical reads
     0  redo size
   768  bytes sent via SQL*Net to client
   523  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     3  rows processed

No comments: