DBMS_WORKLOAD_REPOSITORY 包

数据库快照是一个只读的静态的数据库。DM快照功能是基于数据库实现的,每个快照是基于数据库的只读镜像。通过检索快照,可以获取源数据库在快照创建时间点的相关数据信息。

为了方便管理自动工作集负载信息库AWR(Automatic Workload Repository)的信息,系统为其所有重要统计信息和负载信息执行一次快照,并将这些快照存储在AWR中。AWR功能默认是关闭的,如果需要开启,则调用DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL过程设置快照的间隔时间。DBMS_WORKLOAD_REPOSITORY包还负责snapshot(快照)的管理。

DM数据库在创建该包时,默认创建一个名为SYSAUX的表空间,对应的数据文件为SYSAWR.DBF,该表空间用于存储该包生成快照的数据。如果该包被删除,那么SYSAUX表空间也对应地被删除。

DM MPP环境下不支持DBMS_WORKLOAD_REPOSITORY包。

23.1 相关方法

  1. AWR_CLEAR_HISTORY();

清理之前的所有snapshot记录。

语法如下:

PROCEDURE AWR_CLEAR_HISTORY();
  1. AWR_SET_INTERVAL();

设置生成snapshot的时间间隔。

语法如下:

PROCEDURE AWR_SET_INTERVAL(

AWR_INTERVAL IN INT DEFAULT 60

);

参数详解

  • AWR_INTERVAL

时间间隔,单位分钟。有效范围为 [10, 525600],默认值为60。参数为0时,关闭快照(关闭时参数值为40150分钟(110年),是一个无效的值)。

  1. AWR_REPORT_HTML

生成html格式的报告

定义1:

FUNCTION AWR_REPORT_HTML(

	START_SNAP_ID IN INT,

	END_SNAP_ID IN INT

)RETURN AWRRPT_ROW_TYPE PIPELINED;

参数详解

  • START_SNAP_ID为起始snapshot_id。
  • End_snap_id为终止snapshot_id。

返回值

返回包含报告的全部html脚本信息的嵌套表类型AWRRPT_ROW_TYPE。

把awr数据报表生成到指定路径的html文件。

定义2:

PROCEDURE SYS.AWR_REPORT_HTML(

	START_ID IN INT,

	END_ID IN INT,

	DEST_DIR IN VARCHAR(128),

	DEST_FILE IN VARCHAR(128)

);

参数详解

  • Start_ID为起始snapshot_id。
  • End_id为终止snapshot_id。
  • DEST_DIR为指定生成报告的目标路径。
  • DEST_FILE为指定生成报告的目标文件名,文件名需要以.htm和.html结尾。
  1. AWR_REPORT_TEXT

生成text格式的报告

定义1:

FUNCTION AWR_REPORT_TEXT(

	START_SNAP_ID IN INT,

	END_SNAP_ID IN INT

) RETURN AWRRPT_ROW_TYPE PIPELINED;

参数详解

  • START_SNAP_ID为起始snapshot_id
  • End_snap_id为终止snapshot_id。

返回值

返回包含报告的全部text脚本信息的嵌套表类型AWRRPT_ROW_TYPE。

把awr数据报表生成到指定路径的text文件。

定义2:

PROCEDURE SYS.AWR_REPORT_TEXT(

	START_ID IN INT,

	END_ID IN INT,

	DEST_DIR IN VARCHAR(128),

	DEST_FILE IN VARCHAR(128)

);

参数详解

  • Start_ID为起始snapshot_id。
  • End_id为终止snapshot_id。
  • DEST_DIR为指定生成报告的目标路径。
  • DEST_FILE为指定生成报告的目标文件名,文件名需要以.txt结尾。
  1. CREATE_SNAPSHOT

创建一次快照snapshot

语法如下:

FUNCTION CREATE_SNAPSHOT(

	FLUSH_LEVEL IN VARCHAR2 DEFAULT 'TYPICAL'

) RETURN INT;

参数详解

  • FLUSH_LEVEL

    'TYPICAL' OR 'ALL';如果为空,则默认为'TYPICAL',该值会影响快照生成数据的大小,如果是'ALL',则将全部历史数据保存,如果是'TYPICAL'则会刷部分数据,具体在后续会涉及到。

返回值

返回创建的快照ID值。

  1. DROP_SNAPSHOT_RANGE

删除SNAPSHOT。

语法如下:

PROCEDURE DROP_SNAPSHOT_RANGE(

	LOW_SNAP_ID IN INT,

	HIGH_SNAP_ID IN INT,

DBID IN INT DEFAULT NULL

);

参数详解

  • low_snap_idsnap_id范围的起始值。
  • high_snap_idsnap_id范围的结束值。
  • dbid
    表示snapshot所在的db唯一标识,默认为NULL,表示当前db,目前该参数不起作用。
  1. MODIFY_SNAPSHOT_SETTINGS

设置snapshot的属性值。

语法如下:

PROCEDURE MODIFY_SNAPSHOT_SETTINGS(

	RETENTION IN INT DEFAULT NULL,

	AWR_INTERVAL IN INT DEFAULT NULL,

	TOPNSQL IN INT DEFAULT NULL,

	DBID IN INT DEFAULT NULL

);

PROCEDURE MODIFY_SNAPSHOT_SETTINGS(

	RETENTION IN INT DEFAULT NULL,

	AWR_INTERVAL IN INT DEFAULT NULL,

	TOPNSQL IN VARCHAR2,

	DBID IN INT DEFAULT NULL

);

参数详解

  • RETENTION

表示snapshot在数据库中保留的时间,以分钟为单位,最小值为1天,最大值为100年;如果值为0,则表示永久保留;如果值为NULL,则表示本次设置的该值无效,保留以前的旧值。

  • AWR_INTERVAL

表示每次生成snapshot的间隔时间,以分钟为单位,最小值为10分钟,最大值为1年;如果值为0,则snapshot会失效。如果值为NULL,则表示本次设置的该值无效,保留以前的旧值。

  • topnsql

如果为NULL,则保留当前设置的值。

如果为INT类型,则表示按照sql的衡量标准(执行时间,cpu时间,消耗内存等)获取保存的sql个数,最小值为30,最大值为50000。

如果为varchar2类型,则可以设置如下3个值:DEFAULT、MAXIMUM和N。DEFAULT对应值为100;MAXIMUM对应值为50000;值N是数字串,但要求转化为INT类型之后,值必须在30至50000之间。

目前该参数不起作用。

  • dbid

表示snapshot所在的db唯一标识,默认为NULL,表示当前db。目前该参数不起作用。

23.2 系统表

DBMS_WORKLOAD_REPOSITORY包相关的系统表包括快照信息管理表及快照信息表,这些系统表都只有创建了DBMS_WORKLOAD_REPOSITORY包后才能查看。

23.2.1 快照信息管理表

  1. SYS.WRM$_WR_CONTROL

记录快照的相关控制信息。

列名 类型 说明
DBID INTEGER 数据库id(NULL)
SNAP_INTERVAL INTERVAL DAY(5) TO SECOND(1) 快照间隔
RETENTION INTERVAL DAY(5) TO SECOND(1) 快照保留时间(单位:INTERVAL DAY TO SECOND类型)
TOPSQL INTEGER 每次快照搜集sql条数
STATUS_FLA INTEGER AWR状态(1:开启;0:关闭)
  1. SYS.WRM$_SNAPSHOT

记录快照的相关信息。

列名 类型 说明
SNAP_ID INTEGER 快照ID
DBID INTEGER 数据库ID(NULL)
INSTANCE_NUMBER INTEGER 实例个数,恒为1
STARTUP_TIME DATETIME(6) 启动时间
BEGIN_INTERVAL_TIME DATETIME(6) 开始快照时间
END_INTERVAL_TIME DATETIME(6) 结束快照时间
SNAP_LEVEL INTEGER 快照等级,恒为1

23.2.2 快照信息表

快照信息表记录快照中数据库的一些具体信息。

  1. SYS.WRH$_RESOURCE_LIMIT

显示所有快照中表、用户的空间限制信息。

列名 类型 说明
SNAP_ID INTEGER 快照ID
NAME VARCHAR(128) 表/用户名
ID INTEGER 表/用户ID
TYPE VARCHAR(64) 表/用户类型
SPACE_LIMIT INTEGER 空间限制大小,以页为单位
SPACE_USED INTEGER 空间实际使用大小,以页为单位
  1. SYS.WRH$_SQL_HISTORY

当INI参数ENABLE_MONITOR=1时,显示所有快照中执行sql的历史记录信息,其中不同快照的SQL_ID和START_TIME肯定不相同。

列名 类型 说明
SNAP_ID INTEGER 快照ID
SQL_ID INTEGER 当前语句的SQL ID
START_TIME DATETIME(6) SQL执行的起始时间
TIME_USED BIGINT SQL执行所使用时间(单位:usec)
BYTES_DYNAMIC_ALLOCED BIGINT 动态分配字节数
N_LOGIC_READ INTEGER 语句逻辑读的次数
N_PHY_READ INTEGER 语句物理读的次数
AFFECTED_ROWS INTEGER 语句影响的行数
HARD_PARSE_FLAG INTEGER 语句硬解析标记,0:软解析;1:语义解析;2:硬解析
MPP_EXEC_ID INTEGER MPP会话句柄上的执行序号,同一个会话上的每个节点上值相同
  1. SYS.WRH$_SQLTEXT

显示所有快照的缓冲区中的SQL语句信息。

列名 类型 说明
SNAP_ID INTEGER 快照ID
SQL_ID INTEGER 语句的SQL ID
N_EXEC INTEGER 语句执行次数
SQL_TEXT TEXT SQL语句内容,超过7168的串不分行显示,而是显示成一行
SQL_NTH INTEGER SQL语句段号(从0开始)
  1. SYS.WRH$_SQLTEXT_TMP

SYS.WRH$_SQLTEXT的辅助表,显示所有快照中缓冲区中的SQL语句信息。

列名 类型 说明
SNAP_ID INTEGER 快照ID
SQL_ID INTEGER 语句的SQL ID
SQL_TEXT TEXT SQL语句内容,与v$SQLTEXT里的一致,超过7168的串分行显示
SQL_NTH INTEGER SQL语句段号(从0开始)
  1. SYS.WRH$_SESSIONS

显示所有快照中会话的具体信息,如执行的sql语句、主库名、当前会话状态、用户名等。

列名 类型 说明
SNAP_ID INTEGER 快照ID
N_SESSIONS TEXT 会话数
N_STMT INTEGER 已使用的STMT数量之和
  1. SYS.WRH$_SESSION_WAIT_HISTORY

显示所有快照中会话等待事件的历史信息。

列名 类型 说明
SNAP_ID INTEGER 快照ID
SQL_ID INTEGER 语句的SQL ID
EVENT## SMALLINT 事件编号
EVENT VARCHAR(32) 事件名称
TIME_WAITED_MICRO INTEGER 等待时间(单位:微秒)
WAIT_CLASS## SMALLINT 等待事件类别编号

快照信息表共有37张表,除了上述6张快照信息表与其后缀名相同的动态性能视图在结构上有些许不同,其余的快照信息表仅比对应的动态性能视图多一列名为SNAP_ID的整型列,其余结构完全相同,如SYS.WRH​_ARCH_QUEUE仅比VARCH_QUEUE多一列SNAP_ID,这里不再赘述,仅给出快照信息表的名称,具体可参考《DM8系统管理员手册》附录2。

剩余的快照信息表名称如下:

  1. SYS.WRH$_ARCH_QUEUE
  2. SYS.WRH$_BUFFERPOOL
  3. SYS.WRH$_CKPT_HISTORY
  4. SYS.WRH$_DATAFILE
  5. SYS.WRH$_DB_CACHE
  6. SYS.WRH$_DEADLOCK_HISTORY
  7. SYS.WRH$_DMSQL_EXEC_TIME
  8. SYS.WRH$_DM_INI
  9. SYS.WRH$_HASH_MERGE_USED_HISTORY
  10. SYS.WRH$_LARGE_MEM_SQLS
  11. SYS.WRH$_LOCK
  12. SYS.WRH$_MAL_INFO
  13. SYS.WRH$_MEM_POOL
  14. SYS.WRH$_MTAB_USED_HISTORY
  15. SYS.WRH$_PSEG_ITEMS
  16. SYS.WRH$_RLOG
  17. SYS.WRH$_SCP_CACHE
  18. SYS.WRH$_SESSION_HISTORY
  19. SYS.WRH$_SESSION_STAT
  20. SYS.WRH$_SORT_HISTORY
  21. SYS.WRH$_SQL_NODE_HISTORY
  22. SYS.WRH$_SQL_PLAN_NODE
  23. SYS.WRH$_SQL_STAT_HISTORY
  24. SYS.WRH$_SYSSTAT
  25. SYS.WRH$_SYSTEMINFO
  26. SYS.WRH$_SYSTEM_EVENT
  27. SYS.WRH$_TABLESPACE
  28. SYS.WRH$_TASK_QUEUE
  29. SYS.WRH$_TRACE_QUEUE
  30. SYS.WRH$_VIRTUAL_MACHINE
  31. SYS.WRH$_WAIT_HISTORY

23.3 创建、检测、删除语句

23.3.1 SP_INIT_AWR_SYS

创建或删除DBMS_WORKLOAD_REPOSITORY系统包。

语法如下:

void

SP_INIT_AWR_SYS(

	CREATE_FLAG int

)

参数详解

  • CREATE_FLAG

为1时表示创建DBMS_WORKLOAD_REPOSITORY包;为0表示删除该系统包。

返回值

举例说明

创建DBMS_WORKLOAD_REPOSITORY系统包。

SP_INIT_AWR_SYS(1);

23.3.2 SF_CHECK_AWR_SYS

系统的DBMS_WORKLOAD_REPOSITORY系统包启用状态检测。

语法如下:

int

SF_CHECK_AWR_SYS ()

返回值

0:未启用;1:已启用

举例说明

获得DBMS_WORKLOAD_REPOSITORY系统包的启用状态。

SELECT SF_CHECK_AWR_SYS;

23.4 举例说明

用户在使用DBMS_WORKLOAD_REPOSITORY包之前,需要提前调用系统过程并设置间隔时间:

SP_INIT_AWR_SYS(1);

下面语句设置间隔为10分钟,也可以是其他值:

CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);

设置成功后,可以使用CREATE_SNAPSHOT手动创建快照,也可以等待设置的间隔时间后系统自动创建快照,快照id从1开始递增:

手动创建快照:

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

查看创建的快照信息,包括快照id:

SELECT * FROM SYS.WRM$_SNAPSHOT;

查看snapshot的id在1~2范围内的AWR分析报告的带html格式的内容。然后复制到文本文件中,保存成html格式即可查看。

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));

把snapshot的id在1~2范围内的AWR分析报告生成到c盘awr1.html文件。

SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');

通过DMBS_WORKLOAD_REPOSITORY包还可以对快照本身做增删改操作。

例1删除id在22~32之间的snapshot。

CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(22,32);

例2修改snapshot的间隔时间为30分钟、保留时间为1天。

CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,30);

查询设置后快照参数。

SELECT * FROM SYS.WRM$_WR_CONTROL;

例3 创建一次snapshot。

CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

例4 清理全部snapshot。

CALL DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY();

例5 设置snapshot的间隔为10分钟。

CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10); 
微信扫码
分享文档
扫一扫
联系客服