Oracle教程FG331-统计信息反馈

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

Part01-基础概念与理论知识

1.1 统计信息反馈的概念

Oracle数据库统计信息反馈（Statistics Feedback）是一种智能的统计信息管理机制，用于解决优化器统计信息不准确的问题。统计信息反馈通过收集实际执行过程中的统计信息，自动调整执行计划，提高SQL执行性能。更多视频教程www.fgedu.net.cn

统计信息反馈的特点：

收集实际执行过程中的统计信息
自动调整执行计划
提高SQL执行性能
减少手动干预
适用于复杂查询

1.2 统计信息反馈的原理

Oracle数据库统计信息反馈的原理：

首次执行：优化器基于现有统计信息生成执行计划，执行SQL语句
收集信息：收集实际执行过程中的统计信息（行数、连接基数等）
比较分析：将实际统计信息与估计统计信息进行比较
调整计划：如果估计统计信息与实际统计信息差异较大，生成新的执行计划
应用新计划：下次执行时使用调整后的执行计划

1.3 统计信息反馈的优势

Oracle数据库统计信息反馈的优势：

提高性能：基于实际执行信息调整执行计划，提高SQL执行性能
减少手动干预：自动调整执行计划，减少DBA手动干预
适应数据变化：自动适应数据分布的变化
提高优化器准确性：通过实际执行信息提高统计信息的准确性
适用于复杂查询：特别适合复杂的多表连接查询

风哥提示：统计信息反馈是Oracle数据库优化的重要特性，通过智能的统计信息管理机制，可以显著提高SQL执行性能，特别是对于复杂查询。

Part02-生产环境规划与建议

2.1 统计信息反馈规划

Oracle数据库统计信息反馈规划要点：

# 统计信息反馈规划步骤
1. 分析SQL语句特征
2. 识别复杂查询
3. 评估统计信息准确性
4. 配置统计信息反馈参数
5. 测试和验证
6. 监控和优化

# 适用场景
– 复杂的多表连接查询
– 统计信息不准确的查询
– 数据分布不均匀的表
– 执行计划选择错误的查询
– 统计信息过期的表

# 不适用场景
– 简单查询（执行计划选择有限）
– 数据分布均匀的表
– 统计信息准确的表
– 执行计划选择正确的查询

2.2 统计信息反馈设计

Oracle数据库统计信息反馈设计建议：

# 统计信息反馈设计原则
– 基于SQL语句复杂度设计
– 基于统计信息准确性设计
– 基于数据分布设计
– 最小化执行计划调整成本
– 最大化执行计划质量

# 参数配置
– OPTIMIZER_ADAPTIVE_FEATURES：启用或禁用自适应优化特性
– OPTIMIZER_ADAPTIVE_REPORTING_ONLY：仅报告不实际使用
– OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES：捕获SQL计划基线

# 设计步骤
1. 识别需要统计信息反馈的SQL语句
2. 分析统计信息准确性
3. 配置相关参数
4. 测试执行计划调整
5. 监控性能效果

2.3 统计信息反馈最佳实践

Oracle数据库统计信息反馈最佳实践：

启用自适应优化：确保OPTIMIZER_ADAPTIVE_FEATURES参数设置为TRUE
定期收集统计信息：确保统计信息准确
监控执行计划：定期监控SQL执行计划的调整情况
分析统计信息：定期分析统计信息的准确性
调整参数：根据实际情况调整相关参数
测试验证：在生产环境实施前进行充分测试
持续优化：根据实际执行情况持续优化

生产环境建议：统计信息反馈规划应基于SQL语句复杂度和统计信息准确性，合理配置相关参数，定期监控和优化，确保SQL执行性能最优。学习交流加群风哥微信: itpux-com

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

3.1 统计信息反馈配置

3.1.1 启用统计信息反馈

# 查看当前统计信息反馈设置
SQL> SHOW PARAMETER optimizer_adaptive_features;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_adaptive_features boolean TRUE

# 启用统计信息反馈
SQL> ALTER SYSTEM SET optimizer_adaptive_features = TRUE SCOPE=BOTH;

System altered.

# 查看其他相关参数
SQL> SHOW PARAMETER optimizer_adaptive;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_adaptive_features boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE

3.1.2 配置SQL计划基线

# 启用SQL计划基线捕获
SQL> ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

System altered.

# 手动加载SQL计划基线
SQL> EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘abc123’);

PL/SQL procedure successfully completed.

# 查看SQL计划基线
SQL> SELECT
sql_handle,
plan_name,
enabled,
accepted
FROM dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC
—————————— —————————— — —
SQL_1234567890abcdef SQL_PLAN_1234567890abcdef0001 YES YES

3.2 统计信息反馈管理

3.2.1 管理统计信息反馈

# 查看统计信息反馈相关信息
SQL> SELECT
sql_id,
child_number,
executions,
buffer_gets,
rows_processed,
plan_hash_value,
is_reoptimizable
FROM v$sql
WHERE sql_text LIKE ‘%SELECT * FROM employees e, departments d WHERE e.department_id = d.department_id AND e.salary > :sal%’;

SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE IS_REOPTIMIZABLE
————- ———— ———- ———– ————– ————— —————
abcd1234 0 100 1000 100 1234567890 YES

# 查看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘abcd1234’, 0, ‘ALL’));

PLAN_TABLE_OUTPUT
——————————————————————–
SQL_ID abcd1234, child number 0
———————————
SELECT * FROM employees e, departments d WHERE e.department_id = d.department_id AND e.salary > :sal

Plan hash value: 1234567890
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | | | 5 |
| 1 | HASH JOIN | | 100 | 7800 | 5 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 100 | 5000 | 2 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 783 | 2 |
——————————————————————–

Note
—–
– Statistics feedback used for this statement

3.2.2 处理统计信息反馈问题

# 强制硬解析
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

# 清除特定SQL的游标
SQL> EXEC DBMS_SHARED_POOL.PURGE(‘00000003C9A12345, 0’, ‘C’);

PL/SQL procedure successfully completed.

# 使用提示强制不同的执行计划
SQL> SELECT /*+ USE_HASH(e d) */ * FROM employees e, departments d WHERE e.department_id = d.department_id AND e.salary > :sal;

3.3 统计信息反馈监控

3.3.1 监控统计信息反馈

# 监控统计信息反馈使用情况
SQL> SELECT
sql_id,
child_number,
executions,
buffer_gets,
rows_processed,
plan_hash_value,
is_reoptimizable
FROM v$sql
WHERE is_reoptimizable = ‘YES’;

SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE IS_REOPTIMIZABLE
————- ———— ———- ———– ————– ————— —————
abcd1234 0 100 1000 100 1234567890 YES
xyz7890 0 50 500 500 9876543210 YES

# 查看执行计划详情
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘abcd1234’, 0, ‘ALL’));

Note
—–
– Statistics feedback used for this statement

# 监控执行计划性能
SQL> SELECT
sql_id,
plan_hash_value,
executions,
elapsed_time/1000000 AS elapsed_seconds,
buffer_gets,
rows_processed
FROM v$sql
WHERE sql_id = ‘abcd1234’;

SQL_ID PLAN_HASH_VALUE EXECUTIONS ELAPSED_SECONDS BUFFER_GETS ROWS_PROCESSED
————- ————— ———- ————— ———– ————–
abcd1234 1234567890 100 0.1 1000 100

风哥提示：定期监控统计信息反馈的执行情况，及时发现和处理问题，确保SQL执行性能最优。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 统计信息反馈实施案例

在某企业的生产环境中，存在一个复杂的多表连接查询，需要实施统计信息反馈来提高SQL执行性能。

# 案例背景
– 表名：employees, departments, jobs
– SQL语句：SELECT * FROM employees e, departments d, jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND e.salary > :sal
– 问题：优化器统计信息不准确，导致执行计划选择错误，性能下降

# 实施方案
1. 检查当前统计信息反馈设置
SQL> SHOW PARAMETER optimizer_adaptive_features;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_adaptive_features boolean TRUE

2. 执行SQL语句
SQL> VARIABLE sal NUMBER;
SQL> EXEC :sal := 5000;
SQL> SELECT * FROM employees e, departments d, jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND e.salary > :sal;

3. 查看统计信息反馈使用情况
SQL> SELECT
sql_id,
child_number,
executions,
buffer_gets,
rows_processed,
plan_hash_value,
is_reoptimizable
FROM v$sql
WHERE sql_text LIKE ‘%SELECT * FROM employees e, departments d, jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND e.salary > :sal%’;

4. 查看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘abcd1234’, 0, ‘ALL’));

PLAN_TABLE_OUTPUT
——————————————————————–
SQL_ID abcd1234, child number 0
———————————
SELECT * FROM employees e, departments d, jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND e.salary > :sal

Plan hash value: 1234567890
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | | | 8 |
| 1 | HASH JOIN | | 100 | 10800 | 8 |
| 2 | HASH JOIN | | 100 | 7800 | 5 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 100 | 5000 | 2 |
| 4 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 783 | 2 |
| 5 | TABLE ACCESS FULL | JOBS | 19 | 570 | 2 |
——————————————————————–

Note
—–
– Statistics feedback used for this statement

# 实施效果
– 统计信息反馈成功收集了实际执行过程中的统计信息
– 优化器基于实际统计信息调整了执行计划
– SQL执行性能显著提高
– 执行计划选择更加准确

4.2 统计信息反馈优化案例

在某金融机构的生产环境中，需要优化统计信息反馈配置，提高SQL执行性能。

# 案例背景
– 表名：transactions, accounts, customers
– SQL语句：SELECT * FROM transactions t, accounts a, customers c WHERE t.account_id = a.account_id AND a.customer_id = c.customer_id AND t.amount > :amt
– 问题：统计信息反馈未生效，导致性能问题

# 优化方案
1. 检查当前统计信息反馈设置
SQL> SHOW PARAMETER optimizer_adaptive_features;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_adaptive_features boolean FALSE

2. 启用统计信息反馈
SQL> ALTER SYSTEM SET optimizer_adaptive_features = TRUE SCOPE=BOTH;

System altered.

3. 收集表统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘TRANSACTIONS’);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘ACCOUNTS’);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘CUSTOMERS’);

PL/SQL procedure successfully completed.

4. 执行SQL语句
SQL> VARIABLE amt NUMBER;
SQL> EXEC :amt := 1000;
SQL> SELECT * FROM transactions t, accounts a, customers c WHERE t.account_id = a.account_id AND a.customer_id = c.customer_id AND t.amount > :amt;

5. 查看统计信息反馈使用情况
SQL> SELECT
sql_id,
child_number,
executions,
buffer_gets,
rows_processed,
plan_hash_value,
is_reoptimizable
FROM v$sql
WHERE sql_text LIKE ‘%SELECT * FROM transactions t, accounts a, customers c WHERE t.account_id = a.account_id AND a.customer_id = c.customer_id AND t.amount > :amt%’;

SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE IS_REOPTIMIZABLE
————- ———— ———- ———– ————– ————— —————
xyz7890 0 50 500 500 9876543210 YES

6. 查看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘xyz7890’, 0, ‘ALL’));

PLAN_TABLE_OUTPUT
——————————————————————–
SQL_ID xyz7890, child number 0
———————————
SELECT * FROM transactions t, accounts a, customers c WHERE t.account_id = a.account_id AND a.customer_id = c.customer_id AND t.amount > :amt

Plan hash value: 9876543210
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | | | 15 |
| 1 | HASH JOIN | | 500 | 54000 | 15 |
| 2 | HASH JOIN | | 500 | 36000 | 10 |
| 3 | TABLE ACCESS FULL | TRANSACTIONS | 500 | 20000 | 5 |
| 4 | TABLE ACCESS FULL | ACCOUNTS | 1000 | 32000 | 3 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 500 | 18000 | 2 |
——————————————————————–

Note
—–
– Statistics feedback used for this statement

# 优化效果
– 统计信息反馈成功收集了实际执行过程中的统计信息
– 优化器基于实际统计信息调整了执行计划
– SQL执行性能提高50%
– 执行计划选择更加准确

4.3 统计信息反馈问题处理

在某电商网站的生产环境中，统计信息反馈出现问题，导致SQL执行性能下降。

# 问题现象
– SQL语句执行性能不稳定
– 同一SQL语句有时快有时慢
– 统计信息反馈未正确调整执行计划

# 分析步骤
1. 检查统计信息反馈设置
SQL> SHOW PARAMETER optimizer_adaptive_features;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_adaptive_features boolean TRUE

2. 查看统计信息反馈使用情况
SQL> SELECT
sql_id,
child_number,
executions,
buffer_gets,
rows_processed,
plan_hash_value,
is_reoptimizable
FROM v$sql
WHERE sql_text LIKE ‘%SELECT * FROM orders o, order_items oi WHERE o.order_id = oi.order_id AND o.customer_id = :cust%’;

SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE IS_REOPTIMIZABLE
————- ———— ———- ———– ————– ————— —————
pqr4567 0 200 50000 10000 9988776655 YES

3. 查看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘pqr4567’, 0, ‘ALL’));

PLAN_TABLE_OUTPUT
——————————————————————–
SQL_ID pqr4567, child number 0
———————————
SELECT * FROM orders o, order_items oi WHERE o.order_id = oi.order_id AND o.customer_id = :cust

Plan hash value: 9988776655
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | | | 500 |
| 1 | HASH JOIN | | 10000 | 800000 | 500 |
| 2 | TABLE ACCESS FULL | ORDERS | 1000 | 40000 | 50 |
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 50000 | 2000000 | 400 |
——————————————————————–

Note
—–
– Statistics feedback used for this statement

4. 检查表统计信息
SQL> SELECT
table_name,
last_analyzed
FROM dba_tables
WHERE table_name IN (‘ORDERS’, ‘ORDER_ITEMS’);

TABLE_NAME LAST_ANALYZED
—————————— ——————-
ORDERS 2026-01-01 00:00:00
ORDER_ITEMS 2026-01-01 00:00:00

# 问题原因
– 表统计信息过时
– 统计信息反馈基于过时的统计信息做出了错误的执行计划调整
– 执行计划选择不正确

# 解决方案
1. 收集表统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘ORDERS’);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘ORDER_ITEMS’);

PL/SQL procedure successfully completed.

2. 清除共享池中的游标
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

3. 重新执行SQL语句
SQL> VARIABLE cust NUMBER;
SQL> EXEC :cust := 100;
SQL> SELECT * FROM orders o, order_items oi WHERE o.order_id = oi.order_id AND o.customer_id = :cust;

4. 查看统计信息反馈使用情况
SQL> SELECT
sql_id,
child_number,
executions,
buffer_gets,
rows_processed,
plan_hash_value,
is_reoptimizable
FROM v$sql
WHERE sql_text LIKE ‘%SELECT * FROM orders o, order_items oi WHERE o.order_id = oi.order_id AND o.customer_id = :cust%’;

SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE IS_REOPTIMIZABLE
————- ———— ———- ———– ————– ————— —————
pqr4567 0 50 100 10 1122334455 YES

5. 查看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘pqr4567’, 0, ‘ALL’));

Plan hash value: 1122334455
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | | | 5 |
| 1 | HASH JOIN | | 10 | 800 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 1 | 40 | 2 |
| 3 | INDEX RANGE SCAN | ORD_CUST_IDX | 1 | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS | 10 | 400 | 2 |
| 5 | INDEX RANGE SCAN | OI_ORDER_IDX | 10 | | 1 |
——————————————————————–

Note
—–
– Statistics feedback used for this statement

# 解决效果
– 统计信息反馈现在基于准确的统计信息和实际执行情况调整执行计划
– SQL执行性能稳定
– 执行计划选择正确

生产环境建议：定期收集表统计信息，确保统计信息反馈基于准确的统计信息做出正确的执行计划调整。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 统计信息反馈管理经验

Oracle数据库统计信息反馈管理经验：

启用自适应优化：确保OPTIMIZER_ADAPTIVE_FEATURES参数设置为TRUE
定期收集统计信息：确保统计信息准确
监控统计信息反馈：定期监控统计信息反馈的使用情况
分析执行计划：确保执行计划选择正确
调整参数：根据实际情况调整相关参数
测试验证：在生产环境实施前进行充分测试
持续优化：根据实际执行情况持续优化

风哥提示：统计信息反馈是Oracle数据库优化的重要特性，通过智能的统计信息管理机制，可以显著提高SQL执行性能，特别是对于复杂查询。from oracle:www.itpux.com

5.2 统计信息反馈检查清单

# 统计信息反馈管理检查清单
– [ ] 启用自适应优化（OPTIMIZER_ADAPTIVE_FEATURES=TRUE）
– [ ] 定期收集表统计信息
– [ ] 监控统计信息反馈使用情况
– [ ] 分析执行计划选择
– [ ] 处理统计信息反馈问题
– [ ] 测试复杂查询的执行计划
– [ ] 调整相关参数
– [ ] 验证执行性能
– [ ] 持续优化

# 统计信息反馈问题处理流程
1. 发现SQL执行性能问题
2. 检查统计信息反馈设置
3. 查看统计信息反馈使用情况
4. 分析执行计划选择
5. 检查统计信息
6. 制定解决方案
7. 实施解决方案
8. 验证问题解决
9. 总结经验，优化配置

5.3 统计信息反馈管理工具

Oracle数据库统计信息反馈管理常用工具：

v$sql：查看统计信息反馈使用情况
DBMS_XPLAN.DISPLAY_CURSOR：查看执行计划
DBMS_STATS：收集统计信息
ALTER SYSTEM FLUSH SHARED_POOL：清除共享池
DBMS_SHARED_POOL.PURGE：清除特定游标
Oracle Enterprise Manager：图形化监控和管理
AWR报告：分析SQL执行性能

持续改进：统计信息反馈管理是一个持续的过程，需要定期review和优化。建议建立统计信息反馈管理的规范和流程，不断改进SQL执行性能。

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

Oracle教程FG331-统计信息反馈

Part01-基础概念与理论知识

1.1 统计信息反馈的概念

1.2 统计信息反馈的原理

1.3 统计信息反馈的优势

Part02-生产环境规划与建议

2.1 统计信息反馈规划

2.2 统计信息反馈设计

2.3 统计信息反馈最佳实践

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

3.1 统计信息反馈配置

3.1.1 启用统计信息反馈

3.1.2 配置SQL计划基线

3.2 统计信息反馈管理

3.2.1 管理统计信息反馈

3.2.2 处理统计信息反馈问题

3.3 统计信息反馈监控

3.3.1 监控统计信息反馈

Part04-生产案例与实战讲解

4.1 统计信息反馈实施案例

4.2 统计信息反馈优化案例

4.3 统计信息反馈问题处理

Part05-风哥经验总结与分享

5.1 统计信息反馈管理经验

5.2 统计信息反馈检查清单

5.3 统计信息反馈管理工具

相关推荐

联系我们