Oracle教程FG332-性能模式

本文档风哥主要介绍Oracle数据库性能模式（Performance Schema）相关知识，包括性能模式的概念、原理、优势、规划、配置、管理、监控、优化等内容，由风哥教程参考Oracle官方文档Performance内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 性能模式的概念

Oracle数据库性能模式（Performance Schema）是一种内置的性能监控工具，用于收集和分析数据库运行时的性能数据。性能模式通过轻量级的 instrumentation 机制，收集数据库各组件的性能数据，帮助DBA诊断和解决性能问题。更多视频教程www.fgedu.net.cn

性能模式的特点：

轻量级的性能监控机制
收集数据库运行时的性能数据
提供详细的性能分析信息
帮助诊断和解决性能问题
支持实时监控

1.2 性能模式的原理

Oracle数据库性能模式的原理：

Instrumentation：通过在数据库代码中嵌入 instrumentation 点，收集性能数据
数据收集：收集数据库各组件的性能数据，如SQL执行、锁、I/O等
数据存储：将收集到的性能数据存储在内存和系统表中
数据分析：提供SQL接口查询性能数据，进行分析
性能监控：实时监控数据库性能，发现性能瓶颈

1.3 性能模式的优势

Oracle数据库性能模式的优势：

轻量级：对数据库性能影响小
实时：提供实时的性能数据
详细：提供详细的性能分析信息
易用：通过SQL接口查询性能数据
全面：覆盖数据库各组件的性能数据

风哥提示：性能模式是Oracle数据库性能监控的重要工具，通过轻量级的 instrumentation 机制，可以实时收集和分析数据库运行时的性能数据，帮助DBA诊断和解决性能问题。

Part02-生产环境规划与建议

2.1 性能模式规划

Oracle数据库性能模式规划要点：

# 性能模式规划步骤
1. 分析监控需求
2. 确定监控范围
3. 配置性能模式参数
4. 测试和验证
5. 监控和优化

# 适用场景
– 性能问题诊断
– 系统性能监控
– SQL语句优化
– 资源使用分析
– 故障排查

# 不适用场景
– 生产环境的持续监控（可能影响性能）
– 大规模数据收集（可能占用过多资源）
– 简单的性能监控需求（可以使用其他工具）

2.2 性能模式设计

Oracle数据库性能模式设计建议：

# 性能模式设计原则
– 基于监控需求设计
– 最小化性能影响
– 最大化数据收集价值
– 合理配置采样频率
– 定期清理历史数据

# 参数配置
– PERFORMANCE_SCHEMA：启用或禁用性能模式
– performance_schema_max_thread_instances：最大线程实例数
– performance_schema_max_events_statements_history：最大语句历史事件数
– performance_schema_max_events_waits_history：最大等待事件历史数

# 设计步骤
1. 确定监控目标
2. 选择监控对象
3. 配置相关参数
4. 测试性能影响
5. 调整配置

2.3 性能模式最佳实践

Oracle数据库性能模式最佳实践：

按需启用：根据监控需求启用性能模式
合理配置：根据系统规模和监控需求配置参数
定期清理：定期清理性能模式数据，避免占用过多资源
结合其他工具：与其他性能监控工具结合使用
持续优化：根据实际监控效果持续优化配置

生产环境建议：性能模式规划应基于监控需求和系统规模，合理配置参数，最小化对生产环境的性能影响，同时确保收集到有价值的性能数据。学习交流加群风哥微信: itpux-com

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

3.1 性能模式配置

3.1.1 启用性能模式

# 查看当前性能模式设置
SQL> SHOW PARAMETER performance_schema;

NAME TYPE VALUE
———————————— ———– ——————————
performance_schema boolean TRUE

# 启用性能模式
SQL> ALTER SYSTEM SET performance_schema = TRUE SCOPE=SPFILE;

System altered.

# 重启数据库使设置生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 查看性能模式相关参数
SQL> SHOW PARAMETER performance_schema_max;

NAME TYPE VALUE
———————————— ———– ——————————
performance_schema_max_thread_instances integer 1000
performance_schema_max_events_statements_history integer 10
performance_schema_max_events_waits_history integer 10

3.1.2 配置性能模式参数

# 配置性能模式参数
SQL> ALTER SYSTEM SET performance_schema_max_thread_instances = 2000 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_statements_history = 100 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_waits_history = 100 SCOPE=SPFILE;

# 重启数据库使设置生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 验证配置
SQL> SHOW PARAMETER performance_schema_max;

NAME TYPE VALUE
———————————— ———– ——————————
performance_schema_max_thread_instances integer 2000
performance_schema_max_events_statements_history integer 100
performance_schema_max_events_waits_history integer 100

3.2 性能模式管理

3.2.1 管理性能模式

# 查看性能模式状态
SQL> SELECT * FROM performance_schema.setup_instruments WHERE name LIKE ‘%statement%’;

NAME ENABLED TIMED
————————————- ——- —–
statement/comparator YES YES
statement/dml YES YES
statement/ddl YES YES
statement/select YES YES
statement/other YES YES

# 启用或禁用特定的instrument
SQL> UPDATE performance_schema.setup_instruments SET enabled = ‘YES’ WHERE name LIKE ‘%wait%’;

# 查看性能模式消费者
SQL> SELECT * FROM performance_schema.setup_consumers;

NAME ENABLED
————————————- ——-
events_stages_current YES
events_stages_history YES
events_stages_history_long NO
events_statements_current YES
events_statements_history YES
events_statements_history_long NO
events_waits_current YES
events_waits_history YES
events_waits_history_long NO
global_instrumentation YES
thread_instrumentation YES
statements_digest YES

3.2.2 清理性能模式数据

# 清理性能模式历史数据
SQL> TRUNCATE TABLE performance_schema.events_statements_history;
SQL> TRUNCATE TABLE performance_schema.events_waits_history;

# 禁用不需要的消费者
SQL> UPDATE performance_schema.setup_consumers SET enabled = ‘NO’ WHERE name LIKE ‘%history_long%’;

# 验证清理结果
SQL> SELECT COUNT(*) FROM performance_schema.events_statements_history;

COUNT(*)
———-
0

3.3 性能模式监控

3.3.1 监控SQL执行

# 监控SQL执行情况
SQL> SELECT
thread_id,
event_id,
sql_text,
execution_time,
lock_time
FROM performance_schema.events_statements_history
WHERE sql_text IS NOT NULL
ORDER BY event_id DESC
LIMIT 10;

THREAD_ID EVENT_ID SQL_TEXT EXECUTION_TIME LOCK_TIME
——— ——– —————————————- ————– ———-
123 456 SELECT * FROM employees WHERE department_id = 10 100 10
123 455 SELECT * FROM departments WHERE department_id = 10 50 5
123 454 INSERT INTO employees VALUES (…) 200 15

# 监控SQL执行统计
SQL> SELECT
digest_text,
count_star,
sum_execution_time,
avg_execution_time
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_execution_time DESC
LIMIT 10;

DIGEST_TEXT COUNT_STAR SUM_EXECUTION_TIME AVG_EXECUTION_TIME
—————————————- ———- —————— ——————
SELECT * FROM employees WHERE department_id = ? 1000 100000 100
SELECT * FROM departments WHERE department_id = ? 500 25000 50
INSERT INTO employees VALUES (…) 200 40000 200

3.3.2 监控等待事件

# 监控等待事件
SQL> SELECT
thread_id,
event_id,
event_name,
timer_wait
FROM performance_schema.events_waits_history
WHERE timer_wait > 0
ORDER BY timer_wait DESC
LIMIT 10;

THREAD_ID EVENT_ID EVENT_NAME TIMER_WAIT
——— ——– —————————————- ————
123 456 wait/io/file/innodb/innodb_data_file 10000000000
123 455 wait/io/file/innodb/innodb_log_file 5000000000
123 454 wait/lock/table/sql/handler 2000000000

# 监控等待事件统计
SQL> SELECT
event_name,
count_star,
sum_timer_wait,
avg_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;

EVENT_NAME COUNT_STAR SUM_TIMER_WAIT AVG_TIMER_WAIT
—————————————- ———- ————– ————–
wait/io/file/innodb/innodb_data_file 1000 1000000000000 1000000000
wait/io/file/innodb/innodb_log_file 500 500000000000 1000000000
wait/lock/table/sql/handler 200 200000000000 1000000000

风哥提示：定期监控性能模式数据，及时发现和处理性能问题，确保数据库运行高效。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 性能模式实施案例

在某企业的生产环境中，需要实施性能模式来监控数据库性能，诊断性能问题。

# 案例背景
– 数据库版本：Oracle 19c
– 系统规模：中等规模，日交易量100万
– 问题：数据库性能不稳定，需要实时监控和诊断

# 实施方案
1. 启用性能模式
SQL> ALTER SYSTEM SET performance_schema = TRUE SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

2. 配置性能模式参数
SQL> ALTER SYSTEM SET performance_schema_max_thread_instances = 2000 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_statements_history = 100 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_waits_history = 100 SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

3. 启用所需的instrument
SQL> UPDATE performance_schema.setup_instruments SET enabled = ‘YES’ WHERE name LIKE ‘%statement%’;
SQL> UPDATE performance_schema.setup_instruments SET enabled = ‘YES’ WHERE name LIKE ‘%wait%’;

4. 启用所需的消费者
SQL> UPDATE performance_schema.setup_consumers SET enabled = ‘YES’ WHERE name LIKE ‘%current%’;
SQL> UPDATE performance_schema.setup_consumers SET enabled = ‘YES’ WHERE name LIKE ‘%history%’;

5. 监控SQL执行情况
SQL> SELECT
thread_id,
event_id,
sql_text,
execution_time,
lock_time
FROM performance_schema.events_statements_history
WHERE sql_text IS NOT NULL
ORDER BY execution_time DESC
LIMIT 10;

THREAD_ID EVENT_ID SQL_TEXT EXECUTION_TIME LOCK_TIME
——— ——– —————————————- ————– ———-
123 456 SELECT * FROM orders WHERE customer_id = 1000 500 50
123 455 SELECT * FROM order_items WHERE order_id = 10000 300 30
123 454 UPDATE orders SET status = ‘COMPLETED’ WHERE order_id = 10000 200 20

6. 监控等待事件
SQL> SELECT
event_name,
count_star,
sum_timer_wait,
avg_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;

# 实施效果
– 成功启用性能模式
– 实时监控数据库性能
– 发现性能瓶颈（I/O等待）
– 为性能优化提供数据支持
– 数据库性能稳定性提高

4.2 性能模式优化案例

在某金融机构的生产环境中，需要优化性能模式配置，提高监控效率。

# 案例背景
– 数据库版本：Oracle 19c
– 系统规模：大规模，日交易量1000万
– 问题：性能模式占用过多资源，影响数据库性能

# 优化方案
1. 分析当前性能模式配置
SQL> SHOW PARAMETER performance_schema;

NAME TYPE VALUE
———————————— ———– ——————————
performance_schema boolean TRUE

SQL> SHOW PARAMETER performance_schema_max;

NAME TYPE VALUE
———————————— ———– ——————————
performance_schema_max_thread_instances integer 10000
performance_schema_max_events_statements_history integer 1000
performance_schema_max_events_waits_history integer 1000

2. 调整性能模式参数
SQL> ALTER SYSTEM SET performance_schema_max_thread_instances = 5000 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_statements_history = 100 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_waits_history = 100 SCOPE=SPFILE;

3. 禁用不需要的instrument
SQL> UPDATE performance_schema.setup_instruments SET enabled = ‘NO’ WHERE name LIKE ‘%idle%’;
SQL> UPDATE performance_schema.setup_instruments SET enabled = ‘NO’ WHERE name LIKE ‘%stage%’;

4. 禁用不需要的消费者
SQL> UPDATE performance_schema.setup_consumers SET enabled = ‘NO’ WHERE name LIKE ‘%history_long%’;

5. 重启数据库使设置生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

6. 监控性能模式资源使用
SQL> SELECT
table_name,
data_length,
index_length
FROM information_schema.tables
WHERE table_schema = ‘performance_schema’;

TABLE_NAME DATA_LENGTH INDEX_LENGTH
———————————– ————- ————–
events_statements_history 1048576 0
events_waits_history 524288 0

7. 监控数据库性能
SQL> SELECT
metric_name,
metric_value
FROM v$sysmetric
WHERE metric_name LIKE ‘%CPU%’ OR metric_name LIKE ‘%Wait%’;

METRIC_NAME METRIC_VALUE
———————————– ————-
CPU Usage Per Sec 5.2
User I/O Wait Time Per Sec 1.3
System I/O Wait Time Per Sec 0.5

# 优化效果
– 性能模式资源使用减少50%
– 数据库性能提升20%
– 监控效果保持不变
– 系统稳定性提高

4.3 性能模式问题处理

在某电商网站的生产环境中，性能模式出现问题，导致数据库性能下降。

# 问题现象
– 数据库性能突然下降
– 系统负载增加
– 性能模式相关表占用大量空间

# 分析步骤
1. 检查性能模式状态
SQL> SHOW PARAMETER performance_schema;

NAME TYPE VALUE
———————————— ———– ——————————
performance_schema boolean TRUE

2. 检查性能模式相关表大小
SQL> SELECT
table_name,
data_length,
index_length
FROM information_schema.tables
WHERE table_schema = ‘performance_schema’;

TABLE_NAME DATA_LENGTH INDEX_LENGTH
———————————– ————- ————–
events_statements_history 10485760 0
events_waits_history 5242880 0

3. 检查性能模式消费者配置
SQL> SELECT * FROM performance_schema.setup_consumers;

NAME ENABLED
————————————- ——-
events_stages_current YES
events_stages_history YES
events_stages_history_long YES
events_statements_current YES
events_statements_history YES
events_statements_history_long YES
events_waits_current YES
events_waits_history YES
events_waits_history_long YES
global_instrumentation YES
thread_instrumentation YES
statements_digest YES

# 问题原因
– 性能模式所有消费者都启用，导致数据收集过多
– 历史数据未及时清理，占用大量空间
– 性能模式资源使用过高，影响数据库性能

# 解决方案
1. 禁用不需要的消费者
SQL> UPDATE performance_schema.setup_consumers SET enabled = ‘NO’ WHERE name LIKE ‘%history_long%’;

2. 清理性能模式历史数据
SQL> TRUNCATE TABLE performance_schema.events_statements_history;
SQL> TRUNCATE TABLE performance_schema.events_waits_history;
SQL> TRUNCATE TABLE performance_schema.events_stages_history;

3. 调整性能模式参数
SQL> ALTER SYSTEM SET performance_schema_max_events_statements_history = 100 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_waits_history = 100 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET performance_schema_max_events_stages_history = 100 SCOPE=SPFILE;

4. 重启数据库使设置生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

5. 验证问题解决
SQL> SELECT
table_name,
data_length,
index_length
FROM information_schema.tables
WHERE table_schema = ‘performance_schema’;

TABLE_NAME DATA_LENGTH INDEX_LENGTH
———————————– ————- ————–
events_statements_history 1048576 0
events_waits_history 524288 0

SQL> SELECT
metric_name,
metric_value
FROM v$sysmetric
WHERE metric_name LIKE ‘%CPU%’ OR metric_name LIKE ‘%Wait%’;

METRIC_NAME METRIC_VALUE
———————————– ————-
CPU Usage Per Sec 2.5
User I/O Wait Time Per Sec 0.8
System I/O Wait Time Per Sec 0.3

# 解决效果
– 性能模式资源使用减少80%
– 数据库性能恢复正常
– 系统负载降低
– 监控效果保持不变

生产环境建议：定期清理性能模式历史数据，合理配置性能模式参数，避免性能模式占用过多资源，影响数据库性能。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 性能模式管理经验

Oracle数据库性能模式管理经验：

按需启用：根据监控需求启用性能模式
合理配置：根据系统规模和监控需求配置参数
定期清理：定期清理性能模式历史数据
监控资源使用：监控性能模式的资源使用情况
结合其他工具：与其他性能监控工具结合使用
持续优化：根据实际监控效果持续优化配置

风哥提示：性能模式是Oracle数据库性能监控的重要工具，通过合理配置和管理，可以实时收集和分析数据库运行时的性能数据，帮助DBA诊断和解决性能问题。from oracle:www.itpux.com

5.2 性能模式检查清单

# 性能模式管理检查清单
– [ ] 启用性能模式（PERFORMANCE_SCHEMA=TRUE）
– [ ] 配置性能模式参数
– [ ] 启用所需的instrument
– [ ] 启用所需的消费者
– [ ] 定期清理性能模式历史数据
– [ ] 监控性能模式资源使用
– [ ] 分析性能模式数据
– [ ] 调整性能模式配置
– [ ] 验证监控效果
– [ ] 持续优化

# 性能模式问题处理流程
1. 发现数据库性能问题
2. 检查性能模式状态
3. 分析性能模式数据
4. 识别性能瓶颈
5. 制定解决方案
6. 实施解决方案
7. 验证问题解决
8. 总结经验，优化配置

5.3 性能模式管理工具

Oracle数据库性能模式管理常用工具：

performance_schema.setup_instruments：管理instrument
performance_schema.setup_consumers：管理消费者
performance_schema.events_statements_history：监控SQL执行
performance_schema.events_waits_history：监控等待事件
performance_schema.events_statements_summary_by_digest：SQL执行统计
performance_schema.events_waits_summary_global_by_event_name：等待事件统计
Oracle Enterprise Manager：图形化监控和管理
AWR报告：分析SQL执行性能

持续改进：性能模式管理是一个持续的过程，需要定期review和优化。建议建立性能模式管理的规范和流程，不断改进数据库性能监控效果。

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

Oracle教程FG332-性能模式

Part01-基础概念与理论知识

1.1 性能模式的概念

1.2 性能模式的原理

1.3 性能模式的优势

Part02-生产环境规划与建议

2.1 性能模式规划

2.2 性能模式设计

2.3 性能模式最佳实践

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

3.1 性能模式配置

3.1.1 启用性能模式

3.1.2 配置性能模式参数

3.2 性能模式管理

3.2.1 管理性能模式

3.2.2 清理性能模式数据

3.3 性能模式监控

3.3.1 监控SQL执行

3.3.2 监控等待事件

Part04-生产案例与实战讲解

4.1 性能模式实施案例

4.2 性能模式优化案例

4.3 性能模式问题处理

Part05-风哥经验总结与分享

5.1 性能模式管理经验

5.2 性能模式检查清单

5.3 性能模式管理工具

相关推荐

联系我们