Hi, We are using version 11.2.0.4 of oracle Exadata. We get hit by below bug in which cell offloading was not happening in DML(INSERT.. INTO TAB1.. SELECT...) , so we had set the session level parameter _serial_direct_read to 'ALWAYS' in the code. But as we had multiple DMLS getting impacted by this, we had tried setting the same session level parameter at the start of the package/procedure code which is getting called ~1500 times/day from different sessions ( it getting called concurrently also).
What we noticed is, during the actual run , one of the query which used to finish in <1minutes was running for hours, now when we checked it was all waiting on event "enq: KO - fast object checkpoint". I have posted just the specific section of the sql monitor as below. The sql plan has not been changed from past, but this time the "INDEX STORAGE FAST FULL SCAN" in a nested loop suffered badly as it was doing it 200k+ times, at line no-25 below.
We had talk to Support before setting this underscore parameter , it can be used as an workaround to get benefit of cell smart scan in case of DMLS. But they also , mentioned if any frequent DML is happening(say OLTP kind of app) and that table is getting read as direct path forcibly many times, that can affect negatively, as because each smartscan will try to force the checkpoint and have the DB writer to flush the dirty blocks from buffer cache to disk. But in this scenario the index which was suffering was belongs to a table which holds total ~70 rows and is a reference data table so not getting changed frequently even its getting accessed heavily from multiple sessions.
So , why the "fast object checkpoint" is affecting this query/index scan(which is part of a very small table having least DML acivity on it)?
Bug 13250070 - Enh: Serial direct reads not working in DML (Doc ID 13250070.8)
Below is the part of the sql monitor for the big query in which the INDEX FAST FULL SCAN showing ~4783 sample for "enq: KO - fast object checkpoint":
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload |
======================================================================================================
| 10741 | 807 | 1686 | 4795 | 0.08 | 3453 | 912K | 273K | 2GB | 94.64% |
======================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3918063749)
========================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | | (%) | (# samples) |
========================================================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | | | 1 | | | | | | | |
| -> 1 | LOAD TABLE CONVENTIONAL | | | | 10729 | +16 | 1 | 0 | | | | | 0.03 | Cpu (3) |
***************
*************
| -> 22 | INDEX UNIQUE SCAN | TMRS_PK | 1 | | 10729 | +16 | 82371 | 82370 | | | | | | |
| -> 23 | TABLE ACCESS BY INDEX ROWID | TMRSC | 2 | 1 | 10729 | +16 | 82371 | 268K | | | | | | |
| -> 24 | INDEX RANGE SCAN | TMRSC_IX2 | 3 | | 10729 | +16 | 82371 | 268K | | | | | | |
| -> 25 | INDEX STORAGE FAST FULL SCAN | TMRC_IX1 | 1 | | 10745 | +0 | 268K | 112K | 267K | 2GB | 96.53% | 1M | 99.74 | enq: KO - fast object checkpoint (4783) |
| | | | | | | | | | | | | | | Cpu (753) |
| | | | | | | | | | | | | | | gcs drm freeze in enter server mode (25) |
| | | | | | | | | | | | | | | latch free (1) |
| | | | | | | | | | | | | | | reliable message (3505) |
| | | | | | | | | | | | | | | cell smart index scan (1635) |
| -> 26 | INDEX RANGE SCAN | TMMC_IX1 | 1 | | 10729 | +16 | 112K | 112K | | | | | 0.01 | Cpu (1) |
| -> 27 | TABLE ACCESS BY INDEX ROWID | TMMC | 1 | 1 | 10729 | +16 | 112K | 112K | | | | | | |
========================================================================================================================================================================================================================================