联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.发现多个ora_j0**进程
可以发现进程重启非常频繁,大概1分钟重启一次,启动ora_j0**的个数为20个
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep oracle 213120 1 0 11:01:55 - 0:00 ora_j019_prmdb2 oracle 282808 1 0 11:01:55 - 0:00 ora_j012_prmdb2 oracle 299178 1 0 11:01:54 - 0:00 ora_j004_prmdb2 oracle 434212 1 0 11:01:55 - 0:00 ora_j014_prmdb2 oracle 475238 1 0 11:01:54 - 0:00 ora_j011_prmdb2 oracle 487562 1 0 11:01:54 - 0:00 ora_j008_prmdb2 oracle 614612 1 0 11:01:55 - 0:00 ora_j017_prmdb2 oracle 717008 1 0 11:01:54 - 0:00 ora_j009_prmdb2 oracle 721012 1 0 11:01:54 - 0:00 ora_j006_prmdb2 oracle 749618 1 0 11:01:54 - 0:00 ora_j007_prmdb2 oracle 770268 1 0 11:01:54 - 0:00 ora_j005_prmdb2 oracle 811114 1 0 11:01:55 - 0:00 ora_j015_prmdb2 oracle 831550 1 0 11:01:55 - 0:00 ora_j016_prmdb2 oracle 847940 1 0 11:01:55 - 0:00 ora_j013_prmdb2 oracle 938076 1 0 11:01:54 - 0:00 ora_j010_prmdb2 oracle 942224 1 0 11:01:54 - 0:00 ora_j002_prmdb2 oracle 974968 1 0 11:01:55 - 0:00 ora_j018_prmdb2 oracle 434602 1 0 11:01:54 - 0:00 ora_j000_prmdb2 oracle 668104 1 0 11:01:54 - 0:00 ora_j003_prmdb2 oracle 983424 1 0 11:01:54 - 0:00 ora_j001_prmdb2 [prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep oracle 454676 1 1 11:02:56 - 0:00 ora_j012_prmdb2 oracle 696366 1 1 11:02:56 - 0:00 ora_j011_prmdb2 oracle 749628 1 1 11:02:56 - 0:00 ora_j019_prmdb2 oracle 770276 1 1 11:02:56 - 0:00 ora_j017_prmdb2 oracle 794824 1 1 11:02:56 - 0:00 ora_j010_prmdb2 oracle 827464 1 1 11:02:55 - 0:00 ora_j009_prmdb2 oracle 831560 1 1 11:02:56 - 0:00 ora_j016_prmdb2 oracle 847946 1 1 11:02:56 - 0:00 ora_j014_prmdb2 oracle 888972 1 1 11:02:55 - 0:00 ora_j007_prmdb2 oracle 934064 1 1 11:02:55 - 0:00 ora_j008_prmdb2 oracle 938080 1 1 11:02:56 - 0:00 ora_j013_prmdb2 oracle 942232 1 0 11:02:55 - 0:00 ora_j001_prmdb2 oracle 962760 1 1 11:02:55 - 0:00 ora_j006_prmdb2 oracle 434608 1 1 11:02:55 - 0:00 ora_j004_prmdb2 oracle 528810 1 2 11:02:56 - 0:00 ora_j015_prmdb2 oracle 549228 1 1 11:02:55 - 0:00 ora_j005_prmdb2 oracle 668112 1 1 11:02:55 - 0:00 ora_j003_prmdb2 oracle 709090 1 1 11:02:55 - 0:00 ora_j002_prmdb2 oracle 905720 1 2 11:02:56 - 0:00 ora_j018_prmdb2 oracle 971040 1 1 11:02:55 - 0:00 ora_j000_prmdb2 [prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep |wc -l 20
2.其他参数
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production SQL> show parameter job; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 20 SQL> select count(*) from DBA_JOBS_RUNNING; COUNT(*) ---------- 0 SQL> SELECT count(*) FROM v$session_wait where event='jobq slave wait'; COUNT(*) ---------- 20 SQL> select count(*) from dba_jobs; COUNT(*) ---------- 2 SQL> col name for a32 SQL> col value for a24 SQL> col description for a70 SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.inst_id = USERENV ('Instance') 4 and b.inst_id = USERENV ('Instance') 5 and a.indx = b.indx 6 and upper(a.ksppinm) LIKE upper('%¶m%') 7 order by name 8 / Enter value for param: _JOB_QUEUE_INTERVAL old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_JOB_QUEUE_INTERVAL%') NAME VALUE -------------------------------- ------------------------ DESCRIPTION ---------------------------------------------------------------------- _job_queue_interval 5 Wakeup interval in seconds for job queue co-ordinator
3.对cjq进程做10046
[prmsvr2:/oracle]ps -ef|grep cjq oracle 327870 1 0 Feb 10 - 770:39 ora_cjq0_prmdb2 oracle 929872 794774 0 13:24:59 pts/2 0:00 grep cjq SQL> oradebug setospid 327870 Oracle pid: 19, Unix process pid: 327870, image: oracle@prmsvr2 (CJQ0) SQL> oradebug unlimit Statement processed. SQL> oradebug event 10046 trace name context forever, level 12 Statement processed. SQL> oradebug event 10046 trace name context off; Statement processed. SQL> oradebug tracefile_name /oracle/app/admin/prmdb/bdump/prmdb2_cjq0_327870.trc
4.查看cjq的10046文件
发现大量的process startup等待,而且两次批量运行之间的时间间隔在1分钟左右。
WAIT #0: nam='process startup' ela= 59247 p1=74 p2=0 p3=0 WAIT #0: nam='process startup' ela= 51486 p1=74 p2=1 p3=0 WAIT #0: nam='process startup' ela= 51629 p1=74 p2=2 p3=0 WAIT #0: nam='process startup' ela= 48205 p1=74 p2=3 p3=0 WAIT #0: nam='process startup' ela= 47625 p1=74 p2=4 p3=0 WAIT #0: nam='process startup' ela= 55945 p1=74 p2=5 p3=0 WAIT #0: nam='process startup' ela= 47633 p1=74 p2=6 p3=0 WAIT #0: nam='process startup' ela= 51809 p1=74 p2=7 p3=0 WAIT #0: nam='process startup' ela= 57371 p1=74 p2=8 p3=0 WAIT #0: nam='process startup' ela= 50249 p1=74 p2=9 p3=0 WAIT #0: nam='process startup' ela= 51683 p1=74 p2=10 p3=0 WAIT #0: nam='process startup' ela= 47933 p1=74 p2=11 p3=0 WAIT #0: nam='process startup' ela= 47699 p1=74 p2=12 p3=0 WAIT #0: nam='process startup' ela= 47800 p1=74 p2=13 p3=0 WAIT #0: nam='process startup' ela= 47947 p1=74 p2=14 p3=0 WAIT #0: nam='process startup' ela= 52071 p1=74 p2=15 p3=0 WAIT #0: nam='process startup' ela= 47776 p1=74 p2=16 p3=0 WAIT #0: nam='process startup' ela= 47764 p1=74 p2=17 p3=0 WAIT #0: nam='process startup' ela= 47684 p1=74 p2=18 p3=0 WAIT #0: nam='process startup' ela= 47790 p1=74 p2=19 p3=0
通过O记的大力帮助,终于找出了该问题的原因:Bug 4339922: CJQ PROCESS WAKE UP JOB QUEUE PROCESSES EVERY 1 MINUTES.(THERE IS NO JOBS).因为9i的版本oracle不再提供新补丁支持,ora_j0**相关进程不停重启不太占用系统和数据库资源,在不能升级数据库的情况下,可以考虑设置job_queue_processes到一个合适值,然后忽略该问题。