一个很小的系统为什么负载那么高？-51CTO.COM

最近帮朋友优化一个系统，基本的情况如下：虚拟机，centos7.9，oracle 11.2.0.4，MES系统，数据量<50GB,日常session数不足100；按说这应该是一个负载很低的系统，但是用户却反映系统CPU经常使用率超过60%，偶尔还会有cpu满载的情况，导致系统卡顿，下面我们来根据一个AWR来看看是怎么回事？

1.首先是load profile

可以看到每秒钟logon达到280个，这个不正常，一般的系统来说这个值应该不超过50；另外每秒执行的sql数达到8200多次，这对于一个session数不足100的系统来说也是不正常的

图片

2.系统的平均CPU使用率和系统负载不匹配

图片

3.看系统的foregroud wait event

整体看还好（多核CPU可能会出现db time >100%），但是top1的等待是SQL*Net break/reset to client ，通常出现在数据库与客户端之间的通信过程中。当客户端请求中断当前操作（"break"）或重置它们之间的连接状态（"reset"）时，就会触发此等待事件，而这个等待事件和前面的logons过多相契合。

图片

4.看sql的执行次数

这些sql都和用户的logon有关，有系统后台的，也有前台的应用的。1小时100万次，平均下来每秒278次和前面load profile的logons 282次契合，也就是说该系统1小时内执行了100万次登录操作，total的执行次数约为2500万次，平均每秒约6945，而整个数据库每秒的执行sql数为8254，6945/8254=84%，也就是差不多系统85%的资源都是在处理登陆操作，怪不得一个看似很小的系统有这么大的负载。

图片

5.数据库登陆会执行哪些后台sql

从AWR中可以看出每次登陆会执行五次

sql_id:cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>01.
2.

前台应用sql每次登陆执行2次，why?就为了取个0？

SELECT '0' FROM DUAL1.

其他的均是每次登陆执行一次；

根据官方文档（Doc ID 730066.1）记录，每次用户登陆数据库需要执行如下认证sqlAuthentication SQL

When a database session is in the authentication phase, it will issue a sequence of SQL statements. The authentication is not complete until all these are parsed, executed, fetched completely. Some of the SQL statements in this list e.g. on 10.2 are:

10G是如下：

select value$ from props$ where name = 'GLOBAL_DB_NAME';


select privilege#,level from sysauth$ connect by grantee#=prior privilege# ;
and privilege#>0 start with grantee#=:1 and privilege#>0


select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), 
INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') 
from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')


select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0


ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$'
NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN'
NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+02:00'
NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT=
'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT=
'DD-MON-RR HH.MI.SSXFF AM TZR'1.






















11g 部分没有找到官方文档记录，但是从AWR上可以看出是如下变化

去掉了alter session 部分，一个查询加了hint，其他并无太大变化，19c部分没有验证，应该变化不大。

select value$ from props$ where name = 'GLOBAL_DB_NAME';


select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0


select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), 
INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') 
from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')


select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>01.













6.前台用户登陆操作有查询 NLS_SESSION_PARAMETERS 动作，是否可以一次查询，而不用十几个查询分别来做。

c1nhutgsysm77  SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT'
c8mv956mdm6vg  SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_NUMERIC_CHARACTERS'
c9u3k174kj69m  SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_ISO_CURRENCY'
9d0cthfsv43bt  SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_TIMESTAMP_TZ_FORMAT'
9zhaas4q6s24t  SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_COMP'
a0db07j0jdcrw  SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_LENGTH_SEMANTICS'1.
2.
3.
4.
5.
6.

综上，根据awr分析提出三点优化建议

修改应用登陆模式，没有必要执行一次操作就断开一次连接，然后再重新登陆，这样消耗了大量的系统资源，建议修改应用端登陆模式，一次登陆一直使用，直到连接失败再重新连接；

2. 修改系统profile IDLE_TIME 为unlimited；

alter profile default limit idle_time unlimited;1.

3. 重新审视检查NLS_SESSION_PARAMETERS 参数机制是否合理，是否可以一次查询解决

参考文档：Troubleshooting ORA-3136 Connection Timeouts Errors - Database Diagnostics (Doc ID 730066.1)