This topic describes the views of PolarDB for PostgreSQL(Compatible with Oracle). 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.
- pg_stat_statementsThe following table describes the parameters in this view.Note To use the pg_stat_statements view, you must create the
pg_stat_statements
plug-in by executing the create extension pg_stat_statements statement.Parameter Type Description userid
oid The object identifier (OID) of the user who executes the SQL statement. dbid
oid The OID of the database in which the SQL statement is 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 SQL statements that are 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 consumed to read 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 consumed to write blocks. Unit: milliseconds. This parameter is valid only if the track_io_timing parameter is set to on. Otherwise, zero is returned. - polar_stat_sqlThe following table describes the parameters in this view.Note To use the polar_stat_sql view, you must create the
polar_stat_sql
plug-in by executing the create extension polar_stat_sql statement. The polar_stat_sql view can be used as an extension of thepg_stat_statements
view.Parameter Type Description queryid
bigint The ID of a 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 IPC messages that are sent. msgrcvs
bigint The number IPC messages that are received. nsignals
bigint The number of semaphores that are 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 scan node operations. scan_time
double The time consumed to perform scan node operations. scan_count
bigint The number of scan node operations. join_rows
double The number of rows read by join node operations. join_time
double The time consumed to perform join node operations. join_count
bigint The number of join node operations. sort_rows
double The number of rows read by sort node operations. sort_time
double The time consumed to perform sort node operations. sort_count
bigint The number of sort node operations. group_rows
double The number of rows read by group node operations. group_time
double The time consumed to perform group node operations. group_count
bigint The number of group node operations. hash_rows
double The number of rows read by hash node operations. hash_memory
bigint The memory used by hash node operations. Unit: bytes. hash_count
bigint The number of hash node operations. 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 at which the recorded SQL statement was executed. 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 open file operations. io_seek_count bigint The number of seek file operations. io_open_time double The time consumed to perform open file operations. Unit: microseconds. io_seek_time double The time consumed to perform seek file operations. Unit: microseconds. - polar_stat_query_countThe following table describes the parameters in this view.Note To use the polar_stat_query_count view, you must create the
polar_stat_sql
plug-in by executing the create extension polar_stat_sql statement.Parameter Type Description sqltype
text The SQL type. Valid values: DQL, DML, DDL, and DCL. cmdtype
text The SQL statement type. Examples: SELECT, INSERT, and UPDATE. count
bigint The total number of executions.