RE: index parameters and system stats

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 12 Nov 2004 14:27:01 +0100
Message-ID: <2CF83791A616BB4DA203FFD13007824A01E6AA5F@MSXVS02.trivadis.com>

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

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

Received on Fri Nov 12 2004 - 07:22:45 CST