Through built-in packages, Oracle is now exposing more information about database internals -- information that is not directly visible in the catalog. This chapter describes two packages that expose useful information.
The DBMS_SPACE package provides procedures for obtaining space utilization information about table, index, and cluster segments. This information is not directly available through the Oracle data dictionary views. It can be used to report on and track segment space consumption in an Oracle database more accurately than by monitoring extent allocation alone. By measuring segment growth rates over time, DBAs can better predict the need for additional space in the database.
The DBMS_SPACE package is created when the Oracle database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1, Introduction ) contains the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_SPACE for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.
Table Table 12.1 lists the programs in the DBMS_SPACE package.
This section describes the programs defined in DBMS_SPACE.
Here is the Oracle 7.x specification:
PROCEDURE DBMS_SPACE.FREE_BLOCKS (segment_owner IN VARCHAR2 ,segment_name IN VARCHAR2 ,segment_type IN VARCHAR2 ,freelist_group_id IN NUMBER ,free_blks OUT NUMBER ,scan_limit IN NUMBER DEFAULT NULL);
Here is the Oracle 8.0 specification:
PROCEDURE DBMS_SPACE.FREE_BLOCKS (segment_owner IN VARCHAR2 ,segment_name IN VARCHAR2 ,segment_type IN VARCHAR2 ,freelist_group_id IN NUMBER ,free_blks OUT NUMBER ,scan_limit IN NUMBER DEFAULT NULL ,partition_name IN VARCHAR2 DEFAULT NULL);
Parameters are summarized in the following table.
The FREE_BLOCKS procedure does not raise any package exceptions. FREE_BLOCKS will raise the following Oracle exception if invalid segment data is passed in or if the executing user does not have privileges to use the procedure on the segment:
Note the following restrictions on using FREE_BLOCKS:
The following code block is a simple SQL*Plus report on the size of freelist number 0 for all tables in the current session schema:
/* Filename on companion disk: s pcex1.sql */* DECLARE free_blocks NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(RPAD('TABLE NAME',30)||' FREELIST BLOCKS'); FOR user_tables_rec IN (SELECT table_name FROM user_tables) LOOP DBMS_SPACE.FREE_BLOCKS (segment_owner => USER ,segment_name => user_tables_rec.table_name ,segment_type => 'TABLE' ,freelist_group_id => 0 ,free_blks => free_blocks ,scan_limit => NULL); DBMS_OUTPUT.PUT_LINE(RPAD(user_tables_rec.table_name,30)||' '|| TO_CHAR(free_blocks)); END LOOP; END; /
This is a sample of the report output:
TABLE NAME FREELIST BLOCKS Q$BGP_CONFIG 1 Q$BGP_DEBUG 1 Q$DICACHE_DETL 2 Q$INSTAT_DETL 1 Q$INSTAT_LOG 159 Q$IOWAITS_DETL 1 Q$LATCHSTAT_DETL 2 Q$LIBCACHE_DETL 1 Q$MTSDISP_DETL 1 Q$MTSSERV_DETL 1 Q$PLAN_TABLE 1 Q$SEG 3 Q$SEGFAIL_DETL 1 Q$SEGWATCH 1 Q$SESSIONWAIT_DETL 1
Blocks are added to free lists when the percentage of free space in the block is less that the PCTUSE setting for the segment. These blocks are below the segment highwater mark, and thus are not included in the unused blocks reported by DBMS_SPACE.UNUSED_SPACE.
Freelist groups are used to reduce contention in Oracle Parallel Server installations by helping to partition data among the instances. Most DBAs will thus have less use for the FREE_BLOCKS procedure than for the UNUSED_SPACE procedure.
The UNUSED_SPACE procedure returns information about the unused space and the position of the highwater mark in a table, index, or cluster segment. Specifications for Oracle7 and Oracle8 differ as follows.
Here is the Oracle 7.x specification:
PROCEDURE DBMS_SPACE.UNUSED_SPACE (segment_owner IN VARCHAR2 ,segment_name IN VARCHAR2 ,segment_type IN VARCHAR2 ,total_blocks OUT NUMBER ,total_bytes OUT NUMBER ,unused_blocks OUT NUMBER ,unused_bytes OUT NUMBER ,last_used_extent_file_id OUT NUMBER ,last_used_extent_block_id OUT NUMBER ,last_used_block OUT NUMBER);
Here is the Oracle 8.0 specification:
PROCEDURE DBMS_SPACE.UNUSED_SPACE (segment_owner IN VARCHAR2 ,segment_name IN VARCHAR2 ,segment_type IN VARCHAR2 ,total_blocks OUT NUMBER ,total_bytes OUT NUMBER ,unused_blocks OUT NUMBER ,unused_bytes OUT NUMBER ,last_used_extent_file_id OUT NUMBER ,last_used_extent_block_id OUT NUMBER ,last_used_block OUT NUMBER ,partition_name IN VARCHAR2 DEFAULT NULL);
Parameters are summarized in the following table.
The UNUSED_SPACE procedure does not raise any package exceptions. UNUSED_SPACE will raise the following Oracle exception if invalid segment data is passed in or if the executing user does not have privileges to use the procedure on the segment:
Note the following restrictions on calling the UNUSED_SPACE procedure:
The following is a simple SQL*Plus report on space utilization by tables in the current session schema. It displays total space allocated, total unused space, and the percentage of allocated space that is unused.
//* Filename on companion disk: spcex1.sql */* DECLARE total_blocks NUMBER; total_bytes NUMBER; unused_blocks NUMBER; unused_bytes NUMBER; last_extent_file NUMBER; last_extent_block NUMBER; last_block NUMBER; grand_total_blocks NUMBER := 0; grand_total_unused NUMBER := 0; BEGIN FOR user_tables_rec IN (SELECT table_name FROM user_tables) LOOP DBMS_SPACE.UNUSED_SPACE (segment_owner => USER ,segment_name => user_tables_rec.table_name ,segment_type => 'TABLE' ,total_blocks => total_blocks ,total_bytes => total_bytes ,unused_blocks => unused_blocks ,unused_bytes => unused_bytes ,last_used_extent_file_id => last_extent_file ,last_used_extent_block_id => last_extent_block ,last_used_block => last_block ); grand_total_blocks := grand_total_blocks + total_blocks; grand_total_unused := grand_total_unused + unused_blocks; END LOOP; DBMS_OUTPUT.PUT_LINE('Space utilization (TABLES) '); DBMS_OUTPUT.PUT_LINE('total blocks: '|| TO_CHAR(grand_total_blocks) ); DBMS_OUTPUT.PUT_LINE('unused blocks: '|| TO_CHAR(grand_total_unused) ); DBMS_OUTPUT.PUT_LINE('pct unused: '|| TO_CHAR(ROUND((grand_total_unused/grand_total_blocks)*100) ) ); END; /
This is a sample of the report output:
Space utilization (TABLES) total blocks: 1237 unused blocks: 613 pct unused: 50
In Oracle 8.0, the partition_name parameter was added to support space analysis for partitioned segments. Since the new parameter has a default value, calls to UNUSED_SPACE written to the version 7.x specification will continue to work under 8.0.
ALTER [ TABLE | INDEX | CLUSTER ] segment_name DEALLOCATE UNUSED;
For information on how Oracle allocates and manages segment space, see the Oracle7 Server Concepts manual. For information on the DEALLOCATE UNUSED clause of the ALTER TABLE statement, see the Oracle7 Server SQL Reference .
The DBMS_SPACE package is a good example of how Oracle Corporation is using the built-in packages to expose, in a controlled way, information about database internals not found in the data dictionary.
DBAs managing large transaction-oriented databases must pay attention to space utilization within segments. The UNUSED_SPACE procedure provides an additional level of detail that can help the DBAs make better use of space. For instance, wasted space that can be freed back to the tablespace for use by other segments can be detected and measured. Also, segment growth rates can be measured more accurately than by monitoring extent allocation, providing better information on the need to expand tablespaces.
DBAs, especially those with Oracle parallel server installations, will be interested additionally in monitoring the segment freelist information exposed by the FREE_BLOCKS procedure.
Figure 12.1 illustrates how the blocks of a segment fall into one of three categories: used, unused (above the highwater mark), and on the free list. The latter two categories are the subject of the UNUSED_SPACE and FREE_BLOCKS procedures, respectively.
I really like the information available in the DBMS_SPACE programs. However, I find that the programs are very unwieldy to use, due to their long, cumbersome parameter lists. The UNUSED_SPACE procedure has at least three IN parameters and seven OUT parameters! Even the earlier simple illustrative example is many lines long. What if you're interested in only one of the OUT parameters -- say, unused_blocks? You still have to allocate variables to hold all the other OUT parameters just to make the procedure call. Wouldn't it be nice to simply call a function that returns the unused blocks number for a given segment?
As usual, the solution to such usability issues lies in creating a package to encapsulate those unwieldy program calls with an easier-to-use layer of programs. Here is the specification for my own package called segspace:
|| Compilation Requirements: || || Execution Requirements: || || ANALYZE ANY system privilege || */ AS /* || sets the specified segment as current context */ PROCEDURE set_segment (name_IN IN VARCHAR2 ,type_IN IN VARCHAR2 ,schema_IN IN VARCHAR2 ,partition_IN IN VARCHAR2 DEFAULT NULL); /* returns current segment name */ FUNCTION current_name RETURN VARCHAR2; /* returns current segment type */ FUNCTION current_type RETURN VARCHAR2; /* returns current segment schema */ FUNCTION current_schema RETURN VARCHAR2; /* || returns total_blocks from DBMS_SPACE.UNUSED_SPACE || for the segment specified */ FUNCTION total_blocks (name_IN IN VARCHAR2 DEFAULT current_name ,type_IN IN VARCHAR2 DEFAULT current_type ,schema_IN IN VARCHAR2 DEFAULT current_schema) RETURN NUMBER; /* || returns unused_blocks from DBMS_SPACE.UNUSED_SPACE || for the segment specified */ FUNCTION unused_blocks (name_IN IN VARCHAR2 DEFAULT current_name ,type_IN IN VARCHAR2 DEFAULT current_type ,schema_IN IN VARCHAR2 DEFAULT current_schema) RETURN NUMBER; /* || returns number of blocks on segment freelist using || DBMS_SPACE.FREE_BLOCKS */ FUNCTION freelist_blocks (name_IN IN VARCHAR2 DEFAULT current_name ,type_IN IN VARCHAR2 DEFAULT current_type ,schema_IN IN VARCHAR2 DEFAULT current_schema ,freelist_group_IN IN NUMBER DEFAULT 0 ,partition_IN IN VARCHAR2 DEFAULT NULL) RETURN NUMBER; END segspace;
The segspace package has functions called total_blocks and unused_blocks. These functions both accept segment identification information as IN parameters and return the value of their corresponding OUT parameters from DBMS_SPACE.UNUSED_SPACE. So in SQL*Plus, you can use these functions as follows:
SQL> var tot_blks NUMBER SQL> execute :tot_blks := segspace.total_blocks('TENK','TABLE','LOAD1'); PL/SQL procedure successfully completed. SQL> print tot_blks TOT_BLKS --------- 455
Well, this sure is a lot easier than calling the UNUSED_SPACE procedure directly! Notice, however, that the IN parameters to these functions also all have default values, which means that they can be suppressed when making the function call (as long as the default is the desired value). The default values used are the segment identifiers (name, type, and schema) most recently specified. Thus we can find out the unused blocks for the LOAD1.TENK table by immediately following the preceding call with this:
SQL> var unused_blks number SQL> execute :unused_blks := segspace.unused_blocks; PL/SQL procedure successfully completed. SQL> print unused_blks UNUSED_BLKS ----------- 10
By retaining the current segment context set by the previous call and using default values, a complex procedure call (UNUSED_SPACE) with ten parameters is transformed into a simple function call requiring only a target variable for its result.
Now, this is something I might be able (and want) to actually use.
Astute readers will suspect that private package globals play a part in this trickery, and they are correct. They may also raise questions about the performance implications of splitting the OUT parameters of UNUSED_SPACE into individual function calls, as this is a relatively "expensive" procedure call to make and should not be redundantly or needlessly invoked. Well, segspace is designed to be both useful and efficient.
Here is the package body for segspace (an explanation follows the code):
//* Filename on companion disk: segspace.sql */* CREATE OR REPLACE PACKAGE BODY segspace AS /* record type to hold data on segment */ TYPE segdata_rectype IS RECORD (name VARCHAR2(30) ,schema VARCHAR2(30) DEFAULT USER ,type VARCHAR2(30) DEFAULT 'TABLE' ,partition VARCHAR2(30) DEFAULT NULL ,total_blocks NUMBER ,total_bytes NUMBER ,unused_blocks NUMBER ,unused_bytes NUMBER ,last_extent_file NUMBER ,last_extent_block NUMBER ,last_block NUMBER ,last_segload DATE := SYSDATE - 1 ); /* global rec for current segment data */ segdata_rec segdata_rectype; /* reload timeout in seconds */ segload_timeout INTEGER := 60; /* flag for new segment */ newseg_TF BOOLEAN := TRUE; /* || returns the segment name from segdata_rec */ FUNCTION current_name RETURN VARCHAR2 IS BEGIN RETURN segdata_rec.name; END current_name; /* || returns the segment type from segdata_rec */ FUNCTION c urrent_type RETURN VARCHAR2 IS BEGIN RETURN segdata_rec.type; END current_type; /* || returns the segment schema from segdata_rec */ FUNCTION current_schema RETURN VARCHAR2 IS BEGIN RETURN segdata_rec.schema; END current_schema; /* || sets specific segment as context */ PROCEDURE set_segment (name_IN IN VARCHAR2 ,type_IN IN VARCHAR2 ,schema_IN IN VARCHAR2 ,partition_IN IN VARCHAR2 DEFAULT NULL) IS BEGIN /* check if new segment and set flag */ IF ( segdata_rec.schema != schema_IN OR segdata_rec.name != name_IN OR segdata_rec.type != type_IN OR segdata_rec.partition != partition_IN ) THEN newseg_TF := TRUE; ELSE newseg_TF := FALSE; END IF; /* set segment globals */ segdata_rec.schema := schema_IN; segdata_rec.name := name_IN; segdata_rec.type := type_IN; segdata_rec.partition := partition_IN; END set_segment; FUNCTION reload_TF RETURN BOOLEAN IS /* || returns TRUE if timed out or new segment since last load */ BEGIN RETURN ( SYSDATE > segdata_rec.last_segload + segload_timeout/(24*60*60) ) OR newseg_TF; END reload_TF; PROCEDURE load_unused IS /* || loads segment unused space data for current segment using || DBMS_SPACE.UNUSED_SPACE if the segment is new or timeout limit || reached since last load */ BEGIN IF reload_TF THEN DBMS_SPACE.UNUSED_SPACE (segment_owner => segdata_rec.schema ,segment_name => segdata_rec.name ,segment_type => segdata_rec.type ,total_blocks => segdata_rec.total_blocks ,total_bytes => segdata_rec.total_bytes ,unused_blocks => segdata_rec.unused_blocks ,unused_bytes => segdata_rec.unused_bytes ,last_used_extent_file_id => segdata_rec.last_extent_file ,last_used_extent_block_id => segdata_rec.last_extent_block ,last_used_block => segdata_rec.last_block /* -------------------------------------------- */ /* NOTE: uncomment following line for Oracle 8 */ /* -------------------------------------------- */ /* ,partition_name => segdata_rec.partition */ ); segdata_rec.last_segload := SYSDATE; END IF; END load_unused; FUNCTION total_blocks (name_IN IN VARCHAR2 ,type_IN IN VARCHAR2 ,schema_IN IN VARCHAR2) RETURN NUMBER IS /* || sets current segment and calls load_unused */ BEGIN set_segment(name_IN, type_IN, schema_IN); load_unused; RETURN segdata_rec.total_blocks; END total_blocks; FUNCTION unused_blocks (name_IN IN VARCHAR2 ,type_IN IN VARCHAR2 ,schema_IN IN VARCHAR2) RETURN NUMBER IS /* || sets current segment and calls load_unused */ BEGIN set_segment(name_IN, type_IN, schema_IN); load_unused; RETURN segdata_rec.unused_blocks; END unused_blocks; /* || returns number of blocks on segment freelist using || DBMS_SPACE.FREE_BLOCKS */ FUNCTION freelist_blocks (name_IN IN VARCHAR2 DEFAULT current_name ,type_IN IN VARCHAR2 DEFAULT current_type ,schema_IN IN VARCHAR2 DEFAULT current_schema ,freelist_group_IN IN NUMBER DEFAULT 0 ,partition_IN IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS /* variable to hold output from call to FREE_BLOCKS */ temp_freelist_blocks NUMBER; /* || loads segment freelist size using DBMS_SPACE.FREE_BLOCKS || scan limit NULL means no limit */ BEGIN DBMS_SPACE.FREE_BLOCKS (segment_owner => schema_IN ,segment_name => name_IN ,segment_type => type_IN ,freelist_group_id => freelist_group_IN ,free_blks => temp_freelist_blocks ,scan_limit => NULL /* -------------------------------------------- */ /* NOTE: uncomment following line for Oracle 8 */ /* -------------------------------------------- */ /* ,partition_name => partition_IN */ ); RETURN temp_freelist_blocks; END freelist_blocks; END segspace;
The segspace package body declares a record type called segdata_rectype and a private global of that type called segdata_rec. This record is designed to hold a copy of all parameters (both IN and OUT) used by the UNUSED_SPACE procedure. The name, schema, type, and partition fields in segdata_rec correspond to the IN parameters of UNUSED_SPACE. These are set using the set_segment procedure. Think of this as the current segment context -- the segment currently being analyzed. The functions current_name, current_schema, and current_type simply return the corresponding elements of the current segment context.
The l oad_unused procedure is the one that actually calls UNUSED_SPACE. It takes as IN parameters the appropriate field values from segdata_rec, and assigns its OUT values to the corresponding fields in segdata_rec. Now the individual OUT parameters from UNUSED_SPACE can be exposed through individual function calls that return fields from segdata_rec.
So the basic logic is quite simple:
Now, a three-step process to retrieve the data items individually does not really represent an increase in usability, so what really happens is that the functions total_blocks and unused_blocks each do all three steps.
FUNCTION unused_blocks (name_IN IN VARCHAR2 ,type_IN IN VARCHAR2 ,schema_IN IN VARCHAR2) RETURN NUMBER IS /* || sets current segment and calls load_unused */ BEGIN set_segment(name_IN, type_IN, schema_IN); load_unused; RETURN segdata_rec.unused_blocks; END unused_blocks;
Remember that calling DBMS_SPACE.UNUSED_SPACE is relatively expensive, and we want to avoid calling it more often than necessary. It would be nice to be able to do the following without calling UNUSED_SPACE twice:
BEGIN pct_free := 100* (segspace.unused_blocks('TABLENAME','TABLE','SCHEMA') / segspace.total_blocks('TABLENAME','TABLE','SCHEMA') ); END;
The load_unused procedure avoids calling UNUSED_SPACE too often by checking a function called reload_TF, which returns a BOOLEAN indicating whether to reload segment data. The reload_TF function will return TRUE (reload) if either of the following is TRUE:
Thus the previous PL/SQL block will call UNUSED_SPACE at most once (and perhaps not at all, if it was recently called for the same segment). The private global newseg_TF is a BOOLEAN flag indicating a new context. This is maintained by the set_segment procedure: whenever a context is established, the flag is set to TRUE, if it is a new context.
Additional usability in the total_blocks and unused_blocks functions is achieved by using default values for the IN parameters and careful ordering of the parameters. The default values for name_IN, type_IN, and schema_IN are assigned by the functions current_name, current_type, and current_schema. The parameters are ordered such that the most likely to change (name) is first, followed by type, and then schema. This is based on the reasonable assumption that when doing space analysis, the user will probably do all tables by schema or all indexes by schema. Additionally, the initial default prior to setting a context at all is the current user schema and segment type TABLE.
Q: Q: Why did I use functions for the default values instead of direct reference to segdata_rec?
A: A: In order to directly reference the segdata_rec components for parameter defaults in the package specification, I would have also had to declare segdata_rec in the specification. This would expose segdata_rec such that it could be inadvertently modified by other programs. By using functions for the default values, segdata_rec can be declared privately (and thus protected) in the package body.
A: The function freelist_blocks simply calls DBMS_SPACE.FREE_BLOCKS and returns its single OUT parameter free_blks. Because FREE_BLOCKS has only a single OUT parameter, it was not really necessary to implement the optimizations discussed previously to avoid redundant calls. The function does improve usability by supplying defaults for the IN parameters to FREE_BLOCKS, reducing the calling profile where the defaults are correct. Be careful, though, because freelist_blocks does not do a set_segment to establish a context. If it is used alone (i.e., not in conjunction with total_blocks or unused_blocks), it is best to specify a full segment context in the call.
Q: Q: Why did I choose to not have freelist_blocks call set_segment to establish a segment context?
A: A: The unused_blocks function relies on segdata_rec to provide information about the current segment context loaded by the load_unused procedure. If the freelist_blocks function were to call set_segment to establish a context, it would also have to call load_unused to keep segdata_rec in synch with the context. This would introduce significant and unnecessary overhead, when only freelist information is desired.
Here is an example of using the segspace package in a SQL*Plus script to report on unused space in segments for a specific tablespace:
/* Filename on companion disk: spcex2.sql */* undefine tablespace_name set serveroutput on size 100000 set verify off DECLARE total_blocks NUMBER :=0; unused_blocks NUMBER :=0; BEGIN DBMS_OUTPUT.PUT_LINE('TABLESPACE: '||UPPER('&&tablespace_name')); FOR seg_rec IN (SELECT segment_name, segment_type, owner FROM dba_segments WHERE tablespace_name = UPPER('&&tablespace_name') ) LOOP total_blocks := total_blocks + segspace.total_blocks (seg_rec.segment_name ,seg_rec.segment_type ,seg_rec.owner); unused_blocks := unused_blocks + segspace.unused_blocks; END LOOP; DBMS_OUTPUT.PUT_LINE('Total Blocks: '||TO_CHAR(total_blocks)); DBMS_OUTPUT.PUT_LINE('Unused Blocks: '||TO_CHAR(unused_blocks)); DBMS_OUTPUT.PUT_LINE('Pct Unused: '|| TO_CHAR(ROUND(unused_blocks/total_blocks*100)) ); END; /
This is sample output from executing the script:
Enter value for tablespace_name: LOAD_DATA TABLESPACE: LOAD_DATA Total Blocks: 9195 Unused Blocks: 1300 Pct Unused: 14 PL/SQL procedure successfully completed.
I like using segspace to probe space utilization within segments. With segspace, I can obtain useful reports like the previous one with a few quick lines. Direct use of DBMS_SPACE would require much more time and effort.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.