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 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运行的时间很快就完成了。

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复