Help Center/ GaussDB/ API Reference/ APIs (Recommended)/ SQL Explorer/ Querying Full SQL Statistics of an Instance

Updated on 2025-10-22 GMT+08:00

View PDF

Querying Full SQL Statistics of an Instance

Function

This API is used to query full SQL statistics of a GaussDB instance. The statistics include the total number of SQL records under each unique SQL ID and the average value of related time metrics. Enhanced condition-based filtering is supported. For example, multiple conditions can be combined for SQL text query (using the query field), and the total_sql_time and sql_count fields can be used for filtering based on conditions such as greater than, less than, and range. Before calling this API:

Learn how to authenticate this API.
Obtain the required region and endpoint.

Debugging

You can debug this API in API Explorer.

URI

POST /v3/{project_id}/instances/{instance_id}/list-full-sql-statistics

**Table 1** Parameter description
Parameter	Mandatory	Description
project_id	Yes	Definition Project ID of a tenant in a region. To obtain the value, see Obtaining a Project ID. Constraints N/A Range The value can contain 32 characters. Only letters and digits are allowed. Default Value N/A
instance_id	Yes	Definition Instance ID, which uniquely identifies an instance. Constraints N/A Range The value can contain 36 characters. Only letters and digits are allowed. Default Value N/A

Request Parameters

**Table 2** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	Definition User token. You can obtain the token by calling the IAM API used to obtain a user token. After the request is processed, the value of X-Subject-Token in the message header is the token value. Constraints N/A Range N/A Default Value N/A
X-Language	No	String	Definition Language. Constraints N/A Range zh-cn en-us Default Value en-us

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
node_id	No	String	Definition Node ID. Constraints N/A Range N/A Default Value N/A
component_id	No	String	Definition Component ID. Constraints N/A Range N/A Default Value N/A
begin_time	Yes	String	Definition Query start time. Constraints The value must use the format yyyy-mm-ddThh:mm:ssZ and conform to the ISO 8601 UTC standard. T is the separator between the calendar and the hourly notation of time. Z indicates the time zone offset. The plus sign (+) in the time zone must be URL-encoded to %2B, and the minus sign (-) in the time zone does not need to be encoded. For example, in the Beijing time zone, the time zone offset is shown as +0800 and the value (2024-03-15T17:20:33+0800) of begin_time needs to be encoded as 2024-03-15T17:20:33%2B0800. Range The time range from begin_time to end_time cannot exceed 30 days. Default Value N/A
end_time	Yes	String	Definition Query end time. Constraints The value must use the format yyyy-mm-ddThh:mm:ssZ and conform to the ISO 8601 UTC standard. T is the separator between the calendar and the hourly notation of time. Z indicates the time zone offset. The plus sign (+) in the time zone must be URL-encoded to %2B, and the minus sign (-) in the time zone does not need to be encoded. For example, in the Beijing time zone, the time zone offset is shown as +0800 and the value (2024-03-16T17:20:33+0800) of end_time needs to be encoded as 2024-03-16T17:20:33%2B0800. Range The time range from begin_time to end_time cannot exceed 30 days. Default Value N/A
query	No	String	Definition SQL text. Constraints N/A Range The value can contain 1 to 4096 characters. Default Value N/A
sql_id	No	String	Definition Normalized SQL ID. Constraints N/A Range N/A Default Value N/A
sql_exec_id	No	String	Definition Unique SQL statement ID. Constraints N/A Range N/A Default Value N/A
transaction_id	No	String	Definition Transaction ID. Constraints N/A Range N/A Default Value N/A
trace_id	No	String	Definition Link ID. Constraints N/A Range N/A Default Value N/A
db_name	No	String	Definition Database name. Constraints N/A Range N/A Default Value N/A
schema_name	No	String	Definition Schema name. Constraints N/A Range N/A Default Value N/A
username	No	String	Definition Username. Constraints N/A Range N/A Default Value N/A
client_addr	No	String	Definition Client address. Constraints N/A Range N/A Default Value N/A
client_port	No	String	Definition Client port. Constraints N/A Range N/A Default Value N/A
order_by	No	String	Definition Sorting field. Constraints N/A Range sql_id sql_count avg_db_time avg_cpu_time avg_execution_time avg_data_io_time start_time_stamp Default Value sql_count
is_slow_sql	No	Boolean	Definition Whether the SQL statement is slow. Constraints N/A Range true: The SQL statement is slow. false: The SQL statement is not slow. Default Value N/A
order	No	String	Definition Sorting mode, which can be ascending or descending. Constraints N/A Range DESC: descending order ASC: ascending order Default Value DESC
application_name	No	String	Definition Application name. Constraints N/A Range N/A Default Value N/A
multi_queries	No	Array of MultiMergeCondition	Definition Query conditions for field aggregation. For details, see Table 4. Constraints Only full AND or full OR queries can be performed on the query field.
compare_conditions	No	Array of CompareCondition	Definition Combined comparison query condition. You can combine conditions such as range, greater than, and less than for a specified filter field. For details, see Table 5. Constraints N/A
limit	No	Long	Definition Number of records returned by a query. For example, if this parameter is set to 10, a maximum of 10 records can be displayed. Constraints N/A Range [1, 1000] Default Value 10
offset	No	Long	Definition Index offset. The query starts from the next piece of data indexed by this parameter. For example, if this parameter is set to 0 and limit is set to 10, only the 1st to 10th records are displayed. Constraints N/A Range [0, 9223372036854774807] Default Value 0: The query starts from the first data record.

**Table 4** MultiMergeCondition
Parameter	Type	Description
name	String	Definition Name of the field to be queried. Constraints Only the character string query is supported. Range 1 to 128 characters, including letters (case-sensitive), digits, and underscores (_) Default Value N/A
condition	String	Definition Combined condition. Constraints N/A Range The value can only be and, or, AND or OR. Default Value N/A
values	Array of strings	Definition Set of multiple search criteria. The value is a list of 1 to 5 characters. Constraints N/A
is_fuzzy	Boolean	Definition Set of multiple search criteria. Constraints The value can only be true for fuzzy query. Range true: fuzzy search false: exact match Default Value true

**Table 5** CompareCondition
Parameter	Mandatory	Type	Description
name	Yes	String	Definition Query field name. Currently, only specified numeric fields are supported. Constraints N/A Range total_sql_time sql_count Default Value N/A
enable_equal	No	Boolean	Definition Whether to include the Equal to condition. If this parameter value is set to true, the boundary values (for min and max) are included in the range condition. Constraints N/A Range true false Default Value true
min	No	Long	Definition Value for evaluating the minimum threshold (greater-than condition). Constraints N/A Range [0, 2^63-1] Default Value N/A
max	No	Long	Definition Value for evaluating the maximum threshold (less-than condition). Constraints N/A Range [0, 2^63-1] Default Value N/A
value	No	Long	Definition Value for evaluating the equality threshold (equal-to condition). The value parameter has the highest priority. If value is not left blank, the settings for min and max are ignored. If value is left blank, the min and max filtering conditions are enabled. Constraints N/A Range [0, 2^63-1] Default Value N/A

Response Parameters

**Table 6** Response body parameters
Parameter	Type	Description
statistics	Array of objects	Definition Full SQL statistics. For details, see Table 7.
total_count	Integer	Definition Total number of full SQL statistics. Range N/A

**Table 7** FullSqlStatistics
Parameter	Type	Description
template	String	Definition SQL template. When the kernel GUC parameter track_stmt_parameter is disabled, the normalized SQL template content is displayed; when this parameter is enabled, a randomly selected SQL record is displayed, with the parameter list truncated. Range N/A
sql_id	String	Definition Normalized SQL ID. Range N/A
sql_count	Long	Definition Total number of SQL statements. Range N/A
total_sql_time	Long	Definition Total SQL elapsed time (μs). Range N/A
avg_sql_time	Long	Definition Average SQL elapsed time (μs). Range N/A
total_db_time	Long	Definition Total valid DB time (μs). Range N/A
avg_db_time	Long	Definition Average valid DB time (μs). Range N/A
total_cpu_time	Long	Definition Total CPU time (μs). Range N/A
avg_cpu_time	Long	Definition Average CPU time (μs). Range N/A
avg_execution_time	Long	Definition Average execution time in the SQL executor (μs). Range N/A
avg_parse_time	Long	Definition Average interpreter time (μs). Range N/A
avg_plan_time	Long	Definition Average execution plan time (μs). Range N/A
total_data_io_time	Long	Definition Total I/O time (μs). Range N/A
avg_data_io_time	Long	Definition Average I/O time (μs). Range N/A
avg_n_blocks_hit	Long	Definition Average number of buffer block hits. Range N/A
avg_n_returned_rows	Long	Definition Average rows returned. Range N/A
avg_n_tuples_fetched	Long	Definition Average rows scanned. Range N/A
start_time_stamp	Long	Definition Start timestamp. Range N/A
end_time_stamp	Long	Definition End timestamp. Range N/A

Example Request

Querying the full SQL statistics

POST https://gaussdb-opengauss.ap-southeast-1.myhuaweicloud.com/v3/3d39c18788b54a919bab633874c159dfin14/instances/3d39c18788b54a919bab633874c159dfin01list-full-sql-statistics
{
    "offset": 0,
    "limit": 10,
    "begin_time": "2025-08-06T07:32:24+0000",
    "end_time": "2025-08-06T08:32:24+0000",
    "compare_conditions": [
        {
            "name": "sql_count",
            "min": 1,
            "max": 100
        },
        {
            "name": "total_sql_time",
            "max": 1000
        }
    ],
    "query": "select name $1 from table",
    "sql_exec_id": "72902018970165236",
    "transaction_id": "0",
    "trace_id": "d3eef567-4be6-424c-860f-75684457bf72",
    "order_by": "sql_count",
    "order": "DESC",
    "db_name": "postgres",
    "schema_name": "public",
    "username": "rdsAdmin",
    "node_id": "672cd018568d40b980ce7bf345ebd5a7no14",
    "component_id": "cn_5001",
    "application_name": "gsql",
    "is_slow_sql": true,
    "client_addr": "127.0.0.1",
    "client_port": "58958",  
    "sql_id": "1790862420",
    "multi_queries": [
        {
            "name": "query",
            "condition": "OR",
            "is_fuzzy": true,
            "values": [
                "select"
            ]
        }
    ]
}

Example Response

{
    "total_count": 1,
    "statistics": [
        {
            "template": "SELECT pgxc_node_str(), phyrds, phywrts FROM dbe_perf.local_rel_iostat",
            "sql_id": "1790862420",
            "sql_count": 1,
            "total_sql_time": 489,
            "avg_sql_time": 489,
            "total_db_time": 489,
            "avg_db_time": 489,
            "total_cpu_time": null,
            "avg_cpu_time": null,
            "avg_execution_time": null,
            "avg_parse_time": null,
            "avg_plan_time": null,
            "total_data_io_time": null,
            "avg_data_io_time": null,
            "avg_n_blocks_hit": null,
            "avg_n_returned_rows": null,
            "avg_n_tuples_fetched": null,
            "start_time_stamp": 1754469124297,
            "end_time_stamp": 1754469124297
        }
    ]
}