Re: tracing makes the sql run faster
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: john.kanagaraj@xxxxxxx
- Date: Tue, 02 Nov 2004 23:18:02 +0000
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
> --
> //www.freelists.org/webpage/oracle-l
>=20
>
--=20
Mladen Gogala
Oracle DBA
--
//www.freelists.org/webpage/oracle-l
Other related posts:
