一个优化分析的例子

来源：互联网发布：大尺度美剧知乎编辑：程序博客网时间：2024/05/17 05:12

一.问题发现：在最近二期财务平台的每日statspack 报告中，几乎都是“****_历史数据转换” 程序占了很大的资源，逻辑读上去了，redo size 上去了，enqueue 和latch free 的等待事件也出来了，并且该程序总是在逻辑读top sql 中排名第一。从程序本身的运行来看，6月份平均运行时间有6分钟多，有时单个执行达到8，9分钟左右。如下是昨天statspack 报告的节选，可以看出 CGL_TP_HISTORY_CVRT 的语句占了整个数据库逻辑读的 74.3%！ Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 819,115,999 97 8,444,494.8 74.3 ######## 70409.36 575878228 Module: CGL_TP_HISTORY_CVRT BEGIN cgl_tp_history_cvrt_pkg.main( :errbuf, :rc,:A0,:A1,:A2,:A3 ); END; 423,078,651 61,726 6,854.1 38.4 ######## 17783.36 1590995392 Module: CGL_TP_HISTORY_CVRT SELECT T.GL_CO, T.GL_ACC, T.GL_DEPT, T.GL_PROD, T.GL_IC, T.GL_RE F, T.GL_BGT, T.GL_SPR2, T.GL_ATTRIBUTE1, T.GL_ATTRIBUTE2, T.GL_A TTRIBUTE3, T.GL_ATTRIBUTE4, T.GL_ATTRIBUTE5, T.GL_ATTRIBUTE6, T. GL_ATTRIBUTE7, T.GL_ATTRIBUTE8, T.GL_ATTRIBUTE9, T.GL_ATTRIBUTE1 0, T.GL_ATTRIBUTE11, T.GL_ATTRIBUTE12, T.GL_ATTRIBUTE13, T.GL_AT 361,262,268 96 3,763,148.6 32.8 ######## 32622.73 3431514996 Module: CGL_TP_HISTORY_CVRT UPDATE CGL.CGL_GL_YY_COA_MAPPING T SET T.GL_CCID = FND_FLEX_EXT. GET_CCID('SQLGL', 'GL#', :B3 , TO_CHAR(SYSDATE, :B2 ), T.GL_CO | | '.' || T.GL_ACC || '.' || T.GL_DEPT || '.' || T.GL_PROD || '.' || T.GL_IC || '.' || T.GL_REF || '.' || T.GL_BGT || '.' || T.GL _SPR2) WHERE T.YY_CO = :B1 AND T.ERROR_FLAG <> 'ERROR' 二. 问题分析上面列出出来的第二和第三条语句即是 cgl_tp_history_cvrt_pkg 包中的语句，他们占资源最高，自然就是问题语句。得到select 和update 两条语句的完整语句和执行计划，看到都是只与 CGL_GL_YY_COA_MAPPING 一张表相关。这个表有21万多，48m大小，可以看出从6月14日分析以来，到18日，短短4天时间，update 就有 132万多！相当于把整个表至少update了 6遍！ select 语句的问题在于，每次都是全表扫描，并且该语句在昨天执行了 61,726 多次，而 cgl_tp_history_cvrt_pkg.main 只执行了97 次，就是说，每次package 执行，该select 语句平均要执行630多次！ update 语句的问题在于：平均每次都有2200多行数据被update，从update 的条件来看,error_flag 字段似乎有“pass” ，“new” 和“error”三种值，并且“error” 的状态似乎只是中间过程，如果条件为ERROR_FLAG <> 'ERROR' ，恐怕会有过多的数据被包含了进来，会有重复update 的情况吧？另外，因为这个update 会更新较多的行，造成每次程序执行都会持有这些数据行的锁，当该程序并行执行时，会有严重的enqueue锁等待。数据库中大量的enqueue等待主要是因为这个原因造成的。 -- CGL_GL_YY_COA_MAPPING 表的信息 SQL> select count(*) from CGL_GL_YY_COA_MAPPING ; COUNT(*) ---------- 214257 SQL> select owner,segment_name,bytes/1024/1024 from dba_segments where segment_name='CGL_GL_YY_COA_MAPPING' ; OWNER SEGMENT_NAME BYTES/1024/1024 ------------------------------ -------------------------------------------------------------------------------- --------------- CGL CGL_GL_YY_COA_MAPPING 48 SQL> SQL> select table_name,inserts,updates,deletes,timestamp from sys.dba_tab_modifications where table_name ='CGL_GL_YY_COA_MAPPING'; TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP ------------------------------ ---------- ---------- ---------- ----------- CGL_GL_YY_COA_MAPPING 87363 1328690 1621 2008-6-18 1 SQL> select table_name,last_analyzed from dba_tables where table_name ='CGL_GL_YY_COA_MAPPING'; TABLE_NAME LAST_ANALYZED ------------------------------ ------------- CGL_GL_YY_COA_MAPPING 2008-6-14 3:1 SQL> SQL> select distinct error_flag from CGL_GL_YY_COA_MAPPING ; ERROR_FLAG ---------- NEW PASS SQL> SQL> select error_flag,count(*) from CGL_GL_YY_COA_MAPPING group by error_flag; ERROR_FLAG COUNT(*) ---------- ---------- NEW 12675 PASS 203114 SQL> --第一条select 语句及其执行计划 SELECT T.GL_CO, T.GL_ACC, T.GL_DEPT, T.GL_PROD, T.GL_IC, T.GL_REF, T.GL_BGT, T.GL_SPR2, T.GL_ATTRIBUTE1, T.GL_ATTRIBUTE2, T.GL_ATTRIBUTE3, T.GL_ATTRIBUTE4, T.GL_ATTRIBUTE5, T.GL_ATTRIBUTE6, T.GL_ATTRIBUTE7, T.GL_ATTRIBUTE8, T.GL_ATTRIBUTE9, T.GL_ATTRIBUTE10, T.GL_ATTRIBUTE11, T.GL_ATTRIBUTE12, T.GL_ATTRIBUTE13, T.GL_ATTRIBUTE14, T.GL_ATTRIBUTE15, T.GL_ATTRIBUTE16, T.GL_ATTRIBUTE17, T.GL_ATTRIBUTE18, T.GL_ATTRIBUTE19, T.GL_ATTRIBUTE20 FROM CGL.CGL_GL_YY_COA_MAPPING T WHERE 1 = 1 AND T.ERROR_FLAG = 'PASS' AND TRIM(T.YY_CO) = :B22 AND TRIM(T.YY_ACC) = :B21 AND NVL(TRIM(T.YY_DETAIL1), '-999999999') = NVL(:B20, '-999999999') AND NVL(TRIM(T.YY_DETAIL2), '-999999999') = NVL(:B19, '-999999999') AND NVL(TRIM(T.YY_DETAIL3), '-999999999') = NVL(:B18, '-999999999') AND NVL(TRIM(T.YY_DETAIL4), '-999999999') = NVL(:B17, '-999999999') AND NVL(TRIM(T.YY_DETAIL5), '-999999999') = NVL(:B16, '-999999999') AND NVL(TRIM(T.YY_DETAIL6), '-999999999') = NVL(:B15, '-999999999') AND NVL(TRIM(T.YY_DETAIL7), '-999999999') = NVL(:B14, '-999999999') AND NVL(TRIM(T.YY_DETAIL8), '-999999999') = NVL(:B13, '-999999999') AND NVL(TRIM(T.YY_DETAIL9), '-999999999') = NVL(:B12, '-999999999') AND NVL(TRIM(T.YY_DETAIL10), '-999999999') = NVL(:B11, '-999999999') AND NVL(TRIM(T.YY_DETAIL11), '-999999999') = NVL(:B10, '-999999999') AND NVL(TRIM(T.YY_DETAIL12), '-999999999') = NVL(:B9, '-999999999') AND NVL(TRIM(T.YY_DETAIL13), '-999999999') = NVL(:B8, '-999999999') AND NVL(TRIM(T.YY_DETAIL14), '-999999999') = NVL(:B7, '-999999999') AND NVL(TRIM(T.YY_DETAIL15), '-999999999') = NVL(:B6, '-999999999') AND NVL(TRIM(T.YY_DETAIL16), '-999999999') = NVL(:B5, '-999999999') AND NVL(TRIM(T.YY_DETAIL17), '-999999999') = NVL(:B4, '-999999999') AND NVL(TRIM(T.YY_DETAIL18), '-999999999') = NVL(:B3, '-999999999') AND NVL(TRIM(T.YY_DETAIL19), '-999999999') = NVL(:B2, '-999999999') AND NVL(TRIM(T.YY_DETAIL20), '-999999999') = NVL(:B1, '-999999999') SELECT STATEMENT, GOAL = CHOOSE Cost=586 Cardinality=1 Bytes=135 TABLE ACCESS FULL Object owner=CGL Object name=CGL_GL_YY_COA_MAPPING Cost=586 Cardinality=1 Bytes=135 SQL> select 61726/97 from dual; 61726/97 ---------- 636.350515 --第二条update 语句及其执行计划 UPDATE CGL.CGL_GL_YY_COA_MAPPING T SET T.GL_CCID = FND_FLEX_EXT.GET_CCID('SQLGL', 'GL#', :B3, TO_CHAR(SYSDATE, :B2), T.GL_CO || '.' || T.GL_ACC || '.' || T.GL_DEPT || '.' || T.GL_PROD || '.' || T.GL_IC || '.' || T.GL_REF || '.' || T.GL_BGT || '.' || T.GL_SPR2) WHERE T.YY_CO = :B1 AND T.ERROR_FLAG <> 'ERROR' UPDATE STATEMENT, GOAL = CHOOSE Cost=116 Cardinality=1066 Bytes=65026 UPDATE Object owner=CGL Object name=CGL_GL_YY_COA_MAPPING TABLE ACCESS BY INDEX ROWID Object owner=CGL Object name=CGL_GL_YY_COA_MAPPING Cost=116 Cardinality=1066 Bytes=65026 INDEX RANGE SCAN Object owner=CGL Object name=YY_CO_IDX Cost=10 Cardinality=2132 SQL> select sql_text,executions,rows_processed/executions from v$sql where hash_value='3431514996'; SQL_TEXT EXECUTIONS ROWS_PROCESSED/EXECUTIONS -------------------------------------------------------------------------------- ---------- ------------------------- UPDATE CGL.CGL_GL_YY_COA_MAPPING T SET T.GL_CCID = FND_FLEX_EXT.GET_CCID('SQLGL' 293 2265.90784982935 SQL> 三.解决方案： 1.改变select 语句的全表扫描状况：对于 select 语句，有用到如下的条件，但是如果在YY_CO 和YY_ACC 前面加上trim 的话，又没有建立函数索引，会导致已经在 YY_CO 上的索引不能被使用。我们可以看到在那个update 语句中，有个条件是T.YY_CO = :B1 ，这里却没有使用trim 。那么，是否trim 就是没有必要的呢？即使有trim 的情况，是否可以先将处理处理好，然后再来直接使用呢？另外，从这个表的索引结构看，建立和很多的单列索引。其实是非常建议建立复合索引的，这样可以将多个选择性不高的字段组合成一个高效，选择性较高的索引。就单以上面select 和update 的语句看，是否可以在 YY_CO,YY_ACC,ERROR_FLAG 上建立一个复合索引呢？ AND TRIM(T.YY_CO) = :B22 AND TRIM(T.YY_ACC) = :B21 --表的索引结构 SQL> select index_name,column_name,column_position from dba_ind_columns where table_name ='CGL_GL_YY_COA_MAPPING'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------------------------------------------------------------------- --------------- YY_CO_IDX YY_CO 1 ACC_IDX GL_ACC 1 ACC_PRJ_IDX GL_ACC 1 ACC_PRJ_IDX GL_ATTRIBUTE20 2 BGT_IDX GL_BGT 1 CO_IDX GL_CO 1 DEPT_IDX GL_DEPT 1 IC_IDX GL_IC 1 PROD_IDX GL_PROD 1 REF_IDX GL_REF 1 SPR2_IDX GL_SPR2 1 11 rows selected SQL> 2. 尽量减少select 语句的过多执行一次程序执行，select 语句就要被执行600多次，请问这个是否可以控制？是否可以尽量来减少呢？ 3.update 语句条件细化是否这个update 语句的条件写的有疏漏？ error_flag <> 'ERROR' 可是包括了 PASS 和new 的状态。个人感觉这里是否应该是“new” ？另外，是否可以添加其他的条件来细化update 的条件呢？另外就是对于并行执行是如何考虑？是否要限制？