Hi, We are using version 11.2.0.4 of Oracle Exadata. We had one change(addition of two new columns) went into production after which the user complained the underlying query which used to finish in milli seconds is now taking ~10-15 seconds to finish for few of the execution and sometimes even more. When i check the plan it remained same for all the executions, but i see from DBA_HISt_ACTIVE_SESS_HISTORY the execution which took longer were spending time mostly on line-4 of the below execution path i.e. "TABLE ACCESS STORAGE FULL" and the event was showing "cell single block physical read" against this operation in ASH. Now going through the table details i see this table was holding ~255 columns but now after addition of the two columns its ~257 columns now. It seems someway related to the addition of new columns but am not able to relate these fully. I have executed the query manually and took the sql monitor as below and noticed one of the session level stats "table fetch continued row" which was showing as "1230145" after one time query execution for that session. Also noticed when it takes longer the "redo size" becomes higher in the auto trace output.
1)Why are we seeing maximum amount of "cell single block physical read" against the "TABLE ACCESS STORAGE FULL" whenever the query runs longer?
2)The table holds ~6million rows in total. I do see 98% cell offload in the sql monitor. I am trying to understand the cause of this issue. If the row chaining is causing the issue because of the ~257 columns, why? And what can be done to fix this issue and make this query finish in same time as before i.e. few milli seconds?
3)Is this advisable to pin this table to flash cache like e.g. alter table tab1 storage (cell_flash_cache keep)?
Update- But yes , we are not reading those new columns in the query anyway.
SELECT DISTINCT NAME
FROM TAB1 TAB1
WHERE TAB1.INDICATOR IS NOT NULL
AND TAB1.STATUS NOT IN ( 'Z', 'Y')
AND CODE = 'XX'
AND UPPER (TAB1.NAME) LIKE '%ABCD%'
and rownum<10
ORDER BY UPPER (TAB1.NAME);
table stats:-
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN CHAIN_CNT FLASH_CACHE CELL_FLASH_CACHE
TAB1 6009260 1325454 1391 0 DEFAULT DEFAULT
table fetch continued row - 1230145
SQL Monitoring Report
SQL Text
------------------------------
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 7q6y85cyzv9yk
SQL Execution ID : 33554432
Execution Started : 03/25/2019 15:07:12
First Refresh Time : 03/25/2019 15:07:12
Last Refresh Time : 03/25/2019 15:07:20
Duration : 8s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
=========================================================================================
| 8.40 | 5.57 | 2.83 | 0.00 | 2 | 3M | 21709 | 10GB | 98.59% |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3225255865)
==========================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
==========================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +8 | 1 | 3 | | | | | | |
| 1 | SORT ORDER BY | | 9 | 361K | 1 | +8 | 1 | 3 | | | | 2048 | | |
| 2 | HASH UNIQUE | | 9 | 361K | 7 | +2 | 1 | 3 | | | | 722K | | |
| 3 | COUNT STOPKEY | | | | 3 | +2 | 1 | 4 | | | | | | |
| 4 | TABLE ACCESS STORAGE FULL | TAB1 | 11368 | 361K | 8 | +1 | 1 | 4 | 21709 | 10GB | 98.59% | 7M | 100.00 | Cpu (4) |
| | | | | | | | | | | | | | | cell single block physical read (4) |
==========================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<10)
4 - storage("TAB1"."INDICATOR" IS NOT NULL AND "CODE"='XX' AND
UPPER("TAB1"."NAME") LIKE '%ABCD%' AND "TAB1"."STATUS"<>'Z' AND "TAB1"."STATUS"<>'Y')
filter("TAB1"."INDICATOR" IS NOT NULL AND "CODE"='XX' AND
UPPER("TAB1"."NAME") LIKE '%ABCD%' AND "TAB1"."STATUS"<>'Z' AND "TAB1"."STATUS"<>'Y')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2798272 consistent gets
1336006 physical reads
590620 redo size
427 bytes sent via SQL*Net to client
472 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed