Disk file operations I/O
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: ORACLE-L <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 17 Jan 2012 16:27:13 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
