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

YES.
see example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
pga_aggregate_target                 big integer 25165824

SQL> set autotrace trace exp
SQL> select sum(b1.line) from big b1, big b2   2 where b1.OWNER = b2.owner;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9770 Card=1 Bytes=22)    1 0 SORT (AGGREGATE)
   2 1 HASH JOIN (Cost=9770 Card=96858578004 Bytes=2130888716088)

   3 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=13433076)

   4 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=19403332)

SQL> alter system set pga_aggregate_target=16M;

System altered.

SQL> select sum(b1.line) from big b1, big b2   2 where b1.OWNER = b2.owner;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11617 Card=1 Bytes=22)

   1 0 SORT (AGGREGATE)
   2 1 HASH JOIN (Cost=11617 Card=96858578004 Bytes=2130888716088)

   3 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=13433076)

   4 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=19403332)

When CBO thinks that it can run hash join in memory then cost doesn't change any more. See below for some rather crazy values:

SQL> alter system set pga_aggregate_target=1600M;

System altered.

SQL> select sum(b1.line) from big b1, big b2   2 where b1.OWNER = b2.owner;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8876 Card=1 Bytes=22)    1 0 SORT (AGGREGATE)
   2 1 HASH JOIN (Cost=8876 Card=96858578004 Bytes=2130888716088)

   3 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=13433076)

   4 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=19403332)

SQL> alter system set pga_aggregate_target=6000M;

System altered.

SQL> select sum(b1.line) from big b1, big b2   2 where b1.OWNER = b2.owner;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8876 Card=1 Bytes=22)    1 0 SORT (AGGREGATE)
   2 1 HASH JOIN (Cost=8876 Card=96858578004 Bytes=2130888716088)

   3 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=13433076)

   4 2 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=19403332)

Gints Plivna
http://www.gplivna.eu

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
Received on Fri Oct 27 2006 - 06:51:17 CDT