Re: Disk file operations I/O
Date: Thu, 19 Jan 2012 21:09:15 -0800
Message-ID: <CADsdiQhOPboZbEreoRpG8neVMgJSyos_bVXLkEd9-EM7afBLPg_at_mail.gmail.com>
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_at_oracledepot.com> 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_at_gmail.com> wrote:
> ...
>
>>
>> _at_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
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 19 2012 - 23:09:15 CST