Oracle數(shù)據(jù)庫系統(tǒng)性能分析評價與優(yōu)化

1、Oracle數(shù)據(jù)庫性能分析評估與優(yōu)化實施方案2 性能分析評估CONTENTS 日力 TOC o 1-5 h z HYPERLINK l bookmark4 o Current Document 第一章性能分析評估1索引部份 1表空間碎片 1效率低SQL語句 1 HYPERLINK l bookmark10 o Current Document 第二章實施方案2實施時間 2實施影響和風(fēng)險評估 2實施需求 2實施規(guī)劃 2實施步驟 10第一章 1第一章 性能分析評估維護程師通過對 Oracle數(shù)據(jù)庫巡檢，以對應(yīng)用系統(tǒng)和數(shù)據(jù)庫狀況了解、相關(guān)信息的收集。分析 評估數(shù)據(jù)庫性能瓶頸：.用戶等待I/O;.隨著

2、應(yīng)用系統(tǒng)的數(shù)據(jù)量不斷增大，是否未對一些常用業(yè)務(wù)表和索引進行空間回縮；.應(yīng)用系統(tǒng)層面，如 SQL語句效率、索引的合理性、部份數(shù)據(jù)結(jié)構(gòu)設(shè)計的合理性。性能信息收集：通過ORACLED具RDA (REMOTE DIAGNOSTIC AGEN硬行系統(tǒng)信息收集通過ORACLED具STATSPACK (9i)或AWR(10g或以上)進行數(shù)據(jù)庫性能資料的收集 ，操作 系統(tǒng)工具和命令檢查操作系統(tǒng)。SQL命令檢查數(shù)據(jù)庫配置， SQL命令在 ORACLET具SQL*PLU汕運行。粘貼重點的性能問題信息收集的結(jié)果，及加入分析評估建議 索引部份檢查數(shù)據(jù)表的索引建立情況。表空間碎片檢查數(shù)據(jù)表空間的碎片。效率低SQL語句檢

3、查應(yīng)用程序效率低的 SQL語句。實施方案第二章實施方案本方案實施主要有三點，如下：建立三個索引表空間，將所有業(yè)務(wù)表索引遷移分散在該三個表空間中。通過AWR性能報告，結(jié)合業(yè)務(wù)應(yīng)用系統(tǒng)特點，找出頻繁增刪改、數(shù)據(jù)量大等表碎片較多的 業(yè)務(wù)表，對其進行空間回縮。找出全表掃描導(dǎo)致性能差的 SQL語句實施時間由于本方案的實施對業(yè)務(wù)系統(tǒng)影響較大，建議停止數(shù)據(jù)庫對外服務(wù)，安排在業(yè)務(wù)空閑期如周末 進行。實施周期預(yù)計 1-2天。實施影響和風(fēng)險評估需要停業(yè)務(wù)系統(tǒng)和數(shù)據(jù)庫需要停止業(yè)務(wù)系統(tǒng)和數(shù)據(jù)庫對外服務(wù)，實施前需對數(shù)據(jù)庫進行全備。對業(yè)務(wù)系統(tǒng)的影響由于實施是對大量的索引文件的遷移、重建，在實施完成后，對業(yè)務(wù)系統(tǒng)的某些功能

4、或數(shù)據(jù)庫 對象可能會造成失效，需要與應(yīng)用系統(tǒng)開發(fā)商、xx相關(guān)人員配合，共同檢查，完成將失效的功能或?qū)ο笾匦戮幾g、生效。實施需求需要xx公司對應(yīng)用系統(tǒng)和數(shù)據(jù)庫比較了解的相關(guān)人員配合進行，另外，由于本方案的實施涉及應(yīng)用系統(tǒng)數(shù)據(jù)結(jié)構(gòu)，如索引、業(yè)務(wù)表等，因此，需要應(yīng)用系統(tǒng)開發(fā)商安排對應(yīng)用系統(tǒng)數(shù)據(jù)結(jié)構(gòu)較了 解的人員配合。以保證本方案實施的進度，另外將風(fēng)險降到最低。實施規(guī)劃表索引遷移規(guī)劃遷移重建后的新表空間原表空間索引名備注第二章 3EAS_D_PANCNEW_STANDAFtbs_index01UX_ORG_UNITRELtbs _index02IX_ORG_UNITRELTYPEtbs _index0

5、3PK_ORG_UNITRELAtbs _index01IX_ORG_UNITRELTO、Dbs _index02IX_ORG_UNITRELFROM上述僅列出eas_d_pancnew_standAQ里的部份遷移對象，全部對象用語句可查出，詳見具體實施步維艮JEAS_D_PANCNEW_TEMP3分散遷移到 Gdhd_index01, Gdhd_index02, Gdhd_index03 中該表空間中所有表所有索引HDDA_DATA同上同上EAS_D_HDCW_STANDARD同上同上EAS_D_PANC_STANDARD同上同上EAS_D_HDCW_TEMP3同上同上EAS_D_PANCT

6、EST_TEMP3同上同上EAS_D_TEST1_STANDARD同上同上EAS_D_PANCPMT_STANDAR加上同上EAS_D_PANCTEST_STANDAF回上同上回收表空閑空間規(guī)劃（以下表可根據(jù)業(yè)務(wù)特點先期挑選部份作回縮空間， 作下標(biāo)記）序號用戶名表名表空間名表真實大?。∕B）表估算大?。∕B）比率1PANCNEWT_LOGPANCNEW_STANDARD271658.5646.382PANCNEW27.193.747.283PANCNEW19.313.2264PANCNEW28.695.35.415HDCW5511.64.746PANCNEW40.949.84.187PANCN

7、EW16.194.413.678PANCNEW9.812.963.319PANCNEW12.563.913.2210PANCNEW10.193.822.6711PANCNEW83.282.4412PANCNEW16.58.25213PANCNEW8.564.451.9214PANCNEW10.635.681.8715PANCNEW6.063.371.816HDCW84.561.7617PANCNEW9.885.851.6918PANCNEW2414.321.6819PANCNEW6.884.331.5920PANCNEW26.1316.691.5721PANCNEW199.06127.581.

8、5622PANCNEW6.133.991.5323HDCW53.261.5324PANCNEW230.19150.561.5325PANCNEW5.633.711.5126PANCNEW53.321.5127PANCNEW4.813.231.4928PANCNEW2041.251372.231.494實施方案29PANCNEW53.391.4830HDCW53.391.4731PANCNEW4.813.271.4732PANCNEW5.313.641.4633PANCNEW53.431.4634PANCNEW53.451.4535PANCNEW53.461.4536PANCNEW6.944.8

9、11.4437PANCNEW72.6350.371.4438HDDA106.991.4339PANCNEW4.813.371.4340PANCNEW5.063.551.4341PANCNEW7.55.291.4242PANCNEW85.651.4243PANCNEW8.255.841.4144PANCNEW2114.871.4145PANCNEW4.943.51.4146PANCWZ1611.341.4147PANCNEW7.255.161.4148HDDA85.691.4149PANCNEW72.0651.291.4150PANCNEW64.271.451PANCNEW1913.531.45

10、2PANCNEW6.944.961.453PANCNEW85.731.454HDCW64.31.455PANCNEW85.751.3956PANCNEW85.781.3857PANCWZ42.891.3858HDDA3021.681.3859PANCNEW42.891.3860PANCNEW85.791.3861PANCNEW5.694.121.3862HDDA96.521.3863PANCNEW53.621.3864PANCNEW9.757.071.3865PANCWZ75.081.3866PANCNEW85.811.3867PANCNEW85.811.3868PANCNEW85.811.3

11、869PANCNEW85.811.3870PANCNEW85.811.3871PANCNEW85.811.3872PANCNEW85.811.3873PANCNEW28.3120.581.3874PANCNEW85.821.3875PANCNEW85.821.3876PANCNEW85.821.3877PANCNEW90.2565.641.37第二章 578PANCNEW5.443.961.3779PANCNEW5.313.871.3780PANCNEW85.831.3781PANCNEW85.831.3782PANCNEW85.831.3783PANCNEW85.831.3784PANCNE

12、W85.841.3785PANCNEW119.6387.261.3786PANCNEW75.111.3787PANCNEW90.4466.121.3788PANCNEW1475.691078.91.3789PANCNEW85.861.3790PANCNEW85.881.3691PANCNEW64.411.3692HDCW75.151.3693PANCNEW85.91.3694PANCNEW85.91.3695PANCNEW85.91.3596PANCNEW85.911.3597PANCNEW64.431.3598PANCNEW8.566.331.3599PANCNEW9.947.351.351

13、00PANCNEW85.921.35101PANCNEW85.921.35102PANCNEW85.921.35103PANCNEW85.921.35104PANCNEW85.921.35105PANCNEW85.921.35106PANCNEW85.931.35107PANCNEW85.931.35108PANCNEW85.931.35109PANCNEW85.931.35110PANCNEW85.931.35111PANCNEW85.931.35112PANCNEW85.931.35113PANCNEW85.931.35114PANCNEW85.931.35115PANCNEW85.931

14、.35116PANCNEW85.931.35117PANCNEW85.931.35118PANCNEW85.931.35119PANCNEW85.931.35120PANCNEW604.88448.41.35121PANCNEW128.91.35122PANCNEW85.931.35123PANCNEW85.931.35124PANCNEW85.931.35125PANCNEW85.931.35126PANCNEW85.931.356實施方案127PANCNEW85.931.35128PANCNEW85.931.35129PANCNEW85.931.35130PANCNEW85.931.351

15、31PANCNEW85.931.35132PANCNEW85.931.35133PANCNEW85.931.35134PANCNEW85.931.35135PANCNEW85.931.35136PANCNEW85.931.35137PANCNEW85.931.35138PANCNEW85.931.35139PANCNEW85.931.35140PANCNEW85.931.35141PANCNEW85.931.35142PANCNEW25.9419.241.35143HDCW85.951.35144PANCNEW64.481.34145PANCNEW47.1335.211.34146PANCNE

16、W53.741.34147PANCNEW31.3823.461.34148PANCNEW22.8817.111.34149PANCNEW64.51.33150PANCNEW33.525.171.33151PANCNEW24.8118.661.33152PANCNEW80.1960.341.33153PANCNEW14.510.921.33154PANCNEW64.521.33155PANCNEW43.021.33156PANCNEW6.314.771.32157PANCNEW54.1340.881.32158PANCNEW4.883.691.32159PANCNEW488.94370.481.

17、32160PANCNEW5.884.451.32161PANCNEW11.949.061.32162PANCNEW129.111.32163HDDA6448.641.32164PANCNEW49.5637.681.32165PANCNEW96.841.31166PANCNEW3.812.91.31167PANCNEW17.6313.441.31168PANCNEW39.8830.411.31169PANCNEW88.1967.291.31170PANCNEW129.161.31171PANCNEW3.882.961.31172PANCNEW4.633.541.31173PANCNEW107.6

18、51.31174PANCNEW75.361.31175PANCNEW21.3116.341.3第二章 7176PANCNEW18.6314.291.3177PANCNEW10.067.721.3178PANCNEW53.841.3179PANCNEW53.841.3180PANCNEW53.841.3181PANCNEW53.841.3182HDCW2216.91.3183PANCNEW11.258.641.3184PANCNEW37.3828.761.3185PANCNEW43.081.3186PANCNEW16.8112.961.3187HDCW7255.551.3188HDCW43.09

19、1.29189PANCNEW3.752.911.29190PANCNEW5.254.081.29191PANCNEW6.55.061.29192PANCNEW229178.191.29193PANCNEW3728.81.28194PANCNEW12.59.731.28195PANCNEW17.2513.461.28196PANCNEW16.4412.821.28197PANCNEW17.8813.941.28198PANCNEW10.698.341.28199HDCW9574.151.28200PANCNEW5.194.051.28201PANCNEW9.137.131.28202PANCNE

20、W8.316.51.28203PANCNEW62.2548.71.28204PANCNEW20.7516.231.28205PANCNEW7.816.111.28206PANCNEW4.633.621.28207PANCNEW3.883.041.28208PANCNEW16.4412.91.27209PANCNEW51.1940.161.27210PANCNEW4.253.341.27211PANCNEW1612.561.27212PANCNEW16.56131.27213HDCW8869.151.27214PANCNEW91.2571.81.27215PANCNEW552.94435.731

21、.27216PANCNEW33.8826.71.27217PANCNEW7256.81.27218PANCNEW26.520.921.27219PANCNEW186.25147.191.27220PANCNEW7.385.841.26221PANCNEW18.8114.91.26222PANCNEW46.4436.781.26223PANCNEW156.31123.881.26224PANCNEW8.56.741.268實施方案225PANCNEW6.55.161.26226PANCNEW27.1921.581.26227PANCNEW97.151.26228PANCNEW1310.331.2

22、6229PANCNEW80.8164.291.26230PANCNEW9.067.211.26231PANCNEW457.06364.541.25232PANCNEW2721.541.25233PANCNEW9.817.831.25234PANCNEW14.8811.871.25235PANCNEW288230.081.25236PANCNEW1092.69873.11.25237PANCNEW9.637.691.25238PANCNEW6.815.451.25239PANCNEW7.315.851.25240PANCNEW139.75111.851.25241PANCNEW541.25242

23、PANCNEW366293.421.25243PANCNEW3931.311.25244PANCNEW129.44103.941.25245PANCNEW6.194.971.24246PANCNEW4.53.621.24247PANCNEW13.4410.81.24248PANCNEW54.021.24249PANCNEW54.021.24250PANCNEW54.021.24251PANCNEW18.6915.031.24252PANCNEW9.697.791.24253HDCW129.651.24254PANCNEW54.021.24255PANCNEW54.021.24256PANCNE

24、W22.6918.261.24257PANCNEW7.566.091.24258PANCNEW46.1937.21.24259PANCNEW5.754.631.24260PANCNEW88.5671.441.24261PANCNEW118.881.24262PANCNEW129.19104.281.24263PANCNEW16641344.021.24264PANCNEW24.7519.991.24265HDCW2116.971.24266PANCNEW25.3120.451.24267PANCNEW16.3813.241.24268PANCNEW17.514.151.24269PANCNEW

25、4.133.341.24270PANCNEW2419.461.23271PANCNEW200162.291.23272PANCNEW8568.971.23273PANCNEW97.31.23第二章 9274PANCNEW6250.341.23275PANCNEW84.568.621.23276PANCNEW97.579.221.23277PANCNEW4.633.761.23278PANCNEW28.1322.861.23279PANCNEW1411.381.23280PANCNEW31.3825.51.23281PANCNEW43.6335.471.23282HDCW1613.011.232

26、83PANCNEW22.6918.461.23284PANCNEW22.3118.161.23285PANCNEW28.4423.161.23286PANCNEW6.385.191.23287PANCNEW47.5638.761.23288PANCNEW544.634441.23289PANCNEW14.8812.131.23290PANCNEW4032.651.23291PANCNEW97.351.22292PANCNEW10.258.381.22293PANCNEW72.559.411.22294PANCNEW10.198.351.22295PANCNEW86.561.22296PANCN

27、EW64.921.22297PANCNEW42.0634.521.22298PANCNEW49.7540.831.22299PANCNEW119.031.22300PANCNEW60.4449.631.22301PANCNEW148.38121.891.22302PANCNEW6.885.651.22303PANCNEW46.538.291.21304PANCNEW590486.111.21305PANCNEW2318.961.21306PANCNEW6150.371.21307PANCNEW14.3811.871.21308PANCNEW43.311.21309PANCNEW441364.6

28、51.21310PANCNEW1344.561112.021.21311PANCNEW14.1311.691.21312PANCNEW2218.221.21313PANCNEW167.88139.11.21314PANCNEW57.2547.441.21315PANCNEW497.38412.191.21316PANCNEW7.886.541.2317PANCNEW17.7514.751.2318HDDA64.991.2319PANCNEW262.19217.951.2320PANCNEW96.3880.191.2321PANCNEW46.6338.81.2322PANCNEW148.8812

29、3.941.210實施方案323PANCNEW14.6912.231.2324PANCNEW30.2525.231.2325PANCNEW117.1997.881.2326PANCNEW1310.861.2327PANCNEW5142.621.2328PANCNEW139.06116.271.2329PANCNEW13501128.951.2330PANCNEW9.57.941.2331PANCNEW332.38278.041.2332PANCNEW4301.693600.211.19333PANCWZ16031343.461.19334PANCNEW34.6329.031.19335PANC

30、NEW13.8811.641.19336PANCNEW1915.981.19337PANCNEW663.81558.361.19338PANCNEW1639.811384.651.18339PANCNEW38.1932.291.18340PANCNEW40873456.871.18341PANCNEW3624.133068.621.18342PANCNEW11.319.591.18343PANCNEW84.0671.331.18344PANCNEW505.13428.651.18345PANCNEW1446.941232.31.17346PANCNEW28.0623.991.17347PANC

31、NEW4416.0637881.17348PANCNEW2017.191.16349PANCNEW4337.141.16350HDCW3732.131.15351PANCNEW184.44160.321.15352PANCNEW52.1945.441.15353PANCNEW72616324.11.15354PANCNEW319278.91.14355PANCNEW14.8112.981.14356PANCNEW6.946.111.13357PANCNEW6.445.711.13358PANCNEW5.565.081.09359PANCNEW166.5152.221.09360HDCW43.6

32、71.09361HDCW54.741.05362HDCW7270.911.02實施步驟數(shù)據(jù)庫全備關(guān)閉數(shù)據(jù)庫，對整個數(shù)據(jù)庫進行全備份。第二章 11索引記錄備份.用以下語句查找所有業(yè)務(wù)表索引Selecta.owner,a.index_name,a.index_type,a.table_name,a.uniqueness,a.tablespace_name, b.column_namefrom dba_indexes a,dba_ind_columns bwhere a.index_name=b.index_nameand tablespace_namenotin （SYSTEM, SYSAUX,

33、TEMP, UNDOTBS1； USERS）order by a.owner,a.index_name,b.column_name,table_name;.將第1步生成的所有業(yè)務(wù)表索引另存文檔（如 excel或文件格式），保存。以便在索引重建 失效或出現(xiàn)錯誤等情況時能回退。索引存儲空間遷移和索引重建.創(chuàng)建三個索引表空間（每個初始值100M最大限制10G）CREATESMALLFILETABLESPACTBS_INDEX01DATAFILE/opt/oradata/index11.dbfSIZE100M , 7opt/oradata/index12.dbf SIZE 100M AUTOEXTEN

34、D ON NEXT 64K MAXSIZE 20GLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;CREATE SMALLFILE TABLESPACE TBS_INDEX02 DATAFILE 7opt/oradata/oradata/index21.dbfSIZE 100M , 7opt/oradata/oradata/index22.dbf SIZE 100M AUTOEXTEND ON NEXT 64KMAXSIZE 20G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPA

35、CE MANAGEMENT AUTO;CREATSMALLFILETABLESPACTBS_INDEX03DATAFILE7opt/oradata/index31.dbfSIZE 100M,7opt/oradata/index32.dbf SIZE 100M AUTOEXTEND ON NEXT 64K MAXSIZE 20G LOGGINGEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;.遷移重建EAS_D_PANCNEW_STANDARD里的所有表所有索弓I1）查看該表空間是否有l(wèi)ob大字段select a.owner,a.tab

36、le_name,a.column_name,a.data_type,b.tablespace_namefromdba_tab_columns a,dba_tables bwhere a.table_name=b.table_name and tablespace_name= EAS_D_PANCNEW_STANDARD order by table_name,column_name;2） 查看是否分區(qū)表12實施方案select owner,table_name,tablespace_name,partitionedfrom dba_tableswhere tablespace_name not

37、 in (SYSTEM, SYSAUX, TEMP, UNDOTBS1； USERS);3)檢查數(shù)據(jù)庫中業(yè)務(wù)用戶下是否有失效的對象，另保存結(jié)果，方便查找。select * from dba_objects whereowner notin(ANONYMOUS,CTXSYS,DBSNMP,DIP,DMSYS,EXFSYS, MDDATA,MDDATA,MDSYS,MGMT_VIEW;OLAPSYS,ORDPLUGINS,ORDSYS,OUT LN,PUBLIC,SCOTT,SI_INFORMTN_SCHEMA,SYS,SYSMAN,SYSTEM,TSMSYS ,WMSYS,XDB); 4) 取出

38、EAS_D_PANCNEW_STANDARD可(PANCNE硼戶)所有索引表空間和重建索弓ISQL語句select alter index | owner|.|index_name | rebuild tablespace tbs_index01 parallel(degree 4) online nologging; from dba_indexes where tablespace_name =EAS_D_PANCNEW_STANDARD and owner=PANCNEW;5) 將取出語句放在記事本中編輯，將該批語句的索引表空間依次 tbs_index01,tbs_index02,tbs

39、_index03 ,循環(huán)。Parallel(degree 4):執(zhí)行并行度為4,該值需根據(jù)服務(wù)器 cpu個數(shù)和當(dāng)前cpu使用率決定，不可將所有 cpu個數(shù)分配給它執(zhí)行，應(yīng)保留至少1-2個。6)分批執(zhí)行索引空間遷移和重建將上述第4步整理編輯好的批量語句分批執(zhí)行，監(jiān)控進度，有問題立刻處理。7)檢查索引是否有效select owner,index_name,status,table_name,index_type from dba_indexeswhere tablespace_name not in (SYSTEM, SYSAUX, TEMP, UNDOTBS1, USERS);8)遷移重建完成后

40、再次檢查數(shù)據(jù)庫中業(yè)務(wù)用戶下是否有失效對象根據(jù)第3)步的語句查找數(shù)據(jù)庫中業(yè)務(wù)用戶下是否還有失效的對象，有則和應(yīng)用系統(tǒng)開發(fā)商、xx相關(guān)人員配合核查是否重編譯、生效。重編譯失效對象示例語句：手工編譯：ALTER PACKAGE my_package COMPILE;ALTER PACKAGE my_package COMPILE BODY;ALTER PROCEDURE my_procedure COMPILE;ALTER FUNCTION my_function COMPILE;ALTER TRIGGER my_trigger COMPILE;如果失效對象很多，可編寫存儲過程自動編譯。第二章 13

41、2.5.4收縮部份業(yè)務(wù)表存儲空間根據(jù)收集的表碎片估算表及多個AW救示物理和邏輯讀信息，再結(jié)合業(yè)務(wù)特點，將其中排在前幾位的數(shù)據(jù)表挑選出來，作存儲空間回縮。有以下三種方法選擇（具體采用哪種方式需到現(xiàn)場對業(yè)務(wù)應(yīng)用系統(tǒng)再了解和xx、應(yīng)用系統(tǒng)開發(fā)商相關(guān)人員溝通后決定，推薦使用方法一，但需先做好表數(shù)據(jù)備份）方法一：.備份要收縮空閑空間的表Create table xxx_bak as select * from原表名；Select count（*） from 原表名；Select count（*） from xxx_bak;查看對比原表和備份表記錄數(shù)是否一致，如果一致，則進行下列步驟，否則不能進行下列步驟

42、。.截掉原表數(shù)據(jù)Truncate table原表名；.重導(dǎo)入原數(shù)據(jù)Insert into 原表名 as select * from xxx_bak;Commit;-（如果該表數(shù)據(jù)量大，可選擇分批提交）方法二：收縮表空間參考語句如下（適用oracle 10g 或以上版本，僅對自動段空間管理ASSM表空間有 效）：ALTER TABLE 表名 ENABLE ROW MOVEMENTALTER TABLE 表名 SHRINK SPACE CASCAQE第2句會造成引用表的對象（如存儲過程、包、視圖等 ）變?yōu)闊o效。執(zhí)行完成后，執(zhí)行一下utlrp.sql編譯無效的對象：SQLshutdown immed

43、iateSQLstartupSQL ?/rdbms/admin/utlrp.sql根據(jù)3.5.3第3）步語句檢查是否還有失效對象，是否需重編譯，恢復(fù)到原樣。方法三：. alter table 表名move （tablespace表空間名）;-如果不遷移該表的存儲表空間，則（tablespace 表空間名）不需要；.重建該表所有索引通過下列語句取重建該表所有索引：select alter index | owner | .| index_name | rebuild tablespace tbs_index0114實施方案parallel(degree 4) online nologging;f

44、rom dba_indexes where table_name= 表名;copy所有語句，貼在 sqlplus命令行執(zhí)行即可。.分析該表，重新生成執(zhí)行計劃analyze table XX compute statistics for all indexes;第二章 152.5.5建議優(yōu)化全表掃描導(dǎo)致性能差的語句語句 1BEGIN Drv_Arch_Zhou.Saveywjy(:1, :2, :3, :4, :5, :6); END;分析：運行時間：29.15秒，120分鐘內(nèi)運行246次，建議優(yōu)化里面的語句。語句 2SELECT * FROM DRV_FLOW WHERE TRIM(LSH)

45、= TRIM(:b1)IOesciictbftObiKl cwrerCcs-Cndha 網(wǎng)Bite5I-l SELEILI STAFtMtMI GOiAl33499MEJLE4E55FLILLDRV ADMINDRV_FljOySS45S二75的36呢 7分析：平均每次運行29.51秒，使用了 TRIM(LSH)導(dǎo)致用不上主盤索引， 建議對語句進行優(yōu)化。語句 3SELECT *FROM (SELECT pagetable.*, ROWNUM AS rowcounterFROM (select *from (select *from veh_flowwhere 1 = 1and syr LIKE

46、 佛山市南海佛廣公共汽車有限公司%and glbm in (440682, 440682)union allselect *from veh_flow_hiswhere 1 = 1and syr LIKE 佛山市南海佛廣公共汽車有限公司%and glbm in (440682, 440682)order by sqrq desc) pagetable) subtWHERE subt.rowcounter 0AND subt.rowcounter gztGiriLi冰餐 4*gnr_nami lh 窄p* r ： umL w *ar 寸-h_E1 I Jimd nvnax ,一，VR ALMJM

47、4 r語句執(zhí)行計劃:r ptnuzer gNAl ewf hi *，Z ，心口Le口 wr曰Dbect FidYtaCostCdidndlty Ey.居3 SELECT HMLhrr.Gax2151D51135i VIEVDRV.6DMIN215151b1195: COUNT-VIEWDW.DMIN2151 Elb113D向.SORT ORDFR2151515113D二 MEWDAW 4DHIN215130511303 UHIOKA：7aELEVEH ftDHIb宣H_FL0W2146374534B【ACLE VEH一3MINVEH.-LO.HlS4531129IN： VEHJOHIN|J/E

48、H_FLCW_HIS_XrW2861 期運行時間：171秒分析:語句在表 VEH_FLO摘用了全表掃描，而表VEH_FLOWT 7.7GB,使用全表掃描速度非常慢，建議在適當(dāng)?shù)淖侄渭由纤饕?。例如建?glbm,syr)的聯(lián)合索引。語句 4SELECT *FROM (SELECT pagetable.*, ROWNUM AS rowcounterFROM (select *from drivinglicensewhere 1 = 1and xm = :1) pagetablewhere rownum 0表大?。簆agetable 11 GB語句執(zhí)行計劃：Oeicriobosject ovmObj

49、ect namdCod：Cr dindiDjte-i-S E LECr STM EM ENT, GOAL- LL_A OWE的曉發(fā)IC9090-VIMDRVN的費及Id曲gi-COUNT STOPKEY陽LE ACCESS FILL呵一叩MNDRNirJGLICEMSE弱矣“1U+51 JI分析：語句在表drivinglicense 使用了全表掃描，而表 drivinglicense 有11GB使用全表掃描速度第二章 17非常慢，建議在適當(dāng)?shù)淖侄渭由纤饕?。例如建?(xm)的索弓I。語句 5SELECT count(*) AS rownFROM (select *from drivinglic

50、ensewhere 1 = 1and xm = :1) pagetableubiect 州net3ptt廣3巾由CostUadira)iiHj屢BGLCT STTEMEN7,GQALrALL_R0MS29522217-i EOFTAGGREGATE17T4RLE ACCESS FULLDAMNfiLirFM；E235222711分析：類似語句 4,平均每次運行 426.65秒，使用全表掃描速度非常慢，建議在適當(dāng)?shù)淖侄渭?上索引。例如建立(xm)的索引。語句 6select count(*)from veh_logwhere to_char(clrq, yyyymmdd) = 20100804,

51、and substr(hphm, 2, 1) = 0and substr(hphm, 3, 1) between A and Kand hpzl = 02and czgw = 0201rOfesunpiiLf ibjecl uarisiOb|ttt nArneCud口業(yè)|盤11T SELECTS TATE MFUT.GML-ALL.flnu/SS幽白11-5DRT4GGREGATE123-UELE ACCESS BY INDEX ROWIOVEI-_LDGGQ3QTQ123IDQnDECCAMVCH_DMIN1 VEH LDG lirZLlIMG8OE4Q4101L分析：由于to_char(clrq,