RE: UPDATE STATISTICS

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 25 Jan 2001 14:21:14 -0800
Message-Id: <10752.127551@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0871D.20DD76C0
Content-Type: text/plain;

charset="iso-8859-1"

> -----Original Message-----
> From: bruce.taneja_at_mcd.com [mailto:bruce.taneja_at_mcd.com]
> Sent: jeudi, 25. janvier 2001 12:55
> 
> Anyone have a good script to analyse stats for COST based optimizer
> for the whole database..

I wrote a PL/SQL procedure at my old job that would read a table for analyze instructions (e.g. owner = 'TOOLS' table_name = '%' method = 'COMPUTE') and analyze the tables, indexes, partitions etc... that fit the conditions as specified in the "selection" table. It would then store the results of the analyze in another table (history). We used dbms_job to schedule the procedure once a week. I can send this out if anyone is interested.

> Is there a procedure to do this in one of the packages?

dbms_utility.analyze_database - all tables, clusters, indices
dbms_utility.analyze_part_object - each partition of an object
dbms_utility.analyze_schema  - all tables, clusters, indices in a schema
dbms_ddl.analyze_object - computes statistics for the specified object

any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com

------_=_NextPart_001_01C0871D.20DD76C0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2652.35">
<TITLE>RE: UPDATE STATISTICS</TITLE>
</HEAD>
<BODY>

> -----Original Message-----
 > From: bruce.taneja_at_mcd.com [<A =
HREF=3D"mailto:bruce.taneja_at_mcd.com">mailto:bruce.taneja_at_mcd.com</A>]</F=

ONT>


<BR><FONT SIZE=3D2>&gt; Sent: jeudi, 25. janvier 2001 12:55</FONT>

 > 
 > Anyone have a good script to analyse stats for =
COST based optimizer
 > for the whole database..


I wrote a PL/SQL procedure at my old job that would =
read a table for analyze instructions (e.g. owner =3D 'TOOLS' = table_name =3D '%' method =3D 'COMPUTE') and analyze the tables, = indexes, partitions etc... that fit the conditions as specified in the = "selection" table. It would then store the results of the = analyze in another table (history). We used dbms_job to schedule the = procedure once a week. I can send this out if anyone is = interested.

> Is there a procedure to do this in one of the =
packages?


dbms_utility.analyze_database - all tables, clusters, =
indices
 dbms_utility.analyze_part_object - each partition of =
an object
 dbms_utility.analyze_schema  - all tables, =
clusters, indices in a schema
 dbms_ddl.analyze_object - computes statistics for =
the specified object


------
 any ignorant comments made are the sole =
responsibility of J. R. Kilchoer and should not reflect adversely upon = my employer.

 
 Jacques R. Kilchoer
 (949) 754-8816
 Quest Software, Inc.
 8001 Irvine Center Drive
 Irvine, California 92618
 U.S.A.
 <A HREF=3D"http://www.quest.com" =
TARGET=3D"_blank">http://www.quest.com</A> Received on Thu Jan 25 2001 - 16:21:14 CST