空闲等待事件

时间:2021-02-17 21:47:04

 

1.1 概念

空闲等待事件,表示服务器进行(server process)正在等待。换句话说,就是数据库服务正在闲着,或者说数据库资源充足、够用,如果真的存在瓶颈,那么这个瓶颈绝对不是数据库资源。该类等待事件出现,属于正常情况,在处理问题时可以被忽略;当然一些空闲等待事件,可以从侧面表明当前系统的瓶颈不在这方面。

1.2 查看空闲等待事件

1.2.1 当前数据库版本

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

1.2.2 通过配置statspack查看

配置statspack后,可通过stats$idle_event视图查看有哪些空闲事件。

SQL> select * from stats$idle_event;

EVENT

----------------------------------------------------------------

AQ Proxy Cleanup Wait

ASM background timer

DBRM Logical Idle Wait

DIAG idle wait

EMON idle wait

EMON slave idle wait

GCR sleep

HS message to agent

IORM Scheduler Slave Idle Wait

JOX Jit Process Sleep

JS external job

KSV master wait

LGWR real time apply sync

LNS ASYNC archive log

LNS ASYNC dest activation

LNS ASYNC end of log

LogMiner builder: branch

LogMiner builder: idle

LogMiner client: transaction

LogMiner preparer: idle

EVENT

----------------------------------------------------------------

LogMiner reader: log (idle)

LogMiner reader: redo (idle)

LogMiner: activate

LogMiner: client waiting for transaction

LogMiner: find session

LogMiner: generic process sleep

LogMiner: internal

LogMiner: other

LogMiner: reader waiting for more redo

LogMiner: reset

LogMiner: slave waiting for activate message

LogMiner: waiting for processes to soft detach

LogMiner: wakeup event for builder

LogMiner: wakeup event for preparer

LogMiner: wakeup event for reader

Logical Standby Apply Delay

MRP redo arrival

Null event

PING

PX Deq Credit: need buffer

PX Deq Credit: send blkd

EVENT

----------------------------------------------------------------

PX Deq: Execute Reply

PX Deq: Execution Msg

PX Deq: Index Merge Close

PX Deq: Index Merge Execute

PX Deq: Index Merge Reply

PX Deq: Join ACK

PX Deq: Metadata Update

PX Deq: Msg Fragment

PX Deq: Par Recov Execute

PX Deq: Parse Reply

PX Deq: Signal ACK

PX Deq: Table Q Normal

PX Deq: Table Q Sample

PX Deq: Txn Recovery Reply

PX Deq: Txn Recovery Start

PX Deq: kdcph_mai

PX Deq: kdcphc_ack

PX Deque wait

PX Idle Wait

Queue Monitor Shutdown Wait

Queue Monitor Slave Wait

EVENT

----------------------------------------------------------------

Queue Monitor Wait

SGA: MMAN sleep for component shrink

SQL*Net message from client

SQL*Net message to client

SQL*Net more data from client

SQL*Net vector message from client

SQL*Net vector message from dblink

STREAMS apply coord waiting for slave message

STREAMS apply slave idle wait

STREAMS apply slave waiting for coord message

STREAMS capture process filter callback wait for ruleset

STREAMS fetch slave waiting for txns

STREAMS waiting for subscribers to catch up

Space Manager: slave idle wait

Streams AQ: RAC qmn coordinator idle wait

Streams AQ: deallocate messages from Streams Pool

Streams AQ: delete acknowledged messages

Streams AQ: emn coordinator idle wait

Streams AQ: qmn coordinator idle wait

Streams AQ: qmn slave idle wait

Streams AQ: waiting for messages in the queue

EVENT

----------------------------------------------------------------

Streams AQ: waiting for time management or cleanup tasks

Streams capture: waiting for archive log

Streams fetch slave: waiting for txns

Streams: waiting for messages

VKRM Idle

VKTM Init Wait for GSGA

VKTM Logical Idle Wait

WCR: replay client notify

WCR: replay clock

WCR: replay paused

auto-sqltune: wait graph update

cell worker idle

class slave wait

client message

cmon timer

dispatcher timer

fbar timer

gcs for action

gcs remote message

ges remote message

heartbeat monitor sleep

EVENT

----------------------------------------------------------------

i/o slave wait

jobq slave wait

knlqdeq

lock manager wait for remote message

master wait

null event

parallel query dequeue

parallel recovery control message reply

parallel recovery coordinator waits for slave cleanup

parallel recovery slave idle wait

parallel recovery slave next change

parallel recovery slave wait for change

pipe get

pmon timer

pool server timer

queue messages

rdbms ipc message

shared server idle wait

simulated log write delay

single-task message

slave wait

EVENT

----------------------------------------------------------------

smon timer

virtual circuit status

wait for activate message

wait for unread message on broadcast channel

wait for unread message on multiple broadcast channels

wakeup event for builder

wakeup event for preparer

wakeup event for reader

wakeup time manager

watchdog main loop

135 rows selected

1.2.3 通过v$event_name视图查看

SQL> select name from v$event_name where wait_class='Idle';

NAME

----------------------------------------------------------------

pmon timer

VKTM Logical Idle Wait

VKTM Init Wait for GSGA

IORM Scheduler Slave Idle Wait

rdbms ipc message

i/o slave wait

VKRM Idle

wait for unread message on broadcast channel

wait for unread message on multiple broadcast channels

class slave wait

KSV master wait

PING

watchdog main loop

DIAG idle wait

ges remote message

gcs remote message

heartbeat monitor sleep

GCR sleep

SGA: MMAN sleep for component shrink

MRP redo arrival

NAME

----------------------------------------------------------------

LNS ASYNC archive log

LNS ASYNC dest activation

LNS ASYNC end of log

simulated log write delay

LGWR real time apply sync

parallel recovery slave idle wait

LogMiner builder: idle

LogMiner builder: branch

LogMiner preparer: idle

LogMiner reader: log (idle)

LogMiner reader: redo (idle)

LogMiner client: transaction

LogMiner: other

LogMiner: activate

LogMiner: reset

LogMiner: find session

LogMiner: internal

Logical Standby Apply Delay

parallel recovery coordinator waits for slave cleanup

parallel recovery control message reply

parallel recovery slave next change

NAME

----------------------------------------------------------------

PX Deq: Txn Recovery Start

PX Deq: Txn Recovery Reply

fbar timer

smon timer

PX Deq: Metadata Update

Space Manager: slave idle wait

PX Deq: Index Merge Reply

PX Deq: Index Merge Execute

PX Deq: Index Merge Close

PX Deq: kdcph_mai

PX Deq: kdcphc_ack

virtual circuit next request

shared server idle wait

dispatcher timer

cmon timer

pool server timer

JOX Jit Process Sleep

jobq slave wait

pipe get

PX Deque wait

PX Idle Wait

NAME

----------------------------------------------------------------

PX Deq: Join ACK

PX Deq Credit: need buffer

PX Deq Credit: send blkd

PX Deq: Msg Fragment

PX Deq: Parse Reply

PX Deq: Execute Reply

PX Deq: Execution Msg

PX Deq: Table Q Normal

PX Deq: Table Q Sample

Streams fetch slave: waiting for txns

Streams: waiting for messages

Streams capture: waiting for archive log

single-task message

SQL*Net message from client

SQL*Net vector message from client

SQL*Net vector message from dblink

PL/SQL lock timer

Streams AQ: emn coordinator idle wait

EMON slave idle wait

Streams AQ: waiting for messages in the queue

Streams AQ: waiting for time management or cleanup tasks

NAME

----------------------------------------------------------------

Streams AQ: delete acknowledged messages

Streams AQ: deallocate messages from Streams Pool

Streams AQ: qmn coordinator idle wait

Streams AQ: qmn slave idle wait

Streams AQ: RAC qmn coordinator idle wait

HS message to agent

ASM background timer

auto-sqltune: wait graph update

WCR: replay client notify

WCR: replay clock

WCR: replay paused

JS external job

cell worker idle

96 rows selected