Oracle Database Discussions

Hi, We are using version 11.2.0.4 of oracle Exadata. It is type X5. Our infrastructure team recently enabled "FlashCache writeback mode" on this. And as per the doc it states, Write-back flash cache significantly improves the write intensive operations because writing to flash cache is faster than writing to Hard disks. Writeback is the default starting with X6 Exadata models and recommended by Oracle for X4 and above.

But suddenly after this implementation, we see few of the small queries which used to finish within seconds(.03 seconds/execution) were having increased execution time to ~6 seconds/execution for next  7-8hours and then getting back to normal then after. And these small queries were affecting because of high number of executions, so with increase in time per execution, the overall job execution time was increasing. After digging more into them i found no change in execution path and also the volume is same, it was mainly "cell single block physical read" which has increased for these queries. Then checking the overall USER I/O(cell single block physical read) trend for the database from DBA_HIST_SYSTEM_EVENT, i see ,it(cell single block physical read) has been significantly increased after the change implemented and its getting back to BAU after around 7-8 hrs+. I was not able to relate the symptom with the enabling of "FlashCache writeback mode". Want to know from experts how this can be logically related with each other?

I have few questions ,

1)Is it possible that during the activity for enabling "writeback flash cache" mode flash cache has flushed out completely and its the cache warmup due to which the "cell single block physical read" took longer initially and after ~8hrs they are coming back to BAU? Is it correct to think it can take ~8hrs for cache warmup? Is there some statistics(may be exadata flash cache related) which i can refer to confirm this thought?

2)Is there somewhere in oem i can see the flash cache metrics to find any matching clue?

Below is sample query with its execution plan and details from dba_hist_sqlstat.

INSERT INTO tab1(c1, c2, c3.....c39)  VALUES ( :1, :2, :3, :4, :5, :6, :7, 
:8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, 
:22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, 
:36, :37, :38, :39)
-------------------------------------------------
| Id  | Operation                | Name | Cost  |
-------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |
|   1 |  LOAD TABLE CONVENTIONAL |      |       |
-------------------------------------------------
Note
-----
   - cpu costing is off (consider enabling it)

Below query used to fetch the query specific stats:- After 28th Oct 4pm the query ran slow till 29th Oct 3AM.

SUM (q.EXECUTIONS_DELTA) executions,

         ROUND (SUM (DISK_READS_delta) / GREATEST (SUM (executions_delta), 1),     1)   pio_per_exec,

         ROUND (SUM (BUFFER_GETS_delta) / GREATEST (SUM (executions_delta), 1),   1) lio_per_exec,

         ROUND (SUM (rows_processed_delta) / GREATEST (SUM (executions_delta), 1), 1) rows_per_exec,

             ROUND (SUM (cpu_time_delta) / GREATEST (SUM (executions_delta), 1),  1) cpu_time_exec,

             ROUND (SUM (ccwait_delta) / GREATEST (SUM (executions_delta), 1),  1) concurrency_per_exec,

             ROUND (SUM (IOWAIT_delta) / GREATEST (SUM (executions_delta), 1),   1) IO_per_exec,

                 ROUND (SUM (ApWAIT_delta) / GREATEST (SUM (executions_delta), 1),   1) apwait_per_exec,

         round ((  SUM (ELAPSED_TIME_delta)/ GREATEST (SUM (executions_delta), 1) / 1000000), 4)  msec_exec

    FROM dba_hist_sqlstat q, dba_hist_snapshot s