space management

From: Jean Anderson <jean_at_seismo.CSS.GOV>
Date: 13 Aug 92 04:39:42 GMT
Message-ID: <51074_at_seismo.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 _at_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:  jean_at_seismo.css.gov  |
| SAIC Open Systems Division, MS A2-F         or:  jean_at_esosun.css.gov  |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
 +-----------------------------------------------------------------------+
|                     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 _at_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 _at_(#)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 =_at_(#)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 >----------------------------------

Received on Thu Aug 13 1992 - 06:39:42 CEST