Monitoring Operators

Operators were new to Oracle8i. They enable extensibility of Oracle by allowing users to add operators (+, -, AND, OR, BETWEEN) to the database. Operators are also a key component of the Oracle8i INDEXTYPE (domain indexes). The views used to monitor operators are DBA_OPERATORS, DBA_OPARGUMENTS, DBA_OPANCILLARY, and DBA_OPBINDINGS. The operators? series of views are diagrammed in Figure 10.6 along with the DIMENSION and OUTLINE views. Source 10.37 shows an example of an operator report. An example of the output from the script in Source 10.37 is shown in Listing 10.35.

Figure 10.6 Miscellaneous DBA_ view clusters.

SOURCE 10.37 Example of database OPERATOR report.

rem
rem NAME: operator.sql
rem FUNCTION: Generate a lit of all OPERATORS in the database
rem for a specific user or all users
rem HISTORY: MRA 5/12/98 Created
rem
COLUMN owner             FORMAT a8   HEADING 'Owner'
COLUMN operator_name     FORMAT a10  HEADING 'Operator|Name'
COLUMN number_of_binds   FORMAT 9999 HEADING 'Binds'
COLUMN position                      HEADING 'Position'
COLUMN argument_type     FORMAT A20  HEADING 'Argument|Type'
COLUMN function_name     FORMAT A20  HEADING 'Binding|Argument'
COLUMN return_schema     FORMAT A10  HEADING 'Return|Schema'
COLUMN return_type       FORMAT A20  HEADING 'Return|Type'
BREAK ON owner ON operator_name ON number_of_binds
SET PAGES 58 LINES 130 FEEDBACK OFF VERIFY OFF
START title132 'Database OPERATOR Report'
SPOOL rep_out\&db\operator.lis
SELECT
     a.owner,
     a.operator_name,
     a.number_of_bindings,
     b.position,
     b.argument_type,
     c.function_name,
     DECODE(c.return_schema,NULL,'Internal',c.return_schema) return_schema,
     c.return_type
FROM
     Dba_operators a, dba_oparguments b, dba_opbindings c
WHERE
         Owner LIKE '%&owner%'
     AND a.owner=b.owner
     AND a.operator_name=b.operator_name
     AND a.owner=c.owner
     AND a.operator_name=c.operator_name
     AND b.binding#=c.binding#;
SPOOL OFF
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON

LISTING 10.35 Example of output from database OPERATOR report.

Date: 06/17/99                                                 Page:  1
Time: 08:45 PM               Database OPERATOR Report          SYSTEM
                                 ORTEST1 database                      

       Operator               Argument Bound                 Return   Return
Owner  Name    Binds Position Type     Function              Schema   Type
------ ------  ----- -------- -------- --------------------- -------- --------
SYSTEM CONCAT      1        1 VARCHAR2 "CON"."CONCAT_STRING" Internal VARCHAR2
                            2 VARCHAR2 "CON"."CONCAT_STRING" Internal VARCHAR2

You should review the operator report to ensure that the operator bindings are properly ascribed and are of the correct type. Also, make sure that the function for the operator is properly assigned and that input and output types are properly defined.

Monitoring Dimensions

Dimensions, new to Oracle8i, are usually used in data warehouse applications to allow Oracle to remap queries efficiently against summaries (materialized views). Dimensions describe the relationships in a large denormalized table or a set of quasi-normalized tables such as would be found in a star or snowflake design DSS or data warehouse application. The views used to monitor dimensions are shown in Figure 10.6.

Dimensions contain levels and hierarchies that are linked using join keys. A report showing some of these aspects of dimensions is shown in Source 10.38. An example of the output from Source 10.38 is shown in Listing 10.36. Additional reports showing the relationship of dimension to hierarchy and dimension and attribute are shown in Sources 10.39 and 10.40. Example outputs are shown in Listings 10.36 and 10.37.

SOURCE 10.38 Example of database dimension-level report.

rem
rem NAME: dim_level.sql
rem FUNCTION: Generate a lit of all Dimensions and levels in the
rem database for a specific user or all users
rem HISTORY: MRA 5/12/98 Created
rem
COLUMN owner             FORMAT a8   HEADING 'Owner'
COLUMN dimension_name     FORMAT a10  HEADING 'Dimension|Name'
COLUMN level_name        FORMAT a10 HEADING 'Level|Name'
COLUMN column_name       FORMAT a20 HEADING 'Column|Name'
COLUMN key_position      FORMAT 9999 HEADING 'Key|Position'
BREAK ON owner ON operator_name ON number_of_binds
SET PAGES 58 LINES 130 FEEDBACK OFF VERIFY OFF
START title132 'Database Dimension Levels Report'
SPOOL rep_out\&db\dim_level.lis
SELECT
     a.owner,
     a.dimension_name,
     b.level_name,
     c.column_name,
     c.key_position
FROM
     Dba_dimensions a, dba_dim_levels b, dba_dim_level_key c
WHERE
          a.Owner LIKE '%&owner%'
      AND a.owner=b.owner
      AND a.dimension_name=b.dimension_name
      AND a.owner=c.owner
      AND a.dimension_name=c.dimension_name
      AND b.level_name=c.level_name
ORDER BY
      a.owner,
      a.dimension_name,
      b.level_name;
SPOOL OFF
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON

LISTING 10.36 Example of output from database dimension-level report.

Date: 05/13/99                                               Page:   1
Time: 11:25 PM        Database Dimension Levels Report          SYSTEM
                               ORTEST1 database                                

         Dimension  Level      Column               Key
Owner    Name       Name       Name                 Position
-------- ---------- ---------- -------------------- ---------
TELE_DBA TEST_DIM   CHILD_ID   ID                           1
         TEST_DIM   PARENT_ID  PARENT_ID                    1          

The database dimension-level report should be reviewed to ensure that the levels are assigned to the proper columns.

SOURCE 10.39 Example of database dimension hierarchy report.

rem
rem NAME: dim_hierarchies.sql
rem FUNCTION: Generate a lit of all dimensions and hierarchies in the
rem database for a specific user or all users
rem HISTORY: MRA 5/12/98 Created
rem
COLUMN owner             FORMAT a8   HEADING 'Owner'
COLUMN dimension_name    FORMAT a10  HEADING 'Dimension|Name'
COLUMN column_name       FORMAT a10  HEADING 'Column|Name'
COLUMN hierarchy_name    FORMAT a10  HEADING 'Hierarchy|Name'
COLUMN parent_level_name FORMAT a10  HEADING 'Parent|Level'
COLUMN child_level_name  FORMAT a10  HEADING 'Child|Level'
COLUMN join_key_id       FORMAT a20 HEADING 'Join Key|ID'
BREAK ON owner ON dimension_name
SET PAGES 58 LINES 78 FEEDBACK OFF VERIFY OFF
START title80 'Database Dimension Hierarchy Report'
SPOOL rep_out\&db\dim_hierarchies.lis
SELECT
     a.owner,
     a.dimension_name,
     b.hierarchy_name,
     c.parent_level_name,
     c.child_level_name,
     c.join_key_id
FROM
     Dba_dimensions a, dba_dim_hierarchies b, dba_dim_child_of c
WHERE
          a.Owner LIKE '%&owner%'
      AND a.owner=b.owner
      AND a.dimension_name=b.dimension_name
      AND a.owner=c.owner
      AND a.dimension_name=c.dimension_name
      AND b.hierarchy_name=c.hierarchy_name
ORDER BY
      a.owner,
      a.dimension_name,
      b.hierarchy_name;
SPOOL OFF
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON

Listing 10.37     Example of output from database dimension hierarchy Report

The database dimension hierarchy report should be reviewed to ensure that the hierarchies are using the proper level assignments and that the parent-child relationships make sense (that is, they aren't inverted).

SOURCE 10.40 Example of database dimension attribute report.

rem
rem NAME: dim_attribute.sql
rem FUNCTION: Generate a lit of all Dimensions and atrributes in the
rem database for a specific user or all users
rem HISTORY: MRA 5/12/98 Created
rem
COLUMN owner             FORMAT a8   HEADING 'Owner'
COLUMN dimension_name    FORMAT a10  HEADING 'Dimension|Name'
COLUMN column_name       FORMAT a20  HEADING 'Column|Name'
COLUMN level_name        FORMAT a20  HEADING 'Level|Name'
COLUMN inferred          FORMAT a10  HEADING 'Inferred'
BREAK ON owner ON level_name
SET PAGES 58 LINES 78 FEEDBACK OFF VERIFY OFF
START title80 'Database OPERATOR Report'
SPOOL rep_out\&db\dim_attribute.lis
SELECT
     a.owner,
     a.dimension_name,
     b.level_name,
     c.column_name,
     c.inferred
FROM
     Dba_dimensions a, dba_dim_levels b, dba_dim_attributes c
WHERE
          a.owner LIKE '%&owner%'
      AND a.owner=b.owner
      AND a.dimension_name=b.dimension_name
      AND a.owner=c.owner
      AND a.dimension_name=c.dimension_name
      AND b.level_name=c.level_name
ORDER BY
      a.owner,
      a.dimension_name,
      b.level_name;
SPOOL OFF
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON

LISTING 10.38 Example of output from database dimension attribute report.

Date: 05/13/99                                              Page:   1
Time: 11:34 PM            Database OPERATOR Report             SYSTEM
                              ORTEST1 database                                                                                                        

The database dimension attribute report should be reviewed to be sure that the proper attributes are being ascribed to the proper levels.

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