Oracle数据库的统计数据的实际应用

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

我们今天主要讲述的是Oracle数据库的统计数据以及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例句如下:

 

完全计算法:

  1. analyze table abc compute statistics; 

 

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

  1. analyze table abc estimate statistics sample 20 percent; 

 

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

 

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

 

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.'  
  6. ||TABLE_NAME  
  7. ||' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;  
  8. ' FROM USER_TABLES;  
  9. SPOOL OFF  
  10. SPOOL ANALYIND.SQL  
  11. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME
    ||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;
    ' FROM USER_TABLES;  
  12. SPOOL OFF  
  13. SPOOL ANALYZE.LOG  
  14. @ANALYTAB.SQL  
  15. @ANALYIND.SQL  
  16. SPOOL OFF  
  17. 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. DBMS_DDL.ANALYZE_OBJECT(  
  2. TYPE VARCHAR2,  
  3. SCHEMA VARCHAR2,  
  4. NAME VARCHAR2,  
  5. METHOD VARCHAR2,  
  6. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  7. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  8. METHOD_OPT VARCHAR2 DEFAULT NULL,  
  9. PARTNAME VARCHAR2 DEFAULT NULL ) ; 

 

 

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

 

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

 

DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL,50);

 

2)

 

  1. DBMS_UTILITY.ANALYZE_SCHEMA (  
  2. SCHEMA VARCHAR2,  
  3. METHOD VARCHAR2,  
  4. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  5. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  6. METHOD_OPT VARCHAR2 DEFAULT NULL ) ;  
  7. DBMS_UTILITY.ANALYZE_DATABASE (  
  8. METHOD VARCHAR2,  
  9. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  10. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  11. 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://www.programbbs.com/doc/class10-3.htm

【编辑推荐】

  1. Oracle 语句的优化规则中的相关项目
  2. Oracle实例如何创建?
  3. Oracle sql 性能如何进行调整
  4. Oracle sql 性能的优化调整的2大步骤
  5. 对Oracle SQL相同语句的解析
责任编辑:佚名 来源: programbbs
相关推荐

2010-05-04 16:50:04

Oracle数据库

2010-04-13 12:23:34

Oracle数据库

2023-07-18 10:43:14

物联网IOT

2020-04-24 09:01:23

网络安全数据泄露黑客

2019-06-27 05:00:26

物联网统计数据IOT

2010-05-13 13:49:09

MySQL数据库

2022-10-26 15:17:58

数字存储数据中心

2010-04-19 11:37:20

Oracle数据库

2021-08-17 06:48:43

SpringbootKafkaStream

2011-10-09 10:33:12

2010-04-19 09:26:04

Oracle数据库

2010-04-14 15:30:41

Oracle数据库

2010-03-30 11:15:26

Oracle数据库

2010-05-20 15:22:37

2010-05-28 11:41:46

MySQL数据库备份

2015-07-29 11:21:13

JavaScript统计数据

2010-04-02 16:13:38

Oracle 数据库

2022-10-08 14:29:58

数据安全钓鱼邮件

2022-10-26 15:59:16

2010-04-28 15:04:37

Oracle数据
点赞
收藏

51CTO技术栈公众号