SQL Script List Table Statistics

Updated: 26-Sep-2005
Version: 2.1

Description

Uses the DBMS_STATS package to list the table, index and column statsistics

Parameters

own - Owner
tab - Table Name

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set serveroutput on size 1000000
set verify off 


ACCEPT own PROMPT 'Enter value for owner (RETURN for ALL) > '
ACCEPT tab PROMPT 'Enter value for table name (RETURN for ALL) > '     

spool getstats_&own..lst


declare 
cursor get_tab IS SELECT owner,table_name, last_analyzed FROM all_tables 
                  WHERE owner like UPPER('%&own%')
                  AND table_name like UPPER('%&tab%')
                  ORDER BY owner,table_name;
cursor get_index IS SELECT owner, index_name, last_analyzed FROM all_indexes
                  WHERE owner like UPPER('%&own%')
                  AND table_name like UPPER('%&tab%')
                  ORDER BY owner, index_name;
cursor get_cols IS SELECT owner,table_name,column_name,last_analyzed FROM all_tab_columns
                  WHERE owner like UPPER('%&own%')
                  AND table_name like UPPER('%&tab%')
                  ORDER BY owner,table_name,column_name;
t_numrows number;
t_numblks number;
t_avgrlen number;
i_numrows number;
i_numlblks number;
i_numdist number;
i_avglblk number;
i_avgdblk number;
i_clstfct number;
i_indlevel number;
c_distcnt number ;
c_density number;
c_nullcnt number;
c_srec dbms_stats.StatRec;
c_avgclen number;

begin
for tab in get_tab loop
IF tab.last_analyzed is not null THEN
dbms_stats.get_table_stats (ownname => tab.owner
                , tabname => tab.table_name
                ,numrows => t_numrows
                ,numblks => t_numblks
                ,avgrlen => t_avgrlen );
dbms_output.put_line 
  ('Table Stats: '|| tab.owner||'.'||tab.table_name||' NUM_ROWS '||t_numrows);
dbms_output.put_line 
  ('Table Stats: '|| tab.owner||'.'||tab.table_name||' BLKS '||t_numblks);
dbms_output.put_line 
  ('Table Stats: '|| tab.owner||'.'||tab.table_name||' AVG_ROW_LEN '||t_avgrlen);
dbms_output.put_line ('Table Stats: '||tab.table_name
                        ||' Last Analyzed :'||tab.last_analyzed);

FOR idx IN get_index LOOP
IF idx.last_analyzed is not null THEN
dbms_stats.get_index_stats  (ownname => idx.owner
                           , indname => idx.index_name
                           , numrows => i_numrows 
                           , numlblks => i_numlblks 
                           , numdist => i_numdist 
                           , avglblk => i_avglblk 
                           , avgdblk => i_avgdblk 
                           , clstfct => i_clstfct 
                           , indlevel => i_indlevel
                                );
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' NUM_ROWS '||i_numrows);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' NUM_LBLKS '||i_numlblks);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' NUM_DIST '||i_numdist);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' AVGLBLK '||i_avglblk);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' AVGDBLK '||i_avgdblk);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' CLSTFCT '||i_clstfct);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' IND_LEVEL '||i_indlevel);
dbms_output.put_line ('Index Stats: '||idx.owner||'.'||idx.index_name  
                        ||' Last Analyzed : '||idx.last_analyzed);
ELSE
   dbms_output.put_line ('Index Stats: '||idx.index_name 
                        ||' Index Not Analyzed.');
END IF;
END LOOP;
FOR col in get_cols LOOP
IF col.last_analyzed is not null THEN
   dbms_stats.get_column_stats (ownname => col.owner
                            ,tabname => col.table_name
                            ,colname => col.column_name
                            ,distcnt => c_distcnt
                            ,density => c_density
                            ,nullcnt => c_nullcnt
                            ,srec => c_srec
                            ,avgclen => c_avgclen
                                );

   dbms_output.put_line ('Col Stats: '||col.table_name||'.'||col.column_name
                        ||' DISTCNT '||c_distcnt);
   dbms_output.put_line ('Col Stats: '||col.table_name||'.'||col.column_name
                        ||' DENSITY '||c_density);
   dbms_output.put_line ('Col Stats: '||col.table_name||'.'||col.column_name
                        ||' NULLCNT '||c_nullcnt);
   dbms_output.put_line ('Col Stats: '||col.table_name||'.'||col.column_name
                        ||' AVGCLEN '||c_avgclen);
   dbms_output.put_line ('Col Stats: '||col.table_name||'.'||col.column_name
                        ||' Last Analyzed :'||col.last_analyzed);
ELSE
   dbms_output.put_line ('Col Stats: '||col.table_name||'.'||col.column_name
                        ||' Column Not Analyzed.');
END IF;
END LOOP;


ELSE
   dbms_output.put_line ('Table Stats: '||tab.table_name
                        ||' Table Not Analyzed.');
END IF;

end loop;
end;
/

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links