Re: Does Optimizer use P.A.T to calculate cost..?

<mccmx_at_hotmail.com> wrote in message
news:1161959390.841579.189700_at_f16g2000cwb.googlegroups.com...
>> Now that you have the 10046 trace at level 8, start analyzing the trace
>> file. Scan through the trace file and look for the wait events
>> associated with each SQL statement - what are the causes of the delays.
>> Does it appear that the temp tablespace is being heavily used for
>> large sorts?
>
> Yeah 90% of the waits are associated with direct path read and write
> caused by large HASH joins.
>
>> Have you executed a command to collect server hardware system
>> statistics, something like this:
>> EXEC DBMS_STATS.GATHER_SYSTEM_STATS ('interval', interval => 60)
>
> No, I rely on NOWORKLOAD system statistics - the default behaviour.
>
>> Also, do you frequently collect table and index statistics, or just
>> relying on the automatic collection of statistics in 10g?
>
> No - we use the default 10g auto stats job
>
>> You can influence the apparent cost of full table scans compared with
>> index lookups by modifying the various initialization parameters, such
>> as optimizer_index_caching,
>> optimizer_index_cost_adj, and optimizer_features_enable, in addition to
>> the parameters such as sort_area_size, sort_area_retained_size, and
>> db_file_multiblock_read_count. It is also possible to add first row
>> hints to the SQL statements to essentially force Oracle to use index
>> access.
>
> I want to move away from using the 'old' method of manually specifying
> these parameters. However the more I use 10g the more I realise that
> the 'auto' features are full of bugs.
>
>> On my system, I set a fairly large pga_aggregate_target, and force
>> various parameters, including sort_area_size, to a high minimum value,
>> to help reduce the chance of a sort to the temp tablespace. Automatic
>> adjustment of settings is a nice feature, but specifying appropriate
>> minimum values can also help.
>
> I thought Oracle totaly ignored the *_Area_Size values if
> workarea_size_policy=AUTO...
>
>> Have you reviewed the SQL statements to make certain that they are
>> written efficiently? Some of those SQL statements that I brought over
>> from my Oracle 8.1.7.3 system acquired terrible plans when run against
>> my Oracle 10.2.0.2 system. Rewriting the SQL statements into
>> equivalent forms may help reduce the run times.
>
> Yeah we have spent a long time bringing the execution time of these
> SQLs down to the level they are at now. But it appears that when they
> are run through the application server, the execution time differs
> based on how much Oracle has decided to allocate to the app server PGA
> at the time. This is really frustrating when diagnosing performance
> problems.
>
> Matt
>

Matt,

What is the value of your optimizer_mode init parameter ? You might try setting it to FIRST_ROWS, this can help speed-up slow running queries by favouring indexed access and nested loop joins over FTS and hash joins.

HTH
Matthias Received on Fri Oct 27 2006 - 10:28:46 CDT