Wednesday, January 22, 2014

Execution Plan of a Query in Oracle

Working with Oracle in Unix and getting Execution Plan of a Query:

1) Login to Unix Machine where Oracle is installed.

2) You need to check whether Oracle is running (Listener service is running). To check this type below command:
        oracle@companyName:~$ lsnrctl status (press ENTER)
   This will give around 30-Lines output, at the end, it will show whether this running/ready or not. 
   However if it is not running, start it using below command:         
        oracle@companyName:~$ lsnrctl start (press ENTER)

3) Type below command in Unix (this will set Oracle_Home path and export it):  
        oracle@companyName:~$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 (press ENTER)
        oracle@companyName:~$ export ORACLE_SID=orcl (press ENTER)
        oracle@companyName:~$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME:$PATH:. (press ENTER)

4) Now you need to login to Database, so type below command: 
        oracle@companyName:~$ sqlplus (press ENTER)
            Enter user-name: YOUR_ORACLE_USER_NAME  (press ENTER)
            Enter password:  YOUR_ORACLE_PASSWORD (press ENTER)
        SQL> (You are logged in now...)

/*
For Logging as SYS DBA, required only for DBA activities, don't run oftenly:        
        oracle@companyName:~$ sqlplus / as sysdba
        SQL>
        SQL> exit
*/

5) Set Linesize and Pagesize as Unix(Oracle) will show fragmented pages. Type below commands:
        SQL> set linesize 300;
        SQL> set pagesize 2000;

6) Now to print Execution Plan of a Query to know CPU Utilization, IO Cost, Bytes Read, Index Scan etc, use below queries:
        SQL> SET autotrace ON;
        SQL> SELECT /*+ index(TABLE_NAME INDEX_TIME_STAMP) */  * FROM TABLE_NAME WHERE 
             TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
             ORDER BY CARD_NO ASC;

             Your output will look like below:    
ROW DATA 1........             
ROW DATA 2........
ROW DATA 3........
3 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1246977483

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |    18 |  4122 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY               |                           |    18 |  4122 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE_NAME            |    18 |  4122 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | INDEX_TIME_STAMP |    18 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   3 - access("TIME_STAMP">=TIMESTAMP' 2014-01-10 00:04:20' AND "TIME_STAMP"<=TIMESTAMP'
              2014-01-15 17:04:20')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       4397  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> exit

7) Now the same above query output, can be achieved in ORACLE related UI interfaces like below 
    (above all execution happened in UNIX Black color command line interface), execution below 2 queries in UI interface like SQL Tools:

    explain plan for
    SELECT /*+ index(TABLE_NAME INDEX_TIME_STAMP) */  * FROM TABLE_NAME WHERE 
    TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
    ORDER BY CARD_NO ASC;

    SELECT PLAN_ID,CPU_COST,IO_COST,TIME,BYTES,COST,DEPTH,OPTIMIZER,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,OPTIONS,OPERATION,
    TIMESTAMP FROM PLAN_TABLE;
    
==========================================END==============================================    

No comments:

Post a Comment