| 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: UPDATE STATISTICS
Is this what you are looking for?
rem $DBA/analyze.sql
rem
rem Performs an ANALYZE COMPUTE STATISTICS on all non-SYS/SYSTEM-owned tables
rem and indexes.
rem
rem Creates an analysis report (analyze.lis) of all analyzed tables and indexes.
rem
rem ***** Notes: *****
rem 1) This may take many hours when analyzing large tables! rem 2) The analyze will AUTOMATICALLY turn on Cost-Based Optimization !!! rem 3) Oracle requires locks on the tables to be analyzed. If this prevents rem this script from successfully executing, use the analinds.sql script.rem
cursor time_cursor is select
to_char(sysdate, 'HH:MI:SS')
from dual;
cursor tab_cursor is select
owner,
table_name,
num_rows,
chain_cnt,
avg_row_len
from sys.dba_tables
where owner != 'SYS' and owner != 'SYSTEM'
and table_name != 'ANAL_TEMP'
order by owner, table_name;
cursor ind_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
to_char(sysdate, 'HH:MI:SS'),
index_name,
decode(uniqueness,
'NONUNIQUE','Non-Unique',
'UNIQUE',' Unique',
'BITMAP',' Bitmap', uniqueness),
distinct_keys
from sys.dba_indexes
where owner = c_own and table_name = c_tab
order by index_name;
cursor col_cursor (c_own varchar2, c_ind varchar2) is select
decode(column_position, 1, column_name, ', ' || column_name)
from sys.dba_ind_columns
where index_owner = c_own and index_name = c_ind
order by column_position;
lv_owner sys.dba_tables.owner%TYPE;
lv_table_name sys.dba_tables.table_name%TYPE;
lv_num_rows sys.dba_tables.num_rows%TYPE;
lv_chain_cnt sys.dba_tables.chain_cnt%TYPE;
lv_avg_row_len sys.dba_tables.avg_row_len%TYPE;
lv_index_name sys.dba_indexes.index_name%TYPE;
lv_distinct_keys sys.dba_indexes.distinct_keys%TYPE;
lv_column_name sys.dba_ind_columns.column_name%TYPE;
lv_uniqueness char(10);
now varchar2(8);
lineno number;
recno number;
n number;
a_lin varchar2(80);
x varchar2(80);
function vwri(x_lin in varchar2, x_str in varchar2,
x_force in number) return varchar2 is
begin
if length(x_lin) + length(x_str) > 80
then
lineno := lineno + 1;
insert into anal_temp values (lineno, x_lin);
if x_force = 0
then
return ' ' ||
x_str;
else
lineno := lineno + 1;
insert into anal_temp values (lineno, x_str);
return '';
end if;
else
if x_force = 0
then
return x_lin||x_str;
else
lineno := lineno + 1;
insert into anal_temp values (
lineno, x_lin||x_str);
return '';
end if;
end if;
function format_owner_table (the_owner in varchar2,
the_table in varchar2) return varchar2 is begin
n := length(the_owner) + length(the_table);
if n < 40 then
return rpad(the_owner || '.' || the_table, 40);
else
return '..' || substr(the_owner || '.' || the_table,
n-36, 38);
end if;
procedure wri (my_txt in varchar2) is begin
lineno := lineno + 1;
insert into anal_temp values (lineno, my_txt);
end wri;
begin
lineno := 0;
recno := 0;
/* Analyze all of the desired tables and indexes */
open tab_cursor;
loop
open time_cursor;
fetch time_cursor into now;
close time_cursor;
fetch tab_cursor into
lv_owner,
lv_table_name,
lv_num_rows,
lv_chain_cnt,
lv_avg_row_len;
exit when tab_cursor%notfound;
dbms_output.put_line('Starting analysis of ' ||
format_owner_table(lv_owner, lv_table_name) ||
' at ' || now);
/* Analyze each table */
dbms_ddl.analyze_object('TABLE', lv_owner, lv_table_name,
'COMPUTE');
open ind_cursor(lv_owner, lv_table_name);
loop
fetch ind_cursor into
now,
lv_index_name,
lv_uniqueness,
lv_distinct_keys;
exit when ind_cursor%notfound;
dbms_output.put_line('Starting analysis of ' ||
rpad(lv_index_name, 40) || ' at ' || now);
/* Analyze each index for this table */
dbms_ddl.analyze_object('INDEX', lv_owner,
lv_index_name, 'COMPUTE');
end loop;
close ind_cursor;
dbms_output.put_line('Done with analysis at ' || now);
/* Rerun all the cursors again to create the report */
wri(' ' ||
' Avg');
wri(' Chain' ||
' Row Distinct');
wri('Object Owner / Name Num Rows Count' ||
' Len Keys');
wri('---------------------------------------- --------- ---------' ||
' ----- ---------');
open tab_cursor;
loop
fetch tab_cursor into
lv_owner,
lv_table_name,
lv_num_rows,
lv_chain_cnt,
lv_avg_row_len;
exit when tab_cursor%notfound;
recno := recno + 1;
if recno > 1 then
wri(' ');
end if;
wri(format_owner_table(lv_owner, lv_table_name) ||
to_char(lv_num_rows, '999999999') ||
to_char(lv_chain_cnt, '999999999') ||
to_char(lv_avg_row_len, '99999'));
open ind_cursor(lv_owner, lv_table_name);
loop
fetch ind_cursor into
now,
lv_index_name,
lv_uniqueness,
lv_distinct_keys;
exit when ind_cursor%notfound;
wri(rpad(' ' || lv_uniqueness || ' ' || lv_index_name,
40) || ' ' ||
to_char(lv_distinct_keys, '999999999'));
a_lin := ' Indexed columns: ';
open col_cursor(lv_owner, lv_index_name);
loop
fetch col_cursor into lv_column_name;
exit when col_cursor%notfound;
a_lin := vwri(a_lin, lv_column_name, 0);
end loop;
close col_cursor;
a_lin := vwri(a_lin, '', 1);
/* Analyze for proper index type */
if lv_uniqueness = 'Non-Unique' then
if lv_distinct_keys < 21 then
wri('');
wri(' *************************' ||
'**************************' ||
'***************************');
wri(' ** The above non-unique index' ||
' might not be appropriate,' ||
' since non-unique **');
wri(' ** indexes should be created' ||
' on columns which return' ||
' no more than 2-4% of **');
wri(' ** the total number of rows' ||
' in the table - Assuming' ||
' an average distribution **');
x := to_char(trunc(100 /
lv_distinct_keys));
wri(' ** of values, this index will' ||
' return ' || x ||
'% of the rows' || substr(
' ',
1, 26 - length(x)) || '**');
wri(' *************************' ||
'**************************' ||
'***************************');
wri('');
end if;
elsif lv_uniqueness = ' Bitmap' then
if lv_distinct_keys / lv_num_rows > .001 then
wri('');
wri(' *************************' ||
'**************************' ||
'***************************');
wri(' ** The above bitmap index' ||
' might not be appropriate,' ||
' since bitmap indexes **');
wri(' ** should be created on' ||
' columns having no more' ||
' than 1 unique value' ||
' per **');
x := to_char(trunc(lv_num_rows /
lv_distinct_keys));
wri(' ** 1000 rows - This index' ||
' currently has 1 unique' ||
' value per ' || x || ' rows' ||
substr(' ',
1, 12 - length(x)) || '**');
wri(' *************************' ||
'**************************' ||
'***************************');
wri('');
end if;
end if;
end loop;
close ind_cursor;
end loop;
close tab_cursor;
commit;
exception
when others then
rollback;
raise_application_error(-20000,
'Unexpected error on ' || lv_owner || '.' ||
lv_table_name || ': ' || to_char(SQLCODE) || chr(10) ||
sqlerrm || chr(10) || 'Aborting...');
end;
set termout off
spool analyze.lis
select text from anal_temp order by lineno;
spool off
drop table anal_temp;
set termout on
select 'Created analyze.lis report for your viewing pleasure...' from dual;
exit
ORACLE-L_at_fatcity.com wrote:
> Anyone have a good script to analyse stats for COST based optimizer > for the whole database.. > > Is there a procedure to do this in one of the packages? > > thanks, > Bruce > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: bruce.taneja_at_mcd.com > > 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Jan 25 2001 - 15:50:12 CST
 |
 |