 |
 |
****************************************************************
* 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
Your Comments