I think the question "Is nothing sacred?" is an interesting one. Lots of these things we're talking about have been false for a very long time. It's only that people are finally starting to notice them. Product changes are often *not* what's driving "new knowledge." In many cases, the "change" that's taking place is the improvement in the quality of our conclusions.
"Is nothing sacred?" I think it's perfectly legitimate to confront people's (and companies') conjectures with scientific data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -----Original Message----- [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 10:19 AM To: Multiple recipients of list ORACLE-L Jesse, No, nothing in sacred any more. Change is the theme of the day. BTW: did you experiment with caching these tables in the keep_pool?? I've had some real good luck with unindexed tables that are small (in the 1 to 10 block size) that get assigned to the keep pool and retained in memory forever. Also, BTW: I'll disagree with Cary and Hotsos on the costs of a PIO vs a LIO. In my experience it's not such a clear cut distinction. Whenever Oracle needs a block of data that data must be in memory which means that a PIO requires 2 LIO's to fulfill the request and on top of that there may be other memory management routines that get called if an empty data block in memory must be created. All in all it's a very mixed bag that needs to be considered case by case. I believe that was one of the reasons Oracle allows us to configure the cache three ways. Static, seldomly changed tables in the keep pool. Large constantly changing tables in the discard pool. Also to index or not to index are no longer such clear cut item, especially with CBO which loves to ignore indexes. Dick Goulet ____________________Reply Separator____________________ Author: "Jesse; Rich" <[EMAIL PROTECTED]> Date: 11/11/2002 8:58 AM So, there I am, on 8.1.7.2 (and .4) on HP/UX 11.0, with a process that runs 20 minutes out of every hour of the day (despite my protests to it's design). After it starts having problems (go figure), it becomes a priority to speed it up. Thanks to a 10046 trace, we see that the query taking the most elapsed time does FTSs on each of two very small tables (1 block and 4 blocks -- 8K blocksize). These tables are not indexed, as per the official Oracle recommendation. After reading the excellent Hotsos paper "When to index a table" (THANKS, CARY!), I added an index to reduce elapsed time on this query by 50% (150 to 75 seconds in test), proving to me that the paper is valid. And I've only read to page four! OK, first I'm taught by Oracle to look at Buffer Cache Hit Ratios as a measure of performance, then told (and thoroughly convinced) by experts that this is bunk. Now, I found out that the 15% (or 10% or whatever, depending on version) ratio of rows returned to total rows in determining when to use an index in a query is garbage. 1) Why is this? 2) What other pearls of performance wisdom from Oracle Corp should I completely disregard as false? I know there's an Oracle Fallacy website somewhere... It just looks bad on me, our department, and Oracle when, once again, something I've been preaching to our developers as gospel turns out to be completely false. Maybe I'm grumpy because it's snowing on my leaves right now... <sigh> Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Disclaimer: I only said the Packers would be 12-4 this year -- I never said that they couldn't do better! WOO-HOO! :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: 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: Cary Millsap 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).
