41 DBMS_CUBE_LOG

Using DBMS_CUBE_LOG

DBMS_CUBE_LOG manages several logs. These logs enable you to track the progress of long running processes, then use the results to profile performance characteristics. They provide information to help you diagnose and remedy problems that may occur during development and maintenance of a cube: Hierarchies that are improperly structured in the relational source tables, records that fail to load, or data refreshes that take too long to complete. They also help diagnose performance problems in querying cubes.

Analytic Workspace Manager creates the logs automatically using the default names and types. It also disables the logs when Analytic Workspace Manager is closed. To use the same logs outside of Analytic Workspace Manager, you must first enable them. Alternatively, you can create and manage different logs for use outside of Analytic Workspace Manager.

Logging Types

Several logs are available, each one dedicated to storing messages of a particular type. You may use all of them or only those that you find particularly valuable. The logs and their contents are described later in this topic.

• Cube Build Log

• Cube Dimension Compile Log

• Cube Operations Log

• Cube Rejected Records Log

DBMS_CUBE_LOG provides functions that return the binary integer for each log type. You can produce more readable code by using these functions instead of integers for the argument values of other DBMS_CUBE_LOG procedures and functions. Refer to these descriptions:

• TYPE_BUILD Function

• TYPE_DIMENSION_COMPILE Function

• TYPE_OPERATIONS Function

• TYPE_REJECTED_RECORDS Function

Logging Targets

The TABLE_CREATE procedure creates database tables for storing the logs. Using the ENABLE procedure, you can create additional targets with changes in the destination or logging level. For example, you might target the Cube Operations log to both a table and a disk file. These are the available targets:

• Disk file

• LOB

• Database table

• Trace file

See "ENABLE Procedure" for more information about creating multiple targets.

DBMS_CUBE_LOG provides functions that return the binary integer for each target type. You can produce more readable code by using these functions instead of integers for the argument values of other DBMS_CUBE_LOG procedures and functions. Refer to these descriptions:

• TARGET_FILE Function

• TARGET_LOB Function

• TARGET_TABLE Function

• TARGET_TRACE Function

Verbosity Levels

You can decide how much information is recorded in a log. You may want fewer details when leaving a job to run overnight than when you are monitoring the success of a new build. You can choose from these verbosity levels. Each level adds to the preceding level.

• LOWEST: Logs the status of each command used to build the cube dimensions and cubes, the use of slave processes, and summary records. This is the basic logging level.

• LOW: Logs messages from the OLAP engine, such as start and finish records for SQL Import, Aggregate, and Update.

• MEDIUM: Logs messages at the level used by Analytic Workspace Manager.

• HIGH: Logs messages that provide tuning information, such as composite lengths, partitioning details, object sizes, and aggregation work lists. This level is intended for use by Oracle Field Services.

• HIGHEST: Logs debugging messages and other information typically sent to a trace file. This level is intended for use by Oracle Support Services.

DBMS_CUBE_LOG provides functions that return the binary integer for each verbosity level. You can produce more readable code by using these functions instead of integers for the argument values of other DBMS_CUBE_LOG procedures and functions. Refer to these descriptions:

• LEVEL_LOWEST Function

• LEVEL_LOW Function

• LEVEL_MEDIUM Function

• LEVEL_HIGH Function

• LEVEL_HIGHEST Function

---

Creating Cube Logs

To store logging information in a database table, you must create that table using the TABLE_CREATE procedure. Cube Build logs are always stored in tables. The ENABLE procedure creates the other target types for the other logs.

To create a Cube Build log: 

• Execute the TABLE_CREATE procedure.

The following command creates a Cube Build log with the default name of CUBE_BUILD_LOG:

EXECUTE dbms_cube_log.table_create(dbms_cube_log.type_build);

To create a Cube Dimension Compile log, Cube Operations log, or Cube Rejected Records log with a database table target: 

1. Execute the TABLE_CREATE procedure to create the table.

2. Execute the ENABLE procedure to begin logging.

These commands create and enable a Cube Operations table with the default name of CUBE_OPERATIONS_LOG and the default verbosity level:

EXECUTE dbms_cube_log.table_create(dbms_cube_log.type_operations);
EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations);
 

To create a Cube Dimension Compile log, Cube Operations log, or Cube Rejected Records log with a trace file, disk file, or LOB target: 

• Execute the ENABLE procedure.

This command enables the Cube Rejected Records log, sets verbosity to the lowest level, and directs the output to a disk file named rejects.log in the WORK_DIR database directory:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_rejected_records, -
     dbms_cube_log.target_file, dbms_cube_log.level_lowest, -
     'WORK_DIR/rejects.log');

---

Cube Build Log

The Cube Build log provides information about what happened during a build. Use this log to determine whether the build produced the results you were expecting, and if not, why not.

The contents of the Cube Build log is refreshed continuously during a build. You can query the log at any time to evaluate the progress of the build and to estimate the time to completion.

The default name of the Cube Build log is CUBE_BUILD_LOG. The following table describes its contents.

---

Cube Dimension Compile Log

When solving a cube, OLAP checks the dimension hierarchies to make sure they are valid. Errors that occur during this validation are written to the Cube Dimension Compile log. The checks include:

• Circularity: Hierarchies are defined by parent-child relations among dimension members. Circularity occurs when a dimension member is specified as its own ancestor or descendant.

• Hierarchy type: Hierarchies can be level based or value based. You can define a cube so that only level-based hierarchies are valid, such as a cube materialized view.

• Level options: Level-based hierarchies can be regular, ragged, or skip level. You can define a dimension so that only regular hierarchies are valid, such as a Time dimension.

The default name of the Cube Dimension Compile log is CUBE_DIMENSION_COMPILE. The following table describes its contents.

---

Cube Operations Log

The Cube Operations log contains messages and debugging information for all OLAP engine events. You can track current operations at a very detailed level. Using the SQL_ID column, you can join the Cube Operations log to dynamic performance views such as V$SQL, V$SESSION, and V$SESSION_LONGOPS to see cube operations in the context of other database operations such as I/O Wait and CPU.

The default name of the Cube Operations log is CUBE_OPERATIONS_LOG. The following table describes its contents.

---

Cube Rejected Records Log

The Cube Rejected Records log contains a summary of the loader job and any records that were rejected because they did not meet the expected format.

A single row in the source table may have errors in more than one field. Each field will generates an error in log, resulting in multiple rows with the same rowid in the SOURCE_ROW column.

The default name of the Cube Rejected Records log is CUBE_REJECTED_RECORDS. The following table describes its contents.

Summary of DBMS_CUBE_LOG Subprograms

---

DEFAULT_NAME Function

This function returns the default table names of the various log types.

Syntax

DBMS_CUBE_LOG.DEFAULT_NAME (
           LOG_TYPE       IN   BINARY_INTEGER  DEFAULT TYPE_OPERATIONS)
     RETURN VARCHAR2;

Parameters

Returns

The default table name of the specified log type.

Examples

This example returns the default name of the Cube Operations log:

SELECT dbms_cube_log.default_name FROM dual;
 
DEFAULT_NAME
------------------------------
CUBE_OPERATIONS_LOG

The next example returns the default name of the Cube Rejected Records log:

select dbms_cube_log.default_name(dbms_cube_log.type_rejected_records) -
     "Default Name" from dual;
 
Default Name
-------------------------
CUBE_REJECTED_RECORDS

---

DISABLE Procedure

This procedure turns logging off for the duration of a session, unless logging is explicitly turned on again with the ENABLE procedure.

Syntax

DBMS_CUBE_LOG.DISABLE (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT,
          LOG_TARGET     IN   BINARY_INTEGER  DEFAULT);

Parameters

Example

This command disables the dimension compilation error log table:

EXECUTE dbms_cube_log.disable(dbms_cube_log.type_dimension_compile);

---

ENABLE Procedure

This procedure turns on logging for the duration of a session or until it is turned off using the DISABLE procedure.

The ENABLE procedure also allows you to direct logging to additional output types and to change the amount of detail in the logs. You can enable a log type to each of the log targets. For example, you can enable the Cube Operations log to the trace file, a table, and a file at different verbosity levels, but you cannot enable the Cube Operations log to two files at the same time.

This procedure also checks the format of the logs and updates them if necessary.

Syntax

DBMS_CUBE_LOG.ENABLE (
          LOG_TYPE       IN     BINARY_INTEGER  DEFAULT NULL,
          LOG_TARGET     IN     BINARY_INTEGER  DEFAULT NULL,
          LOG_LEVEL      IN     BINARY_INTEGER  DEFAULT NULL);

DBMS_CUBE_LOG.ENABLE (
          LOG_TYPE       IN     BINARY_INTEGER  DEFAULT NULL,
          LOG_TARGET     IN     BINARY_INTEGER  DEFAULT NULL,
          LOG_LEVEL      IN     BINARY_INTEGER  DEFAULT NULL, 
          LOG_LOCATION   IN     VARCHAR2        DEFAULT NULL);

DBMS_CUBE_LOG.ENABLE (
          LOG_TYPE       IN     BINARY_INTEGER  DEFAULT NULL,
          LOG_TARGET     IN     BINARY_INTEGER  DEFAULT NULL,
          LOG_LEVEL      IN     BINARY_INTEGER  DEFAULT NULL, 
          LOG_LOCATION   IN/OUT CLOB );

Parameters

Examples

The following command enables all cube logs:

EXECUTE dbms_cube_log.enable;

The following PL/SQL procedure sets the log level to LEVEL_LOWEST:

BEGIN
     dbms_cube_log.disable(dbms_cube_log.type_rejected_records);
     dbms_cube_log.enable(dbms_cube_log.type_rejected_records,
          dbms_cube_log.target_table, dbms_cube_log.level_lowest);
END;
/

---

FLUSH Procedure

This procedure forces all buffered messages to be written to the logs. The buffers are flushed automatically throughout a session, but manually flushing them before viewing the logs assures that you can view all of the messages.

Syntax

DBMS_CUBE_LOG.FLUSH ( );

Example

The following example flushes the buffers for all of the logs:

EXECUTE dbms_cube_log.flush;

---

GET_LOG Procedure

This procedure returns the current settings for the level and location of a particular log.

Syntax

DBMS_CUBE_LOG.GET_LOG (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT TYPE_OPERATIONS,
          LOG_TARGET     IN   BINARY_INTEGER  DEFAULT TARGET_TABLE,
          LOG_LEVEL      OUT  BINARY_INTEGER,
          LOG_LOCATION   OUT  VARCHAR2 );

Parameters

Usage Notes

If log_type is not active, then log_level and log_location are null. Use DBMS_CUBE_LOG.ENABLE to activate a log.

Examples

This PL/SQL procedure provides information about the Cube Rejected Records log:

SET serverout ON format wrapped
 
DECLARE 
     myloglevel  binary_integer;
     mylogtarget varchar2(128);
 
BEGIN
     dbms_cube_log.get_log(dbms_cube_log.type_rejected_records,
     dbms_cube_log.target_table, myloglevel, mylogtarget);

     dbms_output.put_line('Log Level: ' || myloglevel);
     dbms_output.put_line('Log Target: ' || mylogtarget);
END;
/

The procedure generates results like the following:

Log Level: 5

Log Target: GLOBAL.CUBE_REJECTED_RECORDS

---

GET_LOG_SPEC Function

This function retrieves a description of all active Cube Operations logs, Cube Rejected Records logs, and Cube Dimension Compile logs.

Syntax

DBMS_CUBE_LOG.GET_LOG_SPEC ( )
     RETURN VARCHAR2;

Returns

The type and target of all active logs.

Usage Notes

You can use the output from this function as the input to SET_LOG_SPEC.

Examples

The following example shows that the Cube Operations log, Cube Rejected Records log, and Cube Dimension Compile log are active. The Cube Operations log is stored in the session trace file and the other logs are stored in tables.

SELECT dbms_cube_log.get_log_spec FROM dual;
 
GET_LOG_SPEC
--------------------------------------------------------------------------------
OPERATIONS(TABLE, TRACE) REJECTED_RECORDS(TABLE[DEBUG])

---

GET_PARAMETER Function

This function returns the current values of the options that control various aspects of logging. To set these options, use the SET_PARAMETER function.

Syntax

DBMS_CUBE_LOG.GET_PARAMETER (
          LOG_TYPE       IN   BINARY_INTEGER,
          LOG_PARAMETER  IN   BINARY_INTEGER )
     RETURN BINARY_INTEGER;

Parameters

Returns

The value of the specified log_parameter.

Examples

This example shows the current maximum number of errors in the Cube Rejected Records log before logging stops. This parameter was previously set with the SET_PARAMETER procedure.

SELECT dbms_cube_log.get_parameter(dbms_cube_log.type_rejected_records, 1) -
     "Maximum Records" FROM dual;
 
Maximum Records
---------------
            100

---

LEVEL_HIGH Function

This function returns the integer value of the high verbosity level.

Syntax

DBMS_CUBE_LOG.LEVEL_HIGH ()
     RETURN BINARY_INTEGER;

Returns

4

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to high:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_high);

---

LEVEL_HIGHEST Function

This function returns the integer value of the highest verbosity level.

Syntax

DBMS_CUBE_LOG.LEVEL_HIGHEST ()
     RETURN BINARY_INTEGER;

Returns

5

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to highest:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_highest);

---

LEVEL_LOW Function

This function returns the integer value of the low verbosity level.

Syntax

DBMS_CUBE_LOG.LEVEL_LOW ()
     RETURN BINARY_INTEGER;

Returns

2

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to low:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_low);

---

LEVEL_LOWEST Function

This function returns the integer value of the lowest verbosity level. This level logs the status of each command used to build the cube dimensions and cubes, the use of slave processes, and summary records.

Syntax

DBMS_CUBE_LOG.LEVEL_LOWEST ()
     RETURN BINARY_INTEGER;

Returns

1

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to lowest:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_lowest);

---

LEVEL_MEDIUM Function

This function returns the integer value of the medium verbosity level.

Syntax

DBMS_CUBE_LOG.LEVEL_MEDIUM ()
     RETURN BINARY_INTEGER;

Returns

3

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Verbosity Levels".

Example

This command sets the verbosity level of the cube operations table to medium:

EXECUTE dbms_cube_log.enable(dbms_cube_log.type_operations, -
     dbms_cube_log.target_table, dbms_cube_log.level_medium);

---

SET_LOG_SPEC Procedure

This procedure sets all logging to the values specified in the input string.

Syntax

DBMS_CUBE_LOG.SET_LOG_SPEC (
          LOG_SPEC       IN   VARCHAR2 );

Parameters

Usage Notes

The GET_LOG_SPEC function returns a properly formatted string for SET_LOG_SPEC.

Examples

This PL/SQL procedure disables all logs, verifies that they are disabled, then activates the Cube Operations log and the Cube Rejected Records log.

BEGIN
     dbms_cube_log.disable;
     dbms_output.put_line('Cube Logs: ' || dbms_cube_log.get_log_spec);

     dbms_cube_log.set_log_spec('OPERATIONS(TRACE) REJECTED_RECORDS(TABLE)');
     dbms_output.put_line('Cube Logs: ' || dbms_cube_log.get_log_spec);
END;
/

The output from the procedure verifies that the DISABLE function de-activated all logs, and the SET_LOG_SPEC function activated two logs:

Cube Logs:

Cube Logs: OPERATIONS(TRACE) REJECTED_RECORDS(TABLE)

---

SET_PARAMETER Procedure

This procedure sets options that control various aspects of logging. To obtain the current value of these options, use the GET_PARAMETER function.

Syntax

DBMS_CUBE_LOG.SET_PARAMETER (
          LOG_TYPE       IN   BINARY_INTEGER,
          LOG_PARAMETER  IN   BINARY_INTEGER,
          VALUE          IN   BINARY_INTEGER );

Parameters

Examples

This PL/SQL procedure sets the two parameters, then uses the GET_PARAMETER function to show the settings:

BEGIN
     dbms_cube_log.set_parameter(dbms_cube_log.type_rejected_records, 1, 150);
     dbms_cube_log.set_parameter(dbms_cube_log.type_rejected_records, 2, 5);

     dbms_output.put_line('Max rejected records: ' ||
        dbms_cube_log.get_parameter(dbms_cube_log.type_rejected_records, 1));

     dbms_output.put_line('Buffer time: ' ||
        dbms_cube_log.get_parameter(dbms_cube_log.type_rejected_records, 2) || 
          ' seconds');
END;
/

The procedure displays this information:

Max rejected records: 150

Buffer time: 5 seconds

---

TABLE_CREATE Procedure

This procedure creates the table targets for the OLAP logs. You must have the CREATE TABLE privilege to use this procedure.

TABLE_CREATE also upgrades existing log tables to the current version while preserving the data.

Syntax

DBMS_CUBE_LOG.TABLE_CREATE (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT,
          TBLNAME        IN   VARCHAR2        DEFAULT );

Parameters

Examples

The

This example creates a Cube Operations log table named CUBE_OPERATIONS_LOG:

EXECUTE dbms_cube_log.table_create;

This example creates a Cube Rejected Records log table named CUBE_REJECTED_RECORDS:

EXECUTE dbms_cube_log.table_create(dbms_cube_log.type_rejected_records);

The next example creates a Cube Build log table named MY_BUILD_LOG:

EXECUTE dbms_cube_log.table_create -
     (dbms_cube_log.type_build, 'MY_BUILD_LOG');

---

TARGET_FILE Function

This function returns the integer value of a file target in DBMS_CUBE_LOG subprograms.

Syntax

DBMS_CUBE_LOG.TARGET_FILE ()
     RETURN BINARY_INTEGER;

Returns

3

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log file:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_file);

---

TARGET_LOB Function

This function returns the integer value of a LOB target.

Syntax

DBMS_CUBE_LOG.TARGET_LOB ()
     RETURN BINARY_INTEGER;

Returns

4

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_LEVEL parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log LOB:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_lob);

---

TARGET_TABLE Function

This function returns the integer value of a table target.

Syntax

DBMS_CUBE_LOG.TARGET_TABLE ()
     RETURN BINARY_INTEGER;

Returns

1

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TARGET parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log table:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_table);

---

TARGET_TRACE Function

This function returns the integer value of a trace file target.

Syntax

DBMS_CUBE_LOG.TARGET_TRACE ()
     RETURN BINARY_INTEGER;

Returns

2

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TARGET parameter in DBMS_CUBE_LOG subprograms. See "Logging Targets".

Example

This command disables the Cube Operations log trace file:

EXECUTE dbms_cube_log.disable -
     (dbms_cube_log.type_operations, dbms_cube_log.target_trace);

---

TYPE_BUILD Function

This function returns the integer value of the Cube Build log.

Syntax

DBMS_CUBE_LOG.TYPE_BUILD ()
     RETURN BINARY_INTEGER;

Returns

4

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Build log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_build) "Log Name" -
     FROM dual;
 
Log Name
-------------------------
CUBE_BUILD_LOG

---

TYPE_DIMENSION_COMPILE Function

This function returns the integer value of the Cube Dimension Compile log.

Syntax

DBMS_CUBE_LOG.TYPE_DIMENSION_COMPILE ()
     RETURN BINARY_INTEGER;

Returns

3

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Dimension Compile log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_dimension_compile) -
     "Log Name" FROM dual;
 
Log Name
-------------------------
CUBE_DIMENSION_COMPILE

---

TYPE_OPERATIONS Function

This function returns the integer value of the Cube Operations log.

Syntax

DBMS_CUBE_LOG.TYPE_OPERATIONS ()
     RETURN BINARY_INTEGER;

Returns

1

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Dimension Compile log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_operations) "Log Name" -
     FROM dual;
 
Log Name
-------------------------
CUBE_OPERATIONS_LOG

---

TYPE_REJECTED_RECORDS Function

This function returns the integer value of the cube Cube Rejected Records log.

Syntax

DBMS_CUBE_LOG.TYPE_REJECTED_RECORDS ()
     RETURN BINARY_INTEGER;

Returns

2

Usage Notes

Use this function instead of its binary integer equivalent for the LOG_TYPE parameter in DBMS_CUBE_LOG subprograms. See "Logging Types".

Example

This query returns the default name of a Cube Rejected Records log:

SELECT dbms_cube_log.default_name(dbms_cube_log.type_rejected_records) -
     "Log Name" FROM dual;
 
Log Name
-------------------------
CUBE_REJECTED_RECORDS

---

VERSION Function

This function returns the version number of a specific log table or the current version number of a specific log type.

Syntax

DBMS_CUBE_LOG.VERSION (
          LOG_TYPE       IN   BINARY_INTEGER  DEFAULT 1,
          TBLNAME        IN   VARCHAR2        DEFAULT NULL)
     RETURN BINARY_INTEGER;

Parameters

Returns

A version number

Examples

This example returns the current version of the Cube Operations log:

SELECT dbms_cube_log.version FROM dual;
 
   VERSION
----------
         2

This example returns the version number of an existing Cube Rejected Records log named CUBE_REJECTED_RECORDS.

SELECT dbms_cube_log.version(dbms_cube_log.type_rejected_records, -
     'CUBE_REJECTED_RECORDS') version FROM dual;
 
   VERSION
----------
         2