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 用户下的所有表和索引), 在 UNIX 平台上 crontab 加入以下文件, 设置为每个月或合适的时间段运行.
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
2. 利用 ORACLE 提供的程序包(PACKAGE)对相关的数据库对象进行分析.
有以下的程序包可以对表, 索引, 簇表进行分析.
包中的存储过程的相关参数解释如下:
TYPE: TABLE, INDEX, CLUSTER.
SCHEMA: TABLE, INDEX, CLUSTER 的所有者, NULL 为当前用户.
NAME: 相关对象的名称.
METHOD: ESTIMATE, COMPUTE, DELETE, 当选用 ESTIMATE 时, ESTIMATE_ROWS 和 ESTIMATE_PERCENT 不能同时为空值.
ESTIMATE_ROWS: 估算的抽样行数.
ESTIMATE_PERCENT: 估算的抽样百分比.
METHOD_OPT:
FOR TABLE /*只统计表*/
[FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只统计有索引的表列*/
FOR ALL INDEXES /*只分析统计相关索引*/
PARTNAME: 指定要分析的分区名称.
1)
DBMS_DDL.ANALYZE_OBJECT(
TYPE VARCHAR2,
SCHEMA VARCHAR2,
NAME VARCHAR2,
METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL,
PARTNAME VARCHAR2 DEFAULT NULL
);
该存储过程可对特定的表, 索引和簇表进行分析.
例如: 对 SCOTT 用户的 EMP 表, 进行 50% 的抽样分析, 如下:
DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL, 50);
2)
DBMS_UTILITY.ANALYZE_SCHEMA(
SCHEMA VARCHAR2,
METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL
);
DBMS_UTILITY.ANALYZE_DATABASE(
METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL
);
ANALYZE_SCHEMA 用于对某个用户拥有的所有 TABLE, INDEX 和 CLUSTER 的分析统计.
ANALYZE_DATABASE 用于对整个数据库进行分析统计.
3) DBMS_STATS 是在 ORACLE8I 中新增的程序包, 它使统计数据的生成和处理更加灵活方便, 并且可以并行方式生成统计数据.
在程序包中的以下过程分别分析统计 TABLE, INDEX, SCHEMA, DATABASE 级别的信息.
DBMS_STATS.GATHER_TABLE_STATS
DBMS_STATS.GATHER_INDEX_STATS
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_DATABASE_STATS
在这里, 我们以数据库 JOB 的方式, 定时对数据库中 SCOTT 模式下所有的表和索引进行分析:
在 SQL*PLUS 下运行:
VARIABLE jobno number;
BEGIN
DBMS_JOBS.SUBMIT(:jobno, 'dbms_utility.analyze_schema("scott", "estimate", NULL, 20);', sysdate, 'sysdate + 30');
commit;
end;
/
Statement processed.
Print jobno
JOBNO
-------------
16
以上作业, 每隔一个月用 DBMS_UTILITY.ANALYZE_SCHEMA 对用户 SCOTT 的所有表, 簇表和索引作统计分析
分享到:
相关推荐
Oracle优化Oracle优化Oracle优化Oracle优化Oracle优化Oracle优化
基于成本的oracle优化法则中文版 基于成本的oracle优化法则中文版
基于成本的Oracle优化法则,作者:刘易斯, ★数据库领域的超级畅销书,世界级Oracle 大师Jonathan Lewis的最新力作! ★荣获2006年年度Oracle杂志编辑选择大奖!
Oracle优化原则整理,里面主要是整理了一些写Oracle SQL 基本的优化方式~~
oracle优化技巧oracle优化技巧oracle优化技巧oracle优化技巧
ORACLE优化的设计方案,前辈dba经验的总结。
优化笔记 sql性能的调整-总结 SQL代码性能优化 Oracle语句优化53个规则详解 ORACLE9i优化设计与系统调整 oracle9i优化器介绍 oracle9i的查询优化 ……
oracle优化 SQL优化 嘞哥Oracle数据优化的集合 分享
其中主要包括收集数据库统计系统、收集列直方图、分析SQL执行计划、如何让CBO优化器选择最优的执行计划,以及如何使用Hint提示认为改变CBO优化器的执行计划等,希望此文档能够帮助大家更深入地理解Oracle优化!
Oracle优化常用概念.pptx
oracle优化规则总汇,详细全面 oracle优化规则总汇,详细全面 oracle优化规则总汇,详细全面
该培训资料中包括5大内容块: 1>Oracle分区技术及大数据量操作性能优化 2>Oracle性能问题一般解决思路 3>Oracle常用hint 4>Oracle动态图 5>Oracle执行计划和SQL调优
ORACLE SQL性能优化系列 ORACLE SQL性能优化系列 ORACLE SQL性能优化系列
常用的oracle优化方法,希望对大家有帮助
《Oracle优化日记:一个金牌DBA的故事》是一本介绍Oracle数据库优化方法的书,以一个实际的大型优化项目为原型,用日记的形式记录了一个优化小组的DBA 如何从纷繁的头绪中找到突破口,进而完成了一个看似不可能完成...
基于成本的oracle优化法则 中文 oracle 优化法则
Oracle优化器的概念、工作原理和使用方法,兼顾了Oracle8i、9i以及最新的10g三个版本。理解本文将有助于您更好的更有效的进行SQL优化工作。
Oracle语句优化规则汇总Oracle语句优化规则汇总Oracle语句优化规则汇总
oracle优化建议34条,只要是针对索引的建议,值得一观