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

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

从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.

列在此次,供参考备忘.

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航