RE: Do programmers tune SQL?

I agree with much of what you say, but, disagree (respectfully, of course ;-)) with other points. In line.

>
> I think there's a new myth: Programmers should tune SQL.
>

I think they should have a core understanding of good SQL practices from a performance standpoint, and, they should be expected to understand some basics. Whether the average developer gives a flip, it varies.

> 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

I would expect a *good* developer to understand this and how it impacts choice of execution plans (and why, just maybe, that FTS isn't a bad thing).

> -- 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

Should also be aware of this and how it impacts their SQL. Not necessarily when to use what strategy, but the benefits of different approaches and how it impacts their SQL. I don't think it's too much to ask a developer to at least have an idea.

> -- fast full index scan

Developers should definitely be aware of IFFS's, how to use them, and when to use them. Don't think it's too much to ask a developer to understand IFFS's.

> -- 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?

I don't have the book handy, and maybe this chapter isn't a good example, but much of what he says in the book should be able to be picked up by a *good* developer. Simple things as when to use a correlated versus non-correlated query, how the CBO can transform statements (Oracle doc's are actually pretty good for this). Example, come in this morning and a query had been running since Saturday afternoon. The developer had 10 or 15 correlated sub-queries and no supporting indexes. These tables were between 90K and 500K rows each. Main portion of the query returned 500K rows, meaning each of the correlated sub-queries were going to be executed 500K times, each doing FTS's every time. Now, Harrison's book goes into some detail about when to use IN versus EXISTS, NOT IN versus NOT EXISTS, correlated vs non-correlated, etc. It doesn't require much DBA knowledge to get a grip on that. Anyway, took the sub-queries and turned into a single UNION (ALL) of the 10 tables using an IN clause referencing it. This resulted in a single FTS on each of the tables with the results driving the rest of the query. Dropped from 40 hours and running to under 30 seconds. So, with a basic understanding of how to handle sub-queries, when to use what, and not requiring any DBA knowledge at all, the developer could have been expected to write the SQL properly. And Harrison's book would help someone determine that how to that. And in talking to the developer, she picked up pretty quickly why the "new" approach was better than the existing one.

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

For advanced tuning, knowledge of some DBA topics is helpful. But, in those cases where I run into SQL that was simply coded in a way that gave it no chance to perform, a book like Harrison's could help the developer avoid such pitfalls.

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

Disagree :-) Ok, noting the difference between developer and good developer ;-)

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

Yeah, the book does get into topics out of the scope of the general developer and more in line with a DBA's type of skills. I don't know that I would say only a third of the book is applicable to developers. Anyway, I don't think we shouldn't expect the developers to be able to write somewhat efficient SQL. And Harrison's book helps in that regard. Can we expect that of developers? Depends upon the shop. I've seen lots of developers who don't give a flip, but there are many of them out there, albeit a minority, who are quite skilled at SQL tuning. And others who want to learn. And many who teach the DBA's what an outer join is ;-)

And I guess one of the areas that cause us to think differently is that you are probably thinking about most developers in general and I'm referring more to the exception. But, many of the good SQL programming practices and tuning *can* be done without knowledge of DBA topics. Is it too much to ask the developers to learn these things? I don't think it is. How many are actually interested is another story ;-) But I really think more are interested than we give credit for. And it's contagious. Once they realize the performance gains they might see by simply writing a query in a different manner, they kind of get caught up in it. And once you light that fire under one or two, it starts to spread.

So, I don't think it's a myth. A developer should, in an ideal world, be able to tune SQL. And they should be able to do a pretty good job of it without understanding DBA topics.

Larry G. Elkins
elkinsl_at_flash.net

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.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).