Oracle-L: RE: Do programmers tune SQL?

From: Norrell, Brian <BNorrell_at_QuadraMed.com>
Date: Mon, 01 Apr 2002 16:28:19 -0800
Message-ID: <F001.004385A6.20020401162819@fatcity.com>

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: 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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Norrell, Brian
INET: BNorrell_at_QuadraMed.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------