ITPub博客

首页 > 数据库 > Oracle > Oracle统计信息自动收集

Oracle统计信息自动收集

原创 Oracle 作者:llnnmc 时间:2017-12-13 16:05:23 0 删除 编辑

Oracle提供了统计数据自动收集功能。在部署安装软件过程中,其中有一个步骤便是提示是否启用这个功能(默认是启用这个功能)。这个功能貌似带来了统计数据采集上的便捷,但是其中却隐藏着性能隐患。在7*24小时的系统中这种自动运行的任务很有可能对系统性能带来冲击。因此,我们酌情需要修改或是禁掉这项功能。10g11g的处理方法有所不同,以下分别阐述。


一、针对Oracle 10g的处理方法


1、自动统计作业的查询

Oracle 10g版本中,自动统计的job名为GATHER_STATS_JOB,这个自动任务默认情况下在工作日晚上10:00-6:00和周末全天开启,它调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级,再开始进行统计。


下面是在10g中查看自动收集统计作业的方法:

col program_name for a30

col schedule_name for a30

col last_run_duration for a30

select owner, job_name, program_name, schedule_name, job_class, enabled, auto_drop, run_count, to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss') last_start_time, last_run_duration, state from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';


OWNER                          JOB_NAME                       PROGRAM_NAME                   SCHEDULE_NAME                  JOB_CLASS                      ENABL AUTO_  RUN_COUNT LAST_START_TIME     LAST_RUN_DURATION              STATE

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----- ----- ---------- ------------------- ------------------------------ ---------------

SYS                            GATHER_STATS_JOB               GATHER_STATS_PROG              MAINTENANCE_WINDOW_GROUP       AUTO_TASKS_JOB_CLASS           TRUE  FALSE         29 2017-01-28 06:00:05 +000000000 00:00:38.535000     SCHEDULED


以下查看自动收集统计作业的历次运行明细,该表会保留最近一个月的记录:

col owner for a10

col job_name for a20

col status for a10

col run_duration for a20

col session_id for a10

col cpu_used for a20

select owner, job_name, status, to_char(actual_start_date, 'yyyy-mm-dd hh24:mi:ss') actual_start_time, run_duration, session_id, cpu_used

  from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by actual_start_date desc;


计算机生成了可选文字:
OWNER 
」 08 NAME 
」 08 
」 08 
」 08 
」 08 
」 08 
」 08 
」 08 
」 08 
」 08 
」 08 
STATUS 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
SUCCEEDED 
ACTUAL START TIME 
RUN DURATION 
+000 0000.38 
+ 000 00 : 01 以 」 
+000 00 04 巧 4 
+000 0 , 01 58 
+000 00 吒 5 4 
+000 0 , 04 : 42 
+000 00 1 : 25 
+000 0 , 0 , 12 
+000 00 2 4 
+000 0 , 02 巧 4 
SESSION ID 
1115704 
11114107 
89187j5 
11 之 51 g 
112 以 丑 
1 ] 1142 
1 ] 11210 
1 ] 1225 
1 ] 15278g 
1 ] 15 17 
CPU USED 
+000 00 、 00 、 1228 
+000 0 , 00 彐 55 
+000 00 1 7 5 
+000 0 , 00 彐 7 . 58 
+000 00 吒 1 彐 2f0 
+ 000 00 : 00 巧 & 04 
+000 00 吒 , 27 . 29 
+000 0 , 01 : 1512 
+000 00 00 5 47 
+000 0 , 01 8 国 g 
GATHER 
GATHER 
GATHER 
GATHER 
GATHER 
GATHER 
GATHER 
GATHER 
GATHER 
GATHER 
STATS 
STATS 
STATS 
STATS 
STATS 
STATS 
STATS 
STATS 
STATS 
STATS 
2015 
2015 
2015 
2015 
2015 
2015 
2015 
2015 
2015 
2015 
05 . 
05 
05 
05 
05 
05 
05 
05 
05 . 
05 
28 05 : 00 吒 ] 
27 22 : 00 : 02 
25 22 , 01 
25 22 : 00 : 02 
24 22 ℃ , 02 
2 」 22 : 00 : 02 
21 05 ℃ , 04 
20 22 , 01 
19 22 : 00 2 
18 22 0 : 02


即便作业被删除,我们仍可以通过log记录查看作业的历史运行情况:

col owner for a10

col job_name for a30

col operation for a20

col status for a10

select to_char(log_date, 'yyyy-mm-dd hh24:mi:ss') log_time, owner, job_name, job_class, operation, status from dba_scheduler_job_log

 where job_name = 'GATHER_STATS_JOB' order by log_date desc;


如果这个作业正在运行中,可以通过以下查询看到:

col elapsed_time for a30

select job_name, session_id, elapsed_time, cpu_used from dba_scheduler_running_jobs;


计算机生成了可选文字:
JOBNAME
GATHERSTATSJOB
SESSIONJD月ELApSED-nME
30+00000:14:19.19
CpUUSED
+00000:02:40.93


2、自动统计作业的关闭和启用

方法一: 

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

方法二:

alter system set "_optimizer_autostats_job"=false scope=spfile;

alter system set "_optimizer_autostats_job"=true scope=spfile;


3、修改自动统计作业的执行时间

1)查看自动统计作业的时间窗口信息:

col window_name for a20

col repeat_interval for a80

col duration for a20

select t1.window_name, t1.repeat_interval, t1.duration

  from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

 where t1.window_name = t2.window_name

   and t2.window_group_name in

       (select window_group_name

          from dba_scheduler_wingroup_members

         where window_group_name in

               (select schedule_name

                  from dba_scheduler_jobs

                 where job_name = 'GATHER_STATS_JOB'));


计算机生成了可选文字:
WINDOWNAME
WEEKNIGHTWINDOW
WEEKENDWINDOW
REpEATINTERVAL
freq二dail犷byday二MON,TU民WED,THU,F吸byhour二22二byminute二0;bys。
freq二daily;byday二SAT:byhour二0;byminute二0;bysecond二0
DURA,nON
二+00008:00:00二
…+00200:00:0O·


2)根据工厂实际开班时间修改这两个窗口,例如可以利用中午吃饭时间进行自动统计,以便避开对生产的干扰。在SYS下执行:

begin

    dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',

                                 'REPEAT_INTERVAL',

                                 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=11;byminute=0; bysecond=0');

    dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',

                                 'DURATION',

                                 '+000 00:30:00');

    dbms_scheduler.set_attribute('WEEKEND_WINDOW',

                                 'REPEAT_INTERVAL',

                                 'freq=daily;byday=SAT,SUN;byhour=11;byminute=0;bysecond=0');

    dbms_scheduler.set_attribute('WEEKEND_WINDOW',

                                 'DURATION',

                                 '+000 00:30:00');

end;

/


3)修改成功后再次查看窗口时间表信息已达到目的:

计算机生成了可选文字:
WINDOWNAME
WEEKNIGHTWINDOW
WEEKENDWINDOW
REpEATINTERVAL
freq二dail犷byday二MON,TU民WED,THU,F欣byhour二11;bymlnute二0:bysecond二0
freq二dail犷byday二弘工SUN:byhour二11;byminute二0;bysecond二0
DURA刀ON
+00000:30:00
+00000:30:00


二、针对Oracle 11g的处理方法


1、查看自动统计任务及其状态

select client_name, status from dba_autotask_client;


CLIENT_NAME                                        STATUS

-------------------------------------------------- --------

auto optimizer stats collection                    ENABLED

auto space advisor                                 ENABLED

sql tuning advisor                                 ENABLED


其中“auto optimizer stats collection”便是我们要寻找的自动收集统计信息的任务名称,它的状态目前是启用状态。


2、禁止自动统计任务

我们可以使用DBMS_AUTO_TASK_ADMIN包完成这个任务。

exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);


select client_name, status from dba_autotask_client;


CLIENT_NAME                                        STATUS

-------------------------------------------------- --------

auto optimizer stats collection                    DISABLED

auto space advisor                                 ENABLED

sql tuning advisor                                 ENABLED


此时“auto optimizer stats collection”任务已经被禁用。


3、启用自动统计任务

exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);


select client_name, status from dba_autotask_client;


CLIENT_NAME                                        STATUS

-------------------------------------------------- --------

auto optimizer stats collection                    ENABLED

auto space advisor                                 ENABLED

sql tuning advisor                                 ENABLED


4修改自动统计任务的执行时间

1)查询自动统计任务的执行窗口时间表:

select t1.window_name, t1.repeat_interval, t1.duration

  from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

 where t1.window_name = t2.window_name

   and t2.window_group_name in

       (select window_group

          from dba_autotask_client t3

         where t3.client_name = 'auto optimizer stats collection');


计算机生成了可选文字:
WINDOWNAME
SUNDAYWINDOW
SATURDAYWINDOW
F田DAYWINDOW
THURSDAYWINDOW
WEDNESDAYWINDOW
TUESDAYWINDOW
MONDAYW】NDOW
REpEATINTERVAL
freq二dail犷byday二SUN:byhour二6;byminute二0;bysecond二0
freq二dail男byday二SA毛byhour二6;byminute二0;bysecond二0
freq二dail丫byday二F甩byhour二22;byminute二0;bysecond二0
freq二dail男byday二THU:byhour二22;byminute二0;bysecond二0
freq二dail犷byday二WED;byhour二22二byminute二0;bysecond二0
freq二dail犷byday二TU乓byhour二22;byminute二0;bysecond二0
freq二dail犷byday二MON;byhour二22:byminute二0二bysecond二0
DURA刀ON
…+00020:00:00…
…+00020:00:00二
…+00004:00:00·
·十00004:00:00·
二十00004:00:00·
…+00004:00:00·
…+00004:00:00…


2)根据需要修改这些窗口的时间表,方法同10g中调用dbms_scheduler.set_attribute过程。


3)实际上,还有另外两个自动任务:

select client_name, status from dba_autotask_client;


CLIENT_NAME                                        STATUS

-------------------------------------------------- --------

auto optimizer stats collection                    ENABLED

auto space advisor                                 ENABLED

sql tuning advisor                                 ENABLED


利用上面的查询可以验证,它们与自动统计任务实际上是默认被安排在同一时间表上的,也可以根据需要修改。


三、其它建议

对于数据量很大的库,一般是建议关闭掉自动收集的任务,通过自己手工写存储过程,再把存储过程加入JOB中去收集统计信息,统计信息的收集是根据表的大小来自己定义收集的采样率以及其他的选项。

可以参考如下的存储过程:

DECLARE

  CURSOR STALE_TABLE IS

    SELECT OWNER,

           SEGMENT_NAME,

           CASE

             WHEN SIZE_GB < 0.5 THEN

             WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN

             WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN

             WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN

             WHEN SIZE_GB >= 10 THEN

           END AS PERCENT,

AS DEGREE

      FROM (SELECT OWNER,

                   SEGMENT_NAME,

                   SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB

              FROM DBA_SEGMENTS

             WHERE OWNER = 'SCOTT'

               AND SEGMENT_NAME IN

                   (SELECT /*+ UNNEST */ DISTINCT TABLE_NAME

                      FROM DBA_TAB_STATISTICS

                     WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')

                       AND OWNER = 'SCOTT')

             GROUP BY OWNER, SEGMENT_NAME);

BEGIN

  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

  FOR STALE IN STALE_TABLE LOOP

    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => STALE.OWNER,

                                  TABNAME          => STALE.SEGMENT_NAME,

                                  ESTIMATE_PERCENT => STALE.PERCENT,

                                  METHOD_OPT       => 'for all columns size skewonly',

                                  DEGREE           => 8,

                                  GRANULARITY      => 'ALL',

                                  CASCADE          => TRUE);

  END LOOP;

END;

上述的存储过程,先定义了表的大小所对应的采样率,然后通过查询DBA_SEGMENTS得到表的大小,再通过DBMS_STATS.GATHER_TABLE_STATS去收集统计信息。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28974745/viewspace-2148664/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-12-29

  • 博文量
    91
  • 访问量
    63329