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: Gints Plivna <gints.plivna_at_gmail.com>
Date: 27 Oct 2006 06:31:28 -0700
Message-ID: <1161955888.286893.243980@i42g2000cwa.googlegroups.com>


m..._at_hotmail.com wrote:
> > 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:
>
> The 10046 hasn't helped. It has just shown me that the plan has
> changed. I've done some tests and I can confirm that I can get much
> more stable execution times if I use the old method of manually
> specifying *_area_size parameters.
>
> I've seen advice on this newsgroup which says that you can set the PGA
> target to a very high value as long as you have the memory available.
>
> But I am seeing that a value that is too high can cause instablity in
> execution plans.
>
> Matt

Bigger value of pga_aggregate_target makes cost of hash_joins smaller, as you can see in my previous example. So probably instead of nested loops you get hash joins or merge joins now. See below also example for merge joins for the same table and statement: SQL> alter system set pga_aggregate_target=25165824;

System altered.
SQL> ed
Wrote file afiedt.buf

  1 select /*+ use_merge (b1, b2)*/ sum(b1.line) from big b1, big b2   2* where b1.OWNER = b2.owner
SQL> / Execution Plan


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

   1 0 SORT (AGGREGATE)
   2 1 MERGE JOIN (Cost=42787 Card=96858578004 Bytes=2130888716088)

   3    2       SORT (JOIN) (Cost=23336 Card=1492564 Bytes=19403332)
   4    3         TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
Bytes=19403332)
   5    2       SORT (JOIN) (Cost=19451 Card=1492564 Bytes=13433076)
   6    5         TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
Bytes=13433076)

SQL> alter system set pga_aggregate_target=10M;

System altered.

SQL> select /*+ use_merge (b1, b2)*/ sum(b1.line) from big b1, big b2   2 where b1.OWNER = b2.owner
  3 /

Execution Plan


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

   1 0 SORT (AGGREGATE)
   2 1 MERGE JOIN (Cost=52382 Card=96858578004 Bytes=2130888716088)

   3    2       SORT (JOIN) (Cost=30190 Card=1492564 Bytes=19403332)
   4    3         TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
Bytes=19403332)
   5    2       SORT (JOIN) (Cost=22193 Card=1492564 Bytes=13433076)
   6    5         TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
Bytes=13433076)

Gints Plivna
http://www.gplivna.eu Received on Fri Oct 27 2006 - 08:31:28 CDT

Original text of this message

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