NetNews Usenet Archive 1992 #18/NN_1992_18.iso/spool/comp/database/oracle/1274

Internet Message Format | 1992-08-12 | 17.6 KB

Path: sparky!uunet!dtix!darwin.sura.net!seismo!jean
From: [e-mail] (Jean Anderson)
Newsgroups: comp.databases.oracle
Subject: space management
Message-ID: <[e-mail]>
Date: 13 Aug 92 04:39:42 GMT
Sender: [e-mail]
Organization: SAIC Open Systems Division, San Diego CA
Lines: 507
Nntp-Posting-Host: beno.css.gov

Somebody requested the script that generates a report I mentioned in a recent
post:

>We routinely run 'actual vs. allocated' reports to see where we can recover
>deleted space. We 'exp compress=y', 'imp indexfile=create.sql', and alter
>the initial storage downward in the create script to a more reasonable
>allocation.
>
>A snippet from last night's report shows massive deletion in one of the tables:

                                       Blocks      Bytes        Bytes  Pct % of
Segment Name                Rows  Ext   Alloc      Alloc         Used Used Data
--------------------- ---------- ---- ------- ---------- ------------ ---- ----
ARCHORID        DATA           2    4      30      61440        2,048    3
  ARCHORDX      INDEX               2      10      16826        5,680   34  277

KAUDIT          DATA     273,885   49    6092   12476416   12,288,000   98
  DOBJIDX       INDEX               5    4493    7678068    4,903,528   64   40

WFDISC          DATA       2,874   13    3391    6944768      464,896    7


I added to the original snippet from the reports that ran this week. :-)
I look for:

    o  Low "Pct Used"
       WFDISC will be recreated smaller to free up wasted space.
    o  High "Ext"ents
       KAUDIT will be recreated larger to reduce fragmentation.
    o  Peculiar "% of data"
       This compares size of the index to size of the base data.  ARCHORID is
       too small to bother about, but I occasionally see indexes that are 2-3
       times the size of the base table. Dropping and recreating the index
       reduces it to a normal 10-20% of the base data (average for our
       datasets).

The scripts that generate this report are down below and are:

dba_actsize:     The main work horse stuffs info into a temp table.
dba_actsize_rpt: Invoked by dba_actsize, this generates a report from the temp
                 table.  It is a separate sql*plus script because it is dense.
                 I wanted to leave a hook for other "prettier" reports.
Sample usage:

    I normally set up cron to run one or two reports each night on the "top
    hogs".  Reports are run on each hog once a week. It takes about an hour
    to run on a 200meg account (SUN 4/330).

    sqlplus -s dba/xxx @dba_actsize geodemo.out geodemo dba_actsize_rpt
                |           |          |           |         |
                |         script       |           |         +=> report script
                |                      |           +=> user name
                |                      +=> output file name
                +=> I set it up so a DBA must run it because it is a serious
                    hog. You may want to change it so non-DBA users can run it.

I started with Maurice Manton's ACTUAL_SIZE.SQL script, added INDEX information.
The script does PL/SQL updates, so my apologies, but this won't help you if
you don't have pl/sql.

It's something of a muddle, full of quickie kludges and stupid tricks to get
something up and running fast.  All suggestions for fixes are welcome! I'm
going to redesign/rewrite it entirely in pl/sql which will make it much more
readable.

    - jean

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  [e-mail]  |
| SAIC Open Systems Division, MS A2-F         or:  [e-mail]  |
| 10210 Campus Point Drive                 phone:  [phone]        |
| San Diego, CA  92121                       fax:  [phone]        |
+-----------------------------------------------------------------------+
|                     std_disclaimer("mine");                           |
+-----------------------------------------------------------------------+

-----------------------------< dba_actsize.sql >------------------------------
rem FILE
rem    dba_actsize.sql
rem
rem DESCRIPTION
rem    ORACLE stores how much space has been allocated to objects in the
rem    SYS.USER_SEGMENTS and SYS.DBA_SEGMENTS data dictionary views.
rem    Actual usage, however, is not stored.
rem
rem    This script reports allocated and actual space used by tables and
rem    indexes belonging to a given user.
rem
rem    It extracts a list of tables and indexes from sys.dba_segments and
rem    generates a second SQL script, space_temp.sql, with the actual
rem    commands necessary for calculating space.
rem
rem    This script is a hog. It must be run by an account with dba privileges
rem    and care should be taken not to run it on a large account during
rem    database prime time usage.
rem
rem USAGE
rem    sqlplus -s dba/password @dba_actsize output_filename user_id report_name
rem
rem NOTES
rem    This section describes how actual usage is calculated.
rem
rem    CALCULATING SPACE USED BY TABLES
rem    --------------------------------
rem    ORACLE tags each record with a unique rowid that contains the datafile,
rem    the block number, and the rownumber:
rem
rem        SQL> select arid, time, rowid, substr(rowid,15,4) filedata,
rem          2  substr(rowid,1,8) block, substr(rowid,10,4) rownumber
rem          3  from arrival;
rem
rem             ARID            TIME ROWID              FILE BLOCK    ROWN
rem         -------- --------------- ------------------ ---- -------- ----
rem           448579   654362719.300 0000764D.0000.0004 0004 0000764D 0000
rem           448581   654362727.500 0000764D.0001.0004 0004 0000764D 0001
rem           448582   654362732.150 0000764D.0002.0004 0004 0000764D 0002
rem                                  -------- ---- ----
rem                                  block    row  file
rem
rem    To find out how much space a table actually uses, count the number of
rem    unique blocks in that table's rowid.  Since a single table could span
rem    multiple database files and the same block number could show up in
rem    both files, count the number of distinct block/file combinations:
rem
rem        select count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))
rem        from <table>;
rem
rem    This query is lifted from the ORACLE 6 DBA Guide, Chapter 6, page 10.
rem    Note this is imprecise; it tells how many blocks are occupied, but
rem    it won't say how much of each block is occupied.  A given block may
rem    have 1 row in it--and yet be counted as a full 2048 byte ORACLE block.
rem
rem    CALCULATING SPACE USED BY INDEXES
rem    ---------------------------------
rem    The VALIDATE INDEX command populates the INDEX_STATS table and contains:
rem
rem        BLOCKS:       the blocks allocated to the INDEX
rem        BTREE_SPACE:  total bytes allocated to the btree
rem        USED_SPACE:   total bytes actually used
rem        PCT_USED:     % of the BTREE_SPACE used (USED_SPACE/BTREE_SPACE)
rem
rem    The % used figure in this report is based on USED_SPACE/BLOCKS.
rem
rem  BUGS
rem    This report is exceptionally labor-intensive to change. Make changes at
rem    your own risk. Debug it by setting 'echo', 'termout', and 'verify' on.
rem
rem    The V7 version will be written entirely in PL/SQL, making it straight
rem    forward to read and debug.
rem
rem  ACKNOWLEDGEMENTS
rem    This bounces off a script and paper by Maurice C. Manton III titled
rem     "Space, The Great Unknown", presented at the 1990 International Oracle
rem     User Group meetings.
rem
rem    Funding for the development of this implementation was provided by
rem    DARPA.
rem
rem  AUTHOR
rem     Jean T. Anderson, SAIC Open Systems Division, November 1991
rem
rem  modification history:
rem
rem    jta    11/05/91    Created -- works with V6.0.30.3.1.
rem    jta    11/14/91    Made space_temp.sql filename unique.
rem    jta    11/15/91    Made temp table and index name unique.
rem    jta    01/13/92    Expanded temp table to include data from
rem                INDEX_STATS view.
rem    jta    03/23/92    Certified for rdbms V6.0.33.2.2.
rem    jta    08/12/92    Certified for rdbms V7.0.9.3.1, but it sure
rem                ain't the right way to do it for V7!
rem  SEE ALSO
rem    dba_space(1)
rem    DBA Guide, Chapter 6
rem    OLS bulletin board article 99751.686 "INDEX STATISTICS"
rem
rem  SccsID @(#)dba_actsize.sql    48.3 8/12/92

WHENEVER SQLERROR EXIT FAILURE

define    filename = &1
define    user_id = &2
define    report_name = &3
define    dot="."

rem    SESSIONID, the user's auditing identifier, is used for creating unique
rem    names for the intermediate sql script, the results table, and the index.

column    uniq_id    new_value sid noprint
column    spfile    new_value spoolfile noprint
column    ttable    new_value space_temp noprint
column    tindex    new_value space_index noprint
column    tmesg    new_value plmesg noprint

rem    Some Oracle installations have pause set for interactive work
set    pause off

rem    Because of the "noprint", blank lines get output to stdout.
rem    Suppress output of these blank lines with 'termout off'
set    termout off

select    userenv('SESSIONID')    uniq_id
from    dual
/

select    &sid||'space_temp.sql'    spfile,
    'space_temp'||&sid    ttable,
    'spacendx'||&sid    tindex,
    'plmesg'||&sid        tmesg
from    dual
/

rem    The SQL code generated by this script must contain single quotes.
rem    s_quote is a trick to surround character strings with single quotes in
rem    space_temp.sql.  But s_quote itself has to be separated from the &,
rem    otherwise it will get immediately expanded; hence the '&'||'s_quote.'
rem    syntax in the queries below.
define    s_quote = ''''

set    echo off
set     verify off
set     heading off
set     pagesize 0
set     feedback off

rem    -- Create the temp table.
create    table &space_temp (
    owner        varchar(30),
    table_name    varchar(30),
    segment_name    varchar(30),
    segment_type    varchar(8),
    recnum        number,
    blocks_alloc    number,
    bytes_alloc    number,
    bytes_used    number,
    pct_used    number,
    pctind        number);

create    table &plmesg (seqno number(4), mesgtext varchar(255), lddate date);

spool    &spoolfile

rem    --- First generate SQL to perform TABLE calculations --

select    'insert into &'||'space_temp ',
    '(owner,table_name,segment_name,segment_type,recnum,bytes_used)',
    ' select ',
    '&'||'s_quote.' || owner ||'&'||'s_quote, ',
    '&'||'s_quote.' || segment_name ||'&'||'s_quote, ',
    '&'||'s_quote.' || segment_name ||'&'||'s_quote, ',
    '&'||'s_quote.TABLE&'||'s_quote, ',
    'count(*), ',
    'count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))*2048 blocks',
    ' from ', owner||'.'||segment_name, ';'
from    sys.dba_segments
where    segment_type = 'TABLE'
and    owner=upper('&user_id');

rem    --- Now generate SQL to validate indexes ---
rem    Later updates to fill in the table_name from sys.dba_indexes are
rem    remarkably expensive, so join in that information on the insert here.
rem    However, to also fill in extents requires a third join to
rem    sys.dba_segments--and that third join turns out to be costly.  Since
rem    extents doesn't figure in any other calculations, sys.dba_segments
rem    will be joined in at the time the report is generated.
rem
rem    The rpad(';', 79, ' ') forces a line feed after the semicolon so the
rem    validate index command will execute before the insert. Perhaps there
rem    is a better way to force a linefeed?

select 'validate index ', owner||'.'||segment_name, rpad(';', 79, ' '),
    'insert into &'||'space_temp ',
    '(owner,table_name,segment_name,segment_type,blocks_alloc,bytes_alloc,bytes_used,pct_used)',
    'select ',
    'UPPER(&'||'s_quote.&user_id&'||'s_quote), ',
    'd.table_name, i.name, ',
    '&'||'s_quote.INDEX&'||'s_quote, ',
    'i.blocks, i.btree_space, i.used_space, i.pct_used ',
    'from sys.dba_indexes d, index_stats i ',
    'where d.index_name=i.name and d.owner=',
    'UPPER(&'||'s_quote.&user_id&'||'s_quote);'
from    sys.dba_segments
where    segment_type = 'INDEX'
and    owner=UPPER('&user_id');

spool off
/

rem    -- Run the script --

start &spoolfile
host    rm -f &spoolfile

rem    -- Set an index for updating statistics in temp table
rem    -- This will commit pending changes

create unique index &space_index on &space_temp (owner, segment_name) pctfree=0;

rem    -- Update TABLE information
rem    Correlated UPDATES are notoriously slow (see Tech Bulletin #99590.164).
rem    PL/SQL is a much faster way to do these updates, which now include:
rem       (1)    TABLE: get blocks_alloc from sys.dba_segments (INDEXES got
rem        it from the INDEX_STATS table). segments_cursor does this.
rem       (2)  TABLE: Update bytes_alloc and pct_used (space_cursor).
rem       (3)  INDEX: Update size of index/size of base data (space_cursor).
rem
set    termout on
DECLARE
        CURSOR  segments_cursor is
            SELECT  d.owner, d.segment_name, d.segment_type, d.blocks
            FROM    sys.dba_segments d
            WHERE   d.owner=UPPER('&user_id')
          AND    d.segment_type = 'TABLE';

        CURSOR  space_cursor IS
                SELECT  table_name, segment_type, blocks_alloc, bytes_alloc,
            bytes_used, pct_used, pctind
                FROM    &space_temp
                ORDER   by table_name asc, segment_type desc
                FOR    UPDATE OF bytes_alloc, pct_used, pctind;

    segments_ptr    segments_cursor%ROWTYPE;
    space_ptr    space_cursor%ROWTYPE;

        tbytes          &space_temp.&dot.bytes_used%TYPE;
    msg        &plmesg.&dot.mesgtext%TYPE;
    badcount    number:= 0;
BEGIN

                -- Update blocks allocated to tables. Owner is redundant in
        -- this query, but is included so the space_temp index is used.
    OPEN segments_cursor;
    LOOP
        FETCH segments_cursor INTO segments_ptr;
        EXIT WHEN segments_cursor%NOTFOUND;

                   UPDATE  &space_temp
                   SET     blocks_alloc=segments_ptr.blocks
                   WHERE   owner=segments_ptr.owner
          AND    segment_name=segments_ptr.segment_name;

        END LOOP;
    CLOSE    segments_cursor;
        COMMIT;

        -- Update bytes_alloc and pct_used for TABLES.
        -- Update pctind for INDEXES.

    OPEN    space_cursor;
    LOOP
        FETCH space_cursor INTO space_ptr;
        EXIT WHEN space_cursor%NOTFOUND;

                IF      space_ptr.segment_type = 'TABLE'
                THEN
            UPDATE    &space_temp
            SET    bytes_alloc=space_ptr.blocks_alloc*2048,
                pct_used=round(space_ptr.bytes_used/(space_ptr.blocks_alloc*2048)*100)
            WHERE   CURRENT OF space_cursor;

            tbytes := space_ptr.bytes_used;
                ELSE
            IF tbytes > 0
            THEN
                            UPDATE  &space_temp
                SET    pctind = round(space_ptr.bytes_used/tbytes*100)
                            WHERE   CURRENT OF space_cursor;
            END IF;
                END IF;
        END LOOP;
    CLOSE    space_cursor;
        COMMIT;

EXCEPTION
    WHEN ZERO_DIVIDE THEN
        badcount := badcount+1;
        msg := 'Cannot divide by 0';
        insert into &plmesg(seqno, mesgtext, lddate)
        values (badcount, msg, sysdate);
        commit;
    WHEN OTHERS THEN
        badcount := badcount+1;
        msg := substr(SQLERRM, 1, 70);
        insert into &plmesg(seqno, mesgtext, lddate)
        values (badcount, msg, sysdate);
        commit;
END;
/

rem   -- Output any errors from PL/SQL block
column    seqno format 999 heading x NOPRINT
column    mesgtext format A58 WORD_WRAPPED
column    lddate format A18
select    seqno, mesgtext, '  ', to_char(lddate,'DD-Mon-YY HH24:mi:ss') lddate
from    &plmesg
where    seqno > 0
order    by 1;

set termout off
rem    -- Output the space report. --

start &report_name &filename &space_temp

drop table &space_temp;

drop table &plmesg;

exit SUCCESS
---------------------------------< dba_actsize_rpt.sql >----------------------
rem  FILE
rem    dba_actsize_rpt.sql
rem
rem  DESCRIPTION
rem    This outputs a report from the temporary table created by dba_actsize.
rem
rem  USAGE
rem    (called by dba_actsize)
rem
rem    start dba_actsize_rpt filename table_name
rem
rem  AUTHOR
rem    Jean Anderson, SAIC Open Systems Operation, November 1991
rem
rem SccsID =@(#)dba_actsize_rpt.sql    48.1 6/19/92
define reportname="dba_actsize_rpt.sql (v48.1)"

define    filename = &1
define    tablename = &2

rem    Some Oracle installations have pause set for interactive work
set    pause off

rem    -- Output report. --

set    pagesize 62
rem    newpage forces a formfeed
set    newpage 0
set    linesize 79
set    heading on
set    feedback off

column    segment_name    format A21 HEADING "Segment Name";
column    recnum        format 9,999,990 HEADING "Rows"
column    extents        format 990 HEADING "Ext";

rem    -- A table with 999,999 blocks would be 2 gigabytes, but 99999 blocks
rem    -- would only allow for 200 meg, so go ahead and give it the extra.
column    blocks_alloc    format 999990 HEADING "Blocks|Alloc";
column    bytes_alloc    format 999999990 HEADING "Bytes|Alloc";
column    bytes_used    format 999,999,990 HEADING "Bytes|Used";
column    pct_used    format 990 HEADING "Pct|Used";
column    pctind        format 990 HEADING "% of|Data"

rem    -- break_column is used to suppress the output of ***** and 'sum'
column    break_column    noprint
column    today new_value rpt_date noprint
select    to_char(sysdate, 'dd-Mon-yy hh24:mi') today from dual;

rem    -- this for sorting
column  sort_column noprint

break    on table_name on break_column skip 1 on report;
compute sum of blocks_alloc on break_column;
compute sum of blocks_alloc on report;
compute sum of bytes_alloc on break_column;
compute sum of bytes_alloc on report;
compute sum of bytes_used on break_column;
compute sum of bytes_used on report;
compute sum of pctind on break_column;

ttitle  skip center "Space Utilization Report for User '&user_id' on &rpt_date" -
    skip 2
/
btitle    skip left '&reportname' -
    right 'Page' FORMAT 99 sql.pno
/

rem    The decode statement below forces index names to be indented two
rem    characters below the parent table name.

select    b.table_name    break_column,
    b.segment_type    sort_column,
    decode(b.segment_type,
        'TABLE',
        rpad(substr(b.segment_name,1,15),16)||'DATA',
        'INDEX',
        '  '||rpad(substr(b.segment_name,1,13),14)||'INDEX')
        segment_name,
    b.recnum,
    a.extents,
    b.blocks_alloc,
    b.bytes_alloc,
    b.bytes_used,
    b.pct_used,
    b.pctind
from    sys.dba_segments a, &tablename b
where    a.segment_name=b.segment_name
  and    a.owner=b.owner
order    by b.table_name asc, b.segment_type desc, b.segment_name asc

spool    &filename
/
spool off
/

exit
---------------------------------< end post >----------------------------------