Re: Reads from Control file under "IOStat by Filetype summary"
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: vijaysehgal21@xxxxxxxxx
- Date: Thu, 5 Jun 2014 12:19:48 +0100
Vijay
What lies behind your request to this group? Are you trying to understand
specific wait events, do you have a business problem you are investigating
or are you looking for help understanding AWR reports in general and not so
much this one in particular?
As John says, you evidently have access to an in-house team who ought to be
able to help you in the first instance with the second of those business
cases, and in any case a 3 hour AWR report is unlikely to be the best tool
for starting diagnostics. In the first instance Timur's given a neat query
to dig further into what sql might be causing the values you see in your
report - you might need to dig into dba_hist_active_sess_history rather
than v$ash for historical querying though.
On Thu, May 29, 2014 at 11:00 AM, vijay sehgal <vijaysehgal21@xxxxxxxxx>
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.
>
> 1. 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).
>
> 2. 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.
>
> 3. Would Direct Path Reads be causing this? if yes how do I dig more
> information to correlate these.
>
> 4. 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
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
