Disk file operations I/O

From: kyle Hailey <kylelf_at_gmail.com>
Date: Tue, 17 Jan 2012 16:27:13 -0800
Message-ID: <CADsdiQgRwkJP05XrqTUzWV-ttEuTOARFchq2J9R8KmiNK=bT6w_at_mail.gmail.com>

Anyone have any info on what exactly "Disk file operations I/O" is? A customer upgraded from 10.2.0.4 where things were running fine to 11.2.0.3 and now they are hitting high contention on buffer busy waits.

The buffer busy waiters are waiting for a session that is waiting for "Disk file operations I/O".

Here is an example of 4 minute period where 5 sessions backed up on buffer busy waits. Both waiters and blocker are executing the same select for update. All users SQL_EXEC_ID stays the same so it's the same execution (ie not multiple executions)

Buffer Busy Waiters:
MAXST MINST COUNT(*) SESSION_ID P1 P2 SQL_ID BSID
----- ----- ---------- ---------- ---------- ---------- -------------

---

09:54 09:58 26 1526 1812 278732 3gbsbw6w8jdb3 11
09:54 09:58 25 528 1812 278732 3gbsbw6w8jdb3 11
09:54 09:58 25 1514 1812 278732 3gbsbw6w8jdb3 11
09:54 09:58 26 777 1812 278732 3gbsbw6w8jdb3 11
09:54 09:58 25 33 1812 278732 3gbsbw6w8jdb3 11

Blocker, sid 11:

MINST MAXST EVENT                            COUNT(*) SQL_ID           BSID
----- ----- ------------------------------ ---------- ------------- -------
09:54 09:58 Disk file operations I/O               26 3gbsbw6w8jdb3

The wait is on a data block of a table. The waiters buffer busy wait data looks like (all same P1 and P2)

ST            P1         P2 CURRENT_OBJ#     FN     BLOCKN SQL_ID
BLOCK_TYPE         BSID

----- ---------- ---------- ------------ ------ ---------- ------------- --------------- -------

09:54       1812     278732        34395   1812     278732 3gbsbw6w8jdb3
data block           11

34395 is a table, applsys.fnd_concurrent_request

(db_files is 10000)

The query in question is like:

SELECT
    blah blah

FROM fnd_concurrent_requests R,
     fnd_concurrent_programs P,
     fnd_application A,
     fnd_user U,
     fnd_oracle_userid O,
     fnd_conflicts_domain C,
     fnd_concurrent_queues Q,
     fnd_application A2,
     fnd_executables E,
     fnd_conc_request_arguments X

WHERE
    blah blah
FOR UPDATE OF R.status_code NoWait
/

• Kyle Hailey

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 17 2012 - 18:27:13 CST