Manage WR

OceanBase Database provides a database management system (DBMS) package for you to manage cluster snapshots related to Workload Repository (WR). Specifically, you can manually create snapshots, delete snapshots, and modify the time interval of the timer thread to adjust the sampling frequency for cluster snapshots.

Create a snapshot

WR automatically creates snapshots. To adjust the snapshot collection time, you can manually create a cluster snapshot. Then, the timer will collect data based on the new time interval. The syntax for creating a snapshot is as follows:

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(
    flush_level  VARCHAR(64) DEFAULT 'TYPICAL'
    );

In the syntax, the value of flush_level can be only TYPICAL, which indicates the periodic snapshot mode. This means WR collects most statistical data only in periodic snapshot mode. Here is an example:

obclient [(none)]> CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level => 'TYPICAL');
Query OK, 0 rows affected, 3 warnings (1.112 sec)

Configure a snapshot strategy

Configure the snapshot interval and retention period

You can modify the snapshot interval and retention period to adjust the sampling frequency of cluster snapshots. The syntax is as follows:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention          INT    DEFAULT  NULL,
   interval           INT    DEFAULT  NULL
   );

The following table describes the fields.

Example: Set the snapshot retention period to 2,265 minutes (about 1 day and 12 hours) and the interval between snapshots to 16 minutes.

obclient [(none)]> CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS('2265','16');
Query OK, 0 rows affected (0.006 sec)

View snapshot configurations

• Query the oceanbase.CDB_WR_CONTROL view for WR related configurations in all tenants.

  obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_CONTROL;
  

  The return result is as follows:

    +-----------+---------------+-------------+---------+
    | TENANT_ID | SNAP_INTERVAL | RETENTION   | TOPNSQL |
    +-----------+---------------+-------------+---------+
    |         1 | +0 00:16:00   | +1 13:45:00 |      30 |
    +-----------+---------------+-------------+---------+
    1 row in set (0.018 sec)
  

  The following table describes the fields.

  Field	Description
  TENANT_ID	The ID of the tenant. Other values: a user tenant or meta tenant.
  SNAP_INTERVAL	The time interval between snapshots, in the DD HH:mm:SS format.
  RETENTION	The retention period of snapshots, in the DD HH:mm:SS format.
  TOPNSQL	The top N SQL statements stored.

  For more information about the oceanbase.CDB_WR_CONTROL view, see oceanbase.CDB_WR_CONTROL.

• Query the oceanbase.DBA_WR_CONTROL view in a user tenant for WR related configurations in the current tenant.

  obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_CONTROL;
  

  The return result is as follows:

    +-----------+---------------+-------------+---------+
    | TENANT_ID | SNAP_INTERVAL | RETENTION   | TOPNSQL |
    +-----------+---------------+-------------+---------+
    |         1 | +0 00:16:00   | +1 13:45:00 |      30 |
    +-----------+---------------+-------------+---------+
    1 row in set (0.003 sec)
  

  The following table describes the fields.

  Field	Description
  TENANT_ID	The ID of the tenant. Other values: a user tenant or meta tenant.
  SNAP_INTERVAL	The time interval between snapshots, in the DD HH:mm:SS format.
  RETENTION	The retention period of snapshots, in the DD HH:mm:SS format.
  TOPNSQL	The top N SQL statements stored.

  For more information about the DBA_WR_CONTROL view, see oceanbase.DBA_WR_CONTROL and DBA_WR_CONTROL.

• Query the oceanbase.CDB_WR_SNAPSHOT view for snapshots of all tenants.

  obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SNAPSHOT limit 1;
  

  The return result is as follows:

    +------------+-----------+---------+----------------+----------+----------------------------+----------------------------+-----------+----------------------------+
    | CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP         | SVR_PORT | BEGIN_INTERVAL_TIME        | END_INTERVAL_TIME          | SNAP_FLAG | STARTUP_TIME               |
    +------------+-----------+---------+----------------+----------+----------------------------+----------------------------+-----------+----------------------------+
    |       4000 |         1 |     114 | xx.xx.xx.xx |     2882 | 2023-09-22 01:20:58.332785 | 2023-09-22 02:20:58.333136 |         0 | 2023-09-21 16:21:41.723234 |
    +------------+-----------+---------+----------------+----------+----------------------------+----------------------------+-----------+----------------------------+
    1 row in set (0.004 sec)
  

  The following table describes the fields.

  Field	Description
  CLUSTER_ID	The ID of the cluster.
  TENANT_ID	The ID of the tenant.
  SNAP_ID	The ID of the snapshot.
  SVR_IP	The IP address of the server.
  SVR_PORT	The port of the server.
  BEGIN_INTERVAL_TIME	The start time of the snapshot. The value is a timestamp accurate to milliseconds.
  END_INTERVAL_TIME	The end time of the snapshot. The value is a timestamp accurate to milliseconds.
  SNAP_FLAG	The reason for triggering the snapshot.
  STARTUP_TIME	The time when the process was started. The value is a timestamp accurate to milliseconds.

  For more information about the oceanbase.CDB_WR_SNAPSHOT view, see oceanbase.CDB_WR_SNAPSHOT.

• Query the oceanbase.DBA_WR_SNAPSHOT view for snapshots of the current tenant.

  obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SNAPSHOT limit 1;
  

  The return result is as follows:

    +------------+-----------+---------+----------------+----------+----------------------------+----------------------------+-----------+----------------------------+
    | CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP         | SVR_PORT | BEGIN_INTERVAL_TIME        | END_INTERVAL_TIME          | SNAP_FLAG | STARTUP_TIME               |
    +------------+-----------+---------+----------------+----------+----------------------------+----------------------------+-----------+----------------------------+
    |       4000 |         1 |     114 | xx.xx.xx.xx |     2882 | 2023-09-22 01:20:58.332785 | 2023-09-22 02:20:58.333136 |         0 | 2023-09-21 16:21:41.723234 |
    +------------+-----------+---------+----------------+----------+----------------------------+----------------------------+-----------+----------------------------+
    1 row in set (0.004 sec)
  

  The following table describes the fields.

  Field	Description
  CLUSTER_ID	The ID of the cluster.
  TENANT_ID	The ID of the tenant.
  SNAP_ID	The ID of the snapshot.
  SVR_IP	The IP address of the server.
  SVR_PORT	The port of the server.
  BEGIN_INTERVAL_TIME	The start time of the snapshot. The value is a timestamp accurate to milliseconds.
  END_INTERVAL_TIME	The end time of the snapshot. The value is a timestamp accurate to milliseconds.
  SNAP_FLAG	The reason for triggering the snapshot.
  STARTUP_TIME	The time when the process was started. The value is a timestamp accurate to milliseconds.

  For more information about the DBA_WR_SNAPSHOT view, see oceanbase.DBA_WR_SNAPSHOT and DBA_WR_SNAPSHOT.

Delete snapshots

You can specify the start ID and end ID of snapshots to delete the snapshot data within the specified ID range. The syntax is as follows:

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
    low_snap_id    INT,
    high_snap_id   INT
    );

The following table describes the fields.

The snapshot deletion procedure is as follows:

1. Query all snapshot IDs in the tenant.

   obclient [oceanbase]> select count(1),min(snap_id), max(snap_id) from oceanbase.DBA_WR_SNAPSHOT;
   

   The return result is as follows:

   +-----------+--------------+--------------+
   | count(1) | min(snap_id) | max(snap_id) |
   +-----------+--------------+--------------+
   |         1 |            2 |            2 |
   +-----------+--------------+--------------+
   1 row in set (0.039 sec)
   

2. Delete the snapshot data within the specified ID range.

   obclient [oceanbase]> CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE('2','2');
   Query OK, 0 rows affected (1.018 sec)
   

3. Query whether the snapshots are successfully deleted.

   obclient [oceanbase]> select count(1),min(snap_id), max(snap_id) from oceanbase.DBA_WR_SNAPSHOT;
   

References

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