RE: How to implement and use DBMS_STATS?
A word of warning. I just came across a really dumb bug (1407738) in
dbms_stats on 8i. If you use gather_schema_stats with the gather stale or
empty options on a schema with a table name longer than 28 characters, you
get an ora-6502. Turns out that dbms_stats uses varchar2(30) variables to
store the table name, but then goes and puts quotes round it to guard
against odd characters. I was irritated to hear from OWS that its fixed in
9i but that they aren't going to backport it to 8.1.7 grr.
David Lord
-Original Message-
Sent: 06 July 2001 21:21
To: Multiple recipients of list ORACLE-L
Steve,
Good stuff !
What's the overhead incurred in monitoring the tables ? Is that
quantifiable ?
The reason I ask is because I am currently gathering statistics weekly on
all tables, but would much rather use the monitoring/stale route. I assume
that the overhead would be less than gathering stats for all tables.
TIA
Srini Chavali
Oracle DBA
Cummins Inc
Orr, Steve [EMAIL PROTECTED]@fatcity.com on 07/06/2001 02:42:03 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
OK, here's an excerpt from our DBA Cookbook
Optimizer Statistics
We are using the cost-based optimizer for our implementation so it is
important that we keep optimizer statistics up-to-date. But computing these
statistics can be very resource intensive requiring lots of CPU and a
tablespace for temporary sorts of up to 1.25 times the size of the largest
table being analyzed. To minimize resource usage we use the new DBMS_STATS
Oracle supplied package.
Here are the steps:
1. First we gather complete statistics on a schema:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,null,null,null,null,TRUE);
2. Next we alter the tables to implement statistical monitoring:
SQL alter table SCHEMANAME.TBLNAME monitoring;
3. Finally, on a periodic basis, we refresh the statistics. The first two
steps above only need to be done once after database/schema creation. The
following step should be performed periodically or as needed:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,FALSE, 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT', -
TRUE,null,null,'GATHER STALE');
By putting the tables in monitor mode, Oracle tracks statistical changes
whenever any DML is executed. If data changes are such that it could affect
optimization, Oracle marks the table or index as stale. When we perform
step 3 above, we are only recomputing statistics on the objects that need
it. This saves computing resource in a 24X7 environment. For more
information see the Automated Statistics Gathering section of the Oracle
Tuning manual. Also, reference the Oracle packages documentation for
information on DBMS_STATS.
Here's some sample contents of a cron job script:
#!/usr/bin/ksh
# File:getstats.sh, Steve Orr, 5/21/01
# Purpose: Oracle maintenance, recompute stats.
# Usage: getstats.sh SCHEMA_OWNER_NAME
. /usr/bin/dbaenv
LOGFILE=$LOGDIR/stats.log
if [ $1 ]
then DASCHEMA=$1
else echo You must supply an argument for the schema.
exit 1
fi
echo Calculating statistics, please wait...
getstats()
{
echo ---
echo `date` -- Compute Stats.
ORACLE_SID=WHATEVER;export ORACLE_SID
sqlplus -s internal/oracle or whatever user/pw EOSQL
execute DBMS_STATS.GATHER_SCHEMA_STATS( -
ownname= '$DASCHEMA' , -
method_opt = 'FOR ALL INDEXED COLUMNS SIZE 64' , -
cascade= TRUE , -
options= 'GATHER STALE' ) ;
exit;
EOSQL
echo `date` -- Done recomputing stats on $DASCHEMA
}
getstats | tee $LOGFILE
Well, that should get you started,
Steve Orr
-Original Message-
Sent: Friday, July 06, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L
OK, enough is enough. For a month now, I've been researching on how one is
supposed to implement and use the DBMS_STATS package. The FMs I R'd,
Oracle
8.1.7's Designing and Tuning for Performance and Supplied PL/SQL
Packages
Reference, give the syntax and some very weak and incomplete examples, but
fail to define how a DBA is actually supposed to USE the package in
day-to-day operation.
And Oracle Support just keeps pointing me back to inane and unrelated
articles in MetaClink.
Can anyone point out a website or a good book with a chapter on using
DBMS_STATS for CBO? Specifically, some good examples, some definitions
(when are stats considered stale?), what is the scope of
GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances
GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and
generally how the hell one goes about implementing this. I've now got less
than two weeks to figure it out!
And if you know this, where did you learn it? Even my Oracle Perf Tuning
Class student guide mentions the package, says to use it, but then points
to
the syntax-only Oracle docs for more info. sigh
Frustratedly yours,
Rich Jesse
Re: How to implement and use DBMS_STATS?
how nice of them, since they are pushing so hard the concept of
dbms_stats.
Long live analyze :)
joe
Lord, David - CS wrote:
A word of warning. I just came across a really dumb bug (1407738) in
dbms_stats on 8i. If you use gather_schema_stats with the gather stale or
empty options on a schema with a table name longer than 28 characters, you
get an ora-6502. Turns out that dbms_stats uses varchar2(30) variables to
store the table name, but then goes and puts quotes round it to guard
against odd characters. I was irritated to hear from OWS that its fixed in
9i but that they aren't going to backport it to 8.1.7 grr.
David Lord
-Original Message-
Sent: 06 July 2001 21:21
To: Multiple recipients of list ORACLE-L
Steve,
Good stuff !
What's the overhead incurred in monitoring the tables ? Is that
quantifiable ?
The reason I ask is because I am currently gathering statistics weekly on
all tables, but would much rather use the monitoring/stale route. I assume
that the overhead would be less than gathering stats for all tables.
TIA
Srini Chavali
Oracle DBA
Cummins Inc
Orr, Steve [EMAIL PROTECTED]@fatcity.com on 07/06/2001 02:42:03 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
OK, here's an excerpt from our DBA Cookbook
Optimizer Statistics
We are using the cost-based optimizer for our implementation so it is
important that we keep optimizer statistics up-to-date. But computing these
statistics can be very resource intensive requiring lots of CPU and a
tablespace for temporary sorts of up to 1.25 times the size of the largest
table being analyzed. To minimize resource usage we use the new DBMS_STATS
Oracle supplied package.
Here are the steps:
1. First we gather complete statistics on a schema:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,null,null,null,null,TRUE);
2. Next we alter the tables to implement statistical monitoring:
SQL alter table SCHEMANAME.TBLNAME monitoring;
3. Finally, on a periodic basis, we refresh the statistics. The first two
steps above only need to be done once after database/schema creation. The
following step should be performed periodically or as needed:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,FALSE, 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT', -
TRUE,null,null,'GATHER STALE');
By putting the tables in monitor mode, Oracle tracks statistical changes
whenever any DML is executed. If data changes are such that it could affect
optimization, Oracle marks the table or index as stale. When we perform
step 3 above, we are only recomputing statistics on the objects that need
it. This saves computing resource in a 24X7 environment. For more
information see the Automated Statistics Gathering section of the Oracle
Tuning manual. Also, reference the Oracle packages documentation for
information on DBMS_STATS.
Here's some sample contents of a cron job script:
#!/usr/bin/ksh
# File:getstats.sh, Steve Orr, 5/21/01
# Purpose: Oracle maintenance, recompute stats.
# Usage: getstats.sh SCHEMA_OWNER_NAME
. /usr/bin/dbaenv
LOGFILE=$LOGDIR/stats.log
if [ $1 ]
then DASCHEMA=$1
else echo You must supply an argument for the schema.
exit 1
fi
echo Calculating statistics, please wait...
getstats()
{
echo ---
echo `date` -- Compute Stats.
ORACLE_SID=WHATEVER;export ORACLE_SID
sqlplus -s internal/oracle or whatever user/pw EOSQL
execute DBMS_STATS.GATHER_SCHEMA_STATS( -
ownname= '$DASCHEMA' , -
method_opt = 'FOR ALL INDEXED COLUMNS SIZE 64' , -
cascade= TRUE , -
options= 'GATHER STALE' ) ;
exit;
EOSQL
echo `date` -- Done recomputing stats on $DASCHEMA
}
getstats | tee $LOGFILE
Well, that should get you started,
Steve Orr
-Original Message-
Sent: Friday, July 06, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L
OK, enough is enough. For a month now, I've been researching on how one is
supposed to implement and use the DBMS_STATS package. The FMs I R'd,
Oracle
8.1.7's Designing and Tuning for Performance and Supplied PL/SQL
Packages
Reference, give the syntax and some very weak and incomplete examples, but
fail to define how a DBA is actually supposed to USE the package in
day-to-day operation.
And Oracle Support just keeps pointing me back to inane and unrelated
articles in MetaClink.
Can anyone point out a website or a good book with a chapter on using
DBMS_STATS for CBO? Specifically, some good examples, some definitions
(when are stats considered stale?), what is the scope of
GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances
GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and
generally how the hell one goes about implementing this. I've now got less
than two weeks to
Re: How to implement and use DBMS_STATS?
Don Burleson just released (via Oracle Press) and entire BOOK on using STATSPACK. From: Jesse, Rich [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: How to implement and use DBMS_STATS? Date: Fri, 06 Jul 2001 08:20:56 -0800 OK, enough is enough. For a month now, I've been researching on how one is supposed to implement and use the DBMS_STATS package. The FMs I R'd, Oracle 8.1.7's Designing and Tuning for Performance and Supplied PL/SQL Packages Reference, give the syntax and some very weak and incomplete examples, but fail to define how a DBA is actually supposed to USE the package in day-to-day operation. And Oracle Support just keeps pointing me back to inane and unrelated articles in MetaClink. Can anyone point out a website or a good book with a chapter on using DBMS_STATS for CBO? Specifically, some good examples, some definitions (when are stats considered stale?), what is the scope of GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and generally how the hell one goes about implementing this. I've now got less than two weeks to figure it out! And if you know this, where did you learn it? Even my Oracle Perf Tuning Class student guide mentions the package, says to use it, but then points to the syntax-only Oracle docs for more info. sigh Frustratedly yours, Rich Jesse System/Database Administrator (wannabe?) [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to implement and use DBMS_STATS?
He asked about DBMS_STATS - not STATSPACK. I do not know any other source except docs and metalink describing DBMS_STATS. Alex Hillman -Original Message- Sent: Friday, July 06, 2001 1:47 PM To: Multiple recipients of list ORACLE-L Don Burleson just released (via Oracle Press) and entire BOOK on using STATSPACK. From: Jesse, Rich [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: How to implement and use DBMS_STATS? Date: Fri, 06 Jul 2001 08:20:56 -0800 OK, enough is enough. For a month now, I've been researching on how one is supposed to implement and use the DBMS_STATS package. The FMs I R'd, Oracle 8.1.7's Designing and Tuning for Performance and Supplied PL/SQL Packages Reference, give the syntax and some very weak and incomplete examples, but fail to define how a DBA is actually supposed to USE the package in day-to-day operation. And Oracle Support just keeps pointing me back to inane and unrelated articles in MetaClink. Can anyone point out a website or a good book with a chapter on using DBMS_STATS for CBO? Specifically, some good examples, some definitions (when are stats considered stale?), what is the scope of GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and generally how the hell one goes about implementing this. I've now got less than two weeks to figure it out! And if you know this, where did you learn it? Even my Oracle Perf Tuning Class student guide mentions the package, says to use it, but then points to the syntax-only Oracle docs for more info. sigh Frustratedly yours, Rich Jesse System/Database Administrator (wannabe?) [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to implement and use DBMS_STATS?
Steve,
Good stuff !
What's the overhead incurred in monitoring the tables ? Is that
quantifiable ?
The reason I ask is because I am currently gathering statistics weekly on
all tables, but would much rather use the monitoring/stale route. I assume
that the overhead would be less than gathering stats for all tables.
TIA
Srini Chavali
Oracle DBA
Cummins Inc
Orr, Steve [EMAIL PROTECTED]@fatcity.com on 07/06/2001 02:42:03 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
OK, here's an excerpt from our DBA Cookbook
Optimizer Statistics
We are using the cost-based optimizer for our implementation so it is
important that we keep optimizer statistics up-to-date. But computing these
statistics can be very resource intensive requiring lots of CPU and a
tablespace for temporary sorts of up to 1.25 times the size of the largest
table being analyzed. To minimize resource usage we use the new DBMS_STATS
Oracle supplied package.
Here are the steps:
1. First we gather complete statistics on a schema:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,null,null,null,null,TRUE);
2. Next we alter the tables to implement statistical monitoring:
SQL alter table SCHEMANAME.TBLNAME monitoring;
3. Finally, on a periodic basis, we refresh the statistics. The first two
steps above only need to be done once after database/schema creation. The
following step should be performed periodically or as needed:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,FALSE, 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT', -
TRUE,null,null,'GATHER STALE');
By putting the tables in monitor mode, Oracle tracks statistical changes
whenever any DML is executed. If data changes are such that it could affect
optimization, Oracle marks the table or index as stale. When we perform
step 3 above, we are only recomputing statistics on the objects that need
it. This saves computing resource in a 24X7 environment. For more
information see the Automated Statistics Gathering section of the Oracle
Tuning manual. Also, reference the Oracle packages documentation for
information on DBMS_STATS.
Here's some sample contents of a cron job script:
#!/usr/bin/ksh
# File:getstats.sh, Steve Orr, 5/21/01
# Purpose: Oracle maintenance, recompute stats.
# Usage: getstats.sh SCHEMA_OWNER_NAME
. /usr/bin/dbaenv
LOGFILE=$LOGDIR/stats.log
if [ $1 ]
then DASCHEMA=$1
else echo You must supply an argument for the schema.
exit 1
fi
echo Calculating statistics, please wait...
getstats()
{
echo ---
echo `date` -- Compute Stats.
ORACLE_SID=WHATEVER;export ORACLE_SID
sqlplus -s internal/oracle or whatever user/pw EOSQL
execute DBMS_STATS.GATHER_SCHEMA_STATS( -
ownname= '$DASCHEMA' , -
method_opt = 'FOR ALL INDEXED COLUMNS SIZE 64' , -
cascade= TRUE , -
options= 'GATHER STALE' ) ;
exit;
EOSQL
echo `date` -- Done recomputing stats on $DASCHEMA
}
getstats | tee $LOGFILE
Well, that should get you started,
Steve Orr
-Original Message-
Sent: Friday, July 06, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L
OK, enough is enough. For a month now, I've been researching on how one is
supposed to implement and use the DBMS_STATS package. The FMs I R'd,
Oracle
8.1.7's Designing and Tuning for Performance and Supplied PL/SQL
Packages
Reference, give the syntax and some very weak and incomplete examples, but
fail to define how a DBA is actually supposed to USE the package in
day-to-day operation.
And Oracle Support just keeps pointing me back to inane and unrelated
articles in MetaClink.
Can anyone point out a website or a good book with a chapter on using
DBMS_STATS for CBO? Specifically, some good examples, some definitions
(when are stats considered stale?), what is the scope of
GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances
GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and
generally how the hell one goes about implementing this. I've now got less
than two weeks to figure it out!
And if you know this, where did you learn it? Even my Oracle Perf Tuning
Class student guide mentions the package, says to use it, but then points
to
the syntax-only Oracle docs for more info. sigh
Frustratedly yours,
Rich Jesse System/Database Administrator
(wannabe?)
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY,
RE: How to implement and use DBMS_STATS?
Hey Steve! PERFECT! This is exactly what I needed to get started. If you're ever in the metro Milwaukee, WI area, you've got some beer here with your name on it. :) http://www.execpc.com/~legoman Thanks again! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Friday, July 06, 2001 14:42 To: Multiple recipients of list ORACLE-L OK, here's an excerpt from our DBA Cookbook [excerpted to save on my Microsoft Lookout quota] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to implement and use DBMS_STATS?
Steve Adams says it's probably less than 1% on heavy OLTP systems. Check out
his excellent explanation at: http://www.ixora.com.au/newsletter/2000_07.htm
Steve
-Original Message-
Sent: Friday, July 06, 2001 2:21 PM
To: Multiple recipients of list ORACLE-L
Steve,
Good stuff !
What's the overhead incurred in monitoring the tables ? Is that
quantifiable ?
The reason I ask is because I am currently gathering statistics weekly on
all tables, but would much rather use the monitoring/stale route. I assume
that the overhead would be less than gathering stats for all tables.
TIA
Srini Chavali
Oracle DBA
Cummins Inc
Orr, Steve [EMAIL PROTECTED]@fatcity.com on 07/06/2001 02:42:03 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
OK, here's an excerpt from our DBA Cookbook
Optimizer Statistics
We are using the cost-based optimizer for our implementation so it is
important that we keep optimizer statistics up-to-date. But computing these
statistics can be very resource intensive requiring lots of CPU and a
tablespace for temporary sorts of up to 1.25 times the size of the largest
table being analyzed. To minimize resource usage we use the new DBMS_STATS
Oracle supplied package.
Here are the steps:
1. First we gather complete statistics on a schema:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,null,null,null,null,TRUE);
2. Next we alter the tables to implement statistical monitoring:
SQL alter table SCHEMANAME.TBLNAME monitoring;
3. Finally, on a periodic basis, we refresh the statistics. The first two
steps above only need to be done once after database/schema creation. The
following step should be performed periodically or as needed:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,FALSE, 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT', -
TRUE,null,null,'GATHER STALE');
By putting the tables in monitor mode, Oracle tracks statistical changes
whenever any DML is executed. If data changes are such that it could affect
optimization, Oracle marks the table or index as stale. When we perform
step 3 above, we are only recomputing statistics on the objects that need
it. This saves computing resource in a 24X7 environment. For more
information see the Automated Statistics Gathering section of the Oracle
Tuning manual. Also, reference the Oracle packages documentation for
information on DBMS_STATS.
Here's some sample contents of a cron job script:
#!/usr/bin/ksh
# File:getstats.sh, Steve Orr, 5/21/01
# Purpose: Oracle maintenance, recompute stats.
# Usage: getstats.sh SCHEMA_OWNER_NAME
. /usr/bin/dbaenv
LOGFILE=$LOGDIR/stats.log
if [ $1 ]
then DASCHEMA=$1
else echo You must supply an argument for the schema.
exit 1
fi
echo Calculating statistics, please wait...
getstats()
{
echo ---
echo `date` -- Compute Stats.
ORACLE_SID=WHATEVER;export ORACLE_SID
sqlplus -s internal/oracle or whatever user/pw EOSQL
execute DBMS_STATS.GATHER_SCHEMA_STATS( -
ownname= '$DASCHEMA' , -
method_opt = 'FOR ALL INDEXED COLUMNS SIZE 64' , -
cascade= TRUE , -
options= 'GATHER STALE' ) ;
exit;
EOSQL
echo `date` -- Done recomputing stats on $DASCHEMA
}
getstats | tee $LOGFILE
Well, that should get you started,
Steve Orr
-Original Message-
Sent: Friday, July 06, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L
OK, enough is enough. For a month now, I've been researching on how one is
supposed to implement and use the DBMS_STATS package. The FMs I R'd,
Oracle
8.1.7's Designing and Tuning for Performance and Supplied PL/SQL
Packages
Reference, give the syntax and some very weak and incomplete examples, but
fail to define how a DBA is actually supposed to USE the package in
day-to-day operation.
And Oracle Support just keeps pointing me back to inane and unrelated
articles in MetaClink.
Can anyone point out a website or a good book with a chapter on using
DBMS_STATS for CBO? Specifically, some good examples, some definitions
(when are stats considered stale?), what is the scope of
GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances
GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and
generally how the hell one goes about implementing this. I've now got less
than two weeks to figure it out!
And if you know this, where did you learn it? Even my Oracle Perf Tuning
Class student guide mentions the package, says to use it, but then points
to
the syntax-only Oracle docs for more info. sigh
Frustratedly yours,
Rich Jesse System/Database Administrator
(wannabe?)
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California
RE: How to implement and use DBMS_STATS?
I did the same thing using directly views dba_tab_modifications and
dba_tables. This way you can device your own algotithm to decide for which
objects you want to gather statistics. Also I use these views to rebuild
indexes - when sum of inserts and deletes then X% of number of records in
dba_tables.
Alex Hillman
-Original Message-
Sent: Friday, July 06, 2001 3:42 PM
To: Multiple recipients of list ORACLE-L
OK, here's an excerpt from our DBA Cookbook
Optimizer Statistics
We are using the cost-based optimizer for our implementation so it is
important that we keep optimizer statistics up-to-date. But computing these
statistics can be very resource intensive requiring lots of CPU and a
tablespace for temporary sorts of up to 1.25 times the size of the largest
table being analyzed. To minimize resource usage we use the new DBMS_STATS
Oracle supplied package.
Here are the steps:
1. First we gather complete statistics on a schema:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,null,null,null,null,TRUE);
2. Next we alter the tables to implement statistical monitoring:
SQL alter table SCHEMANAME.TBLNAME monitoring;
3. Finally, on a periodic basis, we refresh the statistics. The first two
steps above only need to be done once after database/schema creation. The
following step should be performed periodically or as needed:
SQL execute DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', -
null,FALSE, 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT', -
TRUE,null,null,'GATHER STALE');
By putting the tables in monitor mode, Oracle tracks statistical changes
whenever any DML is executed. If data changes are such that it could affect
optimization, Oracle marks the table or index as stale. When we perform
step 3 above, we are only recomputing statistics on the objects that need
it. This saves computing resource in a 24X7 environment. For more
information see the Automated Statistics Gathering section of the Oracle
Tuning manual. Also, reference the Oracle packages documentation for
information on DBMS_STATS.
Here's some sample contents of a cron job script:
#!/usr/bin/ksh
# File:getstats.sh, Steve Orr, 5/21/01
# Purpose: Oracle maintenance, recompute stats.
# Usage: getstats.sh SCHEMA_OWNER_NAME
. /usr/bin/dbaenv
LOGFILE=$LOGDIR/stats.log
if [ $1 ]
then DASCHEMA=$1
else echo You must supply an argument for the schema.
exit 1
fi
echo Calculating statistics, please wait...
getstats()
{
echo ---
echo `date` -- Compute Stats.
ORACLE_SID=WHATEVER;export ORACLE_SID
sqlplus -s internal/oracle or whatever user/pw EOSQL
execute DBMS_STATS.GATHER_SCHEMA_STATS( -
ownname= '$DASCHEMA' , -
method_opt = 'FOR ALL INDEXED COLUMNS SIZE 64' , -
cascade= TRUE , -
options= 'GATHER STALE' ) ;
exit;
EOSQL
echo `date` -- Done recomputing stats on $DASCHEMA
}
getstats | tee $LOGFILE
Well, that should get you started,
Steve Orr
-Original Message-
Sent: Friday, July 06, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L
OK, enough is enough. For a month now, I've been researching on how one is
supposed to implement and use the DBMS_STATS package. The FMs I R'd, Oracle
8.1.7's Designing and Tuning for Performance and Supplied PL/SQL Packages
Reference, give the syntax and some very weak and incomplete examples, but
fail to define how a DBA is actually supposed to USE the package in
day-to-day operation.
And Oracle Support just keeps pointing me back to inane and unrelated
articles in MetaClink.
Can anyone point out a website or a good book with a chapter on using
DBMS_STATS for CBO? Specifically, some good examples, some definitions
(when are stats considered stale?), what is the scope of
GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances
GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and
generally how the hell one goes about implementing this. I've now got less
than two weeks to figure it out!
And if you know this, where did you learn it? Even my Oracle Perf Tuning
Class student guide mentions the package, says to use it, but then points to
the syntax-only Oracle docs for more info. sigh
Frustratedly yours,
Rich Jesse System/Database Administrator (wannabe?)
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for
RE: How to implement and use DBMS_STATS?
I just wrote my own home-grown and posted on my site. The nice thing about this one is that you can tell it to gather stats for 60 minutes, 3 times a week or whatever. http://www.geocities.com/epost1/automating_statistics_for_oracle.htm Not using MONITORING on this database (8.0.4) but script can be changed in about 10 minutes to make use of the MONITORING DBA related tables instead. Also Steve had mentioned something about GATHER_SCHEMA_STATS being a bit buggy in 8.1.6 so I did not want to use that. Steve hasn't been around for a while... Thanks, Ethan -Original Message- Sent: Friday, July 06, 2001 1:47 PM To: Multiple recipients of list ORACLE-L Steve Adams says it's probably less than 1% on heavy OLTP systems. Check out his excellent explanation at: http://www.ixora.com.au/newsletter/2000_07.htm Steve -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
