RE: Is my Oracle Server issuing more IO than it can handle
- From: "Amaral, Rui" <Rui.Amaral@xxxxxxxxxxxxxxxx>
- To: "'Laimutis.Nedzinskas@xxxxxx'" <Laimutis.Nedzinskas@xxxxxx>, "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 9 Dec 2010 09:04:55 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Laimutis.Nedzinskas@xxxxxx
Sent: Thursday, December 09, 2010 2:04 AM
To: Oracle-L@xxxxxxxxxxxxx
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
@yahoo.com> Oracle-L@xxxxxxxxxxxxx
Sent by: cc
oracle-l-bounce@f
reelists.org Subject
Is my Oracle Server issuing more IO
than it can handle
2010.12.08 20:27
Please respond to
oracledbawannabe@
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| | | | | |
|---------------+-------+-------+-----------+-------------+---------|
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
