一次TOP_SQL的性能调优经历

1.问题发现

1.1一份AWR报告

今天，收到一份100个并发用户访问下压力测试的AWR报告，并发事务数平均每秒只有6个不到。

在这个26.53分钟间隔的报告里，CPU TIME在整个TOP 事件中最突出

占了近97.6%，在8个CPU系统中，数据库给CPU造成的压力为：

(5740/(26.53*60*8)*100%=45.07%，这么小的压力下，CPU就能冲得这么高，说明

系统中肯定是有问题的。下面转向TOP SQL去确认下造成资源争用最明显的SQL语句。

1.2TOP SQL

1.2.1SQL ordered by Elapsed Time

1.2.2SQL ordered by CPU Time

1.2.3SQL ordered by Gets

1.3问题发现

对一个OLTP系统来说，每一个语句的执行，都是要将其消耗的资源降到最低，这跟 OLAP系统是有差别的。对于后者来说，它需要的是短的时间返回结果，不管中间你会拿多大的成本做代价。

从上面反映的问题来看，我们的性能，无疑就是葬送在了SQL_ID为4841ajtgh43qy、1hwqh7kvxn6yg、g295mubwupf52和anyty5rts5tzf这四个语句上面，下面将对这四个SQL语句一 一做出分析，并给出相应的调优建议。

2.SQL_ID为4841ajtgh43qy的语句 2.1调整前

2.1.1语句

SELECT *

FROM (SELECT TT.*, ROWNUM AS ROWNO

FROM (select to_char(c.cust_id), c.password

from CUST_CERTIFICATION c

where c.cust_id = :CUST_ID) TT

WHERE ROWNUM <= 10) TABLE_ALIAS where TABLE_ALIAS.rowno > 0

;

2.1.2解释计划

又见全表扫

2.1.3统计信息

每次执行，buffer gets居然达37190.80之多。

2.1.4数值分布

2.2原因分析

从上面的情况分析来看，原因已经非常明显了，也就是在列cust_id上面，没有索引，导致

每次执行该语句时，进行的都是全表扫描操作，这种操作对于一个600万的大表，在一个OLTP 系统中，是很致命的。

2.3调整

2.3.1创建索引，并收集统计信息

create index crm551.IDX_CUST_CERTIFICATION on

crm551.CUST_CERTIFICATION(cust_id);

execute dbms_stats.gather_table_stats(ownname => 'CRM551',tabname => 'CUST_CERTIFICATION',cascade => true);

2.3.2语句

与原语句一致

2.4调整后

2.4.1解释计划

2.4.2统计信息

平均执行一次SQL的一致性读从37,190.80降低到5个，这个优化的效果是相当明显的。

3.SQL_ID为1hwqh7kvxn6yg的语句

3.1调整前

3.1.1语句

SELECT COUNT(*) PAGE_SIZE

FROM (select *

from (SELECT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

(SELECT ACC_NBR

FROM PROD_INST_551 C, ORDER_ITEM_REL E, ORDER_ITEM F

WHERE E.Z_ORDER_ITEM_ID = F.ORDER_ITEM_ID

AND E.A_ORDER_ITEM_ID = A.ORDER_ITEM_ID

AND F.ORDER_ITEM_OBJ_ID = C.PROD_INST_ID

AND F.ORDER_ITEM_CD IN ('13')

AND ROWNUM < 2) ACC_NBR,

TO_CHAR(A.HANDLE_TIME, 'yyyy-mm-dd hh24:mi:ss') ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND ROWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID = A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A, CUSTOMER_ORDER B,

PROD_OFFER_INST_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID = D.PROD_OFFER_INST_ID

AND B.CUST_ID = :CUST_ID

AND https://www.360docs.net/doc/7b15969833.html,TN_ID = :LATN_ID

AND A.MAIN_FLAG = '1'

AND A.PRE_HANDLE_FLAG <> '1'

AND A.CUST_ORDER_ID = :CUST_ORDER_ID

UNION

SELECT DISTINCT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

D.CUST_NUMBER,

TO_CHAR(A.HANDLE_TIME,

'yyyy-mm-dd hh24:mi:ss') ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND R OWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID =

A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A, CUSTOMER_ORDER B, CUST_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID = D.CUST_ID

AND B.CUST_ID = :CUST_ID2

AND https://www.360docs.net/doc/7b15969833.html,TN_ID = :LATN_ID2

AND A.MAIN_FLAG = '1'

AND A.PRE_HANDLE_FLAG <> '1'

AND A.CUST_ORDER_ID = :CUST_ORDER_ID2

UNION

SELECT DISTINCT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

D.ACCOUNT_NUMBER,

TO_CHAR(A.HANDLE_TIME,

'yyyy-mm-dd hh24:mi:ss')

ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND ROWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID =

A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A, CUSTOMER_ORDER B, ACCOUNT_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID = D.ACCOUNT_ID

AND B.CUST_ID = :CUST_ID3

AND https://www.360docs.net/doc/7b15969833.html,TN_ID = :LATN_ID3

AND A.MAIN_FLAG = '1'

AND A.PRE_HANDLE_FLAG <> '1'

AND A.CUST_ORDER_ID = :CUST_ORDER_ID3) t

order by t.ACCEPT_DATE desc) T

3.1.2解释计划

3.1.3统计信息

每次执行，buffer gets居然达20218.44之多。

3.1.4数值分布

3.2原因分析

这个语句看起来很吓人，其实没那么可怕。

碰到这种长语句，我们第一步要做的，就是把这个语句拆分，确认性能瓶颈出在哪个模块。

第一步:我们通过union分隔进行拆分，三个子句的绑定变量分别替换成常量值，分别执行 第二步：对比其统计信息发现，第二、三部分统计信息中的一致性读加起来只有30来个，其余的都是第一部分占用的，因此，我们可以锁定UNION子句的第一部分就是“元凶”。

第三步：再次拆分第一部分语句的，最后发现是SQL语句中的黑粗体部分导致大量的一致性读。查看这部分的解释计划：

果然，这部分一方面ORDER_ITEM_REL表做了全表扫描，然后做HASH JOIN的驱动表也不对。因为只有ORDER_ITEM_REL才与外面的驱动表有关联条件AND E.A_ORDER_ITEM_ID = A.ORDER_ITEM_ID，因此要做HASH JOIN或NESTED LOOP，

驱动表都应该是它而不应该是ORDER_ITEM。根据上节中提到的数据分布情况，这个表的A_ORDER_ITEM_ID字段是具备走索引条件的，因此，可用ORDER_ITEM_REL表做驱动表，走NESTED LOOP应该是最优的。同时，order_item表走的索引idx_order_item_code对应的字段为ORDER_ITEM_CD，因此条件F. ORDER_ITEM_CD=’13’走了索引，但是关联列上并没有用上索引。表order_item上的关联列order_item_id是个唯一性很强的字段（详情见上一节的数值分布部分），在其上建的是bitmap索引，在OLTP系统中用BITMAP 索引，是大忌。需要将索引重建为B-Tree索引，否则这两个列进行关联时，索引根本就用不上。

在OLTP系统中，在语句级别加提示强制走某种执行计划，在遇到倾斜性很大的列时，常常带来很大的性能问题。这是个测试环境，所以这儿就通过提示来改变语句的执行计划，建议非DBA的同学们尽量在开发环境中不要用提示来强化执行计划。

3.3调整

3.3.1创建索引，并收集统计信息

----新建索引，以支撑关联

create unique index crm20_dba.idxz_ORDER_ITEM_REL on

ORDER_ITEM_REL(Z_ORDER_ITEM_ID);

create index crm20_dba.idxa_ORDER_ITEM_REL on

ORDER_ITEM_REL(A_ORDER_ITEM_ID);

---将bitmap索引调整为普通索引

drop index IDX_ORDER_ITEM_STATUS;

drop index IDX_ORDER_ITEM_ID;

create index IDX_ORDER_ITEM_STATUS on ORDER_ITEM(STATUS_CD);

create index IDX_ORDER_ITEM_ID on ORDER_ITEM(ORDER_ITEM_ID);

execute dbms_stats.gather_table_stats(ownname => 'CRM20_DBA',tabname => 'ORDER_ITEM_REL',cascade => true);

3.3.2语句（使用leading和use_nl指导做nested loop并调整驱动表）

SELECT COUNT(*) PAGE_SIZE

FROM (select *

from (SELECT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

(SELECT /*+ leading(E F C) use_nl(E F) */ ACC_NBR

FROM PROD_INST_551 C, ORDER_ITEM_REL E, ORDER_ITEM F

WHERE E.Z_ORDER_ITEM_ID = F.ORDER_ITEM_ID

AND E.A_ORDER_ITEM_ID = A.ORDER_ITEM_ID

AND F.ORDER_ITEM_OBJ_ID = C.PROD_INST_ID

AND F.ORDER_ITEM_CD IN ('13')

AND ROWNUM < 2) ACC_NBR,

TO_CHAR(A.HANDLE_TIME, 'yyyy-mm-dd hh24:mi:ss') ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND ROWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID = A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A, CUSTOMER_ORDER B,

PROD_OFFER_INST_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID = D.PROD_OFFER_INST_ID

AND B.CUST_ID = :CUST_ID

AND https://www.360docs.net/doc/7b15969833.html,TN_ID = :LATN_ID

AND A.MAIN_FLAG = '1'

AND A.PRE_HANDLE_FLAG <> '1'

AND A.CUST_ORDER_ID = :CUST_ORDER_ID

UNION

SELECT DISTINCT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

D.CUST_NUMBER,

TO_CHAR(A.HANDLE_TIME,

'yyyy-mm-dd hh24:mi:ss') ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND ROWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID =

A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A, CUSTOMER_ORDER B, CUST_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID = D.CUST_ID

AND B.CUST_ID = :CUST_ID2

AND https://www.360docs.net/doc/7b15969833.html,TN_ID = :LATN_ID2

AND A.MAIN_FLAG = '1'

AND A.PRE_HANDLE_FLAG <> '1'

AND A.CUST_ORDER_ID = :CUST_ORDER_ID2

UNION

SELECT DISTINCT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

D.ACCOUNT_NUMBER,

TO_CHAR(A.HANDLE_TIME,

'yyyy-mm-dd hh24:mi:ss')

ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND ROWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID =

A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A, CUSTOMER_ORDER B, ACCOUNT_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID = D.ACCOUNT_ID

AND B.CUST_ID = :CUST_ID3

AND https://www.360docs.net/doc/7b15969833.html,TN_ID = :LATN_ID3

AND A.MAIN_FLAG = '1'

AND A.PRE_HANDLE_FLAG <> '1'

AND A.CUST_ORDER_ID = :CUST_ORDER_ID3) t order by t.ACCEPT_DATE desc) T

3.4调整后

3.4.1解释计划（第一个子句）

现在来看，order_item_id字段上的索引已经用上，这对走嵌套至关重要。

3.4.2统计信息

看下统计信息，现在一次执行一致性读只有65了，这个提升简直太让人振奋了

4.SQL_ID为g295mubwupf52语句

此语句与语句1hwqh7kvxn6yg中的同出一撤，这里不做过多分析，在索引已经建好，分析已经完成的情况下，只需要用提示强制一下路径就OK了。因此这个分析过程略过。

4.1原语句

SELECT *

FROM (SELECT TT.*, ROWNUM AS ROWNO

FROM (select *

from (SELECT A.CUST_ORDER_ID,

A.ORDER_ITEM_ID,

A.OFFER_ID,

(SELECT PROD_OFFER_NAME

FROM PROD_OFFER

WHERE PROD_OFFER_ID = A.OFFER_ID

AND ROWNUM = 1) OFFER_NAME,

(SELECT ACC_NBR

FROM PROD_INST_551 C,

ORDER_ITEM_REL E,

ORDER_ITEM F

WHERE E.Z_ORDER_ITEM_ID =

F.ORDER_ITEM_ID

AND E.A_ORDER_ITEM_ID =

A.ORDER_ITEM_ID

AND F.ORDER_ITEM_OBJ_ID =

C.PROD_INST_ID

AND F.ORDER_ITEM_CD IN ('13')

AND ROWNUM < 2) ACC_NBR,

TO_CHAR(A.HANDLE_TIME, 'yyyy-mm-dd hh24:mi:ss') ACCEPT_DATE,

A.STATUS_CD,

(SELECT CODE_NAME

FROM MAP_CODE

WHERE CODE = A.STATUS_CD

AND TABLE_NAME = 'ORDER_ITEM'

AND CODE_TYPE = 'SOS'

AND ROWNUM = 1) STATUS_NAME,

A.SERVICE_OFFER_ID,

(SELECT SERVICE_OFFER_NAME

FROM SERVICE_OFFER

WHERE SERVICE_OFFER_ID =

A.SERVICE_OFFER_ID) SERVICE_OFFER_NAME

FROM ORDER_ITEM A,

CUSTOMER_ORDER B,

PROD_OFFER_INST_551 D

WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID

AND A.ORDER_ITEM_OBJ_ID =

D.PROD_OFFER_INST_ID

AND B.CUST_ID = :CUST_ID