Overview

Workload Repository (WR) periodically collects data from performance views of OceanBase Database to help you analyze performance issues.

About WR

WR periodically collects data from related views of OceanBase Database, records the system status, and provides views for querying database performance information. WR can automatically collect data and retain historical performance data for performance monitoring and management. You can use the DBMS_WORKLOAD_REPOSITORY package to adjust WR configurations. For example, you can enable snapshots periodically, configure the snapshot period, and delete snapshots.

Features

WR periodically records performance data of OceanBase Database. Therefore, you can use WR to trace back the historical performance records of OceanBase Database and analyze the performance issues within a specific period. WR provides the following features:

• Automatic data collection: WR periodically collects data from specified performance views, such as V$ACTIVE_SESSION_HISTORY, V$SYSSTAT, and V$STATNAME, processes the data, and then inserts the data into internal tables of the meta tenant. After a cluster is deployed, WR automatically creates snapshots. You can also modify the time of the snapshot timer to manually schedule snapshot creation. By default, WR collects snapshots on an hourly basis.

• Data persistence and cleanup: Collected data is persisted to the disk for retention. WR regularly deletes expired data to avoid occupying excessive storage space. Generally, a deletion operation is triggered every day after a major compaction is completed. By default, snapshot data that has been stored for more than seven days is considered expired.

• Snapshot collection and deletion: WR provides a database management system (DBMS) package for you to manually initiate a collection task by using PL or SQL, or to delete specified WR snapshots.

• Collection frequency adjustment: With the DBMS package, you can modify the time interval of the timer thread to adjust the snapshot collection frequency for WR.

• Low overhead and load: WR consumes a few resources and produces a low overhead during running. Therefore, the system performance is not affected.

• Time model collection: The DB time consumed by all sessions in the database is updated every second. The DB time is a cumulative value calculated by using the following formula: DB time = elapse time - idle wait time = cpu time + non-idle wait time.

  Note

  In the DB time calculation formula:

  • elapse time is the elapsed time from the start of the operation to the present time.
  • idle wait time is the idle wait time of the session.
  • cpu time is the CPU execution time.
  • non-idle wait time is the non-idle wait time of the session.

Related views

WR periodically collects data from the following views of OceanBase Database:

• [G]V$SYSSTAT: displays system statistics that are most widely used and represent the running status of the database.

• V$STATNAME: displays system metrics that can reflect the running status of the database.

• [G]V$ACTIVE_SESSION_HISTORY: displays information about active sessions, which are sampled every second, and records the system running status based on time. WR persists the data at a sampling rate of 10:1.

WR will support more performance views in later versions to display details about database performance, resource utilization, and other key metrics.

References

• Manage WR
• Monitor historical session performance
• Clean up WR data