Skip Headers
Previous
Previous
 
Next
Next

Diagnosing Performance Issues

Oracle Database provides various tools to help you diagnose performance problems. As an Oracle DBA, you may find these tools useful in tuning the database:

Oracle Database also provides system views to help you diagnose performance problems. The following topics identify views that are either specific to OLAP or provide database information that is pertinent to OLAP.

Accessing the Reports in SQL Developer

SQL Developer provides numerous reports that access the system views described in this section.

To access reports in SQL Developer: 

  1. In the reports navigator, expand the OLAP Reports folder. The reports are grouped into these folders:

    • OLAP Build Reports: Provides information about current and past builds.

    • OLAP DBA Reports: Provides system monitoring and diagnostic information.

    • OLAP Dictionary Reports: Queries the data dictionary for information about dimensional objects.

  2. Expand the OLAP DBA Reports folder.

  3. For performance information, expand the Performance (System) folder and select a report.

  4. To monitor OLAP objects, under OLAP DBA Reports, expand the OLAP Dictionary Reports folder. Most reports are categorized under Cubes and Cube Dimensions.

Dynamic Performance Views

Each Oracle Database instance maintains fixed tables that record current database activity. These tables collect data on internal disk structures and memory structures. Among them are tables that collect data on Oracle OLAP.

These tables are available to users through a set of dynamic performance views. By monitoring these views, you can detect usage trends and diagnose system bottlenecks. Table: OLAP Dynamic Performance Views provides a brief description of each view. Global dynamic performance views (GV$) are also provided.


See Also:

Oracle Database Reference for full descriptions of the OLAP dynamic performance views.

OLAP Dynamic Performance Views

View Description

V$AW_AGGREGATE_OP

Lists the aggregation operators available in analytic workspaces.

V$AW_ALLOCATE_OP

Lists the allocation operators available in analytic workspaces.

V$AW_CALC

Collects information about the use of cache space and the status of dynamic aggregation.

V$AW_LONGOPS

Collects status information about SQL fetches.

V$AW_SESSION_INFO

Collects information about each active session.

V$AW_OLAP

Collects information about the status of active analytic workspaces.


Table: Selected Database Performance Views describes some other dynamic performance views that are not specific to OLAP, but which you may want to use when tuning your database for OLAP.

Selected Database Performance Views

View Description

V$LOG

Displays log file information from the control file.

V$LOGFILE

Contains information about redo log files.

V$PGASTAT

Provides PGA memory usage statistics and statistics about the automatic PGA memory manager when PGA_AGGREGATE_TARGET is set.

V$ROWCACHE

Displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.

V$SYSSTAT

Lists system statistics.


Basic Queries for Monitoring the OLAP Option

The following queries extract OLAP information from the data dictionary. You must have a privileged account to query the DBA views.

You can also monitor the OLAP option using the reports provided in SQL Developer. See "Accessing the Reports in SQL Developer."

Is the OLAP Option Installed in the Database?

The OLAP option is provided with Oracle Database Enterprise Edition. To verify that the OLAP components have been installed, issue this SQL command:

SELECT comp_name, version, status FROM DBA_REGISTRY 
     WHERE comp_name LIKE '%OLAP%';

COMP_NAME                VERSION                        STATUS     
------------------------ ------------------------------ -----------
OLAP Analytic Workspace  12.1.0.2.0                     VALID      
Oracle OLAP API          12.1.0.2.0                     VALID      

What Analytic Workspaces Are in the Database?

The DBA_AWS view provides information about all analytic workspaces. Use the following SQL command to get a list of names, their owners, and the version:

SELECT owner, aw_name, aw_version FROM DBA_AWS;
 
OWNER      AW_NAME                        AW_VERSION
---------- ------------------------------ ----------
SYS        EXPRESS                        12.0
GLOBAL     GLOBAL                         12.0
SYS        AWCREATE                       12.0
SYS        AWMD                           12.0
SYS        AWXML                          12.0
SYS        AWREPORT                       12.0
SYS        AWCREATE10G                    12.0

How Big Is the Analytic Workspace?

To find out the size in bytes of the tablespace extents for a particular analytic workspace, use the following SQL statements, replacing GLOBAL with the name of your analytic workspace.

SELECT extnum, SUM(dbms_lob.getlength(awlob)) bytes FROM global.aw$global
     GROUP BY extnum;
 
    EXTNUM      BYTES
---------- ----------
         0  191776956

To see the size of the LOB table containing an analytic workspace, use a SQL command like the following, replacing GLOBAL.AW$GLOBAL with the qualified name of your analytic workspace.

SELECT ROUND(SUM(dbms_lob.getlength(awlob))/1024,0) kb 
     FROM global.aw$global;
 
        KB
----------
    187282

When Were the Analytic Workspaces Created?

The DBA_OBJECTS view provides the creation date of the objects in your database. The following SQL command generates an easily readable report for analytic workspaces.

SELECT owner, object_name, created, status FROM dba_objects 
      WHERE object_name LIKE 'AW$%' AND object_name!='AW$' 
      GROUP BY owner, object_name, created, status 
      ORDER BY owner, object_name;
 
OWNER           OBJECT_NAME          CREATED   STATUS
--------------- -------------------- --------- -------
GLOBAL          AW$GLOBAL            02-JUL-13 VALID
SYS             AW$AWCREATE          02-JUL-13 VALID
SYS             AW$AWCREATE10G       02-JUL-13 VALID
SYS             AW$AWMD              02-JUL-13 VALID
SYS             AW$AWREPORT          02-JUL-13 VALID
SYS             AW$AWXML             02-JUL-13 VALID
SYS             AW$EXPRESS           02-JUL-13 VALID
 
7 rows selected.