# 背景

根据现场dem发生的空间不足的问题，dem为老版本，特为项目制定了dem采集数据清理方案。

# 采集数据清理sql

清理相关的表部分
```
declare
a bigint;
begin
a=0;
select max(TS) into a FROM DEM.DMA_DATABASE_SQL_COUNT WHERE START_TIME < SYSDATE -60; 
if a is not null then
  delete from DEM.DMA_DATABASE_SQL_COUNT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_SQL_COUNT_SQL_TEXT WHERE ID NOT IN (SELECT     DISTINCT SQL_TEXT_ID from DEM.DMA_DATABASE_SQL_COUNT);
  commit;
  delete from DEM.DMA_DATABASE_SQL_ERR WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_SQL_SLOW WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_TABLE_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_TS WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_UD_SQL_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_USER WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_LOG_HISTORY WHERE TS <  a;
  commit;
  delete from DEM.DMA_DATABASE_RLOG_HISTORY WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_BACKUPSET WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_BACKUPSET_BKP WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_DISK WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_EXEC_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_PROCESS_INFO WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_STAT WHERE TS < a;
  commit;
END IF;
end;
```

收集相关表统计信息
```
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_SQL_COUNT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_SQL_COUNT_SQL_TEXT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_SQL_ERR', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_SQL_SLOW', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_STAT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_TABLE_STAT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_TS', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_UD_SQL_STAT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_USER', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_LOG_HISTORY', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_RLOG_HISTORY', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_BACKUPSET', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_DATABASE_BACKUPSET_BKP', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_MAINFRAME_DISK', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_MAINFRAME_EXEC_STAT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_MAINFRAME_PROCESS_INFO', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS('DEM', 'DMA_MAINFRAME_STAT', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
```

四、加入JOB计划，每周日晚1点执行清理60天前的采集数据
 
```
call SP_INIT_JOB_SYS(1);

call SP_CREATE_JOB('clearSJ',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('clearSJ');

call SP_ADD_JOB_STEP('clearSJ', 'step1', 0, 'declare
a bigint;
begin
a=0;
select max(TS) into a FROM DEM.DMA_DATABASE_SQL_COUNT WHERE START_TIME < SYSDATE -60; 
if a is not null then
  delete from DEM.DMA_DATABASE_SQL_COUNT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_SQL_COUNT_SQL_TEXT WHERE ID NOT IN (SELECT     DISTINCT SQL_TEXT_ID from DEM.DMA_DATABASE_SQL_COUNT);
  commit;
  delete from DEM.DMA_DATABASE_SQL_ERR WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_SQL_SLOW WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_TABLE_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_TS WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_UD_SQL_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_USER WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_LOG_HISTORY WHERE TS <  a;
  commit;
  delete from DEM.DMA_DATABASE_RLOG_HISTORY WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_BACKUPSET WHERE TS < a;
  commit;
  delete from DEM.DMA_DATABASE_BACKUPSET_BKP WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_DISK WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_EXEC_STAT WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_PROCESS_INFO WHERE TS < a;
  commit;
  delete from DEM.DMA_MAINFRAME_STAT WHERE TS < a;
  commit;
END IF;
end;', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_STEP('clearSJ', 'step2', 0, 'DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_SQL_COUNT'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_SQL_COUNT_SQL_TEXT'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_SQL_ERR'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_SQL_SLOW'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_STAT'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_TABLE_STAT'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_TS'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_UD_SQL_STAT'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_USER'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_LOG_HISTORY'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_RLOG_HISTORY'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_BACKUPSET'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');
DBMS_STATS.GATHER_TABLE_STATS(''DEM'', ''DMA_DATABASE_BACKUPSET_BKP'', NULL, 100, TRUE, ''FOR ALL COLUMNS SIZE AUTO'');', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('clearSJ', 'dd1', 1, 2, 1, 1, 0, '01:00:00', NULL, '2021-08-16 15:39:15', NULL, '');

call SP_JOB_CONFIG_COMMIT('clearSJ');
```

DM 武汉达梦数据库股份有限公司
24小时免费服务热线：400 991 6599
达梦技术社区：https://eco.dameng.com

ITPub博客

DEM采集数据清理

最新文章