精华内容
下载资源
问答
  • 并行收集统计信息

    2017-11-11 09:41:00
    实际上也只能用到parallel_max_servers设定的值parallel_max_serversselect * from v$session_longops where opname like '%Gather Database Statistics%'可以查看收集的进度从v$session中可以查看实际使用的并行度...

    设置参数值,否则即使任务开启了100个并行,实际上也只能用到parallel_max_servers设定的值
    parallel_max_servers

    select * from v$session_longops where opname like '%Gather Database Statistics%'可以查看收集的进度

    从v$session中可以查看实际使用的并行度。

    转载于:https://www.cnblogs.com/Clark-cloud-database/p/7818252.html

    展开全文
  • 本文针对并行和并发收集统计信息的相关知识内容以及部分案例进行介绍,并重点介绍并发统计信息收集。

    并行和并发收集统计信息

    概述

    随着应用数据的增多和表量的增加,为了增加统计信息收集的效率,Oracle推出了并行和并发收集统计信息的方法。

    本文将针对并行和并发收集统计信息的相关知识内容以及部分案例进行介绍,并重点介绍并发统计信息收集。

    并行收集统计信息(PARALLEL )

    当某个表的Size特别大时,可以通过并行的Slave进程共同工作来加快统计信息收集的效率。
    默认情况下,数据库能够根据在表或者索引级别设置的并行度(默认:1)进行并行统计信息收集。
    但我们也可以通过显示地设置degree参数来控制并行统计信息收集的并行度。

    DEGREE参数:

    DEGREE参数用于控制统计信息收集的并行度。
    你可以通过以下的方式进行赋值:

    1.通过DBMS_STATS.SET_*_PREFS包设置全局变量
    2.通过DBMS_STATS.GATHER_*_STATS包设置某次执行变量
    

    例:

    EXEC DBMS_STATS.SET_SCHEMA_PREFS('SH', 'DEGREE','4');
    or
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH', DEGREE=>4); 
    

    Oracle推荐指定degree参数为DBMS_STATS.AUTO_DEGREE,由Oracle根据对象的大小和并行参数的设置情况来决定统计信息收集的并行度。
    例:

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'SH', DEGREE => DBMS_STATS.AUTO_DEGREE);
    

    ※注意:
    Oracle不能并行收集某些类型的索引,如:cluster indexes, domain indexes, and bitmap join indexes。

    并发收集统计信息(CONCURRENT)

    并行的统计信息收集仅仅意味着:对某个对象进行统计收集时会采用多个并行Slave进行处理,但是对于多个对象(表、索引或分区)来讲,处理还是串行的。
    即:处理完一个对象后再去处理下一个对象。

    从11.2.0.2 开始,Oracle为了能够使多个对象的统计信息收集也能够同时进行,推出了并发收集统计信息(CONCURRENT)模式,使多进程的环境更加有效率。
    即:同时启动多个JOB,并发地处理多个对象(表、索引或分区)。

    从12.1.0.1 开始,Oracle进一步扩展了并发收集统计信息,使Oracle自动统计收集任务(automatic statistics gather task)也能从中受益使用并发收集统计信息。

    并发收集统计信息主要使用了以下的几种技术:

    Oracle Scheduler:用于启动多个Job
    Advanced Queuing (AQ):用于控制处理的排序
    Resource Manager :管理使用资源
    

    要启用并发收集统计信息,需要设置以下的参数:

    CONCURRENT:启用并发收集统计信息功能
    JOB_QUEUE_PROCESSES:最大JOB数
    RESOURCE_MANAGER_PLAN:启用Resource Manager有效
    

    并发收集统计信息时,数据库生成的JOB数会根据具体情况来分配,大多数情况下,DBMS_STATS 程序会给每个对象分配一个JOB;但如果对象(表或者分区)的大小太小,为了节省资源,Oracle会合并多个表和分区在一个JOB中执行。

    ※注意:
    为了防止同时处理多个分区表的分区时发生死锁,对于分区表是通过Queue的机制进行处理的。
    即:每次只能处理一个分区表,其他的需要在Queue等待,待前一个分区表处理完后再处理下一个。

    以下是Oracle白皮书中的一个并发收集统计信息的例图:

    这里写图片描述

    通过该例子我们可以看到,在针对并发收集统计信息时会有不同层级的JOB,
    对于分区表除了一个协调JOB外还会针对各个分区分配一个JOB。
    另外,如同前面所讲的,多个分区表不能同时处理。图中的COSTS表和SALES表的处理会被排序,即:COSTS表的JOB结束后SALES表的处理才会开始。

    CONCURRENT参数:

    CONCURRENT参数用于控制并发收集统计信息。需要通过DBMS_STATS.SET_GLOBAL_PREFS来进行全局设置。

    在11.2.0.2~11.2.0.4的版本上,可以设置的值:

    TRUE  :并发有效
    FALSE :并发无效。
    

    12c的版本上,可以设置以下的值:

    'MANUAL'   :只有当手动收集时,并发有效
    'AUTOMATIC':只有当自动收集时,并发有效   
    'ALL'      : 当手动/自动收集,并发都有效
    'OFF'      : 并发无效
    

    并发执行例1(11.2.0.3):

    11.2.0.3环境上的测试:

    1.测试数据的准备:

    SQL> conn scott/tiger
    Connected.
    SQL> CREATE TABLE TBL1_NONPART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;
    Table created.  
    SQL> CREATE TABLE TBL2_NONPART2 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
    Table created.  
    SQL> CREATE TABLE TBL3_NONPART3 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
    Table created.  
    SQL> CREATE TABLE TBL4_PART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2))
    PARTITION BY LIST (COL1)(
     PARTITION PTBL1 values ('1'),
     PARTITION PTBL2 values ('2'),
     PARTITION PTBL3 values ('3')
    ) ;  2    3    4    5    6  
    Table created.
    SQL> begin 
    for i in 1..2000000 loop 
        insert into TBL1_NONPART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL2_NONPART2 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL3_NONPART3 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL4_PART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL4_PART1 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL4_PART1 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    commit; 
    end loop; 
    end; 
    / 
    PL/SQL procedure successfully completed.
    

    2.设置相关的参数:

    ---2.1.使Resource Manager有效(DEFAULT_PLAN)
    SQL> conn /as sysdba
    Connected.
    SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
    System altered.
    
    ---2.2.查看JOB_QUEUE_PROCESSES设置,如果需要可以改变
    SQL> SELECT VALUE FROM v$parameter WHERE NAME='job_queue_processes';
    VALUE
    --------------------------------------------------------------------------------
    1000
    
    ---2.3.设置CONCURRENT为TRUE  ※
    SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
    DBMS_STATS.GET_PREFS('CONCURRENT')
    --------------------------------------------------------------------------------
    OFF
    
    SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
    PL/SQL procedure successfully completed.
    
    SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
    DBMS_STATS.GET_PREFS('CONCURRENT')
    --------------------------------------------------------------------------------
    TRUE
    

    3.执行统计信息收集

    SQL> conn /as sysdba
    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
    PL/SQL procedure successfully completed.
    

    4.监视结果

    SQL> select job_name, state, comments
    from dba_scheduler_jobs
    where job_class like 'CONC%';
    
    JOB_NAME       STATE                                         COMMENTS
    -------------- --------------------------------------------- ------------------------
    ST$SD1_7       RUNNING                                       "SCOTT"."TBL3_NONPART3".
    ST$SD1_6       RUNNING                                       "SCOTT"."TBL2_NONPART2".
    ST$SD1_5       RUNNING                                       "SCOTT"."TBL1_NONPART1".
    ....
    

    并发执行例2(12.1.0.2 ):

    12.1.0.2 环境上的测试:

    1.测试数据的准备:

    SQL> conn scott/tiger
    Connected.
    SQL> CREATE TABLE TBL1_NONPART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;
    Table created.  
    SQL> CREATE TABLE TBL2_NONPART2 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
    Table created.  
    SQL> CREATE TABLE TBL3_NONPART3 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
    Table created.  
    SQL> CREATE TABLE TBL4_PART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2))
    PARTITION BY LIST (COL1)(
     PARTITION PTBL1 values ('1'),
     PARTITION PTBL2 values ('2'),
     PARTITION PTBL3 values ('3')
    ) ;  2    3    4    5    6  
    Table created.
    SQL> begin 
    for i in 1..4000000 loop 
        insert into TBL1_NONPART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL2_NONPART2 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL3_NONPART3 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL4_PART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL4_PART1 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
        insert into TBL4_PART1 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    commit; 
    end loop; 
    end; 
    / 
    PL/SQL procedure successfully completed.
    

    2.设置相关的参数:

    ---2.1.使Resource Manager有效(DEFAULT_PLAN)
    SQL> conn /as sysdba
    Connected.
    SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
    System altered.
    
    ---2.2.查看JOB_QUEUE_PROCESSES设置,如果需要可以改变
    SQL> SELECT VALUE FROM v$parameter WHERE NAME='job_queue_processes';
    VALUE
    --------------------------------------------------------------------------------
    1000
    
    ---2.3.设置CONCURRENT为ALL
    SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
    DBMS_STATS.GET_PREFS('CONCURRENT')
    --------------------------------------------------------------------------------
    OFF
    
    SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
    PL/SQL procedure successfully completed.
    
    SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
    DBMS_STATS.GET_PREFS('CONCURRENT')
    --------------------------------------------------------------------------------
    ALL
    

    3.执行统计信息收集

    SQL> conn /as sysdba
    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
    PL/SQL procedure successfully completed.
    

    4.监视结果

    SQL> SET LINESIZE 200
    SQL> COLUMN TARGET FORMAT a25
    SQL> COLUMN TARGET_TYPE FORMAT a25
    SQL> COLUMN JOB_NAME FORMAT a14
    SQL> COLUMN START_TIME FORMAT a40
    SQL> SELECT TARGET, TARGET_TYPE, JOB_NAME,
           TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi:ss')
    FROM   DBA_OPTSTAT_OPERATION_TASKS
    WHERE  STATUS = 'IN PROGRESS'
    AND    OPID = (SELECT MAX(ID)
                   FROM   DBA_OPTSTAT_OPERATIONS
                   WHERE  OPERATION = 'gather_schema_stats');
      2    3    4    5    6    7  
    
    TARGET                    TARGET_TYPE               JOB_NAME       TO_CHAR(START_TIME,'D
    ------------------------- ------------------------- -------------- ---------------------
    SCOTT.TBL4_PART1          TABLE (COORDINATOR JOB)   ST$SD42_2      18-jun-2016 14:07:56
    SCOTT.TBL4_PART1          TABLE (GLOBAL STATS ONLY) ST$T44_2       18-jun-2016 14:07:57
    SCOTT.SYS_C0010346        INDEX                     ST$T44_2       18-jun-2016 14:08:11
    SCOTT.TBL1_NONPART1       TABLE                     ST$SD42_1_B7   18-jun-2016 14:07:58
    SCOTT.SYS_C0010343        INDEX                     ST$SD42_1_B7   18-jun-2016 14:08:09
    

    并发统计信息收集的监视

    可以通过以下的视图,对并发统计信息收集进行监视

    DBA_OPTSTAT_OPERATION_TASKS:当前和历史的统计信息收集的执行任务 (12c)
    DBA_OPTSTAT_OPERATIONS     :当前和历史的统计信息收集的执行操作 (12c)
    DBA_SCHEDULER_JOBS         : SCHEDULER JOBS信息
    

    并行 VS 并发

    有时候可能对并行和 并发统计信息收集的概念有些混淆,下面我们通过一张表来总结对比一下并行和并发统计信息收集:

    这里写图片描述

    并发和并行执行统计信息收集组合

    为了提高效率,可以使并发和并行执行统计信息收集同时有效,这种组合使用对于非常大的表和分区非常有效。
    要使组合有效,你需要再设置PARALLEL_ADAPTIVE_MULTI_USER参数为False,以防止自适应导致的并行无效。

    例:

    ALTER SYSTEM SET PARALLEL_ADAPTIVE_MULTI_USER=false;
    

    关于PARALLEL_ADAPTIVE_MULTI_USER参数,可以参考在线文档:

    Database Reference
    PARALLEL_ADAPTIVE_MULTI_USER

    咨询案例:

    在笔者的经验中,曾遇到过以下的几个咨询案例,在这里对调查方法和结果进行一些分享。

    SE版本并发统计信息收集是否有效?

    虽然并发执行统计信息收集使用的技术中包括Resource Manager,并且Resource Manager是Enterprise Edition版本才能使用的功能,但是Standard Edition内部的一些动作也会用到Resource Manager功能,所以不明确使用Resource Manager的情况下,也能够进行并发执行统计信息收集。
    通过上面的例子在Standard Edition运行,也可以验证这个结论。

    设定相关内容,并发统计信息收集却无效?

    有用户咨询,为什么我的环境中设定了并发执行统计信息收集,查看相关的视图却发现,统计信息收集时并没有并发执行?
    这个问题,其实由于进行并发执行时,Oracle内部事实上是有一定临界值设定的。当这些表很小和其他一些环境因素时,Oracle会合并多个表和分区在一个JOB中批量执行,就会产生统计信息收集时没有并发执行的表象。

    我们可以通过跟踪dbms_stats可以查看到相关的一些内容。
    例:

    SQL> conn /as sysdba
    SQL> exec dbms_stats.set_global_prefs('TRACE', 4+8+16+128+2048);
    PL/SQL procedure successfully completed.
    
    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_stats.set_global_prefs('TRACE', 0);
    PL/SQL procedure successfully completed.
    

    查看相关的跟踪文件:

    DBMS_STATS: Estimate cost for target:  ownname: "SCOTT" tabname: "TBL2_NONPART2" partname:  subpartname:  type: 1
    DBMS_STATS:   --> Estimated cost for 12137 blocks is : 12.137
    DBMS_STATS: process_task: cost: 12.137, batching threshold: 50 ★
    DBMS_STATS: priority: 6 ownname: "SCOTT" stattab: 
    DBMS_STATS: Adding task SCOTT.TBL2_NONPART2 into the current batch.★ Task Cost: 12.137, current batch size: 5, current batch cost: 13.195299, batching_coeff: .24274
    DBMS_STATS: @ Adding params into cctx..
    DBMS_STATS: Scheduling Manager State (has_more): sofar: 6 sofar_dl: 0 deadlockQueue.count: 0 sofar_ix: 0 ixDependencyQueue.count: 0 concurrent: TRUE 
    

    我们可以看到,在DBMS_STATS执行过程中在做一些比较,如果预估值没有满足临界值的话,就会进行批量处理。

    ※注意:
    这个临界值(batching threshold)由多方面因素影响,会根据环境不同而不同。

    如何限定仅对一部分表进行并发统计信息收集?

    在过去的咨询案件中,确实有些客户希望仅对某Schema的一部分表进行并发统计信息收集。
    针对这种需求可以通过DBMS_STATS的“obj_filter_list”参数来实现。

    例:

    DECLARE
          filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
          obj_lst     DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
    BEGIN
      filter_lst.extend(5);
      filter_lst(1).ownname := 'SH';
      filter_lst(1).objname := 'SALES';
      filter_lst(2).ownname := 'SH';
      filter_lst(2).objname := 'COSTS';
      filter_lst(3).ownname := 'SH';
      filter_lst(3).objname := 'SALES2';
      filter_lst(4).ownname := 'SH';
      filter_lst(4).objname := 'COSTS2';
      filter_lst(5).ownname := 'SH';
      filter_lst(5).objname := 'SALES3';
     DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SH',objlist=>obj_lst, obj_filter_list=>filter_lst);
    END;
    /
    

    关于这点,详细可以参考 Maria Colgan-Oracle写的一个Blog:
    Oracle Optimizer Blog
    >How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

    参考:

    Database SQL Tuning Guide
    >Gathering Optimizer Statistics Concurrently

    Oracle Optimizer Blog
    >Concurrent Statistics Gathering
    >How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

    ★ White Papers
    Understanding Optimizer Statistics with Oracle Database 12c - Part 1

    Improving the efficiency of Gathering Statistics

    Concurrent Statistic gathering

    Best Practices for Gathering Optimizer Statistics with Oracle Database 12c - Part 2

    Improving the efficiency of gathering statistics

    Intra object parallelism
    Inter object parallelism

    版权声明:本文为博主原创文章,转载请注明出处,谢谢。http://blog.csdn.net/lukeunique

    展开全文
  • ORACLE 收集统计信息

    千次阅读 2014-08-07 23:56:46
    今天网上看到一篇关于收集统计信息的文章,还不错,特转载下来。 1. 理解什么是统计信息 优化器统计信息就是一个更加详细描述数据库和数据库对象的集合,这些统计信息被用于查询优化器,让其为每条SQL语句选择最佳...

    原文链接:http://blog.itpub.net/2317695/viewspace-1226138/

     

    今天网上看到一篇关于收集统计信息的文章,还不错,特转载下来。

    1.     理解什么是统计信息
    优化器统计信息就是一个更加详细描述数据库和数据库对象的集合,这些统计信息被用于查询优化器,让其为每条SQL语句选择最佳的执行计划。优化器统计信息包括:

    ·         表的统计信息
    o   行数
    o   Block数

    o   行平均长度
    ·         列的统计信息
    o   列中不同值的数量
    o   列中null的数量

    o   数据分布(柱状图/直方图)

    ·         索引的统计信息
    o   叶子块的数量
    o   索引的高度
    o   聚簇因子(clustering factor)

    ·         系统的统计信息
    o   I/O性能和利用

    o   CPU性能和利用

    优化器统计信息存储在下列数据字典中
    ·         DBA_TABLES

    ·         DBA_OBJECT_TABLES

    ·         DBA_TAB_STATISTICS

    ·         DBA_TAB_COL_STATISTICS

    ·         DBA_TAB_HISTOGRAMS

    ·         DBA_INDEXES

    ·         DBA_IND_STATISTICS

    ·         DBA_CLUSTERS

    ·         DBA_TAB_PARTITIONS

    ·         DBA_TAB_SUBPARTITIONS

    ·         DBA_IND_PARTITIONS

    ·         DBA_IND_SUBPARTITIONS

    ·         DBA_PART_COL_STATISTICS

    ·         DBA_PART_HISTOGRAMS

    ·         DBA_SUBPART_COL_STATISTICS

    ·         DBA_SUBPART_HISTOGRAMS

    ·         INDEX_STATS              存储ANALYZE ..VALIDATE STRUCTURE统计信息

    ·         AUX_STATS$               存储CPU统计信息

    ·         X$KCFIO                  存储I/O统计信息

    因为数据库中的对象会经常的变化,所以统计信息必须有规律的更新以便更加准确的描述这些数据库对象。统计信息默认是由ORACLE自动维护的,不过我们也可以用DBMS_STATS包手动收集统计信息。DBMS_STATS包同样提供了过程来维护统计信息。关于DBMS_STATS包更详细的描述请参阅官方文档PL/SQL Packages and Types Reference部分。
    2. 自动收集统计信息
    Oracle10g中,在安装Oracle的时候,就默认创建了一个名为GATHER_STATS_JOB的job来自动收集优化器统计信息。这个job收集数据库中所有对象的统计信息。默认的情况下这个job是周一到周五每天晚上10点到第二天早上6点以及整个周末来收集统计信息。

    可以查看DBA_SCHEDULER_JOBS, DBA_SCHEDULER_PROGRAMS,DBA_SCHEDULER_WINDOWS,DBA_SCHEDULER_JOB_RUN_DETAILS等视图来查看JOB设置以及运行信息

     

    自动收集过期的统计信息依赖于表监控特征,在Oracle10g中表监控默认是开启的,同时它也依赖STATISTICS_LEVEL参数的值,10g中默认为typical,只有将STATISTICS_LEVEL参数设置为ALL或者TYPICAL才能让ORACLE识别过期的统计信息

     

    3. 关闭自动收集统计信息
    在某些情况下,我们想关闭自动收集统计信息那么我们可以利用如下方法:

       BEGIN

          DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

       END;

       /

     

    4. 何时该手动收集统计信息


    有时候自动收集统计并不合适,因为自动收集统计信息是在午夜运行的,然而由于对象是在白天被修改了,导致导致的统计信息变得陈旧,这里有2种这类对象:

    ·         白天经常被delete,或者truncated之后又rebuild的表(经常变化的表)

    ·         批量操作之后有10%或者以上的数据被更改的表(批量处理的表)

    ·         对于经常变化的表,可以将其统计信息设置为null,当ORACLE遇到一个表没有统计信息,ORACLE会动态采样以便为查询优化器收集必要的统计信息。动态采样这个特征受到参数optimizer_dynamic_sampling的控制,它的默认值为2,同时呢optimizer_mode也能控制动态采样,可将其设置为all.

    以SCOTT用户下的DEPT表为例,将一个表的统计信息设置为null的方法如下:

    BEGIN  DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT');  DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');END;/我们也可以在表具有典型的,代表性的时候收集统计信息,并且锁住其统计信息,因为在夜晚自动收集的统计信息未必适用于白天的负载,而典型的统计信息具有代表意义,所以这个时候采取lock其典型的统计信息更能让CBO选择更优的执行计划。

     

    至于上面的两种方法用哪种,这个还需要根据业务,实际情况分析之。
    ·         对于批量处理的表 ,应该在批量处理完成的时候立即对其收集统计信息,可以将收集统计信息的脚本绑定到批量处理的脚本中。
    ·         对于外部表,只能通过gather_table_stats过程来收集统计信息,并且外部表不支持取样,所以需要把gather_table_stats中的estimate_percent设置为null

    ·         系统的统计信息也需要手动收集,因为这些信息是不会自动收集的。
    ·         对于固定对象,比如说动态性能表,需要手动的执行gather_fixed_objects_stats过程来收集。固定的对象反映了当前数据库的活动。当数据库活动处于具有代表性的时候,就应该收集这类统计信息。

     

    5. 锁住/解锁统计信息

     

    ·         LOCK_SCHEMA_STATS

    ·         LOCK_TABLE_STATS

    ·         UNLOCK_SCHEMA_STATS

    ·         UNLOCK_TABLE_STATS

     

    6. 手动收集统计信息


    ·         如果你选择手动收集统计信息,那么你需要手动的收集所有用户的统计信息,包括系统用户。如果你数据库中的数据是有规律的变化的,那么你可以有规律的收集统计信息,以便统计信息能够准确的反映数据库中的对象的特征。


    ·         可以利用DBMS_STATS包,来收集表,索引,列,以及分区表的统计信息,DBMS_STATS不能收集CLUSTER 的统计信息,不过可以收集单个表来代替收集整个CLUSTER的统计信息。

     

    ·         当你收集表,列,索引的统计信息的时候,如果ORACLE在数据字典中发现这个对象已经收集了统计信息,那么ORACLE会更新已经存在的统计信息,旧的统计信息会被保存下来,如果你愿意还能还原旧的统计信息

     

    ·         你可以使用DBMS_STATS.GATHER_DICTIONARY_STATS来收集系统用户的统计信息,这个过程收集所有的系统用户的统计信息,包括SYS和SYSTEM,以及其他用户,比如CTXSYS,DRSYS。

     

    ·         当数据库对象的统计信息被更新之后,ORACLE会使已经解析的SQL语句作废,当再次运行该SQL语句的时候,ORACLE会重新解析该SQL,优化器会自动的根据新的统计信息选择一条新的执行计划。对于分布式的数据库,不会作废。

     

    ·         收集统计信息的过程


    o   GATHER_INDEX_STATS      --收集索引统计信息

    o   GATHER_TABLE_STATS      --收集表,列,索引统计信息

    o   GATHER_SCHEMA_STATS     --收集schema所有对象统计信息

    o   GATHER_DICTIONARY_STATS –-收集所有系统用户的统计信息

    o   GATHER_DATABASE_STATS   --收集数据库所有对象统计信息

     

    ·         我们利用上面的过程收集统计信息的时候有几个需要关心的参数


    o   采样
    o   并行
    o   分区
    o   列统计以及直方图/柱状图
    o   过期的统计
    o   自定义统计


    Ø  在收集统计信息的操作过程中我们可以使用采样来评估统计信息。采样对于收集统计信息来说是一项很重要的技术。如果在收集统计信息的时候不使用采样,那么就需要对表进行全表扫描,以及排序整个表。通过采样可以降低收集必要的统计信息所花费的资源。
    控制采样的参数是ESTIMATE_PERCENT,采样的参数可以设置任意值(当然要在范围内),不过ORACLE公司推荐设置ESTIMATE_PERCENT为DBMS_STATS.AUTO_SAMPLE_SIZE。

    AUTO_SAMPLE_SILE可以让ORACLE自己决定最好的采样值,因为不同类型(table,index,column)的统计信息有不同的需求。采样的例子:

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCOTT’,DBMS_STATS.AUTO_SAMPLE_SIZE);

     

    当ESTIMATE_PERCENT参数是手动指定的,如果手动指定的参数过小,不能收集到足够的信息,那么DBMS_STATS可能会自动增长ESTIMATE_PERCENT的值,这样就能确保收集到足够的统计信息。

     

    Ø  我们既可以串行的收集统计信息,也可以并行的收集统计信息。参数DEGREE控制DBMS_STATS是否使用并行特征。ORACLE公司推荐将DEGREE参数设置为DBMS_STATS.AUTO_DEGREE这样设置过后,ORACLE就能够根据OBJECT的SIZE,以及与并行有关的init参数来决定一个恰当的并行度,收集统计信息。注意:cluster index,domain index,bitmap join index不能使用并行特征。

     

    Ø  对于分区表和分区索引,DBMS_STATS既可以单独的收集分区统计信息,也可以收集整个表/索引的统计信息。对于组合分区,DBMS_STATS也能够收集子分区,分区,以及整个表/索引的统计信息。参数GRANULARITY控制分区统计信息的收集。因为分区统计信息,全局统计信息对于大多数系统来说都是非常重要的,所以ORACLE公司推荐将其设置为AUTO来收集分区,以及全局的统计信息。

     

    Ø  当对表收集统计信息的时候,DBMS_STATS会收集列的数据分布信息。数据分布最基本的统计信息就是这个列的最大值与最小值。如果这一列是倾斜的,那么优化器仅仅根据列最大值与最小值是无法制定出准确的执行计划的。对于倾斜的数据分布,我们可以收集列的直方图/柱状图统计信息,这样可以让优化器制定出更加准确的执行计划。

    参数METHOD_OPT控制柱状图的收集。ORACLE公司推荐设置METHOD_OPT为FOR ALL COLUMNS SIZE AUTO。这样设置过后ORACLE会自动的判断哪一列需要收集柱状图,并且自动的设置柱状图的bucket。你同样可以手动的设置哪一列需要收集柱状图,以及柱状图的bucket。

     

    Ø  为了知道统计信息是否过期,ORACLE提供了表监控功能将init参数STATISTICS_LEVEL设置为ALL或者TYPICAL(默认),就开启了表监控的功能(10g已经不需要alter table monitor了)。表监控功能跟踪表的insert,update,delete,truncate,操作,并且记录在DBA_TAB_MODIFICATIONS视图里面。我们在查询DBA_TAB_MODIFICATIONS视图的时候有可能查询不到结果,或者查询的结果不准确,这个时候需要用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO过程将内存中的信息刷新到

    该视图中。OPTIONS参数设置为GATHER STALE或者GATHER AUTO,就会让DBMS_STATS判断表的统计信息是否过期(注意GATHER_TABLE_STATS中没有这个参数,只有GATHER_DATABASE_STATS,GATHER_SCHEMA_STATS过程中有这个参数)。判断表的统计信息是否过期的依据是是否有10%以上的数据被修改过,如果被修改过了,那么ORACLE就认为之前的统计信息过期了,ORACLE会重新收集统计信息。

     

    Ø  在我们创建了函数索引之后,我们要为列收集统计信息,这个时候我们需要设置参数METHOD_OPT为FOR ALL HIDDEN COLUMNS。

     

    7. 收集统计信息的策略


    通常情况下,我们会将ORACLE自动收集统计信息功能给关闭,我们会采用手动的方式给数据库收集统计信息。至于收集统计信息的策略需要根据系统来确定。下面说说几种常见的情况:

    ·         如果你系统中的表的数据是增量(有规律)的增加,也就是说你几乎不做任何的批量处理操作,比如批量删除,批量加载操作。对于这样的表收集统计信息是非常简单的。你可以通过查看DBA_TAB_MODIFICATIONS视图来观察表的变化情况,观察表中数据量的变化是否超过了10%,并且记录下天数。这样你就可以每隔这样的时间间隔对其收集一次统计信息。你可以用CRONTAB,或者JOB调用GATHER_SCHEMA_STATS或者GATHER_TABLE_STATS过程来收集统计信息。

    ·         对于经常批量操作的表,那么表的统计信息就必须在批量操作之后对其收集统计信息。
    ·         对于分区表,通常只有一个分区被修改,这种情况下可以只收集单独分区的统计信息,不过收集整个表的统计信息还是非常有必要的。
    ·         最后我会给出两个脚本,判断该表是否需要收集统计信息。
    8. 收集统计信息的一些例子


    例子1对表收集统计信息

    BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
                                     tabname => 'DEPT',
                                     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                     method_opt => 'for all columns size repeat',
                                     degree => DBMS_STATS.AUTO_DEGREE,
                                     cascade=>TRUE
                                     );
    END;
    /

    上面的例子收集SCOTT.DEPT表的统计信息。这里面值得关注的一个参数就是method_opt。这个参数控制是否收集列的直方图信息。通常情况下,是不会收集直方图的,关于直方图不是三言两语可以说明白的。

    它的四个选项method_opt=>'for all columns size skewonly' 

    ORACLE会根据数据分布收集直方图
    method_opt=>'for all columns size repeat'

    只有以前收集过直方图,才会收集直方图信息,所以一般我们会设置method_opt 为repeat
    method_opt=>'for all columns size auto' 

    ORACLE会根据数据分布以及列的workload来确定是否收集直方图
    method_opt=>'for all columns size interger'

    我们自己指定一个bucket值


    例子2对某一个schma收集统计信息
    BEGIN
       DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',
                                      estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
                                      ptions => 'gather auto',
                                      degree  => DBMS_STATS.AUTO_DEGREE,
                                      method_opt => 'for all columns size repeat',
                                      cascade => TRUE 
                                     );
    END;                                 
    /

    上面的例子收集SCOTT模式下所有对象的统计信息。里面值得注意的一个参数就是options。前面已经讲到过,他与表监控有关。它有四个选项
    Options =>’gather’       收集所有对象的统计信息
    Options =>’gather empty’ 只收集还没被统计的表
    Options =>’gather stale’ 只收集修改量超过10%的表
    Options =>’gather auto’  相当于empty+stale ,所以我们一般设置为AUTO。


    例子3 对一个分区表收集统计信息
    BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
                                     tabname => 'P_TEST',
                                     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                     method_opt => 'for all columns size repeat',
                                     degree => DBMS_STATS.AUTO_DEGREE,

                                     granularity => 'ALL',
                                     cascade=>TRUE
                                     );
    END;
    /

    上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity,他有7个选项。


    granularity => 'ALL'  收集分区,子分区,全局的统计信息
    granularity => 'AUTO' 这个是默认的设置,ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
    granularity => 'DEFAULT' 这个是过期了的
    granularity => 'GLOBAL' 收集全局统计信息
    granularity => 'GLOBAL AND PARTITION' 收集全局,分区统计信息,但是不收集子分区统计信息
    granularity => 'PARTITION' 收集分区统计信息
    granularity => 'SUBPARTITION' 收集子分区统计信息

    当然我们可以指定partname,自己控制对哪个分区收集统计信息


    9. 列出表需要收集统计信息的脚本
    普通表
    set serveroutput on

    declare

       -----select OVER THE Change RATE TABLES---------------

       cursor overchangerate is

     select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows

                    from dba_tab_modifications a, dba_tables b

                   where a.table_name = b.table_name

                     and table_owner not in

                         ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                          'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                          'OUTLN', 'TSMSYS', 'MDSYS')

                     and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1

                     or a.table_name = b.table_name

                     and table_owner not in

                         ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                          'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                          'OUTLN', 'TSMSYS', 'MDSYS')

                     and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1 or

                     a.table_name = b.table_name

                     and table_owner not in

                         ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                          'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                          'OUTLN', 'TSMSYS', 'MDSYS')

                     and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ;

        ----select the unanalyzed table---------------

        cursor nullmonitor is

          select owner, table_name

            from dba_tables

           where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',

                  'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',

                  'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')

             and last_analyzed is null;

      begin

        dbms_output.enable(1000000);

        ----flush the monitorring information into the dba_tab_modifications

        DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

        ----display the unanalyzed table--------------

        dbms_output.put_line('- - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

      

        dbms_output.put_line('Unalalyzed tables:');

        for v_null in nullmonitor loop

          dbms_output.put_line(v_null.owner || '.' || v_null.table_name ||

                               ' has not been analyzed, consider gathering statistics');

        end loop;

        ----display the  information-------------------

        dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

        dbms_output.put_line('Over the Change_Rate 10%:');

        for v_topinsert in overchangerate loop

          dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

                               'till now inserted ' || v_topinsert.inserts || ' rows, updated  ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||   

                               ' rows. consider gathering statistics');

        end loop;

         dbms_output.put_line('- - - - - - - - - - - - - - - - - - -  - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

      end;

    /

     

    下面的是分区表
    set serveroutput on

    declare

       -----select OVER THE Change RATE TABLES---------------

       cursor overchangerate is

    select a.table_owner,a.table_name,a.partition_name,sum(a.inserts) inserts,sum(a.updates) updates,sum(a.deletes) deletes,sum(b.num_rows) num_rows

    from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name

    and a.partition_name=b.partition_name and   a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

    'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS')

    group by a.table_owner,a.table_name,a.partition_name

    having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

    or

    (sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

    or

    (sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

    order by a.table_name;

      begin

        dbms_output.enable(1000000);

        ----flush the monitorring information into the dba_tab_modifications

        DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

        ----display the top_n_insert information-------------------

        dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

        dbms_output.put_line('Over the Change_Rate 10%:');

        for v_topinsert in overchangerate loop

          dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name  || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

                               'till now inserted ' || v_topinsert.inserts || ' rows, updated  ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||   

                               ' rows. consider gathering statistics');

        end loop;

        dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

      end;

    /

     

    在此特别声明一点,在oracle11.2版本中有一个相关的BUG

    Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8

    该BUG会导致严重的性能问题。

    oracle官方申明,只有在12.1版本才解决这个问题,临时解决方案是手动关闭动态采样。

     

    顺便贴上10个level的动态采样介绍

    Level 0: Do not use dynamic sampling. 


    Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32). 

    Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks. 

    Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks. 

    Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks. 

    Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively. 

    Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

     

     

     

     

     

     

    展开全文
  • 本篇主要是从两个角度出发:1、什么统计信息;2、怎么正确收集统计信息

    本篇主要是从两个角度出发:
    1、什么统计信息;
    2、怎么正确收集统计信息(dbms_stats)

    一、统计信息相关概念

    1、什么是统计信息?
    oracle数据库中的统计信息存储在数据字典中,从多个维度描述了oracle数据库里的详细信息。

    2、统计信息作用是什么?
    oracle CBO优化器会利用统计信息计算目标SQL各种可能、不同的执行路径的成本,并从中选择一条最小的执行路径来作为目标SQL的执行计划。(统计信息不准确,SQL的执行计划会走错,SQL会出现性能问题)

    3、统计信息分类:

    • 表的统计信息
      表的统计信息主要包含表的总行数(num_rows),表块数(blocks)以及平均长度(avg_row_len)
    • 索引的统计信息
      索引的统计信息描述了索引的详细信息,所以索引的层级、叶子块的数量、聚簇因子等
    • 列统计信息
      列统计信息记录了列的distinct值的数量、null的数量、列最小值和列最大值。
    • 系统统计信息
      系统统计信息是描述了oracle数据库服务器的系统处理能力,包含cpu和I/O两个方面,可以通过这两个方面来知道数据库服务器的实际处理能力
    • 数据字典统计信息
      描述了字典基表(tab,ind,ind等),数据字典基表上的索引。
    • 内部对象统计信息
      记录了一些内部表(x0x系统表)的详细信息,它的维度和普通表的统计信息类似,但是其表块数为0,x实际上只是oracle自定义的内存结构,不占用实际物理空间。

    二、收集统计信息方法选择

    oracle有两种方法收集统计信息,1、analyze;2、dbms_stats

    2.1 analyze命令收集

    oracle 7开始,通过analyze命令来收集表、索引、列的统计信息。以下是一些典型的用法。

    • 采样比为15%,对test表搜集统计信息
    analyze table test estimate statistics smaple 15 percent for table;
    
    • 计算模式 对test表收集统计信息
    analyze table test compute statistics for table;
    

    该模式下,只有test表有统计信息,test的列和索引都没有统计信息,且收集的统计信息和实际情况是一致的。

    • 计算模式下对test表 列1和列2收集统计信息
    analyze table test compute statistics for cloumns 列1,列2;
    

    只会对列1和列2收集统计信息,且之前的覆盖掉之前的收集统计信息。

    • 计算模式下同时对表和列1和列2收集统计信息
    analyze table test compute statistics for table for cloumns 列1,列2;
    
    • 收集索引统计信息
    analyze index idx_1 statistics;
    
    • 删除统计信息
    analyze table test delete statistics;
    

    删除test表、列、所有索引的统计信息

    analyze index idx_1 delete statistics;
    

    2.2 dbms_stats包收集统计信息

    oracle 8.1.5开始,dbms_stats被广泛应用于统计信息收集,也是oracle官方推荐的方式。
    dbms_stats有4个存储过程。

    • gather_table_stats:用于收集目标表、列和索引的统计信息。
      示例:
      收集test表的统计信息
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                    tabname          => 'TEST',
                                    estimate_percent => 15, --采样比是15%
                                    method_opt       => 'for table',
                                    cascade          => false);  --默认是true,级联收集
    END;
    /
    
    
    • gather_index_stats:用于收集指定统计信息。
      示例:收集索引idx_text的统计信息
    BEGIN
      DBMS_STATS.GATHER_INDEX_STATS(ownname          => 'SCOTT',
                                    indname          => 'IDX_TEST',
                                    estimate_percent => 100,
    								);
    END;
    /
    
    • gather_schema_stats:用于收集指定schema下的所有对象统计信息。
      示例:收集scott用户下的所有对象
    BEGIN
      DBMS_STATS.GATHER_SCHEMA_STATS(ownname		 => 'SCOTT',
                                    cascade			 =>true,
                                    granularity=>'ALL', --收集分区表
    								);
    END;
    /
    
    • gather_database_stats:用于书籍全库所有的统计信息。
      示例:收集全库的统计信息
    BEGIN
      DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>100,
                                       degree=>8,
                                       cascade=>true,
                                       granularity=>'ALL', --收集分区表
    								);
    END;
    /
    

    2.2.1 DBMS_STATS重要参数详解

    ownname:表示表的拥有者,不区分大小写。

    tabname:表示表名字,不区分大小写。

    granularity:表示收集统计信息的粒度,该选项只对分区表生效,默认为 AUTO,表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。
    对于该选项,我们一般采用AUTO 方式,也就是数据库默认方式,因此在后面的脚本中,省略该选项。

    estimate_percent“”表示采样率,范围是0.000 001~100。
    这个参数主要是用于CBO估算表的总行数,采样率越高,CBO估算的表行数越接近于真实值,执行计划越能走正确。
    估算总行数=样本大小(DBA_TAB_STATISTICS.SAMPLE_SIZE)*100/采样率(estimate_percent)
    这个参数可能对于很多新手来说都不知道怎么设置:
    一般对小于 1GB 的表进行100%采样,因为表很小,即使100%采样速度也比较快。有时候小表有可能数据分布不均衡,如果没有100%采样,可能会导致统计信息不准。
    因此建议对小表 100%采样。我们一般对表大小在1GB~5GB 的表采样50%,对大于5GB的表采样30%。如果表特别大,有几十甚至上百 GB,我们建议应该先对表进行分区,然后分别对每个分区收集统计信息。一般情况下,为了确保统计信息比较准确,我们建议采样率不要低于30%。
    <1GB 建议采样比100%
    1GB~5GB 建议采样比50%
    >5GB 建议采样比30%

    method_opt:用于控制收集直方图策略。
    直方图简单来说就是数据库了解表中某列的数据分布,从而更正确的走更优的执行计划
    method_opt => ‘for all columns size 1’ 表示所有列都不收集直方图
    method_opt => ‘for all columns size skewonly’ 表示对表中所有列收集自动判断是否收集直方图。选择率非常高的列和null的列不会收集(谨慎使用)
    method_opt => ‘for all columns size auto’ 表示对出现在 where 条件中的列自动判断是否收集直方图。
    method_opt => ‘for all columns size repeat’ 表示当前有哪些列收集了直方图,现在就对哪些列收集直方图。
    在实际工作中,当系统趋于稳定之后,使用 REPEAT 方式收集直方图。

    no_invalidate :表示共享池中涉及到该表的游标是否立即失效,默认值为 DBMS_STATS.AUTO_INVALIDATE,表示让 Oracle 自己决定是否立即失效。
    建议将 no_invalidate 参数设置为 FALSE,立即失效。因为发现有时候 SQL 执行缓慢是因为统计信息过期导致,重新收集了统计信息之后执行计划还是没有更改,原因就在于没有将这个参数设置为 false。

    degree: 表示收集统计信息的并行度,默认为 NULL。如果表没有设置 degree。如果表没有设置 degree,收集统计信息的时候后就不开并行;如果表设置了 degree,收集统计信息的时候就按照表的 degree 来开并行。可以查询 DBA_TABLES.degree 来查看表的 degree,一般情况下,表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。

    cascade :表示在收集表的统计信息的时候,是否级联收集索引的统计信息,默认值为DBMS_STATS.AUTO_CASCADE,表示让 Oracle 自己判断是否级联收集索引的统计信息。

    2.3 analyze和dbms_stats的区别

    1. analyze命令不能正确的手机分区表的统计信息,而dbms_stats包却可以。
    2. analyze命令不能并行收集统计信息,而dbms_stats包可以。
    3. analyze命令不能收集x$的统计信息

    所以选择推荐使用dbms_stats来对表进行统计信息收集。

    2.4 推荐建议收集统计信息脚本

    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                    tabname          => 'TEST',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size repeat',
                                    no_invalidate    => FALSE,
                                    degree           => 8,
                                    granularity      => 'ALL',
                                    cascade          => TRUE);
    END;
    /
    

    唯一需要注意选择的地方是:
    estimate_percent :
    <1GB 建议采样比100%
    1GB~5GB 建议采样比50%
    >5GB 建议采样比30%
    (朋友们可以自己尝试用存储过程去判断表大小,自定义收集统计信息脚本)
    degree :
    根据服务器的资源,和业务负载来指定
    method_opt :
    系统刚上线使用auto,业务系统稳定后使用repeat。

    展开全文
  • oracle收集统计信息

    千次阅读 2017-11-21 17:47:30
    什么是统计信息 统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。...CBO正是根据这些统计信息数据,计算出不同访问路径下,不同join 方式下,各种计划的成本,最
  • 收集统计信息

    万次阅读 2010-02-26 15:11:00
    1. 理解什么是统计信息优化器统计信息就是一个更加详细描述数据库和数据库对象的集合,这些统计信息被用于查询优化器,让其为每条SQL语句选择最佳的执行计划。优化器统计信息包括:· 表的统计信息o 行数o Block...
  • Oracle收集统计信息详解

    千次阅读 2014-09-16 15:27:44
    收集系统 优化器统计范围: 表统计; --行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN; 列统计; --列中唯一值的数量(NDV),NULL值的数量,数据分布;  --DBA_TAB_COLUMNS:NUM_...
  • greenplum 收集统计信息

    千次阅读 2013-04-03 14:33:10
    定期使用Vacuum analyze tablename 回收垃圾和收集统计信息,尤其在大数据量删除,导入以后,非常重要 vacuum分两种,一种是analize优化查询计划的,还有一种是清理垃圾数据, vacuum,该选项主要是清理数据库表...
  • ORACLE收集统计信息

    千次阅读 2015-09-07 17:26:02
    1. 理解什么是统计信息优化器统计信息就是一个更加详细描述数据库和数据库对象的集合,这些统计信息被用于查询优化器,让其为每条SQL语句选择最佳的执行计划。优化器统计信息包括:· 表的统计信息o 行数o Block数o ...
  • Oracle 收集统计信息

    千次阅读 2013-04-26 16:43:53
    什么是统计信息 统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO...
  • 查看当前库中统计信息过期的表: set linesize 150 set pagesize 1000 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT OWNER, TABLE_NAME, PARTITION_NAME, OBJECT_TYPE, STALE_STATS, LAST_...
  • 对于大表的统计信息收集,我们可以加degree参数,使得扫描大表的时候,进行并行扫描,加快扫描速度。 但是这在收集的时候,还是进行一个表一...可以通过以下语句看到你的数据库是否启用了CONCURRENT收集统计信息
  • Oracle 手动收集统计信息

    千次阅读 2015-04-17 16:29:00
    收集oracle统计信息 优化器统计范围: 表统计: --行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN; 列统计: --列中唯一值的数量(NDV),NULL值的数量,数据分布; --DBA_TAB_COLUMNS...
  • Oracle 各种收集统计信息的方法

    万次阅读 2019-04-04 17:07:35
    查看某个表的统计信息 alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in ('T1','T2'); 查看某个表上...
  • No.2.1_10 Oralce 收集统计信息之2_Analyze

    千次阅读 2020-12-25 15:45:36
    Oralce 收集统计信息之2_Analyze,来自Oralce官方操作手册,官方建议使用DBMS_STATS 代替 ANALYZE
  • Oracle收集统计信息方式1 SYS.DBMS_STATS
  • 收集统计信息(二) 之 dbms_stats

    千次阅读 2011-12-15 20:32:08
    这次讲解一下收集统计信息常用的存储过程 收集统计信息类: 一、dbms_stats.gather_table_stats存储过程 This procedure gathers table and column (and index) statistics. It attempts to parallelize as ...
  • 收集oracle统计信息

    千次阅读 2018-07-27 12:57:58
    优化器统计范围: 表统计; --行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN;列统计; --列中唯一值的数量(NDV),NULL值的数量,数据分
  • 生产环境上的数据库经常要手工更新统计信息,表非常多,要除去备份表(备份表通常会加上bak或是日期)。  EAM : 数据库用户名  degree: 并行度,加快收集时间,我的数据库是12核,所以设置24  cascade :...
  • 1.分析更新表的统计信息,,有可能导致执行计划改变.. 2.以的analyze table abc compute statistics;这条为例,生成的统计信息会存在于user_tables这个视图, select * from user_tables where table_name='ABC'; ...
  • 使用DBMS_STATS来收集统计信息

    千次阅读 2013-12-17 16:55:22
    .(某些DDL语句可以自动收集统计信息) DBMS_STATS  does not generate information about chained rows and the structural integrity of segments. (不会收集chainrow和段结构有效性的统计信息) The DBA can ...
  • 并行收集器(Parallel Collector)(这里也称为吞吐量收集器(throughput collector))是与串行收集器(serial collector)类似的分代收集器(generational collector)。串行收集器(serial collector)和并行收集器...
  • DBMS_STATS包-收集统计信息

    千次阅读 2011-12-17 20:08:02
    1、gather_table_stats 存储过程用来收集表上的相关信息。 procedure gather_table_stats(ownname varchar2, --所有者名字 tabnam
  • Oracle快速收集全库统计信息

    千次阅读 2018-12-01 00:26:00
    环境:Oracle 11.2.0.4 采用并行的方式,快速收集全库统计信息,多用于跨...--设置并行收集 exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');   --开始收集全库统计信息 begin dbms_stats.gather_database...
  • 收集全库统计信息的解释

    千次阅读 2016-05-06 16:02:47
    收集全库的统计信息参数理解  经常使用一条收集数据库统计信息的语句,对它的参数不是很了解。网上搜索一下 exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'...
  • 数据库统计信息收集

    2017-08-21 15:58:30
    由于Oracle的优化器是CBO,所以对象的统计数据对执行计划的生成至关重要! ...作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息(默认参数下是对表进行直方图信息收集,包含该表的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,323
精华内容 16,929
热门标签
关键字:

并行收集统计信息