You did not mention the Oracle version you are using (a cardinal sin when
asking for advice). If you are on Oracle 9, use system statistics to give
the optimizer exactly that information about the server load. Oracle 10
expands on that.
At 09:04 PM 3/4/2004, you wrote:
>Dear Gurus,
>
>I have this query joining 2 tables. Before analyze table, optimizer chooses
>index range scan + nested loops, after analyze it switched to FTS + hash
>join. As you can see, in sqlplus the latter does come back 10+ times faster
>-- meaning CBO is making the right decision.
>
>But when my multi-threaded application calls the same query, all threads
>start doing same FTS on same tables. Things become much worse. Plus those
>tables block are not cached, difference in speed gets bigger from index-lead
>execution plan when I rerun the query.
>
>I'm wondering, besides ship the correct execution plan w/ the product, and
>change product to add hint in query, and change product to put in the
>beginning something like 'alter session set optimizer_index_cost_adj=1'. Is
>there any other way to force index scan? Will 10G or whatever be smarter in
>this area?
>
>If this has been mentioned before, please kindly point me to the archive or
>URL.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Mar 04 2004 - 22:14:35 CST
