RE: [Q] Optimizer_mode and performance?

Hi L and Lists,

1. Yes, it's correct. But maybe it's superfluous to support histogramms for all indexed columns.
2. You need to consider upgrading of your hardware as a last thing
3. Look at v$sqlarea and find out top of the worst sql's with highest disk_reads per execution. Analyze results, if you find out that the sql's are bad, then you have to insist that these sql's must be rewriten by your vendor. You are not expected, to support both CBO and RBO concurrently. It's the wrong way.

Ed

>
>
> We have ORACLE 7.3.4.4 on SUN SPARC Solaris 2.6. Our ORACLE database
> running third party application on it. Recently, due to data growing
> (2i.5GB data on data tablespace), user starting to compliant
> performance
> slow on some SQL statements. The "optimizer_mode" we use
> are "choose" and
> I "analyze" the schema every week use following statements:
>
>
> exec dbms_utility.analyze_schema('USER1',
> 'COMPUTE',NULL,NULL,'FOR ALL
> INDEXED COLUMNS');
> exec dbms_utility.analyze_schema('USER1', 'COMPUTE');
>
>
>
> I turn on "tkprof" to trace the third party application (we
> DON'T have
> source code). I found some SQL statements run quickly under
> "rule" mode.
> After I change database "optimizer_mode" to "rule", users
> complaint other
> SQL statements run very slow. I report this problem to third party
> company. The engineer continue said following:
>
> 1. Analyze schema (actually we did)
> 2. Buy high speed CPU and high speed Hard disk RAID.
>
> My questions are:
>
> 1. Does my "analyze" statements collect NOT enought information?
>
> 2. user better hardware to fix software problem is correct way?
>
> 3. any other suggestion?
>
> Thanks.
>
>
> --------
> Think you know someone who can answer the above question?
> Forward it to them!
> to unsubscribe, send a blank email to
> oracledba-unsubscribe_at_LAZYDBA.com
> to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
> Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Received on Mon Dec 04 2000 - 02:39:52 CST