Hi, We are using version 11.2.0.4 of Oracle Exadata. We see one top -N query , giving variable performance, during the slow period i see the ASH showing "cell multiblock physical read" but during fast execution period the ASH shows event as "cell smart table scan". I got the sql monitor for the period when it was running slow , its as below. So we are struggling to understand what exact stats is influencing it to switch between "cell multiblock physical read" and "cell smart table scan" during run time between multiple runs. Can it be table statistics anyway?
When i manually ran the query by setting "set autotrace traceonly explain statistics" i saw "TABLE ACCESS STORAGE FULL FIRST ROWS" instead of "TABLE ACCESS STORAGE FULL". Not sure if this anyway related. And also is it possible that by deault Oracle is switching between "first_row" vs "all_rows" and so causing this issue , but then i should have seen the different plan_hash_value in ASH which i am not seeing. So i am thinking its always going for " TABLE ACCESS STORAGE FULL" during run time, but somehow the "cell smart table scan" not triggered for few of the run causing it to suffer. So want to understand the cause of it?
SELECT NVL(C2_ID,0) FROM TAB1 WHERE C1_ID = :B2 AND PARTITION_DT = :B1 AND ROWNUM < 2
Global Stats
=========================================================
| Elapsed | Cpu | IO | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
=========================================================
| 42 | 17 | 25 | 2M | 17262 | 17GB |
=========================================================
SQL Plan Monitoring Details (Plan Hash Value=2276381224)
============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | |
| 1 | COUNT STOPKEY | | | | | | 1 | | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 2 | | | 1 | | | | | |
| -> 3 | TABLE ACCESS STORAGE FULL | TAB1 | 1 | 2 | 43 | +2 | 1 | 0 | 16694 | 16GB | 97.73 | Cpu (22) |
| | | | | | | | | | | | | cell multiblock physical read (21) |
============================================================================================================================================================================================
*********Manually running the query by setting "set autotrace traceonly explain statistics"
Execution Plan
----------------------------------------------------------
Plan hash value: 2276381224
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 55 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE SINGLE | | 2 | 56 | 55 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS STORAGE FULL FIRST ROWS| TAB1 | 2 | 56 | 55 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - storage("C1_ID"=:B2 AND "PARTITION_DT"=TO_DATE(:B1,'DD-MON-YYYY'))
filter("C1_ID"=:B2 AND "PARTITION_DT"=TO_DATE(:B1,'DD-MON-YYYY'))