Re: Tablespace

From: Jean Anderson <jean_at_esosun.UUCP>
Date: 5 Jun 92 20:50:14 GMT
Message-ID: <974_at_esosun.UUCP>

In <1254_at_usdsd1.DaytonOH.NCR.COM>, slindemn_at_usdsd1.DaytonOH.NCR.COM (Steve Lindeman):
> I have (re)posted a series of articles that deal with determining space usage
> for an ORACLE database.

Let's add calculation of index space to the thread. I thought I posted Terry's email below, but I didn't keep a copy and it hasn't been included in any of the reposts.

I'm also including RTSS Bulletin #99751.686 (without permission) that describes the output from the VALIDATE INDEX command.

Calculating space usage seems to be a recurring topic. If folks are interested in a single, consolidated FAQ, let me know. If you'll inundate me with your tricks and tips, I'll try to consolidate them with the current thread into a single FAQ (with all fixes to posted scripts applied).

• jean SAIC Open Systems Division (Geophysical Systems Operation) jean_at_esosun.css.gov

Date: Tue, 20 Aug 91 14:08:19 EDT
From: tjr25%CAS.BITNET_at_CUNYVM.CUNY.EDU (Terri Roden x2410 4438B) Message-Id: <9108201408.AA4288_at_cas.org> 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 foun 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
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

--------------------------< RTSS Bulletin #99751.686 >-------------------------
INDEX STATISTICS                                                           RDBMS
Steve S. Tonas                                                         V6.0.30.3

October 26, 1990

     A user can gather index statistics by doing the following:

SQL>  validate index INDEX_NAME;
SQL>  connect SYS/SYS_PASSWORD
SQL>  select * from INDEX_STATS;
SQL>  select * from INDEX_HISTOGRAM;

     INDEX_STATS and INDEX_HISTOGRAM are views that contain statistics

on the last index validated. So, what do all the columns in these views mean? Here is a description of each:

INDEX_STATS Column Name     Description
-----------------------     ----------------------------------------------------
HEIGHT                      height of the b-tree
BLOCKS                      blocks allocated to the segment
NAME                        name of the index
LF_ROWS                     number of leaf rows
LF_BLKS                     number of leaf blocks in the b-tree
LF_ROWS_LEN                 sum of the lengths of all the leaf rows
LF_BLK_LEN                  useable space in a leaf block
BR_ROWS                     number of branch rows
BR_BLKS                     number of branch blocks in the b-tree
BR_ROWS_LEN                 sum of the lengths of all the branch block rows
BR_BLK_LEN                  useable space in a branch block
DEL_LF_ROWS                 number of deleted leaf rows in the index
DEL_LF_ROWS_LEN             total length of all deleted rows in the index
DISTINCT_KEYS               number of distinct keys in the index
MOST_REPEATED_KEY           how many times the most repeated key is repeated
BTREE_SPACE                 total space currently allocated in the b-tree
USED_SPACE                  total space that is currently being used
PCT_USED                    percent of space allocated that is being used
ROWS_PER_KEY                average number of rows per distinct key
BLKS_GETS_PER_ACCESS        Expected number of consistent mode block
                            gets per row. This assumes that a row
                            chosen at random from the table is being
                            searched for using the index

INDEX_HISTOGRAM Column Name Description

--------------------------- ----------------------------------------------------
REPEAT_COUNT                number of times that a key is repeated
KEYS_WITH_REPEAT_COUNT      number of keys that are repeated REPEAT_COUNT times
================================================================================

Received on Fri Jun 05 1992 - 22:50:14 CEST