Re: SQL Tuning

"Pat" <pat_at_microsoft.com> wrote in message news:dc8bra$1lv3$1_at_news.hgc.com.hk...
>
> "IANAL_VISTA" <IANAL_Vista_at_hotmail.com> wrote in message
> news:Xns96A053E642293SunnySD_at_68.6.19.6...
>> "Pat" <pat_at_microsoft.com> wrote in news:dc89co$1kng$1_at_news.hgc.com.hk:
>>
>> > Hi all,
>> >
>> > My new job requires me to tune lots of SQL which run for long time.
>> > I'm not keen in this area and would like to get some idea how to start.
>> >
>> > I read some books on EXPLAIN_PLAN, TKPROF, etc. but those are
>> > not easy to understand for me (I'm new to Oracle !). Although I
>> > generate some information from the PLAN_TABLE, I still have
>> > difficulties in understanding those terms like RANGE SCAN,
>> > MERGE SORT, etc.. Besides, even I found some tables were
>> > queried by full table scan, how could I force it to use the index?
>> > From some books, it said even CBO was used, Oracle may
>> > still use full table scan for some tables if, based on the statistics
>> > analysed, Oracle found it's worth to do so.
>> >
>> > Any good web site have expertise in this tuning area?
>> >
>> > thx.
>> >
>> >
>> >
>>
>> Book -> "SQL Tuning"
>>
>> # Paperback: 336 pages
>> # Publisher: O'Reilly; 1 edition (December 1, 2003)
>> # Language: English
>> # ISBN: 0596005733
>> # Product Dimensions: 9.2 x 7.1 x 0.8 inches
>> # Shipping Weight: 1.2 pounds. (View shipping rates and policies)
>> # Average Customer Review: based on 10 reviews. (Write a review)
>>
>> It is a HEAVY read but his approach works.
>>
>> Alternatively
>>
>> Oracle Performance Tuning 101
>>
>> # Paperback: 404 pages
>> # Publisher: Osborne/McGraw-Hill (May 29, 2001)
>> # Language: English
>> # ISBN: 0072131454
>> # Product Dimensions: 9.2 x 7.4 x 1.2 inches
>> # Shipping Weight: 1.9 pounds.
>> # Average Customer Review: based on 29 reviews. (Write a review)
>
> Thanks. Which one is good for beginners?
>
>

I keep this little booklet always at hand, it's VERY good and highly recommended :

Oracle SQL Tuning Pocket Reference
http://www.oreilly.com/catalog/orsqltunpr/

The differences between RBO and CBO are explained, as the different join types (nested loops, hash, sort merge, ...), the use of hints, DBMS_STATS etc.

I can say that it's one of the best Oracle books I ever bought.

HTH
Matthias Hoys Received on Wed Jul 27 2005 - 14:25:57 CDT