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

Thanks for looking into this and reverting.

I am still waiting for output of queries from team as per suggestions.

I am not able to follow you, could you please shed some more light on this.

Warm Regards,
Vijay Sehgal.

On Fri, May 30, 2014 at 2:57 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

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