awr分析，io很高，负载也很高

lymaya · 发表于 2013-6-30 19:34

awr分析，io很高。系统负载也很高。

awrrpt_1_9995_9996.rar (34.98 KB, 下载次数: 156)

to_be_dba · 发表于 2013-6-30 22:49

select seq_integral_growthlog_id.nextval from dual
select seq_userlogin_login.nextval from dual

这两个sequence的cache值应该调大点吧

shuyingxi · 发表于 2013-6-30 22:53

Time Model Statistics
里面看最耗时的是 connection management call elapsed time 占了36的cpu

貌似看是一直在连数据库吧，是否没用连接池，一直在建立连接

yu5782647 · 发表于 2013-7-1 13:37

看了你的awr 发现你的shared pool 争用很厉害啊 library cache: mutex X 168,275,450 100 43,339 0 107,938.07 27.20
latch: row cache objects 3,406 0 40,887 12004 2.18 25.66
latch free 3,315 0 29,356 8856 2.13 18.43
latch: shared pool 6,757 0 24,255 3590 4.33 15.22
cursor: pin S wait on X 106,544 100 1,274 12 68.34 0.80
cursor: pin S 7,103,066 101 1,013 0 4,556.17 0.64
library cache load lock 3 0 168 55975 0.00 0.11

digdeep126 · 发表于 2013-7-1 14:50

Logons: 11.3 --每秒登录11.3次，这个要查是什么原因，是否密码错了，导致不断的登录
---
Time Model Statistics 中：
connection management call elapsed time 57,478.87 36.08
parse time elapsed 40,178.19 25.22
sql execute elapsed time 21,017.09 13.19
DB CPU 19,967.39 12.53
hard parse elapsed time 1,187.76 0.75
hard parse (sharing criteria) elapsed time 481.97 0.30
hard parse (bind mismatch) elapsed time 161.21 0.10
主要是登录和软解析占用了大量 DB Time.

Parses: 1,664.5
Executes: 1,686.2
Execute to Parse %: 1.29
执行解析比几乎1:1，提高 session_cached_cursors试试。
----
top5:
library cache: mutex X 168,275,450 43,339 0 27.20 Concurrency
latch: row cache objects 3,406 40,887 12004 25.66 Concurrency
latch free 3,315 29,356 8856 18.43 Other
latch: shared pool 6,757 24,255 3590 15.22 Concurrency

主要是library cache和shared pool相关的等待，你的版本是11.1.0.7.0，看看是否是bug导致的。
---
1d49xxa85nuyk select seq_userlogin_login.nextval from dual
1d49xxa85nuyk select seq_userlogin_login.nextval from dual
4mzk97ffruv5s select user_id userId, user_account userAccount, db_source dbSource, oper_date operDate from t_master_userinfo where user_account = :1
重点关注上面几条SQL语句。
---
SQL AREA 115,134 88.79 459,807 -0.13 96 4
Pct Miss为什么这么高？可以keep一部分SQL。

wisdomone1 · 发表于 2013-7-2 13:57

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 9995 30-Jun-13 13:00:30 1455    .1
End Snap: 9996 30-Jun-13 13:02:59 2538    .2
Elapsed:         2.49 (mins)
DB Time:         2,655.46 (mins)
采样仅2分钟；而db time可知数据库非常busy

Load Profile

71,894.3 即每秒产生70m左右的日志

Parses:    1,664.5    159.7
Hard parses:    1.6    0.2
硬解析有些高

Transactions:    10.4    事务不多

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:    100.00 Redo NoWait %:    100.00
Buffer Hit %:    99.98 In-memory Sort %:    100.00
Library Hit %:    100.09 Soft Parse %:    99.91
Execute to Parse %:    1.29 Latch Hit %:    99.35
Parse CPU to Parse Elapsd %:    0.00 % Non-Parse CPU:    42.25

由上可知花在解析上面的工作量达到58%左右；所以应从减少硬解析入手

Execute to Parse %:    1.29 此值应很大，很小说明大多sql仅运行一次；大多花在解析上；或解析了没有执行；

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
library cache: mutex X 168,275,450 43,339 0 27.20 Concurrency
latch: row cache objects 3,406 40,887 12004 25.66 Concurrency
latch free 3,315 29,356 8856 18.43 Other
latch: shared pool 6,757 24,255 3590 15.22 Concurrency
DB CPU      19,967      12.53

由上可知大多事件与并发有关；

library cache: mutex X的几个产生原因：
1,high version
2,high hard parse
3,invalid and reload
4,known bugs

Host CPU (CPUs: 80 Cores: 40 Sockets: 4)

Load Average Begin Load Average End %User %System %WIO %Idle
392.25    380.41    85.4    7.5    0.1    6.6
Instance CPU

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
90.8    97.3    0.0

可知cpu相当busy,80个cpu完全跑满了，撑爆了

我们先处理:library cache: mutex X等待事件
SQL ordered by Version Count
Only Statements with Version Count greater than 20 are displayed
Version Count Executions    SQL Id SQL Module SQL Text
42 1 8krp481x0sxyj    JDBC Thin Client    select app.app_id, ...
35 1 3t5q7trrqq1mz    JDBC Thin Client    select app.app_id, ...
35 1 5f5xg6q71qt4y    JDBC Thin Client    select app.app_id, ...
35 2 91spqdg7rtjgf    JDBC Thin Client    select app.app_id, ...
34 1 3vxm62hv2mc3y    JDBC Thin Client    select app.app_id, ...
34 3 d1hw67928v43z    JDBC Thin Client    select app.app_id, ...
33 1 5qabgav1zjrc0    JDBC Thin Client    select app.app_id, ...
32 1 5ywh9zay9mwqa    JDBC Thin Client    select app.app_id, ...
32 1 gutpynvnbstm2    JDBC Thin Client    select app.app_id, ...
23 18 7pztwymxu1jvn    JDBC Thin Client    select app.app_id, ...

由上可知:如上的几个sql完全可以采用绑定变量；减少硬解析和多版本产生

File IO Stats
ordered by Tablespace, File
Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)

APP_DATA /dev/vx/rdsk/datadg/ora_appdata13 8 0 5531.25 1.88 0 0 0 0.00

APP_DATA /dev/vx/rdsk/datadg/ora_appdata33 21 0 1500.00 1.33 0 0 1 0.00
APP_DATA /dev/vx/rdsk/datadg/ora_appdata34 6 0 13.33 1.00 1 0 0 0.00
APP_DATA /dev/vx/rdsk/datadg/ora_appdata35 10 0 14.00 1.00 11 0 0 0.00
APP_DATA /dev/vx/rdsk/datadg/ora_appdata36 95 1 12.53 1.07 387 3 1 0.00
APP_DATA /dev/vx/rdsk/datadg/ora_appdata37 34 0 14.12 1.62 134 1 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex01 6 0 13.33 1.00 31 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex02 9 0 12.22 1.00 47 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex03 7 0 11.43 1.00 51 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex04 15 0 11.33 1.00 29 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex05 7 0 15.71 1.00 31 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex06 7 0 24.29 1.00 40 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex07 5 0 18.00 1.00 19 0 1 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex08 6 0 11.67 1.00 13 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex09 11 0 12.73 1.00 37 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex10 7 0 11.43 1.00 39 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex11 11 0 20.00 1.00 42 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex12 10 0 14.00 1.00 20 0 5 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex13 7 0 14.29 1.00 44 0 9 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex14 8 0 11.25 1.00 35 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex15 12 0 14.17 1.00 63 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex16 121 1 13.72 1.00 98 1 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex17 113 1 14.07 1.00 91 1 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex18 99 1 12.73 1.00 125 1 2 5.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex19 12 0 11.67 1.00 32 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex20 10 0 14.00 1.00 29 0 0 0.00
APP_INDEX /dev/vx/rdsk/datadg/ora_appindex21 6 0 18.33 1.00 25 0 0 0.00
SYSAUX /dev/vx/rdsk/datadg/ora_sysaux 97 1 7.11 1.00 36 0 0 0.00
SYSTEM /dev/vx/rdsk/datadg/ora_system 80 1 24.00 1.00 10 0 0 0.00
UNDOTBS1 /dev/vx/rdsk/datadg/ora_undo01 0 0           61 0 0 0.00
UNDOTBS1 /dev/vx/rdsk/datadg/ora_undo02 0 0           2 0 0 0.00
UNDOTBS1 /dev/vx/rdsk/datadg/ora_undo03 0 0           57 0 0 0.00
UNDOTBS1 /dev/vx/rdsk/datadg/ora_undo04 0 0           1 0 0 0.00
USERS /dev/vx/rdsk/datadg/ora_user01 0 0           6 0 0 0.00

再来看等待事件
latch: row cache objects

SQL ordered by Executions
Total Executions: 252,149
Captured SQL account for 106.6% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s)    SQL Id SQL Module SQL Text
97,172 97,088 1.00 0.00 0.00 g3176qdxahvv9    JDBC Thin Client select :"SYS_B_0" from dual
90,381 0 0.00 0.00 0.00 3d6kky3cj2vrm    JDBC Thin Client    select threadfla...
42,399 42,387 1.00 0.22 0.56 1d49xxa85nuyk    JDBC Thin Client    select seq_userlogin_login....
8,344 8,344 1.00 0.00 0.00 9tgj4g8y4rwy8      select type#, blocks, extents,...
6,104 63,850 10.46 0.34 1.90 0h6b2sajwb74n      select privilege#, level from ...
3,852 3,849 1.00 0.01 0.04 3u2m6qpc5xbys    JDBC Thin Client    select count(1) fro...
3,785 3,784 1.00 0.14 0.57 12zjy1vwys7fj    JDBC Thin Client    select seq_integral_growthl...
3,641 0 0.00 0.60 1.26 axz3zy088g3fm    JDBC Thin Client    select url from t_recommend...
2,497 1,930 0.77 0.02 0.07 4mzk97ffruv5s    JDBC Thin Client    select user_id userId, ...
1,687 6,209 3.68 0.00 0.03 0k8522rmdzg4k      select privilege# from sysauth...

及
Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
PR-Process Startup 5 5 0 2 159 79,335.00
SQ-Sequence Cache 305 305 0 242 134 554.75
TX-Transaction (row lock contention) 1 1 0 1 1 500.00

由上可知sql_id为1d49xxa85nuyk的sql select seq_userlogin_login.nextval from dual，执行次数很高；42399/120计算出：每秒执行353次；
可加大此序列的cache值，以减少latch:row cache objects的竞争

再看等待事件latch free
--杨大师的blog
http://yangtingkun.itpub.net/post/468/273340

Latch Miss Sources
only latches with sleeps are shown
ordered by name, sleeps desc
Latch Name Where NoWait Misses    Sleeps Waiter Sleeps
In memory undo latch ktiFlush: child 0 16 19
In memory undo latch kticmt: child 0 10 1
In memory undo latch ktiTxnPoolFree 0 1 0
OS process allocation ksoreq_submit 0 1 0
OS process allocation ksosp_forknew 0 1 0
Real-time plan statistics latch keswxAddNewPlanEntry 0 215 215
active service list ksws_event: ksws event 0 54 59
active service list kswsnfy: create ksim groups 0 11 1
active service list kswsite: service iterator 0 1 0
cache buffer handles kcbzgs 0 110 103
cache buffer handles kcbzfs 0 7 14
cache buffers chains kcbgtcr: fast path (cr pin) 0 1,061 764
cache buffers chains kcbrls: fast release 0 616 566
cache buffers chains kcbgtcr: fast path 0 106 8
cache buffers chains kcbgtcr_9 0 93 71
cache buffers chains kcbrls_2 0 52 173
cache buffers chains kcbrls 0 30 247
cache buffers chains kcbgtcr: kslbegin excl 0 19 153
cache buffers chains kcbget: pin buffer 0 8 1
cache buffers chains kcbgcur_2 0 7 1
cache buffers chains kcbget_1 0 1 0
cache buffers chains kcbzpbuf 0 1 0

latch:shared pool
--惜分飞
http://www.xifenfei.com/3115.html
1，hard parse
2,shared pool够大

awr分析，io很高，负载也很高

浏览过的版块