This topic describes the views of PolarDB O Edition. You can query views to obtain information about SQL statements. This way, you can identify performance issues.

SQL

To obtain information about SQL statements, you can query the views that are described in this topic

  • polar_stat_sql
    Note To use the polar_stat_sql view, you must create the polar_stat_sql plug-in. To create the plug-in, execute the create extension polar_stat_sql statement.
    The following table describes the parameters in this view.
    Parameter Type Description
    queryid bigint The ID of the query.
    datname name The name of the database.
    rolname name The username.
    user_time double The period when the user mode is used.
    system_time double The period when the system mode is used.
    minflts bigint The number of recycled pages or minor faults.
    majflts bigint The number of major page faults.
    nswaps bigint The number of page swaps.
    reads bigint The number of bytes read from disks.
    reads_blks bigint The number of blocks read from disks.
    writes bigint The number of bytes written to disks.
    writes_blks bigint The number of blocks written to disks.
    msgsnds bigint The number of times that IPC messages are sent.
    msgrcvs bigint The number of times that IPC messages are received.
    nsignals bigint The number of times that semaphore is received.
    nvcsws bigint The number of voluntary context switches.
    nivcsws bigint The number of involuntary context switches.
    scan_rows double The number of rows read by the nodes on which scans are performed.
    scan_time double The time when the nodes on which scans are performed execute the SQL statement.
    scan_count bigint The number of times that the nodes on which scans are performed execute the SQL statement.
    join_rows double The number of rows read by the nodes on which joins are performed.
    join_time double The time when the nodes on which joins are performed execute the SQL statement.
    join_count bigint The number of times that the nodes on which joins are performed execute the SQL statement.
    sort_rows double The number of rows read by the nodes on which sorts are performed.
    sort_time double The time when the nodes on which sorts are performed execute the SQL statement.
    sort_count bigint The number of times that the nodes on which sorts are performed execute the SQL statement.
    group_rows double The number of rows read by the nodes on which GROUP BY operations are performed.
    group_time double The time when the nodes on which GROUP BY operations are performed execute the SQL statement.
    group_count bigint The number of times that the nodes on which GROUP BY operations are performed execute the SQL statement.
    hash_rows double The number of rows read by the nodes on which hashes are performed.
    hash_memory bigint The memory used by the hash node. Unit: bytes.
    hash_count bigint The number of times that the nodes on which hashes are performed execute the SQL statement.
    parse_time double The time consumed to parse the SQL statement.
    analyze_time double The time consumed to analyze the SQL statement.
    rewrite_time double The time consumed to rewrite the SQL statement.
    plan_time double The time consumed to generate the execution plan.
    execute_time double The time consumed to execute the SQL statement.
    lwlock_wait double The lwlock wait time.
    rel_lock_wait double The wait time for table locks.
    xact_lock_wait double The wait time for transaction locks.
    page_lock_wait double The wait time for page locks.
    tuple_lock_wait double The wait time for row locks.
    shared_read_ps bigint The read IOPS.
    shared_write_ps bigint The write IOPS.
    shared_read_throughput bigint The read throughput. Unit: bytes.
    shared_write_throughput bigint The write throughput. Unit: bytes.
    shared_read_latency double The read latency. Unit: microseconds.
    shared_write_latency double The write latency. Unit: microseconds.
    io_open_num bigint The number of times that the files are opened.
    io_seek_count bigint The number of seek operations performed on the files.
    io_open_time double The time consumed to open the files. Unit: microseconds.
    io_seek_time double The time consumed to perform seek operations on the files. Unit: microseconds.
  • polar_stat_query_count
    Note To use the polar_stat_query_count view, you must create the polar_stat_sql plug-in. To create the plug-in, execute the create extension polar_stat_sql statement.
    The following table describes the parameters in this view.
    Parameter Type Description
    sqltype text The SQL type. Valid values: DQL, DML, DDL, and DCL.
    cmdtype text The SQL command type. Examples: SELECT, INSERT, and UPDATE.
    count bigint The total number of executions.
  • pg_stat_statements
    Note To use the pg_stat_statements view, you must create the pg_stat_statements plug-in. To create the plug-in, execute the create extension pg_stat_statements statement.
    The following table describes the parameters in this view.
    Parameter Type Description
    userid oid The object identifier (OID) of the user who executed the SQL statement.
    dbid oid The OID of the database in which the SQL statement was executed.
    queryid bigint The internal hash code calculated based on the parse tree of the SQL statement.
    query text The text of the SQL statement.
    calls bigint The number of times that the SQL statement is executed.
    total_time double precision The total time consumed to execute the SQL statement. Unit: milliseconds.
    min_time double precision The shortest time consumed to execute the SQL statement. Unit: milliseconds.
    max_time double precision The longest time consumed to execute the SQL statement. Unit: milliseconds.
    mean_time double precision The average time consumed to execute the SQL statement. Unit: milliseconds.
    stddev_time double precision The population standard deviation of the time consumed to execute the SQL statement. Unit: milliseconds.
    rows bigint The total number of rows retrieved or affected by the SQL statement.
    shared_blks_hit bigint The total number of shared-block cache hits by the SQL statement.
    shared_blks_read bigint The total number of shared blocks read by the SQL statement.
    shared_blks_dirtied bigint The total number of shared blocks dirtied by the SQL statement.
    shared_blks_written bigint The total number of shared blocks written by the SQL statement.
    local_blks_hit bigint The total number of local-block cache hits by the SQL statement.
    local_blks_read bigint The total number of local blocks read by the SQL statement.
    local_blks_dirtied bigint The total number of local blocks dirtied by the SQL statement.
    local_blks_written bigint The total number of local blocks written by the SQL statement.
    temp_blks_read bigint The total number of temporary blocks read by the SQL statement.
    temp_blks_written bigint The total number of temporary blocks written by the SQL statement.
    blk_read_time double precision The total time that the SQL statement spent on reading blocks. Unit: milliseconds.This parameter is valid only if the track_io_timing parameter is set to on. Otherwise, zero is returned.
    blk_write_time double precision The total time that the SQL statement spent on writing blocks. Unit: milliseconds.This parameter is valid only if the track_io_timing parameter is set to on. Otherwise, zero is returned.

Execution plans

You can query the polar_stat_plans view to obtain the information about the execution plans of queries.

polar_stat_plans

This view displays statistics about the execution plans. The following table describes the parameters in this view.
Parameter Type Description
userid oid The ID of the user.
dbid oid The ID of the database.
queryid bigint The ID of the SQL statement.
planid oid The ID of the execution plan.
plan text The execution plan.
calls bigint The number of times that the SQL statement is invoked.
total_time double The total time consumed to execute the SQL statement.
min_time double The shortest time consumed to execute the SQL statement.
max_time double The maximum time consumed to execute the SQL statement.
mean_time double The average time consumed to execute the SQL statement.
stddev_time double The standard deviation of the time consumed to execute the SQL statement.
rows bigint The number of rows retrieved by the SQL statement.
shared_blks_hit bigint The number of cache hits in the shared memory.
shared_blks_read bigint The number of times that the shared memory reads data from disks.
shared_blks_dirtied bigint The number of times that the shared memory is dirtied.
shared_blks_written bigint The number of times that the shared memory writes data to disks.
local_blks_hit bigint The number of cache hits in the local memory.
local_blks_read bigint The number of times that the local memory reads data from disks.
local_blks_dirtied bigint The number of times that the local memory is dirtied.
local_blks_written bigint The number of times that the local memory writes data to disks.
temp_blks_read bigint The number of times that the temporary memory reads data from disks.
temp_blks_written bigint The number of times that the temporary memory writes data to disks.
blk_read_time double The time consumed to read blocks.
blk_write_time double The time consumed to write blocks.
utime double The period during which CPUs run in user mode.
stime double The period during which CPUs run in system mode.
lwlock_wait double The lwlock wait time.
rel_lock_wait double The wait time for table locks.
xact_lock_wait double The wait time for transaction locks.
page_lock_wait double The wait time for page locks.
tuple_lock_wait double The wait time for row locks.
last_startup_cost double The latest startup cost.
last_total_cost double The latest total cost.
shared_read_ps bigint The read IOPS.
shared_write_ps bigint The write IOPS.
shared_read_throughput bigint The read throughput. Unit: bytes.
shared_write_throughput bigint The write throughput. Unit: bytes.
shared_read_latency double The read latency. Unit: microseconds.
shared_write_latency double The write latency. Unit: microseconds.
io_open_num bigint The number of times that the files are opened.
io_seek_count bigint The number of seek operations performed on the files.
io_open_time double The time consumed to open the files. Unit: microseconds.
io_seek_time double The time consumed to perform seek operations on the files. Unit: microseconds.
Note To use the polar_stat_plans view, you must create the polar_stat_plans plug-in. To create the plug-in, execute the create extension polar_stat_plans statement.