GaussDB T 的创建脚本中，initplsql.sql 负责创建了两个存储过程，分别是 gather_db_stats 用于收集数据库的统计信息；GATHER_CHANGE_STATS 用于收集数据库变化信息。
CREATE OR REPLACE PROCEDURE GATHER_DB_STATS(
estimate_percent NUMBER DEFAULT 10,
force BOOLEAN DEFAULT TRUE,
max_minutes INTEGER DEFAULT 360
)
–force false: don’t gather when cbo is disable
–max_minutes: default 6 hours
IS
cbo_enable VARCHAR(3);
start_time DATE;
curr_time DATE;
is_finish boolean;
snapshot_too_old EXCEPTION; – declare exception
PRAGMA EXCEPTION_INIT (snapshot_too_old, 715); – assign error code to exception
BEGIN
IF max_minutes < 1 OR max_minutes > 1440 THEN
RAISE_APPLICATION_ERROR(-20000, ‘max_minutes should between [1, 1440]’);
END IF;
--check cbo flag
IF force = FALSE THEN
SELECT VALUE INTO cbo_enable FROM SYS.DV_PARAMETERS WHERE NAME='CBO';
IF UPPER(cbo_enable) = 'OFF' THEN
RETURN;
END IF;
END IF;
start_time := SYSDATE;
--only gather new heap table
LOOP
is_finish := false;
BEGIN
FOR ITEM IN (SELECT OWNER, TABLE_NAME FROM ADM_TABLES WHERE TABLE_TYPE in ('HEAP', 'NOLOGGING') AND LAST_ANALYZED is NULL)
LOOP
curr_time := SYSDATE;
IF curr_time > (start_time + max_minutes/1440) THEN
RETURN;
END IF;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ITEM.OWNER, ITEM.TABLE_NAME, null, estimate_percent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
is_finish := true;
EXCEPTION
WHEN snapshot_too_old THEN -- handle exception
NULL;
END;
IF is_finish = true THEN
EXIT;
END IF;
END LOOP;
--only gather heap table
LOOP
is_finish := false;
BEGIN
FOR ITEM IN (SELECT OWNER, TABLE_NAME,LAST_ANALYZED FROM ADM_TABLES WHERE TABLE_TYPE in ('HEAP', 'NOLOGGING') AND
LAST_ANALYZED < start_time ORDER BY LAST_ANALYZED)
LOOP
curr_time := SYSDATE;
IF curr_time > (start_time + max_minutes/1440) THEN
RETURN;
END IF;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ITEM.OWNER, ITEM.TABLE_NAME, null, estimate_percent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
is_finish := true;
EXCEPTION
WHEN snapshot_too_old THEN -- handle exception
NULL;
END;
IF is_finish = true THEN
EXIT;
END IF;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE GATHER_CHANGE_STATS(
estimate_percent NUMBER DEFAULT 10,
change_percent NUMBER DEFAULT 10,
force BOOLEAN DEFAULT TRUE,
max_minutes NUMBER DEFAULT 60
)
–force false: don’t gather when cbo is disable
–max_minutes: default 1 hour
IS
cbo_enable VARCHAR(3);
start_time DATE;
curr_time DATE;
is_finish boolean;
snapshot_too_old EXCEPTION; – declare exception
PRAGMA EXCEPTION_INIT (snapshot_too_old, 715); – assign error code to exception
BEGIN
IF max_minutes < 1 OR max_minutes > 1440 THEN
RAISE_APPLICATION_ERROR(-20000, ‘max_minutes should between [1, 1440]’);
END IF;
--check cbo flag
IF force = FALSE THEN
SELECT VALUE INTO cbo_enable FROM SYS.DV_PARAMETERS WHERE NAME='CBO';
IF UPPER(cbo_enable) = 'OFF' THEN
RETURN;
END IF;
END IF;
start_time := SYSDATE;
--flush modification to table
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
--(1)gather the new table
LOOP
is_finish := false;
BEGIN
FOR ITEM IN (SELECT OWNER, TABLE_NAME FROM ADM_TABLES WHERE TABLE_TYPE in ('HEAP', 'NOLOGGING') AND LAST_ANALYZED is NULL)
LOOP
curr_time := SYSDATE;
IF curr_time > (start_time + max_minutes/1440) THEN
RETURN;
END IF;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ITEM.OWNER, ITEM.TABLE_NAME, NULL, estimate_percent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
is_finish := true;
EXCEPTION
WHEN snapshot_too_old THEN -- handle exception
NULL;
END;
IF is_finish = true THEN
EXIT;
END IF;
END LOOP;
--(2)gather the table changed
LOOP
is_finish := false;
BEGIN
FOR ITEM IN (SELECT A.OWNER, A.TABLE_NAME, A.LAST_ANALYZED FROM ADM_TABLES A, ADM_TAB_MODIFICATIONS B
WHERE A.TABLE_TYPE in ('HEAP', 'NOLOGGING') AND A.OWNER = B.TABLE_OWNER AND A.TABLE_NAME=B.TABLE_NAME
AND B.PARTITION_NAME IS NULL AND A.LAST_ANALYZED < start_time AND
((NVL(B.INSERTS, 0) + NVL(B.UPDATES, 0) + NVL(B.DELETES, 0))>= (CHANGE_PERCENT * A.NUM_ROWS/100))
ORDER BY A.LAST_ANALYZED)
LOOP
curr_time := SYSDATE;
IF curr_time > (start_time + max_minutes/1440) THEN
RETURN;
END IF;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ITEM.OWNER, ITEM.TABLE_NAME, NULL, estimate_percent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
is_finish := true;
EXCEPTION
WHEN snapshot_too_old THEN -- handle exception
NULL;
END;
IF is_finish = true THEN
EXIT;
END IF;
END LOOP;
--(3)gather the new partition
LOOP
is_finish := false;
BEGIN
FOR ITEM IN (SELECT U.NAME AS OWNER, T.NAME AS TABLE_NAME, TP.NAME AS PARTITION_NAME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON U.ID = T.USER#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE# AND TP.ANALYZETIME IS NULL)
LOOP
curr_time := SYSDATE;
IF curr_time > (start_time + max_minutes/1440) THEN
RETURN;
END IF;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ITEM.OWNER, ITEM.TABLE_NAME, ITEM.PARTITION_NAME, estimate_percent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
is_finish := true;
EXCEPTION
WHEN snapshot_too_old THEN -- handle exception
NULL;
END;
IF is_finish = true THEN
EXIT;
END IF;
END LOOP;
--(4)gather the partition changed
LOOP
is_finish := false;
BEGIN
FOR ITEM IN (SELECT U.NAME AS OWNER, T.NAME AS TABLE_NAME, TP.NAME AS PARTITION_NAME, TP.ANALYZETIME
FROM SYS.SYS_USERS U JOIN SYS.SYS_TABLES T ON U.ID = T.USER#
JOIN SYS.SYS_TABLE_PARTS TP ON T.USER# = TP.USER# AND T.ID = TP.TABLE#
JOIN SYS.SYS_DML_STATS MO ON T.USER# = MO.USER# AND T.ID = MO.TABLE# AND MO.PART# = TP.PART#
WHERE MO.PARTED = 1 AND MO.PART# <> -1 AND TP.ANALYZETIME < start_time AND
((NVL(MO.INSERTS, 0) + NVL(MO.UPDATES, 0) + NVL(MO.DELETES, 0))>= (CHANGE_PERCENT * TP.ROWCNT/100))
ORDER BY TP.ANALYZETIME)
LOOP
curr_time := SYSDATE;
IF curr_time > (start_time + max_minutes/1440) THEN
RETURN;
END IF;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ITEM.OWNER, ITEM.TABLE_NAME, ITEM.PARTITION_NAME, estimate_percent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
is_finish := true;
EXCEPTION
WHEN snapshot_too_old THEN -- handle exception
NULL;
END;
IF is_finish = true THEN
EXIT;
END IF;
END LOOP;
END;
/
DECLARE
JOBNO NUMBER;
JOB_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO JOB_COUNT FROM SYS_JOBS WHERE WHAT LIKE ‘%GATHER_DB_STATS%’;
IF JOB_COUNT = 0 THEN
DBMS_JOB.SUBMIT(JOBNO,‘GATHER_DB_STATS(estimate_percent=>10, force=>FALSE);’, TRUNC(SYSDATE+1) + 1/24, ‘TRUNC(sysdate+1) +1/24’);
DBMS_JOB.BROKEN(JOBNO,true);
COMMIT;
END IF;
END;
/
DECLARE
JOBNO NUMBER;
JOB_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO JOB_COUNT FROM SYS_JOBS WHERE WHAT LIKE ‘%GATHER_CHANGE_STATS%’;
IF JOB_COUNT = 0 THEN
DBMS_JOB.SUBMIT(JOBNO,‘GATHER_CHANGE_STATS(estimate_percent=>10, change_percent=>10, force=>FALSE);’, SYSDATE, ‘SYSDATE+15/24/60’);
DBMS_JOB.BROKEN(JOBNO,true);
COMMIT;
END IF;
END;
/
