Re: Disk file operations I/O
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: Andy Klock <andy@xxxxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2012 21:09:15 -0800
Jonathan pointed out that there is a bug with "Disk file operations I/O"
with dNFS.
The database isn't on dNFS but is using NFS, so this sounds like a
candidate, especially considering the bug sounds like a slow I/O can send a
waiter off into la la land for much longer than necessary which is the
case.
The database is 11.2.0.3 and the bug says fixed in 11.2.0.3 patch set. Can
anyone tell me if 11.2.0.3 patch set is different than 11.2.0.3?
*Bug 10361651 - File open may hang/spin forever if dNFS is unresponsive [ID
10361651.8]*
- Kyle
PS Here are some more stats:
There were 4 major periods of slowdown in the data
MAXST MINST COUNT(*) P1 P2 SQL_ID BSID
----- ----- ---------- ---------- ---------- ------------- -------
15:11 15:16 306 1800 375460 3gbsbw6w8jdb3 785
15:31 15:38 252 1811 273100 3gbsbw6w8jdb3 281
19:17 19:22 248 1813 265327 3gbsbw6w8jdb3 533
21:54 21:58 127 1812 278732 3gbsbw6w8jdb3 11
of those periods the "Disk file operations I/O" was 2 orders of magnitude
slower than the maximum slowness of any other type of I/O. MX is the max
time in ms of an I/O of the type in EVENT.
The first column represent a 10 minute bucket starting at that time.
ST EVENT MX (ms) CNT
----- ------------------------- ---------- ----------
1510 Disk file operations I/O 130070 299
1510 db file parallel read 0 1
1510 db file scattered read 1383 72
1510 db file sequential read 544 23
1510 direct path read 814 19
1510 direct path write 319 33
1510 utl_file I/O 0 1
1530 Disk file operations I/O 415226 602
1530 db file scattered read 1426 68
1530 db file sequential read 88 9
1530 direct path read 427 13
1530 direct path write 235 11
1910 Disk file operations I/O 144682 226
1910 db file parallel read 3641 2
1910 db file scattered read 308 21
1910 db file sequential read 658 70
1910 direct path read 800 37
1910 read by other session 0 10
1920 Disk file operations I/O 12 192
1920 db file scattered read 972 21
1920 db file sequential read 758 76
1920 direct path read 710 43
1920 read by other session 0 29
1920 utl_file I/O 0 1
2150 Disk file operations I/O 315174 294
2150 db file parallel read 16 1
2150 db file scattered read 537 31
2150 db file sequential read 529 14
2150 db file single write 510 17
2150 direct path read 527 45
On Thu, Jan 19, 2012 at 11:17 AM, Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
> Awesome. I noticed late yesterday that I only provided one side of the
> mappings you were asking about. With the file operation mapping (thanks
> Yong) you've got a nice direction to go down. Look forward to hearing
> about the outcome.
>
> On Thu, Jan 19, 2012 at 2:08 PM, kyle Hailey <kylelf@xxxxxxxxx> wrote:
> ...
>
>>
>> @Yong Huang: thanks , DBA_HIST_IOSTAT_FUNCTION_NAME looks encouraging:
>>
>> select distinct function_id, function_name from
>> DBA_HIST_IOSTAT_FUNCTION_NAME
>> order by function_id
>> /
>>
>>
>>
>> FUNCTION_ID FUNCTION_NAME
>> ----------- ------------------------------
>> 0 RMAN
>> 1 DBWR
>> 2 LGWR
>> 3 ARCH
>> 4 XDB
>> 5 Streams AQ
>> 6 Data Pump
>> 7 Recovery
>> 8 Buffer Cache Reads
>> 9 Direct Reads
>> 10 Direct Writes
>> 11 Smart Scan
>> 12 Archive Manager
>> 13 Others
>> which would make it an issue of LGWR on a DATAFILE.
>> Will look into LGWR issues and follow up.
>> Thanks
>>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
