oracle中的自动统计信息收集

原创

aoerqilengbai 2021-09-08 09:17:33 博主文章分类：sql优化 ©著作权

文章标签 sql 运行时间直方图 sql优化存储过程 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者aoerqilengbai的原创作品，请联系作者获取转载授权，否则将追究法律责任

摘自基于orcle的sql优化

10g:

查看收集的job

SQL> select job_name,program_name,schedule_name,last_start_date from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';

JOB_NAME PROGRAM_NAME SCHEDULE_NAME LAST_START_DATE
-------------------- ------------------------------ ---------------------------------------- -------------------------------------------
-------------
GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP 23-5月 -15 08.01.16.987000 上午 +08:00

查看program对应的存储过程

SQL> select program_name,program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG';

PROGRAM_NAME PROGRAM_ACTION
-------------------- --------------------------------------------------
GATHER_STATS_PROG dbms_stats.gather_database_stats_job_proc

看到是调用了dbms_stats的包

3查看job的运行窗口

SQL> select * from dba_scheduler_wingroup_members;

WINDOW_GROUP_NAME WINDOW_NAME
------------------------------ ----------------------------
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

4查看各个窗口的具体运行时间

SQL> select * from dba_scheduler_windows;

SQL> select window_name,repeat_interval,duration,next_start_date,last_start_date from dba_scheduler_windows;

WINDOW_NAME REPEAT_INTERVAL DURATION NEXT_START_DATE LAST_START_DATE
------------------------------ -------------------------------------------------------------------------------- --------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00 25-5月 -15 10.00.00.600000 下午 +08:00 22-5月 -15 10.00.00.386000 下午 +08:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 23-5月 -15 12.00.00.000000 上午 +08:00 23-5月 -15 08.01.15.911000 上午 +08:00

5查看资源使用限制

SQL> select window_name,resource_plan from dba_scheduler_windows;

WINDOW_NAME RESOURCE_PLAN
------------------------------ ------------------------------
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

没有资源限制。

6禁用自动统计信息收集

exec dbms_scheduler.disable('GATHER_STATS_JOB');

7禁用工作日的窗口，该窗口被其他的job使用，所以要是禁用后会影响很多别的任务

begin

dbms_scheduler.disable(

name='"sys."."WEEKNIGHT_WINDOW"',

force=>TRUE);

end;

10g中默认是自动收集直方图的

select dbms_stats.get_param('METHOD_OPT') from dual;

FOR ALL COLUMNS SIZE AUTO