HomeMogDBMogDB StackUqbar
v3.0

Documentation:v3.0

Supported Versions:

STATEMENT

STATEMENT obtains information about execution statements (unique SQL statements) on the current node. To query this view, you must have the sysadmin permission. You can view all statistics about unique SQL statements received by the primary database node and other database nodes, whereas you can view only the statistics about unique SQL statements executed on other database nodes.

Table 1 STATEMENT columns

Name Type Description
node_name name Database process name
node_id integer Node ID (node_id in pgxc_node)
user_name name Username
user_id oid OID of the user
unique_sql_id bigint ID of the unique SQL statement
query text Unique SQL statement
Note: The length is controlled by track_activity_query_size.
n_calls bigint Number of calls
min_elapse_time bigint Minimum execution time of the SQL statement in the kernel (unit: μs)
max_elapse_time bigint Maximum execution time of the SQL statement in the kernel (unit: μs)
total_elapse_time bigint Total execution time of the SQL statement in the kernel (unit: μs)
n_returned_rows bigint Number of rows in the result set returned by the SELECT statement
n_tuples_fetched bigint Number of rows randomly scanned
n_tuples_returned bigint Number of rows sequentially scanned
n_tuples_inserted bigint Number of rows inserted
n_tuples_updated bigint Number of rows updated
n_tuples_deleted bigint Number of rows deleted
n_blocks_fetched bigint Number of buffer block access times
n_blocks_hit bigint Number of buffer block hits
n_soft_parse bigint Number of soft parsing times. The value of n_soft_parse plus the value of n_hard_parse may be greater than the value of n_calls because the number of subqueries is not counted in the value of n_calls.
n_hard_parse bigint Number of hard parsing times. The value of n_soft_parse plus the value of n_hard_parse may be greater than the value of n_calls because the number of subqueries is not counted in the value of n_calls.
db_time bigint Valid DB time, which is accumulated if multiple threads are involved (unit: μs)
cpu_time bigint CPU time (unit: μs)
execution_time bigint Execution time in the executor (unit: μs)
parse_time bigint SQL parsing time (unit: μs)
plan_time bigint SQL plan generation time (unit: μs)
rewrite_time bigint SQL rewriting time (unit: μs)
pl_execution_time bigint Execution time of PL/pgSQL (unit: μs)
pl_compilation_time bigint Compilation time of PL/pgSQL (unit: μs)
data_io_time bigint I/O time (unit: μs)
net_send_info text Network status of messages sent through a physical connection, including the time (unit: μs), number of calls, and throughput (unit: byte). This column can be used to analyze the network overhead of SQL in a distributed system. This column is not supported in a standalone mode. Example: {"time":xxx, "n_calls":xxx, "size":xxx}.
net_recv_info text Network status of messages received through a physical connection, including the time (unit: μs), number of calls, and throughput (unit: byte). This column can be used to analyze the network overhead of SQL in a distributed system. This column is not supported in a standalone mode. Example: {"time":xxx, "n_calls":xxx, "size":xxx}.
net_stream_send_info text Network status of messages sent through a logical connection, including the time (unit: μs), number of calls, and throughput (unit: byte). This column can be used to analyze the network overhead of SQL in a distributed system. This column is not supported in a standalone mode. Example: {"time":xxx, "n_calls":xxx, "size":xxx}.
net_stream_recv_info text Network status of messages received through a logical connection, including the time (unit: μs), number of calls, and throughput (unit: byte). This column can be used to analyze the network overhead of SQL in a distributed system. This column is not supported in a standalone mode. Example: {"time":xxx, "n_calls":xxx, "size":xxx}.
sort_count bigint Sorting count
sort_time bigint Sorting duration (unit: μs)
sort_mem_used bigint Size of work memory used during sorting (unit: KB)
sort_spill_count bigint Count of file writing when data is flushed to disks during sorting
sort_spill_size bigint File size used when data is flushed to disks during sorting (unit: KB)
hash_count bigint Hashing count
hash_time bigint Hashing duration (unit: μs)
hash_mem_used bigint Size of work memory used during hashing (unit: KB)
hash_spill_count bigint Count of file writing when data is flushed to disks during hashing
hash_spill_size bigint File size used when data is flushed to disks during hashing (unit: KB)

This feature corresponds to the system function get_instr_unique_sql, which is used to store status records of SQL statements that run after the database is started.

General usage syntax:

MogDB=# select * from dbe_perf.statement;

It is mainly controlled by the following parameters:

  • enable_resource_track: tracks resource usage at runtime.

  • instr_unique_sql_count: indicates the total number of SQL statements that can be recorded in the memory. Each time this parameter is modified, all unique SQL statements in the memory are reset.

  • instr_unique_sql_track_type: indicates the unique SQL tracing mode. The value can be top or all. Currently, only top is supported. For stored procedures, only the outermost call is recorded.

  • enable_auto_clean_unique_sql: indicates whether to enable the auto cleanup mechanism of unique SQL. It can automatically clean up 10% of the records randomly when the upper limit is reached. If it is not enabled, error logs will be recorded and SQL related content will not be recorded in memory.

For details, see GUC parameter: Query.

Copyright © 2011-2024 www.enmotech.com All rights reserved.