Oracle-L: Re: Do programmers tune SQL?

From: Greg Moore <sqlgreg_at_pacbell.net>
Date: Mon, 01 Apr 2002 12:13:24 -0800
Message-ID: <F001.004382D1.20020401121324@fatcity.com>

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: sqlgreg_at_pacbell.net

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: ListGuru_at_fatcity.com (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). Received on Mon Apr 01 2002 - 14:13:24 CST