一次排序的调整与优化--下篇

SQL优化与问题解决

SQL问题找到了，那么接下来要分析一下，这个SQL应该如何调整：

SQL> select sql_text

2 from v$sqltext_with_newlines where hash_value='2784903626' order by piece;

SQL_TEXT

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

select PRODUCTMATERIALLISTITEMIDFrom orderedmateriallist where

bomid = :"SYS_B_0"

order by DisplayPriority

此处的ORDEREDMATERIALLIST是一个视图，其创建语句如下：

SQL> select dbms_metadata.get_ddl('VIEW','ORDEREDMATERIALLIST')VIEW_SQL from dual;

VIEW_SQL

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

CREATE OR REPLACE FORCEVIEW "MESW"."ORDEREDMATERIALLIST"

("BOMID", "PRODUCTMATERIALLISTITEMID","DISPLAYPRIORITY") AS

select distinct pli.BOMID, pli.PRODUCTMATERIALLISTITEMID,pt.DISPLAYPRIORITY

from PRODUCTMATERIALLISTITEMpli, PRODUCT p, PRODUCTBASE pb, PRODUCTTYPE pt

where ((pli.PRODUCTID= p.PRODUCTID and p.PRODUCTTYPEID = pt.PRODUCTTYPEID)

or (pli.PRODUCTBASEID = pb.PRODUCTBASEID andpb.REVOFRCDID = p.PRODUCTID and

p.PRODUCTTYPEID= pt.PRODUCTTYPEID))

再来确认一下执行计划的子指针信息：

SQL> SELECT child_number,plan_hash_value, object_status

2 FROMv$sql

3 WHEREhash_value = '2784903626'

4 ORDERBY plan_hash_value

5 /

CHILD_NUMBER PLAN_HASH_VALUEOBJECT_STATUS

------------ --------------- -------------------
0 0
3 0
7 0
10 0
8 0
4 0
11 0
13 0
12 0
1 62016752 INVALID_UNAUTH
2 62016752 INVALID_UNAUTH
5 62016752 INVALID_UNAUTH
9 62016752 VALID
6 62016752 INVALID_UNAUTH

14 rows selected.

我们注意到只有一个有效的执行计划，不存在多执行计划问题，这样调整起来就较为简单了，以下是该SQL被记录的执行计划：

SQL> SELECT operation,options, object_name

2 FROMv$sql_plan

3 WHEREhash_value = '2784903626'

4 ANDchild_number = '9'

5 /

OPERATION OPTIONS OBJECT_NAME
-------------------- -------------------- ------------------------------
SELECT STATEMENT
VIEW
SORT UNIQUE
CONCATENATION
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID PRODUCTMATERIALLISTITEM
INDEX RANGE SCAN PRODMATLLISTITEM1
TABLE ACCESS BY INDEX ROWID PRODUCTBASE
INDEX UNIQUE SCAN PRODUCTBASE310
TABLE ACCESS BY INDEX ROWID PRODUCT
INDEX UNIQUESCAN PRODUCT502
TABLE ACCESS BY INDEX ROWID PRODUCTTYPE
INDEX UNIQUE SCAN PRODUCTTYPE347
HASH JOIN
TABLE ACCESS FULL PRODUCTTYPE
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID PRODUCTMATERIALLISTITEM
INDEX RANGE SCAN PRODMATLLISTITEM1
TABLE ACCESS BY INDEX ROWID PRODUCT
INDEX UNIQUE SCAN PRODUCT502
TABLE ACCESS FULL PRODUCTBASE

24 rows selected.

我们用10046来进一步跟踪，看一下具体执行的过程：

SQL> alter sessionset events '10046 trace name context forever';

Session altered.

Elapsed: 00:00:00.04

SQL> SELECT productmateriallistitemid

2 FROM(SELECT DISTINCT pli.bomid bomid,

3 pli.productmateriallistitemid productmateriallistitemid,

4 pt.displaypriority displaypriority

5 FROM productmateriallistitem pli,

6 product p,

7 productbase pb,

8 producttype pt

9 WHERE ((pli.productid = p.productidAND

10 p.producttypeid = pt.producttypeid) OR

11 (pli.productbaseid = pb.productbaseid AND

12 pb.revofrcdid = p.productid AND

13 p.producttypeid = pt.producttypeid)))

14 WHEREbomid = '0018a68000093231'

15 ORDERBY displaypriority;

PRODUCTMATERIALL

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

0017c080000b5176

0017c080000b5177

0017c080000b5178

0017c080000b517b

0017c080000b517c

0017c080000b5172

0017c080000b517d

0017c080000b519d

0017c080000b519e

0017c080000b519f

0017c080000b5171

0017c080000b5173

0017c080000b5174

0017c080000b5175

0017c080000b5179

0017c080000b517a

16 rows selected.

Elapsed: 00:00:00.83

SQL> alter sessionset events '10046 trace name context off';

Session altered.

在跟踪文件中，找到更为详尽的执行信息：

SELECT productmateriallistitemid

FROM (SELECT DISTINCT pli.bomid bomid,

pli.productmateriallistitemid productmateriallistitemid,

pt.displaypriority displaypriority

FROM productmateriallistitem pli,

product p,

productbase pb,

producttype pt

WHERE ((pli.productid = p.productid AND

p.producttypeid= pt.producttypeid) OR

(pli.productbaseid = pb.productbaseid AND

pb.revofrcdid = p.productid AND

p.producttypeid= pt.producttypeid)))

WHERE bomid = :"SYS_B_0"

ORDER BY displaypriority

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 1 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.64 0.63 0 3148 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.64 0.65 1 3153 0 16

Misses in library cacheduring parse: 1

Optimizer goal: CHOOSE

Parsing user id: 24

Rows Row Source Operation
------- ---------------------------------------------------
16 VIEW
16 SORT UNIQUE
251072 CONCATENATION
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
16 TABLE ACCESS BY INDEX ROWIDPRODUCTMATERIALLISTITEM
16 INDEX RANGE SCAN PRODMATLLISTITEM1(object id 7800)
0 TABLE ACCESS BY INDEX ROWIDPRODUCTBASE
0 INDEX UNIQUE SCAN PRODUCTBASE310(object id 7790)
0 TABLE ACCESS BY INDEX ROWIDPRODUCT
0 INDEX UNIQUE SCAN PRODUCT502(object id 7788)
0 TABLE ACCESS BY INDEX ROWIDPRODUCTTYPE
0 INDEX UNIQUE SCAN PRODUCTTYPE347(object id 7804)
251072 HASH JOIN
30 TABLE ACCESS FULL PRODUCTTYPE
251072 NESTED LOOPS
16 NESTED LOOPS
16 TABLE ACCESS BY INDEX ROWID PRODUCTMATERIALLISTITEM
16 INDEX RANGE SCAN PRODMATLLISTITEM1(object id 7800)
16 TABLE ACCESS BY INDEX ROWIDPRODUCT
16 INDEX UNIQUE SCAN PRODUCT502(object id 7788)
251072 TABLE ACCESS FULL PRODUCTBASE

********************************************************************************

alter session set events '10046 trace namecontext off'

SQL共执行了0.65秒，其中CPU工作了0.64秒。这么高的CPU使用率主要用于最后一步sort unique，因为要对251 072行代码进行排序，通过DBMS_XPLAN获得Cursor的执行计划，注意观察其后输出的谓词信息：

SQL> select *from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

| Id |Operation | Name |Rows |Bytes |Cost |
----------------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 18 | 882 |2489 |
| 1 |VIEW | | 18 | 882 |2489 |
| 2 | SORT UNIQUE | | 18 | 2790 | 2489 |
| 3 | CONCATENATION | | | | |
| 4 | NESTED LOOPS | | 191K | 28M | 180 |
| 5 | NESTED LOOPS | | 12 | 1212 | 17 |
| 6 | NESTED LOOPS | |191K | 24M | 173 |
| 7 | TABLE ACCESS BY INDEX ROWID |PRODUCTMATERIALLISTITEM | 12 | 816 | 5 |
|* 8 | INDEX RANGE SCAN |PRODMATLLISTITEM1 | 12 | | 3 |
| 9 | TABLE ACCESS BY INDEX ROWID |PRODUCTBASE |15611 | 518K | 13 |
|* 10 | INDEX UNIQUE SCAN |PRODUCTBASE310 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID |PRODUCT | 1 | 33 | 1 |
|* 12 | INDEX UNIQUE SCAN |PRODUCT502 | 1 | | |
| 13 | TABLE ACCESS BY INDEX ROWID |PRODUCTTYPE | 30 | 600 | 5 |
|* 14 | INDEX UNIQUE SCAN |PRODUCTTYPE347 | 1 | | |
|* 15 | HASH JOIN | | 191K | 28M | 180 |
| 16 | TABLE ACCESS FULL |PRODUCTTYPE | 30 | 600 | 5 |
| 17 | NESTED LOOPS | | 191K | 24M | 173 |
| 18 | NESTED LOOPS | | 12 | 1212 | 17 |
| 19 | TABLE ACCESS BY INDEX ROWID |PRODUCTMATERIALLISTITEM | 12 | 816 | 5 |
|* 20 | INDEX RANGE SCAN |PRODMATLLISTITEM1 | 12 | | 3 |
| 21 | TABLE ACCESS BY INDEX ROWID |PRODUCT | 1 | 33 | 1 |
|* 22 | INDEX UNIQUE SCAN |PRODUCT502 | 1 | | |
|* 23 | TABLE ACCESS FULL |PRODUCTBASE |15611 | 518K | 13 |
----------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

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

8 - access("PLI"."BOMID"=:Z)

10 -access("PLI"."PRODUCTBASEID"="PB"."PRODUCTBASEID")

12 -access("PB"."REVOFRCDID"="P"."PRODUCTID")

14 -access("P"."PRODUCTTYPEID"="PT"."PRODUCTTYPEID")

15 -access("P"."PRODUCTTYPEID"="PT"."PRODUCTTYPEID")

20 -access("PLI"."BOMID"=:Z)

22 -access("PLI"."PRODUCTID"="P"."PRODUCTID")

23 -filter(LNNVL("PLI"."PRODUCTBASEID"="PB"."PRODUCTBASEID")OR

LNNVL("PB"."REVOFRCDID"="P"."PRODUCTID"))

Note: cpu costing is off

结合10046的跟踪输出及执行计划的谓词信息，就可以看到问题所在了。执行计划的第23步执行了一个过滤（LNNVL函数在9i中没有文档记录，请参考10gR2的Oracle Database SQL Reference）：

23 - filter(LNNVL("PLI"."PRODUCTBASEID"="PB"."PRODUCTBASEID")OR
LNNVL("PB"."REVOFRCDID"="P"."PRODUCTID"))

10046这个步骤的结果集由NestLoop产生并一直向上运算传递到顶层：

251072 HASHJOIN
30 TABLEACCESS FULL PRODUCTTYPE
251072 NESTEDLOOPS
16 NESTEDLOOPS
16 TABLEACCESS BY INDEX ROWID PRODUCTMATERIALLISTITEM
16 INDEXRANGE SCAN PRODMATLLISTITEM1 (object id 7800)
16 TABLEACCESS BY INDEX ROWID PRODUCT
16 INDEXUNIQUE SCAN PRODUCT502 (object id 7788)
251072 TABLEACCESS FULL PRODUCTBASE

这段执行计划用于实现的是如下谓词约束：

WHERE ((pli.productid = p.productid AND
p.producttypeid = pt.producttypeid)

前面SQL通过filter来过滤掉PRODUCTBASE的连接两个条件，PRODUCTBASE全表 15.6k行和前面的16行结果集作nested loops，就会出现16×15.6k=251k行的结果集，这么大的结果集排序所耗费的CPU会很高。

把查询用union改写：

SELECT productmateriallistitemid

FROM (SELECT DISTINCT pli.bomid,

pli.productmateriallistitemid,

pt.displaypriority

FROM productmateriallistitem pli,

product p,

--productbase pb,

producttype pt

WHERE ((pli.productid= p.productid AND

p.producttypeid= pt.producttypeid)

--OR

--(pli.productbaseid= pb.productbaseid AND pb.revofrcdid = p.productid AND p.producttypeid = pt.producttypeid)

)

UNION

SELECT DISTINCT pli.bomid,

pli.productmateriallistitemid,

pt.displaypriority

FROM productmateriallistitem pli,

product p,

productbase pb,

producttype pt

WHERE (

--(pli.productid = p.productid AND p.producttypeid = pt.producttypeid)

--OR

(pli.productbaseid= pb.productbaseid AND

pb.revofrcdid = p.productid AND

p.producttypeid = pt.producttypeid)))

WHERE bomid = :"SYS_B_0"

ORDER BY displaypriority

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.01 0.00 0 80 0 16

------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.02 0.00 0 80 0 16

Misses in library cacheduring parse: 1

Optimizer goal: CHOOSE

Parsing user id: 24

Rows Row SourceOperation

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

16 SORTORDER BY

16 VIEW

16 SORTUNIQUE

16 UNION-ALL

16 NESTED LOOPS

16 NESTED LOOPS

16 TABLE ACCESS BY INDEX ROWID PRODUCTMATERIALLISTITEM

16 INDEX RANGE SCAN PRODMATLLISTITEM1 (object id 7800)

16 TABLE ACCESS BY INDEX ROWID PRODUCT

16 INDEX UNIQUE SCAN PRODUCT502 (object id 7788)

16 TABLE ACCESS BY INDEX ROWID PRODUCTTYPE

16 INDEX UNIQUE SCAN PRODUCTTYPE347 (object id 7804)

0 NESTED LOOPS

0 NESTED LOOPS

0 NESTED LOOPS

16 TABLE ACCESS BY INDEX ROWID PRODUCTMATERIALLISTITEM

16 INDEX RANGE SCAN PRODMATLLISTITEM1 (object id 7800)

0 TABLE ACCESS BY INDEX ROWID PRODUCTBASE

0 INDEX UNIQUE SCAN PRODUCTBASE310 (object id 7790)

0 TABLE ACCESS BY INDEX ROWID PRODUCT

0 INDEX UNIQUE SCAN PRODUCT502 (object id 7788)

0 TABLE ACCESS BY INDEX ROWID PRODUCTTYPE

0 INDEX UNIQUE SCAN PRODUCTTYPE347 (object id 7804)

********************************************************************************

alter session set events '10046 trace namecontext off'

分割成两个SQL作union后避免了filter减少了须排序的集，同时也减少了逻辑读（其中的distinct可以从SQL中去掉，因为union本身就有distinct的作用，但是为了阅读方便，保留这个写法）。

文章来源：《OracleDBA手记1》第19章 一次排序的调整与优化 作者：段林仲

配图来源：http://rioit.co.uk/sites/default/files/remote-IT-support-figure_710x396.jpg

回复“手记1”可下载本书前三篇内容：DBA工作手记/诊断案例篇/SQL调优篇(建议复制链接至电脑端)