FAQ--Calculating Actual Space Used
Date: 19 Jul 92 22:11:59 GMT
Message-ID: <50975_at_seismo.CSS.GOV>
This FAQ consolidates several postings on how to calculate actual space used by tables and indexes. I have tried to reduce verbiage to lower the byte count.
The topics are:
- V6: How to determine actual bytes used by a table
- V6: actual_size.sql, SQL*Plus script that reports space used by a table
- V7: How to determine actual bytes used by a table
- V6: How to determine actual bytes used by an index
- V6: ind_stats.sql, SQL*Plus script that reports space used by an index
- V7: How to determine actual bytes used by an index
Each topic begins with the number flush against the left margin to make it easy to search down with an editor to a given topic.
I have included some sketchy details on V7 because space management changes for the better. So don't invest lots of effort in V6 space management routines if you anticipate upgrading to V7 as soon as the production release becomes available.
This posting is not the production of ORACLE Corp*. All errors, opinions, etc. are mine and I admit to having a Unix/rdbms/DBA bias. Feel free to email me corrections, additions or editorial comments and I'll do my best to incorporate them into a repost.
- jean
Jean Anderson, SAIC Open Systems Division phone: (619)458-2727 10210 Campus Point Drive, MS A2-F fax: (619)458-4993 San Diego, CA 92121 email: jean_at_esosun.css.gov * Any opinions are mine, not my employer's. jean_at_seismo.css.gov ==============================================================================
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
- V6: How to determine actual bytes used by a table
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
From: jean_at_beno.CSS.GOV (Jean Anderson)
Newsgroups: comp.databases
Subject: Re: Oracle Free Space or Space used - DBA
Message-ID: <49911_at_seismo.CSS.GOV>
Date: 15 Aug 91 03:11:37 GMT
[... lots deleted ...]
ORACLE tags each record with a unique rowid that contains the datafile, the block number, and the rownumber. To find out how much space a table actually uses, count the number of unique blocks in that table's rowid. Since a single table could span multiple database files and the same block number could show up in both files, count the number of distinct block/file combinations (query is lifted from the ORACLE 6 DBA Guide, Chapter 6, page 10):
select count(distinct(substr(rowid,1,8)||substr(rowid,15,4))) from <table>;
Mind you, it doesn't tell you "how occupied" that block is so partially filled blocks will get counted the same as 100% filled blocks. But it gets me within 5% of reality, which is close enough for what I need.
I am including a script below that was distributed at the last IOUG. Maurice verbally told me at that time it could be freely distributed, so I assume it's ok to post it here. If not, my apologies in advance.
[ IOUG script moved down to Topic #2, rest deleted ... ]
>>>>>>>>>>>>>>>>>>>>>>
2. V6: actual_size.sql
>>>>>>>>>>>>>>>>>>>>>>
REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% %%% REM %%% ACTUAL_SIZE.SQL %%% REM %%% %%% REM %%% This SQL script uses the following SYS view %%% REM %%% USER_SEGMENTS. It also uses a table named %%% REM %%% TEMP_SIZE_TABLE which contains the table name and %%% REM %%% the number of blocks in use. These views and table are %%% REM %%% used to compare the number of blocks allocated to a %%% REM %%% table to the actual number of blocks used by the same %%% REM %%% table's data. %%% REM %%% This script creates a second SQL script which %%% REM %%% is made up of inserts. These inserts select the %%% REM %%% tablename and block-in-use count to be used by the %%% REM %%% actual report select statement. This secondary file is %%% REM %%% named FILL_SIZE_TABLE.SQL and can be discarded as soon %%% REM %%% as the script has finished running. %%% REM %%% %%% REM %%% This script is designed to be run in an account %%% REM %%% with the RESOURCE privilege. %%% REM %%% %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% %%% REM %%% Oracle Version 6 IOUG Sept 1990 %%% REM %%% Author: Maurice C. Manton III %%% REM %%% %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM SET TERM OFF; TTITLE OFF; BTITLE OFF; SET PAGESIZE 0; SET VERIFY OFF; SET HEADING OFF; SET RECSEP OFF; REM CREATE TABLE temp_size_table( table_name CHAR(30), blocks NUMBER); REM SPOOL fill_size_table.sql REM REM Note: REM Script corrected by Steve Lindeman in <1254_at_usdsd1.DaytonOH.NCR.COM> REM (slindemn_at_usdsd1.DaytonOH.NCR.COM) to report accurate results for REM tablespaces containing multiple datafiles. REM SELECT 'INSERT INTO temp_size_table', ' SELECT ','&'||'temp_var.' || segment_name ||'&'||'temp_var', ', COUNT( DISTINCT( SUBSTR( ROWID,1,8) || SUBSTR( ROWID,15,4))) blocks', ' FROM ', segment_name, ';' FROM user_segments WHERE segment_type = 'TABLE'; REM SPOOL OFF; REM DEFINE temp_var = ''''; START fill_size_table;
HOST rm fill_size_table.sql;
REM
compute sum of blocks on report; compute sum of act_blocks on report; break on report; REM SET TERM ON; TTITLE ON; BTITLE OFF; REM SET VERIFY ON; SET HEADING ON; SET LINESIZE 80; SET PAGESIZE 60; SET NEWPAGE 0; REM COLUMN t_date NOPRINT new_value t_date; COLUMN user_id NOPRINT new_value user_id; REM SELECT sysdate t_date, user user_id FROM dual; REM COLUMN segment_name FORMAT A20 HEADING "SEGMENT|NAME"; COLUMN segment_type FORMAT A7 HEADING "SEGMENT|TYPE"; COLUMN extents FORMAT 999 HEADING "EXTENTS"; COLUMN bytes FORMAT A6 HEADING "BYTES"; COLUMN blocks FORMAT 9,999,999 HEADING "ORACLE|BLOCKS"; COLUMN act_blocks FORMAT 9,999,999 HEADING "ACTUAL|USED|BLOCKS"; COLUMN pct_block FORMAT 999.99 HEADING "PCT|BLOCKS|USED"; REM SPOOL &user_id._actual_to_allocated_space REM TTITLE LEFT t_date RIGHT 'PAGE: ' FORMAT 999 SQL.PNO SKIP 1 - CEN "&user_id ACTUAL VS ALLOCATED STORAGE REPORT" SKIP 2; REM SELECT segment_name, segment_type, extents, to_char( bytes/1024)||'K' bytes, a.blocks, b.blocks act_blocks, b.blocks/a.blocks*100 pct_block FROM sys.user_segments a, temp_size_table b WHERE segment_name = UPPER( b.table_name ); REM SPOOL OFF; REM REM DROP TABLE temp_size_table; REM REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% END OF SCRIPT. %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%exit
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3. V7: How to determine actual bytes used by a table
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The ANALYZE TABLE command populates USER_TABLES with statistics about the table, including number of rows and number of bytes used. You have the option of calculating exact statistics or estimates.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4. V6: How to determine actual bytes used by an index
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The VALIDATE INDEX command populates the INDEX_STATS view with information about the index, including bytes allocated and used.
>>>>>>>>>>>>>>>>>>>
5. 6: ind_stats.sql
>>>>>>>>>>>>>>>>>>>
Date: Tue, 20 Aug 91 14:08:19 EDT
From: tjr25%CAS.BITNET_at_CUNYVM.CUNY.EDU (Terri Roden x2410 4438B)
Subject: Space used in indexes
To: jean_at_beno.CSS.GOV
Jean,
I saw your note on estimating space used within tables. That's the best way we've come up with estimating it also. Here is how we do indexes. I never found it documented very well but the "validate index" command can be very useful. Try describing the index_stats table and look at the comments on each column. Here's the script I use that I've found pretty reliable...I'd say your within 5% probably holds here too!
rem ind_stats.sql
rem
rem v1/l1 4/4/91 T. J. Roden
rem
rem Use the VALIDATE INDEX command to report on index usage and space.
rem You can only run the VALIDATE command if you either own the index
rem or are a DBA. VALIDATE creates a dynamic x$ table that holds the
rem results of only the last validate command. Each person/session has
rem it's onn x$ table.
rem
rem Creates a file in current directory, index_name.lst
rem
prompt
PROMPT ~ Usage: _at_ind_stats index_name
PROMPT
define I = &1
rem Use next line if you want to be prompted rather than command line input rem accept I char prompt 'Enter index name:'
set verify off
column "% USED" format 99.99
rem spool &I
validate index &I;
select name "INDEX_NAME",
blocks * 2048 "BYTES ALLOCATED",
btree_space "BYTES USED",
(btree_space/(blocks * 2048)) * 100 "% USED"
FROM INDEX_STATS;
rem spool off
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
6. V7: How to determine actual bytes used by an index
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The ANALYZE INDEX command populates USER_INDEXES with the information previously stored in INDEX_STATS, except for max/min values which are stored in USER_TAB_COLUMNS. Received on Mon Jul 20 1992 - 00:11:59 CEST