Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> CBO doesn't have knowledge about concurrent activities in instanc e?

CBO doesn't have knowledge about concurrent activities in instanc e?

From: Jessica Mao <jmao_at_portal.com>
Date: Thu, 4 Mar 2004 20:04:04 -0800
Message-ID: <18019F9E2EC47849BE29AB2FBA93934812E17E@CUP1-EXBE1.portal.com>


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_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:02:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US