Re: tracing makes the sql run faster

John, here is a good method of making sure that system stats is =20
collected from a very busy system:

begin
DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode=3D>'START');
DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent  =3D> NULL,
   block_sample      =3D> FALSE,
   method_opt        =3D> 'FOR COLUMNS SIZE SKEWONLY',
   degree            =3D> 8,
   granularity       =3D> 'DEFAULT',
   options           =3D> 'GATHER',
   gather_sys        =3D> TRUE,
   no_invalidate     =3D> FALSE,
   gather_temp       =3D> FALSE);
DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode=3D>'STOP');
end;
/


This will keep even a multi-terabyte system fairly busy for a while.
System statistics will be of an excellent quality. If your database is
a large and busy OLTP dtabase, this best executed during peak time =20
hours. Your users will notice the difference.

On 11/02/2004 06:08:02 PM, John Kanagaraj wrote:
> Prasad,
>=20
> >Looks like CBO is changing plan during the day when there is a peak
> >activity. I am not sure why it changes the plan. I will
> >monitor it tomorrow and posts the findings.
>=20
> Just another though - You may have collected system stats (new in 9i)
> during
> off peak hours and that is not applicable during peak hours...
>=20
> John
> --
> http://www.freelists.org/webpage/oracle-l
>=20
>

--=20
Mladen Gogala
Oracle DBA


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

Other related posts: