Global SysCache Functions

Description: Queries the table metadata in the global system cache in a database. The user who calls this function must have the SYSADMIN permission.

Parameter: Specifies the database and table to be queried in the global system cache. The default value NULL or value –1 of database_id indicates all databases. The value 0 indicates a shared table. Other values indicate a specified database and a shared table. rel_id indicates the OID of a specified table. The default value NULL or value –1 indicates all tables. Other values indicate a specified table. If database_id does not exist, an error is reported. If rel_id does not exist, the query result is empty.

Return type: tuple

gaussdb=# SELECT * FROM  gs_gsc_table_detail(-1) limit 1;
database_oid | database_name | reloid |         relname         | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relhasindex | relisshared | relkind | relnatts | relhasoids | relhaspkey | parttype | tdhasuids | attnames  | extinfo
--------------+---------------+--------+-------------------------+--------------+---------+-----------+----------+-------+-------------+---------------+-------------+-------------+---------+----------+------------+------------+----------+-----------+-----------+---------
0 |               |   2676 | pg_authid_rolname_index |           11 |       0 |         0 |       10 |   403 |           0 |          1664 | f           | t           | i       |        1 | f          | f          | n        | f         | 'rolname' |
(1 row)

Description: Queries the system catalog row information in the global system cache in a database. The user who calls this function must have the SYSADMIN permission.

Parameter: Specifies the database and table to be queried in the global system cache. The default value NULL or value –1 of database_id indicates all databases. The value 0 indicates a shared table. Other values indicate a specified database and a shared table. rel_id indicates the ID of a specified table, including all system catalogs in the system cache. The default value NULL or value –1 indicates all tables. Other values indicate a specified table. If database_id does not exist, an error is reported. If rel_id does not exist, the result is empty.

Return type: tuple

Example:

-- Obtain the OID of a specific database through pg_database, for example, running SELECT oid, * FROM pg_database;.
-- In the returned tuple, find the value of the OID column based on the database name column, and then run the following query command. In the example, the obtained OID is 16574.
gaussdb=# SELECT * FROM  gs_gsc_catalog_detail(16574, 1260);
 database_id | database_name | rel_id | rel_name  | cache_id |  self  |  ctid  | infomask | infomask2 | hash_value | refcount
-------------+---------------+--------+-----------+----------+--------+--------+----------+-----------+------------+----------
           0 |               |   1260 | pg_authid |       10 | (0, 9) | (0, 9) |    10507 |        26 |  531311568 |       10
           0 |               |   1260 | pg_authid |       11 | (0, 4) | (0, 4) |     2313 |        26 |  365368336 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 9) | (0, 9) |    10507 |        26 | 3911517328 |       10
           0 |               |   1260 | pg_authid |       11 | (0, 7) | (0, 7) |     2313 |        26 | 1317799983 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 5) | (0, 5) |     2313 |        26 | 3664347448 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 1) | (0, 1) |     2313 |        26 |  276477273 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 3) | (0, 3) |     2313 |        26 | 2465837659 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 8) | (0, 8) |     2313 |        26 | 3205288035 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 6) | (0, 6) |     2313 |        26 |  131811687 |        1
           0 |               |   1260 | pg_authid |       11 | (0, 2) | (0, 2) |     2313 |        26 | 1226484587 |        1
(10 rows)

Description: Clears the global system cache. Note that data in use will not be cleared. The user who calls this function must have the SYSADMIN permission.

Parameter: Specifies the database whose global system cache needs to be cleared. The default value NULL or value –1 indicates that the global system cache of all databases is forcibly cleared. The value 0 indicates that the global system cache of only the shared table is cleared. Other values indicate that the global system cache of a specified database and a specified shared table is cleared. If database_id does not exist, an error is reported.

Return type: Boolean

Example:

gaussdb=# SELECT * FROM  gs_gsc_clean();
gs_gsc_clean
--------------
t
(1 row)

Description: Obtains GSC memory statistics on the local node, including cache query, hit, loading, expiration, and occupied space information of tuples, relations, and partitions, database-level eviction information, thread reference information, and memory usage information. This function can be used to locate performance problems. For example, if the value of the hits or searches array is far less than 1, the value of global_syscache_threshold may be too small. As a result, the query hit ratio decreases. The user who calls this function must have the SYSADMIN permission.

Parameter: Specifies the global system cache statistics of the database to be queried. Value NULL or –1 indicates that all databases are queried. Value 0 indicates that only the shared table is queried. Other values indicate that a specified database and a specified shared table are queried. If an invalid value is entered, an error is reported, indicating that database_id does not exist.

Return type: tuple

Example:

gaussdb=# SELECT * FROM  gs_gsc_dbstat_info();
database_id | database_name | tup_searches | tup_hits | tup_miss | tup_count | tup_dead | tup_memory | rel_searches | rel_hits | rel_mis
s | rel_count | rel_dead | rel_memory | part_searches | part_hits | part_miss | part_count | part_dead | part_memory | total_memory | swa
pout_count | refcount
-------------+---------------+--------------+----------+----------+-----------+----------+------------+--------------+----------+--------
--+-----------+----------+------------+---------------+-----------+-----------+------------+-----------+-------------+--------------+----
-----------+----------
           0 |               |          300 |      235 |       31 |        22 |        2 |       9752 |          598 |      108 |       1
8 |        18 |        0 |      77720 |             0 |         0 |         0 |          0 |         0 |           0 |       752912 |
         0 |        0
       16574 | testdb      |         3368 |     2289 |      329 |       273 |        0 |      92593 |         1113 |      524 |       4
8 |        48 |        0 |     340456 |             0 |         0 |         0 |          0 |         0 |           0 |      4124792 |
         0 |       10
(2 rows)