Home > Monitoring and Tuning Oracl... > Diagnosing Performance Issues
![]() Previous |
![]() Next |
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 Enterprise Manager Cloud Control is a general database management and administration tool. In addition to facilitating basic tasks like adding users and modifying datafiles, Cloud Control presents a graphic overview of the current status of a database. It also provides an interface to troubleshooting and performance tuning utilities.
Automatic Workload Repository collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and saves session information.
Automatic Database Diagnostic Monitor watches database performance statistics to identify bottlenecks, analyze SQL statements, and offer suggestions to improve performance.
SQL Developer provides many reports for monitoring performance issues, as described in "Accessing the Reports in SQL Developer."
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.
SQL Developer provides numerous reports that access the system views described in this section.
To access reports in SQL Developer:
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.
Expand the OLAP DBA Reports folder.
For performance information, expand the Performance (System) folder and select a report.
To monitor OLAP objects, under OLAP DBA Reports, expand the OLAP Dictionary Reports folder. Most reports are categorized under Cubes and Cube Dimensions.
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.
OLAP Dynamic Performance Views
View | Description |
---|---|
|
Lists the aggregation operators available in analytic workspaces. |
|
Lists the allocation operators available in analytic workspaces. |
|
Collects information about the use of cache space and the status of dynamic aggregation. |
|
Collects status information about SQL fetches. |
|
Collects information about each active session. |
|
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 |
---|---|
|
Displays log file information from the control file. |
|
Contains information about redo log files. |
|
Provides PGA memory usage statistics and statistics about the automatic PGA memory manager when |
|
Displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache. |
|
Lists system statistics. |
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."
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
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
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
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.