RE: I/O issues on DB 11g
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dramirezr@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Apr 2014 17:07:36 -0400
IF adaptive direct read is kicking in it is entirely possible that disk
reads increase and whether that is a good thing is a classic it depends.
Adaptive direct read cuts out the middle man stashing results right in the
PGA and this is especially effective if most of what was read would have
simply been churned out of the SGA buffer cache anyway. Often this is an
overall performance improvement, but it is likely to show higher disk
utilization if any of the data that might have been read into the SGA buffer
cache would have later been a buffer hit.
Carefully pre-reading and heating up small look up tables and the like into
the SGA buffer cache will tend to give you the best of both worlds. Only
knowing your specific usage patterns would tell you whether it was useful to
load certain blocks into the default or KEEP cache on database instance
restarts or immediately prior to particular database jobs or work shift
activities.
Occasionally reading in large tables that may have built up delayed block
cleanouts (which do not get cleared by direct read into the PGA) may be
useful, but please do not turn this into a silver bullet treadmill.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Ramírez Reyes
Sent: Wednesday, April 23, 2014 4:40 PM
To: 'oracle-l@xxxxxxxxxxxxx' (oracle-l@xxxxxxxxxxxxx)
Subject: I/O issues on DB 11g
Hi All,
This is the environment.
Windows 2008 R1 Standard, Oracle DB 11g Standard R2, 8 cpu's, 16 GB of
physical memory, 3 disk drives (1 for the OS, 1 for db files, 1 for
backups).
Now the problem:
Since we went live with the 11g system about 1 year ago (we used to be on a
very old and horrible 8i -don't ask why-), we have been receiving Email
alerts about Disk Utilization; at the beginning I thought it should be a bug
of the R2 version as I wrongly understood it was referring about filesystem
space, which is not a problem.
After several months of 5 or 8 daily mails, I decided to look at it on
detail and check what was necessary to drop off that "false alarm".
After Goggling, I realized that the alarm is not related to disk space, but
I/O reads, as we have 3 db's on the same disk drive, each of them with 20 db
files (the biggest DB has datafiles of about 6 GB, the smallest about 2 GB).
The problem is not really "critical" now because general performance is
"good" (we have more than a year with it!), but that of course does not mean
it has to keep on with those problems (and that alarm is starting causing me
headaches also!).
The first two things I though were increasing the PGA size in order to
reduce Virtual Memory usage (and, I/O as consequence) and add 2 more disk
drives to split the db files of each db into a single and dedicated
filesystem; I was also thinking about tuning some high I/O queries, but
don't think the difference could be huge...
Any ideas or suggestions?
Thanks
David Ramírez
Other related posts:
