****************************************************************
* Description: Knowledge of explaining an Oracle execution plan
* Date: 11:07 PM EST, 07/16/2017
****************************************************************



<1> Command - explain plan:
    |
    |__ o. Command is used to display the execution plan of a SQL statement without actually executing it.
    |
    |
    |__ o. SQL> explain plan for
    |           SELECT * FROM RBSAPPCORE.TABLE_NAME 
    |           WHERE PARTITIONID = :B6 AND ((UPPER(:B5 ) = 'DISBURSED' AND COLUMN_NAME_1 = :B4 ) OR (UPPER(:B5 ) != 'DISBURSED' AND COLUMN_NAME_2 = :B4 ))
    |           AND LASTTRANSACTIONID = :B3 AND (COLUMN_NAME_3 = :B2 OR :B2 IS NULL) AND (TAXREF = :B1 OR :B1 IS NULL) ORDER BY COLUMN_NAME_3, TAXREF;
    |
    |
    |__ o. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
	
    
                      PLAN_TABLE_OUTPUT
                      ---------------------------------------------------------------------------------------------------------------------------
                      Plan hash value: 3697501635
                      
                      -------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                      -------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                    |                   |     1 |   558 |    12   (9)| 00:00:01 |       |       |
                      |   1 |  SORT ORDER BY                      |                   |     1 |   558 |    12   (9)| 00:00:01 |       |       |
                      |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_NAME        |     1 |   558 |    11   (0)| 00:00:01 | ROWID | ROWID |
                      |*  3 |    INDEX RANGE SCAN                 | IDX_TABLE_NAME_8  |    45 |       |     4   (0)| 00:00:01 |       |       |
                      -------------------------------------------------------------------------------------------------------------------------
                    
                    
                      PLAN_TABLE_OUTPUT
                      ---------------------------------------------------------------------------------------------------------------------------
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                      2 - filter((:B2 IS NULL OR "COLUMN_NAME_3"=:B2) AND (:B1 IS NULL OR "TAXREF"=:B1) AND ("COLUMN_NAME_2"=:B4 AND
                                 UPPER(:B5)<>'DISBURSED' OR "COLUMN_NAME_1"=:B4 AND UPPER(:B5)='DISBURSED') AND
                                 "PARTITIONID"=TO_NUMBER(:B6))
                      3 - access("LASTTRANSACTIONID"=:B3)
			 
	
	
	
<2> View V$SQL_PLAN - query execution plan with actual running state:
     |
     |__ o. SQL> set linesize 200;
                 set pagesize 200;
                 col object_name format a20;
                 col object_type format a20;
                 select depth, object_name, object_type, operation, CARDINALITY, bytes/1024 as KB, ACCESS_PREDICATES, FILTER_PREDICATES from v$sql_plan where sql_id='7x56nmhc9vrjf';
				 
				 
            
                 DEPTH OBJECT_NAME          OBJECT_TYPE  OPERATION           CARDINALITY      KB         ACCESS_PREDICATES             FILTER_PREDICATES
                ------ -------------------- ------------ ------------------- ----------- ---------- -------------------------------- -----------------------
                     0                                   SELECT STATEMENT                                                           
                     1                                   SORT                          2     1.0625                                 
                     2                                   CONCATENATION                                                              
                     3                                   FILTER                                                                      UPPER(:B5)='DISBURSED'
                     4 SOURCETABLE          TABLE        TABLE ACCESS                  1     .53125                                  ("PARTITIONID"=:B6 AND (:B1 IS
                                                                                                                                      NULL OR "TAXREF"=:B1) AND (:B
                                                                                                                                     2 IS NULL OR "POLICYCOL"=:B2)
                                                                                                                                     AND TO_NUMBER("LASTTRANSACTION
                                                                                                                                     ID")=:B3)
                                                                                                                                     
                     5 IDX_SOURCETABLE_4    INDEX        INDEX                       106                "COLUMN_NAME_2"=:B4         
                     3                                   FILTER                                                                      UPPER(:B5)<>'DISBURSED'
                     4 SOURCETABLE          TABLE        TABLE ACCESS                  1     .53125                                  ("PARTITIONID"=:B6 AND (:B1 IS
                                                                                                                                      NULL OR "TAXREF"=:B1) AND (:B
                                                                                                                                     2 IS NULL OR "POLICYCOL"=:B2)
                                                                                                                                     AND TO_NUMBER("LASTTRANSACTION
                                                                                                                                     ID")=:B3 AND (LNNVL("PAYSHEETD
                                                                                                                                     ISBURSEDREF"=:B4) OR LNNVL(UPP
                                                                                                                                     ER(:B5)='DISBURSED')))
                                                                                                                                    
                     5 IDX_SOURCETABLE_2    INDEX        INDEX                       102                "COLUMN_NAME_3"=:B4           
                                                                            
     
	 
	 
<3> Tunning tips regarding above SQL:
     |
     |__ o. Performance issue got reported by customer. More and more daily transaction got queued up.
         o. Based on AWR report, above query is consuming exetremely high physical reads on disk I/O.	 
         o. Above query is running every million-second by application based on daily business.
         o. According to execution plan, the returned rows are not fetching too much data.
         o. Index may not be used. So, huge size of data blocks got read from disk.
         o. How come index not being used? Execution plan might be changed.
         o. As a solution, we re-gathered the table statistics at the moment. 		 
                                                                            
                                                                            
              
			  
<4> Reference:                                                                            
     |
     |__ o. https://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm#TGSQL279