Noticed 2 databases ran on the same disk got different performance on same query in identical plan. Ran AWR and saw big difference in IO stats section. i.e Buffer Cache Reads is in us level but another one is in ms. What could caused this difference if on the same disk, it is /u01 in my case ? thanks.
19c on linux
filesystemio_options none
=>One active database
IOStat by Function summary
'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
ordered by (Data Read + Write) desc
Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Time
Direct Reads 2.9T 845.98 832.589M 8M 0.06 .002M 3.1M 65.22ns
Buffer Cache Reads 26.5G 756.04 7.484M 0M 0.00 0M 2.2M 165.58us
Others 519M 7.27 .143M 263M 1.57 .073M 29.4K 75.63us
=>Another much less active database
IOStat by Function summary
'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Time
Direct Reads 49.2G 14.35 14.154M 0M 0.00 0M 51.1K 1.31us
Others 220M 3.45 .062M 53M 0.95 .015M 14K 24.99us
Buffer Cache Reads 214M 1.05 .06M 0M 0.00 0M 3163 2.56ms
Not much to go on there, but if I had to guess - I'd say CPU.
If a machine is max'd out, then everything (including memory access and IO becomes slower, because you are constantly competing to get a slice of CPU to do your work)