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

Home -> Community -> Usenet -> c.d.o.tools -> Re: question about analyze / cost-based optimizer

Re: question about analyze / cost-based optimizer

From: Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid>
Date: 2000/07/20
Message-ID: <0a04f8cd.7c3b5fd9@usw-ex0102-015.remarq.com>#1/1

Jonas Malmsten <jonas_at_malmsten.net> wrote:
>Check what queries that run extremelly slow and force rulebased
 on
>those queries using:
>
>select --+ RULE ....
>
>instead of
>
>select ....
>
>That should solve your problems for now. Then reconstruct your
>database/queries so that they go fast also for costbased
 approach.
>
>//Jonas
>
>
>In article <3976A026.E526A366_at_beusen.de>,
> Stephan Born <stephan.born_at_beusen.de> wrote:
>> Hello group,
>>
>> my company is developing applications for the oracle-database
 since
>> oracle version 5.
>> Despite the long time we did not use the analyze-tool,
 therefor the
>> optimizer does
>> its optimization by RULE.
>>
>> Now a customer of our product does every night analyze all
>> application-schemas
>> with the following command
>>
>> begin
>> dbms_utility.analyze_schema (user, 'COMPUTE');
>> end;
>>
>> and complains about the bad performance of our product. Other
>> applications
>> (they didn't tell us which one) shall run at least with the
 same
>> speed..if not better!!!
>>
>> We did the same on our test-systems.... with Oracle 7.3.4 and
 Oracle
>> 8.0.5.
>>

If I was developing an application for sale I would be hinting every SQL statement in it OR I would give the customer the ability to hint the SQL as necessary. Depending on the application some SQL statements may need to be hinted differently for different customers based on setup and data.

In the example posted I believe it is the hash join that is the problem. Hash joins were really intended for use with warehouses and not in OLTP systems. Because Oracle has chosen the hash join it will probably ignore the first_rows hint which I find preferable to RULE since on some future release the rule based optimizer will be gone. You can use the alter session command to set hash_join_enabled=FALSE for the session or set them off via the init.ora for the whole instance if possible. The combination of ORDERED with USE_NL can also normally be used to eliminate hash joins if altering the session is out.


Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 20 2000 - 00:00:00 CDT

Original text of this message

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