RE: Is my Oracle Server issuing more IO than it can handle
Date: Thu, 9 Dec 2010 09:04:55 -0500
Message-ID: <1B861F1ABE40A84AA92AD585B20C558B26E4E11D21_at_EX7T2-CV06.TDBFG.COM>
I am assuming you are referring to dbc_min_pct and dbc_max_pct and assuming that nbuf and bufpages are set to 0 then they would be dynamic so yes.
But there's also a couple of other things to look at that might be different too:
mincache
convosync
This would be regarding double buffering between the db and OS.
Rui Amaral
Database Administrator
ITS - SSG
TD Bank Financial Group
220 Bay St., 11th Floor
Toronto, ON, CA, M5K1A2
(bb) (647) 204-9106
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis.Nedzinskas_at_seb.lt
Sent: Thursday, December 09, 2010 2:04 AM
To: Oracle-L_at_freelists.org
Subject: Re: Is my Oracle Server issuing more IO than it can handle
>- is there a possibility that the extra memory on the server that
>performs
well is being utilized by the file system buffer cache and therefore allowing writes or the acknowledgment of those writes to perform quicker (no direct io being used) - compared to the server with less memory?
according to the recent findings we have on one of our databases - yes. After we switched to direct IO the performance of full table scans went down, dba_hist_seg_stats shows longer times.
Please consider the environment before printing this e-mail
Oracle Dba
Wannabe
<oracledbawannabe To
_at_yahoo.com> Oracle-L_at_freelists.org
Sent by: cc
oracle-l-bounce_at_f
reelists.org Subject
Is my Oracle Server issuing more IO
than it can handle
2010.12.08 20:27
Please respond to
oracledbawannabe_at_
yahoo.com
Hi All,
So I was wrong about a different storage being used - well basically the volumes on the poor performing server have been mounted onto another server which uses a different san. The volumes are veritas file systems for the HP-UX platform. So I'd assume the following would not really come into
play:
1. size and number of redo log files - since these are the same volumes on the server that perform poorly and the one that performs with acceptable performance 2. the possibility of a faulty disk - again its the same volumes so issues with the disk would apply to both servers - since again same volumes One difference I do see, however, is that the server that performs poorly has 32gb (and 5 procs) of memory whereas the one that performs well has 64gb (16 procs). Now both databases have an 8gb buffer cache (the good performing server however also hosts another oracle database). Now, that said, HP-UX has two kernel parameters that control the file system buffer cache - which by default (from what I've read) is pretty generous - is there a possibility that the extra memory on the server that performs well is being utilized by the file system buffer cache and therefore allowing writes or the acknowledgment of those writes to perform quicker (no direct io being used) - compared to
the server with less memory?
Here is a side by side comparison of the load profile:
|---------+---------+---------+---------+---------+---------+------------| | |TPS |Redo |PhyW |PhyR |LogicalR |BlockChanges| |---------+---------+---------+---------+---------+---------+------------| |Poor |54/s |4.2mb/s |428/s |1983/s |13,000/s |9155/s | |Server | | | | | | | |---------+---------+---------+---------+---------+---------+------------| |Good |225/s |7.4mb/s |564/s |243/s |110,000/s|7500/s | |Server | | | | | | | |---------+---------+---------+---------+---------+---------+------------|
Here's a comparison of top 5 events for both servers:
Poor Server:
Top 5 Timed Events
|----------------+----------+-------+---------+---------+-------------| | Event | Waits |Time(s)|Avg Wait | % Total | Wait Class | | | | | (ms) |Call Time| | |----------------+----------+-------+---------+---------+-------------| |free buffer |12,471,382|133,152| 11| 52.7|Configuration| |waits | | | | | | |----------------+----------+-------+---------+---------+-------------| |log file switch | 50,033| 38,433| 768| 15.2|Configuration| |(checkpoint | | | | | | |incomplete) | | | | | | |----------------+----------+-------+---------+---------+-------------| |log file sync | 198,985| 30,749| 155| 12.2|Commit | |----------------+----------+-------+---------+---------+-------------| |buffer busy | 46,927| 17,681| 377| 7.0|Concurrency | |waits? | | | | | | |----------------+----------+-------+---------+---------+-------------| |db file parallel| 373| 13,772| 36,921| 5.5,|System I/O | |write | | | | | | |----------------+----------+-------+---------+---------+-------------|
Good Server:
Top 5 Timed Events
|---------------+-------+-------+-----------+-------------+---------| | Event | Waits |Time(s)| Avg Wait |% Total Call | Wait | | | | | (ms) | Time | Class | |---------------+-------+-------+-----------+-------------+---------| |log file sync |821,418| 35,503| 43| 83.2|Commit | |---------------+-------+-------+-----------+-------------+---------| |CPU time | | 7,158| | 16.8| | |---------------+-------+-------+-----------+-------------+---------| |db file |128,596| 2,851| 22| 6.7|System | |parallel write | | | | |I/O | |---------------+-------+-------+-----------+-------------+---------| |log file |235,049| 2,845| 12| 6.7|System | |parallel write | | | | |I/O | |---------------+-------+-------+-----------+-------------+---------| |db file |179,777| 727| 4| 1.7|User I/O | |sequential read| | | | | | |---------------+-------+-------+-----------+-------------+---------|
-- http://www.freelists.org/webpage/oracle-l NOTICE: Confidential message which may be privileged. Unauthorized use/disclosure prohibited. If received in error, please go to www.td.com/legal for instructions. AVIS : Message confidentiel dont le contenu peut être privilégié. Utilisation/divulgation interdites sans permission. Si reçu par erreur, prière d'aller au www.td.com/francais/avis_juridique pour des instructions. -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 09 2010 - 08:04:55 CST