Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> CBO doesn't have knowledge about concurrent activities in instanc e?
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