Performance Issue?

[Geoff Wild]

Having a bit of a performance issue with a system.

System is a RP4440 with 12GB of ram a 2 dual core 1GB cpus running 11iv2.

Kernel has been tuned for Oracle 10g as per Oracle specs.

From the dba's:

"The average read and write i/o response was around 1.5 msec or less, with most datafiles getting under 1 msec response. I did not see any spikes anywhere near 1 sec, a few up to 5 msec at most.
Some of the high i/o service time numbers in Oracle 10g Grid control are for root disks and not database disks."

And yet - the disks spike at 100%!!!

Background on disks:

The "disk" that the OS sees (/dev/dsk/c4t10d1 for example) is not really a disk - it is a LUN. The LUN is a 32 GB meta made up of 8MB chunks of several disks. So, even if I were to "present" say a 2 GB LUN (disk to the OS), it will for all intensive purposes be on the same "physical" disks as the rest. The frames are RAID 10 - that is striped and mirrored across as many disks as possible.

On another system, the i/o response times were higher then this server 5 - 10 msec range - and yet that system doesn't spike at 100% ever on the disks.

What I think we have is an application that is constantly hittng the database - and not doing it very elegantly (IE expensive sql).

I attached a lengthy text file with sar/glance data...

Looking for comments...

Thanks...Geoff

[Alex Lavrov.]

Did you try to track the process that uses the disks? It can be done in glance.

If it' oracle's, than your DBA can analyse Oracle with their tool to see what session is "killing" the disks.

Alex.

[Geoff Wild]

Here's some of the processess:

B3692A GlancePlus C.03.86.00 11:00:51 svr104 9000/800 Current Avg High
-----------------------------------------------------------------------------------------------------------------------
CPU Util S SN NU U | 48% 66% 84%
Disk Util F F |100% 100% 100%
Mem Util S SU UB B | 73% 73% 74%
Networkil U UR R | 53% 53% 53%
-----------------------------------------------------------------------------------------------------------------------
PROCESS LIST Users= 4
User CPU Util Cum Disk Thd
Process Name PID PPID Pri Name ( 400% max) CPU IO Rate RSS Cnt
--------------------------------------------------------------------------------
gzip 3825 3406 245 oracle 86.9/72.1 35.9 166/ 137 644kb 1
oracleipris 19200 1 154 oracle 12.2/ 2.0 72.1 72.0/ 6.0 13.9mb 1
oracleipris 26732 1 148 oracle 10.2/ 0.8 851.9 10.4/ 2.5 24.5mb 1
oracleipris 24218 1 200 oracle 10.2/ 0.4 555.8 0.0/ 1.8 15.4mb 1
oracleipris 4573 1 154 oracle 8.2/ 0.3 235.9 0.0/ 0.8 19.5mb 1
oracleipris 25217 1 154 oracle 8.2/ 0.4 186.9 46.3/ 1.1 13.8mb 1
oracleipris 24594 1 148 oracle 4.9/ 1.1 1353.1 66.8/ 3.1 17.7mb 1
oracleipris 18426 1 154 oracle 4.7/ 1.1 40.3 57.0/ 4.7 16.9mb 1
oracleipris 6616 1 148 oracle 4.5/ 0.6 812.2 39.0/ 1.5 15.5mb 1
oracleipris 3380 1 154 oracle 3.1/ 3.7 2.4 0.0/ 0.7 10.5mb 1
oracleipris 19206 1 148 oracle 2.9/ 1.7 60.6 87.5/ 3.6 13.7mb 1
oracleipris 8205 1 154 oracle 2.2/ 1.0 793.4 2.5/ 2.4 15.5mb 1
oracleipris 28225 1 154 oracle 2.0/ 1.4 1666.8 0.9/ 3.1 18.0mb 1
emagent 2805 2795 154 oracle 2.0/ 0.5 480.0 0.9/ 0.1 59.7mb 6
oracleipris 2108 1 154 oracle 1.6/ 0.9 875.3 3.8/ 2.3 17.0mb 1
oracleipris 28355 1 154 oracle 1.6/ 1.3 1609.0 0.0/ 3.3 18.5mb 1
oracleipris 16548 1 154 oracle 1.6/ 1.1 1168.8 9.3/ 3.3 17.2mb 1
nmupm 3872 2805 179 oracle 1.3/ 1.3 0.1 0.4/ 0.4 51.5mb 1
oracleipris 21022 1 148 oracle 1.3/ 0.6 431.7 88.8/ 2.5 16.2mb 1
oracleipris 24395 1 154 oracle 1.1/ 0.6 743.6 5.9/ 1.4 18.5mb 1
nmupm 3874 2805 179 oracle 1.1/ 1.1 0.1 0.0/ 0.0 51.5mb 1
nmupm 3875 3874 179 oracle 1.1/ 1.1 0.1 0.0/ 0.0 8.0mb 1
midaemon 1529 1 -16 root 1.1/ 1.7 2393.1 0.0/ 0.0 22.8mb 2
oracleipris 13479 1 154 oracle 1.1/ 0.9 854.6 1.1/ 2.9 19.9mb 1
oracleipris 15244 1 148 oracle 0.9/ 0.1 111.3 102/ 0.5 18.3mb 1
oracleipris 775 1 154 oracle 0.9/ 0.3 300.4 1.3/ 1.1 18.1mb 1
ora_lgwr_ip 6003 1 156 oracle 0.4/ 1.1 1448.9 129/ 160 19.9mb 1
dbsnmp 5800 5797 154 oracle 0.4/ 0.3 422.6 0.0/ 0.0 21.4mb 16
oracleipris 25578 1 154 oracle 0.4/ 0.3 270.6 2.7/ 1.0 17.1mb 1
oracleipris 17471 1 154 oracle 0.2/ 0.4 192.5 7.2/ 2.0 16.2mb 1
ora_dbw3_ip 5993 1 156 oracle 0.2/ 0.1 183.5 70.4/21.5 11.9mb 1
ora_dbw1_ip 5989 1 156 oracle 0.2/ 0.1 183.6 48.1/21.5 11.9mb 1
oracleipris 25223 1 154 oracle 0.2/ 0.3 180.8 1.1/ 1.3 14.0mb 1
oracleipris 13994 1 154 oracle 0.2/ 0.3 172.2 1.8/ 1.1 14.3mb 1
ora_dbw4_ip 5995 1 156 oracle 0.2/ 0.1 183.1 44.0/21.5 11.9mb 1
The gzip is the archive logs - lasts about 45 seconds. Even after done disks are still hammered.


Rgds...Geoff

[Rita C Workman]

Well Geoff, first...I'm no expert but we run a variety of Oracle versions, and if there is one thing I do know it's that the Oracle tuning document is just plain wrong on a few parms. But parms isn't what you say is your problem...disk I/O is what you mention.
So I might look at the basics, cause everything is just basics in the end.

Oracle gzip for the archives...how often does this happen ? Based on how they set things, the DBA's should be able to set this so it doesn't become a 'too often' process.
Next I'd look at the disks and what is writing on them....do you have lvols on the same disk that might cause contention problems. Here the storage vendor said you don't have to map it out...the array can handle the load. Wrong. I/O performance issues all over the place till we manually moved (pvmove) things around to clear up the I/O contentions. Like I said just doing the basics.
I'd especially look at where Oracle is writing their logfiles...for everything. I had one DBA write everything to one disk (no longer with us, thankfully)-and didn't see why I had an issue with it.

On the application thing...that is something you should have the DBA's looking into using some Oracle tools to tune lousy querries. Developers don't always really test the code, put it in production and wonder what happens when bad code tries to do lookups on big tables. Too many DBA's don't like to do this.

Hope I didn't ramble too much,
Rgrds,
Rita

[TwoProc]

Good news! You don't need to tune your system disks architecture! :-) (sarcasmistic funny) :-) (new word for free too) :-)

It's then obvious that your disk I/O isnt taking up lots of time per call, you're just doing lots and lots of I/O.

Well, there's 3 things you can do, in the following order of cost:

1) Tune the queries to run better. This is the most obvious, but in deference to the leafy hat you're wearing - I'm going to assume that this is done already.

2) Quit using disk bandwidth and use more memory bandwidth (which is muuuuch faster) by increasing the size of the buffer_cache. Keep in mind that if your current hit ratio is now at 96%, *then the remaining 4% represents practically ALL of your I/O*. So, if you can move the hit ratio 1% better, you've reduced total I/O by 25% (roughly)!.
This is a case, not of any percentages, or waits being "wrong", but just a matter of how much I/O you're trying to push. If you can push less I/O you'll perform much better.
A note about this. You'll find folks here who'd be opposed to this approach (as I used to be), but it's NOT TRUE that the system will actually run slower managing a huge cache b/c you increased the buffer cache. Managing these areas is what Oracle does quite well, and you're not going to hurt it by doing so. It does increase the amount of time to start the database and to a lesser degree stop it, but that's about all you could measure in a throughput test.

2a) I'm adding this in here, because I've seen measurable differences in performance due to the following: multiblock_read_count.
Change that from 8(default in 9i, don't know about 10g) to 16 and see if your measured system disk I/O from the Unix system (glance or perfview) drops. It might. It will throw you off a bit however, because the cost optimizer will be biased more often to do full table scans (or full index scans) instead of index range scans (or similar). This looks at first glance to be less efficient (because the estimated costs of the large queries you're running are higher). But you may notice (and you may not) that total system I/O is less (ours was). The parameter IS DYNAMIC, so if you don't like the way the day is running with the new setting, you can flip it back down to the old level quickly, with no harm done.

3) You can put more cache in your storage server, and if that doesn't work, you can buy faster storage systems/servers. This is almost always effective (unles you're "there" already).

My guess that you've already done suggestion 1), but you've not ventured into 2). Try it, it cam be a pretty inexpensive (all things considered) fix to an I/O problem. Like I said though, provided that suggestion 1 is done already, and there is room for improvement in the cache hit ratio, even a few percent.

[Bill Thorsteinson]

Have the DBAs check for longops and see if
you have frequent full table scans. If you
have multiple CPUs running parallel table
scans on tables which are not cached
you will get high read I/O.

Check the frequency of archive log switches.
If this is the case you would see high
write I/O.

The DBAs should run the database monitor
for a while and check cache hits. If the buffer cache is too small you can get
frequent re-reads on data which should
be kept in memory. Aim for 98% cache hits.

The DBAs can also check the v@sql table to
see if there are any nasty statements being
run. These include flushing the sga,
updates without where clauses on large
tables, creating large tables with select
clauses.

The read to write ratio on the LUN may
give a hint as to the type of problem
which is occuring.

[Geoff Wild]

Very interesting comments so far - thanks.

As far as the leafy hat - I'm Unix all the way - so the queries are in the hands of the developers - not me :)

Buffer cache - you of course mean Oracle's buffer cache as Oracle bypassess the OS's - again - not my job man. :)

Currently, the OS buffer cache is: 860MB

gzip runs every 5 minutes.

Adding more cache to the frame - not needed - as this is the only system have the issue - and frame stats show the frame/ FA's/ disks are behaving fine (BTW - 2GB SAN).

I'll check the others.

Thanks...Geoff

[Geoff Wild]

From the dba's:

These have all been looked at, are under continual review and/or have been set already. The only one outstanding which we may still do is adjusting the online log sizes to reduce the number of switches. Usually an online log is filling up every 5 minutes or less even during primetime. Each online log is 250 Meg in size, so this gives you an idea of the amount of update activity going on during prime time.

Rgds...Geoff

[TwoProc]

I'm pretty certain that switching log files every 5 minutes at peak time isn't hurting you, providing that the alert log is telling you that you are completing the log switch (start and finish times are indicated in the log file) well within that 5 minute time frame. However, the "log_buffer" (buffer size for redo logs) may not be large enough, and this is something that we've increased recently. We went from 52,428,800 to 73,400,320. I know that when we first went live with the newer, larger modules the values for this were much lower way back when.

Also, are your redo logs interleaved across two different file systems and disk sets? I've found that in order to keep up with heavy I/O I've had to keep two mount points for redos and one for archive logs. Odd numbered log files go in one redo log mount point, and even numbered ones go in other. This way, during the switch from say, redolog1 to redolog2, you'd be reading from redolog1 and writing to the next archive log (which hopefully resides on yet another file system and set of disks) and at the same time writing to the next redolog2 on the second redolog filesystem (and set of disks).

I always have LOTS of wasted space on the two redolog interleaved sets of disks, as my goal is a rather large set of disk mechs striped together across a nice chunk of controllers to get the job done quickly. The goal here is to ignore "gigs" of space and consider how much hardware is answering the call to making the writes happen quickly. And, all of this is RAID 0/1 naturally (never, ever R5 for this type of data).

What is the size of the dba_buffer_cache and what is the hit ratio? What data rates (read/write) are you seeing on the busiest pvs? And, from your previous post, can we infer that these busiest pv's are supporting the redo log areas?

Oh yeah, these redo log mount points should be mounted as "convosync=direct" - even if your multiblock_read_count is less than 32 in the Oracle database, therefore excluding the use of the system's buffer cache. Everything else, needs to allow the system to buffer cache if the multiblock_read_count is less than 32(that is NOT to use convosync=direct). The multiblock_read_count parameter is normally below 32 unless you've got a datawarehouse. If you've got an XP series, the optimal setting that I've found from heavy performance testing is 16 if your block size 8k. This because 8kx16=128k is the reported (from HP) max single data transfer size for a storage server of this type, and our testing validated this point for total max processing throughput (by user model types of various types throughout the company). The wierd datapoint of whether or not to use the direct mount point at a multblock_read_count of less than 32 came from the HP tuning team (actually the sub-team) dedicated to tuning Oracle on the HP platforms, and verified by Oracle itself. This data point comes out b/c of something that is done differently (that is a code branch) that happens when the multiblock_read_count is less than 32, which requires a set of I/O at the file system level, and not just within the file itself. Of course, if you're using raw files (and according to your last posting, I don't think you are) this doesn't apply. Caveat Emptor: this information is gathered from Oracle 9i, not 10g.

[TwoProc]

A bit more on redologs...

Re: Log file sizes - we've got them at 1G each, and at peak we hit a switchlog rate of 3 files per minute (and hence we're looking at making them even larger for a little elbow room), but we're not really in any trouble b/c the switches complete within the time windows of the max switch out per minute rate - 20 seconds (which gives you an idea of the amount of update activity at peak we've managed to address these types of problems for).

[Ninad_1]

Geoff,

Is performance really a problem or is it just the 100% for the disk shown in glance ?
What I observed is that the disk/LUN - c4t10d1 is very busy till around 1:20 and then the disk c4t10d2 is busy . The avserv times are around 17 ms for them when they are busy , but there is no significant avwait of avqueue. So what I feel is that there may be a slight performance issue but not as much as felt due to 100% figure of the disk activity. As you must be definitely aware that glance will report the highest disk activity percent - so even if a particular disk is very busy it will show 100% busy in glance and the next moment some other disk is very busy again you will see 100% busy disk in glance - even though its not the same disk each time.
I dont think its very bad to have a disk very busy for some times.
The next thing would be to move some of the most used datafiles from these disks to some other disks/LUNS if you feel that there is perf problem and this will help in requests scattered across disks thus probably keeping each disk lesser busy.
But as I can see the data seems to be more or less distributed - as per c4 disks being almost equally busy except for those few instances of d1 and d2 disks being more busy.

Regards,
Ninad

[Darrel Louis]

Geoff,

Is the other system at the same RAID level?

What I do, when experiencing the same issue you have, ask the DBA to check what the session is really doing.
The 100% is mostly caused by a full table scan(query) or the indexes which needs to be re-analyzed(Update statistics).

Goodluck

Darrel

[Geoff Wild]

The performance issue is that occasionally they lose transactions (3 or 4 during peak time).

RAID is same on all our systems.

I tried mounting with convosync=direct and it appeared to make no difference - though not for redo.

It is my understanding, that Oracle 10g bypasses the OS buffer cache anyways.

Thanks all!

Rgds...Geoff

[TwoProc]

Geoff, IMHO - I wouldn't call the loss of 3 or 4 transactions (depending on how they are lost I guess, that is, error codes) at peak periods a performance issue. Sounds like a bug in Oracle, or possibly a resource constriant (in Oracle or in HPUX) to me. What's the error code that Oracle provides when the failure occurs.

[Alzhy]

Greetings Geoff!

" I tried mounting with convosync=direct and it appeared to make no difference - though not for redo.

It is my understanding, that Oracle 10g bypasses the OS buffer cache anyways. "


Geoff, the rule of thumb for cooked filesystems even for Oracle 10G is *still* to use forced directIO on filesystem mounts. So that means:

mincache=direct,delaylog,convosync=direct

and with your buffer cache between 800MB to 1.6GB (depending on other non-Oracle activity).

But of course RAW is always better...

Messr Joubert (Mon Favourite Oracle ITRC Person), Geoff pardon for the hijack...:

We actually have somewaht of a similar issue on a large cooked environment (9i). The system is hooked up to a massive XP12000, all best prctices, forcedDirectIO and my applications are always complaining of slow response and throughput questions. Yet I see Oracle is not able to exact more storage performance and the XP12000 is dying to give more of what it can give. SAN and FC channels are healthy. I do notice "db_file_multiblock_read_count" is simply at 8. And my DBAs are "afraid" to tweak it upwards due to possible "repercussions" which they would not explain anyway. What do you think? Is it dangerous to increase this parameter and what will be the consequences?

[Geoff Wild]

As far as convosync=direct,mincache=direct,goes, we had to remove that from my QA SG cluster as we saw this in the startup log after patching our system:

vxfs mount: option not supported on this version of vxfs.

Running 11.11.

# fstyp -v /dev/vg50/sapdata12
vxfs
version: 4
f_bsize: 8192
f_frsize: 8192
f_blocks: 17670144
f_bfree: 43635
f_bavail: 43551
f_files: 2976
f_ffree: 10880
f_favail: 10880
f_fsid: 1077018636
f_basetype: vxfs
f_namemax: 254
f_magic: a501fcf5
f_featurebits: 0
f_flag: 16
f_fsindex: 7
f_size: 17670144

Version 4 is highest on 11.11 - so I'm not too sure what happened there...

[Alzhy]

VxFS version 4.0 layout should be able to allow your Forced DirectIO mounts. What was the mount directive used?

[Geoff Wild]

Here's a test - I created a 512MB filesystem:

mount -o mincache=direct /dev/vg01/lvol4 /zmnt
vxfs mount: option not supported on this version of vxfs.

# fstyp -v /dev/vg01/lvol4
vxfs
version: 4
f_bsize: 8192
f_frsize: 1024
f_blocks: 524288
f_bfree: 523059
f_bavail: 490368
f_files: 130796
f_ffree: 130764
f_favail: 130764
f_fsid: 1073807364
f_basetype: vxfs
f_namemax: 254
f_magic: a501fcf5
f_featurebits: 0
f_flag: 0
f_fsindex: 7
f_size: 524288

[Alzhy]

Hmm, my /etc/fstab entry is:

/dev/vx/dsk/sapdg1/ora001 /oradata/ora001 vxfs rw,suid,largefiles,convosync
=direct,mincache=direct,datainlog 0 2

I am 1t 11.11 but using VxFS/OJFS 3.5 version which should be your default version at 11i R2.

I think you may have a bad patch or something is different on 11i R2.

Can you try testing your mount via an fstab entry?

[Geoff Wild]

Some more feedback from our dba's:

Here are some stats from a recent statspack report:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.18 In-memory Sort %: 99.98
Library Hit %: 98.81 Soft Parse %: 94.14
Execute to Parse %: 89.18 Latch Hit %: 99.67
Parse CPU to Parse Elapsd %: 56.61 % Non-Parse CPU: 83.70


Overall the database is running very efficiently - what's hurting is the transactional load against the database and this is coming from the application.

For example - the application was causing one sql select statement to run over 1373 times a second. A change in how the application uses its own caching dropped the execution rate on this sql statement to 149 times a second.

There are a number of other similar candidates with at least 5 other sql statements being executed between 121 -> 649 times a second.

Rgds...Geoff

As far as the OS Buffer cache, this is how I was mounting:

/dev/vg20iprismp/data01 /data/oracle/iprismp/data01 vxfs rw,suid,largefiles,mincache=direct,convosync=direct,delaylog,nodatainlog 0 2
/dev/vg20iprismp/data02 /data/oracle/iprismp/data02 vxfs rw,suid,largefiles,mincache=direct,convosync=direct,delaylog,nodatainlog 0 2
/dev/vg20iprismp/data03 /data/oracle/iprismp/data03 vxfs rw,suid,largefiles,mincache=direct,convosync=direct,delaylog,nodatainlog 0 2
/dev/vg20iprismp/data04 /data/oracle/iprismp/data04 vxfs rw,suid,largefiles,mincache=direct,convosync=direct,delaylog,nodatainlog 0 2
/dev/vg20iprismp/indx01 /data/oracle/iprismp/indx01 vxfs rw,suid,largefiles,mincache=direct,convosync=direct,delaylog,nodatainlog 0 2
/dev/vg20iprismp/exports /data/oracle/iprismp/exports vxfs rw,suid,largefiles,delaylog,datainlog 0 2
/dev/vg20iprismp/arch /data/oracle/iprismp/arch vxfs rw,suid,largefiles,delaylog,datainlog 0 2
/dev/vg20iprismp/redo01a /data/oracle/iprismp/redo01a vxfs rw,suid,largefiles,delaylog,nodatainlog 0 2
/dev/vg20iprismp/redo01b /data/oracle/iprismp/redo01b vxfs rw,suid,largefiles,delaylog,nodatainlog 0 2
/dev/vg20iprismp/redo02a /data/oracle/iprismp/redo02a vxfs rw,suid,largefiles,delaylog,nodatainlog 0 2
/dev/vg20iprismp/redo02b /data/oracle/iprismp/redo02b vxfs rw,suid,largefiles,delaylog,nodatainlog 0 2


How should I be?

[Geoff Wild]

Doh!

I now know why those mount options don't work on that 11.11 server:

# /sbin/fs/vxfs/vxenablef
System is not licensed

Sigh...

Rgds...Geoff

[Alexey_12]

It looks like a database load issue. It is a good idea for DBA to run perfstat and see what kind of execution plan improvements might be done for top I/O statements. Sometimes adding just one index might solve a problem.

[TwoProc · Accepted solution]

Geoff, a few points.

1) Your buffer cache hit ratio at 97% at peak. I've said this before (kinda feel like I'm soap boxin' - so I really apologize), but if you've got a high I/O load at 97% cache hit ratio - then the big concept here is to remember that the missing 3% represents almost 100% of your I/O. This means you could really try it bring up the hit count by increasing the size of the buffer cache (that being said, I don't know what your size is now, it could be that you really can't). If you could get this to be at 98%, you'd cut total I/O by 1/3. This could possibly be the very thing that gets you out of the hole.

2) Revisit the size of the "log_buffer" cache with the DBA team, if this is too small (most sites have this too small, and overlook it IMHO), this can draw down your total throughput by 10% or so.

3) Now that you've got those problematic (high call rate) queries identified, chances are that these are NOT the queries that were flushed (and thene reloaded) from/to the shared pool (Library). That would be verified with looking the in the sga and seeing the reload quantities for those statement to be absolutely sure though. However, at the times when you're losing transactions - are any of these high utilization sql statments part of the problematic failing processes? If so, they could be booted out of the shared_pool by some other big footprint procedure call. These reparses and reloads in the shared pool by these high volume statments (because of some other largish thing) could be causing the problem. So, the reload stats should be carefully reviewed during this period. If this is the problem (and I really don't think it is, so I must admit I feel a little silly following this train of thought, but heck it's lunch hour anyways...) you've got a few choices a) pin the large beast that kicked out your code into the shared_pool first, b) put in the large pool, c) pin the fast turning code in the shared-pool as well as doing a) along with it. Anyways, the fast turn code needs to reduced where it can, and where it can't make sure it stays in the shared pool - easy enough.

4) The tables involved in the fast turn/churn queries - what are their INI_TRANS values? Are these set at the defaults of "2". If so, these aren't high enough to support a high level of activity. Try setting these to 12 or so. I've noticed large differences in our ability to sustain high work loads by tweaking these values. You'll need to review the associated indexes in use by these queries too (check out the estimated cost plan in Toad/explain plan to see these). Keep in mind that resetting these in the tables only affects NEW records added to the tables, to affect old records that may be constantly updated (less likely scenario though) you'll need to rebuild the table either through a move command or an import/export (pick ur poison). If the tables being hit are mostly with "new" records from the say day or two of business, then don't worry about the rebuilds, just make the change. However, to make the change in an index, you must rebuild.

5) The tables that are involved in the high turn/churn queries do you have a "Cache" hint turned on for these? This is to tell the buffer_cache to try and leave these tables in memory and not use them as a target for a file when space is needed. This "should" happen normally anyways (algorithmically speaking), but it is a simple change to make, requiring no rebuild. I generally set this on these types of readily identified tables, just to lower the probability that I'm going to have to fix this as an issue.

6) Cost of the tables involved in the high turn/churn queries. If these tables are high growth, then recalculating the stats of these target tables often (so that the row counts are correctly reflected among other things) can make a big difference in avoiding the problems you may be having. I do these types of target tables (where I've identified that it actually helps) a few hours before the peak of the day kicks in.

7) Histograms of the tables involved in the high turn/churn queries. Are your dba folks calculating histograms during cost estimates for the highly used columns? Columns that are used to increase selectivity through filtering by matching stated literals in the queries (not variables) are targets for histograms. Many histograms have only two buckets by default, and need tweaking to really work well. When properly put in place, and when usable, this makes a dramatic different in run/execution times, as well as number of busy blocks being hit by the database at once, increasing efficiency, increasing total throughput of the database. Don't forget that any time stats a recalculated (see item 6) you need to include statements to update histograms simultaneously - or the histograms get blasted away.

8) Since you're not using raw ... The tables involved in the high turn/churn become constrained for the number of updates that can be done at once - becuase if all of those updates are happening in a single file, then updates really are only going to happen one at a time. One of the ways to help relieve this condition is to use spread datafiles for your tablespaces. That is, instead of having 2 4G datafiles for, lets say a busy inventory tablespace - I'd have 16 1/2G files with free space preallocated and available in each so that new data naturally stripes across the files. Conceptually, if the world is being nice to you, and your query data at hand is evenly spread, you could do 16 updates at the same time. You probably won't get all that, but you'll get some of it (your mileage will vary).

9) And since you took up my advice on number 8), (grin) follow this up by wasting a bit of space by increasing the value of pct_free for each of the tables that are targets from the above scenarios. Although this will waste space, it will spread out the number of blocks in use that are highly contentious (busy block problem) and give you some percentage edge in accessing data in there reducing latch waits. That is of course, if the dba's can identify this as a problem(latch waits), but it's pretty common with these churn&burn queries. What happens at that point, is that you can only access blocks in the buffer cache as fast your cpu can do executes and let-go of the latch. Spreading the data across more blocks gives you more space to build queued requests across, for more subsequent queries and different queries.

10) If you're not using uniform table allocation and locally managed tablespaces, you could also play with the freelists onthe tables, but the chances of that being available to you are pretty low, and this is supposed to be managed automatically for you(us) now.

HTH

[Geoff Wild]

John,

"1) Your buffer cache hit ratio at 97% at peak. I've said this before (kinda feel like I'm soap boxin' - so I really apologize), but if you've got a high I/O load at 97% cache hit ratio - then the big concept here is to remember that the missing 3% represents almost 100% of your I/O. This means you could really try it bring up the hit count by increasing the size of the buffer cache (that being said, I don't know what your size is now, it could be that you really can't). If you could get this to be at 98%, you'd cut total I/O by 1/3. This could possibly be the very thing that gets you out of the hole."

Is that OS buffer cache or Oracle?

Thanks...Geoff

[TwoProc]

Oracle Geoff. Re: the stats from your previous posting.