登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›ORACLE数据挖掘之 MSET-SPRT

ORACLE数据挖掘之 MSET-SPRT

眸胝 3 天前
虽然是熟悉的Oracle数据库，但关于机器学习、数据挖掘这方面的知识笔者起初也是不了解的，文中MSET相关设置来源于同事提供的sample，在测试过程中边查资料边学习吸收，也因此看到了别样的Oracle。
Oracle 的 MSET-SPRT 主要用于 高精度异常检测和预测性维护，尤其适用于关键业务系统，如数据库、存储、工业控制系统和数据中心运营环境。
MSET-SPRT翻译成中文是：多变量状态估计技术 - 序列概率比检验。
个人感觉这个名词翻译过来有些绕口.. 下面还是以英文简写词来代替。
在ORACLE官方文档中，是这样描述 MSET-SPRT 的：
The Multivariate State Estimation Technique - Sequential Probability Ratio Test (MSET-SPRT) algorithm monitors critical processes and detects subtle anomalies.
下面记录下MSET的测试过程，这里直接复用之前用于测试TPC-H的环境来验证：

  • 1.新建测试表和配置表
  • 2.创建构造测试数据的存储过程
  • 3.模拟不同规模测试数据并插入
  • 4.创建数据挖掘模型
  • 5.查询预测结果
  • 6.其他测试建议
1.新建测试表和配置表

这里只选择有实际意义的字段，构建TBL_IOT表：
  1.   CREATE TABLE "TPCH"."TBL_IOT"
  2.    ("B1_T1" NUMBER(38,0),
  3.         "B1_T2" NUMBER(38,0),
  4.         "B1_T3" NUMBER(38,0),
  5.         "B2_T1" NUMBER(38,0),
  6.         "B2_T2" NUMBER(38,0),
  7.         "B2_T3" NUMBER(38,0),
  8.         "B3_T1" NUMBER(38,0),
  9.         "B3_T2" NUMBER(38,0),
  10.         "B3_T3" NUMBER(38,0),
  11.         "B4_T1" NUMBER(38,0),
  12.         "B4_T2" NUMBER(38,0),
  13.         "B4_T3" NUMBER(38,0),
  14.         "B5_T1" NUMBER(38,0),
  15.         "B5_T2" NUMBER(38,0),
  16.         "B5_T3" NUMBER(38,0),
  17.         "B6_T1" NUMBER(38,0),
  18.         "B6_T2" NUMBER(38,0),
  19.         "B6_T3" NUMBER(38,0),
  20.         "B7_T1" NUMBER(38,0),
  21.         "B7_T2" NUMBER(38,0),
  22.         "B7_T3" NUMBER(38,0),
  23.         "B8_T1" NUMBER(38,0),
  24.         "B8_T2" NUMBER(38,0),
  25.         "B8_T3" NUMBER(38,0),
  26.         "B9_T1" NUMBER(38,0),
  27.         "B9_T2" NUMBER(38,0),
  28.         "B9_T3" NUMBER(38,0),
  29.         "B10_T1" NUMBER(38,0),
  30.         "B10_T2" NUMBER(38,0),
  31.         "B10_T3" NUMBER(38,0),
  32.         "B11_T1" NUMBER(38,0),
  33.         "B11_T2" NUMBER(38,0),
  34.         "B11_T3" NUMBER(38,0),
  35.         "B12_T1" NUMBER(38,0),
  36.         "B12_T2" NUMBER(38,0),
  37.         "B12_T3" NUMBER(38,0),
  38.         "S" NUMBER(38,1),
  39.         "K_TS" TIMESTAMP (6)
  40.    );
复制代码
创建 MSET_IOT_SETTINGS 表，用于存储数据挖掘算法的配置，插入 MSET-SPRT 算法的关键参数：

  • 选择 MSET-SPRT 算法
  • 开启自动数据准备
  • 设置向量存储大小
  • 设定 Alpha（假阳性）概率
  • 设定异常警报阈值（次数 & 窗口大小）
  1. -- Create setting table        
  2. CREATE TABLE MSET_IOT_SETTINGS(SETTING_NAME VARCHAR2(30),
  3.                              SETTING_VALUE VARCHAR2(128));
  4. -- Populate setting table
  5. BEGIN
  6.   -- Select MSET-SPRT as the algorithm
  7.   INSERT INTO MSET_IOT_SETTINGS
  8.          VALUES(DBMS_DATA_MINING.ALGO_NAME,
  9.                 DBMS_DATA_MINING.ALGO_MSET_SPRT);
  10.   -- Turn on automatic data preparation   
  11.   INSERT INTO MSET_IOT_SETTINGS
  12.          VALUES(DBMS_DATA_MINING.PREP_AUTO,
  13.                 DBMS_DATA_MINING.PREP_AUTO_ON);
  14.   -- Set memory vector
  15.   INSERT INTO MSET_IOT_SETTINGS
  16.     VALUES(DBMS_DATA_MINING.MSET_MEMORY_VECTORS, 100);
  17.   -- Set alpha
  18.   INSERT INTO MSET_IOT_SETTINGS
  19.     VALUES(DBMS_DATA_MINING.MSET_ALPHA_PROB, 0.1);
  20.   -- Set alert count
  21.   INSERT INTO MSET_IOT_SETTINGS
  22.     VALUES(DBMS_DATA_MINING.MSET_ALERT_COUNT, 3);
  23.   -- Set alert window
  24.   INSERT INTO MSET_IOT_SETTINGS
  25.     VALUES(DBMS_DATA_MINING.MSET_ALERT_WINDOW, 5);  
  26.   -- Examples of other possible settings are:
  27.   -- (dbms_data_mining.mset_beta_prob, 0.1)
  28.   -- (dbms_data_mining.mset_adb_height, 0.01)
  29.   -- (dbms_data_mining.mset_std_tolerance, 3)
  30.   -- (dbms_data_mining.mset_heldaside, 500)
  31.   COMMIT;  
  32. END;
  33. /
复制代码
2.创建构造测试数据的存储过程

具体创建一个存储过程，传入行数作为参数，这样方便随时插入不同规模的数据。
这里模拟IOT场景，要求每秒一条数据。
插入数据前需要手工清空表，因为防止误操作，我没有将truncate表的高危命令直接写入到存储过程中，在执行存储过程之前，由人工来执行truncate表操作，用于插入数据的存储过程如下：
  1. CREATE OR REPLACE PROCEDURE INSERT_IOT_DATA(p_total_rows IN NUMBER) AS
  2.     v_start_time TIMESTAMP := TO_TIMESTAMP('2025-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
  3. BEGIN
  4.     FOR i IN 1..p_total_rows LOOP
  5.         INSERT INTO TBL_IOT (
  6.             B1_T1, B1_T2, B1_T3, B2_T1, B2_T2, B2_T3, B3_T1, B3_T2, B3_T3,
  7.             B4_T1, B4_T2, B4_T3, B5_T1, B5_T2, B5_T3, B6_T1, B6_T2, B6_T3,
  8.             B7_T1, B7_T2, B7_T3, B8_T1, B8_T2, B8_T3, B9_T1, B9_T2, B9_T3,
  9.             B10_T1, B10_T2, B10_T3, B11_T1, B11_T2, B11_T3, B12_T1, B12_T2, B12_T3,
  10.             S, K_TS
  11.         ) VALUES (
  12.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  13.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  14.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  15.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  16.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  17.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  18.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  19.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  20.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  21.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  22.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  23.             DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55), DBMS_RANDOM.VALUE(50, 55),
  24.             DBMS_RANDOM.VALUE(2, 5),
  25.             v_start_time + INTERVAL '1' SECOND * i
  26.         );
  27.         
  28.         IF MOD(i, 1000) = 0 THEN
  29.             COMMIT;
  30.         END IF;
  31.     END LOOP;
  32.    
  33.     COMMIT;
  34. END;
  35. /
复制代码
注意：这里构建数据我预期是插入50到55之间的随机整数，但使用DBMS_RANDOM.VALUE(50,55)，实际随机是带小数的，但结果是符合预期的。这是因为当向 NUMBER(38,0) 类型的列插入带小数的数据时，Oracle 并不会报错，而是会自动截断小数部分，只保留整数部分。可手工验证数据是否符合预期。
3.模拟不同规模测试数据并插入

下面需要分别测试不同数据规模：86w、130w、260w。
插入不同规模的数据之前，需要手工truncate表：
  1. -- 插入数据前，确保已清空表数据，高危操作手工执行：
  2. truncate table TBL_IOT;
  4. -- 按需求插入不同规模数据：
  5. -- 插入86万行
  6. EXEC INSERT_IOT_DATA(860000);
  8. -- 插入130万行
  9. EXEC INSERT_IOT_DATA(1300000);
  11. -- 插入260万行
  12. EXEC INSERT_IOT_DATA(2600000);
  14. -- 查询表占用空间
  15. select SEGMENT_NAME, BYTES / 1024 / 1024 "MB" from dba_segments where SEGMENT_NAME = 'TBL_IOT';
复制代码
4.创建数据挖掘模型

创建数据挖掘模型IOTMSET_MODEL，如果之前存在就删除掉再创建：
  1. -- 删除数据挖掘模型IOTMSET_MODEL
  2. exec dbms_data_mining.drop_model('IOTMSET_MODEL');
  4. -- 创建数据挖掘模型IOTMSET_MODEL
  5. BEGIN
  6.   dbms_data_mining.create_model(model_name => 'IOTMSET_MODEL',
  7.                mining_function   => 'CLASSIFICATION',
  8.                data_table_name => 'TBL_IOT', --表名
  9.                case_id_column_name => 'K_TS', --时间戳列名
  10.                target_column_name => '',
  11.                settings_table_name => 'mset_iot_settings'); --配置表名
  12. END;
  13. /
复制代码
创建模型完成后，会发现当前用户下的对象多了一系列以 DM$ 前缀命名的表和视图，这是 Oracle 数据挖掘（Oracle Data Mining, ODM）自动创建的模型存储对象，它们用于存储 MSET-SPRT 模型的相关数据。
5.查询预测结果

查询预测测试数据集的结果：
  1. SELECT rownum, K_TS, pred FROM (SELECT K_TS, prediction(IOTMSET_MODEL using *)
  2.   over (ORDER BY K_TS) pred FROM tbl_iot)
  3.   where pred < 1 --异常
  4.   order by 2, 1 ;
复制代码
6.其他测试建议

建议在测试开始和结束，创建awr的snapshot：

  • exec DBMS_WORKLOAD_REPOSITORY.create_snapshot();
测试完成获取AWR报告：

  • @?/rdbms/admin/awrrpt
  • awrrpt_1_220_221.html
测试结果略。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
眸胝
3 天前

0

粉丝关注

8

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫