We are using version 11.2.0.4 of oracle Exadata. We have one UPDATE query which is executing Avg~11million times each day and in each execution it UPDATES ~4rows and taking Avg ~3micro seconds/execution as i see from dba_hist_sqlstat. So each day it Updates ~44million rows. I want to know if there is any possible strategy/way to further improve the query performance?
Below is the explain plan for the sql query. As i was not able to capture the sql monitor because of very fast execution, so i have executed the SELECT part of it and below is the sql monitor for the SELECT query. Table TAB1 is range partitioned on column RSKEY and its having one local prefixed index in it on column (RSKEY,CUKEY). The specific partition which is queried contains ~40million records in it and the filter on (RSKEY,CUKEY) itself minize the set of record to 20 as evident from the sql monitor. and then rest "16" rows has been filtered by column "MKEY" so the final result comes out as ~4 rows. Initially i was under assumption it must be the UPDATE itself which is taking time but as i see from the DBA_HIST_ACTIVE_SESS_HISTORY , ~70% of the execution time is spent on plan_line_id-3.
1)I was thinking at this scenario, if its better idea to include column MKEY to the existing index, but again that may deteriorate the time of UPDATE itself as because the index will be bulkier then. Is my understanding is correct?
2)Is it good idea to modify the local prefix index to non prefix index(INDEX only on column CUKEY), but then the index will be less bulkier and also it will partition pruned as because we have filter on partition key, but it may not give much benefit as because most of the time is spend on line-3 "TABLE ACCESS BY LOCAL INDEX ROWID", is my understanding correct here? or is there any other option i should consider in this situation to further improve the query performance?
INDEX_NAME PARTITION_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
TAB1_IX1 TAB1_PART1 3 157300 1467773 17471800 46432300 464323
UPDATE TAB1
SET C1 = :1, C2 = :2, C3 = :3,C4 = :4,C5 = :5, C6 = :6,C7 = :7,C8 = :8
WHERE EKEY = :9
AND MKEY = :10
AND TMADKEY = :11
AND CKEY = :12
AND RSKEY = :13
AND CUKEY = :14;
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 22 (100)| | | |
| 1 | UPDATE | TAB1 | | | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 78 | 22 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 1 | 78 | 22 (0)| 00:00:01 | KEY | KEY |
|* 4 | INDEX RANGE SCAN | TAB1_IX1 | 35 | | 4 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("TMADKEY"=:11 AND "MKEY"=:10 AND "EKEY"=:9 AND "CKEY"=:12))
4 - access("RSKEY"=:13 AND "CUKEY"=:14)
SQL Text
------------------------------
select C1 , C2 , C3, C4, C5, C6, C7,C8 from TAB1
WHERE EKEY = :b9 AND MKEY = :b10 AND TMADKEY = :b11 AND CKEY = :b12 AND RSKEY = :b13 AND CUKEY = :b14
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : akt9t7narj435
SQL Execution ID : 33554432
Execution Started : 09/24/2018 09:36:04
First Refresh Time : 09/24/2018 09:36:04
Last Refresh Time : 09/24/2018 09:36:05
Duration : 1s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
====================================================
| Elapsed | Cpu | Concurrency | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
====================================================
| 0.00 | 0.00 | 0.00 | 2 | 15 |
====================================================
SQL Plan Monitoring Details (Plan Hash Value=3126256143)
=================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2 | +0 | 1 | 4 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 22 | 2 | +0 | 1 | 4 | | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB1 | 1 | 22 | 2 | +0 | 1 | 4 | | |
| 3 | INDEX RANGE SCAN | TAB1_IX1 | 35 | 4 | 2 | +0 | 1 | 20 | | |
=================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 3126256143
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 73 | 7 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 1 | 73 | 7 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | TAB1_IX1 | 2 | | 4 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TMADKEY"=TO_NUMBER(:B11) AND "MKEY"=TO_NUMBER(:B10) AND "EKEY"=TO_NUMBER(:B9) AND
"CKEY"=TO_NUMBER(:B12))
3 - access("RSKEY"=TO_NUMBER(:B13) AND "CUKEY"=TO_NUMBER(:B14))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
943 bytes sent via SQL*Net to client
472 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed