Re: Tuning SQL in Oracle

From: Svend.Jensen <Svend.S.Jensen_at_it.dk>
Date: Fri, 01 Aug 2003 19:39:16 +0200
Message-ID: <3f2aa5b5$0$32495$edfadb0f_at_dread16.news.tele.dk>

Daniel Roy wrote:

> Even if you analyzed all your tables and indices, one more issue you
> might want to consider is to create histograms for the (indexed)
> columns heavily skewed. They can greatly improve the performance with
> the CBO sometimes.
> 
> Daniel
> 
> 

>>On Wed, 30 Jul 2003 18:46:33 +0000, LKBrwn_DBA
>><member30625_at_dbforums.com> wrote:
>>
>>
>>>Hint:
>>>
>>>Analyze all tables and indexes.
>>
>>That's what got me into this. :) My code starting dragging on our
>>production database right after a analyze had taken place. Apparently
>>we were right under a certain level of data before the analyze was run
>>that the new stats took us past. At that point I guess the CBO chose a
>>very bad access path.
>>
>>
>>>http://www.amazon.com/exec/obidos/ASIN/0130123811/qid=1059590327/sr=2-3-
>>>/ref=sr_2_3/103-8017330-2186264
>>
>>Thanks. I just ordered one. This book is a couple of years old though.
>>Has the CBO changed much for 9i?

Histograms are no god if you are a decent programmer and use bind variables. With literal; yes - but the con's are severe for performance.

PS: A mere analyze table | index <<name>> and nothing further [compute| estimate percent|...] will on 8.1.7 (and others?) only analyze the first 1184 rows of a table and 1162 entries in the index. :-(( These few rows merely generate accurate statistics for the hole table nor the full index. Use compute or estimate xx percent. :-)

/Svend
Remove spamkiller [S.] in the reply address. Received on Fri Aug 01 2003 - 19:39:16 CEST