Re: Really deleting system statistics?

How about settting

_optimizer_cost_model to *io

SQL> alter session set "_optimizer_cost_model"=abc; ERROR:
ORA-00096: invalid value ABC for parameter _optimizer_cost_model, must be from
among choose, io, cpu

thanks
amit
*
Christian Antognini wrote:

>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
>
>
>

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