Purpose
The GV$SYSSTAT view displays tenant-level statistical events on all OBServer nodes.
Note
To use this view, you need to enable monitoring by setting the enable_perf_event parameter to True.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | The ID of the tenant. |
| SVR_IP | varchar(46) | NO | The IP address of the server where the information is located. |
| SVR_PORT | bigint(20) | NO | The port number of the server where the information is located. |
| STATISTICS# | bigint(20) | NO | The index of the statistical event. |
| NAME | varchar(64) | NO | The name of the statistical event. |
| CLASS | bigint(20) | NO | The class to which the statistical event belongs. |
| VALUE | bigint(20) | NO | The value of the statistical item. |
| VALUE_TYPE | varchar(16) | NO | The type of the statistical value:
|
| STAT_ID | bigint(20) | NO | The ID of the statistical event. |
Usage
This view shows the cumulative sum of each field at the tenant level. OCP uses this view as the data source for displayed information. Therefore, in performance tuning scenarios, if you have deployed OCP, we recommend that you use OCP to view the information, which is more intuitive. Otherwise, for each statistical metric, you must manually write a script to process the increment value at a time interval (such as 5s) to analyze the change per second.
The following are key performance-related monitoring metrics:
obclient> SELECT /*+read_consistency(weak)*/ NAME, VALUE FROM GV$SYSSTAT WHERE SVR_IP = 'xxx' AND STAT_ID IN (10000, 10001, 10002, 10003, 10005, 10006, 140002, 140003, 40006, 40007, 40008, 40009, 40010, 40011, 40012, 50000, 50001, 50008, 50009, 60000, 60001, 60002, 60003, 60004, 60005, 60019, 60020, 60021, 60022, 60023, 60024, 80057) and (CON_ID > 1000);
+--------------------------------+--------------+
| NAME | VALUE |
+--------------------------------+--------------+
| rpc packet in | 160812 |
| rpc packet in bytes | 49059218 |
| rpc packet out | 160812 |
| rpc packet out bytes | 36409912 |
| rpc net delay | 320766513 |
| rpc net frame delay | 189653 |
| request dequeue count | 24344130 |
| request queue time | 274719157 |
| sql select count | 5 |
| sql select time | 508 |
| sql insert count | 24182271 |
| sql insert time | 3637065124 |
| sql replace count | 0 |
| sql replace time | 0 |
| sql update count | 0 |
| sql update time | 0 |
| sql delete count | 0 |
| sql delete time | 0 |
| sql local count | 24182830 |
| sql remote count | 0 |
| sql distributed count | 0 |
| row cache hit | 136 |
| row cache miss | 180 |
| block cache hit | 4204 |
| block cache miss | 8 |
| io read count | 10 |
| io read delay | 1654 |
| io read bytes | 61440 |
| io write count | 0 |
| io write delay | 0 |
| io write bytes | 0 |
| clog write count | 0 |
| clog write time | 0 |
+--------------------------------+--------------+
By analyzing the monitoring metrics sql insert time and sql insert count, you can obtain the accumulative sum in 5s. The average response time in 5s is Sum{sql insert time}/Sum{sql insert count}.