Tuning SQL in Oracle

[jag]

I have some fairly complex queries (joins to 20 tables etc) that run
fine using the rule based optimizer, but simply die using the CBO. Can
anyone offer any hints or point me to a book, web resource, etc. that
will help me tune these correctly using the CBO? I'm using /*+ Rule*/
as a stopgap measure because we're running 8.1.7 right now, but our
move to 9i is coming soon, so I really need to get these running
efficiently using cost based optimization.

Thanks in advance for any help you can offer.

[LKBrwn_DBA]

Hint:

Analyze all tables and indexes.

http://www.amazon.com/exec/obidos/AS...590327/sr=2-3-
/ref=sr_2_3/103-8017330-2186264

--
Posted via http://dbforums.com

[jag]

On Wed, 30 Jul 2003 18:46:33 +0000, LKBrwn_DBA
<me*********@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/AS...590327/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?

[Jim Kennedy]

Hard to know if we don't see the explain plan and tkprof outputs.(and the
sql)
Jim

"jag" <7b********@sneakemail.com> wrote in message
news:n8********************************@4ax.com...

On Wed, 30 Jul 2003 18:46:33 +0000, LKBrwn_DBA
<me*********@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/AS...590327/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?

[Daniel Roy]

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
<me*********@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/AS...590327/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?

[jag]

On Thu, 31 Jul 2003 00:34:56 GMT, "Jim Kennedy"
<kennedy-down_with_spammers@no_spam.comcast.net> wrote:

Hard to know if we don't see the explain plan and tkprof outputs.(and the
sql)
Jim

Of course. I wouldn't want to subject anyone else to that. One is
enough. :)

That's why I was just asking for general hints and book
recommendations. Fortunately using + RULE has bought me enough time to
allow me to get some studying in. I suspect I'll have a lot better
knowledge of the CBO and Oracle internals once I'm done and that's a
great thing.

My sincere thanks to everyone responding.