Re: Is my Oracle Server issuing more IO than it can handle
- From: Laimutis.Nedzinskas@xxxxxx
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Thu, 9 Dec 2010 09:03:36 +0200
>- 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
Other related posts:
