Oracle 数据库 自动工作负载仓库

Oracle 数据库阿木 发布于 2025-07-08 28 次阅读

摘要：

随着企业业务的发展，数据库工作负载日益复杂，如何高效地管理和分析数据库工作负载成为数据库管理员和开发人员关注的焦点。本文将围绕Oracle数据库，探讨如何构建一个自动工作负载仓库，并对其实现和优化进行详细阐述。

一、

工作负载仓库（Workload Repository，简称WLM）是Oracle数据库提供的一种用于收集、存储和分析数据库工作负载的工具。通过WLM，管理员和开发人员可以深入了解数据库的性能，优化数据库配置，提高系统性能。

本文将介绍如何使用Oracle数据库构建一个自动工作负载仓库，包括数据收集、存储、分析和优化等方面。

二、自动工作负载仓库的构建

1. 数据收集

（1）配置WLM

在Oracle数据库中，首先需要配置WLM，包括创建工作负载管理器（Workload Manager，简称WLM）和定义工作负载分类（Workload Class）。

sql
-- 创建WLM

BEGIN

  DBMS_WORKLOAD_REPOSITORY.CREATE_WORKLOAD_MANAGER('WLM_MGR', 'WLM Manager');

END;

/



-- 创建工作负载分类

BEGIN

  DBMS_WORKLOAD_REPOSITORY.CREATE_CLASS('WLM_CLASS', 'WLM Class');

END;

/



-- 将工作负载分类分配给WLM

BEGIN

  DBMS_WORKLOAD_REPOSITORY.ENABLE_CLASS('WLM_CLASS', 'WLM_MGR');

END;

/

（2）启用自动统计信息收集

为了收集工作负载数据，需要启用自动统计信息收集。

sql
-- 启用自动统计信息收集

ALTER SYSTEM SET WMGMT_AUTOTASK_START=TRUE;

2. 数据存储

（1）创建WLM仓库表

在数据库中创建WLM仓库表，用于存储收集到的数据。

sql
-- 创建WLM仓库表

CREATE TABLE wlm_repository (

  id NUMBER PRIMARY KEY,

  class VARCHAR2(30),

  sql_id VARCHAR2(13),

  sql_plan_hash VARCHAR2(16),

  session_id NUMBER,

  session_serial NUMBER,

  start_time TIMESTAMP,

  end_time TIMESTAMP,

  wait_class VARCHAR2(30),

  service_name VARCHAR2(30),

  sql_text CLOB

);

（2）创建WLM仓库视图

为了方便查询和分析数据，可以创建WLM仓库视图。

sql
-- 创建WLM仓库视图

CREATE VIEW v_wlm_repository AS

SELECT  FROM wlm_repository;

3. 数据分析

（1）查询WLM仓库数据

使用SQL语句查询WLM仓库数据，分析数据库工作负载。

sql
-- 查询特定SQL语句的执行情况

SELECT  FROM v_wlm_repository WHERE sql_id = 'some_sql_id';



-- 查询特定工作负载分类的执行情况

SELECT  FROM v_wlm_repository WHERE class = 'WLM_CLASS';

（2）使用Oracle SQL Developer Data Modeler分析数据

Oracle SQL Developer Data Modeler是一款可视化数据建模工具，可以方便地分析WLM仓库数据。

三、自动工作负载仓库的优化

1. 优化WLM仓库表结构

根据实际需求，对WLM仓库表结构进行调整，例如添加索引、分区等。

sql
-- 添加索引

CREATE INDEX idx_wlm_repository ON wlm_repository(sql_id);



-- 分区WLM仓库表

CREATE TABLE wlm_repository_part (

  id NUMBER PRIMARY KEY,

  class VARCHAR2(30),

  ...

) PARTITION BY RANGE (start_time) (

  PARTITION p1 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),

  PARTITION p2 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),

  ...

);

2. 优化WLM仓库数据收集

（1）调整自动统计信息收集参数

根据实际需求，调整自动统计信息收集参数，例如收集频率、收集时间等。

sql
-- 调整自动统计信息收集频率

ALTER SYSTEM SET WMGMT_AUTOTASK_FREQUENCY=60;

（2）优化WLM仓库数据清理策略

定期清理WLM仓库数据，释放空间，提高数据库性能。

```sql

-- 清理WLM仓库数据

BEGIN

DBMS_WORKLOAD_REPOSITORY.CLEANUP('WLM_MGR', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS', 'WLM_CLASS',