ORACLE数据库的统计数据及优化_oracle统计优化资源-CSDN文库

oracle,统计数据及优化

需积分: 9 141 浏览量 2008-07-18 14:39:21 上传评论收藏 370KB PDF 举报

资源推荐

资源详情

资源评论

首页网络编程可视化编程数据库网络技术网页制作调试环境软件下载汇款方式留言簿

公告：

祝大家新年快乐！

ADO特辑

Oracle特辑

MsSQL特辑

Access特辑

Informix特辑

Sybase特辑

Foxpro特辑

MySQL特辑

.net技术

XML技术

JAVA技术

WAP技术

VRML技术

用户名：

密码：

［搜索关键字］

搜索

使用dbms\_stats提高SQL性能

执行一个SQL语句时，数据库必须将查询转换成一个执行计划，并选择检索数据的最佳方式。对于Oracle，每个

SQL查询都能选择多种执行计划，包括用哪个索引来检索表行，以哪种顺序来联接多个表，以及要使用哪种内部

联接方法（Oracle支持嵌套循环联接、哈希联接、星形联接以及排序合并联接方法）。这些执行计划是由

Oracle的“基于代价的SQL优化器”（通常称为CBO）来制定的。

Oracle SQL优化器制定执行计划的依据是Oracle的统计数据；统计越好，它的效果越好。为了确保总是为一个

SQL查询选择最佳的执行计划，Oracle要依赖于查询所涉及的表和索引的统计数据。

dbms_stats包问世以后，Oracle专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用

老式的分析表和dbms_utility方法来生成CBO统计数据。那些古老的方式甚至有可能危及SQL的性能，因为它们

并非总是能够捕捉到有关表和索引的高质量信息。CBO使用对象统计，为所有SQL语句选择最佳的执行计划。

dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度

更快的SQL执行计划。

清单A展示了dbms_stats的一次示范执行情况，其中使用了options子句。

execdbms_stats.gather_schema_stats( -

ownname => 'SCOTT', -

options => 'GATHER AUTO', -

estimate_percent => dbms_stats.auto_sample_size, -

method_opt => 'for all columns size repeat', -

degree => 15 -

)

为了充分认识dbms_stats的好处，你需要仔细体会每一条主要的预编译指令（directive）。下面让我们研究每

一条指令，并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据。

options参数

使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式：

l gather

——

重新分析整个架构（Schema）。

l gather empty——只分析目前还没有统计的表。

l gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。

l gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用

gather auto类似于组合使用gather stale和gather empty。

注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命

令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分

析统计数据以来，发生了多少次插入、更新和删除操作。

estimate_percent选项

以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要

采样的一个segment的最佳百分比：

estimate_percent => dbms_stats.auto_sample_size

要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样

时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。

method_opt选项

dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于

判断哪些列需要直方图（histograms）。

某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在

where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现

这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只

有在列值要求必须修改执行计划时，才应使用直方图。

为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的

新选项，包括skewonly，repeat和auto：

method_opt=>'for all columns size skewonly'

method_opt=>'for all columns size repeat'

method_opt=>'for all columns size auto'

金桥论坛已阅读次数:691

页码，1/2金桥网络信息服务网

2006-7-20http://www.gbnis.com/read_file.php?id=4271&left=db

PDF 文件使用 "pdfFactory Pro" 试用版本创建 www.fineprint.com.cn

skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。

假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器

决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所

示，那么为了检索这些行，全表扫描的速度会快于索引扫描。

--*************************************************************

-- SKEWONLY option—Detailed analysis

-- Use this method for a first-time analysis for skewed indexes

-- This runs a long time because all indexes are examined

--*************************************************************

begin

dbms_stats.gather_schema_stats(

ownname => 'SCOTT',

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all columns size skewonly',

degree => 7

);

end;

重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项（清单C）

时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这

种方式。

--**************************************************************

-- REPEAT OPTION - Only reanalyze histograms for indexes

-- that have histograms

-- Following the initial analysis, the weekly analysis

-- job will use the “repeat” option. The repeat option

-- tells dbms_stats that no indexes have changed, and

-- it will only reanalyze histograms for

-- indexes that have histograms.

--**************************************************************

begin

dbms_stats.gather_schema_stats(

ownname => 'SCOTT',

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all columns size repeat',

degree => 7

);

end;

使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D

所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直

方图。使用method_opt=>

’

auto

’

类似于在dbms_stats的option参数中使用gather auto。

begin

dbms_stats.gather_schema_stats(

ownname => 'SCOTT',

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all columns size auto',

degree => 7

);

end;

并行收集

Oracle允许以并行方式来收集CBO统计数据，这就显著提高了收集统计数据的速度。但是，要想并行收集统计数

据，你需要一台安装了多个CPU的SMP服务器。

更快的执行速度

dbms_stats是提高SQL执行速度的一种出色机制。通过使用dbms_stats来收集最高质量的统计数据，CBO能够正

确判断执行任何SQL查询时的最快途径。dbms_stats还在不断地改进。目前，它的一些令人激动的新特性（自动

样本大小和自动直方图生成）已经显著简化了Oracle专家的工作。

发表评论返回页首关闭窗口

网址：http://www.gbnis.com http://gbnis.raedu.net

页码，2/2金桥网络信息服务网

2006-7-20http://www.gbnis.com/read_file.php?id=4271&left=db

PDF 文件使用 "pdfFactory Pro" 试用版本创建 www.fineprint.com.cn

首页 PL/SQL 数据库结构备份与恢复性能优化 OCP认证数据库理论其它数据库 UNIX基础常用软件电子书籍

2006年7月20日星期四

ORACLE 数据库的统计数据及其生成方式

ORACLE 数据库的 PL/SQL 语句执行的优化器，有基于代价的优化器（ CBO ）和基于规则的优化器（ RBO ）。

RBO 的优化方式，依赖于一套严格的语法规则，只要按照规则写出的语句，不管数据表和索引的内容是否发生变化，不会影响 PL/SQL 语

句的 " 执行计划 " 。

CBO 自 ORACLE 7 版被引入， ORACLE 自 7 版以来采用的许多新技术都是只基于 CBO 的，如星型连接排列查询，哈希连接查询，反向索

引，索引表，分区表和并行查询等。 CBO 计算各种可能 " 执行计划 " 的 " 代价 " ，即 cost ，从中选用 cost 最低的方案，作为实

际运行方案。各 " 执行计划 " 的 cost 的计算根据，依赖于数据表中数据的统计分布， ORACLE 数据库本身对该统计分布是不清楚的，

须要分析表和相关的索引，才能搜集到 CBO 所需的数据。

CBO 是 ORACLE 推荐使用的优化方式，要想使用好 CBO ，使 SQL 语句发挥最大效能，必须保证统计数据的及时性。

统计信息的生成可以有完全计算法和抽样估算法。 SQL 例句如下：

完全计算法： analyze table abc compute statistics;

抽样估算法 ( 抽样 20%) ： analyze table abc estimate statistics sample 20 percent;

对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有精确数

据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。

我们可以采用以下两种方法，对数据库的表和索引及簇表定期分析生成统计信息，保证应用的正常性能。

1. 在系统设置定时任务，执行分析脚本。

在数据库服务器端，我们以 UNIX 用户 oracle ，运行脚本 analyze, 在 analyze 中，我们生成待执行 sql 脚本，并运行。（假设我们

要分析 scott 用户下的所有表和索引）

Analyze 脚本内容如下：

sqlplus scott/tiger << EOF

set pagesize 5000

set heading off

SPOOL ANALYTAB.SQL

SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;' FROM USER_TABLES ；

SPOOL OFF

SPOOL ANALYIND.SQL

SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES

；

SPOOL OFF

SPOOL ANALYZE.LOG

@ANALYTAB.SQL

@ANALYIND.SQL

SPOOL OFF

EXIT

在 UNIX 平台上 crontab 加入，以上文件，设置为每个月或合适的时间段运行。

2. 利用 ORACLE 提供的程序包（ PACKAGE ）对相关的数据库对象进行分析。

页码，1/3ORACLE 数据库的统计数据及其生成方式

2006-7-20http://www.dbonline.cn/source/oracle/20040213/TUNING_Oracle%20statistic%20data%20and%20gen...

PDF 文件使用 "pdfFactory Pro" 试用版本创建 www.fineprint.com.cn

剩余31页未读，继续阅读

评论收藏

内容反馈

usersjb

粉丝: 0
资源: 32

ORACLE 数据库的统计数据及优化

最新资源

ORACLE 数据库的统计数据及优化

ORACLE数据库的优化

ORACLE数据库优化

ORACLE数据库设计与优化

ORACLE数据库参数优化与分析

ORACLE数据库性能优化

oracle 数据库优化技术资料

高性能动态SQL Oracle数据安全 Oracle 数据库的聚簇技术 等等

Oracle数据库管理员技术指南

ORACLE 数据库优化指南

整理Oracle数据库中数据查询优化的一些关键点

Oracle数据库的性能优化

用java编程将txt文件数据导入oracle

Oracle数据库实验操作

Object Browser7.0中文版（ORACLE数据库的开发工具）

腾讯大讲堂38-Oracle基础体系结构及性能优化

oracle数据库dba管理手册

Oracle 高性能SQL引擎剖析：SQL优化与调优机制详解 (黄玮) 高清PDF扫描版

数据库优化--局部数据迁移

ORACLE数据库DBA面试集锦

《Oracle DBA手记——数据库诊断案例与性能优化实践》第一章 to be continued

oci.dll 12版本全部

最新资源

高性能动态SQL Oracle数据安全 Oracle 数据库的聚簇技术等等