Having worked mostly as a developer, the myth is that ALL programmers should tune SQL. Poor programmers trying to tune a query just make it unreadable. Good programmers should be able to tune, and depending on the shop, they should be able to do it better than an DBA in some situations (There are many places where the programmers have a better understanding of how the tables interrelate than the DBA)
Note I said GOOD programmers. Now, if you have a good programmer, the more information available, the better. True, buffer cache hit may not help him much, but knowing the types of indexes and strategies will help in many cases. Having the information available lets the programmer at least present and discuss the issue intelligently, do you prefer "Help, this query runs to slow" or "This query doesn't run as fast as it should. Index x would help, but it is not being used. I'm checking a low frequency value, but it looks like it ends up in the same histogram buckets as a high frequency code." Again, note that I said good programmers... :) -----Original Message----- Sent: Monday, April 01, 2002 2:13 PM To: Multiple recipients of list ORACLE-L I think there's a new myth: Programmers should tune SQL. Harrison says his book is for developers, but consider what he actually covers. Chapter 8, Tuning Table Access, covers many topics that are for DBA's, not developers: -- hit rate in the buffer cache -- db_file_multiblock_read_count -- number of blocks used for the table -- size of data blocks -- depth of index -- histograms -- use of ANALYZE and dbms_stats -- subtle points of index creation -- types of indexes, strategies -- fast full index scan -- bitmap_merge_area_size parm -- alter table minimize records_per_block -- setting up hash clusters -- IOT's, configuring the overflow statement -- periodic rebuild of indexes -- fast_full_scan_enabled=true parm -- lowering the high water mark -- optimizing PCTFREE andd PCTUSED That's from just *one* chapter. Oh sure, he also devotes a few pages to avoiding accidental full table scans caused by SQL that disables an index, etc. But how often are developers going to tune SQL by using the rest of the stuff in this chapter? The root problem is, the phrase "tune SQL" is a myth. Sure the SQL runs slow, then you tune it and it runs faster. But tuning it often requires DBA knowledge, something DBA's may take for granted, but for developers it's a huge area they have no familiarity with at all. There's a specific set of things you can ask developers to do, but there's another set of things that are required and that you can't reasonably ask developers to do. Harrison's idea that you can get developers to "tune SQL" is a myth. He's really written a DBA book that delves into tuning SQL, which is a reasonable goal. But to do the reverse -- asking a developer to delve into tuning SQL -- means they have to come to grips with DBA topics, and that's not a reasonable thing to ask. He should take a subset of his book (perhaps a third) and call it SQL Tuning For Developers, and give the current book an accurate title ... SQL Tuning for DBA's. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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: Norrell, Brian INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
