| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Question On Statistics Gathering
Dennis,
First, #2 since it is a shorter answer... I don't know, but I just saw
another resonder say it did not. That's good to know, I haven't
previously taken that into account.
Personally, I like the monitor/gather stale feature. I have heard,
some on this list, some on metalink of bugs where the 'gather stale'
option has issues. (1890016, 2157655 are a couple of them, didn't read
them entirely or know when they are fixed.)
However, I don't user gather stale anyway for actually stats gathering,
only to show which tables are stale, then use a different block of code
to gather stats on those tables.
One example: (this may turn out messy coming out of groupwise)
(Also, must give credit and thanks to Tom Kyte,
http://asktom.oracle.com where I obtained this logic.)
Just compile this procedure, then run it/schedule it whenever and pass
it a schema owner.
You'll also want to add some error checking / exception handling, which
I haven't done yet (working on another procedure to use java for an OS
call to send me a page if this has errors).
create or replace procedure anlz_stale_tabs
( v_schema_owner in varchar2 )
/* Name: anlz_stale_tabs
Purpose: Generate a list of stale, monitored tables
from the specified schema and gather
stats
for each.
Usage: exec anlz_stale_tabs('SCHEMA_OWNER')
*/
AUTHID CURRENT_USER
as
l_objlist dbms_stats.objecttab; l_logfile utl_file.file_type;
BEGIN
dbms_stats.gather_schema_stats (
ownname => v_schema_owner,
options => 'LIST STALE',
objlist => l_objlist );
l_logfile :=
utl_file.fopen('/home/oracle/log',v_schema_owner||'_analyze_stale.log','w'
);
utl_file.put_line( l_logfile, 'Starting list of stale objects at '
||to_char(sysdate,'DD-MON-YYYY
HH24:MI'));
/* Loop through list of stale objects, list to logfile and gather
stats. */
for i in 1 .. l_objlist.count
loop
/* List the stale objects to the log file. */
utl_file.putf( l_logfile,
'*********************************************** \n' );
utl_file.putf( l_logfile, '\nStale object type is
'||l_objlist(i).objtype||'\n' );
utl_file.putf( l_logfile, 'Stale object is
'||l_objlist(i).objname||'\n\n' );
/* Gather stats for the stale objects. */
utl_file.putf( l_logfile, 'Starting analyze of
'||l_objlist(i).objname
||' at '||to_char(sysdate,'DD-MON-YYYY
HH24:MI')||'. \n');
dbms_stats.gather_table_stats(ownname=>v_schema_owner,
tabname=>l_objlist(i).objname,
method_opt=>'FOR ALL INDEXED COLUMNS
SIZE 1',
cascade=>TRUE,
granularity=>'ALL',
degree=>6);
utl_file.putf( l_logfile, 'Completed analyze of
'||l_objlist(i).objname
||' at '||to_char(sysdate,'DD-MON-YYYY
HH24:MI')||'. \n');
end loop;
utl_file.putf( l_logfile, '\n\n Finished list of stale objects at '
||to_char(sysdate,'DD-MON-YYYY
HH24:MI'));
>>> [EMAIL PROTECTED] 06/27/03 03:34PM >>>
Hi All,
Currently we have a very large database ( 8.1.7.4 ) on Tru64 that is
having query optimization problems. Lacking good, up-to-date statistics
is the main cause of it. Because of the size and availability window we
can't afford to analyze the whole schema. Now 8i has
a new feature that only gathers stats when stale. You have to set table
to monitoring mode first.
I am intrigued by this new feature and am reading up on it and have 2
questions:
1. How reliable and bug-free is this feature?
2. We do a lot of sqlldr direct path, which bypasses table insert. Will
this feature catch the rows inserted that way?
TIA
Dennis
-----Original Message-----
Sent: Friday, June 27, 2003 2:30 PM
To: Multiple recipients of list ORACLE-L
One of our third party applications gave us the following message:
ORA-04031: unable to allocate 4192 bytes of shared memory
Environment is Oracle 8.1.7 on AIX 4.3.2
The output from "select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the "shared pool"/"free memory" value in v$sgastat. It seems to vary between about 3 and 11 mb.
Thanks,
Peter Schauss
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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-LReceived on Fri Jun 27 2003 - 16:26:14 CDT
(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.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
 |
 |