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

Thanks a lot for your inputs.

1. 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"
2. As Jonathan hinted, the "archive_lag_target" is set to 5 minutes.
3. 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.
>>
>> 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
>>
>
>
> --
> Regards
> Timur Akhmadeev
>
>
>

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