When running spreport.sql top events are idle event, no I/O, very little CPU...
To fix the issue in the report, need to sync idle events in STATS$IDLE_EVENT
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- --------
Begin Snap: 42251 15-Jan-24 10:00:06 233 3.3
End Snap: 42252 15-Jan-24 11:00:11 232 3.3
Elapsed: 60.08 (mins) Av Act Sess: 1.0
DB time: 61.11 (mins) DB CPU: 60.82 (mins)
When running perfstat, only idle event appear as top time consumets.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~
Event Waits Time (s) (ms) Time
-------------------------- ------------ ----------- ------ ------
LGWR worker group idle 6,575 7,202 1095 33.2
AQPC idle 121 3,631 30005 16.7
CPU time 3,619 16.7
heartbeat redo informer 3,604 3,605 1000 16.6
lreg timer 1,260 3,604 2860 16.6
-------------------------------------------------------------
There were idle events that have been introduced in recent versions, which are not listed in STATS$IDLE_EVENT
To sync the idle events list:
SELECT COUNT(*) FROM STATS$IDLE_EVENT;
135
SELECT COUNT(*) FROM V$EVENT_NAME where wait_class='Idle';
121
desc STATS$IDLE_EVENT
Name Null? Type
----------------------------------------- -------- -----------------
EVENT NOT NULL VARCHAR2(64 CHAR)
desc V$EVENT_NAME
Name Null? Type
----------------------------------------- -------- -----------------
EVENT# NUMBER
EVENT_ID NUMBER
NAME VARCHAR2(64)
PARAMETER1 VARCHAR2(64)
PARAMETER2 VARCHAR2(64)
PARAMETER3 VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
DISPLAY_NAME VARCHAR2(64)
CON_ID NUMBER
SELECT NAME FROM V$EVENT_NAME WHERE wait_class='Idle' order by 1;
NAME
----------------------------------------------------------------
AQ Cross Master idle
AQ: 12c message cache init wait
AQPC idle
ASM background timer
Backup Appliance Comm SGA setup wait
Backup Appliance Servlet wait
Backup Appliance Surrogate wait
Backup Appliance waiting for work
Backup Appliance waiting restore start
DIAG idle wait
EMON slave idle wait
Emon coordinator main loop
Emon slave main loop
GCR sleep
HS message to agent
IORM Scheduler Slave Idle Wait
JOX Jit Process Sleep
JS external job
LGWR real time apply sync
LGWR worker group idle
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
LogMiner reader: log (idle)
LogMiner reader: redo (idle)
LogMiner: activate
LogMiner: find session
LogMiner: internal
LogMiner: other
LogMiner: reset
Logical Standby Apply Delay
MRP redo arrival
OFS idle
PING
PL/SQL lock timer
PX Deq Credit: need buffer
PX Deq Credit: send blkd
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: Parse Reply
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
REPL Apply: txns
REPL Capture/Apply: RAC AQ qmn coordinator
REPL Capture/Apply: messages
REPL Capture: archive log
SGA: MMAN sleep for component shrink
SQL*Net message from client
SQL*Net vector message from client
SQL*Net vector message from dblink
Sharded Queues : Part Maintenance idle
Space Manager: slave idle wait
Streams AQ: deallocate messages from Streams Pool
Streams AQ: delete acknowledged messages
Streams AQ: emn coordinator idle wait
Streams AQ: load balancer idle
Streams AQ: qmn coordinator idle wait
Streams AQ: qmn slave idle wait
Streams AQ: waiting for messages in the queue
Streams AQ: waiting for time management or cleanup tasks
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
cmon timer
dispatcher timer
fbar timer
gcs remote message
ges remote message
gopp msg
heartbeat monitor sleep
heartbeat redo informer
i/o slave wait
imco timer
iowp file id
iowp msg
jobq slave wait
lreg timer
netp network
parallel recovery control message reply
parallel recovery coordinator idle wait
parallel recovery coordinator waits for slave cleanup
parallel recovery slave idle wait
parallel recovery slave next change
pipe get
pmon timer
pool server timer
process in prespawned state
rdbms ipc message
recovery merger idle wait
recovery receiver idle wait
recovery sender idle wait
shared server idle wait
simulated log write delay
single-task message
smon timer
virtual circuit next request
wait for unread message on broadcast channel
wait for unread message on multiple broadcast channels
watchdog main loop
SELECT name FROM V$EVENT_NAME WHERE wait_class='Idle'
MINUS
SELECT event FROM STATS$IDLE_EVENT;
order by 1
NAME
---------------------------------------------------------------------
AQ Cross Master idle
AQ: 12c message cache init wait
AQPC idle => top perfstat wais
Backup Appliance Comm SGA setup wait
Backup Appliance Servlet wait
Backup Appliance Surrogate wait
Backup Appliance waiting for work
Backup Appliance waiting restore start
Emon coordinator main loop
Emon slave main loop
LGWR worker group idle => top perfstat wais
OFS idle
PL/SQL lock timer
REPL Apply: txns
REPL Capture/Apply: RAC AQ qmn coordinator
REPL Capture/Apply: messages
REPL Capture: archive log
Sharded Queues : Part Maintenance idle
Streams AQ: load balancer idle
gopp msg
heartbeat redo informer
imco timer
iowp file id
iowp msg
lreg timer
netp network
parallel recovery coordinator idle wait
process in prespawned state
recovery merger idle wait
recovery receiver idle wait
recovery sender idle wait
virtual circuit next request
SQL>INSERT INTO STATS$IDLE_EVENT
SELECT name FROM V$EVENT_NAME WHERE wait_class='Idle'
MINUS
SELECT event FROM STATS$IDLE_EVENT;
32 rows created.
SQL> commit;
Commit complete.
Now, when running spreport.sql top events are without idle events.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ Event Waits Time (s) (ms) Time
-------------------------- ------------ ----------- ------ ------
CPU time 3,619 99.2
db file async I/O submit 2,058 11 5 .3
db file sequential read 59,240 4 0 .1
log file parallel write 6,568 4 1 .1
oracle thread bootstrap 179 4 20 .1
-------------------------------------------------------------
No comments:
Post a Comment