SQL

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.

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

pg_stat_statements

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.

The following table describes the parameters in this view.


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_sql

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 the pg_stat_statements view.

The following table describes the parameters in this 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_count

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.

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 statement type. Examples: SELECT, INSERT, and UPDATE.
`count`	bigint	The total number of executions.