Functions for Collecting Statistics in Partitioned Tables
gs_stat_get_partition_stats(oid)
Description: Obtains the statistics of a specific partition.
Return type: record
gs_stat_get_xact_partition_stats(oid)
Description: Obtains transaction statistics of a specific partition.
Return type: record
gs_stat_get_all_partitions_stats()
Description: Obtains the statistics of all partitions.
Return type: SETOF record
gs_stat_get_xact_all_partitions_stats()
Description: Obtains transaction statistics of all partitions.
Return type: SETOF record
gs_statio_get_all_partitions_stats()
Description: Obtains the I/O statistics of all partitions.
Return type: SETOF record
Examples of the preceding five functions
Statistics are reported asynchronously during execution. Based on UDP, delay and packet loss may occur during backend thread processing. The following example is for reference only.
Querying out-of-transaction statistics:
gaussdb=# CREATE TABLE part_tab1 gaussdb-# ( gaussdb(# a int, b int gaussdb(# ) gaussdb-# PARTITION BY RANGE(b) gaussdb-# ( gaussdb(# PARTITION P1 VALUES LESS THAN(10), gaussdb(# PARTITION P2 VALUES LESS THAN(20), gaussdb(# PARTITION P3 VALUES LESS THAN(MAXVALUE) gaussdb(# ); CREATE TABLE gaussdb=# CREATE TABLE subpart_tab1 gaussdb-# ( gaussdb(# month_code VARCHAR2 ( 30 ) NOT NULL , gaussdb(# dept_code VARCHAR2 ( 30 ) NOT NULL , gaussdb(# user_no VARCHAR2 ( 30 ) NOT NULL , gaussdb(# sales_amt int gaussdb(# ) gaussdb-# PARTITION BY RANGE (month_code) SUBPARTITION BY RANGE (dept_code) gaussdb-# ( gaussdb(# PARTITION p_201901 VALUES LESS THAN( '201903' ) gaussdb(# ( gaussdb(# SUBPARTITION p_201901_a VALUES LESS THAN( '2' ), gaussdb(# SUBPARTITION p_201901_b VALUES LESS THAN( '3' ) gaussdb(# ), gaussdb(# PARTITION p_201902 VALUES LESS THAN( '201904' ) gaussdb(# ( gaussdb(# SUBPARTITION p_201902_a VALUES LESS THAN( '2' ), gaussdb(# SUBPARTITION p_201902_b VALUES LESS THAN( '3' ) gaussdb(# ) gaussdb(# ); CREATE TABLE gaussdb=# CREATE INDEX index_part_tab1 ON part_tab1(b) LOCAL gaussdb-# ( gaussdb(# PARTITION b_index1, gaussdb(# PARTITION b_index2, gaussdb(# PARTITION b_index3 gaussdb(# ); CREATE INDEX gaussdb=# CREATE INDEX idx_user_no ON subpart_tab1(user_no) LOCAL; CREATE INDEX gaussdb=# INSERT INTO part_tab1 VALUES(1, 1); INSERT 0 1 gaussdb=# INSERT INTO part_tab1 VALUES(1, 11); INSERT 0 1 gaussdb=# INSERT INTO part_tab1 VALUES(1, 21); INSERT 0 1 gaussdb=# UPDATE part_tab1 SET a = 2 WHERE b = 1; UPDATE 1 gaussdb=# UPDATE part_tab1 SET a = 3 WHERE b = 11; UPDATE 1 gaussdb=# UPDATE /*+ indexscan(part_tab1) */ part_tab1 SET a = 4 WHERE b = 21; UPDATE 1 gaussdb=# DELETE FROM part_tab1; DELETE 3 gaussdb=# ANALYZE part_tab1; ANALYZE gaussdb=# VACUUM part_tab1; VACUUM gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '1', '1', 1); INSERT 0 1 gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '2', '2', 1); INSERT 0 1 gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '1', '3', 1); INSERT 0 1 gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '2', '4', 1); INSERT 0 1 gaussdb=# UPDATE subpart_tab1 SET sales_amt = 2 WHERE user_no='1'; UPDATE 1 gaussdb=# UPDATE subpart_tab1 SET sales_amt = 3 WHERE user_no='2'; UPDATE 1 gaussdb=# UPDATE subpart_tab1 SET sales_amt = 4 WHERE user_no='3'; UPDATE 1 gaussdb=# UPDATE /*+ indexscan(subpart_tab1) */ subpart_tab1 SET sales_amt = 5 WHERE user_no='4'; UPDATE 1 gaussdb=# DELETE FROM subpart_tab1; DELETE 4 gaussdb=# ANALYZE subpart_tab1; ANALYZE gaussdb=# VACUUM subpart_tab1; VACUUM gaussdb=# SELECT * FROM gs_stat_all_partitions; partition_oid | schemaname | relname | partition_name | sub_partition_name | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count ---------------+------------+--------------+----------------+--------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+ ------------+-------------------------------+------------------------+-------------------------------+------------------------+--------------+------------------+---------------+------------------- 16964 | public | subpart_tab1 | p_201902 | p_201902_b | 5 | 1 | 4 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:45.293965+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688861+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 16963 | public | subpart_tab1 | p_201902 | p_201902_a | 5 | 1 | 4 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:45.291022+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688843+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 16961 | public | subpart_tab1 | p_201901 | p_201901_b | 5 | 1 | 4 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:45.288037+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688829+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 16960 | public | subpart_tab1 | p_201901 | p_201901_a | 5 | 1 | 4 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:45.285311+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688802+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 16954 | public | part_tab1 | p3 | | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:29.490636+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540115+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 16953 | public | part_tab1 | p2 | | 4 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:29.487914+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540098+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 16952 | public | part_tab1 | p1 | | 5 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:29.48536+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540071+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 (7 rows) gaussdb=# SELECT * FROM gs_statio_all_partitions; partition_oid | schemaname | relname | partition_name | sub_partition_name | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | t idx_blks_hit ---------------+------------+--------------+----------------+--------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+-- ------------- 16964 | public | subpart_tab1 | p_201902 | p_201902_b | 4 | 8 | 2 | 21 | | | | 16963 | public | subpart_tab1 | p_201902 | p_201902_a | 4 | 8 | 2 | 21 | | | | 16961 | public | subpart_tab1 | p_201901 | p_201901_b | 4 | 8 | 2 | 21 | | | | 16960 | public | subpart_tab1 | p_201901 | p_201901_a | 4 | 8 | 2 | 21 | | | | 16954 | public | part_tab1 | p3 | | 4 | 8 | 2 | 15 | | | | 16953 | public | part_tab1 | p2 | | 4 | 8 | 2 | 15 | | | | 16952 | public | part_tab1 | p1 | | 4 | 8 | 2 | 15 | | | | (7 rows) gaussdb=# SELECT * FROM gs_stat_get_partition_stats(16952); partition_oid | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count | last_data_changed | heap_blks_read | heap_blks_hit | idx_blks_re ad | idx_blks_hit | tup_fetch | block_fetch ---------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+------------------------------+---------------------- --+-------------------------------+------------------------+--------------+------------------+---------------+-------------------+------------------------+----------------+---------------+------------ ---+--------------+-----------+------------- 16952 | 5 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 2023-05-15 20:36:29.48536+08 | 2000-01-01 08:00:00+0 8 | 2023-05-15 20:36:28.540071+08 | 2000-01-01 08:00:00+08 | 1 | 0 | 1 | 0 | 2000-01-01 08:00:00+08 | 4 | 8 | 2 | 21 | 0 | 12 (1 row)
Querying statistics within a transaction
gaussdb=# BEGIN; BEGIN gaussdb=# INSERT INTO part_tab1 VALUES(1, 1); INSERT 0 1 gaussdb=# INSERT INTO part_tab1 VALUES(1, 11); INSERT 0 1 gaussdb=# INSERT INTO part_tab1 VALUES(1, 21); INSERT 0 1 gaussdb=# UPDATE part_tab1 SET a = 2 WHERE b = 1; UPDATE 1 gaussdb=# UPDATE part_tab1 SET a = 3 WHERE b = 11; UPDATE 1 gaussdb=# UPDATE /*+ indexscan(part_tab1) */ part_tab1 SET a = 4 WHERE b = 21; UPDATE 1 gaussdb=# DELETE FROM part_tab1; DELETE 3 gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '1', '1', 1); INSERT 0 1 gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '2', '2', 1); INSERT 0 1 gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '1', '3', 1); INSERT 0 1 gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '2', '4', 1); INSERT 0 1 gaussdb=# UPDATE subpart_tab1 SET sales_amt = 2 WHERE user_no='1'; UPDATE 1 gaussdb=# UPDATE subpart_tab1 SET sales_amt = 3 WHERE user_no='2'; UPDATE 1 gaussdb=# UPDATE subpart_tab1 SET sales_amt = 4 WHERE user_no='3'; UPDATE 1 gaussdb=# UPDATE /*+ indexscan(subpart_tab1) */ subpart_tab1 SET sales_amt = 5 WHERE user_no='4'; UPDATE 1 gaussdb=# DELETE FROM subpart_tab1; DELETE 4 gaussdb=# SELECT * FROM gs_stat_xact_all_partitions; partition_oid | schemaname | relname | partition_name | sub_partition_name | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd ---------------+------------+--------------+----------------+--------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+--------------- 16964 | public | subpart_tab1 | p_201902 | p_201902_b | 4 | 4 | 1 | 2 | 1 | 1 | 1 | 1 16963 | public | subpart_tab1 | p_201902 | p_201902_a | 4 | 4 | 1 | 0 | 1 | 1 | 1 | 1 16961 | public | subpart_tab1 | p_201901 | p_201901_b | 4 | 4 | 1 | 0 | 1 | 1 | 1 | 1 16960 | public | subpart_tab1 | p_201901 | p_201901_a | 4 | 4 | 1 | 0 | 1 | 1 | 1 | 1 16954 | public | part_tab1 | p3 | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 16953 | public | part_tab1 | p2 | | 3 | 2 | 0 | 0 | 1 | 1 | 1 | 1 16952 | public | part_tab1 | p1 | | 4 | 2 | 0 | 0 | 1 | 1 | 1 | 1 (7 rows) gaussdb=# SELECT * FROM gs_stat_get_xact_partition_stats(16952); partition_oid | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | tup_fetch ---------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+----------- 16952 | 4 | 2 | 0 | 0 | 1 | 1 | 1 | 1 | 0 (1 row)
gs_stat_get_partition_analyze_count(oid)
Description: Specifies the number of times that a user starts analysis on a partition.
Return type: bigint
gs_stat_get_partition_autoanalyze_count(oid)
Description: Specifies the number of times that the autovacuum daemon thread starts analysis in a partition.
Return type: bigint
gs_stat_get_partition_autovacuum_count(oid)
Description: Specifies the number of times that the autovacuum daemon thread starts vacuum in a partition.
Return type: bigint
gs_stat_get_partition_last_analyze_time(oid)
Description: Specifies the last time when a partition starts to be analyzed manually or by the autovacuum thread.
Return type: timestamptz
gs_stat_get_partition_last_autoanalyze_time(oid)
Description: Specifies the time when the last analysis initiated by the autovacuum daemon thread in a partition.
Return type: timestamptz
gs_stat_get_partition_last_autovacuum_time(oid)
Description: Specifies the time of the last vacuum initiated by the autovacuum daemon thread in a partition.
Return type: timestamptz
gs_stat_get_partition_last_data_changed_time(oid)
Description: Specifies the last time of a modification in a partition, such as insert, update, delete, and truncate. Currently, this parameter is not supported.
Return type: timestamptz
gs_stat_get_partition_last_vacuum_time(oid)
Description: Specifies the most recent time when the user manually cleared a table or when the autovacuum thread was started to clear a partition.
Return type: timestamptz
gs_stat_get_partition_numscans(oid)
Description: Specifies the number of sequential scans in a partition.
Return type: bigint
gs_stat_get_partition_tuples_returned(oid)
Description: Specifies the number of rows read by sequential scan in a partition.
Return type: bigint
gs_stat_get_partition_tuples_fetched(oid)
Description: Specifies the number of rows fetched by bitmap scans in a partition.
Return type: bigint
gs_stat_get_partition_vacuum_count(oid)
Description: Specifies the number of times that a user starts vacuum in a partition.
Return type: bigint
gs_stat_get_xact_partition_tuples_fetched(oid)
Description: Specifies the number of tuple rows scanned in a transaction.
Return type: bigint
gs_stat_get_xact_partition_numscans(oid)
Description: Specifies the number of sequential scans performed on a partition in the current transaction.
Return type: bigint
gs_stat_get_xact_partition_tuples_returned(oid)
Description: Specifies the number of rows read through sequential scans in a partition in the current transaction.
Return type: bigint
gs_stat_get_partition_blocks_fetched(oid)
Description: Specifies the number of disk block fetch requests for a partition.
Return type: bigint
gs_stat_get_partition_blocks_hit(oid)
Description: Specifies the number of disk block requests found in cache for a partition.
Return type: bigint
pg_stat_get_partition_tuples_inserted(oid)
Description: Specifies the number of rows inserted into a specified table partition.
Return type: bigint
pg_stat_get_partition_tuples_updated(oid)
Description: Specifies the number of rows updated in a specified table partition.
Return type: bigint
pg_stat_get_partition_tuples_deleted(oid)
Description: Specifies the number of rows deleted from a specified table partition.
Return type: bigint
pg_stat_get_partition_tuples_changed(oid)
Description: Specifies the total number of inserted, updated, and deleted rows after a table partition was last analyzed or autoanalyzed.
Return type: bigint
pg_stat_get_partition_live_tuples(oid)
Description: Specifies the number of live rows in a partitioned table.
Return type: bigint
pg_stat_get_partition_dead_tuples(oid)
Description: Specifies the number of dead rows in a partitioned table. It indicates the number of inactive row pointers in Ustore tables.
Return type: bigint
pg_stat_get_xact_partition_tuples_inserted(oid)
Description: Specifies the number of inserted tuples in the active sub-transactions related to a table partition.
Return type: bigint
pg_stat_get_xact_partition_tuples_deleted(oid)
Description: Specifies the number of deleted tuples in the active sub-transactions related to a table partition.
Return type: bigint
pg_stat_get_xact_partition_tuples_hot_updated(oid)
Description: Specifies the number of hot updated tuples in the active sub-transactions related to a table partition.
Return type: bigint
pg_stat_get_xact_partition_tuples_updated(oid)
Description: Specifies the number of updated tuples in the active sub-transactions related to a table partition.
Return type: bigint
pg_stat_get_partition_tuples_hot_updated(oid)
Description: Returns statistics on the number of hot updated tuples in a partition with a specified partition ID.
Parameter: oid
Return type: bigint
gs_session_alt_status()
Description: Queries the recoverable status of unplanned ALT sessions executed by the current user. Session status changes caused by unsupported driver API calling are not included. Only basic categories are provided for unrecoverable causes. If GUC parameters, SQL statements, or system functions are not supported, query the list supported by the ALT to check whether the requests called by the application are supported. For details, see section "Unplanned Application Lossless and Transparent" in Feature Guide. ALT support policy list description for SQL syntax and system functions.
Return type: SETOF record
|
Name |
Type |
Description |
|---|---|---|
|
sessionid |
integer |
Session ID. |
|
sessionreplaystatus |
text |
ALT session recovery status.
|
|
xactreplaystatus |
text |
ALT transaction replay status.
|
|
reason |
text |
Description of the reason why the fault cannot be rectified. |
Example:
1 2 3 4 5 6 |
gaussdb=# SELECT * FROM GS_SESSION_ALT_STATUS(); sessionid | sessionreplaystatus | xactreplaystatus | reason -----------------+---------------------+------------------+---------------------------------------------------------------------------------------- 140564166014720 | disable | disable | Session data types(valid after transaction is finished):unsupported set from current, + | | | Transaction non-replayable types:advisory lock, (1 row) |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
