性能调优小记
性能调优小记
这里以Linux为例，其他操作系统可能命令稍有不同。
1、查看系统资源情况
vmstat命令详见Linux文档
/opt/oracle>vmstat 2
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 1 488700 245704 178276 12513572 0 1 10 17 48 1365 40 12 43 5
2 0 488700 302568 178312 12514904 0 0 157 686 3354 4342 55 22 22 1
3 1 488700 257500 178508 12517896 0 0 616 221 1352 2132 21 7 64 9
2 2 488700 232348 178820 12525392 0 0 1550 274 3632 6091 29 9 42 20
3 0 488700 225040 178880 12527336 0 0 346 452 2494 3300 45 13 38 4
vmstat命令详见Linux文档
/opt/oracle>vmstat 2
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 1 488700 245704 178276 12513572 0 1 10 17 48 1365 40 12 43 5
2 0 488700 302568 178312 12514904 0 0 157 686 3354 4342 55 22 22 1
3 1 488700 257500 178508 12517896 0 0 616 221 1352 2132 21 7 64 9
2 2 488700 232348 178820 12525392 0 0 1550 274 3632 6091 29 9 42 20
3 0 488700 225040 178880 12527336 0 0 346 452 2494 3300 45 13 38 4
等待队列的数量理想情况下不能超过主机CPU的数量，最大数量不能超过CPU数量的2倍，对应上面输出结果的“r”。
CPU如果idle小于10说明系统存在CPU资源问题，对应上面输出结果的“id”。
swap列的si与so理想情况下为0，说明系统不存在交换活动。
CPU如果idle小于10说明系统存在CPU资源问题，对应上面输出结果的“id”。
swap列的si与so理想情况下为0，说明系统不存在交换活动。
Note: 查找Linux系统的CPU数量
cat /proc/cpuinfo | grep -w “processor” |wc -l
cat /proc/cpuinfo | grep -w “processor” |wc -l
查看内存情况
/opt/oracle>free -m
total used free shared buffers cached
Mem: 15635 15476 159 0 173 12196
-/+ buffers/cache: 3106 12528
Swap: 3999 477 3522
/opt/oracle>free -m
total used free shared buffers cached
Mem: 15635 15476 159 0 173 12196
-/+ buffers/cache: 3106 12528
Swap: 3999 477 3522
Note：关注上面标红结果
top命令详见Linux文档
“top”命令也能查看资源消耗情况，特别是CPU
/opt/oracle>top
09:04:01 up 243 days, 13:01, 10 users, load average: 2.80, 3.14, 3.13
813 processes: 797 sleeping, 8 running, 8 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 40.7% 0.0% 12.7% 0.0% 0.3% 2.3% 43.7%
cpu00 42.8% 0.0% 13.4% 0.3% 1.1% 1.9% 40.0%
cpu01 37.3% 0.0% 12.6% 0.0% 0.0% 1.7% 48.2%
cpu02 47.5% 0.0% 14.1% 0.0% 0.1% 1.1% 36.9%
cpu03 35.1% 0.0% 10.5% 0.0% 0.0% 4.5% 49.7%
“top”命令也能查看资源消耗情况，特别是CPU
/opt/oracle>top
09:04:01 up 243 days, 13:01, 10 users, load average: 2.80, 3.14, 3.13
813 processes: 797 sleeping, 8 running, 8 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 40.7% 0.0% 12.7% 0.0% 0.3% 2.3% 43.7%
cpu00 42.8% 0.0% 13.4% 0.3% 1.1% 1.9% 40.0%
cpu01 37.3% 0.0% 12.6% 0.0% 0.0% 1.7% 48.2%
cpu02 47.5% 0.0% 14.1% 0.0% 0.1% 1.1% 36.9%
cpu03 35.1% 0.0% 10.5% 0.0% 0.0% 4.5% 49.7%
Mem: 16010560k av, 15808688k used, 201872k free, 0k shrd, 181324k buff
11368660k actv, 2257548k in_d, 265628k in_c
Swap: 4095984k av, 488700k used, 3607284k free 12551192k cache
11368660k actv, 2257548k in_d, 265628k in_c
Swap: 4095984k av, 488700k used, 3607284k free 12551192k cache
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
24062 oracle 18 0 1342M 1.3G 1328M S 14.0 8.5 370:13 2 oracle
6129 oracle 18 0 958M 953M 923M R 12.3 6.0 172:14 0 oracle
10077 oracle 25 0 149M 149M 145M R 4.5 0.9 0:00 2 oracle
9924 oracle 16 0 156M 155M 150M S 2.6 0.9 0:00 2 oracle
10038 oracle 17 0 140M 140M 136M S 2.5 0.8 0:00 1 oracle
10055 oracle 24 0 136M 135M 132M S 2.4 0.8 0:00 0 oracle
25529 oracle 17 0 782M 782M 775M S 1.7 5.0 3:58 0 oracle
8245 oracle 16 0 274M 274M 268M S 1.6 1.7 0:03 1 oracle
9751 oracle 16 0 70484 67M 62952 S 1.3 0.4 0:01 2 oracle
31879 oracle 16 0 830M 828M 815M R 1.0 5.2 46:06 3 oracle
9210 oracle 15 0 229M 229M 225M S 0.7 1.4 0:01 3 oracle
load average如果大于10，说明系统可能处于高负载状态。
可以查看CPU消耗最多的PID，通过PID在下面的脚本可以获取到SQL语句，分析消耗严重的原因。
iowait列也可以帮助我们定位IO问题。该值如果超过40%说明系统存在IO资源问题，需要进一步分析是磁盘本身IO慢还是数据库争用严重。
2、查看数据库情况
首先确认数据库问题是性能低下还是挂死引起的。
以非SYSDBA帐号连接数据库，通过SQL*NET方式，验证监听是否能正常接受客户端的连接。
确认数据库是否是归档，如果是，归档目录是否100%。
检查alert日志是否有错误信息。
确保Oracle软件所在目录没有达到100%，并且控制文件所在的磁盘没有达到100%。
检查监听日志listener.log是否存在，大小是否超过2G，在一些操作系统比如Linux，有文件大小限制。
查看被锁的会话(参考脚本check_lock.sql)。
根据操作系统进程ID查找对应的sql语句(参考脚本check_pid_sql.sql)
根据会话SID查找应对的sql语句(参考脚本check_sid_sql.sql)
根据用户名查找应对的sql语句(参考脚本check_username_sql.sql)
根据会话SID查找应对的sql语句(参考脚本check_sid_sql.sql)
根据用户名查找应对的sql语句(参考脚本check_username_sql.sql)
重中之重--查看数据库主要等待事件(参考脚本wait_events.sql)，根据相应的oracle等待事件来处理，可以参考书籍《Oracle Wait Interface》。
查看等待事件的sid(see script. wait_events_sid.sql)，根据SID，查看对应的sql语句(参考脚本check_sid_sql.sql)
检查是否有会话等待latch(参考脚本check_latch.sql)
查看等待事件的sid(see script. wait_events_sid.sql)，根据SID，查看对应的sql语句(参考脚本check_sid_sql.sql)
检查是否有会话等待latch(参考脚本check_latch.sql)
3、确认最近是否有任何改动：
最近数据库是否有升级过？
最近数据库的参数是否有改动过？
是否是因为表的索引的添加与删除或表结构改变过？
最近系统平台或数据库版本是否修改过？
是否有不平常的业务活动，比如月度数据导入或一次性批处理任务？
最近数据库的参数是否有改动过？
是否是因为表的索引的添加与删除或表结构改变过？
最近系统平台或数据库版本是否修改过？
是否有不平常的业务活动，比如月度数据导入或一次性批处理任务？
Note：数据库情况查看脚本
check_spid_sql.sql
SET PAGESIZE 500
set long 500000
set head off
select s.username su, substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and p.spid=;
check_sid_sql.sql
SET PAGESIZE 500
col sql_text for a70 hea "Current SQL"
select q.sql_text from v$session s, v$sql q
WHERE s.sql_address = q.address
and s.sql_hash_value + DECODE(SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value
AND s.sid=;
select q.sql_text from v$session s, v$sql q
WHERE s.sql_address = q.address
and s.sql_hash_value + DECODE(SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value
AND s.sid=;
check_username_sql.sql
set long 500000
SET PAGESIZE 500
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and s.username=upper('&username');
SET PAGESIZE 500
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and s.username=upper('&username');
11g可根据sql_id来关联查找。
check_lock.sql
prompt blocked objects from V$LOCK and SYS.OBJ$
set lines 132
col BLOCKED_OBJ format a35 trunc
col BLOCKED_OBJ format a35 trunc
select /*+ ORDERED */ l.sid, l.lmode, TRUNC(l.ctime/60) min_blocked, u.name||'.'||o.NAME blocked_obj
from (select * from v$lock where type='TM' and
sid in (select sid from v$lock where block!=0)) l,sys.obj$ o, sys.user$ u
where o.obj# = l.ID1 and o.OWNER# = u.user#;
from (select * from v$lock where type='TM' and
sid in (select sid from v$lock where block!=0)) l,sys.obj$ o, sys.user$ u
where o.obj# = l.ID1 and o.OWNER# = u.user#;
prompt blocked sessions from V$LOCK
select /*+ ORDERED */ blocker.sid blocker_sid, blocked.sid blocked_sid,
TRUNC(blocked.ctime/60) min_blocked, blocked.request
from (select * from v$lock where block != 0 and type = 'TX') blocker, v$lock blocked
where blocked.type='TX' and blocked.block = 0
and blocked.id1 = blocker.id1;
TRUNC(blocked.ctime/60) min_blocked, blocked.request
from (select * from v$lock where block != 0 and type = 'TX') blocker, v$lock blocked
where blocked.type='TX' and blocked.block = 0
and blocked.id1 = blocker.id1;
prompt blockers session details from V$SESSION
set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc
SELECT sid, serial#, username, osuser, machine FROM v$session
WHERE sid IN (select sid from v$lock where block != 0 and type = 'TX');
WHERE sid IN (select sid from v$lock where block != 0 and type = 'TX');
wait_events.sql
SELECT count(*), event FROM v$session_wait WHERE wait_time = 0
AND event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client')
GROUP BY event ORDER BY 1 DESC;
AND event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client')
GROUP BY event ORDER BY 1 DESC;
wait_events_sid.sql
col username format a12
col sid format 9999
col state format a15
col event format a45
col wait_time format 99999999
set pagesize 800
set linesize 800
select s.sid, s.username, se.event
from v$session s, v$session_wait se where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by 3;
col sid format 9999
col state format a15
col event format a45
col wait_time format 99999999
set pagesize 800
set linesize 800
select s.sid, s.username, se.event
from v$session s, v$session_wait se where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by 3;
check_latch.sql
select count(*), name latchname from v$session_wait, v$latchname
where event='latch free' and state='WAITING' and p2=latch#
group by name order by 1 desc;
where event='latch free' and state='WAITING' and p2=latch#
group by name order by 1 desc;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24996904/viewspace-767583/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
