RE: Really deleting system statistics?

Hi Peter

>in 10.1.0.3 I tried to delete system statistics with
>
>exec DBMS_STATS.DELETE_SYSTEM_STATS;

Notice that system statistics are always available in 10g. In fact even = if you don't gather them, Oracle automatically gathers them with the = NOWORKLOAD method.

>No error. In sys.aux_stats$ the 'Flag' goes from 1 to 0.=20
>And values for mreadtim,sreadtim.. stay set. (And I cannot=20
>set them to NULL with SET_SYSTEM_STATS).=20

I never saw another behaviour.

>Any idea how to convince the optimizer not to use the deleted =
statistics?

For test purposes, i.e. to see what happen if they are not available, I = run the following statements...

delete aux_stats$;
commit;
shutdown immediate
startup
select * from aux_stats$;

I know, I know, it's probably not the best approach, but I tested it = many times on different databases and I never had problems... Notice = that when the database is restarted a set of NOWORKLOAD statistics are = gathered (to do so they read some blocks from each datafile):

- CPUSPEEDNW =3D ??? (depends on the CPU)
- IOSEEKTIM =3D 10=20
- IOTFRSPEED =3D 4096

My guess, which is practically certitude, is that IOSEEKTIM/IOTFRSPEED = are fix.

HTH
Chris

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