2016-8-26并行惹的祸

   刚现场反馈，系统有点慢，现场把进程的数量查了一下，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