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

Home -> Community -> Usenet -> c.d.o.server -> Re: Does Optimizer use P.A.T to calculate cost..?

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

From: <mccmx_at_hotmail.com>
Date: 27 Oct 2006 07:29:50 -0700
Message-ID: <1161959390.841579.189700@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 Received on Fri Oct 27 2006 - 09:29:50 CDT

Original text of this message

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