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