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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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: