Monitoring Cluster Storage Statistics

Oracle Database Tips by Donald Burleson

As a DBA, you may be interested in the storage statistics used to create each cluster. This data is found in the DBA_CLUSTERS view. The script in Source 10.29 is an example of this type of report. An example of the script's output is shown in Listing 10.28.

SOURCE 10.29 Example of script to produce a cluster sizing report.

rem Name: clus_siz.sql
rem
rem FUNCTION: Generate a cluster sizing report
rem
COLUMN owner                 FORMAT a10
COLUMN cluster_name          FORMAT a15            HEADING "Cluster"
COLUMN tablespace_name       FORMAT a15            HEADING "Tablespace"
COLUMN pct_free              FORMAT 999            HEADING "%|Fre"
COLUMN pct_used              FORMAT 999            HEADING "%|Use"
COLUMN key_size              FORMAT 999999         HEADING "Key Size"
COLUMN ini_trans             FORMAT 999            HEADING "Ini|Trn"
COLUMN max_trans             FORMAT 999            HEADING "Max|Trn"
COLUMN initial_extent        FORMAT 999999999      HEADING "Init Ext"
COLUMN next_extent           FORMAT 999999999      HEADING "Next Ext"
COLUMN min_extents           FORMAT 999            HEADING "Min|Ext"
COLUMN max_extents           FORMAT 999            HEADING "Max|Ext"
COLUMN pct_increase          FORMAT 999            HEADING "%|Inc"
SET PAGES 56 LINES 130 FEEDBACK OFF
START title132 "Cluster Sizing Report"
BREAK ON owner ON tablespace_name
SPOOL rep_out\&db\cls_sze
SELECT
     owner,
     tablespace_name,
     cluster_name,
     pct_free,
     pct_used,
     key_size,
     ini_trans,
     max_trans,
     initial_extent,
     next_extent,
     min_extents,
     max_extents,
     pct_increase
FROM
     dba_clusters
ORDER BY
     1,2,3
/
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
SET PAGES 22 LINES 80 FEEDBACK ON
PAUSE Press enter to continue

LISTING 10.28 Example of output of the cluster sizing report.

Date: 05/17/96                                                                  Page:   1
Time: 04:06 PM                Cluster Sizing Report                             SYS
                              ORDSPTD6 database

Schema                           %   %            Int Max                     Min Max %
Owner Tablespace Cluster        Fre Use Key Size Trn Trn Init Ext Next Ext   Ext Ext Inc
------ ---------- -------------- --- --- --- ---- --- --- ---- --- ---- ----- --- --- ---
SYS    SYSTEM     C_COBJ#        10   50      300   2 255    51200      83968   1 121 50
                  C_FILE#_BLOCK# 10   40      225   2 255    20480     190464   1 121 50
                  C_MLOG#        10   40            2 255    10240      10240   1 121 50
                  C_OBJ#          5   40      800   2 255   122880     430080   1 121 50
                  C_RG#          10   40            2 255    10240      10240   1 121 50
                  C_TS#          10   40            2 255    10240      16384   1 121 50
                  C_USER#        10   40      315   2 255    10240      10240   1 121 50
                  HIST$           5   40      200   2 255    10240      10240   1 121 50

The reports in Sources 10.28 and 10.29 give the DBA information on cluster keys, cluster columns, cluster tables, and columns and cluster sizing. Combined with the actual size and extent reports previously shown, the DBA can have a complete picture of clusters in his or her database.

Monitoring Cluster Statistics

The DBA_CLUSTERS view in versions after Oracle8 has several additional columns: DBA_CLUSTERS view are AVG_BLOCKS_PER_KEY, CLUSTER_TYPE, FUNCTION, and HASHKEYS. These additional columns provide a more detailed glimpse of cluster status. The report script shown in Source 10.30 can be modified to include these columns (132 is about the widest you can go, and not a good choice) or a new report can be created. An example of the output from Source 10.30 is shown in Listing 10.29.

SOURCE 10.30 Report script for new DBA_CLUSTERS columns.

rem Name        : clu_stat.sql
rem Purpose     : Report on new DBA_CLUSTER columns
rem Use         : From an account that accesses DBA_ views
rem
COLUMN owner                   FORMAT a10      HEADING "Owner"
COLUMN cluster_name            FORMAT a15      HEADING "Cluster"
COLUMN tablespace_name         FORMAT a10      HEADING "Tablespace"
COLUMN avg_blocks_per_key      FORMAT 999999   HEADING "Blocks per Key"
COLUMN cluster_type            FORMAT a8       HEADING "Type"
COLUMN function                FORMAT 999999   HEADING "Function"
COLUMN hashkeys                FORMAT 99999    HEADING "# of Keys"
SET PAGES 56 LINES 79 FEEDBACK OFF
START title80 "Cluster Statistics Report"
SPOOL report_output/&db/clu_type
SELECT
     owner,
     cluster_name,
     tablespace_name,
     avg_blocks_per_key,
     cluster_type,
     function,
     hashkeys
FROM
     dba_clusters
ORDER BY 2
GROUP BY owner, tablespace, type
/
SPOOL OFF
SET PAGES 22 LINES 80 FEEDBACK ON
CLEAR COLUMNS
TTITLE OFF

LISTING 10.29 Example of output from cluster statistics report script.

Date: 05/22/96                                               Page:   1
Time: 12:54 PM                Cluster Type Report               SYSTEM
                              ORDSPTD6 database

                                 Blocks
                                 per                              # of
Owner Cluster        Tablespace Key     Type     Function        Keys
------ -------------- ---------- ------- -------- --------------- ----
SYS    C_COBJ#        SYSTEM               INDEX
       C_FILE#_BLOCK#
       C_MLOG#
       C_OBJ#
       C_RG#
       C_TS#
       C_USER#
       HIST$
       C_RG#

Monitoring Cluster Hash Expressions

As of later versions of Oracle7, including all versions of Oracle8, and Oracle8i and Oracle9i, the capability to specify your own hash expressions for a hash cluster has been provided. These hash expressions can be viewed for a specific cluster by querying the DBA_CLUSTER_ HASH_EXPRESSION view. The DBA_CLUSTER_HASH_EXPRESSION view has three columns: OWNER, CLUSTER_NAME, and HASH_EXPRESSION. HASH_EXPRESSION is a LONG, so be sure to allow for extra-length character strings by using the WORD_WRAPPED parameter on a COLUMN command when querying this value.

Monitoring of Materialized Views and Materialized View Logs Using DBA_ and V Type Views

Snapshots and snapshot logs are Oracle7 and Oracle8 features. Under Oracle8i, the localized snapshot (materialized views) also became available. Snapshots make it possible to maintain read-only copies of a table or columns from multiple tables in several locations. The refresh rate of the materialized views can be varied and accomplished automatically.

Note: Remember, as of Oracle8i, the term materialized view has replaced the term snapshot.

The DBA needs tools to monitor materialized views and materialized view logs. The OEM in Oracle8, Oracle8i, and Oracle9i provide screens that let the DBA see the status of the database's materialized views. The Replication Manager application also provides monitoring and control functionality. At times, however, it is more convenient to have a hard-copy documentation of materialized views and materialized view logs. The script in Source 10.31, the report in Listing 10.30, and Source 10.32 document a database's materialized views and materialized view logs.

SOURCE 10.31 Example of script to document materialized views and materialized view logs.

rem
rem Name:     mv_rep.sql
rem Purpose:Report on database Materialized views
rem Use:     From an account that accesses DBA_MVIEWS
rem
rem   When       Who         What
rem   -------    --------- ----------------
rem   5/27/93    Mike Ault Initial Creation
rem   10/10/01   Mike Ault Update to 9i
rem
SET PAGES 56 LINES 130 FEEDBACK OFF VERIFY OFF
rem
COLUMN mv      FORMAT a30      HEADING "Materialized|View"
COLUMN source        FORMAT a30      HEADING "Source Table"
COLUMN log                           HEADING "Use|Log?"
COLUMN type          FORMAT a10      HEADING "Ref|Type"
COLUMN refreshed                     HEADING "Last Refresh"
COLUMN start         FORMAT a13      HEADING "Start Refresh"
COLUMN error                         HEADING "Error"
COLUMN next          FORMAT a13      HEADING "Next Refresh"
rem
PROMPT Percent signs are wild card
ACCEPT mv_owner PROMPT Enter the materialized view owner
START title132 "Materialized View Report for &mv_owner"
SPOOL rep_out/&db/mv_rep&db
rem
SELECT
     Owner||'.'||mview_name mv, master_view,
     master_link Source,
     substr(query,1,query_len) query,
     updatable,
     update_log Log, last_refresh_date Refreshed,
DECODE(refresh_mode,'FAST','F','COMPLETE','C','FORCE','FR','COMMIT','CM'),
     query,
     master_rollback_segment rbk
FROM dba_mviews
WHERE owner LIKE UPPER('%&mv_owner%')
ORDER BY owner,mview_name;
rem
SPOOL OFF

LISTING 10.30 Example of output of the script in Source 10.31.

Date: 06/10/93                  "Your Company Name "                 Page:  1
Time: 04:28 PM               Snapshot Report for DEV7_DBA
DEV7_DBA
                                  "Your Database"
                                   Use           Ref
Snapshot         View    Source    Log Last Ref Typ Next Ref Started   Query
---------------- ------- --------- --- --------- --- --------- --------- ------
TEST.SNAP$_TEST MVIEW$_ DEV7_DBA. YES 10-JUN-93 F   SYSDATE+7 10-JUN-93 SELECT
CHECK_DATE
      TEST     HIT_RATIO                                       FROM HIT_RATIOS

SOURCE 10.32 Example of script to generate materialized view log report.

rem
rem Name:     mv_log_rep.sql
rem Purpose:     Report on database materialized view Logs
rem Use:     From an account that accesses DBA_ views
rem
rem   When       Who         What
rem   -------    ---------   ----------------
rem   5/27/93    Mike Ault   Initial Creation
rem   10/10/01   Mike Ault    Updated to oracle9i
rem
SET PAGES 56 LINES 130 FEEDBACK OFF
START title132 "Materialized View Log Report"
SPOOL rep_out/&db/mv_log_rep&db
rem
COLUMN log_owner      FORMAT a10 HEADING "Owner"
COLUMN master         FORMAT a20 HEADING "Master"
COLUMN log_table      FORMAT a20 HEADING "Materialized View"
COLUMN trigger        FORMAT a20 HEADING "Trigger Text"
COLUMN current                   HEADING "Last Refresh"
rem
SELECT
     log_owner, master, log_table table,
     log_trigger trigger, rowids, filter_columns filtered,
     object_id id, sequence seq,Include_new_values new
FROM
     dba_mview_logs
ORDER BY 1;
rem
SPOOL OFF
CLEAR COLUMNS
SET FEEDBACK ON
TTITLE OFF

The reports from Sources 10.31 and 10.32 will provide the DBA with hard-copy documentation of all materialized views and materialized view logs in the database. Each can be made more restrictive by using WHERE clauses, by selecting on a specific set of values such as owner or log_owner, type, or date since last refresh (last_refresh > &date or current_snapshots > &date).

Later in this chapter, you will learn how to monitor DIMENSIONS, a new Oracle8i feature used with materialized views to provide for query rewrite.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

��