RE: Reads from Control file under "IOStat by Filetype summary"
Date: Thu, 29 May 2014 11:09:11 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DF6B26_at_exmbx05.thus.corp>
I think your first query should be:
"Please, DBA team, can let give me a short list of the SQL statements, with execution plans, that are responsible for a large fraction of the direct path reads, db file scattered reads, and control file reads."
You shouldn't have to supply them with diagnostic queries - they should (if necessary) be able to write them to match your requirements.
If you've got the whole AWR report you may be able to make a few guesses about the direct path and scattered reads by looking at the "SQL ordered by" and "Segments by" sections of the report. You might also check the archive_lag_target, since you're generating virtually no redo and doing 12 switches per hour it's possible that the parameter has been set to 5 minutes.
Not sure I can give you a confident answer about the control file reads - the volume does seem unusually high for "normal" processing - but Timur's advice about query ASH should help to move you in the right direction.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of vijay sehgal [vijaysehgal21_at_gmail.com] Sent: 29 May 2014 11:00
To: ORACLE-L
Subject: Reads from Control file under "IOStat by Filetype summary"
Dear Experts,
Below are few sections from AWR report. The server is running Linux x86 64 bit, 11.2.0.3.0 with 24 CPUs, 12 Cores and 2 Sockets.Physical Memory is 22GB.
The AWR reports is for 3 hours.
Per Second Per Transaction Per Exec Per Call
----------------------------------------------------------------------------
DB Time(s): 0.2 0.6 0.01 0.08
DB CPU(s): 0.1 0.1 0 0.02
Redo size: 1,371.50 3,402.50
Logical reads: 983.5 2,439.90
Block changes: 4.6 11.3
Physical reads: 869.8 2,157.90
Physical writes: 1.6 4.1
User calls: 2.9 7.2
Parses: 0.7 1.6
Hard parses: 0 0.1
W/A MB processed: 0.4 0.9
Logons: 0 0.1
Executes: 21.4 53.1
Rollbacks: 0 0
Transactions: 0.4
-----------------------------------------------------------------------------------------------------------
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
direct path read 320,496 838 3 33.45 User I/O
db file scattered read 170,986 744 4 29.72 User I/O
DB CPU 581 23.19
control file sequential read 48,036 320 7 12.77 System I/O
db file sequential read 23,789 42 2 1.68 User I/O
-----------------------------------------------------------------------------------------------------------
IOStat by Function summary
Function Name Reads: Reqs per Data per Writes: Reqs per Data per Waits: Avg Tm(ms)
Data sec sec Data sec sec Count
-----------------------------------------------------------------------------------------------------------
Direct Reads 52.3G 40.2 4.95684 0M 0 0M 0
Others 31.7G 8.94 2.99843 620M 3.38 0.057357 109.4K 3.01
Buffer Cache Reads 19.4G 18.8 1.8349 0M 0 0M 202.6K 3.52
LGWR 329M 1.96 0.030436 318M 3.79 0.029419 38.7K 0.56
DBWR 0M 0 0M 133M 1.39 0.012304 0
Direct Writes 0M 0 0M 3M 0.04 0.000277 0
TOTAL: 103.7G 69.91 9.82061 1G 8.59 0.099358 350.7K 3.03
-----------------------------------------------------------------------------------------------------------
IOStat by Filetype summary
Filetype Name Reads: Reqs per Data per Writes: Reqs per Data per Small Read Large Read
Data sec sec Data sec sec
-----------------------------------------------------------------------------------------------------------
Data File 71.7G 59.1 6.79266 138M 1.43 0.012766 3.3 5.27
Control File 31.9G 10.31 3.02369 834M 4.94 0.077155 0.26 9.02
Flashback Log 19M 0.01 0.001757 46M 0.06 0.004255 0.2 40.94
Log File 19M 0.06 0.001757 37M 2.13 0.003422 0.19 27.45
Archive Log 0M 0 0M 18M 0 0.001665
Other 9M 0.42 0.000832 2M 0.02 0.000185 0.11
Temp File 0M 0.01 0M 0M 0 0M 0.75
TOTAL: 103.7G 69.91 9.8207 1G 8.59 0.099451 1.77 5.48
-----------------------------------------------------------------------------------------------------------
I have a few questions with respect to above, if further details are required please revert and I would provide the same.
Log switch during this period was 12 per hour (derived from AWR). I have raised this with the team.
- IOStat by Filetype shows reads from control file as 31.9G. I am not sure why would database be reading 31.9 GB from control file? (There was no backup being performed during this interval).
- How do I investigate this further, I don't have access to production box. I will have to give queries to DBA team to get the details. So any pointed queries, would be helpful.
- Would Direct Path Reads be causing this? if yes how do I dig more information to correlate these.
- I tried to do some search on the wait event "control file sequential read" and found that using Subquery factoring could cause "control file sequential reads". But the issue was fixed in 11.1.7.0.1. Are there any other known issues which could cause this problem.
Your help is much appreciated.
Warm Regards,
Vijay Sehgal
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 29 2014 - 13:09:11 CEST