Oracle-L: RE: Do programmers tune SQL?

From: Kimberly Smith <ksmith2_at_myfirstlink.net>
Date: Mon, 01 Apr 2002 18:08:19 -0800
Message-ID: <F001.004386A7.20020401180819@fatcity.com>

Well, I cannot speak on the book but you can tune SQL statements. I think a developer should be aware of indexes and how they are used. I think they should be aware of the CBO and how stats are needed. How to generate their stats. I think rebuilding of indexes are not beyond their realm of knowledge. They should be familiar with IOT's. They have to know the in's and out's of their language they are developing in and I believe that SQL is just one of them. The more senior they are, the more I expect of them.

I also believe that part of me coming in as an 'Oracle expert' is to teach and guide. And I do that every time I move to a new site. So far I have seen no resistance. Granted, I have had the one or two idiots per site that just don't get it but their problems go way beyond writing poor SQL.

I am not speaking from a wish list. I have seen in in action. I have had developers come to me and say, I think we need an index here and low and behold they have been right. We loaded a whole bunch of data once and the stats caused a query to go wacky cause of the way it was written and the developer picked up on this. Together we decided to take the stats off until he rewrote the query. All was fine. I could give you many more cases but this is already way more then I wanted to type. Just on a rant roll.

I so believe that you have to work with your developers. I also know that a lot of DBA's out there will not or cannot do this. Some just do not have the ability to communicate or teach. But then you cannot turn around and critize your developers. Most of what you learn is from experience, not from a book (there is that whole OCP vs experience fight again) and you, as the DBA, tend to have it. So you should be able to provide better guidelines then a book.

-----Original Message-----

Sent: Monday, April 01, 2002 12: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: Kimberly Smith
INET: ksmith2_at_myfirstlink.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