分类： Oracle
2011-02-24 11:56:15
一、GATHER_STATS_JOB是10g开始引入的自动统计数据收集功能的重要组成部分，但是这个定时任务带来的问题也是较多的，应当根据应用的具体情况进行定制，通过DBA_SCHEDULER_JOBS可以查询JOB的执行情况：
|
SELECT owner,job_name,state, TO_CHAR (last_start_date, 'DD-MON-YYYY HH24:MI') last_start_date, TO_CHAR (last_run_duration, 'DD-MON-YYYY HH24:MI') last_run_duration,failure_count FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB'; |
执行时间是每晚22:00，持续时间为8小时，如果没有执行完成，也就是说在次日6时，强制终止
二、通过dba_scheduler_job_run_details表可以获得JOB的执行情况细节
|
SELECT log_id, job_name, status, TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date,ADDITIONAL_INFO FROM dba_scheduler_job_run_details WHERE job_name = 'GATHER_STATS_JOB'; |
收集统计信息的JOB没有完成，自动终止
三、JOB任务运行的具体过程如下：
|
select PROGRAM_ACTION from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG'; |
四、查看调度名，类型
|
select schedule_name,schedule_type from dba_scheduler_jobs where job_name='GATHER_STATS_JOB'; |
五、自动任务是使用WINDOW_GROUP来决定其运行时间的，WINDOW_GROUP名称是MAINTENANCE_WINDOW_GROUP，查询dba_scheduler_window_groups、dba_scheduler_wingroup_members和dba_scheduler_windows视图
|
select window_name from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP';
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='MAINTENANCE_WINDOW_GROUP'; |
发现这个WINDOW_GROUP有两个WINDOW，分别是WEEKNIGHT_WINDOW和WEEKEND_WINDOW，定义的时间分别是周一至周五的晚上10点到早上6点和周六全天
六、更改JOB执行时间为每晚8:00至次日早上6:00
| begin dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','REPEAT_INTERVAL','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=20;byminute=0; bysecond=0'); dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','DURATION','+000 10:00:00'); end; / |
七、查看top 信息，8642为job进程号
| System: crmdb1 Thu Dec 9 23:09:20 2010 Load averages: 0.30, 0.29, 0.31 1168 processes: 1125 sleeping, 42 running, 1 zombie Cpu states: CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 0 0.24 31.0% 0.0% 3.1% 65.8% 0.0% 0.0% 0.0% 0.0% 1 0.28 29.9% 0.0% 4.9% 65.2% 0.0% 0.0% 0.0% 0.0% 2 0.29 30.1% 0.0% 5.3% 64.6% 0.0% 0.0% 0.0% 0.0% 3 0.27 25.9% 0.0% 6.5% 67.6% 0.0% 0.0% 0.0% 0.0% 8 0.35 39.3% 0.0% 1.2% 59.5% 0.0% 0.0% 0.0% 0.0% 9 0.33 25.3% 0.0% 3.5% 71.1% 0.0% 0.0% 0.0% 0.0% 10 0.35 27.1% 0.0% 2.4% 70.5% 0.0% 0.0% 0.0% 0.0% 11 0.27 34.2% 0.0% 2.8% 63.1% 0.0% 0.0% 0.0% 0.0% --- ---- ----- ----- ----- ----- ----- ----- ----- ----- avg 0.30 30.3% 0.0% 3.7% 66.0% 0.0% 0.0% 0.0% 0.0% System Page Size: 4Kbytes Memory: 14640696K (2385624K) real, 17887192K (2846988K) virtual, 4113580K free Page# 1/69 CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 9 ? 6326 oracle 236 20 15771M 6088K run 36:14 56.07 55.97 oraclengcrm 0 ? 11473 oracle 233 20 15773M 7280K run 659:42 46.36 46.28 oraclengcrm 2 ? 8642 oracle 148 20 16016M 235M sleep 51:38 19.76 19.73 ora_j001_ngcrm 10 ? 19724 oracle 154 20 15773M 8056K sleep 13663:27 16.93 16.90 oraclengcrm 11 ? 22974 oracle 148 20 15774M 8368K sleep 0:06 9.87 9.86 oraclengcrm 9 ? 19736 oracle 154 20 15774M 8180K sleep 18:41 8.83 8.81 oraclengcrm 10 ? 6348 oracle 154 20 15771M 6088K sleep 4:25 6.84 6.82 oraclengcrm 10 ? 1387 oracle 154 20 15772M 6624K sleep 97:05 4.97 4.96 oraclengcrm 0 ? 11593 oracle 154 20 15773M 7988K sleep 2115:39 3.25 3.24 oraclengcrm 2 ? 1153 oracle 154 20 15772M 6248K sleep 48:20 2.52 2.51 oraclengcrm 9 ? 1156 oracle 154 20 15772M 6176K sleep 48:20 2.41 2.40 oraclengcrm 3 ? 22463 oracle 154 20 15773M 7720K sleep 0:39 2.19 2.19 oraclengcrm 3 ? 129 root 152 20 16632K 14784K run 1465:09 2.11 2.11 vxfsd 11 ? 28604 oracle 154 20 15773M 6936K sleep 0:42 1.80 1.79 oraclengcrm 2 pts/ta 22093 oracle 168 20 7984K 2048K sleep 7:54 1.78 1.77 top 8 pts/te 11798 oracle 179 20 7536K 1536K run 0:33 1.41 1.41 top |
八、根据PID查看当前执行的SQL
|
SELECT SQL_TEXT FROM V$SQLTEXT WHERE (ADDRESS,HASH_VALUE) =
(select SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid =
(
select sid from v$session where v$session.paddr = (
select addr from v$process where spid =&spid)
)
) order by piece;
Enter value for spid: 8642
SQL_TEXT ---------------------------------------------------------------- select count(rep) from (select /*+ leading(v1 v2) use_nl_with_in dex(v2) */ count(v1.val) rep from (select rn, val from (select r ownum rn, val from (select /*+ no_merge no_parallel(t) no_parall el_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl d ynamic_sampling(0) no_monitoring */substrb("SERIAL_NUMBER",1,32) val from sys.ora_temp_1_ds_76657 t where substrb("SERIAL_NUMBER ",1,32) is not null group by substrb("SERIAL_NUMBER",1,32) havin g count(substrb("SERIAL_NUMBER",1,32)) = 1)) where ora_hash(rn) <= 31854568) v1, (select /*+ index(t2) */ "SERIAL_NUMBER" val fr om "UCR_CRM1"."TI_CH_OLCOMWORK" t2) v2 where v2.val like v1.val| |'%' group by v1.val having count(v1.val) <= 2)
11 rows selected. |
此SQL正在分析"UCR_CRM1"."TI_CH_OLCOMWORK"表
九、查看AWR报告
|
经过核实，此表"UCR_CRM1"."TI_CH_OLCOMWORK"数据量非常的大，证实是系统JOB执行的瓶颈所在。
十、手工分析此表
| exec dbms_stats.gather_table_stats('UCR_CRM1','TI_CH_OLCOMWORK',cascade=>true,estimate_percent=>3 ,degree=>4); |
十一、次日查看job运行结果
成功执行JOB
十二、问题总结：
Oracle 10g自动分析数据库对象，通常情况下，在规定的时间内是可以完成的。如在给定范围时间内未操作完成，为不影系统应用，系统会强行终止JOB的运行。根据业务繁忙程度，可适当的调整JOB运行时长。
对于大数据量的库而言，如不能每天收集所有对象的统计信息。关闭系统自动执行job，可以采用自定义JOB的方式分批次收集统计信息。
