从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.
列在此次,供参考备忘.