Re: Disk file operations I/O

From: kyle Hailey <kylelf_at_gmail.com>
Date: Tue, 17 Jan 2012 20:34:02 -0800
Message-ID: <CADsdiQj5kBDbz_mdF3dd12UebNm7RX=tdF8LPFMVZv77osMW-g_at_mail.gmail.com>

Would love to know the translations.
The docs say p1 is action and p3 is file type and this is the breakdown select p1,p3, count(*) from
dba_hist_active_sess_history
where dbid=1436980507
and event ='Disk file operations I/O'
group by p1,p3
/

        P1 P3 COUNT(*)
---------- ---------- ----------

         2          1        193
         2          3         14
         4          4          1
*         2          2       4459*
         3          4        160
         1         18        103

• Kyle

On Tue, Jan 17, 2012 at 4:27 PM, kyle Hailey <kylelf_at_gmail.com> wrote:

> 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 - 22:34:02 CST