Oracle-L: RE: Do programmers tune SQL?

From: Orr, Steve <sorr_at_rightnow.com>
Date: Wed, 03 Apr 2002 17:28:20 -0800
Message-ID: <F001.0043A8BF.20020403172820@fatcity.com>

Whether a programmer tunes SQL depends on their answer to another question, "Does the programmer care about overall application performance?" If they do and a significant part of the application involves a database then they need to get on a professional development track to become a "Database Programmer" just like other programmers may need to become "Network Programmers," "Real Time Embedded Systems Programmers," "Internet Programmers," or, for those old Mainframe heads, "Systems Programmers." If they don't want to be bothered with such stuff then they'll remain mere programmers and never progress to Developers, Software Engineers, Systems Analysts, System Architects, and other gods. IMHO :-)

Programming for the database in not that much of a specialty and anyone who wants to be a pro should get some solid experience with SQL. If you just read Harrison's book then you may think that SQL tuning is overwhelming but it really doesn't have to be that way. If you were to distill down SQL tuning to the basics then you could create a programmers guide to SQL that would be simple, concise, (less than 10 pages) and easy to understand. I wouldn't expect every programmer to come up with SQL so good that there's no room for improvement but... I do expect them to make an effort. I've seen so much sloppy SQL where it was obvious that all the programmer cared about was getting an expected result set against limited data and then moving on to the next function to program. If someone has good skills for producing efficient C code, then those same skills can easily be applied to SQL... actually SQL is really a lot easier.

One approach is to create a database API to centralize all your database calls and avoid embedded SQL scattered throughout the code which is difficult to maintain or tune. With all database calls passing through the API, skilled database programmers can tune database access without breaking the application.

But there is a point at which SQL tuning becomes overly obtuse, obscure, or esoteric, especially for Oracle-specific or other database-specific tuning. That's when the DBA comes in. For instance, I think it should be rare that the programmer be concerned with using Oracle hints. The cost based optimizer is getting good enough now that we can rely on it a great deal for providing adequate SQL execution paths... just as long as the SQL isn't sloppy to begin with. If we introduce too many hints or other obscure SQL tuning efforts then we create a maintenance burden because the data changes and today's good SQL statement may become tomorrow's pig.

Just today I saw a query with an 11 table join and 3 of the 11 tables referenced were the same table but with different aliases. Not only could this have been down with just one join to that table, some of the other join tables were not needed altogether. On top of that, to limit the size of the result set they were using distinct because they couldn't figure out how to do it otherwise. Ugggh!! That's what happens with a programmer thinks he shouldn't have to be "bothered" with developing good SQL technique.

Steve Orr
Bozeman, MT

-----Original Message-----
Sent: Monday, April 01, 2002 1: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: Orr, Steve
INET: sorr_at_rightnow.com

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 Wed Apr 03 2002 - 19:28:20 CST