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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 26 Oct 2006 05:44:56 -0700
Message-ID: <1161866696.918368.91130@e3g2000cwe.googlegroups.com>

mccmx_at_hotmail.com wrote:
> Oracle 10.2.0.2 on W2K3
>
> I have increased our PGA_AGGR_TARGET from 90Mb to 200Mb and some of our
> batch jobs have started to do Full Table Scans where they were using
> Index lookups before....
>
> Some of these jobs have gone from 3 minute execution to 49 minute
> execution.
>
> Does Oracle use the size of the PGA when calculating cost for a
> query...?
>
> Matt

pga_aggregate_target indirectly controls several other parameters, including sort_area_size and sort_area_retained_size. sort_area_size and sort_area_retained_size influence the calculated cost of perfoming full table scans.

Time to start investigating what is happening, a 10046 trace at level 8 will help. Also, take a look at the output of this SQL statement: SELECT
  NAME,
  VALUE
FROM
  V$PARAMETER
ORDER BY
  UPPER(NAME); Do any of the parameters have unexpected values?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Oct 26 2006 - 07:44:56 CDT