Oracle数据库系统性能分析评价与优化

1、Oracle数据库性能分析评估与优化实施方案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实施影响和风险评估 2实施需求 2实施规划 2实施步骤 10第一章 1第一章 性能分析评估维护程师通过对 Oracle数据库巡检，以对应用系统和数据库状况了解、相关信息的收集。分析 评估数据库性能瓶颈：.用户等待I/O;.随着

2、应用系统的数据量不断增大，是否未对一些常用业务表和索引进行空间回缩；.应用系统层面，如 SQL语句效率、索引的合理性、部份数据结构设计的合理性。性能信息收集：通过ORACLED具RDA (REMOTE DIAGNOSTIC AGEN硬行系统信息收集通过ORACLED具STATSPACK (9i)或AWR(10g或以上)进行数据库性能资料的收集 ，操作 系统工具和命令检查操作系统。SQL命令检查数据库配置， SQL命令在 ORACLET具SQL*PLU汕运行。粘贴重点的性能问题信息收集的结果，及加入分析评估建议 索引部份检查数据表的索引建立情况。表空间碎片检查数据表空间的碎片。效率低SQL语句检

3、查应用程序效率低的 SQL语句。实施方案第二章实施方案本方案实施主要有三点，如下：建立三个索引表空间，将所有业务表索引迁移分散在该三个表空间中。通过AWR性能报告，结合业务应用系统特点，找出频繁增删改、数据量大等表碎片较多的 业务表，对其进行空间回缩。找出全表扫描导致性能差的 SQL语句实施时间由于本方案的实施对业务系统影响较大，建议停止数据库对外服务，安排在业务空闲期如周末 进行。实施周期预计 1-2天。实施影响和风险评估需要停业务系统和数据库需要停止业务系统和数据库对外服务，实施前需对数据库进行全备。对业务系统的影响由于实施是对大量的索引文件的迁移、重建，在实施完成后，对业务系统的某些功能

4、或数据库 对象可能会造成失效，需要与应用系统开发商、xx相关人员配合，共同检查，完成将失效的功能或对象重新编译、生效。实施需求需要xx公司对应用系统和数据库比较了解的相关人员配合进行，另外，由于本方案的实施涉及应用系统数据结构，如索引、业务表等，因此，需要应用系统开发商安排对应用系统数据结构较了 解的人员配合。以保证本方案实施的进度，另外将风险降到最低。实施规划表索引迁移规划迁移重建后的新表空间原表空间索引名备注第二章 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回上同上回收表空闲空间规划（以下表可根据业务特点先期挑选部份作回缩空间， 作下标记）序号用户名表名表空间名表真实大小（MB）表估算大小（MB）比率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实施步骤数据库全备关闭数据库，对整个数据库进行全备份。第二章 11索引记录备份.用以下语句查找所有业务表索引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步生成的所有业务表索引另存文档（如 excel或文件格式），保存。以便在索引重建 失效或出现错误等情况时能回退。索引存储空间迁移和索引重建.创建三个索引表空间（每个初始值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）查看该表空间是否有lob大字段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） 查看是否分区表12实施方案select owner,table_name,tablespace_name,partitionedfrom dba_tableswhere tablespace_name not

37、 in (SYSTEM, SYSAUX, TEMP, UNDOTBS1； USERS);3)检查数据库中业务用户下是否有失效的对象，另保存结果，方便查找。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 ,循环。Parallel(degree 4):执行并行度为4,该值需根据服务器 cpu个数和当前cpu使用率决定，不可将所有 cpu个数分配给它执行，应保留至少1-2个。6)分批执行索引空间迁移和重建将上述第4步整理编辑好的批量语句分批执行，监控进度，有问题立刻处理。7)检查索引是否有效select owner,index_name,status,table_name,index_type from dba_indexeswhere tablespace_name not in (SYSTEM, SYSAUX, TEMP, UNDOTBS1, USERS);8)迁移重建完成后

40、再次检查数据库中业务用户下是否有失效对象根据第3)步的语句查找数据库中业务用户下是否还有失效的对象，有则和应用系统开发商、xx相关人员配合核查是否重编译、生效。重编译失效对象示例语句：手工编译：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收缩部份业务表存储空间根据收集的表碎片估算表及多个AW救示物理和逻辑读信息，再结合业务特点，将其中排在前几位的数据表挑选出来，作存储空间回缩。有以下三种方法选择（具体采用哪种方式需到现场对业务应用系统再了解和xx、应用系统开发商相关人员沟通后决定，推荐使用方法一，但需先做好表数据备份）方法一：.备份要收缩空闲空间的表Create table xxx_bak as select * from原表名；Select count（*） from 原表名；Select count（*） from xxx_bak;查看对比原表和备份表记录数是否一致，如果一致，则进行下列步骤，否则不能进行下列步骤

42、。.截掉原表数据Truncate table原表名；.重导入原数据Insert into 原表名 as select * from xxx_bak;Commit;-（如果该表数据量大，可选择分批提交）方法二：收缩表空间参考语句如下（适用oracle 10g 或以上版本，仅对自动段空间管理ASSM表空间有 效）：ALTER TABLE 表名 ENABLE ROW MOVEMENTALTER TABLE 表名 SHRINK SPACE CASCAQE第2句会造成引用表的对象（如存储过程、包、视图等 ）变为无效。执行完成后，执行一下utlrp.sql编译无效的对象：SQLshutdown immed

43、iateSQLstartupSQL ?/rdbms/admin/utlrp.sql根据3.5.3第3）步语句检查是否还有失效对象，是否需重编译，恢复到原样。方法三：. 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命令行执行即可。.分析该表，重新生成执行计划analyze table XX compute statistics for all indexes;第二章 152.5.5建议优化全表扫描导致性能差的语句语句 1BEGIN Drv_Arch_Zhou.Saveywjy(:1, :2, :3, :4, :5, :6); END;分析：运行时间：29.15秒，120分钟内运行246次，建议优化里面的语句。语句 2SELECT * FROM DRV_FLOW WHERE TRIM(LSH)

45、= TRIM(:b1)IOesciictbftObiKl cwrerCcs-Cndha 网Bite5I-l SELEILI STAFtMtMI GOiAl33499MEJLE4E55FLILLDRV ADMINDRV_FljOySS45S二75的36呢 7分析：平均每次运行29.51秒，使用了 TRIM(LSH)导致用不上主盘索引， 建议对语句进行优化。语句 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语句执行计划: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,使用全表扫描速度非常慢，建议在适当的字段加上索引。例如建立(glbm,syr)的联合索引。语句 4SELECT *FROM (SELECT pagetable.*, ROWNUM AS rowcounterFROM (select *from drivinglicensewhere 1 = 1and xm = :1) pagetablewhere rownum 0表大小：pagetable 11 GB语句执行计划：Oeicriobosject ovmObj

49、ect namdCod：Cr dindiDjte-i-S E LECr STM EM ENT, GOAL- LL_A OWE的晓发IC9090-VIMDRVN的费及Id曲gi-COUNT STOPKEY阳LE ACCESS FILL呵一叩MNDRNirJGLICEMSE弱矣“1U+51 JI分析：语句在表drivinglicense 使用了全表扫描，而表 drivinglicense 有11GB使用全表扫描速度第二章 17非常慢，建议在适当的字段加上索引。例如建立 (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秒，使用全表扫描速度非常慢，建议在适当的字段加 上索引。例如建立(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口业|盘11T SELECTS TATE MFUT.GML-ALL.flnu/SS幽白11-5DRT4GGREGATE123-UELE ACCESS BY INDEX ROWIOVEI-_LDGGQ3QTQ123IDQnDECCAMVCH_DMIN1 VEH LDG lirZLlIMG8OE4Q4101L分析：由于to_char(clrq,