一次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 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,