CBO doesn't have knowledge about concurrent activities in instance?
- From: Jessica Mao <jmao@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 20:04:04 -0800
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.
Thanks a lot! -Jessica
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » CBO doesn't have knowledge about concurrent activities in instance?
