RE: Cost Based Optimizer

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 20 May 2005 10:11:25 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D0A49F9@MSXVS01.trivadis.com>

Hi Mladen

>Larry, according to what I know, the statement plan depends
>entirely on the statistics (with histograms, of course) and
>availability of usable indexes.

For some operations, like sorts and merge/hash joins, the cost also = depends on the "PGA size". I.e. for manual PGA management on = SORT_AREA_SIZE/HASH_AREA_SIZE. Here an example with a sort:

SQL> ALTER SESSION SET workarea_size_policy =3D manual;

SQL> ALTER SESSION SET sort_area_size =3D 1048576;

SQL> SELECT * FROM sales ORDER BY 1,2,3,4,5;

Execution Plan

---

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D18119)   SORT (ORDER BY) (Cost=3D18119)
    PARTITION RANGE (ALL)
      TABLE ACCESS (FULL) OF 'SALES' (Cost=3D2454)

SQL> ALTER SESSION SET sort_area_size =3D 131072;

SQL> SELECT * FROM sales ORDER BY 1,2,3,4,5;

Execution Plan

---

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D39007)   SORT (ORDER BY) (Cost=3D39007)
    PARTITION RANGE (ALL)
      TABLE ACCESS (FULL) OF 'SALES' (Cost=3D2454)

For automatic PGA management it's similar:

SQL> ALTER SESSION SET workarea_size_policy =3D auto;

SQL> ALTER SYSTEM SET pga_aggregate_target =3D 32M;

SQL> SELECT * FROM sales ORDER BY 1,2,3,4,5;

Execution Plan

---

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D18815)   SORT (ORDER BY) (Cost=3D18815)
    PARTITION RANGE (ALL)
      TABLE ACCESS (FULL) OF 'SALES' (Cost=3D2454)

SQL> ALTER SYSTEM SET pga_aggregate_target =3D 512M;

SQL> SELECT * FROM sales ORDER BY 1,2,3,4,5;

Execution Plan

---

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13245)   SORT (ORDER BY) (Cost=3D13245)
    PARTITION RANGE (ALL)
      TABLE ACCESS (FULL) OF 'SALES' (Cost=3D2454)

Now, in case of automatic PGA management, it's not impossible that the = amount of allocated PGA has an impact on the costs. I'm not saying that = it does happen, in fact I never saw it... anyway this topic is on my "to = be tested" list.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 20 2005 - 04:16:05 CDT