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

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