WSR Report ​

What Is a WSR Report ​

Workload Statistics Report (WSR) is a professional reporting tool dedicated to database performance analysis, and its functional positioning is similar to that of the AWR report in Oracle databases. The report contains performance statistics that help developers and maintenance personnel quickly locate performance issues.

A WSR report periodically generates snapshots (the default snapshot interval is 30 minutes; you can modify the interval or manually create a snapshot), and generates report data based on the information collected between two snapshots.

Steps for Generating a WSR Report ​

Execute the following command to query the operations related to generating a WSR report.

```sql

SQL> WSR

The syntax of generating a WSR report is as follows: 

    Format:  WSR snap_id1 snap_id2 "FILENAME" [ shard ] 
    Format:  WSR starttime endtime "FILENAME" 
            snap_id1 and snap_id2 indicate the IDs of the start and end snapshots, respectively. FILENAME is optional.
            If no snapshot is generated, you can specify the start time and end time to generate the report. The time format is yyyy-mm-dd hh24:mi:ss.
            You can use the command with shard to collect the WSR information of cluster, but only support on CN node.
            You can create a snapshot using the SYS.WSR$CREATE_SNAPSHOT stored procedure and obtain snapshot IDs from the adm_hist_snapshot system view.
            You can also create a global snapshot to the whole cluster through the WSR CREATE_GLOBAL_SNAPSHOT command, but only support on CN node.
            You can drop snapshots using the WSR$DROP_SNAPSHOT_RANGE stored procedure and obtain the latest 20 snapshot IDs by running the WSR list command.
    Example1: WSR 10 20
    Use snapshot 10 and snapshot 20 to generate a report, with a default report name.
    Example2: WSR 10 20 "e:\wsr.html"
    Use snapshot 10 and snapshot 20 to generate a report, with a specified report name.
    Example3: WSR list
    Obtain information about the latest 20 snapshots.
    Example4: WSR list 50
    Obtain information about the latest 50 snapshots.
    Example5: CALL WSR$CREATE_SNAPSHOT;
    Create a snapshot.
    Example6: CALL WSR$DROP_SNAPSHOT_RANGE(10, 20);
    Drop snapshots from snapshot 10 to snapshot 20.
    Example7: WSR CREATE_GLOBAL_SNAPSHOT
    Create a global snapshot.
    Example8: wsr "2021-06-16 10:00:00" "2021-06-16 10:10:00"
    Specify the start time and end time to generate the report.
Note: For WSR, the values of the SQL_STAT and TIMED_STATS system parameters are true.

```

These commands can be divided into four categories: report generation and management, and snapshot creation and deletion.

Command Details ​

Category 1: Generating a Performance Report ​

1. WSR snap_id1 snap_id2

   Meaning: Generates a WSR performance report using two specified snapshot IDs (snap_id1 as the start and snap_id2 as the end).

   The report file name is automatically generated by the system. This is the most basic report generation command and is used when a custom report file name is not required.

2. WSR snap_id1 snap_id2 "FILENAME"

   Meaning: Uses two specified snapshot IDs to generate a WSR performance report, and customizes the report file name and save path.

   A path can be specified (such as "e:\wsr.html") to save the report to a specific location.

3. WSR starttime endtime

   Meaning: Directly specifies a time range (instead of snapshot IDs) to generate a performance report.

   The time format must be "yyyy-mm-dd hh24:mi:ss". This is used when a very specific time period needs to be analyzed (such as a known app lag period) but no precise snapshot corresponds to that time period.

Category 2: Viewing the Snapshot List ​

4. WSR list

   Meaning: Lists information about the latest 20 snapshots.

   Quickly view the recently available snapshots so that you can select the correct snapshot ID to generate a report.

5. WSR list 50

   Meaning: Lists the latest snapshot information for the specified number (50 here).

   Use this command to extend the list when you need to view earlier historical snapshots.

Category 3: Creating Snapshots ​

6. CALL WSR$CREATE_SNAPSHOT;

   Meaning: Manually creates a performance snapshot immediately.

   Before performing performance analysis (for example, before a stress test), or when the app suddenly slows down, manually create a snapshot for comparison with the subsequent situation.

7. WSR CREATE_GLOBAL_SNAPSHOT

   Meaning: In a cluster environment, creates a global snapshot to collect performance data from all nodes in the entire cluster.

   It can be executed only on the coordinator node (CN) and is used to analyze the overall performance of distributed or cluster databases.

Category 4: Deleting Snapshots ​

8. CALL WSR$DROP_SNAPSHOT_RANGE(10, 20);

   Deletes snapshots within a specified range (this example deletes all snapshots from snapshot ID 10 to 20), cleaning up historical snapshot data that is no longer needed and freeing storage space. Exercise caution when performing this operation, because deleted data cannot be restored.

WSR Configuration Information ​