RE: CBO doesn't have knowledge about concurrent activities in instanc e?
- From: Jessica Mao <jmao@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Mar 2004 11:25:46 -0800
Thank you sir, for tolerating with me and my questions. I'm on 9204 and I
haven't touched system statistics before. Now I'm already at
tahiti.oracle.com and reading the manuals... I'll let you know once I find
it working on my DB. Thanks a lot for the kick start. -Jessica
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
Sent: Thursday, March 04, 2004 8:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: CBO doesn't have knowledge about concurrent activities in
instanc e?
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
