Dennis as you know, there is no 'follow these steps to get a better performing application' guide when it comes to tuning. An intimate knowledge of what the application does is a must. I sell myself (tried the street corners but was not getting much intrest) as a Siebel performance tuning specialist, so when customers say 'Oh, you are an Oracle DBA !', I respond with 'No, Oracle DBA is just one of the things I do in order to get my job done'. there are plenty of DBA's out there, (and DBB's too), but understnading how the application (in my case Siebel) works and what it is trying to accomplish from a functional perspective helps me to know immediately what is the framework of limitations I can work in. For instance, Siebel is written for RBO, so when someone comes spouting partitions and bitmap indexes, I buzz them out on try 1.
now for Siebel specific EIM (Enterprise Integration Manager) type tuning , when I see that index range scans are killing me, I try to reduce the batch size first so that it will not have to go through as many records per value (think of a batch size of 20,000 records where it is doing a correlated subquery on just the batch_id). Now change this into 100 batches of 200 rows each, and immediately you have a huge saving in logical IO, since each time excpet the first iteration, the index blocks and table blocks should be found in DBBC (Also see Cary's paper on www.hotsos.com which goes into deeper details on the latches needed and the recursive calls for buffer hits.) Other things include looking at SQL where you can see it is using an index to look up a row in the table to get a single value (column). In this case, for a large load, it may be beneficial to recreate this same index with the column concatenated on the end, and avoid the table lookup altogether. Also knowing EXACTLY how RBO works (there are only about 20 rules and in reality only 5 or 6 get used in an application), will help you to know when it may even be beneficial to DROP an index (gasp ! can he be serious ? Youbetcha ! ). anyway, that is it for today, class dismissed. Have a great day ! Ferenc Mantfeld -----Original Message----- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 28, 2002 3:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: Using RECYCLE pool? 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). -- 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).
