怎么样判断系统运行慢，或者SQL运行慢原因以及解决办法?
第一步：判断哪个应用进程消耗资源
查看CPU的信息：
[root@alimysql03 ~]# top #top查看cup的资源和内存利用率，磁盘io等问题：主要看%CPU，MEM，WA等参数，按1可以查看多
个CPU的信息。
top – 09:55:39 up 41 days, 1 min, 2 users, load average: 0.02, 0.04, 0.05
Tasks: 144 total, 2 running, 141 sleeping, 1 stopped, 0 zombie
%Cpu0 : 1.0 us, 0.3 sy, 0.0 ni, 98.6 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 1.0 us, 0.0 sy, 0.0 ni, 99.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8011076 total, 1588524 free, 2358156 used, 4064396 buff/cache
KiB Swap: 8388604 total, 7984112 free, 404492 used. 2923288 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18979 oracle -2 0 2845172 17540 14412 S 98.7 0.2 134:43.63 oracle
16154 mysql 20 0 10.144g 763720 5924 S 2.0 9.5 658:07.26 mysqld
查看io的信息：
iostat主要用于监控系统设备的IO负载情况，iostat首次运行时显示自系统启动开始的各项统计信
息，之后运行iostat将显示自上次运行该命令以后的统计信息。用户可以通过指定统计的次数和时间来获得所需的统计信息
[root@alimysql03 ~]# iostat -d -k 3
参数 -d 表示，显示设备(磁盘)使用状态;-k某些使用block为单位的列强制使用Kilobytes为单位;2表示，数据显示每隔2秒刷新一次。
Linux 3.10.0-514.el7.x86_64 (alimysql03) 2017年11月27日 _x86_64_ (2 CPU)
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 0.61 3.19 18.31 11304036 64868149
sdb 0.11 2.02 7.11 7175028 25182752
dm-0 0.74 5.20 25.30 18442140 89639957
dm-1 0.03 0.00 0.12 7144 408740
tps：该设备每秒的传输次数(Indicate the number of transfers per second that were issued to the device.)。”一次传输”意
思是”一次I/O请求”。多个逻辑请求可能会被合并为”一次I/O请求”。”一次传输”请求的大小是未知的。
kB_read/s：每秒从设备(drive expressed)读取的数据量;
kB_wrtn/s：每秒向设备(drive expressed)写入的数据量;
kB_read：读取的总数据量;
kB_wrtn：写入的总数量数据量;这些单位都为Kilobytes。
[root@alimysql03 ~]# iostat -d -x -m 3
Linux 3.10.0-514.el7.x86_64 (alimysql03) 2017年11月27日 _x86_64_ (2 CPU)
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.04 0.12 0.49 0.00 0.02 70.88 0.01 19.17 11.84 20.99 0.76 0.05
sdb 0.00 0.01 0.05 0.07 0.00 0.01 164.19 0.00 30.40 11.76 43.43 0.51 0.01
dm-0 0.00 0.00 0.17 0.58 0.01 0.02 81.99 0.02 23.02 11.88 26.20 0.68 0.05
dm-1 0.00 0.00 0.00 0.03 0.00 0.00 8.01 0.00 14.99 5.87 15.13 0.04 0.00
rrqm/s：每秒这个设备相关的读取请求有多少被Merge了(当系统调用需要读取数据的时候，VFS将请求发到各个FS，如果FS发现不同的读取
请求读取的是相同Block的数据，FS会将这个请求合并Merge);wrqm/s：每秒这个设备相关的写入请求有多少被Merge了。
rsec/s：每秒读取的扇区数;
wsec/：每秒写入的扇区数。
rKB/s：The number of read requests that were issued to the device per second;
wKB/s：The number of write requests that were issued to the device per second;
avgrq-sz 平均请求扇区的大小
avgqu-sz 是平均请求队列的长度。毫无疑问，队列长度越短越好。
await： 每一个IO请求的处理的平均时间(单位是微秒毫秒)。这里可以理解为IO的响应时间，一般地系统IO响应时间应该低于5ms，如果
大于10ms就比较大了。
这个时间包括了队列时间和服务时间，也就是说，一般情况下，await大于svctm，它们的差值越小，则说明队列时间越短，反之差值越
大，队列时间越长，说明系统出了问题。
svctm 表示平均每次设备I/O操作的服务时间(以毫秒为单位)。如果svctm的值与await很接近，表示几乎没有I/O等待，磁盘性能很好，如
果await的值远高于svctm的值，则表示I/O队列等待太长， 系统上运行的应用程序将变慢。
%util： 在统计时间内所有处理IO时间，除以总共统计时间。例如，如果统计间隔1秒，该设备有0.8秒在处理IO，而0.2秒闲置，那么该设备
的%util = 0.8/1 = 80%，所以该参数暗示了设备的繁忙程度
一般地，如果该参数是100%表示设备已经接近满负荷运行了(当然如果是多磁盘，即使%util是100%，因为磁盘的并发能力，所以磁盘使用未必就到了瓶颈)。
查看网络信息：
Netstat 命令用于显示各种网络相关信息，如网络连接，路由表，接口状态 (Interface Statistics)，masquerade 连接，多播成员 (Multicast Memberships) 等等
[root@alimysql03 ~]# netstat -t
[root@alimysql03 ~]# netstat -u
[root@alimysql03 ~]# netstat -alop
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name Timer
tcp 0 0 localhost:6379 0.0.0.0:* LISTEN 31334/redis-server off (0.00/0/0)
tcp 0 0 alimysql03:ssh 192.168.75.161:54628 ESTABLISHED 27722/sshd: root@pt keepalive (4256.28/0/0)
tcp 0 0 alimysql03:39710 alimysql03:24913 ESTABLISHED 16154/mysqld off (0.00/0/0)
tcp 0 0 alimysql03:ssh 192.168.75.162:52753 ESTABLISHED 29032/sshd: root@pt keepalive (5075.48/0/0)
tcp 0 0 alimysql03:24913 alimysql03:39712 ESTABLISHED 16154/mysqld off (0.00/0/0)
tcp6 0 0 alimysql03:mysql alimysql05:38306 ESTABLISHED 16154/mysqld keepalive (3797.49/0/0)
tcp6 0 0 alimysql03:mysql alimysql04:47580 TIME_WAIT – timewait (1.84/0/0)
tcp6 0 0 alimysql03:mysql alimysql05:38304 ESTABLISHED 16154/mysqld keepalive (3797.49/0/0)
udp 0 0 localhost:323 0.0.0.0:* 673/chronyd off (0.00/0/0)
Proto RefCnt Flags Type State I-Node PID/Program name Path
unix 2 [ ACC ] STREAM LISTENING 4263722 1/systemd /var/run/rpcbind.sock
unix 2 [ ACC ] STREAM LISTENING 9791 1/systemd /run/systemd/private
unix 2 [ ACC ] STREAM LISTENING 4241663 8624/mysqld /opt/mysql/mysql3307.sock
unix 2 [ ACC ] STREAM LISTENING 9799 1/systemd /run/lvm/lvmetad.socket
unix 2 [ ] DGRAM 9832 1/systemd /run/systemd/shutdownd
unix 2 [ ACC ] STREAM LISTENING 9834 1/systemd /run/lvm/lvmpolld.socket
unix 2 [ ] DGRAM 1387 1/systemd /run/systemd/notify
unix 2 [ ] DGRAM 1389 1/systemd /run/systemd/cgroups-agent
第二步：制作AWR报表
[root@alimysql03 ~]#su – oracle
[oracle@alimysql03 ~]#sqlplus / as sysdba
sys as sysdba
–调用脚本，生成文件
SQL> @?/rdbms/admin/awrrpt.sql
输入报告参数
输入生成报告类型，目前AWR提供txt和html两种格式。需要确认生成格式，默认是html格式。
html
报告涉及天数范围
3
输入开始和结束的snapshot编号
输入天数信息后，AWR生成代码会将天数范围内的snapshot镜像点列出，供输入选择
Enter value for begin_snap: 1796
Begin Snapshot Id specified: 1796
Enter value for end_snap: 1813
确定报告名称
最后就是确定生成报告的名称。一般采用默认的名称就可以了
于是，指定目录上可以看到相应的报告文件。
第二个就是调用脚本的方式问题。调用时使用的sqlplus客户端可以在Oracle服务器本机上(远程登录)，也可以在客户端机器本
机上。笔者建议是在客户端本机上进行生成，这样可以避免报告文件来回拷贝的工作。但是最好要保证客户端版本与服务器版本相匹配。
第三步查看AWR报表，找出问题的SQL或者系统在哪里出现问题
SQL ordered by Elapsed Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement pided into the Total Database Time multiplied by 100
%Total – Elapsed Time as a percentage of Total DB time
%CPU – CPU Time as a percentage of Elapsed Time
%IO – User I/O Time as a percentage of Elapsed Time
Captured SQL account for 99.0% of Total DB Time (s): 7,366
Captured PL/SQL account for 0.0% of Total DB Time (s): 7,366
Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
7,163.06 20,563 0.35 97.24 99.96 0.00 a2tdryafmbwch OGG-RJRWZ2ZX-OPEN_DATA_SOURCE DELETE FROM “JRWZ2_ZX”.”JS_ZX_…
54.38 3 18.13 0.74 99.90 0.00 gf507afc2c36b TOAD 12.1.0.22 select sql_text from v$sql whe…
29.07 32 0.91 0.39 99.81 0.00 bps65v7v04b9h TOAD 12.1.0.22 SELECT a.sid, decode(b.class, …
14.19 66 0.21 0.19 99.80 0.00 dzm7tjg4ksc0b TOAD background query session Select lk.SID, se.username, se…
8.17 1 8.17 0.11 21.74 77.77 abmvfc90dzky0 TOAD 12.1.0.22 select count(1) from JRWZ2_ZX….
6.87 1 6.87 0.09 22.59 79.38 40ycagu7bv1gv TOAD 12.1.0.22 select count(1) from jrwz2_zx….
2.50 156 0.02 0.03 99.09 0.00 dtgnyrc4v3tk4 TOAD 12.1.0.22 SELECT round(bitand(s.ownerid,…
2.46 3 0.82 0.03 87.72 0.00 6btpzpwsbw52s TOAD background query session SELECT sid, owner, type, objec…
1.54 28 0.05 0.02 99.84 0.00 4q2wng79hbc1w TOAD 12.1.0.22 select o.sid, o.sql_text, o.ad…
0.82 55 0.01 0.01 98.85 0.00 6zzd74gqqndf2 TOAD 12.1.0.22 SELECT round(bitand(s.ownerid,…
Back to SQL Statistics
Back to Top
a2tdryafmbwch这条sql_id的执行状态: Elapsed Time花费时间为7163秒，Executions重复执行次数为20563次，%CPU为99.96
SQL ordered by CPU Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total – CPU Time as a percentage of Total DB CPU
%CPU – CPU Time as a percentage of Elapsed Time
%IO – User I/O Time as a percentage of Elapsed Time
Captured SQL account for 99.6% of Total CPU Time (s): 7,298
Captured PL/SQL account for 0.0% of Total CPU Time (s): 7,298
CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
7,159.86 20,563 0.35 98.10 7,163.06 99.96 0.00 a2tdryafmbwch OGG-RJRWZ2ZX-OPEN_DATA_SOURCE DELETE FROM “JRWZ2_ZX”.”JS_ZX_…
54.33 3 18.11 0.74 54.38 99.90 0.00 gf507afc2c36b TOAD 12.1.0.22 select sql_text from v$sql whe…
29.02 32 0.91 0.40 29.07 99.81 0.00 bps65v7v04b9h TOAD 12.1.0.22 SELECT a.sid, decode(b.class, …
14.16 66 0.21 0.19 14.19 99.80 0.00 dzm7tjg4ksc0b TOAD background query session Select lk.SID, se.username, se…
2.48 156 0.02 0.03 2.50 99.09 0.00 dtgnyrc4v3tk4 TOAD 12.1.0.22 SELECT round(bitand(s.ownerid,…
2.16 3 0.72 0.03 2.46 87.72 0.00 6btpzpwsbw52s TOAD background query session SELECT sid, owner, type, objec…
1.78 1 1.78 0.02 8.17 21.74 77.77 abmvfc90dzky0 TOAD 12.1.0.22 select count(1) from JRWZ2_ZX….
1.55 1 1.55 0.02 6.87 22.59 79.38 40ycagu7bv1gv TOAD 12.1.0.22 select count(1) from jrwz2_zx….
1.53 28 0.05 0.02 1.54 99.84 0.00 4q2wng79hbc1w TOAD 12.1.0.22 select o.sid, o.sql_text, o.ad…
0.81 55 0.01 0.01 0.82 98.85 0.00 6zzd74gqqndf2 TOAD 12.1.0.22 SELECT round(bitand(s.ownerid,…
Back to SQL Statistics
Back to Top
SQL ordered by User I/O Wait Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total – User I/O Time as a percentage of Total User I/O Wait time
%CPU – CPU Time as a percentage of Elapsed Time
%IO – User I/O Time as a percentage of Elapsed Time
Captured SQL account for 73.6% of Total User I/O Wait Time (s): 18
Captured PL/SQL account for 0.6% of Total User I/O Wait Time (s): 18
User I/O Time (s) Executions UIO per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
6.35 1 6.35 35.02 8.17 21.74 77.77 abmvfc90dzky0 TOAD 12.1.0.22 select count(1) from JRWZ2_ZX….
5.45 1 5.45 30.05 6.87 22.59 79.38 40ycagu7bv1gv TOAD 12.1.0.22 select count(1) from jrwz2_zx….
0.62 2 0.31 3.43 0.66 2.42 94.04 350myuyx0t1d6 insert into wrh$_tablespace_st…
0.25 1 0.25 1.40 0.27 5.13 93.34 a6ktvdq2g8q99 TOAD 12.1.0.22 SELECT ou.NAME owner, oc.NAME …
0.11 2 0.06 0.62 0.31 61.32 36.34 6ajkhukk78nsr begin prvt_hdm.auto_execute( :…
0.07 2 0.04 0.39 0.07 5.39 96.12 4bh0c6kf1dw2q TOAD 12.1.0.22 select count(1) from jrwz2_zx….
0.06 1 0.06 0.34 0.06 1.57 96.39 83yqh77vjh1jr TOAD 12.1.0.22 SELECT o.owner, o.object_name,…
0.06 36 0.00 0.32 0.06 12.52 90.20 3m8smr0v7v1m6 INSERT INTO sys.wri$_adv_messa…
0.06 1 0.06 0.31 0.08 24.55 73.69 fnk7155mk2jq6 insert into wrh$_sysmetric_his…
0.06 1 0.06 0.31 0.06 1.74 98.25 apw0wpatc3pu8 TOAD 12.1.0.22 Select owner, object_name, obj…
[oracle@bspdev test]$ ls -l
total 508
-rw-r–r–. 1 oracle oinstall 515262 Jun 21 13:10 awrrpt_1_1796_1813.html
找出有问题的SQL：sql_id为a2tdryafmbwch 花费时间7,163.06秒 20,563 0.35 97.24 99.96 0.00 a2tdryafmbwch OGG-RJRWZ2ZX-OPEN_DATA_SOURCE
如果SQL不在awr报表中，可以通过模糊查询找到这个SQL，例如：
–1.执行一个SQL
SELECT /* TOTO */ ename, dname
FROM dept d join emp e USING (deptno);
–2.获取这个SQL的sql_id和 child_number
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE ‘%TOTO%’;
SQL_ID CHILD_NUMBER
———- —————————–
gwp663cqh5qbf 0
–3.根据sql_id就可以显示刚才那个SQL的执行计划
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(‘a2tdryafmbwch’,0,’all’))
select * from v$sql where sql_id=’a2tdryafmbwch’
sql_id表示存储在cursor cache中的SQL语句的id，child_number用于指示缓存sql语句计划的子id，format参数用于控制包含在输出中的信息类型，官档的参数如下：
1.BASIC: 显示最少的信息，只包括操作类型，ID名称和选项。
2.TYPICAL: 默认值，显示相关信息以及某些附加的显示选项，如分区和并发使用等。
3.SERIAL: 与TYPICAL类型相似，区别是它不包括并发的信息，即使是并行执行的计划。
4.ALL: 显示最多的信息，包含了TYPICAL的全部以及更多的附加信息,如别名和远程调用等
查看执行计划的内容，然后判断哪里有问题
PLAN_TABLE_OUTPUT
SQL_ID a2tdryafmbwch, child number 0
————————————-
DELETE FROM “JRWZ2_ZX”.”JS_ZX_JYDX_CJB” WHERE “BZ” = :b0 AND “SSSQ” =
:b1 AND “GF_NSRSBH” = :b2 AND “XF_NSRSBH” = :b3 AND “JE” = :b4 AND “SE”
= :b5 AND “LRSJ” = :b6 AND “NSRSBH” = :b7 AND ROWNUM = 1
Plan hash value: 54941260
——————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————–
| 0 | DELETE STATEMENT | | | | 23 (100)| |
| 1 | DELETE | JS_ZX_JYDX_CJB | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| JS_ZX_JYDX_CJB | 1 | 81 | 23 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_JS_ZX_JYDX_CJB_NSR | 445 | | 4 (0)| 00:00:01 |
——————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – filter(ROWNUM=1)
3 – filter((“GF_NSRSBH”=:B2 AND “XF_NSRSBH”=:B3 AND “SSSQ”=:B1 AND “BZ”=:B0 AND
“JE”=TO_NUMBER(:B4) AND “SE”=TO_NUMBER(:B5) AND “LRSJ”=:B6))
4 – access(“NSRSBH”=:B7)
查看这个表是否有索引，索引是否生效，，这个表的数据总量是多少，重复数据是多少，看下表结构是否有text，blob等内容，是否有高水位，碎片等问题，表是否有问题。
根据执行计划，来判断SQL语句的效率，看是否可以改善。
因为”JRWZ2_ZX”.”JS_ZX_JYDX_CJB”表NSRSBH是有建索引，但是有很多重复的数据。然后考虑建个复合索引，尽量确定是唯一的。
create index idx_JS_ZX_JYDX_CJB on JRWZ2_ZX.JS_ZX_JYDX_CJB(GF_NSRSBH,XF_NSRSBH);
然后SQL运行的时间很快就完成了。