RE: index parameters and system stats

  • From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • To: <ryan_gaffuri@xxxxxxxxxxx>
  • Date: Fri, 12 Nov 2004 14:27:01 +0100

Hi Ryan

>I could have swarn I read somewhere(yes I forgot where again) that if I =
use system stats the
>following parameters are ignored by oracle. Is this correct?

NO! They just made the two parameters less important. In fact if you =
gather system stats (what I strongly suggest to do!!) the default values =
are usually good. I rarely had to tweak them with system stats in 9i... =
This is good, because especially optimizer_index_cost_adj has some =
important drawbacks!

It's quite easy to show it...

rem
rem the database version
rem

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

rem
rem system stats are gathered
rem

SQL> select pname, pval1 from sys.aux_stats$ where sname =3D =
'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     818.501996
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                           20.888
MREADTIM                           16.818
CPUSPEED                              987
MBRC                                    7
MAXTHR                             346112
SLAVETHR

rem
rem build test table and index
rem

SQL> create table t as select * from dba_objects;
SQL> create index i on t (object_id);

SQL> set autotrace trace exp

> optimizer_index_cost_adj

rem=20
rem index range scans are cheaper with smaller values, in this case=20
rem the cost of the query goes from 50 to 15.
rem

SQL> alter session set optimizer_index_cost_adj =3D 100;
SQL> select * from t where object_id between 1000 and 1500;

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D50 Card=3D501 =
Bytes=3D88677)
  TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3D50 Card=3D501 =
Bytes
    INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=3D3 Card=3D501)

SQL> alter session set optimizer_index_cost_adj =3D 30;
SQL> select * from t where object_id between 1000 and 1500;

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D15 Card=3D501 =
Bytes=3D88677)
  TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3D15 Card=3D501 =
Bytes
    INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=3D3 Card=3D501)

>optimizer_index_caching

rem
rem nested loops are cheaper with larger values, in this case the cost
rem of the query goes from 71 to 68, notice that there's a small=20
rem difference because the FTS is the most important contributor...
rem

SQL> alter session set optimizer_index_cost_adj =3D 100;
SQL> alter session set optimizer_index_caching =3D 0;
SQL> select t1.* from t t1, t t2 where t1.object_id =3D t2.object_id and =
t1.object_name =3D 'T';

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D71 Card=3D3 Bytes=3D570)
  NESTED LOOPS (Cost=3D71 Card=3D3 Bytes=3D570)
    TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3D68 Card=3D3 Bytes=3D531)
    INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=3D1 Card=3D1 Bytes=3D13)

SQL> alter session set optimizer_index_caching =3D 100;
SQL> select t1.* from t t1, t t2 where t1.object_id =3D t2.object_id and =
t1.object_name =3D 'T';

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D68 Card=3D3 Bytes=3D570)
  NESTED LOOPS (Cost=3D68 Card=3D3 Bytes=3D570)
    TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3D68 Card=3D3 Bytes=3D531)
    INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=3D0 Card=3D1 Bytes=3D13)


HTH
Chris

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

Other related posts: