Oracle数据库的表与索引保证性能的方案介绍

数据库 Oracle
以下的文章主要是对Oracle数据库的相关统计数据以及Oracle数据库生成方式的详细介绍,以下就是文章的具体内容的描述。

PL/SQL语句是Oracle数据库执行的优化器,它有基于代价的相关的优化器(CBO)与基于规则的相关的优化器(RBO)。我们大家都知道RBO的相关优化方式,都是依赖于一套严格的语法规则。

只要按照规则写出的语句,不管数据表和索引的内容是否发生变化,不会影响PL/SQL语句的"执行计划"。

 

CBO自Oracle 7版被引入,Oracle自7版以来采用的许多新技术都是只基于CBO的,如星型连接排列查询,哈希连接查询,反向索引,索引表,分区表和并行查询等。CBO计算各种可能"执行计划"的"代价",即cost,从中选用cost***的方案,作为实际运行方案。

各"执行计划"的cost的计算根据,依赖于数据表中数据的统计分布,Oracle数据库本身对该统计分布是不清楚的,须要分析表和相关的索引,才能搜集到CBO所需的数据。

 

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

 

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

 

完全计算法:

  1. analyze table abc compute statistics; 

 

抽样估算法(抽样20%):

  1. analyze table abc estimate statistics sample 20 percent; 

 

对表作完全计算所花的时间相当于做全表扫描,抽样估算法由于采用抽样,比完全计算法的生成统计速度要快,如果不是要求要有精确数据的话,尽量采用抽样分析法。建议对表分析采用抽样估算,对索引分析可以采用完全计算。

 

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

 

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

 

在Oracle数据库服务器端,我们以UNIX用户Oracle,运行脚本analyze,在analyze中,我们生成待执行sql脚本,并运行。(假设我们要分析scott用户下的所有表和索引)

 

Analyze脚本内容如下:

 

  1. sqlplus scott/tiger << EOF 
  2. set pagesize 5000  
  3. set heading off  
  4. SPOOL ANALYTAB.SQL  
  5. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||

     

    ' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;' FROM USER_TABLES;  
  6. SPOOL OFF  
  7. SPOOL ANALYIND.SQL  
  8. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||

     

    ' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES;  
  9. SPOOL OFF  
  10. SPOOL ANALYZE.LOG  
  11. @ANALYTAB.SQL  
  12. @ANALYIND.SQL  
  13. SPOOL OFF  
  14. EXIT  

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

2. 利用Oracle提供的程序包(PACKAGE)对相关的Oracle数据库对象进行分析。

有以下的程序包可以对表,索引,簇表进行分析。

 

包中的存储过程的相关参数解释如下:

 

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. 1)   
  2. DBMS_DDL.ANALYZE_OBJECT(  
  3. TYPE VARCHAR2,  
  4. SCHEMA VARCHAR2,  
  5. NAME VARCHAR2,  
  6. METHOD VARCHAR2,  
  7. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  8. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  9. METHOD_OPT VARCHAR2 DEFAULT NULL,  
  10. PARTNAME VARCHAR2 DEFAULT NULL ) ; 

该存储过程可对特定的表,索引和簇表进行分析。

例如,对SCOTT用户的EMP表,进行50%的抽样分析,参数如下:

  1. DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL,50);   
  2. 2)   
  3. DBMS_UTILITY.ANALYZE_SCHEMA (  
  4. SCHEMA VARCHAR2,  
  5. METHOD VARCHAR2,  
  6. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  7. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  8. METHOD_OPT VARCHAR2 DEFAULT NULL ) ;  
  9. DBMS_UTILITY.ANALYZE_DATABASE (  
  10. METHOD VARCHAR2,  
  11. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  12. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  13. METHOD_OPT VARCHAR2 DEFAULT NULL ) ;   

 其中,ANALYZE_SCHEMA用于对某个用户拥有的所有TABLE,INDEX和CLUSTER的分析统计。

ANALYZE_DATABASE用于对整个Oracle数据库进行分析统计。

3) DBMS_STATS是在Oracle8I中新增的程序包,它使统计数据的生成和处理更加灵活方便,并且可以并行方式生成统计数据。在程序包中的以下过程分别分析统计TABLE,INDEX,SCHEMA,DATABASE级别的信息。

  1. DBMS_STATS.GATHER_TABLE_STATS  
  2. DBMS_STATS.GATHER_INDEX_STATS  
  3. DBMS_STATS.GATHER_SCHEMA_STATS  
  4. DBMS_STATS.GATHER_DATABASE_STATS  

在这里,我们以数据库JOB的方式,定时对Oracle数据库中SCOTT模式下所有的表和索引进行分析:

在SQL*PLUS下运行:

 

  1. VARIABLE jobno number;  
  2. BEGIN  
  3. DBMS_JOBS.SUBMIT ( :jobno ,  
  4. ' dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ',  
  5. sysdate, 'sysdate+30');  
  6. commit;  
  7. end;  
  8. /  
  9. Statement processed.  
  10. Print jobno  
  11. JOBNO  
  12. 16 

以上作业,每隔一个月用DBMS_UTILITY.ANALYZE_SCHEMA对用户SCOTT的所有表,簇表和索引作统计分析

文章出自:http://database.51cto.com/art/200703/43583.htm

【编辑推荐】

  1. Oracle数据库外部的身份认证的正确方法
  2. Oracle监听器出现的6种连接问题及其解决方法
  3. 对Oracle数据库的外部身份认证的2种方案
  4. Oracle数据库表的大小修改5方案
  5. Oracle数据库文件移动的实现
责任编辑:佚名 来源: programbbs
相关推荐

2023-11-16 17:12:33

数据库oracle

2010-05-07 17:39:02

Oracle数据库性能

2010-04-07 09:31:02

Oracle数据库

2011-03-22 14:49:35

Oracle数据库重定义表

2010-04-21 14:45:39

Oracle数据库

2010-04-26 14:24:58

Oracle数据库索引

2011-03-07 17:51:00

Oracle数据库优缺点

2010-04-09 13:59:48

Oracle数据库索引

2010-04-23 10:18:42

Oracle数据库字符

2011-03-16 08:54:45

Oracle数据库索引

2010-06-04 11:28:05

MySQL数据库

2010-04-22 17:06:24

Oracle数据库

2010-04-15 13:01:25

Oracel数据库

2010-04-19 12:16:53

Oracle数据库

2010-04-13 17:35:50

Oracle数据库性能

2011-04-12 10:21:24

Oracle数据库索引树

2011-05-26 09:08:31

Oracle数据库

2010-04-26 09:28:43

Oracle数据库

2010-04-22 16:00:45

Oracle数据库

2010-02-01 10:10:41

Oracle数据库优化
点赞
收藏

51CTO技术栈公众号