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

mccmx_at_hotmail.com wrote:
> > 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

If 90% of the _wait time in seconds_ is for direct path read and write, that is a sign that the effective sort_area_size is much too small, likely resulting in multi-pass sorts. This can be caused by having the pga_aggregate_target set too small. A single session can access up to 5% of the pga_aggregate_target, so you need to keep that in mind when specifying the value. sort_area_size can be used when the workarea_size_policy is set to auto, and Oracle does honor this setting as the new minimum value. sort_area_size can also be specified at the session level - you might try adjusting it to 20MB for _the session_ that is executing the queries to see if that fixes the performance problem.

I can't say that I have seen too many bugs with the Auto features in 10g R2. However, even with the pga_aggregate_target set to 2GB and one client connected to the server, the auto workarea_size_policy still permitted single pass sorts to the temp tablespace, when there was more then enough memory available to the session (roughly 100MB at 5% of total) to perform the sort completely in memory without using the temp tablespace. Manually specifying a sort_area_size of 20MB removed the sort to the temp tablespace, as would a specification of 10MB.

I suggest that you are hiding information from the database by using NOWORKLOAD, and relying on the system to auto-collect statistics.

Can you post your SQL statements and execution plans. Someone on the newsgroup might be able to suggest a small change that will make a large difference. Also, it would be helpful to see the initialization parameters.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Oct 27 2006 - 10:17:58 CDT