By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

Tuning SQL in Oracle

P: n/a
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.

Jul 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

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
Jul 19 '05 #2

P: n/a
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?
Jul 19 '05 #3

P: n/a
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?

Jul 19 '05 #4

P: n/a
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?

Jul 19 '05 #5

P: n/a
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.
Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.