Re: tracing makes the sql run faster

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Tue, 02 Nov 2004 23:18:02 +0000
Message-Id: <1099437482l.26821l.0l@medo.noip.com>

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

Received on Tue Nov 02 2004 - 17:14:09 CST