12.2 RAC DB Background process SCM0 consuming excessive CPU (文档 ID 2373451.1)

郑全

12.2 RAC DB Background process SCM0 consuming excessive CPU (文档 ID 2373451.1)
转到底部



In this Document

Symptoms

Changes

Cause

Solution

References


Applies to:
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
12.2.0.1 database has constant cpu usage, there is no connection to the database other than OEM and the OEM agent.

In AWR report, we can see:
Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 15706 07-Mar-18 16:00:21 89 1.8 2
End Snap: 15707 07-Mar-18 17:00:29 89 1.8 2
Elapsed: 60.13 (mins)     ========>60 minutes elapsed time
DB Time: 0.19 (mins)      =======>This is practically inactive database, the database is idle.

Top 10 Foreground Events by Total Wait Time
=================================
Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class
DB CPU 5.9 50.7       <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  
acknowledge over PGA limit 242 2.6 10.58ms 22.0 Scheduler
CRS call completion 30 1.7 56.90ms 14.7 Other
Disk file operations I/O 251 1.3 5.08ms 11.0 User I/O
GPnP Get Item 30 .5 16.00ms 4.1 Other
Disk file Mirror Read 62 .3 4.33ms 2.3 User I/O
log file sync 359 .2 543.17us 1.7 Commit
enq: PS - contention 758 .1 177.67us 1.2 Other
CSS initialization 60 .1 2.01ms 1.0 Other
control file sequential read 262 .1 335.07us .8 System I/O

Top Sessions
==========
Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs
28, 722 91.06 CPU + Wait for CPU 91.06 SYS oracle@xxxxxxxxxxxxxxxxxxx (SCM0) 3,595/3,600 [100%] 0     <<<<<<<<<<<   SCM0 consuming excessive CPU
3,53822 5.60 CPU + Wait for CPU 3.05 SYS oracle@xxxxxxxxxxxxxxxxxxx (IPC0) 12/361 [ 3%] 0
             RMA: IPC0 completion sync 2.54                             10/361 [ 3%] 0
69, 9016 1.27 CPU + Wait for CPU 0.51 SYS rman@xxxxxxxxxxxxxxxxxxx (TNS V1-V3) 2/361 [ 1%] 0

SQL> select * from (
select x.ksppinm param, y.ksppstvl value
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '\_%' escape '\'
order by x.ksppinm
) where param ='_dlm_stats_collect'; 2 3 4 5 6 7
PARAM
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_dlm_stats_collect
1

pstack <os pid of SCM0> output:
...
#7 0x00000000109dfa8d in kslpsprns ()
#8 0x0000000007385abd in kjsca_add ()
#9 0x0000000006e46d18 in kjbcropen ()  <<<

Changes
Upgrade to 12.2.0.1
Cause
Bug 24590018 - RAC PERF: SCM0 PROCESS USING 100% CPU, FG'S USING ~80% SYS CPU POSTING SCM0
Solution
The DLM Statistics Collection and Management slave (SCM0) is responsible for collecting and managing the statistics related to global enqueue service (GES) and global cache service (GCS). This slave exists only if DLM statistics collection is enabled.
The value is set to 1. Please go ahead and run the following command to change the value of _dlm to 0:
alter system set "_dlm_stats_collect" = 0 scope = spfile sid = '*';
This does require a reboot for changes to take effect. If a reboot is not an option, as a workaround you may kill the SCM0 process at OS level, it will respawn a new process soon.
kill -9 <os pid of SCM0>

Note: Disabling dlm_stats_collect (ie setting to 0) has no negative effect in 12.2.  This is because the stats are not yet used in 12.2 (the features that would use these stats service based affinity and cache warmup are also disabled in 12.2 by default). Versions 18 and 19 may have them enabled, so re-evaluate at that time.


References
BUG:27115422 - PERFORMANCE ISSUE ON 12.2.0.1 DB WHEN USING REGEXP_LIKE