生产环境有一条sql语句执行比较频繁，占用了大量的cpu资源。原本执行需要花费11秒。在一次排查中引起了我的注意，决定看看cpu消耗到底在哪儿？
sql语句是比较简单的，通过查询SUBSCRIBER_FA_V是一个视图。在视图中关联了几个和业务核心表。

SELECT TO_CHAR(SUBSCRIBER_NO) SUBSCRIBER_ID,
SUB_STATUS,
SUB_STS_RSN_CD,
TO_CHAR(SUB_STATUS_DATE, 'yyyyMMdd') SUB_STATUS_DATE,
SUBSCRIBER_TYPE
FROM SUBSCRIBER_FA_V
WHERE BAN = :1
AND ROWNUM <= :2
AND SUB_STATUS NOT IN ('C', 'L', 'T')
ORDER BY INIT_ACT_DATE, SUBSCRIBER_NO

如果想做sql检查，对于sql中传入的变量，sql monitor提供了很方便的功能。
Binds

Name	Position	Type	Value
:B2	1	NUMBER	10308170
:B1	2	NUMBER	6

很清晰看到正在执行的sql语句对应的变量值。

还是来看看执行计划。

SQL Plan Monitoring Details (Plan Hash Value=1125972187)

Id

Operation

Name

Estimated
Rows

Cost

Active Period
(11s)

Execs

Rows

Memory
(Max)

Temp
(Max)

IO Requests

CPU Activity

Wait Activity

.

0

SELECT STATEMENT

.

1

.

1

. SORT ORDER BY

.

6

67758

.

1

2.0KB

.

2

.. COUNT STOPKEY

.

1

.

3

... VIEW

SUBSCRIBER_FA_V

577K

59898

.

1

.

4

.... SORT UNIQUE STOPKEY

.

577K

59898

.

1

2.0KB

.

5

..... UNION-ALL

.

1

.

6

......NESTED LOOPS

.

1

.

7

.......NESTED LOOPS

.

1

3

.

1

.

8

........NESTED LOOPS

.

1

2

.

1

.

9

.........INDEX RANGE SCAN

CHANNEL_1IX

1

.

1

.

10

.........TABLE ACCESS BY INDEX ROWID

DISTRIBUTE

1

.

1

.

11

..........INDEX RANGE SCAN

DISTRIBUTE_3IX

1

.

1

.

12

........INDEX UNIQUE SCAN

SUBSCRIBER_PK

1

.

1

.

13

....... TABLE ACCESS BY INDEX ROWID

SUBSCRIBER

1

.

1

.

14

...... VIEW

.

577K

35591

.

1

0

.

20%

.

15

.......WINDOW SORT PUSHED RANK

.

577K

35591

.

1

522K

21.2MB

63.0MB

.

627 (38%)

.

40%

.

33%

.

16

........ HASH JOIN

.

577K

23809

.

1

550K

23.4MB

88.0MB

.

390 (23%)

.

66%

.

17

.........INDEX FULL SCAN

CHANNEL_1IX

1M

1269

.

1

1M

.

18

.........HASH JOIN

.

577K

18648

.

1

550K

30.7MB

40.0MB

.

53 (3.2%)

.

20%

.

19

..........TABLE ACCESS FULL

SUBSCRIBER

430K

8704

.

1

424K

.

20%

.

20

..........TABLE ACCESS FULL

DISTRIBUTE

2M

6046

.

1

2M

.

性能瓶颈都在几个全表扫描和一个分析函数相关的rank操作中。毕竟返回的记录数只有1条。如果过多的资源都消耗在一些不正确的连接访问中，是完全可以避免的。

视图的内容如下：
SELECT xxxxxxx
1 RANK
FROM subscriber, distribute ed, channel cpc
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND ed.expiration_date IS NULL
AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
UNION
SELECT xxxxxxx
FROM (SELECT cpc.ban,
subscriber.prim_resource_val,
subscriber.init_act_date,
SUBSCRIBER.CUSTOMER_ID,
subscriber.subscriber_no,
SUBSCRIBER.SUBSCRIBER_TYPE,
SUBSCRIBER.SUB_STATUS,
SUBSCRIBER.SUB_STS_RSN_CD,
SUBSCRIBER.SUB_STATUS_DATE,
SUBSCRIBER.EFFECTIVE_DATE,
ROW_NUMBER ()
OVER (
PARTITION BY subscriber.subscriber_no
ORDER BY
ed.expiration_date, subscriber.subscriber_no DESC)
AS RANK
FROM subscriber, distribute ed, channel cpc
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T'))
WHERE RANK = 1

根据执行计划，出问题的正式标黄的union子句。
根据传入的参数，是可以走索引的，但是在Union子句中，嵌入了子查询，导致在整个视图在数据的访问中，先全表扫描整个子查询，然后再匹配传入的参数。
视图的内容是不能随便改的，可能在这种场景中合适，其他的相关查询就有问题了。
所以尝试把视图的内容直接转换成直接的sql,标黄的部分是做的相应改动。

select xxxxxxx
from (SELECT xxxxxxx
1 RANK
FROM subscriber, event_distribute ed, csm_pay_channel cpc
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND ed.expiration_date IS NULL
AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
and cpc.ban = 10308170
UNION
SELECT "SUBSCRIBER_NO",
"INIT_ACT_DATE",
"SUB_STATUS",
"SUB_STS_RSN_CD",
"SUB_STATUS_DATE",
"SUBSCRIBER_TYPE",
"RANK"
FROM (SELECT subscriber.subscriber_no,
SUBSCRIBER.INIT_ACT_DATE,
SUBSCRIBER.SUBSCRIBER_TYPE,
SUBSCRIBER.SUB_STATUS,
SUBSCRIBER.SUB_STS_RSN_CD,
SUBSCRIBER.SUB_STATUS_DATE,
ROW_NUMBER() OVER(PARTITION BY subscriber.subscriber_no ORDER BY ed.expiration_date, subscriber.subscriber_no DESC) AS RANK
FROM subscriber, event_distribute ed, csm_pay_channel cpc
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T')
and cpc.ban = 10308170)
WHERE RANK = 1)
where SUB_STATUS NOT IN ('C','L','T') and rownum < 6
ORDER BY INIT_ACT_DATE, SUBSCRIBER_ID

看似语句挺长的，但是走了索引执行效率还是很高的。

Elapsed: 00:00:00.01

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

22 consistent gets

0 physical reads

0 redo size

857 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

1 rows processed

ITPub博客

生产环境sql语句调优实战第三篇

最新文章