SQL的性能调优.docVIP

问题发现 一份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语句。 TOP SQL SQL ordered by Elapsed Time SQL ordered by CPU Time SQL ordered by Gets 问题发现 对一个OLTP系统来说，每一个语句的执行，都是要将其消耗的资源降到最低，这跟 OLAP系统是有差别的。对于后者来说，它需要的是短的时间返回结果，不管中间你会拿多大的成本做代价。 从上面反映的问题来看，我们的性能，无疑就是葬送在了SQL_ID为4841ajtgh43qy、 1hwqh7kvxn6yg、g295mubwupf52和anyty5rts5tzf这四个语句上面，下面将对这四个SQL语句一 一做出分析，并给出相应的调优建议。 SQL_ID为4841ajtgh43qy的语句 调整前 语句 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 ; 解释计划 又见全表扫 统计信息 每次执行，buffer gets居然达37190.80之多。 数值分布 原因分析 从上面的情况分析来看，原因已经非常明显了，也就是在列cust_id上面，没有索引，导致 每次执行该语句时，进行的都是全表扫描操作，这种操作对于一个600万的大表，在一个OLTP系统中，是很致命的。 调整 创建索引，并收集统计信息 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); 语句 与原语句一致 调整后 解释计划 统计信息 平均执行一次SQL的一致性读从37,190.80降低到5个，这个优化的效果是相当明显的。 SQL_ID为1hwqh7kvxn6yg的语句 调整前 语句 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_IT