oracle 优化3

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 的所有表, 簇表和索引作统计分析