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

《Oracle 11g R2 全表扫描成本计算(工作量模式-workload).doc》

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

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

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

点击下载文档

Oracle 11g R2 全表扫描成本计算(工作量模式-workload).doc

标题：Oracle 11g R2 全表扫描成本计算（工作量模式-workload）

一、引言

在Oracle数据库优化过程中，全表扫描（Full Table Scan, FTS）的成本计算是性能调优的核心环节之一。Oracle 11g R2引入的基于工作量模式（Workload-Based）的成本计算方法，通过分析历史SQL执行统计信息，更精准地预测全表扫描的资源消耗。本文将深入探讨该模式下全表扫描成本的计算逻辑、关键参数及优化实践，帮助DBA和开发者理解Oracle优化器的决策机制。

二、全表扫描成本计算基础

1. 成本计算模型概述

Oracle优化器通过成本（Cost）量化操作开销，成本值越小表示执行效率越高。全表扫描成本由以下三部分组成：

总成本 = I/O成本 + CPU成本 + 内存成本

在11g R2中，工作量模式通过统计信息（如DBA_HIST_SEG_STAT、DBA_HIST_SQLSTAT）动态调整权重，使成本计算更贴合实际工作负载。

2. 关键统计信息来源

（1）系统统计信息（System Statistics）

通过DBMS_STATS.GATHER_SYSTEM_STATS收集，包含：

ioseektim：单次I/O寻道时间（毫秒）
iotrfspeed：I/O传输速率（MB/s）
cpuspeed：CPU处理速度（MHz）

-- 示例：收集系统统计信息
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(
  gathering_mode => 'NOWORKLOAD', -- 或'WORKLOAD'
  interval => 60
);

（2）对象统计信息（Object Statistics）

表级统计信息通过DBMS_STATS.GATHER_TABLE_STATS收集，核心字段包括：

NUM_ROWS：表总行数
BLOCKS：表占用的数据块数
AVG_ROW_LEN：平均行长度（字节）
SAMPLE_SIZE：采样行数

三、工作量模式下的成本计算逻辑

1. I/O成本计算

（1）多块读取成本（Multiblock Read Cost）

公式：

I/O成本 = CEIL(表块数 / 多块读取计数) * 单块读取调整因子

其中：

多块读取计数由db_file_multiblock_read_count参数决定（默认8）
单块读取调整因子通过系统统计信息计算

-- 查询多块读取参数
SELECT name, value FROM v$parameter 
WHERE name = 'db_file_multiblock_read_count';

（2）缓冲区缓存命中率影响

若表数据已缓存，I/O成本按比例降低：

调整后I/O成本 = I/O成本 * (1 - 缓冲区命中率)

缓冲区命中率通过V$SYSSTAT视图计算：

SELECT 
  (1 - (phy.value / (cur.value + con.value))) * 100 AS "Buffer Hit Ratio"
FROM 
  v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE 
  cur.name = 'db block gets'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads';

2. CPU成本计算

CPU成本由过滤条件复杂度决定，公式：

CPU成本 = 行处理成本 * 过滤后行数

其中：

行处理成本 = CPU周期数 / CPU速度
过滤后行数 = 总行数 * 选择性（Selectivity）

选择性计算示例：

-- 计算列选择性
SELECT 
  (num_distinct / num_rows) AS selectivity
FROM 
  dba_tab_columns
WHERE 
  table_name = 'EMPLOYEES'
  AND column_name = 'DEPARTMENT_ID';

3. 内存成本计算

内存成本主要考虑排序和哈希操作：

内存成本 = 排序区大小需求 / PGA可用内存

通过V$PGASTAT监控PGA使用情况：

SELECT name, value FROM v$pgastat 
WHERE name IN ('total PGA allocated', 'total PGA inuse');

四、工作量模式优化实践

1. 统计信息收集策略

（1）自动统计信息收集

启用自动任务：

-- 检查自动统计信息任务状态
SELECT client_name, status FROM dba_autotask_client 
WHERE client_name LIKE '%Stats%';

-- 启用自动统计信息收集
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL
  );
END;

（2）手动统计信息收集

针对大表使用增量统计：

EXEC DBMS_STATS.SET_TABLE_PREFS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  pname => 'INCREMENTAL',
  pvalue => 'TRUE'
);

2. 全表扫描优化技术

（1）分区表优化

通过分区裁剪（Partition Pruning）减少扫描范围：

-- 创建范围分区表
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
) PARTITION BY RANGE (sale_date) (
  PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
  PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY'))
);

（2）并行查询优化

设置并行度加速全表扫描：

-- 修改表并行度
ALTER TABLE employees PARALLEL 4;

-- 执行并行查询
SELECT /*+ PARALLEL(employees 4) */ * FROM employees;

（3）索引替代方案

当全表扫描成本过高时，考虑创建合适索引：

-- 创建位图索引（适用于低基数列）
CREATE BITMAP INDEX idx_emp_dept ON employees(department_id);

-- 创建函数索引（适用于过滤条件包含函数）
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

五、案例分析

1. 案例背景

某ERP系统中的ORDERS表包含1.2亿条记录，在执行以下查询时出现全表扫描：

SELECT * FROM orders 
WHERE order_date > SYSDATE - 30 
AND status = 'SHIPPED';

2. 成本分析

（1）收集统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'ERP',
  tabname => 'ORDERS',
  estimate_percent => 10,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  degree => 4
);

（2）查看执行计划：

EXPLAIN PLAN FOR
SELECT * FROM orders 
WHERE order_date > SYSDATE - 30 
AND status = 'SHIPPED';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

输出显示全表扫描成本为12,450，预计返回行数85万。

3. 优化方案

（1）创建复合索引：

CREATE INDEX idx_orders_date_status ON orders(order_date, status);

（2）更新统计信息并重新分析：

EXEC DBMS_STATS.GATHER_INDEX_STATS('ERP', 'IDX_ORDERS_DATE_STATUS');
-- 再次查看执行计划，成本降至820

六、常见问题与解决方案

1. 统计信息过期导致成本误判

症状：执行计划突然变化，性能下降

解决方案：

-- 锁定统计信息防止自动更新
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- 手动收集最新统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

2. 多块读取参数设置不当

症状：I/O成本异常高

解决方案：

-- 根据存储设备调整参数
ALTER SYSTEM SET db_file_multiblock_read_count=16 
SCOPE=SPFILE;
-- 重启实例生效

3. 缓冲区缓存不足

症状：物理读取频繁，I/O等待高

解决方案：

-- 增加DBWR进程
ALTER SYSTEM SET db_writer_processes=4 
SCOPE=SPFILE;

-- 调整SGA大小
ALTER SYSTEM SET sga_target=8G 
SCOPE=SPFILE;

七、总结

Oracle 11g R2的工作量模式通过整合历史执行数据，使全表扫描成本计算更贴近实际工作负载。DBA应重点关注以下方面：

定期收集准确的系统统计信息和对象统计信息
合理设置多块读取参数和并行度
结合分区表、索引等物理设计优化全表扫描
通过AWR报告监控高成本全表扫描SQL

掌握这些技术后，可显著提升数据库查询性能，降低系统资源消耗。

关键词：Oracle 11g R2、全表扫描、成本计算、工作量模式、系统统计信息、I/O成本、CPU成本、内存成本、统计信息收集、执行计划优化

简介：本文详细阐述Oracle 11g R2中基于工作量模式的全表扫描成本计算方法，涵盖I/O成本、CPU成本和内存成本的计算逻辑，介绍统计信息收集策略和优化实践，通过案例分析展示如何降低全表扫描成本，最后提供常见问题解决方案。

《Oracle 11g R2 全表扫描成本计算(工作量模式-workload).doc》

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

推荐度：

点击下载文档