位置：文档库 > 数据库 > 文档下载预览

1. 下载的文档为doc格式,下载后可用word或者wps进行编辑;

2. 将本文以doc文档格式下载到电脑，方便收藏和打印;

3. 下载后的文档,内容与下面显示的完全一致,下载之前请确认下面内容是否您想要的,是否完整.

Oracle执行计划与统计信息的一些总结.doc

《Oracle执行计划与统计信息的一些总结》

在Oracle数据库性能优化中，执行计划（Execution Plan）和统计信息（Statistics）是两个核心概念。执行计划是Oracle优化器（Optimizer）为SQL语句生成的执行路径，决定了数据如何被访问、连接和排序；而统计信息则是优化器进行决策的数据基础，包含表、索引、列的元数据（如行数、块数、数据分布等）。本文将系统梳理二者的关系、工作原理及常见优化场景，帮助DBA和开发者深入理解Oracle优化机制。

一、执行计划基础

1.1 执行计划的生成过程

当提交一条SQL语句时，Oracle优化器会经历以下步骤生成执行计划：

解析阶段：检查语法、验证对象是否存在，生成初始查询块。
查询转换：对SQL进行等价改写（如视图合并、子查询解嵌套）。
代价估算：基于统计信息计算不同执行路径的代价（Cost）。
计划生成：选择代价最低的计划，存储在共享池中。

示例：通过EXPLAIN PLAN FOR查看执行计划：

EXPLAIN PLAN FOR
SELECT e.employee_id, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

1.2 执行计划的关键操作

执行计划由一系列操作（Operation）组成，常见操作包括：

TABLE ACCESS FULL：全表扫描，适用于小表或无合适索引时。
INDEX UNIQUE SCAN：唯一索引扫描，如主键查询。
NESTED LOOPS：嵌套循环连接，适用于小数据集。
HASH JOIN：哈希连接，适用于大数据集等值连接。
SORT ORDER BY：排序操作，可能引发临时表空间使用。

二、统计信息的核心作用

2.1 统计信息的组成

Oracle统计信息分为对象级和系统级，主要包含：

表统计信息：行数（NUM_ROWS）、块数（BLOCKS）、平均行长（AVG_ROW_LEN）。
列统计信息：不同值的数量（NUM_DISTINCT）、密度（DENSITY）、直方图（HISTOGRAM）。
索引统计信息：叶块数（LEAF_BLOCKS）、聚簇因子（CLUSTERING_FACTOR）。
系统统计信息：CPU速度、I/O性能、多块读大小。

查看统计信息：

-- 查看表统计信息
SELECT table_name, num_rows, blocks, avg_row_len 
FROM user_tables 
WHERE table_name = 'EMPLOYEES';

-- 查看列统计信息
SELECT column_name, num_distinct, num_nulls, density 
FROM user_tab_columns 
WHERE table_name = 'EMPLOYEES' AND column_name = 'DEPARTMENT_ID';

2.2 统计信息的收集与更新

统计信息过期会导致优化器选择次优计划，需定期收集：

DBMS_STATS包：Oracle推荐工具，支持精确采样。
自动任务：通过AUTO_TASK_CLIENT_NAME配置自动统计收集。

示例：手动收集统计信息

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'HR',
    tabname          => 'EMPLOYEES',
    estimate_percent => 10,  -- 采样百分比
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',  -- 自动直方图
    degree           => 4,    -- 并行度
    granularity      => 'AUTO',
    cascade          => TRUE  -- 同时收集索引统计
  );
END;

三、执行计划与统计信息的交互

3.1 优化器模式的影响

Oracle优化器有两种模式：

基于规则的优化器（RBO）：依赖固定规则（已废弃）。
基于代价的优化器（CBO）：依赖统计信息（默认模式）。

通过OPTIMIZER_MODE参数控制：

-- 查看当前优化器模式
SHOW PARAMETER optimizer_mode;

-- 修改为ALL_ROWS（最小化总资源消耗）
ALTER SYSTEM SET optimizer_mode = 'ALL_ROWS' SCOPE=BOTH;

3.2 统计信息缺失的典型问题

案例1：表无统计信息导致全表扫描

-- 模拟无统计信息
EXEC DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');

-- 执行查询（可能使用全表扫描）
SELECT * FROM employees WHERE department_id = 10;

解决方案：收集统计信息后，优化器可能选择索引扫描。

案例2：直方图缺失导致选择性估算错误

若列数据分布不均匀（如STATUS列90%为'ACTIVE'），无直方图时优化器会假设均匀分布，导致错误估算返回行数。

四、常见优化场景与解决方案

4.1 索引未使用问题

原因：

统计信息过期，低估了索引选择性。
SQL隐式转换导致索引失效（如字符列与数字比较）。

诊断步骤：

-- 检查执行计划是否包含INDEX SCAN
-- 使用SQL Trace跟踪执行
ALTER SESSION SET tracefile_identifier = 'index_test';
ALTER SESSION SET events '10046 trace name context forever, level 12';
-- 执行SQL
-- 关闭Trace
ALTER SESSION SET events '10046 trace name context off';

4.2 连接方法选择不当

Oracle三种连接方式对比：

连接类型	适用场景	代价因素
NESTED LOOPS	小表驱动大表	驱动表返回行数、被驱动表访问成本
HASH JOIN	大数据集等值连接	哈希表构建成本、内存使用
SORT MERGE	非等值连接或排序后连接	排序成本、I/O开销

强制使用特定连接方式（调试时使用）：

SELECT /*+ USE_NL(e d) */ e.employee_id, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;

4.3 动态采样与统计信息补充

当统计信息不足时，可通过动态采样临时收集数据：

-- 启用动态采样（级别1-10，越高采样越精确）
ALTER SESSION SET optimizer_dynamic_sampling = 4;

-- 或在SQL提示中指定
SELECT /*+ DYNAMIC_SAMPLING(t 4) */ * FROM employees t WHERE salary > 10000;

五、高级主题：SQL Profile与SQL Plan Baseline

5.1 SQL Profile固定优化器行为

当优化器生成次优计划时，可通过SQL Profile保留正确计划的提示：

-- 生成SQL Profile（需DBMS_SQLTUNE权限）
DECLARE
  l_sql_profile   VARCHAR2(100);
  l_plan_hash_value NUMBER;
BEGIN
  l_plan_hash_value := 123456789; -- 目标计划的HASH值
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    sql_text    => 'SELECT * FROM employees WHERE department_id = :deptid',
    profile     => sqlprof_attr('FULL(employees)'),
    name        => 'EMP_DEPT_PROFILE',
    description => 'Force full scan for dept query',
    replace     => TRUE
  );
END;

5.2 SQL Plan Baseline历史计划复用

Oracle 11g引入的计划管理机制，可自动捕获并复用有效计划：

-- 启用自动计划捕获
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

-- 手动加载计划到Baseline
DECLARE
  l_plans EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '3x4y5z6a7b8c9d0e',
    plan_hash_value => 987654321,
    baseline_name => 'MY_BASELINE'
  );
END;

六、总结与最佳实践

1. 统计信息是优化器决策的基础，需定期收集并验证准确性。

2. 通过DBMS_XPLAN、SQL Trace等工具深入分析执行计划。

3. 对关键SQL建立SQL Profile或Baseline防止计划回退。

4. 监控V$SQL_PLAN、DBA_HIST_SQL_PLAN等视图跟踪计划变化。

5. 在测试环境验证统计收集和计划稳定性后再部署到生产环境。

关键词：Oracle执行计划、统计信息、优化器模式、DBMS_STATS、SQL Profile、SQL Plan Baseline、动态采样、连接方法

简介：本文系统阐述了Oracle数据库中执行计划与统计信息的核心机制，包括执行计划生成过程、统计信息组成与收集方法、二者交互对SQL优化的影响，以及常见性能问题的诊断与解决方案，结合代码示例与最佳实践，为DBA和开发者提供完整的性能优化指南。

《Oracle执行计划与统计信息的一些总结.doc》

将本文以doc文档格式下载到电脑，方便收藏和打印

推荐度：

点击下载文档