Niall and John, 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@xxxxxxxxx > 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@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 >