2. 首页 > Oracle教程 > 正文

Oracle教程FG295-Oracle指标与阈值实战

教程整理：风哥教程  |  更新时间：2026-01-10  |  教程分类：Oracle教程  |  文档学习：9

内容大纲

内容简介：本文主要介绍Oracle数据库的指标与阈值管理，包括指标的定义、监控、阈值设置和告警。风哥教程参考Oracle官方文档指标与阈值相关内容，为生产环境提供完整的指标与阈值管理解决方案。

Part01-基础概念与理论知识

1.1 指标与阈值概念

Oracle指标是用于监控数据库性能和健康状况的度量标准，包括系统指标、会话指标、SQL指标等。阈值是指指标的临界值，当指标超过阈值时，系统会触发告警。指标与阈值管理是Oracle数据库监控的重要组成部分，能够帮助DBA及时发现和解决数据库问题。

1.2 指标类型

  • 系统指标：如CPU使用率、内存使用率、I/O等待时间等
  • 数据库指标：如缓冲区缓存命中率、共享池使用率、重做日志切换频率等
  • 会话指标：如会话数量、活跃会话数、会话等待时间等
  • SQL指标：如SQL执行时间、SQL执行计划、SQL资源使用情况等
  • 存储指标：如表空间使用率、数据文件大小、备份状态等

1.3 阈值设置方法

  • 基于经验值：根据DBA的经验设置阈值
  • 基于历史数据：根据历史数据的统计分析设置阈值
  • 基于Oracle建议：根据Oracle官方建议设置阈值
  • 基于业务需求：根据业务需求和SLA设置阈值

Part02-生产环境规划与建议

2.1 指标与阈值规划

制定合理的指标与阈值规划：

  • 识别关键指标
  • 设置合理的阈值
  • 建立指标监控的流程和规范
  • 定期调整阈值
  • 跟踪指标与阈值的效果

2.2 指标与阈值建议

指标与阈值建议：

  • 根据数据库类型和负载设置合理的阈值
  • 定期监控指标，及时发现异常
  • 结合其他监控工具，全面分析数据库状态
  • 根据业务需求调整阈值
  • 建立指标与阈值的审核机制

2.3 指标与阈值结果管理

指标与阈值结果管理建议：

  • 保存指标与阈值的历史数据
  • 建立指标与阈值的审核机制
  • 跟踪指标的变化趋势
  • 分析指标异常的原因
  • 与开发团队分享指标与阈值结果，提高应用程序性能

Part03-生产环境项目实施方案

3.1 指标与阈值配置与管理

# 1. 查看当前指标与阈值配置
SQL> SELECT * FROM dba_thresholds;

# 2. 设置表空间使用率阈值
SQL> EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
warning_value => ’80’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
critical_value => ’90’,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => ‘USERS’
);

# 3. 设置CPU使用率阈值
SQL> EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.CPU_USAGE_PERCENT,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
warning_value => ’70’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
critical_value => ’90’,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM,
object_name => NULL
);

# 4. 设置会话数量阈值
SQL> EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.ACTIVE_SESSIONS,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
warning_value => ‘100’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
critical_value => ‘150’,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM,
object_name => NULL
);

# 5. 查看设置的阈值
SQL> SELECT * FROM dba_thresholds;

3.2 指标监控

# 1. 查看系统指标
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%CPU%’;
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%Memory%’;
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%I/O%’;

# 2. 查看数据库指标
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%Buffer Cache%’;
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%Shared Pool%’;
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%Redo%’;

# 3. 查看会话指标
SQL> SELECT count(*) FROM v$session WHERE status = ‘ACTIVE’;
SQL> SELECT event, count(*) FROM v$session_wait GROUP BY event ORDER BY count(*) DESC;

# 4. 查看SQL指标
SQL> SELECT * FROM v$SQL WHERE executions > 1000 ORDER BY elapsed_time DESC;

# 5. 查看存储指标
SQL> SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;

# 6. 查看告警历史
SQL> SELECT * FROM dba_outstanding_alerts;
SQL> SELECT * FROM dba_alert_history;

3.3 指标与阈值调优

# 1. 分析指标数据
# 查看指标的历史数据，分析变化趋势

# 2. 调整阈值设置
# 根据分析结果调整阈值
SQL> EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
warning_value => ’75’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
critical_value => ’85’,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => ‘USERS’
);

# 3. 验证阈值调整效果
# 查看调整后的阈值和告警情况

# 4. 优化指标监控
# 根据业务需求优化指标监控策略

3.4 指标与阈值结果管理

# 1. 保存指标与阈值历史数据
# 将指标数据保存到表中，用于后续分析

# 2. 建立指标与阈值的审核机制
# 定期审核指标与阈值设置，确保合理

# 3. 跟踪指标的变化趋势
# 分析指标的变化趋势，预测可能的问题

# 4. 分析指标异常的原因
# 识别指标异常的原因，采取相应的措施

# 5. 与开发团队分享指标与阈值结果
# 提供指标与阈值结果给开发团队，帮助优化应用程序

Part04-生产案例与实战讲解

4.1 指标与阈值配置与管理实战

# 1. 连接数据库
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Apr 4 03:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

# 2. 查看当前指标与阈值配置
SQL> SELECT * FROM dba_thresholds;

METRICS_ID METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE OBSERVATION_PERIOD CONSECUTIVE_OCCURRENCES INSTANCE_NAME OBJECT_TYPE OBJECT_NAME
———- ———————————– —————- ————- —————– ————– —————— ———————— ————- ——————– ————
1 Tablespace Space Usage (%) GREATER 80 GREATER 90 1 1 USERS
2 CPU Usage (%) GREATER 70 GREATER 90 1 1
3 Active Sessions GREATER 100 GREATER 150 1 1

# 3. 设置表空间使用率阈值
SQL> EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
warning_value => ’80’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
critical_value => ’90’,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => ‘SYSTEM’
);

PL/SQL procedure successfully completed.

# 4. 查看设置的阈值
SQL> SELECT * FROM dba_thresholds WHERE object_name = ‘SYSTEM’;

METRICS_ID METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE OBSERVATION_PERIOD CONSECUTIVE_OCCURRENCES INSTANCE_NAME OBJECT_TYPE OBJECT_NAME
———- ———————————– —————- ————- —————– ————– —————— ———————— ————- ——————– ————
1 Tablespace Space Usage (%) GREATER 80 GREATER 90 1 1 SYSTEM

4.2 指标监控与分析实战

# 1. 查看系统指标
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%CPU%’;

METRIC_ID METRIC_NAME GROUP_ID GROUP_NAME SUBGROUP_ID SUBGROUP_NAME UNIT_NAME VALUE METRIC_UNIT
——— ——————————– ——– ————— ———— —————- ————— ———- ————
123 CPU Usage Per Sec 1 System Metrics 1 CPU % 45 %
124 CPU Usage Per Txn 1 System Metrics 1 CPU %/txn 0.5 %/txn

# 2. 查看数据库指标
SQL> SELECT * FROM v$sysmetric WHERE metric_name LIKE ‘%Buffer Cache%’;

METRIC_ID METRIC_NAME GROUP_ID GROUP_NAME SUBGROUP_ID SUBGROUP_NAME UNIT_NAME VALUE METRIC_UNIT
——— ——————————– ——– ————— ———— —————- ————— ———- ————
145 Buffer Cache Hit Ratio 1 System Metrics 2 Buffer Cache % 95 %
146 Buffer Cache Read Hit Ratio 1 System Metrics 2 Buffer Cache % 92 %

# 3. 查看会话指标
SQL> SELECT count(*) FROM v$session WHERE status = ‘ACTIVE’;

COUNT(*)
———-
56

SQL> SELECT event, count(*) FROM v$session_wait GROUP BY event ORDER BY count(*) DESC;

EVENT COUNT(*)
———————————– ———-
SQL*Net message from client 34
db file sequential read 12
db file scattered read 5
latch: shared pool 3

# 4. 查看存储指标
SQL> SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_PERCENT
—————————— ————
SYSTEM 65.2
SYSAUX 58.7
UNDOTBS1 45.3
TEMP 23.1
USERS 78.9

# 5. 查看告警历史
SQL> SELECT * FROM dba_outstanding_alerts;

no rows selected

SQL> SELECT * FROM dba_alert_history;

no rows selected

4.3 指标与阈值调优实战

# 1. 分析指标数据
# 查看表空间使用率
SQL> SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_PERCENT
—————————— ————
SYSTEM 65.2
SYSAUX 58.7
UNDOTBS1 45.3
TEMP 23.1
USERS 78.9

# 2. 调整表空间使用率阈值
# USERS表空间使用率接近80%，调整阈值
SQL> EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
warning_value => ’75’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GREATER,
critical_value => ’85’,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => ‘USERS’
);

PL/SQL procedure successfully completed.

# 3. 验证阈值调整效果
SQL> SELECT * FROM dba_thresholds WHERE object_name = ‘USERS’;

METRICS_ID METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE OBSERVATION_PERIOD CONSECUTIVE_OCCURRENCES INSTANCE_NAME OBJECT_TYPE OBJECT_NAME
———- ———————————– —————- ————- —————– ————– —————— ———————— ————- ——————– ————
1 Tablespace Space Usage (%) GREATER 75 GREATER 85 1 1 USERS

# 4. 模拟表空间使用率告警
# 向USERS表空间插入数据，使使用率超过75%
SQL> CREATE TABLE fgedu.test_table (id NUMBER, name VARCHAR2(100)) TABLESPACE USERS;

Table created.

SQL> BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO fgedu.test_table VALUES (i, ‘Test data ‘ || i);
IF MOD(i, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/

PL/SQL procedure successfully completed.

# 5. 查看告警
SQL> SELECT * FROM dba_outstanding_alerts;

SEQUENCE_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE MESSAGE_TYPE MESSAGE_GROUP MESSAGE_LEVEL REASON_TIME REASON_SIGNAL REASON_ARGUMENT1 REASON_ARGUMENT2 REASON_ARGUMENT3 REASON_ARGUMENT4 REASON_ARGUMENT5 STATUS RESOLUTION_TIME RESOLUTION_SIGNAL RESOLUTION_OWNER RESOLUTION_OBJECT_NAME RESOLUTION_SUBOBJECT_NAME
———– ——– ————— ————— ——————— ———— ————— ————- —————– ————- —————– —————– —————– —————– —————– ——- —————– —————– —————- ————————- —————————
1 SYS USERS Tablespace 2 Space 5 2026-04-04 03:30:00 USERS 78.9 85 ACTIVE

Part05-风哥经验总结与分享

5.1 指标与阈值管理最佳实践

  • 识别关键指标：根据数据库类型和负载识别关键指标
  • 设置合理阈值：根据业务需求和历史数据设置合理的阈值
  • 定期监控：定期监控指标，及时发现异常
  • 分析异常：分析指标异常的原因，采取相应的措施
  • 持续优化：根据业务需求和数据库负载持续优化指标与阈值

5.2 指标与阈值管理注意事项

  • 根据数据库类型和负载设置合理的阈值
  • 定期监控指标，及时发现异常
  • 结合其他监控工具，全面分析数据库状态
  • 根据业务需求调整阈值
  • 建立指标与阈值的审核机制

5.3 指标与阈值管理建议

  • 建立指标与阈值管理流程，定期执行指标监控和分析
  • 培训DBA，提高指标与阈值管理能力
  • 建立指标与阈值的审核机制
  • 跟踪指标的变化趋势
  • 与Oracle支持团队保持沟通，获取指标与阈值管理的最佳实践

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息