服务器 频道

Oracle9i到10g,空闲等待事件的变化

  【IT168 服务器学院】从Statspack的创建脚本中,我们可以看到从Oracle9i到Oracle10g,空闲等待事件的改变.

  空闲等待事件在Statspack中,记录在系统表STATS$IDLE_EVENT中.检查其创建脚本我们可以找到这些变化.这个脚本位于$ORACLE_HOME/rdbms/admin/spctab.sql

  在Oracle9iR2中,空闲等待有:

  /* ------------------------------------------------------------------------- */

  create table          STATS$IDLE_EVENT
  (event                varchar2(64)     not null
  ,constraint STATS$IDLE_EVENT_PK primary key (event)
   using index tablespace &&tablespace_name
     storage (initial 100k next 100k pctincrease 0)
  ) tablespace &&tablespace_name
    storage (initial 100k next 100k pctincrease 0) pctfree 5 pctused 40;

  insert into STATS$IDLE_EVENT (event) values (''smon timer'');
  insert into STATS$IDLE_EVENT (event) values (''pmon timer'');
  insert into STATS$IDLE_EVENT (event) values (''rdbms ipc message'');
  insert into STATS$IDLE_EVENT (event) values (''Null event'');
  insert into STATS$IDLE_EVENT (event) values (''parallel query dequeue'');
  insert into STATS$IDLE_EVENT (event) values (''pipe get'');
  insert into STATS$IDLE_EVENT (event) values (''client message'');
  insert into STATS$IDLE_EVENT (event) values (''SQL*Net message to client'');
  insert into STATS$IDLE_EVENT (event) values (''SQL*Net message from client'');
  insert into STATS$IDLE_EVENT (event) values (''SQL*Net more data from client'');
  insert into STATS$IDLE_EVENT (event) values (''dispatcher timer'');
  insert into STATS$IDLE_EVENT (event) values (''virtual circuit status'');
  insert into STATS$IDLE_EVENT (event) values (''lock manager wait for remote message'');
  insert into STATS$IDLE_EVENT (event) values (''PX Idle Wait'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Execution Msg'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Table Q Normal'');
  insert into STATS$IDLE_EVENT (event) values (''wakeup time manager'');
  insert into STATS$IDLE_EVENT (event) values (''slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''i/o slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''jobq slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''null event'');
  insert into STATS$IDLE_EVENT (event) values (''gcs remote message'');
  insert into STATS$IDLE_EVENT (event) values (''gcs for action'');
  insert into STATS$IDLE_EVENT (event) values (''ges remote message'');
  insert into STATS$IDLE_EVENT (event) values (''queue messages'');
  commit;

  create public synonym  STATS$IDLE_EVENT   for STATS$IDLE_EVENT;

  /* ------------------------------------------------------------------------- */

  在Oracle10gR2中,这个数字大大增加:

  /* ------------------------------------------------------------------------- */

  create table          STATS$IDLE_EVENT
  (event                varchar2(64)     not null
  ,constraint STATS$IDLE_EVENT_PK primary key (event)
   using index tablespace &&tablespace_name
     storage (initial 100k next 100k pctincrease 0)
  ) tablespace &&tablespace_name
    storage (initial 100k next 100k pctincrease 0) pctfree 5 pctused 40;

  insert into STATS$IDLE_EVENT (event) values (''smon timer'');
  insert into STATS$IDLE_EVENT (event) values (''pmon timer'');
  insert into STATS$IDLE_EVENT (event) values (''rdbms ipc message'');
  insert into STATS$IDLE_EVENT (event) values (''Null event'');
  insert into STATS$IDLE_EVENT (event) values (''parallel query dequeue'');
  insert into STATS$IDLE_EVENT (event) values (''pipe get'');
  insert into STATS$IDLE_EVENT (event) values (''client message'');
  insert into STATS$IDLE_EVENT (event) values (''SQL*Net message to client'');
  insert into STATS$IDLE_EVENT (event) values (''SQL*Net message from client'');
  insert into STATS$IDLE_EVENT (event) values (''SQL*Net more data from client'');
  insert into STATS$IDLE_EVENT (event) values (''dispatcher timer'');
  insert into STATS$IDLE_EVENT (event) values (''virtual circuit status'');
  insert into STATS$IDLE_EVENT (event) values (''lock manager wait for remote message'');
  insert into STATS$IDLE_EVENT (event) values (''PX Idle Wait'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Execution Msg'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Table Q Normal'');
  insert into STATS$IDLE_EVENT (event) values (''wakeup time manager'');
  insert into STATS$IDLE_EVENT (event) values (''slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''i/o slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''jobq slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''null event'');
  insert into STATS$IDLE_EVENT (event) values (''gcs remote message'');
  insert into STATS$IDLE_EVENT (event) values (''gcs for action'');
  insert into STATS$IDLE_EVENT (event) values (''ges remote message'');
  insert into STATS$IDLE_EVENT (event) values (''queue messages'');
  insert into STATS$IDLE_EVENT (event) values (''wait for unread message on broadcast channel'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq Credit: send blkd'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Execute Reply'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Signal ACK'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deque wait'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq Credit: need buffer'');
  insert into STATS$IDLE_EVENT (event) values (''STREAMS apply coord waiting for slave message'');
  insert into STATS$IDLE_EVENT (event) values (''STREAMS apply slave waiting for coord message'');
  insert into STATS$IDLE_EVENT (event) values (''Queue Monitor Wait'');
  insert into STATS$IDLE_EVENT (event) values (''Queue Monitor Slave Wait'');
  insert into STATS$IDLE_EVENT (event) values (''wakeup event for builder'');
  insert into STATS$IDLE_EVENT (event) values (''wakeup event for preparer'');
  insert into STATS$IDLE_EVENT (event) values (''wakeup event for reader'');
  insert into STATS$IDLE_EVENT (event) values (''wait for activate message'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Par Recov Execute'');
  insert into STATS$IDLE_EVENT (event) values (''PX Deq: Table Q Sample'');
  insert into STATS$IDLE_EVENT (event) values (''STREAMS apply slave idle wait'');
  insert into STATS$IDLE_EVENT (event) values (''STREAMS capture process filter callback wait for ruleset'');
  insert into STATS$IDLE_EVENT (event) values (''STREAMS fetch slave waiting for txns'');
  insert into STATS$IDLE_EVENT (event) values (''STREAMS waiting for subscribers to catch up'');
  insert into STATS$IDLE_EVENT (event) values (''Queue Monitor Shutdown Wait'');
  insert into STATS$IDLE_EVENT (event) values (''AQ Proxy Cleanup Wait'');
  insert into STATS$IDLE_EVENT (event) values (''knlqdeq'');
  insert into STATS$IDLE_EVENT (event) values (''class slave wait'');
  insert into STATS$IDLE_EVENT (event) values (''master wait'');
  insert into STATS$IDLE_EVENT (event) values (''DIAG idle wait'');
  insert into STATS$IDLE_EVENT (event) values (''ASM background timer'');
  insert into STATS$IDLE_EVENT (event) values (''KSV master wait'');
  insert into STATS$IDLE_EVENT (event) values (''EMON idle wait'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: RAC qmn coordinator idle wait'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: qmn coordinator idle wait'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: qmn slave idle wait'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: waiting for time management or cleanup tasks'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: waiting for messages in the queue'');
  insert into STATS$IDLE_EVENT (event) values (''Streams fetch slave: waiting for txns'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: deallocate messages from Streams Pool'');
  insert into STATS$IDLE_EVENT (event) values (''Streams AQ: delete acknowledged messages'');
  insert into STATS$IDLE_EVENT (event) values (''LNS ASYNC archive log'');
  insert into STATS$IDLE_EVENT (event) values (''LNS ASYNC dest activation'');
  insert into STATS$IDLE_EVENT (event) values (''LNS ASYNC end of log'');
  insert into STATS$IDLE_EVENT (event) values (''LogMiner: client waiting for transaction'');
  insert into STATS$IDLE_EVENT (event) values (''LogMiner: slave waiting for activate message'');
  insert into STATS$IDLE_EVENT (event) values (''LogMiner: wakeup event for builder'');
  insert into STATS$IDLE_EVENT (event) values (''LogMiner: wakeup event for preparer'');
  insert into STATS$IDLE_EVENT (event) values (''LogMiner: wakeup event for reader'');

  commit;

  create public synonym  STATS$IDLE_EVENT   for STATS$IDLE_EVENT;

  /* ------------------------------------------------------------------------- */

  如果你注意到的话,很多等待事件在9i中属于空闲等待,但是未被列入,有的是为了向后兼容,有的则是因为Bug.

  列在此次,供参考备忘.

0
相关文章