This is an Oracle database performance issue that appeared in a client of mine.
In the client’s server, there were application flows running successfully everyday in a matter of minutes.
One day, the customer reported that these flows where running for too long and they were stuck, so they were terminated.
The same thing happened the next day too. The flows were stuck again and they could not be completed.
In order to investigate thoroughly the issue, I requested the client to send me the AWR reports of those hours for these 2 days.
When I received them, I started examining all the facts and performance issues I could.
After comparing the AWRs from the two different days, I noticed a pattern in a specific duration of hours:
Period between the time 7-8:
Redo size (bytes): 3,279.1 per second
Logical read (blocks): 101,490.2 per second
Network: 43,076 waits
System I/O: 27,044 waits
Cluster: 7,132 waits
User I/O: 2,877 waits
Period between the time 8-9:
–The DB starts running in parallel: call dbms_stats.gather_database_stats_job_proc () –increasing all I/O, Network and overall performance
Redo size (bytes): 38,475.7 per second
Logical read (blocks): 103,431.1 per second
System I/O: 105,141 waits
Network: 294,394 waits
Cluster: 121,999 waits
User I/O: 174,767 waits
In a matter of hour, suddenly the Redo size, I/O activity and the Network activity, had increased rapidly.
So the next step was to find out what was going on that hour and i check that
the DBMS Scheduler, was running Database statistics in that hour.
-> dbms_stats.gather_database_stats_job_proc ()
Which was causing the performance delays and increasing all I/O, Network etc.
Next thing, the client was informed not to run that time the flows since the statistics were gathered also.
Written by Nikos Minaidis
21/07/2021
Nikos Minaidis 