Re: OLTP help

Dave:

I concur with Brandon and Jared. I would emphasize more that a different blocksize disallows the use of multiple buffer pools. You should check out the Performance Tuning Guide, search docs for V$BH. The general idea is to get heavily used tables and indices out of the default pool so they don't interfere so much with everything else as Oracle does its buffer housekeeping. I've seen magically delicious results just moving a few key objects (into the recycle pool in the case of things that have a large proportion of blocks flushing through). OEM's pretty graphs (instance --> resource advisors --> buffer cache size advisor - grab a screen print before and after you move things into the other pool and it runs for a while) can make you look good to damagement, too.

I don't think there is anything wrong with grabbing low-hanging tuning fruit from statspack, though. It's just not the be-all and end-all, and sometimes the top usage is just that - not necessarily an indication of anything wrong. But it might be, and is worth looking at in the course of making sure everything is done right - especially if you are having performance problems. It's just a tool - you don't have to not use it just because you use another methodology. It did point you at a suspect table with no PK, right?

When you add the index, everything might get way better anyways. If you are full-table scanning 100K rows just to update 1 row (couldn't quite tell from your post), that is ridiculous. So fix that before you do anything else. There is normally no performance issue regarding which tablespace an index is in (unless you have some more-heavily used device and put it there).

Joel Garry 
http://www.garry.to
 

--
http://www.freelists.org/webpage/oracle-l