home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 11.2 DBMS_System: Setting Events for Debugging Chapter 12 Next: 12.2 DBMS_SHARED_POOL: Pinning Objects
 

12. Managing Server Resources

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.

DBMS_SPACE

Gives DBAs an analysis of the amount of space both used and free within a table, index, or cluster segment. It also provides information about segment free list sizes -- information of special interest to Oracle Parallel Server administrators.

DBMS_SHARED_POOL

On the memory side, gives DBAs some measure of control over the Oracle System Global Area's (SGA's) shared pool. By pinning large packages into the shared pool, expensive runtime memory management (and even errors) can be avoided.

12.1 DBMS_SPACE: Obtaining Space 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.

12.1.1 Getting Started with DBMS_SPACE

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.


Table 12.1: DBMS_SPACE Programs

Name

Description

Use in

SQL?

FREE_BLOCKS

Returns information on free blocks for a segment

No

UNUSED_SPACE

Returns unused space information for a segment

No

The DBMS_SPACE package does not declare any exceptions or nonprogram elements.

12.1.2 The DBMS_SPACE Interface

This section describes the programs defined in DBMS_SPACE.

12.1.2.1 The DBMS_SPACE.FREE_BLOCKS procedure

The FREE_BLOCKS procedure returns information about the number of blocks on Oracle's freelist groups for a table, index, or cluster segment. Specifications for Oracle7 and Oracle8 vary as follows.

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.

Parameter

Description

segment_owner

Schema of segment

segment_name

Name of segment

segment_type

Type of segment

freelist_group_id

Freelist group to compute

free_blks

Number of blocks on freelist

scan_limit

Maximum blocks to read

partition_name

Name of partition (8.0 only)

12.1.2.1.1 Exceptions

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:

ORA-00942

Table or view does not exist.

12.1.2.1.2 Restrictions

Note the following restrictions on using FREE_BLOCKS:

  • The user must have the ANALYZE ANY system privilege to use DBMS_SPACE.FREE_BLOCKS on segments from schemas other than the current session schema.

  • The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

12.1.2.1.3 Example

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.

For more information about freelists and how they can be used to minimize contention, see the Oracle7 Parallel Server Concepts and Administration manual.

12.1.2.2 The DBMS_SPACE.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.

Name

Description

segment_owner

Schema of segment

segment_name

Name of segment

segment_type

Type of segment

total_blocks

Total data blocks in segment

total_bytes

Total bytes in segment

unused_blocks

Total unused blocks in segment

unused_bytes

Total unused bytes in segment

last_used_extent_file_id

File id of last used extent

last_used_extent_block_id

Block id of last used extent

last_used_block

Last used block in extent

partition_name

Name of partition (8.0 only)

12.1.2.2.1 Exceptions

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:

ORA-00942

Table or view does not exist.

12.1.2.2.2 Restrictions

Note the following restrictions on calling the UNUSED_SPACE procedure:

  • The user must have the ANALYZE ANY system privilege to use UNUSED_SPACE on segments from schemas other than the current session schema.

  • The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

12.1.2.2.3 Example

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.

WARNING: Under Oracle 8.0, calling DBMS_SPACE.UNUSED_SPACE for a segment results in a DDL lock being held on the segment until the PL/SQL scope within which the call is made completes. This prevents any other DDL from being executed on the segment, so long-running programs that use the UNUSED_SPACE procedure could cause unexpected interference with other DDL operations in the database.

Unused space can be deallocated from segments and returned to the free space for the segment's tablespace using the following SQL command:

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 .

12.1.3 DBMS_SPACE Examples

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.

Figure 12.1: Space utilization in a segment

Figure 12.1

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?

12.1.3.1 The segspace package

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:

//* Filename on companion disk: 

segspace.sql */*
CREATE OR REPLACE PACKAGE segspace
   /*
   || Extends the DBMS_SPACE package by creating function
   || calls to return individual parameter values
   ||
   || Author:  John Beresniewicz, Savant Corp
   || Created: 07/29/97
   ||
   || 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.

The set_segment procedure is used to set the current segment context, which amounts to establishing the default IN parameters for all the functions.

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:

  1. The set_segment procedure establishes a segment context in segdata_rec.

  2. The load_unused procedure loads UNUSED_SPACE information for the current segment into segdata_rec.

  3. Individual field values from segdata_rec are returned through functions such as total_blocks and unused_block.

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:

  • It has been longer than segload_timeout seconds since the last call to DBMS_SPACE.LOAD_UNUSED.

  • The current segment context is different than the context for the last call to DBMS_SPACE.LOAD_UNUSED.

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.


Previous: 11.2 DBMS_System: Setting Events for Debugging Oracle Built-in Packages Next: 12.2 DBMS_SHARED_POOL: Pinning Objects
11.2 DBMS_System: Setting Events for Debugging Book Index 12.2 DBMS_SHARED_POOL: Pinning Objects

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference