Re:: Disk file operations I/O
Date: Tue, 17 Jan 2012 20:34:15 -0800 (PST)
Message-ID: <1326861255.73049.yint-ygo-j2me_at_web121006.mail.ne1.yahoo.com>
Hi Kyle,
I am going through something similar in our "hybrid" environment, (really, really big OLTP that we've just convinced ourselves is a warehouse... :)) We have always experienced high IO, but after our upgrade from 10.2.0.4 to 11.2.0.2, noticed some performance challenges in just a few large transational proesses.
When pulling the dbms_xplan.display_awr, I noted that many times we were experiencing cpu_costing off. Adding this hint often corrected the problem alone or pushing an index hint with the cpu_costing to again, eliminate IO. Upon investigating deeper with a 10053 trace, noted high file operation IO along with the performance degradation. Stepping through, also showed more expensive CPU costing vs. IO costing that made no sense, it occurred to me to check the system stats since they were the base for the costing. They had been gathered and were old, before new hardware/11g parameter increases for memory, etc. In 11g the optimizer choice is set to choose, where we had it locked down in 10g and the combination, with CPU costing utilizing the system stats it seems to be our optimizer choice killer. *Currently* we have updated the system stats and I'm going to then verify the performance correction, hoping to no longer see the increase in I/O, just not there yet.
I'm not one to go crazy with system stats outside of database duplicates on new hardware, but inspecting the cpu_costing area might be worth the time. I hope this helps and I'll update you if this appears to correct my IO file operations issues. I will know by the end of the weekend!
Kellyn Pot'Vin
Sr. Database Administrator and Developer
On Tue, Jan 17, 2012 4:27 PM PST kyle Hailey 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
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 17 2012 - 22:34:15 CST