在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时，得到的sql monitor报告如下：

Global Information: EXECUTING

Instance ID

:

1

Buffer Gets

IO Requests

Database Time

Wait Activity

.

40M

.

17M

.

8450s

.

100%

Session

:

PRODUSER(14:22343)

SQL ID

:

fkzafpjs28d6d

SQL Execution ID

:

16777216

Execution Started

:

07/17/2014 12:02:17

First Refresh Time

:

07/17/2014 12:02:21

Last Refresh Time

:

07/17/2014 16:51:01

Duration

:

17328s

Module/Action

:

xxxxxxx (TNS V1-V3)/-

Service

:

PRODB

Program

:

NextPricePl@XXXX(TNS V1-V3)

Fetch Calls

:

2671

--》对应的sql语句如下：
select document.period_key,
document.cycle_seq_no,
document.ba_no,
document.customer_no,
bill_statement.pay_channel_no
from document, --千万数据量 12671016 rows
cycle_control, --数据字典表，2118 rows
bill_statement, --千万数据量 12671016 rows
cyc_payer_pop --百万数据量 5400326 rows
where cycle_control.cycle_code = 2
and cycle_control.cycle_instance = 7
and cycle_control.cycle_year = 2014
and cyc_payer_pop.cycle_seq_no = cycle_control.cycle_seq_no
and cyc_payer_pop.db_status = 'BL'
and document.ba_no = cyc_payer_pop.ba_no
and document.cycle_seq_no = cyc_payer_pop.cycle_seq_no
and document.cycle_seq_run = cyc_payer_pop.cycle_seq_run
and document.period_key = cyc_payer_pop.period_key
and document.customer_key = cyc_payer_pop.customer_key
and document.doc_produce_ind in ('Y ', ' E ')
and document.document_status != ' N'
and bill_statement.ba_no = cyc_payer_pop.ba_no
and bill_statement.cycle_seq_no = document.cycle_seq_no
and bill_statement.cycle_seq_run = document.cycle_seq_run
and bill_statement.period_key = cyc_payer_pop.period_key
and bill_statement.customer_key = cyc_payer_pop.customer_key
and bill_statement.document_seq_no = document.doc_seq_no
可以通过执行计划看到，性能的瓶颈主要在两个地方，一个是做了全表扫描的部分表CYC_PAYER_POP，另外一个就是CPU资源的过度消耗，表DOCUMENT
SQL Plan Monitoring Details (Plan Hash Value=1606258714)

Id

Operation

Name

Estimated
Rows

Cost

Active Period
(17328s)

Execs

Rows

Memory

Temp

IO Requests

CPU Activity

Wait Activity

Progress

->

0

SELECT STATEMENT

.

1

270K

.

.12%

.

->

1

. NESTED LOOPS

.

1

270K

.

->

2

.. NESTED LOOPS

.

1

16500

.

1

270K

.

.12%

.

->

3

... NESTED LOOPS

.

23

16497

.

1

270K

.

->

4

.... NESTED LOOPS

.

56

16441

.

1

270K

.

5

..... TABLE ACCESS BY INDEX ROWID

CYCLE_CONTROL

1

.

1

.

->

6

...... INDEX UNIQUE SCAN

CYCLE_CONTROL_1UQ

1

.

1

.

->

7

.....PARTITION RANGE ALL

.

56

16440

.

1

270K

.

->

8

...... TABLE ACCESS FULL

CYC_PAYER_POP

56

16440

.

171

270K

.

4925 (<0.1%)

.12%

.07%

2859s

->

9

....PARTITION RANGE ITERATOR

.

1

.

270K

.

.35%

.

->

10

..... TABLE ACCESS BY LOCAL INDEX ROWID

DOCUMENT

1

.

270K

.

17M (98%)

.

96%

.

96%

.

->

11

...... INDEX RANGE SCAN

DOCUMENT_1IX

10

1

.

270K

2M

.

261K (1.5%)

.

1.5%

.

1.9%

.

->

12

...PARTITION RANGE ITERATOR

.

1

.

301K

270K

.

.47%

.

->

13

.... INDEX UNIQUE SCAN

BILL_STATEMENT_1IX

1

.

301K

270K

.

56807 (.3%)

.70%

.

1.3%

.

->

14

.. TABLE ACCESS BY LOCAL INDEX ROWID

BL1_BILL_STATEMENT

1

.

324K

270K

.

17599 (.1%)

.47%

.23%

.

可以看到CYC_PAYER_POP 做了全表扫描，估算出的数据条数是56条。
而绝大多数的IO都是在DOCUMENT,IO请求达1700万次，大约是128G的数据量，而且还不停的走索引做数据查取。
奇怪的是估算的数据返回量和实际执行的数据返回差距太大,返回结果大约有27万条。

->

8

...... TABLE ACCESS FULL

CYC_PAYER_POP

56

16440

.

171

270K

.

4925 (<0.1%)

但是第8步的数据得到。一次56条数据返回* 执行171次=9.5k 但是实际的返回结果得到了270K,差距实在太大。这是执行计划不正确导致的。

明确了上面一步，下面DOCUMENT表做了1700万次的io查询就可以理解了，这些都是连带的问题。

从千万的数据中得到27万的数据，还是很小的数据范围。
首先排查得到从数据字典表cycle_control中根据如下的条件，得到的数据只有一行。这和执行计划是一致的。
cycle_control.cycle_code = 2
and cycle_control.cycle_instance = 7
and cycle_control.cycle_year = 2014

因为document和bill_statement都是千万数据量的大表。所以在和它们两个表做数据关联的时候应该尽可能的过滤掉大部分数据。因为数据最终的返回只有27万，相对千万的数据还是很小的一部分。从百万的数据中走全表扫描还是不小的消耗，看看能不能从索引的角度入手。

索引信息如下：
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- ---------
CYC_PAYER_POP_1IX NORMAL NONUNIQUE YES CUSTOMER_NO TABLE N/A 5320775 16-JUL-14 N
CYC_PAYER_POP_2IX NORMAL NONUNIQUE YES CONFIRM_GROUP,CYCLE_SEQ_NO TABLE N/A 5642000 16-JUL-14 N
CYC_PAYER_POP_3IX NORMAL NONUNIQUE YES FORMAT_EXT_GROUP,CYCLE_SEQ_NO, TABLE N/A 5623545 16-JUL-14 N
DB_STATUS
CYC_PAYER_POP_4IX NORMAL NONUNIQUE YES GROUP_ID,CYCLE_SEQ_NO TABLE N/A 5142606 16-JUL-14 N
CYC_PAYER_POP_5IX NORMAL NONUNIQUE YES QA_GROUP,CYCLE_SEQ_NO TABLE N/A 5776258 16-JUL-14 N
CYC_PAYER_POP_PK NORMAL UNIQUE YES BA_NO,CYCLE_SEQ_NO,PERIOD_KEY, TABLE N/A 5368484 16-JUL-14 N

碰巧的是在数据的连接条件和输出列中，都是和主键相关的一些列。这样就可以考虑通过hint来启用索引了。当然启用索引也有一定的标准，在这个查询中。
通过索引和过滤条件查到的数据有不到30万，数据量是500多万，占到的数据比例不到10%，是可以考虑启用索引的。如果数据结果集较大，启用索引反而不利于数据的查询速度。

明确了这一点，我尝试把CYC_PAYER_POP的查询和数据字典表结合起来，过滤掉绝大部分数据。形成一个子查询。
在子查询中，启用了hint来强制查询按照计划的顺序和索引来执行。
(select /*+ leading (c p) index(p CYC_PAYER_POP_PK)*/
p.ba_no,p.cycle_seq_no,p.cycle_seq_run,p.period_key,p.customer_key
from cyc_payer_pop p, cycle_control c
where c.cycle_code = 2
and c.cycle_instance = 7
and c.cycle_year = 2014
and p.cycle_seq_no = c.cycle_seq_no
and p.db_status = 'BL' ) cyc_payer_pop

然后在这个基础上，再和两个大表做关联，

优化后的sql语句如下：
select /*+ leading( cyc_payer_pop bill_statement document)*/
document.period_key,
document.cycle_seq_no,
document.ba_no,
document.customer_no,
bill_statement.pay_channel_no
from document, --千万数据量 12671016 rows
bill_statement ,--千万数据量 12671016 rows
(select /*+ leading (c p) index(p CYC_PAYER_POP_PK)*/
p.ba_no,p.cycle_seq_no,p.cycle_seq_run,p.period_key,p.customer_key
from cyc_payer_pop p, cycle_control c
where c.cycle_code = 2
and c.cycle_instance = 7
and c.cycle_year = 2014
and p.cycle_seq_no = c.cycle_seq_no
and p.db_status = 'BL' ) cyc_payer_pop
where
and document.ba_no = cyc_payer_pop.ba_no
and document.cycle_seq_no = cyc_payer_pop.cycle_seq_no
and document.cycle_seq_run = cyc_payer_pop.cycle_seq_run
and document.period_key = cyc_payer_pop.period_key
and document.customer_key = cyc_payer_pop.customer_key
and document.doc_produce_ind in ('Y ', ' E ')
and document.document_status != ' N'
and bill_statement.ba_no = cyc_payer_pop.ba_no
and bill_statement.cycle_seq_no = document.cycle_seq_no
and bill_statement.cycle_seq_run = document.cycle_seq_run
and bill_statement.period_key = cyc_payer_pop.period_key
and bill_statement.customer_key = cyc_payer_pop.customer_key
and bill_statement.document_seq_no = document.doc_seq_no

优化后的执行计划如下。document表的io请求数从1700万次，降低到了将近8万次。解决了性能瓶颈。
SQL Plan Monitoring Details (Plan Hash Value=1573871804)

Id

Operation

Name

Estimated
Rows

Cost

Active Period
(247s)

Execs

Rows

Memory
(Max)

Temp
(Max)

IO Requests

CPU Activity

Wait Activity

.

0

SELECT STATEMENT

.

1

291K

.

1

. NESTED LOOPS

.

1

291K

.

2

.. NESTED LOOPS

.

1

5406

.

1

291K

.

3

... NESTED LOOPS

.

27

5403

.

1

291K

.

4

.... NESTED LOOPS

.

56

5347

.

1

291K

.

5

..... TABLE ACCESS BY INDEX ROWID

CYCLE_CONTROL

1

.

1

.

6

...... INDEX UNIQUE SCAN

CYCLE_CONTROL_1UQ

1

.

1

.

7

.....PARTITION RANGE ALL

.

56

5346

.

1

291K

.

8

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

CYC_PAYER_POP

56

5346

.

181

291K

.

10002 (8.3%)

.

20%

.

4.6%

.

9

.......INDEX FULL SCAN

CYC_PAYER_POP_PK

29672

2540

.

181

479K

.

4673 (3.9%)

.

6.1%

.

10

....PARTITION RANGE ITERATOR

.

1

.

291K

.

11

..... TABLE ACCESS BY LOCAL INDEX ROWID

DOCUMENT

1

.

291K

.

79597 (66%)

.

52%

.

68%

.

12

...... INDEX RANGE SCAN

DOCUMENT_1IX

10

1

.

291K

2M

.

3877 (3.2%)

.

8.0%

.

2.6%

.

13

...PARTITION RANGE ITERATOR

.

1

.

294K

291K

.

14

.... INDEX UNIQUE SCAN

BILL_STATEMENT_1IX

1

.

294K

291K

.

4574 (3.8%)

.

12%

.

3.6%

.

15

.. TABLE ACCESS BY LOCAL INDEX ROWID

BILL_STATEMENT

1

.

308K

291K

.

15323 (12%)

.

8.0%

.

14%

当然了关键的还是查取速度。
查询速度也从原本的5个小时降低到了4-5分钟。
在测试和生产环境中实际执行的速度和预期是一致的。
290896 rows selected.
Elapsed: 00:04:08.04

291001 rows selected.
Elapsed: 00:05:08.66

ITPub博客

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

最新文章