(1) In 9i, if gather_system_stats is run, the plan of a SQL will show CPU cost. When delete_system_stats is run (or if gather_system_stats was never run), the plan will not, with a line saying "Note: cpu costing is off". Whether you have system stats strictly corresponds to whether sys.aux_stats$ table is populated. (2) In 10g, we can't get an empty sys.aux_stats$, even if catupgrd.sql is not run (at least not run by us) in the case a 10g database was directly installed, not upgraded to, and even if the table is manually truncated and the DB bounced. A SQL's plan always shows CPU cost even after sys.aux_stats$ is truncated, and after delete_system_stats procedure was run. In 10g, that procedure, in spite of the name, only deletes workload system stats (if collected), leaving behind noworkload system stats. (3) For a given SQL, cpu_costing / no_cpu_costing hints enable / disable system stats (i.e. CPU costing) in both Oracle versions. Setting _optimizer_cost_model to cpu / io does the same, respectively. (Not sure what _optimizer_system_stats_usage does. There's no effect in my test.) Oracle 9.2.0.5 set autotrace on -> plan doesn't show CPU Cost: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=4) 1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (FULL) OF 'T' (Cost=5 Card=9 Bytes=36) Explain plan shows it: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 5 (20)| |* 1 | COUNT STOPKEY | | | | | | 2 | TABLE ACCESS FULL | T | 9 | 36 | 5 (20)| _optimizer_system_stats_usage can be 0 (default) or 1. But no change is found anywhere by changing this param. 10053 trace does not list this param. _optimizer_cost_model set to io (default choose) changes CBO to no CPU cost, as does cpu_costing hint. dbms_stats.gather_system_stats SQL trace shows DELETE FROM SYS.AUX_STATS$ WHERE SNAME LIKE 'SYSSTATS_%' DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_INFO' INSERT INTO SYS.AUX_STATS$ VALUES ( 'SYSSTATS_INFO','STATUS', NULL ,:b1 ) <-- :b1 is "NOWORKLOAD" INSERT INTO SYS.AUX_STATS$ VALUES ( 'SYSSTATS_INFO','DSTART', NULL ,:b1 ) <-- :b1 is "08-09-2006 14:10" INSERT INTO SYS.AUX_STATS$ VALUES ( 'SYSSTATS_INFO','DSTOP', NULL ,:b1 ) <-- :b1 is "08-09-2006 14:10" INSERT INTO SYS.AUX_STATS$ VALUES ( 'SYSSTATS_INFO','FLAGS',:b1, NULL ) <-- :b1 is 1 dbms_stats.delete_system_stats SQL trace shows DELETE FROM SYS.AUX_STATS$ WHERE SNAME LIKE 'SYSSTATS_%' Even if aux_stats$ table is truncated or renamed, followed by flushing shared pool and compiling DBMS_STATS package body and DBMS_STATS_INTERNAL package and body which become invalid, explain plan still remembers the old setting, i.e. CPU cost is still there. But after database bounce, CPU cost is finally off! Oracle 10.2.0.1, freshly installed CPU 757 MHz, 256MB, AMD Duron SQL> select * from aux_stats$; SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- ------------------------------ SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 03-10-2004 01:02 SYSSTATS_INFO DSTOP 03-10-2004 01:02 SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 904.86697 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR SQL> exec dbms_stats.gather_system_stats PL/SQL procedure successfully completed. SQL> select * from aux_stats$; SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- ------------------------------ SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 06-02-2007 21:30 SYSSTATS_INFO DSTOP 06-02-2007 21:30 SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 459.046 SYSSTATS_MAIN IOSEEKTIM 30.207 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR SQL> exec dbms_stats.gather_system_stats('interval', 1) PL/SQL procedure successfully completed. SQL> select * from aux_stats$; SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- ------------------------------ SYSSTATS_INFO STATUS AUTOGATHERING SYSSTATS_INFO DSTART 06-02-2007 21:34 SYSSTATS_INFO DSTOP 06-02-2007 21:35 SYSSTATS_INFO FLAGS 0 SYSSTATS_MAIN CPUSPEEDNW 459.046 SYSSTATS_MAIN IOSEEKTIM 30.207 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR SYSSTATS_TEMP SBLKRDS 104994 SYSSTATS_TEMP SBLKRDTIM 721610 SYSSTATS_TEMP MBLKRDS 5135 SYSSTATS_TEMP MBLKRDTIM 64500 SYSSTATS_TEMP CPUCYCLES 6972 SYSSTATS_TEMP CPUTIM 15188 SYSSTATS_TEMP JOB 1 SYSSTATS_TEMP CACHE_JOB 2 SYSSTATS_TEMP MBRTOTAL 143553 22 rows selected. SQL> select * from aux_stats$; SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- ------------------------------ SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 06-02-2007 21:34 SYSSTATS_INFO DSTOP 06-02-2007 21:35 SYSSTATS_INFO FLAGS 0 SYSSTATS_MAIN CPUSPEEDNW 459.046 SYSSTATS_MAIN IOSEEKTIM 30.207 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 6.106 SYSSTATS_MAIN MREADTIM 17.627 SYSSTATS_MAIN CPUSPEED 459 SYSSTATS_MAIN MBRC 8 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR 13 rows selected.