Thanks Denny, Connor, and Ferenc for your helpful suggestions. Ferenc - I particularly appreciated your insights. This is also a packaged app where I can't tune the SQL. It does no table scans (long story, but that is the way this app works). My logic is that the biggest wait (85% of wait) is "db file sequential read", and the BHR is fairly low, about 80%. So my thought is to increase the buffer, and while I was at it, thought I would try the KEEP and RECYCLE pools. But I find your comment about logical tuning very interesting. Can you explain more, in case I'm missing something basic? Thanks.
Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Tuesday, November 26, 2002 7:29 PM To: Multiple recipients of list ORACLE-L Hi Dennis I try to not think of the pool names as being descriptive of what they should be allocated for. I regard them as pool 1 (default), of which I can configure two other pools, (pool 2 and pool 3). For Siebel applications (probably works similar for PSOFT [Joe, you in on this thread ?] and SAP), knowing the application and what it does, the repository tables, like the tables that define position based access, views, responsibilities, position relationships (team-based visibility in Siebel), broadcast messages, workflow rules and rule items, I put them into a separate smaller but very frequently accessed pool, knowing they are going to get hit at least a few times every minute with a few hundred users logged on. Then I try to identify those tables that DO get FTS, and if I cannot tune the query by placing relevant indices (sometimes it is better to have FTS than large index range scan to reduce logical IO, the big performance killer), put these into a separate pool, and leave the rest in default. Alternatively, the hot smaller tables go into one pool, the indices in another and the rest of the tables stay in default. There are various tricks for this. Oracle 9 makes things easier because you can identify which indexes are beig used, and then not waste your time with the others. Just remember, you will get much further distance from reducing logical IO's than playing with various buffer pools, though there is a minimal argument for playing with buffer pools, once logical IO's have been decreased. Real-life example : using Siebel EIM, by placing EIM tables into separate buffer pools, I saw a small advantage, say 5 - 10 % in buffer cache latch reduction and more efficient use of cached IO. But after tuning the structures so that I reduced logical IO's, I saw a 2000% throughput improvement of EIM, to the amazement of all skeptics on the project (also bumped up initrans and ran multiple parallel streams). So prioritize where you spend your tuning efforts. Reduction of logical IO = biggest bang for buck ! Getting off my soapbox now. Lots to do. Ciao : Ferenc Mantfeld -----Original Message----- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, November 27, 2002 8:30 AM To: Multiple recipients of list ORACLE-L Subject: Using RECYCLE pool? Is anyone using the Oracle RECYCLE buffer pool? What was your criteria to select tables? The application I am considering RECYCLE for doesn't perform table scans, so that eliminates one common suggestion. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mantfield INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
