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

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

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

重庆思庄12c OCP认证培训周末班将于2020年2月15日开课,循环开班,更多详情请访问思庄网站咨询在线客服!

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看