Re: DB Space Utilization Reports Needed

From: Joe Minnich <jminnich_at_bugaboo.ssnet.com>
Date: 19 Mar 1994 01:52:20 GMT
Message-ID: <2mdlsk$2ad_at_marlin.ssnet.com>

In article <1994Mar17.153041.21734_at_balrog.dseg.ti.com>, garrett_at_balrog.dseg.ti.com (Josh Garrett) writes:

|> I've just started getting into Oracle and have been running some
|> stress tests of our application.  Problem is, how can you determine
|> the actual bytes used vs. bytes allocated by table and by index?
|> If anyone could forward some information or SQL for reporting, it
|> would be much appreciated.

I have developed xorafree utility script that we run from cron bi-monthly to evaluate our tablespace freespace/fragmentation, segment extent and block allocation. The output can be optionally filtered for error thresholds. Specific types of free space checking can be specified via command line switches. The alarm/error output then gets mailed to several people for all of our databases.

The only method that I am currenlty aware of for evaulating actual block usage on table and index segments is not real clean.

Tables: % blocks used - estimate

   (count(distinct(rowid file||block))) / blocks allocated

Indexes: % blocks used

    validate the index
    this will place index statistics in an X$ kernel table which is     accessable via the oracle supplied view index_stats     (leaf_blocks+branch_blocks) / blocks allocated

This must be done for each segment of interest. It can be a time consuming depending on the size of your segments. Here are some of the xorafree sql code pieces which handle building a temp sql script for tables and indexes.

Tables:

---

if [ "${ORAblkinfo}" ]; then
  f_debug "Including table block stats"
  cat >> ${ORAsqltmp} <<!
spool ${ORAblktmp}

select 'set head off;                                              ',


       'set verify off;                                            ',

       'set echo off;                                              ',

       'set pages 0;                                               ',

       'set termout off;                                           ',

       'set feedback off;                                          ',

       'set recsep off;                                            ',

       'set doc off;                                               ',


       'spool ${ORAblkdata};'

from dual;
col Brdate format a11;
col Bsegname format a31 trunc;
col Bblks format 99999990;
col Bpct format 99.90;
set space 0;

select 'select ''B '',                                              ',


       '  to_char(sysdate,''YYMMDDHH24MI'') Brdate,                 ',

       '  '''||segment_name||''' Bsegname,                          ',

       '  '||blocks||' Bblks,''  '',                                ',

       '  trunc(count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))/',

       '       '||blocks||'*10000)/100 Bpct                            ',


       'from  '||owner||'.'||segment_name||';                          '

from dba_segments
where segment_type = 'TABLE'
and tablespace_name ${ORAtspsel2}
and tablespace_name != 'SYSTEM'
and segment_name ${ORAtabsel}
order by segment_name;

spool off;
start ${ORAblktmp};
!

Indexes:

---

if [ "${ORAidxinfo}" ]; then
  f_debug "Including index stats info"
  cat >> ${ORAsqltmp} <<!
spool ${ORAivaltmp}

select 'set head off;                                              ',


       'set verify off;                                            ',

       'set echo off;                                              ',

       'set pages 0;                                               ',

       'set termout off;                                           ',

       'set feedback off;                                          ',

       'set recsep off;                                            ',

       'set doc off;                                               ',

       'set space 0;                                               ',


       'spool ${ORAidxdata};                                       '


from  dual;
select 'validate index '||owner||'.'||index_name||';'               ,


       'select ''I ''||                                             ',

       '  to_char(sysdate,''YYMMDDHH24MI'')||'' ''||                ',

       '  rpad(name,20,'' '')||'' ''||                              ',

       '  lpad(lf_rows,09,'' '')||'' ''||                           ',

       '  lpad(blocks,06,'' '')||'' ''||                            ',

       '  lpad(lf_blks,06,'' '')||'' ''||                           ',

       '  lpad(br_blks,05,'' '')||'' ''||                           ',

       '  lpad(trunc((lf_blks+br_blks)/blocks*100)                  ',

       '       ,3,'' '')||'' ''||                                   ',

       '  lpad(trunc(del_lf_rows_len/decode(lf_rows_len,0,1,lf_rows_len)*100)',

       '       ,3,'' '')||'' ''||                                   ',

       '  lpad(trunc(most_repeated_key/decode(lf_rows,0,1,lf_rows)*100)',

       '       ,3,'' '')                                            ',

       'from  index_stats                                           ',

       'where trunc((lf_blks+br_blks)/blocks*100) >= ${EXCidxuse}   ',

       'or    trunc(del_lf_rows_len/decode(lf_rows_len,0,1,lf_rows_len)*100)',


       '        >= ${EXCidxdel};                                    '

from dba_indexes
where tablespace_name ${ORAtspsel2}
and tablespace_name != 'SYSTEM'
and table_name ${ORAtabsel}
order by index_name;
spool off;
start ${ORAivaltmp};
!
fi

Hope this helps. Thanks,

---

joseph r minnich jminnich_at_bugaboo.ssnet.com Received on Sat Mar 19 1994 - 02:52:20 CET