DBMSSTATS系統包函數使用詳解-雲原生資料庫 PolarDB-阿里雲

DBMS_STATS用於收集資料庫運行過程中的統計資訊，提供現有統計資訊的備份，支援將備份統計項進行還原。可以自訂備份統計資訊的時間點，根據需求修改表的統計資訊，從而輔助最佳化器產生期望中的執行計畫。

前提條件

使用dbms_stats需要將參數polar_dbms_stats.use_locked_stats的值設定為ON。

可以通過以下命令查看該參數的狀態：

SHOW polar_dbms_stats.use_locked_stats;

可以通過以下命令設定該參數：

SET polar_dbms_stats.use_locked_stats = ON;   -- 開啟
SET polar_dbms_stats.use_locked_stats = OFF;  -- 關閉

DBMS_STATS子程式總覽

子程式	說明
GATHER_SCHEMA_STATS Procedure	收集和備份Schema級的統計資訊。
GATHER_TABLE_STATS Procedure	收集和備份表級的統計資訊。
GATHER_DATABASE_STATS Procedure	收集和備份資料庫級的統計資訊。
GATHER_INDEX_STATS Procedure	收集和備份索引的統計資訊。
GATHER_COLUMN_STATS Procedure	收集和備份指定列的統計資訊。
RESTORE_SCHEMA_STATS Procedure	還原指定Schema的統計資訊。
RESTORE_DATABASE_STATS Procedure	還原資料庫的統計資訊。
RESTORE_TABLE_STATS Procedure	還原指定表的統計資訊。
RESTORE_COLUMN_STATS Procedure	還原指定列的統計資訊。
PURGE_STATS Procedure	刪除指定時間點之前的統計資訊備份。
SET_TABLE_STATS Procedure	設定指定表的統計資訊。
GET_TABLE_STATS Procedure	擷取指定表的統計資訊。
SET_INDEX_STATS Procedure	設定索引的統計資訊。
GET_INDEX_STATS Procedure	擷取指定索引的統計資訊。
GET_COLUMN_STATS Procedure	擷取指定列的統計資訊。
LOCK_TABLE_STATS Procedure	鎖定正在使用的表統計資訊。
UNLOCK_TABLE_STATS Procedure	解鎖正在使用的表統計資訊。
LOCK_SCHEMA_STATS Procedure	鎖定正在使用的Schema的統計資訊。
UNLOCK_SCHEMA_STATS Procedure	解鎖正在使用的Schema的統計資訊。
LOCK_COLUMN_STATS Procedure	鎖定正在使用的列的統計資訊。
UNLOCK_COLUMN_STATS Procedure	解鎖正在使用的列的統計資訊。
DELETE_TABLE_STATS Procedure	刪除已有的表統計資訊。
DELETE_COLUMN_STATS Procedure	刪除已有的列統計資訊。
DELETE_SCHEMA_STATS Procedure	刪除已有的Schema對象統計資訊。
DELETE_INDEX_STATS Procedure	刪除已有的索引對象統計資訊。

準備測試資料

測試資料僅適用本文中的操作樣本。

建立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.%';