Hi
We observed a sudden IO spike during a point of day (2:30 PM (highlighted) as per below table )and our server threads dropped down to 0, On analysis it was found that all were stuck while accessing a sproc's query..
I can see the plan hash value has not changed but the average LIO is less. Also per the below data whenever the LIO is less the etime is high.
All these refer to the same query of the sproc but executed using different date values in the where clause.Baselines capture is On but no plan has been made as fixed for these.
Any possible reasons for this or areas we should try and look.
We are using: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
| SNAP_ID | NODE | BEGIN_INTERVAL_TIME | SQL_ID | PLAN_HASH_VALUE | EXECS | AVG_ETIME | AVG_LIO |
| 270586 | 1 | 05/12/2018 1:00:09.817 AM | dxmkxw418kk4y | 3806962829 | 1 | 7.233615 | 4148316 |
| 270586 | 1 | 05/12/2018 1:00:09.817 AM | 25qz4pgyf19mq | 3806962829 | 1 | 7.345023 | 4168025 |
| 270544 | 2 | 04/12/2018 2:30:09.832 PM | avznuk37vd0kf | 3806962829 | 1 | 887.34972 | 1064947 |
| 270339 | 2 | 02/12/2018 11:15:17.375 AM | 41hws91b83pgf | 3806962829 | 1 | 7.334937 | 4144121 |
| 270339 | 2 | 02/12/2018 11:15:17.375 AM | bwx8tduxpv3qa | 3806962829 | 1 | 7.123231 | 4144121 |
| 270218 | 1 | 01/12/2018 5:00:23.993 AM | 0y8db3num2htn | 3806962829 | 1 | 6.793669 | 4143127 |
| 270214 | 1 | 01/12/2018 4:00:18.360 AM | 9g5ws2cjgzyka | 3806962829 | 1 | 349.93583 | 435652 |
| 269643 | 1 | 25/11/2018 5:00:08.501 AM | 962nvfqmbkmdn | 3806962829 | 1 | 7.763583 | 4133820 |
| 269576 | 1 | 24/11/2018 12:15:25.052 PM | 9vysrqgvrsvdf | 3806962829 | 1 | 7.025915 | 4134099 |
| 269563 | 2 | 24/11/2018 9:00:05.176 AM | 5wpfnwh5qjhhj | 3806962829 | 1 | 27.917755 | 102288 |
| 268247 | 1 | 10/11/2018 5:00:07.099 AM | 6x40jggtrfxk0 | 3806962829 | 1 | 375.81507 | 281025 |
| 267974 | 1 | 07/11/2018 8:45:34.319 AM | 1kf07wmyqba7n | 3806962829 | 1 | 694.75385 | 789082 |
Thanks