Fine-grained Space Management in Oracle
By J.B. Sastry
This article provides a glimpse into one of the concerns in Oracle�s storage management, a primary responsibility of DBAs and certain application developers. I promise not to repeat basic concepts of Oracle�s storage in this article. A more formidable introduction to the ideas presented in here and other primary issues/concepts of storage like storage clause, efficient deployment of pctfree and pctused criteria, is available in voluminous detail through Oracle manuals and several books written by DBA gurus.
This article addresses the question of "How much data can be loaded into a database without having to add any more files to the tablespaces?" It also discusses the diagnostic information that can be derived while answering this question. The article will also address relevance to costs arising out of performance degradation from fragmentation and ill-effects of unscientific file additions to tablespaces. This article includes the code to accomplish the above task. The script in its entirety yields a highly resolved data to facilitate space administration and serves as a diagnostic tool that can be deployed if low performance is suspected due to fragmentation. This approach can be highly valuable in a datawarehousing environment with hundreds of gigabytes of data and where simplistic solutions like export and reimport or CTAS are not conducive operations. The timeless advise of prevention being better than cure is once again the ultimate truth. Scientific planning of tablespaces that is in sync with database growth is more important than ad hoc cures.
Tablespaces are the basic logical containers of Oracle data in the databases. Tablespaces reside in file-spaces on the servers and store segments of logical data such as tables and indexes. In other words, users create tables and indexes in tablespaces that are "opened" for them. As the space available for a "container" is consumed, new data files are added. Ultimately, in a moderately large database you will find that tablespaces are spread across several files and disks . Such a spread might not, after all, have occurred from intentional striping, but from a poor or inadequate planning.
Let us begin our discussion with certain assumptions in a hypothetical scenario, typically deploying sql*loader for loading into a multitude of tables. The goal is to develop a script that would evaluate the situation and forewarn us with information such as "some tablespace can accommodate up to 12,550,000 rows in its present state and if our load is in excess of this volume, please add some data files to this tablespace." It might also throw out a report which says that "clusters of blocks starting at of 25,345,000 bytes in file_id are worthless because they can not be used any more for a new extent and you have not only wasted so much space but also created a fragmented disk space."
Normally, we would assume that in our database we have dedicated tablespaces, i.e., each table has its own. For instance, a table "employee" resides in tablespace "employee_ts" and its associated indexes reside in tablespace "employee_is." This type of database design is not only useful but imperative for databases of any significant size. Enough can never be said about how easier it would be to manage such systems.
By the same token, we have to realize that it is impossible to visualize each index of a table residing in its own tablespace, as the sizes of indexes are much smaller. Fine-graining to such an extent is also not desirable and probably will exceed the MAX_FILES limitation. As the script development progresses in our present venture, we will see how the computation gets slightly complicated when multiple segments in a single tablespace extent are involved.
Let us commence our study with a typical layout of a database segment. In order to determine the number of rows of data that can be loaded into this object- a table or a set of indexes, the following factors need to be considered.
1. The amount of free space within a data block in the existing segment. This arises out of the rows DELETED and that are listed in freelists for Oracle for INSERT operations. We are not considering this particular factor in the present analysis for various reasons. A few reasons include: a typical datawarehousing application does not involve many DELETE transactions, the volume of such a space is not bound to be high and does not warrant attention in a bulk loading scenario, or we might not even consider conventional Oracle loading and prefer directpath loads.
2. The second chunk of space lies in the segment itself, unused.- See the area marked as Unused space in Fig:1. This is the space between the sum of the extents already formed for this object and the utilized space. In this case there is no extension of any segment involved and it is a straight forward computation.
3. The third chunk of space to be analyzed is the space beyond the table segment but below the tablespace- marked as free-space in the figure. This arises when the extent sizes of objects are smaller than that of the tablespace. This is the space in which the table segment will extend next, provided there are enough data blocks available to accommodate its next extent. The free chunks are assumed to be coalesced by Oracle . When there are no contiguous blocks to accommodate a table extent, this space remains unused forever, at least till some repair action is taken by the DBA. The next extent to grow would size to the next_extent parameter of the table. When there are multiple tables in a tablespace or several indexes with different next_extent sizes in a tablespace, the growth would take place for the size of the next object due. Both these situations involving multiple tables and multiple indexes are similar to a degree and in the script we are going to develop we will be addressing the indexes case. The script can be "tweaked" for the other case, if need be.
4. The fourth chunk of space is the space available in the data file (file_space in the figure 1) and ready to be used by the tablespace in its future extensions. Same conditions that are mentioned earlier apply to this space too. We need to keep in mind that the extent size in this case is the next_extent size of the tablespace and not of the table.
The principle we assume is that as dataload progresses, Oracle will insert rows into the free unused space in the segment. This is followed by growing the extents of the table until the tablespace can accommodate and then extend the tablespace until the OS file can provide, after which the dataload will fail. We need to find the number of rows that can be loaded into various tablesbeforee such a failure so that we can come back to a pleasant greeting on Monday morning!
5. For accurate computations we will be reading the following values from data dictionary:
- Number of columns in the table or/and index
- Average row length of the table/index
- Names of each index
- Next_extent sizes of table, index, and respective tablespaces
- Oracle block size is a constant and presumed to be known
Let us briefly consider the relative complexity in computing the index segments. Assume that three index segments(indexes) exist in a tablespace created with equal Initial_Extent sizes. Due to the differences in the widths and number of columns in their sizes, the used spaces in their segments will differ. Accordingly, the number of rows they can and have stored for a given chunk of blocks will also differ. If their next_extent sizes are different, they grow almost at random. Their growth pattern will resemble Fig.2. The first segment to grow will be the index that can accommodate least number of rows in its unused space(see note 2 above). The next growth would occur in a segment with next least rows, and so on . Therefore, we will have to capture this sequence in an algorithm. This pattern will remain even in a new extent of the tablespace. This point will be referred to later in this article.
The code is developed for Oracle 7.3 and differs with earlier versions in a few ways, including:
- If you are using bitmaps, the column width and subsequent space calculations will be over stated as the compression technology attains a compaction level of almost 25 percent of the original space. Also the actual size of the bitmap depends upon the data distribution like selectivity of the index, number of nulls, etc.
- For computing rows possible from unused space within a table/index segment we use the new built-in package DBMS_SPACE. This information is available in the earlier versions in different data-dictionary views. we need to extract information from DBA_SEGMENTS, DBA_TABLES AND DBA_INDEXES and manipulate it to derive the unused values.
As stated earlier, let us assume that our tablespaces are well organized and named with _IS extents for the tablespaces housing the indexes and _TS for tables. Further, only one table exists for each _TS. The treatment of a tablespace where multiple tables are grouped in a table is somewhat similar to the treatment for _IS and is left to the reader as an exercise.
The computational parts of the code will be in a package, which we will refer to as pkg_compute_rows. Its main procedure would be proc_compute_rows with input parameter of the tablespace name. Each index or table tablespace name needs to be passed to this package in a loop. The technique to develop these names is largely related to the application. In a situation involving partitions of tables, the partitions are generally named after the lastname alphabet or the month or quarter of operation which serves as a check constraint for creating the partitions and views. If such is the case, the list of tablespaces can be generated out of a PL/SQL routine. Otherwise we need to SELECT DISTINCT TABLESPACE_NAMEs form user_tablespaces view. We can then use a FOR or WHILE loop to pass each tablespace name and call the procedure with the following command:
pkg_compute_rows.proc_compute_rows(v_tsname);
The specification of the package will be simple and involve just the main procedure, the code for which follows below:
CREATE OR REPLACE PACKAGE pkg_compute_rows AS PROCEDURE proc_compute_rows(v_tsname IN VARCHAR2); END pkg_compute_rows;
Before we start defining the body of the package, let us dwell on the package variables that we need:
- We will need variables to store the values from the database, such as the number of columns, average row length, row header values, Oracle block size, next_extent size, block_id, file_id, etc.
- Since the DBMS_SPACE.UNUSED_SPACE packaged procedure will be employed, we will need seven variables to hold return values of total/unused/last-used blocks, bytes, block_id.
- For computation of indexes, PL/SQL tables may be employed to store these values with record_type variables.
A complete list of variables would therefore contain-
CREATE OR REPLACE PACKAGE BODY pkg_compute_rows AS --Declare some variables blk_size INTEGER :=16384 or whatever; --Constant, DB specific v_row_len INTEGER :=0; v_avg_row_len INTEGER :=0; v_num_cols INTEGER :=0; --(either in a table or in an index) tbl_row_hdr INTEGER :=8; --(Constant- for row_id information) ind_row_hdr INTEGER :=3; --(Constant- for index header info.) v_table_name VARCHAR2(30):=' '; v_index_name VARCHAR2(40):=' '; v_next_ext NUMBER :=0; v_next_blks NUMBER :=0; v_ext_possible INTEGER :=0; --(number of extents possible) v_rows_possible NUMBER :=0; v_blks_per_ext NUMBER :=0; v_rows_per_block INTEGER :=0; v_length NUMBER :=0; v_least_rows NUMBER :=0; v_index_with_least_rows VARCHAR2(40):=' '; v_index_id_with_least_rows BINARY_INTEGER:=0; --Variables for receiving values from procedure DBMS_SPACE.UNUSED_SPACE ds_tblocks NUMBER :=0; -- (Total blocks in the segment) ds_tbytes NUMBER :=0; -- (Total bytes in the segment) ds_ublocks NUMBER :=0; -- (Unused blocks in the segment) ds_ubytes NUMBER :=0; -- (Unused bytes in the segment) ds_lufid NUMBER :=0; -- Last used file_id) ds_lubid NUMBER :=0; -- (Last used block_id) ds_lublock NUMBER :=0; -- (Last used block)Variables for PL/SQL tables used in index segment computations
TYPE ind_rec is RECORD (
ind_name VARCHAR2(30),
avglen INTEGER,
ublocks NUMBER,
next_extent NUMBER,
rows_possible NUMBER,
num_cols INTEGER );
TYPE ind_tab_type IS TABLE OF ind_rec INDEX BY BINARY_INTEGER;
ind_tab ind_tab_type;
idx BINARY_INTEGER :=0;
The main procedure in the package does little. It checks the v_tsname parameter to see if this is an index space or a table tablespace and calls the relevant program.
PROCEDURE proc_compute_rows( v_tsname VARCHAR2) IS
v_tsname_ind VARCHAR2(2);
BEGIN
--Determine if the tablespace is for an index or a table segment
v_tsname_ind := SUBSTR(v_tsname,(LENGTH(v_tsname)-1));
ind_tab.DELETE; --Initialize
IF v_tsname_ind = 'TS' THEN
pkg_compute_rows.proc_compute_table(v_tsname);
ELSE
pkg_compute_rows.proc_compute_index(v_tsname);
END IF;
ind_tab.DELETE;--To ensure that we do not waste disk /memory space by leaving
some values behind in these global PL/SQL tables.
END proc_compute_rows;
Now to the procedure pkg_compute_rows.proc_compute_table(v_tsname). This is an unspecified procedure internal to the package and has three action segments. The first one is for computing the rows that can be loaded from unused space within the table segment already formed.
PROCEDURE proc_compute_table( v_tsname VARCHAR2) IS
This procedure is called when the main procedure evaluates that the segment passed belongs to a table. Begin by initializing some variables:
BEGIN v_table_name := SUBSTR(v_tsname, 1, LENGTH(v_tsname)-3); SELECT avg_row_len INTO v_avg_row_len FROM tabs WHERE table_name = v_table_name; SELECT next_extent INTO v_next_ext FROM tabs WHERE table_name= v_table_name; v_next_blks := TRUNC(v_next_ext/blk_size);--Number of blocks required for next_extentWe need this information for computing the required row lengths. See the Oracle documentation on space calculations for details.
SELECT MAX(column_id) INTO v_num_cols FROM cols WHERE table_name = v_table_name;
There is a small approximation in the line below. The num_cols are just added where as Oracle advises differently for some data types.
v_rows_per_block := TRUNC(blk_size/(tbl_row_hdr + v_avg_row_len + v_num_cols));
Compute from DBMS_SPACE first for the rows that can be inserted without any extension to the table or the tablespace.
DBMS_SPACE.UNUSED_SPACE('APPOWNER', �table_name�, 'TABLE', ds_tblocks,
ds_tbytes, ds_ublocks, ds_ubytes, ds_lufid, ds_lubid, ds_lublock);
v_rows_possible := TRUNC( ds_ublocks * v_rows_per_block );At this point we have the information that can perhaps be stored in a result- summary table for querying later.
INSERT INTO summary_compute_rows(tablespace_name, bytes, blocks, rows_possible, check_date) VALUES(v_tsname, ds_ubytes,ds_ublocks,v_rows_possible,SYSDATE); COMMIT; v_rows_possible :=0;
Now check if the tablespace can accommodate any more table extents. We are assuming that Oracle has coalesced all contiguous chunks of space. We are computing this information using user_free_space view.
FOR rec IN ( SELECT DISTINCT file_id, bytes, blocks, block_id start_blk,
(block_id+blocks) end_blk FROM user_free_space
WHERE tablespace_name = v_tsname ) LOOP
IF rec.blocks >= v_next_blks THEN
v_ext_possible := TRUNC(rec.blocks/v_next_blks); --Extents possible
v_rows_possible := TRUNC(v_rows_per_block * v_next_blks * v_ext_possible);
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, rec.bytes, rec.blocks,v_next_ext,
v_rows_possible,SYSDATE,rec.start_blk, rec.end_blk);
COMMIT;
v_rows_possible :=0;
ELSE
When this condition is hit, we know that we have a chunk of space that does not have enough blocks to extend a table segment. Though this chunk of space is useless for loading, we need this information to estimate and diagnose fragmentation. More of these empty and useless spaces would warrant corrective action.
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, rec.bytes, rec.blocks,v_next_ext,
0,SYSDATE,rec.start_blk, rec.end_blk);
COMMIT;
END IF;
END LOOP;
The next chunk of space of interest is the space between the OS file for the tablespace and the already formed tablespace extents. We now check to see if the files can accommodate any more extents of the tablespace. Note that v_ext_possible and v_next_ext refer to the tablespace and not table. We are checking the difference between total blocks and used blocks in each file to determine if a tablespace can grow in that free area.
SELECT next_extent INTO v_next_ext FROM user_tablespaces WHERE tablespace_name= v_tsname; v_next_blks := TRUNC(v_next_ext/blk_size);
These are the blocks required for the next tablespace extent. There are bound to be a number of non-contiguous chunks of blocks in a file system that has been put to use for a while and we need to examine of these spaces. The way wee accomplish this is to check the file_id of each space and then query the file$ table for total blocks and uet$ for used blocks.
FOR rec IN (SELECT DISTINCT file_id FROM sys.dba_extents WHERE
tablespace_name=v_tsname) LOOP
SELECT f.blocks into ds_tblocks FROM sys.file$ --Total blocks from file
WHERE file#= rec.file_id ;
----------------------------------
SELECT SUM(u.length) INTO ds_ublocks FROM sys.uet$ --Used blocks from extents
WHERE file# = rec.file_id;
----------------------------------
IF (ds_tblocks- ds_ublocks) >= v_next_blks THEN
v_ext_possible := TRUNC((ds_tblocks- ds_ublocks)/v_next_blks);
v_rows_possible := TRUNC(v_rows_per_block * v_next_blks * v_ext_possible);
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, (ds_tblocks- ds_ublocks)*blk_size,
(ds_tblocks- ds_ublocks), v_next_ext,v_rows_possible,
SYSDATE,0,0);
COMMIT;
v_rows_possible :=0;
ELSE
Though this chunk of space is useless for loading, we need this information to estimate and diagnose fragmentation. More of these empty and useless spaces would warrant corrective action.
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, (ds_tblocks- ds_ublocks)*blk_size,
(ds_tblocks- ds_ublocks), v_next_ext,0,SYSDATE,0,0);
COMMIT;
END IF;
END LOOP;
END proc_compute_table;
We shall now move on to the computation for index spaces. The three steps are the same but with a small twist because of the existence of multiple indexes in each tablespace. We therefore initially �load� the details of all indexes in a tablespace in to PL/SQL table ind_tab, defined among the variables.
This procedure is called when the main program detects that the segment passed to it belongs to indexes
PROCEDURE proc_compute_index(v_tsname VARCHAR2) IS v_blocks NUMBER;
Please note that "hwm" is a variable serving as a watermark for the rows that are already entered into summary_compute_rows table. This will eliminate duplication of rows already considered. Its use will become evident later.
hwm NUMBER;
--Initialize some variables defined in PL/SQL table
BEGIN v_table_name := SUBSTR(v_tsname, 1, LENGTH(v_tsname)-3);
We need to find all the indexes for this table and load them into PL/SQL tables as multiple indexes share the same tablespace
idx :=0; FOR rec IN (SELECT index_name, next_extent FROM ind WHERE table_name = v_table_name) LOOP idx := idx + 1; ind_tab(idx).ind_name := rec.index_name; ind_tab(idx).next_extent := rec.next_extent; END LOOP;
Pick up the number of columns in each index and their length.
FOR I IN 1..idx LOOP v_index_name := ind_tab(I).ind_name; SELECT COUNT(*), SUM(column_length) INTO v_num_cols, v_length FROM user_ind_columns WHERE index_name= v_index_name; ind_tab(I).num_cols := v_num_cols; ind_tab(I).avglen := v_length;
Extract from DBMS_SPACE first for the rows that can be inserted without any extension to the table or the tablespace.
DBMS_SPACE.UNUSED_SPACE('APPOWNER', �table_name�, 'TABLE',ds_tblocks, ds_tbytes, ds_ublocks,ds_ubytes, ds_lufid,ds_lubid,ds_lublock);
ind_tab(I).ublocks := ds_ublocks;
Compute the rows_possible without any extension to the segments.
v_rows_per_block := TRUNC(blk_size/(ind_row_hdr + v_length + v_num_cols)); v_rows_possible := TRUNC( ds_ublocks * v_rows_per_block); ind_tab(I).rows_possible := v_rows_possible; v_rows_possible :=0; END LOOP ;
It is likely that the indexes have differing load capacities. To post the results of above check, we therefore consider the lowest possible rows among all indexes and post it for entire IS tablespace. This is accomplished by calling the procedure to find the index with the least rows. The code for this procedure is given at the end. This procedure can be included in the package as a stand-alone module to which other programs can refer to as needed.
pkg_compute_rows.proc_find_least_rows;
INSERT INTO summary_compute_rows (tablespace_name, rows_possible, check_date) VALUES (v_tsname, v_least_rows,SYSDATE); hwm := v_least_rows; --Assign the entry value(rows already considered)
Next we need to evaluate the possibility of extensions to various index segments to the extent the tablespace can accommodate. The extension would start with the index with least capable rows and then on the next one. The way this is accomplished is by checking for the index with least rows and extending its rows and reducing the size of tablespace then recheck the lowest index, and so on. the program for identifying the least row index is called repetitiously.
FOR rec IN ( SELECT DISTINCT file_id, bytes, blocks, block_id start_blk,
(block_id+blocks) end_blk FROM user_free_space
WHERE tablespace_name = v_tsname ) LOOP
v_blocks := rec.blocks;
v_next_blks := TRUNC(ind_tab(v_index_id_with_lst_rows).next_extent/blk_size);
We need a second loop to extend the index segments individually, based on their status in the v_least_rows extraction. The loop exits when a particular segment is completely allocated by the diminishing v_blocks check in the WHILE loop.
WHILE v_blocks >= v_next_blks LOOP
v_blocks := v_blocks - v_next_blks ;
v_row_len := (ind_row_hdr + ind_tab(v_index_id_with_lst_rows).avglen +
ind_tab(v_index_id_with_lst_rows).num_cols);
v_rows_per_block := TRUNC(blk_size/v_row_len);
ind_tab(v_index_id_with_lst_rows).rows_possible :=
ind_tab(v_index_id_with_lst_rows).rows_possible + (v_next_blks * v_rows_per_block );
---------------------------------
pkg_compute_rows.proc_find_least_rows;
---------------------------------
v_next_blks := TRUNC(ind_tab(v_index_id_with_lst_rows).next_extent/blk_size);
END LOOP;
At this stage all the relevant records in PL/SQL table have been updated. We are ready to move on to the next file_id. Now we should record the new information, i.e., the latest rows possible(once again the least record) into the dataload table.
pkg_compute_rows.proc_find_least_rows;
---------------------------------
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, rec.bytes, rec.blocks,0,
(v_least_rows-hwm),SYSDATE,rec.start_blk, rec.end_blk);
---------------------------------
hwm := v_least_rows;
---------------------------------
COMMIT;
END LOOP;
The final step is the possibility of extension to the tablspace itself within the file system. We compute the difference between the file blocks (total) and used blocks for the tablespace as the blocks available. The rows possible is estimated by summing up the over all row lengths, num_cols for all indexes and estimating the rows possible. This is because the extension might take place on any index in the given tablespace. For a more accurate calculation, we might incorporate the same technique used in the above program block.
SELECT next_extent INTO v_next_ext FROM user_tablespaces WHERE tablespace_name= v_tsname;
v_next_blks := TRUNC(v_next_ext/blk_size);
FOR rec IN (SELECT DISTINCT file_id FROM sys.dba_extents WHERE
tablespace_name=v_tsname) LOOP
SELECT f.blocks into ds_tblocks FROM sys.file$ f
WHERE f.file#= rec.file_id ;
SELECT SUM(u.length) INTO ds_ublocks FROM sys.uet$ u
WHERE u.file# = rec.file_id;
IF (ds_tblocks- ds_ublocks) >= v_next_blks THEN
v_ext_possible := TRUNC((ds_tblocks- ds_ublocks)/v_next_blks);
Compute the overall row length for a more generic estimate of rows because at this stage we are only looking at an entire tablespace extent growth.
v_row_len := 0;
FOR I IN 1..idx LOOP
v_row_len:= v_row_len + (ind_row_hdr + ind_tab(I).avglen + ind_tab(I).num_cols);
END LOOP;
v_rows_per_block := TRUNC(blk_size/v_row_len);
v_rows_possible := TRUNC(v_rows_per_block * v_next_blks * v_ext_possible);
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, (ds_tblocks- ds_ublocks)*blk_size,
(ds_tblocks- ds_ublocks), v_next_ext,v_rows_possible,
SYSDATE,0,0);
COMMIT;
v_rows_possible :=0;
END IF;
END LOOP;
END proc_compute_index;
PROCEDURE proc_find_least_rows IS
This procedure is called a number of times by index computing procedure Methodology is to start with the first number and then substitute it if with subsequent smaller numbers. the values in the PL/SQL table at the point of calling this procedure are used for this extraction.
BEGIN
--start with the first row
v_least_rows := ind_tab(1).rows_possible;
v_index_with_lst_rows := ind_tab(1).ind_name;
v_index_id_with_lst_rows := 1;
FOR I IN 1..idx LOOP
IF ind_tab(I).rows_possible < v_least_rows THEN
v_least_rows := ind_tab(I).rows_possible;
v_index_with_lst_rows := ind_tab(I).ind_name;
v_index_id_with_lst_rows := I;
END IF;
END LOOP;
END;
The summary_compute_rows table now has all kinds of useful information. It not only projects the number of rows any dataload can sustain but also can map the chunks of empty spaces that are forming in the database. One can write simple SQL*PLUS scripts to report formatted information from this table. A simple sample report is shown below.
| Tablespace | File | Bytes | Blocks | Extent_Size | Rows_Possible |
| SAMPLE_TABLE_TS | 278,528 | 17 | 1,700 | ||
| 18 | 15,859,712968188,743,6800 | ||||
| 19 | 9,338,880570188,743,6800|||||
| 20 | 16,3841188,743,6800|||||
| 21 | 8,192,000500188,743,6800|||||
| sum | 33,685,504 | 2,056 | 1,700 |
From the above report we can see that the sample_table can load only up to 1700 rows from the unused space in its already formed segments(file_id is NULL). The other rows pertain to the different file segments of the tablespace. In all these cases there are free blocks available but none of them is useful in letting an extent grow. Without a detailed analysis, one might have wrongfully concluded that 240,000 rows could have been added to the table over a combined free space of 33.6MB for a row-size of 140 bytes. In reality the dataload would have failed. Taking a look at the number of files that carry useless empty-spaces, we know that they typically result in fragmentation, if more files get added without corrective action. Fragmentation of tablespaces is bad for two reasons- it creates performance degradation as disk reads of non-contiguous blocks is inefficient and also the trend can only continue if corrective actions are not made.
In summary, we can conclude that space management can become an intricate task as one tends to develop fine-grained analysis and solutions to data loading and fragmentation problems. A simple addition of more file spaces is never a solution, at least not an intelligent one. One must decide what needs to be done based on facts. The above developed script is one such value-added tool that can quickly show the database�s health and can provide intelligible information.
(Author�s Note: I have not considered or come across any Third Party tool/utility which would have answered the requirement mentioned in this paper directly. It is very possible that some innovative entrepreneur has already developed and marketed a suitable tool, in which case I accept my ignorance -JB.)
Acknowledgments:
Thanks to Bob Desatnik of BCBSO.
About The Author:
With more than 8 years of Business and Information Systems design and management back-ground, JB Sastry works for Megasys as a consultant and am on assignment at BCBSO, Cleveland. Readers can reach me at [email protected].
My Address:
J. B. Sastry
7605 Lucerne Drive, Apt- A16
Middleburg Hts., OH- 44130
Phones:
Day: +1.216.292.0400 x 3455
Evening:+1.216.816.1642
Full listing of the code:
---------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE pkg_compute_rows AS
PROCEDURE proc_compute_rows(v_tsname IN VARCHAR2);
END pkg_compute_rows;
---------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY pkg_compute_rows AS
--Declare some variables
blk_size INTEGER :=16384 or whatever; --Constant, DB specific
v_row_len INTEGER :=0;
v_avg_row_len INTEGER :=0;
v_num_cols INTEGER :=0; --either in a table or in an index
tbl_row_hdr INTEGER :=8; --Constant- for row_id information
ind_row_hdr INTEGER :=3; --Constant- for index header info
v_table_name VARCHAR2(30):=' ';
v_index_name VARCHAR2(40):=' ';
v_next_ext NUMBER :=0;
v_next_blks NUMBER :=0;
v_ext_possible INTEGER :=0; --number of extents possible
v_rows_possible NUMBER :=0;
v_blks_per_ext NUMBER :=0;
v_rows_per_block INTEGER :=0;
v_length NUMBER :=0;
v_least_rows NUMBER :=0;
v_index_with_least_rows VARCHAR2(40):=' ';
v_index_id_with_least_rows BINARY_INTEGER:=0;
--Variables for receiving values from procedure DBMS_SPACE.UNUSED_SPACE
ds_tblocks NUMBER :=0; --Total blocks in the segment
ds_tbytes NUMBER :=0; --Total bytes in the segment
ds_ublocks NUMBER :=0; --Unused blocks in the segment
ds_ubytes NUMBER :=0; --Unused bytes in the segment
ds_lufid NUMBER :=0; --Last used file_id
ds_lubid NUMBER :=0; --Last used block_id
ds_lublock NUMBER :=0; --Last used block
--Variables for PL/SQL tables used in index segment computations
TYPE ind_rec is RECORD (
ind_name VARCHAR2(30),
avglen INTEGER,
ublocks NUMBER,
next_extent NUMBER,
rows_possible NUMBER,
num_cols INTEGER );
TYPE ind_tab_type IS TABLE OF ind_rec INDEX BY BINARY_INTEGER;
ind_tab ind_tab_type;
idx BINARY_INTEGER :=0;
PROCEDURE proc_compute_rows ( v_tsname VARCHAR2) IS
v_tsname_ind VARCHAR2(2);
BEGIN
--Determine if the tablespace is for an index or a table segment
v_tsname_ind := SUBSTR(v_tsname,(LENGTH(v_tsname)-1));
ind_tab.DELETE; --Initialize
IF v_tsname_ind = 'TS' THEN
pkg_compute_rows.proc_compute_table(v_tsname);
ELSE
pkg_compute_rows.proc_compute_index(v_tsname);
END IF;
ind_tab.DELETE;
END proc_compute_rows;
---------------------------------------------------------------------------------------------------------
PROCEDURE proc_compute_table( v_tsname VARCHAR2) IS
---------------------------------------------------------------------------------
BEGIN
--Initialize some variables
v_table_name := SUBSTR(v_tsname, 1, LENGTH(v_tsname)-3);
--------------------------------
SELECT avg_row_len INTO v_avg_row_len FROM tabs WHERE table_name = v_table_name;
--------------------------------
SELECT next_extent INTO v_next_ext FROM tabs WHERE table_name= v_table_name;
--------------------------------
v_next_blks := TRUNC(v_next_ext/blk_size);--Number of blocks required for next_extent
--------------------------------
SELECT MAX(column_id) INTO v_num_cols FROM cols WHERE table_name = v_table_name;
--------------------------------
v_rows_per_block := TRUNC(blk_size/(tbl_row_hdr + v_avg_row_len + v_num_cols));
---------------------------------------------------------------------------------
DBMS_SPACE.UNUSED_SPACE('APPOWNER', �table_name�, 'TABLE', ds_tblocks,
ds_tbytes, ds_ublocks, ds_ubytes, ds_lufid, ds_lubid, ds_lublock);
--------------------------------
v_rows_possible := TRUNC( ds_ublocks * v_rows_per_block );
INSERT INTO summary_compute_rows(tablespace_name, bytes, blocks, rows_possible, check_date)
VALUES(v_tsname, ds_ubytes,ds_ublocks,v_rows_possible,SYSDATE);
COMMIT;
v_rows_possible :=0;
----------------------------------
FOR rec IN ( SELECT DISTINCT file_id, bytes, blocks, block_id start_blk,
(block_id+blocks) end_blk FROM user_free_space
WHERE tablespace_name = v_tsname ) LOOP
IF rec.blocks >= v_next_blks THEN
v_ext_possible := TRUNC(rec.blocks/v_next_blks); --Extents possible
v_rows_possible := TRUNC(v_rows_per_block * v_next_blks * v_ext_possible);
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, rec.bytes, rec.blocks,v_next_ext,
v_rows_possible,SYSDATE,rec.start_blk, rec.end_blk);
COMMIT;
v_rows_possible :=0;
ELSE
----------------------------------
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, rec.bytes, rec.blocks,v_next_ext,
0,SYSDATE,rec.start_blk, rec.end_blk);
COMMIT;
END IF;
END LOOP;
---------------------------------------------------------------------------------
SELECT next_extent INTO v_next_ext FROM user_tablespaces WHERE tablespace_name= v_tsname;
--------------------------------
v_next_blks := TRUNC(v_next_ext/blk_size);
--------------------------------
FOR rec IN (SELECT DISTINCT file_id FROM sys.dba_extents WHERE
tablespace_name=v_tsname) LOOP
SELECT f.blocks into ds_tblocks FROM sys.file$ --Total blocks from file
WHERE file#= rec.file_id ;
----------------------------------
SELECT SUM(u.length) INTO ds_ublocks FROM sys.uet$ --Used blocks from extents
WHERE file# = rec.file_id;
----------------------------------
IF (ds_tblocks- ds_ublocks) >= v_next_blks THEN
v_ext_possible := TRUNC((ds_tblocks- ds_ublocks)/v_next_blks);
v_rows_possible := TRUNC(v_rows_per_block * v_next_blks * v_ext_possible);
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, (ds_tblocks- ds_ublocks)*blk_size,
(ds_tblocks- ds_ublocks), v_next_ext,v_rows_possible,
SYSDATE,0,0);
COMMIT;
v_rows_possible :=0;
ELSE
----------------------------------
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, (ds_tblocks- ds_ublocks)*blk_size,
(ds_tblocks- ds_ublocks), v_next_ext,0,SYSDATE,0,0);
COMMIT;
END IF;
END LOOP;
END proc_compute_table;
---------------------------------------------------------------------------------------------------------
PROCEDURE proc_find_least_rows IS
----------------------------------
BEGIN
--start with the first row
v_least_rows := ind_tab(1).rows_possible;
v_index_with_lst_rows := ind_tab(1).ind_name;
v_index_id_with_lst_rows := 1;
FOR I IN 1..idx LOOP
IF ind_tab(I).rows_possible < v_least_rows THEN
v_least_rows := ind_tab(I).rows_possible;
v_index_with_lst_rows := ind_tab(I).ind_name;
v_index_id_with_lst_rows := I;
END IF;
END LOOP;
END proc_find_least_rows;
----------------------------------------------------------------------------------------------------
PROCEDURE proc_compute_index(v_tsname VARCHAR2) IS
v_blocks NUMBER;
hwm NUMBER;
---------------------------------
BEGIN
v_table_name := SUBSTR(v_tsname, 1, LENGTH(v_tsname)-3);
---------------------------------
idx :=0;
FOR rec IN (SELECT index_name, next_extent FROM ind WHERE table_name = v_table_name) LOOP
idx := idx + 1;
ind_tab(idx).ind_name := rec.index_name;
ind_tab(idx).next_extent := rec.next_extent;
END LOOP;
---------------------------------
FOR I IN 1..idx LOOP
v_index_name := ind_tab(I).ind_name;
SELECT COUNT(*), SUM(column_length) INTO v_num_cols, v_length FROM user_ind_columns WHERE
index_name= v_index_name;
ind_tab(I).num_cols := v_num_cols;
ind_tab(I).avglen := v_length;
---------------------------------
DBMS_SPACE.UNUSED_SPACE('APPOWNER', �table_name�, 'TABLE',ds_tblocks, ds_tbytes, ds_ublocks,ds_ubytes, ds_lufid,ds_lubid,ds_lublock);
ind_tab(I).ublocks := ds_ublocks;
---------------------------------
v_rows_per_block := TRUNC(blk_size/(ind_row_hdr + v_length + v_num_cols));
v_rows_possible := TRUNC( ds_ublocks * v_rows_per_block);
ind_tab(I).rows_possible := v_rows_possible;
v_rows_possible :=0;
END LOOP ;
---------------------------------
pkg_compute_rows.proc_find_least_rows;
---------------------------------
INSERT INTO summary_compute_rows (tablespace_name, rows_possible,
check_date) VALUES (v_tsname, v_least_rows,SYSDATE);
hwm := v_least_rows; --Assign the entry value(rows already considered)
---------------------------------------------------------------------------------
FOR rec IN ( SELECT DISTINCT file_id, bytes, blocks, block_id start_blk,
(block_id+blocks) end_blk FROM user_free_space
WHERE tablespace_name = v_tsname ) LOOP
v_blocks := rec.blocks;
v_next_blks := TRUNC(ind_tab(v_index_id_with_lst_rows).next_extent/blk_size);
---------------------------------
WHILE v_blocks >= v_next_blks LOOP
v_blocks := v_blocks - v_next_blks ;
v_row_len := (ind_row_hdr + ind_tab(v_index_id_with_lst_rows).avglen +
ind_tab(v_index_id_with_lst_rows).num_cols);
v_rows_per_block := TRUNC(blk_size/v_row_len);
ind_tab(v_index_id_with_lst_rows).rows_possible :=
ind_tab(v_index_id_with_lst_rows).rows_possible + (v_next_blks * v_rows_per_block );
---------------------------------
pkg_compute_rows.proc_find_least_rows;
---------------------------------
v_next_blks := TRUNC(ind_tab(v_index_id_with_lst_rows).next_extent/blk_size);
END LOOP;
---------------------------------
pkg_compute_rows.proc_find_least_rows;
---------------------------------
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, rec.bytes, rec.blocks,0,
(v_least_rows-hwm),SYSDATE,rec.start_blk, rec.end_blk);
---------------------------------
hwm := v_least_rows;
---------------------------------
COMMIT;
END LOOP;
---------------------------------------------------------------------------------
SELECT next_extent INTO v_next_ext FROM user_tablespaces WHERE tablespace_name= v_tsname;
v_next_blks := TRUNC(v_next_ext/blk_size);
---------------------------------------------------------------------------------
FOR rec IN (SELECT DISTINCT file_id FROM sys.dba_extents WHERE
tablespace_name=v_tsname) LOOP
---------------------------------
SELECT f.blocks into ds_tblocks FROM sys.file$ f
WHERE f.file#= rec.file_id ;
SELECT SUM(u.length) INTO ds_ublocks FROM sys.uet$ u
WHERE u.file# = rec.file_id;
---------------------------------
IF (ds_tblocks- ds_ublocks) >= v_next_blks THEN
v_ext_possible := TRUNC((ds_tblocks- ds_ublocks)/v_next_blks);
v_row_len := 0;
FOR I IN 1..idx LOOP
v_row_len:= v_row_len + (ind_row_hdr + ind_tab(I).avglen + ind_tab(I).num_cols);
END LOOP;
---------------------------------
v_rows_per_block := TRUNC(blk_size/v_row_len);
v_rows_possible := TRUNC(v_rows_per_block * v_next_blks * v_ext_possible);
---------------------------------
INSERT INTO summary_compute_rows
VALUES(v_tsname, rec.file_id, (ds_tblocks- ds_ublocks)*blk_size,
(ds_tblocks- ds_ublocks), v_next_ext,v_rows_possible,
SYSDATE,0,0);
COMMIT;
v_rows_possible :=0;
END IF;
END LOOP;
---------------------------------------------------------------------------------
END proc_compute_index;
END pkg_compute_rows;
---------------------------------------------------------------------------------