刚现场反馈,系统有点慢,现场把进程的数量查了一下,1万多个进程。现场数据库是Oracle11g RAC两个实例。
select count(*) from gv$process;
COUNT(*)----------
10497
select s.USERNAME,s.SCHEMANAME,s.MACHINE,s.PROGRAM,s.EVENT from v$session s
USERNAME SCHEMANAME MACHINE PROGRAM EVENT
GG_ZC GG_ZC zctzjhapp03 oracle@zcnora02 (PC82) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (PM36) latch: parallel query alloc buffer
GG_ZC GG_ZC zctzjhapp03 oracle@zcnora02 (PB62) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (PU40) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (P098) latch: parallel query alloc buffer
GG_ZC GG_ZC zctzjhapp03 oracle@zcnora02 (P926) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (PS00) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (P440) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (PL19) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (PT20) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (PI79) latch: parallel query alloc buffer
GG_ZC GG_ZC zcjjapp01 oracle@zcnora02 (P324) latch: parallel query alloc buffer
从等待事件上看,是并行查询惹的祸。
select s.owner,s.table_name,degree from dba_tables s where s.degree >1;GG_ZC XXXX1 8
GG_ZC XXXX2 8
alter table XXXX1 noparallel;
alter table XXXX2 noparallel;
select count(*) from gv$process;
COUNT(*)
----------
2099