Re: Disk file operations I/O

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@xxxxxxxxx> 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


Other related posts: