oracle_慢SQL的优化过程（awr报表）



怎么样判断系统运行慢，或者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 divided 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运行的时间很快就完成了。