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, andV$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:
time modelupdates the database time (DB time) consumed by all sessions in seconds. This is an accumulated value, representing the total database time for all sessions within a given time period. The calculation formula is:DB time = elapse time - idle wait time = cpu time + non-idle wait time.Note
Parameters in the DB time calculation formula:
elapse time: The time elapsed from the start of the operation until now.idle wait time: The time the session spent in idle wait.cpu time: The time spent executing operations on the CPU.non-idle wait time: The time the session spent in non-idle wait.
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.