Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Does Optimizer use P.A.T to calculate cost..?
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=1492564Bytes=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=1492564Bytes=13433076)
Gints Plivna
http://www.gplivna.eu
Received on Fri Oct 27 2006 - 08:31:28 CDT