I/O issue on QA environment - real time resolution

Please find below our findings regarding QA_DB performance issues.

Disk usage from last 31 days is showing us, that starting Monday disk reads significantly growth. You can see, that previously there was no so much reads from disks on the server. 

In alert log I’ve found high number of rows like (hundreds of them):

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2651.582.877530961  

Deleted Oracle managed file +QDB_FR1/qln_dbflashback/log_2650.2190.877530957  

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2649.2988.877530955  

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2648.337.877530953  

Deleted Oracle managed file +QDB_FR1/qln_db/flashback/log_2647.731.877530951  

Oracle started to cleanup space for new flashback logs by deleting old ones. This operation took most 

of I/O bandwidth, and You’ve experienced waits as described.

All those events occurred during the time, when You faced performance issues. All I/O performace issues are related to flashback feature enabled on your database.

Also all of them were observer in same time with big database workload (a lot of sessions, big number executions of some single sqlid’s, etc.)

According to my knowledge, this is how oracle is behaving, it is deleting flash log files, when needed. So in cases, when database is loaded by current ‘heavy’ usage, oracle delete operation can consume additional resources and cause performance issues.

You need to remember, that this is shared cluster, with many databases running. All resources are also shared between those databases.