RE: I/O issues on DB 11g
Date: Wed, 23 Apr 2014 17:07:36 -0400
Message-ID: <0fb701cf5f38$0e8bbd40$2ba337c0$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of David Ramírez Reyes
Sent: Wednesday, April 23, 2014 4:40 PM
To: 'oracle-l_at_freelists.org' (oracle-l_at_freelists.org)
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 23 2014 - 23:07:36 CEST