AC10_TEMP AC11 LC30 AD05 DY_GRJFQK AC36_IN KC23 AC05 IC20 IC10_BACK 5 5 5 6 6 6 6 6 6 6 AC05_APPEND KC24 AB13 AB01_DSHD DSHD_AB01_YIL DSHD_AB01_YAL DSHD_AB01 DSHD_AB01_SY AB01 AC01 9 10 10 11 11 11 11 11 14 17 请根据索引监控结果对索引进行修改。同时，一般一个表不建议操作5个索引，请根据情况调整索引定义方式。
第7章SQL 性能问题
7.1OLTP和OLAP业务混合使用同库问题
由于目前系统是典型的OLTP，OLAP混合的运行的方式，所以，短事务和大量的中心端查询、统计业务对数据库的参数配置和执行计划选择很难能通过固定的参数及方式给出。
7.2业务人员操作管理问题
在生产库上部分消耗性能的SQL语句在执行，经过和开发人员沟通发现基本是上都是由于业务人员自己编写的大的统计查询，这样极大的消耗了系统的资源，对正常的业务具有很大的影响。Sql如下：
执行次数 1 1 1 IO次数 5788700 2800926 2789360 SQL 语句 select * from kc69 where nvl(aac193,'0')<>'0' and akc021 <>'44' and akc021 like '4%' and (akc197 is null or akc197='0') select * from kc69 where nvl(aac193,'0')<>'0' and akc021 not in ('3A','3B','3C','3D') and akc021 like '3%' and (akc197 is null or akc197='0') select * from kc69 where nvl(aac193,'0')<>'0' and akc021 <>'44' and akc021 like '4%' and (akc197 is null or akc197='0') 21
杭州社保系统分析方案 -- Created on 2002-7-26 by ADMINISTRATOR declare Cursor cur_kc24 ( prm_month Varchar2) Is Select sum( Nvl(akc265,0 ) ) akc265 , sum( Nvl(zkc024,0) + Nvl(zkc025,0) + Nvl(akc260,0) + Nvl(ckc039,0) + Nvl(ckc602,0) + Nvl(ckc706,0) + Nvl(ckc625,0) + Nvl(ckc626,0) + Nvl(ckc627,0) + Nvl(bkc265,0) + Nvl(ckc941,0) + Nvl(ckc942,0) + Nvl(ckc943,0) + Nvl(ckc944,0) + Nvl(ckc952,0) + Nvl(ckc953,0) + Nvl(ckc954,0) + nvl(ckc955,0) + nvl(ckc956,0) + nvl(ckc957,0) + nvl(ckc958,0) + nvl(ckc959,0) + nvl(ckc960,0 ) + nvl(ckc965,0) + nvl(ckc966,0) + nvl(ckc967,0) + nvl(ckc968,0) + nvl(ckc972,0) ) apply, akc190,akb020,SUM(akc264) akc264 From kc24 Where /*ckc131 < to_char(add_months(to_date(prm_month,'yyyymm'),1),'yyyymm')||'000000000000000' and ckc131 >= prm_month||'000000000000000'*/ bkc270 = prm_month --AND akb020='3001' --AND akb020 IN ('5024') -- AND substr(aka130,1,1)='3' -- AND akb020='2010' 1 438946 7.3消耗CPU的SQL
次数 542810 507766 逻辑读/次 1574.48645 1667.116552 DELETE FROM TEMP_GRJFQK1 DELETE FROM TEMP_GRJFQK INSERT INTO TEMP_GRJFQK (AAC001, AAE002, AAE003, AAE140, AAE143, AAB001, AAA060, JFJS, AAC123, BAA082, AAC008, AAA041, AAE114, BAC202, AAE00A, BAB221, AAE061, BAA063, BAA065, AAE068, BAC121) SELECT T.AAC001, T.AAE002, T.AAE003, T.AAE140, T.AAE143, T.AAB001, T.AAA060, (CASE WHEN (AA07.BAA087 = '1') OR (AA07.BAA087 = '2' AND NOT EXISTS (SELECT 1 FROM TEMP_GRJFQK1 T1, AA07 AA07_T WHERE T1.AAE143 = T.AAE143 AND T1.AAC001 = T.AAC001 AND T1.AAE002 = T.AAE002 AND T1.AAE003 = T.AAE003 AND T1.AAE140 <= SUBSTR(T.AAE140, 1, 1) || '9' AND T1.AAE140 >= SUBSTR(T.AAE140, 1, 1) || '1' AND T1.AAA060 = AA07_T.AAA060 AND NVL(AA07_T.BAA087, 0) = '1' AND NVL(T1.AAE068, '0') = '0' AND ROWNUM = 1)) THEN (DECODE(AAE149, '0', NVL(T.JFJS, 0), '1', NVL(T.JFJS, 0), '2', -NVL(T.JFJS, 0), '3', NVL(T.JFJS, 0), '4', NVL(T.JFJS, 0), '5', NVL(T.JFJS, 0), 0)) ELSE 0 END) AS BAC121, T.AAC123, T.BAA082, T.AAC008, T.AAA041, T.AAE114, T.BAC202, NVL(T.AAE00A, T.AAE002), T.BAB221, T.AAE061, T.BAA063, AA07.BAA065, T.AAE068, T INSERT INTO TEMP_GRJFQK (AAC001, AAE002, AAE003, AAE140, AAE143, AAB001, AAA060, JFJS, AAC123, BAA082, AAC008, AAA041, AAE114, BAC202, AAE00A, BAB221, AAE061, BAA063, BAA065, AAE068, BAC121) SELECT T.AAC001, T.AAE002, T.AAE003, T.AAE140, T.AAE143, T.AAB001, T.AAA060, (CASE WHEN (AA07.BAA087 = '1') OR (AA07.BAA087 = '2' AND NOT EXISTS (SELECT 1 FROM TEMP_GRJFQK1 T1, AA07 AA07_T WHERE T1.AAE143 = T.AAE143 AND T1.AAC001 = T.AAC001 AND T1.AAE002 = T.AAE002 AND T1.AAE003 = T.AAE003 AND T1.AAE140 <= SUBSTR(T.AAE140, 1, 1) || '9' AND T1.AAE140 >= SUBSTR(T.AAE140, 1, 1) || '1' AND T1.AAA060 = AA07_T.AAA060 AND NVL(AA07_T.BAA087, 0) = '1' AND ROWNUM = 1)) THEN (DECODE(AAE149, '0', NVL(T.JFJS, 0), '1', NVL(T.JFJS, 0), '2', -NVL(T.JFJS, 0), '3', NVL(T.JFJS, 0), '4', NVL(T.JFJS, 0), '5', NVL(T.JFJS, 0), 0)) ELSE 0 END) AS BAC121, T.AAC123, T.BAA082, T.AAC008, T.AAA041, T.AAE114, T.BAC202, NVL(T.AAE00A, T.AAE002), T.BAB221, T.AAE061, T.BAA063, AA07.BAA065, T.AAE068, T.JFJS FROM TEMP_GRJFQK1 T, AA0 SQL_TEXT 354027 2116.953792 1927 287129.1541 7699620 34.73422052 INSERT INTO TEMP_GRJFQK1 (AAC001, AAE002, AAE003, AAE140, AAE143, AAB001, JFJS, AAA060, AAC123, BAA082, AAC008, AAA041, AAE114, BAC202, AAE00A, BAB221, AAE061, BAA063, BAA065, AAE068 ) VALUES (:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 ) 251991 815.1856654 UPDATE \= :B3 WHERE \22
INSERT INTO TEMP_GRJFQK ( AAC001, AAE002, AAE003, AAE140, AAE143, AAB001, AAA060, JFJS, AAC123, BAA082, AAC008, AAA041, AAE114, BAC202, AAE00A, BAB221, AAE061, BAA063, BAA065,AAE068,BAC121 ) SELECT T.AAC001, T.AAE002, T.AAE003, T.AAE140, T.AAE143, T.AAB001, T.AAA060, (CASE WHEN (AA07.BAA087 = '1') OR (AA07.BAA087 = '2' AND NOT EXISTS( SELECT 1 FROM TEMP_GRJFQK1 T1 ,AA07 AA07_T WHERE T1.AAE143 = T.AAE143 AND T1.AAC001 = T.AAC001 AND T1.AAE002 = T.AAE002 AND T1.AAE003 = T.AAE003 AND T1.AAE140 <= SUBSTR(T.AAE140,1,1)||'9' AND T1.AAE140 >= SUBSTR(T.AAE140,1,1)||'1' AND T1.AAA060 = AA07_T.AAA060 AND NVL(AA07_T.BAA087,0) = '1' AND ROWNUM = 1 )) THEN (DECODE( AAE149,'0', NVL(T.JFJS,0), '1', NVL(T.JFJS,0), '2',-NVL(T.JFJS,0), '3', NVL(T.JFJS,0), '4', NVL(T.JFJS,0), '5', NVL(T.JFJS,0), 0)) ELSE 0 END ) AS BAC121, T.AAC123, T.BAA082, T.AAC008, T.AAA041, T.AAE114, T.BAC202, NVL(T.AAE00A,T.AAE002), T.BAB221, T.AAE061, T.BAA063, AA07.BAA065,T.AAE068,T.JFJS FROM TEMP_GRJFQK1 T,AA07,AA18 WHERE T.AAA SELECT DISTINCT AA10.* FROM AA10,AA02 WHERE AAA100 = 'BAA061' AND AA10.AAA102 = AA02.BAA061 INSERT INTO TEMP_GRJFQK ( AAC001, AAE002, AAE003, AAE140, AAE143, AAB001, AAA060, JFJS, AAC123, BAA082, AAC008, AAA041, AAE114, BAC202, AAE00A, BAB221, AAE061, BAA063, BAA065,AAE068,BAC121 ) SELECT T.AAC001, T.AAE002, T.AAE003, T.AAE140, T.AAE143, T.AAB001, T.AAA060, (CASE WHEN (AA07.BAA087 = '1') OR (AA07.BAA087 = '2' AND NOT EXISTS( SELECT 1 FROM TEMP_GRJFQK1 T1 ,AA07 AA07_T WHERE T1.BAB221 = T.BAB221 AND T1.AAE143 = T.AAE143 AND T1.AAC001 = T.AAC001 AND T1.AAE002 = T.AAE002 AND T1.AAE003 = T.AAE003 AND T1.AAE140 <= SUBSTR(T.AAE140,1,1)||'9' AND T1.AAE140 >= SUBSTR(T.AAE140,1,1)||'1' AND T1.AAA060 = AA07_T.AAA060 AND NVL(AA07_T.BAA087,0) = '1' AND ROWNUM = 1 )) THEN (DECODE( AAE149,'0', NVL(T.JFJS,0), '1', NVL(T.JFJS,0), '2',-NVL(T.JFJS,0), '3', NVL(T.JFJS,0), '4', NVL(T.JFJS,0), 0)) ELSE 0 END ) AS BAC121, T.AAC123, T.BAA082, T.AAC008, T.AAA041, T.AAE114, T.BAC202,T.AAE002, T.BAB221, T.AAE061, T.BAA063, AA07.BAA065,T.AAE068,T.JFJS FROM TEMP_GRJFQK1 T,AA07,AA18 WHERE T.AAA060 = AA07 DELETE FROM WNAMELIST_TEMP WHERE AAC001=:B1 DELETE FROM TEMP_GRJFQK2 WHERE AAC001 = SUBSTR(:B1 , 14, 8) SELECT \ \ FROM \ WHERE \ ORDER BY TO_NUMBER ( \ INSERT INTO IC02 (AAC001, AAE001, AAB001, AIC040, AIC041, AIC042, AIC043, AIC044, AIC045, AIC058, AIC059, AIC072, AIC073, AIC074, AIC075, AIC076, AIC077, AIC078, AIC079, AIC080, AIC081, AAE120, BAC001, BAC002) VALUES (:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 ) 71684 2642.455262 567724 309.0065401 36182 3886.586286 24711 3536.625673 70336 1285902 71948 1021.00155 54.009923 546.6426169
特别是对临时表的删除操作，建议修改为truncate等。
7.4物理读过高的SQL
READS_PER_EXEC SQL_TEXT 17772.87313 BEGIN PKG_I_ICBC.prc_I_IC08(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26)3510.621381 SELECT /*+ index(kc24,INDEX_KC24_BKC270_2) */ AKB020, AKC190, AAE072, CKC131, CKC130, NVL(AKCFROM KC24 WHERE AKB020 = :B3 AND SUBSTR(AKA130, 1, 1) = :B2 AND BKC270 = :B1 AND (AKC197 =AKC197 IS NULL) AND AKA130 NOT IN ('14','15','16','17','12','24','34') AND SUBSTR(AKC021, 1, 1) NOT IN ('3AND NVL(AKC026, '0') = '0' AND NVL(CKC945, '0') = '0' AND NVL(CKC969, '0') = '0' 226926.75 SELECT AKB020 FROM KB01 WHERE AKB022 = :B1 AND EXISTS ( SELECindex(AUDIT_LIST,INDEX_INDEX_KC69_AKB020) */ 1 FROM \WAUDIT_LIST.HOSPITAL_NUMBER = KB01.AKB020 AND SUBSTR(TREAT_TYPE, 1, 1) = :B3AUDIT_LIST.MONTH = :B2 AND SUBSTR(AUDIT_LIST.AKC021, 1, 1) NOT IN ('3', '4', '7') AND NVL(AKC026, AND NVL(CKC945, '0') = '0' AND NVL(CKC969, '0') = '0' AND TREAT_TYPE NOT IN ('12','24','34') AND ROWNORDER BY AKB020 23
杭州社保系统分析方案 176722.6667 SELECT DISTINCT \ \ S(\ TREAT_TYPE, COUNT ( distinct decode(audit_li\ ) PERSONTIMES_YC, COUNT ( distinct decode(audit_li\ ) PERSONTIMES_BQ, COUNT ( \) PERSONTIMES, TO( to_date(audit_list.month,'yyyy-mm'),'yyyy-mm' ) YMN, NVL (sum( decode(audit_li\ SUM_FEE_YC, NVL (sum( decode(audit_li\ SUM_FEE_BQ, SUM (\ SUNVL (sum( decode(audit_list.bz,'1', \ APPLYING_SUM_YC , (sum( decode(audit_list.bz,'0', \ APPLYING_SUM_BQ , (\ APPLY 224016.5 SELECT SUM( NVL(AKC265,0 ) ) AKC265 , SUM( NVL(ZKC024,0) + NVL(ZKC025,0) + NVL(AKC2NVL(CKC039,0) + NVL(CKC602,0) + NVL(CKC706,0) + NVL(CKC625,0) + NVL(CKC626,0) + NVL(CKC6NVL(BKC265,0) + NVL(CKC941,0) + NVL(CKC942,0) + NVL(CKC943,0) + NVL(CKC944,0) + NVL(CKC9NVL(CKC953,0) + NVL(CKC954,0) + NVL(CKC955,0) + NVL(CKC956,0) + NVL(CKC957,0) + NVL(CKC9NVL(CKC959,0) + NVL(CKC960,0 ) + NVL(CKC965,0) + NVL(CKC966,0) + NVL(CKC967,0) + NVL(CKC9NVL(CKC972,0) ) APPLY, AKC190,AKB020,SUM(AKC264) AKC264 FROM KC24 WHERE BKC270 = :B1 AND AIS NULL GROUP BY AKB020,AKC190 1425.459677 SELECT /*+ index(AUDIT_LIST,INDEX_INDEX_KC69_AKB020) */ COUNT(DI\= :B2 AND SUBSTR(TREAT_TYPE, 1, 1) = '1' AND AUDIT_LIST.MONTH = :B1 AND AUDIT_LIST.AUDIT_STATAND SUBSTR(AUDIT_LIST.AKC021, 1, 1) NOT IN ('3', '4', '7') AND NVL(AKC026, '0') = '0' AND NVL(CKC945, AND NVL(CKC969, '0') = '0' AND TREAT_TYPE NOT IN ('14','15','16','17','12','24','34') GROU\SUBSTR(\1, TO_CHAR(TO_DATE(AUDIT_LIST.MONTH, 'yyyy-mm'), 'yyyy-mm') 2258.881818 SELECT /*+ index(AUDIT_LIST,INDEX_INDEX_KC69_AKB020) */ COUNT(DI\= :B2 AND SUBSTR(TREAT_TYPE, 1, 1) = '3' AND AUDIT_LIST.MONTH = :B1 AND AUDIT_LIST.AUDIT_STATAND SUBSTR(AUDIT_LIST.AKC021, 1, 1) NOT IN ('3', '4', '7') AND NVL(AKC026, '0') = '0' AND NVL(CKC945, AND NVL(CKC969, '0') = '0' AND TREAT_TYPE NOT IN ('12','24','34') GROUP\SUBSTR(\1, TO_CHAR(TO_DATE(AUDIT_LIST.MONTH, 'yyyy-mm'), 'yyyy-mm') 4615.6 UPDATE IC08 SET AIC101 = '00', AIC102 = :B2 WHERE AIC080 = 1 AND AAE074 = TO_NUMBER(:B1 ) 2087.681319 SELECT /*+ index(AUDIT_LIST,INDEX_INDEX_KC69_AKB020) */ COUNT(DI\= :B2 AND SUBSTR(TREAT_TYPE, 1, 1) = '2' AND AUDIT_LIST.MONTH = :B1 AND AUDIT_LIST.AUDIT_STATAND SUBSTR(AUDIT_LIST.AKC021, 1, 1) NOT IN ('3', '4', '7') AND NVL(AKC026, '0') = '0' AND NVL(CKC945, AND NVL(CKC969, '0') = '0' AND TREAT_TYPE NOT IN ('12','24','34') GROUP\SUBSTR(\1, TO_CHAR(TO_DATE(AUDIT_LIST.MONTH, 'yyyy-mm'), 'yyyy-mm') 7.5消耗临时表空间的SQL
见《消耗临时表空间的SQL.xls》
24
第8章碎片情况
8.1表碎片情况
OWNER HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS HZSIMIS TABLE_NAME IC15 IC14 IB01 IC10_BACK IC16 IC06_BACK IC18 IC13 IC11 IC45 IB02 SEGMENT_TYPE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE WASTE_PER 43.1 45.53 10.05 51.23 69.65 16.78 72.13 62.28 41.89 24.64 38.32 TABLE_KB 6922240 7204864 434176 704512 696320 212992 581632 335872 155648 73728 81920 NUM_ROWS 19362597 19359414 1605765 1095827 2343165 1761069 1792991 2882268 1230169 235248 255451 TABLESPACE_NAME USERS_IC01 USERS_IC01 USERS_I USERS_I USERS_I USERS_I USERS_I USERS_I USERS_I USERS_I USERS_I 红色的代表，标的碎片的百分比。所以，在进行index range及full talbe scan的时候将会极大的消耗系统的IO。
8.2表空间碎片情况
TABLESPACE_NAME FSFI INDX_A INDX_AC13 INDX_F INDX_I INDX_IC01 INDX_K INDX_KCXX INDX_YYBXMID QTBX TABSPC0801 UNDOTBS1 USERS_A USERS_AC13 USERS_F USERS_I USERS_IC01 USERS_K USERS_KCXX 14.27205569 3.932369961 3.469299477 15.12735438 9.343441894 27.89798344 12.2174073 8.897092445 16.63206583 34.35922428 6.785796961 20.34915646 35.63182887 46.2172061 28.76095978 29.1048451 39.78112361 21.08236893 25