Re: Reads from Control file under "IOStat by Filetype summary"

Thanks a lot for taking out time and looking into this.

The request to group was to get help / understand, what could cause high reads from control file (31.9G). For the same duration reads from data file was 71.7G.

Why this request to the group and not in-house team. I received AWR report from a team as they were facing issues with a particular process taking longer than expected.

I thought to do my findings before going to the production support DBA team, so that I could go with pointed request / questions. The pool of DBA would not know application specifics.Since I had not seen any AWR report with this kind of reads from control file, I decided to seek help from experts here.

As Timur suggested I had raised request with the DBA team to share / find queries which had "control file sequential read" wait event during this period and if any monitoring queries were being executed.

I am still waiting for them to revert.

Thanks again to all for the help.

Warm Regards,
Vijay Sehgal.

On Thu, Jun 5, 2014 at 4:49 PM, Niall Litchfield <niall.litchfield_at_gmail.com
> wrote:

> 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_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.
>>
>> 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
>

--
http://www.freelists.org/webpage/oracle-l