一個很小的系統(tǒng)為什么負載那么高？
最近幫朋友優(yōu)化一個系統(tǒng)，基本的情況如下：虛擬機，centos7.9，oracle 11.2.0.4，MES系統(tǒng)，數(shù)據(jù)量<50GB,日常session數(shù)不足100；按說這應該是一個負載很低的系統(tǒng)，但是用戶卻反映系統(tǒng)CPU經(jīng)常使用率超過60%，偶爾還會有cpu滿載的情況，導致系統(tǒng)卡頓，下面我們來根據(jù)一個AWR來看看是怎么回事？
1.首先是load profile
可以看到每秒鐘logon達到280個，這個不正常，一般的系統(tǒng)來說這個值應該不超過50；另外每秒執(zhí)行的sql數(shù)達到8200多次，這對于一個session數(shù)不足100的系統(tǒng)來說也是不正常的
圖片
2.系統(tǒng)的平均CPU使用率和系統(tǒng)負載不匹配
圖片
3.看系統(tǒng)的foregroud wait event
整體看還好（多核CPU可能會出現(xiàn)db time >100%），但是top1的等待是SQL*Net break/reset to client ，通常出現(xiàn)在數(shù)據(jù)庫與客戶端之間的通信過程中。當客戶端請求中斷當前操作（"break"）或重置它們之間的連接狀態(tài)（"reset"）時，就會觸發(fā)此等待事件，而這個等待事件和前面的logons過多相契合。
圖片
4.看sql的執(zhí)行次數(shù)
這些sql都和用戶的logon有關，有系統(tǒng)后臺的，也有前臺的應用的。1小時100萬次，平均下來每秒278次和前面load profile的logons 282次契合，也就是說該系統(tǒng)1小時內(nèi)執(zhí)行了100萬次登錄操作，total的執(zhí)行次數(shù)約為2500萬次，平均每秒約6945，而整個數(shù)據(jù)庫每秒的執(zhí)行sql數(shù)為8254，6945/8254=84%，也就是差不多系統(tǒng)85%的資源都是在處理登陸操作，怪不得一個看似很小的系統(tǒng)有這么大的負載。
圖片
5.數(shù)據(jù)庫登陸會執(zhí)行哪些后臺sql
從AWR中可以看出每次登陸會執(zhí)行五次
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#>0前臺應用sql每次登陸執(zhí)行2次，why?就為了取個0？
SELECT '0' FROM DUAL其他的均是每次登陸執(zhí)行一次；
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是如下：
1.select value$ from props$ where name = 'GLOBAL_DB_NAME';
2.select privilege#,level from sysauth$ connect by grantee#=prior privilege# ;
and privilege#>0 start with grantee#=:1 and privilege#>0
3.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')
4.select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
5.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'11g 部分沒有找到官方文檔記錄，但是從AWR上可以看出是如下變化
去掉了alter session 部分，一個查詢加了hint，其他并無太大變化，19c部分沒有驗證，應該變化不大。
1.select value$ from props$ where name = 'GLOBAL_DB_NAME';
2.select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
3.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')
4.select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>06.前臺用戶登陸操作有查詢 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'綜上，根據(jù)awr分析提出三點優(yōu)化建議
- 修改應用登陸模式，沒有必要執(zhí)行一次操作就斷開一次連接，然后再重新登陸，這樣消耗了大量的系統(tǒng)資源，建議修改應用端登陸模式，一次登陸一直使用，直到連接失敗再重新連接；
2. 修改系統(tǒng)profile IDLE_TIME 為unlimited；
alter profile default limit idle_time unlimited;3. 重新審視檢查NLS_SESSION_PARAMETERS 參數(shù)機制是否合理，是否可以一次查詢解決
參考文檔：Troubleshooting ORA-3136 Connection Timeouts Errors - Database Diagnostics (Doc ID 730066.1)
