PolarDB PostgreSQL版(相容Oracle)提供了DBMS_STATS包,用於收集資料庫運行過程中的統計資訊,提供現有統計資訊的備份,並支援將備份統計項進行還原。您可以自訂備份統計資訊的時間點,根據需求修改表的統計資訊,從而輔助最佳化器產生期望中的執行計畫。
前提條件
如您需要使用DBMS_STATS包相關功能,請聯絡我們處理。
準備測試資料
測試資料僅適用於本文中的操作樣本。
建立Schema,名為
dbms_stats_schema
。CREATE SCHEMA dbms_stats_schema;
建立表,名為
dbms_stats_test
。CREATE TABLE dbms_stats_schema.dbms_stats_test(id int);
建立索引。
CREATE INDEX dbms_stats_index on dbms_stats_schema.dbms_stats_test(id);
插入資料。
INSERT INTO dbms_stats_schema.dbms_stats_test values (generate_series(1,10000));
DBMS_STATS.GATHER_SCHEMA_STATS
該函數用於收集和備份Schema級的統計資訊。
文法
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待分析的Schema名稱。 | 是 |
樣本
收集和備份Schema級的統計資訊,樣本如下:
CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');
查詢統計資訊的備份情況,查詢樣本如下:
SELECT * FROM polar_dbms_stats.backup_history;
DBMS_STATS.GATHER_TABLE_STATS
該函數用於收集和備份表級的統計資訊。
文法
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待分析的表所屬的Schema。 | 是 |
tabname | 待分析的表名稱。 | 是 |
樣本
收集並備份表級的統計資訊,樣本如下:
CALL DBMS_STATS.GATHER_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');
查詢統計資訊備份情況,查詢樣本如下:
SELECT * FROM polar_dbms_stats.backup_history;
DBMS_STATS.GATHER_DATABASE_STATS
該函數用於收集和備份資料庫級的統計資訊。
文法
DBMS_STATS.GATHER_DATABASE_STATS ();
樣本
收集並備份資料庫級的統計資訊,樣本如下:
CALL DBMS_STATS.GATHER_DATABASE_STATS();
查詢統計資訊備份情況,查詢樣本如下:
SELECT * FROM polar_dbms_stats.backup_history;
DBMS_STATS.GATHER_INDEX_STATS
該函數用於收集和備份索引的統計資訊。
文法
DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待分析的索引所屬的Schema。 | 是 |
indname | 待分析的索引名稱。 | 是 |
樣本
收集並備份索引級的統計資訊,樣本如下:
CALL DBMS_STATS.GATHER_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');
查詢統計資訊備份情況,查詢樣本如下:
SELECT * FROM polar_dbms_stats.backup_history;
DBMS_STATS.GATHER_COLUMN_STATS
該函數用於收集和備份指定列的統計資訊。
文法
DBMS_STATS.GATHER_COLUMN_STATS (
ownname VARCHAR2,
tablename VARCHAR2,
attname TEXT
);
參數
參數 | 描述 | 是否必填 |
ownname | 待分析的列所屬的Schema。 | 是 |
tablename | 待分析的列所屬的表。 | 是 |
attname | 待分析的列名稱。 | 是 |
樣本
收集並備份指定列的統計資訊,樣本如下:
CALL DBMS_STATS.GATHER_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');
查詢統計資訊備份情況,查詢樣本如下:
SELECT * FROM polar_dbms_stats.backup_history;
DBMS_STATS.RESTORE_SCHEMA_STATS
該函數用於還原指定Schema的統計資訊。
文法
DBMS_STATS.RESTORE_SCHEMA_STATS (
ownname VARCHAR2,
as_of_timestamp TIMESTAMP WITH TIME ZONE
);
參數
參數 | 描述 | 是否必填 |
ownname | 待還原的Schema名稱。 | 是 |
as_of_timestamp | 待還原的時間點。 | 是 |
樣本
還原指定Schema的統計資訊,樣本如下:
SELECT DBMS_STATS.RESTORE_SCHEMA_STATS('dbms_stats_schema',time) FROM polar_dbms_stats.backup_history WHERE unit='s';
查詢還原後的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';
DBMS_STATS.RESTORE_DATABASE_STATS
該函數用於還原資料庫的統計資訊。
文法
DBMS_STATS.RESTORE_DATABASE_STATS (
as_of_timestamp TIMESTAMP WITH TIME ZONE
);
參數
參數 | 描述 | 是否必填 |
as_of_timestamp | 待還原的時間點。 | 是 |
樣本
還原資料庫統計資訊,樣本如下:
SELECT DBMS_STATS.RESTORE_DATABASE_STATS(time) FROM polar_dbms_stats.backup_history WHERE unit='d';
查詢還原後的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';
DBMS_STATS.RESTORE_TABLE_STATS
該函數用於還原指定表的統計資訊。
文法
DBMS_STATS.RESTORE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
as_of_timestamp TIMESTAMP WITH TIME ZONE
);
參數
參數 | 描述 | 是否必填 |
ownname | 待還原的表所屬的Schema。 | 是 |
tabname | 待還原的表名稱。 | 是 |
as_of_timestamp | 待還原的時間點。 | 是 |
樣本
還原指定表統計資訊,樣本如下:
SELECT DBMS_STATS.RESTORE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', time) FROM polar_dbms_stats.backup_history WHERE unit='t';
查詢還原後的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';
DBMS_STATS.RESTORE_COLUMN_STATS
該函數用於還原指定列的統計資訊。
文法
DBMS_STATS.RESTORE_COLUMN_STATS (
ownname TEXT,
tablename TEXT,
attname TEXT,
as_of_timestamp timestamp with time zone
);
參數
參數 | 描述 | 是否必填 |
ownname | 待還原的列所屬的Schema。 | 是 |
tabname | 待還原的列所屬的表。 | 是 |
attname | 待還原的列名。 | 是 |
as_of_timestamp | 待還原的時間點。 | 是 |
樣本
還原指定列統計資訊,樣本如下:
SELECT DBMS_STATS.RESTORE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', time) FROM polar_dbms_stats.backup_history WHERE unit='c';
查詢還原後的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';
DBMS_STATS.PURGE_STATS
該函數用於刪除指定時間點之前的統計資訊備份。
文法
DBMS_STATS.PURGE_STATS (
before_timestamp timestamp
);
參數
參數 | 描述 | 是否必填 |
before_timestamp | 指定的刪除時間點。 | 是 |
樣本
刪除指定時間點之前的統計資訊備份,樣本如下:
SELECT DBMS_STATS.PURGE_STATS(time) FROM polar_dbms_stats.backup_history WHERE unit='c';
DBMS_STATS.SET_TABLE_STATS
該函數用於設定指定表的統計資訊。
在進行set_table_stats前,如果沒有採集相關表的統計資訊,需要先執行gather_schema_stats或者gather_table_stats。
文法
DBMS_STATS.SET_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
numrows NUMBER DEFAULT NULL,
numblks NUMBER DEFAULT NULL
);
參數
參數 | 描述 | 是否必填 |
ownname | 待設定的表所屬的Schema。 | 是 |
tabname | 待設定的表名稱。 | 是 |
numrows | 設定的錶行數。 | 是 |
numblks | 設定的表塊數。 | 是 |
樣本
設定表的統計資訊,樣本如下:
CALL DBMS_STATS.SET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', 1234, 4321);
DBMS_STATS.GET_TABLE_STATS
該函數用於擷取指定表的統計資訊。
文法
DBMS_STATS.GET_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
numrows OUT NUMBER,
numblks OUT NUMBER
);
參數
參數 | 描述 | 是否必填 |
ownname | 待擷取的表所屬的Schema。 | 是 |
tabname | 待擷取的表名稱。 | 是 |
numrows | 擷取的錶行數。 | 是 |
numblks | 擷取的表塊數。 | 是 |
樣本
擷取表的統計資訊,樣本如下:
DECLARE
numrows integer;
numblks integer;
BEGIN
CALL DBMS_STATS.GET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', numrows, numblks);
raise notice '%', numrows;
raise notice '%', numblks;
END;
DBMS_STATS.SET_INDEX_STATS
該函數用於設定索引的統計資訊。
文法
DBMS_STATS.SET_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
numrows NUMBER DEFAULT NULL,
numblks NUMBER DEFAULT NULL
);
參數
參數 | 描述 | 是否必填 |
ownname | 待設定的索引所屬的Schema。 | 是 |
indname | 待設定的索引名稱。 | 是 |
numrows | 設定的索引行數。 | 是 |
numblks | 設定的索引塊數。 | 是 |
樣本
設定索引的統計資訊,樣本如下:
CALL DBMS_STATS.SET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', 2345, 5432);
DBMS_STATS.GET_INDEX_STATS
該函數用於擷取指定索引的統計資訊。
文法
DBMS_STATS.GET_INDEX_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
numrows OUT NUMBER,
numblks OUT NUMBER
);
參數
參數 | 描述 | 是否必填 |
ownname | 待擷取的索引所屬的Schema。 | 是 |
tabname | 待擷取的索引名稱。 | 是 |
numrows | 擷取的索引行數。 | 是 |
numblks | 擷取的索引塊數。 | 是 |
樣本
擷取索引的統計資訊,樣本如下:
DECLARE
numrows integer;
numblks integer;
BEGIN
CALL DBMS_STATS.GET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', numrows, numblks);
raise notice '%', numrows;
raise notice '%', numblks;
END;
DBMS_STATS.GET_COLUMN_STATS
該函數用於擷取指定列的統計資訊。
文法
DBMS_STATS.GET_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
distcnt OUT NUMBER,
nullcnt OUT NUMBER,
avgclen OUT NUMBER
);
參數
參數 | 描述 | 是否必填 |
ownname | 待擷取的列所屬的Schema。 | 是 |
tabname | 待擷取的列所屬的表。 | 是 |
colname | 待擷取的列名稱。 | 是 |
distcnt | 擷取的列中不同值的數量。 | 是 |
nullcnt | 擷取的列空值數量。 | 是 |
avgclen | 擷取的列平均長度。 | 是 |
樣本
擷取列的統計資訊,樣本如下:
DECLARE
distcnt integer;
nullcnt integer;
avgclen integer;
BEGIN
CALL DBMS_STATS.GET_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', distcnt, nullcnt, avgclen);
raise notice '%', distcnt;
raise notice '%', nullcnt;
raise notice '%', avgclen;
END;
DBMS_STATS.LOCK_TABLE_STATS
該函數用於鎖定正在使用的表統計資訊。
文法
DBMS_STATS.LOCK_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待鎖定的表所屬的Schema。 | 是 |
tabname | 待鎖定的表名稱。 | 是 |
樣本
鎖定正在使用的表的統計資訊,樣本如下:
CALL DBMS_STATS.LOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');
查看鎖定的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname = 'dbms_stats_schema.dbms_stats_test';
DBMS_STATS.UNLOCK_TABLE_STATS
該函數用於解鎖正在使用的表統計資訊。
文法
DBMS_STATS.UNLOCK_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待解鎖的表所屬的Schema。 | 是 |
tabname | 待解鎖的表名稱。 | 是 |
樣本
解鎖正在使用的表的統計資訊,樣本如下:
CALL DBMS_STATS.UNLOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');
查看鎖定的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname = 'dbms_stats_schema.dbms_stats_test';
DBMS_STATS.LOCK_SCHEMA_STATS
該函數用於鎖定正在使用的Schema的統計資訊。
文法
DBMS_STATS.LOCK_SCHEMA_STATS (
ownname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待鎖定的Schema名稱。 | 是 |
樣本
鎖定正在使用的Schema的統計資訊,樣本如下:
CALL DBMS_STATS.LOCK_SCHEMA_STATS('dbms_stats_schema');
查看鎖定的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema.%';
DBMS_STATS.UNLOCK_SCHEMA_STATS
該函數用於解鎖正在使用的Schema的統計資訊。
文法
DBMS_STATS.UNLOCK_SCHEMA_STATS (
ownname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待解鎖的Schema名稱。 | 是 |
樣本
解鎖正在使用的Schema的統計資訊,樣本如下:
CALL DBMS_STATS.UNLOCK_SCHEMA_STATS('dbms_stats_schema');
查看鎖定的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema.%';
DBMS_STATS.LOCK_COLUMN_STATS
該函數用於鎖定正在使用的列的統計資訊。
文法
DBMS_STATS.LOCK_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
attname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待鎖定的列所屬的Schema。 | 是 |
tabname | 待鎖定的列所屬的表。 | 是 |
attname | 待鎖定的列名稱。 | 是 |
樣本
鎖定正在使用的列的統計資訊,樣本如下:
CALL DBMS_STATS.LOCK_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');
DBMS_STATS.UNLOCK_COLUMN_STATS
該函數用於解鎖正在使用的列的統計資訊。
文法
DBMS_STATS.UNLOCK_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
attname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待解鎖的列所屬的Schema。 | 是 |
tabname | 待解鎖的列所屬的表。 | 是 |
attname | 待解鎖的列名稱。 | 是 |
樣本
解鎖正在使用的列的統計資訊,樣本如下:
CALL DBMS_STATS.UNLOCK_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');
DBMS_STATS.DELETE_TABLE_STATS
該函數用於刪除已有的表統計資訊。
文法
DBMS_STATS.DELETE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待刪除的表所屬的Schema。 | 是 |
tabname | 待刪除的表名稱。 | 是 |
樣本
刪除已有的表統計資訊,樣本如下:
CALL DBMS_STATS.DELETE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');
查看刪除後的表統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname = 'dbms_stats_schema.dbms_stats_test';
DBMS_STATS.DELETE_COLUMN_STATS
該函數用於刪除已有的列統計資訊。
文法
DBMS_STATS.DELETE_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
attname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待刪除的列所屬的Schema。 | 是 |
tabname | 待刪除的列所屬的表。 | 是 |
attname | 待刪除的列名稱。 | 是 |
樣本
刪除已有的列統計資訊,樣本如下:
CALL DBMS_STATS.DELETE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');
DBMS_STATS.DELETE_SCHEMA_STATS
該函數用於刪除已有的Schema對象統計資訊。
文法
DBMS_STATS.DELETE_SCHEMA_STATS (
ownname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待刪除的Schema名稱。 | 是 |
樣本
刪除已有的Schema統計資訊,樣本如下:
CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');
查詢刪除後的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname LIKE 'dbms_stats_schema.%';
DBMS_STATS.DELETE_INDEX_STATS
該函數用於刪除已有的索引對象統計資訊。
文法
DBMS_STATS.DELETE_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2
);
參數
參數 | 描述 | 是否必填 |
ownname | 待刪除的索引所屬的Schema。 | 是 |
indname | 待刪除的索引名稱。 | 是 |
樣本
刪除已有的索引統計資訊,樣本如下:
CALL DBMS_STATS.DELETE_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');
查詢刪除後的統計資訊,樣本如下:
SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname LIKE 'dbms_stats_schema.%';