在数据仓库环境中,分区表会非常多,尤其是范围分区的表。为了确保业务不受影响,需要确保分区表的分区的边界足够大,以免导入数据时会出错。
为了避免出错,很多人设置了maxvalue或者提早分配很多个分区,这些都是不好的习惯。
设置MAXVALUE可能会导致两个问题:
1) 产生垃圾数据
2) 因为疏忽导致MAXVALUE的分区变得巨大,给后续的数据管理带来麻烦
提早分配很多个分区也会导致一些问题:
1) 也可能会产生垃圾数据
2) 不是按需分配可能会导致后续的一系列问题,如因为数据量变化而修改分区方式等
更好的做法是只提前分配少量的分区,并密切监控分区的扩展情况。那么,如何监控分区是否会溢出边界呢?我的做法是写一段代码实时监控分区的边界及当前时间的差异,如果边界接近溢出,则告警。
这里比较麻烦的是处理high_value,它是一个long类型的字段,下面是我的做法。
[@more@] --适用场景:按时间作分区的表。分区字段数据类型可以使DATE,也可以使VARCHAR2。如果分区字段是VARCHAR2,则键值的格式为YYYY-MM-DD。
在我的数据仓库环境中,有大量的这种分区表存在,且分区表都是严格按照标准的设置的,所以可以用以下代码监控。
-- CREATE TEMPORATY TABLE
CREATE GLOBAL TEMPORARY TABLE TEMP_PARTITION_MONITOR
(OWNER VARCHAR2(32),TABLE_NAME VARCHAR2(32),PARTITION_NAME VARCHAR2(32),DATA_TYPE VARCHAR2(20),HIGH_VALUE VARCHAR2(128))
ON COMMIT PRESERVE ROWS;
CREATE OR REPLACE PROCEDURE P_MONITOR_PARTITION(P_PRE_DAYS NUMBER DEFAULT 7) IS
L_CNT INT;
L_BEHIND_DAYS INT := 90;
BEGIN
/*
P_PRE_DAYS: 设定提前多少天预警
*/
FOR C IN (SELECT A.*, K.OWNER, K.NAME, K.COLUMN_NAME, TC.DATA_TYPE
FROM (SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM (SELECT /*+ rule */
T1.TABLE_NAME, T1.PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE, ROW_NUMBER() OVER(PARTITION BY T1.TABLE_OWNER, T1.TABLE_NAME ORDER BY PARTITION_POSITION DESC) RN
FROM DBA_TAB_PARTITIONS T1, DBA_PART_TABLES T2
WHERE T1.TABLE_OWNER = 'OWNER' AND
T1.TABLE_OWNER = T2.OWNER AND
T1.TABLE_NAME = T2.TABLE_NAME AND
T2.PARTITIONING_TYPE = 'RANGE')
WHERE RN = 1) A, DBA_PART_KEY_COLUMNS K, DBA_TAB_COLUMNS TC
WHERE K.OWNER = 'OWNER' AND K.NAME = A.TABLE_NAME AND
K.COLUMN_POSITION = 1 AND K.OBJECT_TYPE = 'TABLE' AND
K.OWNER = TC.OWNER AND K.NAME = TC.TABLE_NAME AND
K.COLUMN_NAME = TC.COLUMN_NAME AND
TC.DATA_TYPE NOT IN ('NUMBER')) LOOP
--我关心的信息比较多,所以SQL会比较长
INSERT INTO TEMP_PARTITION_MONITOR
VALUES
(C.OWNER, C.TABLE_NAME, C.PARTITION_NAME, C.DATA_TYPE, REPLACE(REPLACE(C.HIGH_VALUE, '''', ''), ' ', ''));
END LOOP;
SELECT COUNT(1)
INTO L_CNT
FROM TEMP_PARTITION_MONITOR T
WHERE (DATA_TYPE IN ('DATE') OR DATA_TYPE LIKE '%CHAR%') AND
HIGH_VALUE <> 'MAXVALUE' AND CASE WHEN
DATA_TYPE LIKE '%CHAR%' THEN HIGH_VALUE ELSE SUBSTR(HIGH_VALUE, INSTR(HIGH_VALUE, '(') + 1, INSTR(HIGH_VALUE, ',') - INSTR(HIGH_VALUE, '(') - 9) END > TO_CHAR(SYSDATE - L_BEHIND_DAYS, 'YYYY-MM-DD') AND CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN HIGH_VALUE ELSE SUBSTR(HIGH_VALUE, INSTR(HIGH_VALUE, '(') + 1, INSTR(HIGH_VALUE, ',') - INSTR(HIGH_VALUE, '(') - 9) END < TO_CHAR(SYSDATE + P_PRE_DAYS, 'YYYY-MM-DD');
IF L_CNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(L_CNT || ' tables will run out of range in ' ||
P_PRE_DAYS || ' days');
--可以在这里添加发送邮件或者短信的代码,自动告警
END IF;
END P_MONITOR_PARTITION;
提交一个定时任务,让它定期运行就可以实现分区边界溢出的自动化监控了。
为了避免出错,很多人设置了maxvalue或者提早分配很多个分区,这些都是不好的习惯。
设置MAXVALUE可能会导致两个问题:
1) 产生垃圾数据
2) 因为疏忽导致MAXVALUE的分区变得巨大,给后续的数据管理带来麻烦
提早分配很多个分区也会导致一些问题:
1) 也可能会产生垃圾数据
2) 不是按需分配可能会导致后续的一系列问题,如因为数据量变化而修改分区方式等
更好的做法是只提前分配少量的分区,并密切监控分区的扩展情况。那么,如何监控分区是否会溢出边界呢?我的做法是写一段代码实时监控分区的边界及当前时间的差异,如果边界接近溢出,则告警。
这里比较麻烦的是处理high_value,它是一个long类型的字段,下面是我的做法。
[@more@] --适用场景:按时间作分区的表。分区字段数据类型可以使DATE,也可以使VARCHAR2。如果分区字段是VARCHAR2,则键值的格式为YYYY-MM-DD。
在我的数据仓库环境中,有大量的这种分区表存在,且分区表都是严格按照标准的设置的,所以可以用以下代码监控。
-- CREATE TEMPORATY TABLE
CREATE GLOBAL TEMPORARY TABLE TEMP_PARTITION_MONITOR
(OWNER VARCHAR2(32),TABLE_NAME VARCHAR2(32),PARTITION_NAME VARCHAR2(32),DATA_TYPE VARCHAR2(20),HIGH_VALUE VARCHAR2(128))
ON COMMIT PRESERVE ROWS;
CREATE OR REPLACE PROCEDURE P_MONITOR_PARTITION(P_PRE_DAYS NUMBER DEFAULT 7) IS
L_CNT INT;
L_BEHIND_DAYS INT := 90;
BEGIN
/*
P_PRE_DAYS: 设定提前多少天预警
*/
FOR C IN (SELECT A.*, K.OWNER, K.NAME, K.COLUMN_NAME, TC.DATA_TYPE
FROM (SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM (SELECT /*+ rule */
T1.TABLE_NAME, T1.PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE, ROW_NUMBER() OVER(PARTITION BY T1.TABLE_OWNER, T1.TABLE_NAME ORDER BY PARTITION_POSITION DESC) RN
FROM DBA_TAB_PARTITIONS T1, DBA_PART_TABLES T2
WHERE T1.TABLE_OWNER = 'OWNER' AND
T1.TABLE_OWNER = T2.OWNER AND
T1.TABLE_NAME = T2.TABLE_NAME AND
T2.PARTITIONING_TYPE = 'RANGE')
WHERE RN = 1) A, DBA_PART_KEY_COLUMNS K, DBA_TAB_COLUMNS TC
WHERE K.OWNER = 'OWNER' AND K.NAME = A.TABLE_NAME AND
K.COLUMN_POSITION = 1 AND K.OBJECT_TYPE = 'TABLE' AND
K.OWNER = TC.OWNER AND K.NAME = TC.TABLE_NAME AND
K.COLUMN_NAME = TC.COLUMN_NAME AND
TC.DATA_TYPE NOT IN ('NUMBER')) LOOP
--我关心的信息比较多,所以SQL会比较长
INSERT INTO TEMP_PARTITION_MONITOR
VALUES
(C.OWNER, C.TABLE_NAME, C.PARTITION_NAME, C.DATA_TYPE, REPLACE(REPLACE(C.HIGH_VALUE, '''', ''), ' ', ''));
END LOOP;
SELECT COUNT(1)
INTO L_CNT
FROM TEMP_PARTITION_MONITOR T
WHERE (DATA_TYPE IN ('DATE') OR DATA_TYPE LIKE '%CHAR%') AND
HIGH_VALUE <> 'MAXVALUE' AND CASE WHEN
DATA_TYPE LIKE '%CHAR%' THEN HIGH_VALUE ELSE SUBSTR(HIGH_VALUE, INSTR(HIGH_VALUE, '(') + 1, INSTR(HIGH_VALUE, ',') - INSTR(HIGH_VALUE, '(') - 9) END > TO_CHAR(SYSDATE - L_BEHIND_DAYS, 'YYYY-MM-DD') AND CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN HIGH_VALUE ELSE SUBSTR(HIGH_VALUE, INSTR(HIGH_VALUE, '(') + 1, INSTR(HIGH_VALUE, ',') - INSTR(HIGH_VALUE, '(') - 9) END < TO_CHAR(SYSDATE + P_PRE_DAYS, 'YYYY-MM-DD');
IF L_CNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(L_CNT || ' tables will run out of range in ' ||
P_PRE_DAYS || ' days');
--可以在这里添加发送邮件或者短信的代码,自动告警
END IF;
END P_MONITOR_PARTITION;
提交一个定时任务,让它定期运行就可以实现分区边界溢出的自动化监控了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-1040594/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/231499/viewspace-1040594/