Re: Reads from Control file under "IOStat by Filetype summary"
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 May 2014 12:45:38 +0100
I like that guess - I think it fits symptoms rather nicely.
Just do a few "select * from v$datafile" or "select * from V$filestat" and
watch the control file read count climb.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: <coskan@xxxxxxxxx>
To: <timur.akhmadeev@xxxxxxxxx>; <vijaysehgal21@xxxxxxxxx>
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 29, 2014 12:29 PM
Subject: Re: Reads from Control file under "IOStat by Filetype summary"
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@xxxxxxxxx
Reply To: timur.akhmadeev@xxxxxxxxx
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@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
--
Regards
Timur Akhmadeev
-- //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
