Re: Tuning SQL in Oracle
Date: Wed, 30 Jul 2003 22:34:18 +0200
Message-ID: <3f282bbc$0$32507$edfadb0f_at_dread16.news.tele.dk>
jag wrote:
> 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.
>
Hi Jag
Rule number one; The CBO selection of execution path is as well selected
as the statistics make it do. No stats => very poor execution path.
Run stats on all tables, indexes.
Use the dbms_stats package. Or the god old analyze table compute|sample
statistics on table on index.
/Svend
Remove the spamkiller [S.] in reply address.
Received on Wed Jul 30 2003 - 22:34:18 CEST