RE: Reads from Control file under "IOStat by Filetype summary"
Date: Thu, 29 May 2014 17:27:06 -0400
Message-ID: <021f01cf7b84$bea297e0$3be7c7a0$_at_rsiz.com>
One more thing: If the control files are left in triplicate in the default location, that may well explain the waits. The size, however, seems a bit bizarre, at least to me.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of vijay sehgal
Sent: Thursday, May 29, 2014 8:46 AM
To: coskan_at_gmail.com
Cc: timur.akhmadeev_at_gmail.com; ORACLE-L
Subject: Re: Reads from Control file under "IOStat by Filetype summary"
Dear All,
Thanks a lot for your inputs.
- I have requested the DBA team to provide me queries that had wait events "control file sequential read", "db file scattered read" and "direct path read"
- As Jonathan hinted, the "archive_lag_target" is set to 5 minutes.
- I have also requested the team to share if any monitoring scripts are being executed against the database.
While I wait for the inputs, I have 1 more question. How to derive balance value for "arhive_lag_target" parameter. For this particular scenario the environment has dataguard configured.
Warm Regards,
Vijay Sehgal.
On Thu, May 29, 2014 at 4:59 PM, <coskan_at_gmail.com> wrote:
Apart from what Timur and Jonathan already said, If you want to guess it can be monitoring script(most probably space checker) with an inefficient plan or unnecessary intervals
From: Timur Akhmadeev
Sent: Thursday, 29 May 2014 14:25
To: vijaysehgal21_at_gmail.com
Reply To: timur.akhmadeev_at_gmail.com
Cc: ORACLE-L
Subject: Re: Reads from Control file under "IOStat by Filetype summary"
Hi Vijay
You may try to find SQL id and SQL plan id from ASH for 'control file sequential read' events and go from there
Something like
select sql_id, sql_plan_line_id, count(*)
from v$ash
where event = ...
Group by ...
It may not give you anything though and then you may need to trace some sessions to find the definitive answer
On Thursday, May 29, 2014, vijay sehgal <vijaysehgal21_at_gmail.com> wrote:
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
--
Regards
Timur Akhmadeev
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 29 2014 - 23:27:06 CEST