教程分析oracle-练习题

1、现场支持文档控制修改审阅分发SR拷贝No.1职位日期作者版本修改1目录背景概述 系统环境 问题描述 一、硬硬 软 硬过高 情况： 比率： 对于数据库的危害主要有几方面： 中的语句的情况： 总结： 解决办法： 性能预期： 二、PGA中率偏低 三、KEEP缓冲池很低 四、部分耗费资源大的SQL语句有待优化 SQL语句1： SQL语句2： SQL语句3： 背景概述帐务库（BILL）并提出优化建议。进行为期1周的性能分析，对目前帐务库存在的性能瓶颈作出系统环境硬件& 操作系统IBM AIX操作系统版本5.3内存91GB硬盘CPU个数3Oracle产品及版本第产品及版本业务类型帐务问题描述

2、帐务库采用2节点RAC的运行方式，目前一号机平均会话连接数为500，二号机平均会话连接数为1000。二号机压力较大，系统资源相对繁忙，业务通过分析，找到下面几点性能问题：期CPUIDLE小于10。1、硬 2、Pga过高；中率偏低；3、Keep缓冲池很低；4、部分耗费资源大的sql语句有待优化。一、高在业务时段对数据库作sspack快照，分析数据库性能：硬情况：11月20日9点08到9点31分sspack：11月20日15点24到15点59分sspack：11月21日8点59到9点18分sspack：4LorofilePer SecondPer TranionRedo size:1,009,09

3、5.6911,843.65Logical reads:455,981.845,351.81Block changes:5,018.0358.90Physical reads:10,953.32128.56Physical writes:3,505.1241.14User calls:4,275.8350.19Parses:1,815.2321.31Hard parses:220.532.59Sorts:1,257.6014.76Logons:6.460.08Executes:4,865.4457.11Tranions:85.20LorofilePer SecondPer TranionRedo

4、 size:919,268.4913,928.00Logical reads:365,555.205,538.59Block changes:4,433.2067.17Physical reads:6,121.4292.75Physical writes:1,619.8324.54User calls:4,381.1566.38Parses:1,833.2327.78Hard parses:197.382.99Sorts:1,210.7218.34Logons:5.340.08Executes:4,560.0569.09Tranions:66.00LorofilePer SecondPer T

5、ranionRedo size:-2,127,142.82-25,943.81Logical reads:413,276.505,040.55Block changes:5,226.5863.75Physical reads:14,110.91172.10Physical writes:1,954.3523.84User calls:4,462.8954.43Parses:1,712.6020.89Hard parses:135.491.65Sorts:1,335.3616.29Logons:5.850.07Executes:5,293.6664.56Tranions:81.9911月21日9

6、点18到9点34分sspack：11月22日9点08到9点46分sspack：软比率：通过观察上面列出的数据库期负载情况，可以看到数据库正在历经很高的硬的比率：，高峰时达到每秒200多次，进一步观察软11月20日9点08到9点31分sspack：11月20日15点24到15点59分sspack：Instance Efficiency Percentages (100%)Buffer Nowait %:99.99Redo NoWait %:100.00Buffer Hit%:98.57 Library Hit%:96.23 Execute to Parse %:59.80Parse CPU to

7、 Parse Elapsd %:30.58%emory Sort %:100.00Soft Parse %:89.23Latch Hit %:99.76-Parse CPU:94.81Instance Efficiency Percentages (100%)Buffer Nowait %:99.99Redo NoWait %:100.00Buffer Hit%:96.68 Library Hit%:97.16 Execute to Parse %:72.39Parse CPU to Parse Elapsd %:36.20%emory Sort %:99.99Soft Parse %:89.

8、71Latch Hit %:99.63-Parse CPU:94.01LorofilePer SecondPer TranionRedo size:-774,231.40-9,756.72Logical reads:398,714.995,024.53Block changes:5,434.1468.48Physical reads:11,283.02142.19Physical writes:1,745.3721.99User calls:4,328.6854.55Parses:1,699.3621.42Hard parses:179.282.26Sorts:1,219.2115.36Log

9、ons:5.560.07Executes:4,365.8755.02Tranions:79.35LorofilePer SecondPer TranionRedo size:1,044,146.9312,217.86Logical reads:496,332.105,807.72Block changes:5,222.9761.12Physical reads:8,452.4898.90Physical writes:3,254.8138.09User calls:4,427.9051.81Parses:1,822.0321.32Hard parses:213.552.50Sorts:1,30

10、7.2115.30Logons:6.860.08Executes:5,065.8159.28Tranions:85.4611月21日8点59到9点18分sspack：11月21日9点18到9点34分sspack：11月22日9点08到9点46分sspack：Soft parse 的计算公式为：(1-hardparses/parses)100目前软的范围，oracle的比率为88，这意味着每100次就会有12次硬。这已经超出了合理建议软比率要=99。硬对于数据库的危害主要有几方面：1、消耗服务器的CPU资源；2、引起3、占用的LATCH争用；的shared pool 空间。1、消耗服务器的CPU

11、资源观察上面蓝色标识的 -Parse CPU:计算公式为(1-parse cpu time/exeu time)100公式的含义是非操作所使用的CPU百分比，目前数据库的这个比率为95，这意味着有5的CPU资源浪费在了上面，如果能够有效的降低硬，数据库的整体性能将会5。2、目前数据库的LATCH争用不明显3、shared pool空间消耗：11月20日9点08到9点31分sspack：Instance Efficiency Percentages (100%)Buffer Nowait %:99.99Redo NoWait %:100.00Buffer Hit%:97.37emory Sort

12、 %:99.99Library Hit%:96.41Soft Parse %:89.45Execute to Parse %:61.08Latch Hit %:99.75Parse CPU to Parse Elapsd %:31.47%-Parse CPU:95.19Instance Efficiency Percentages (100%)Buffer Nowait %:99.99Redo NoWait %:100.00Buffer Hit%:98.79emory Sort %:99.98Library Hit%:96.27Soft Parse %:88.28Execute to Pars

13、e %:64.03Latch Hit %:99.70Parse CPU to Parse Elapsd %:27.46%-Parse CPU:95.46Instance Efficiency Percentages (100%)Buffer Nowait %:99.99Redo NoWait %:100.00Buffer Hit%:98.16 Library Hit%:96.08 Execute to Parse %:62.69Parse CPU to Parse Elapsd %:27.36%emory Sort %:99.98Soft Parse %:87.85Latch Hit %:99

14、.71-Parse CPU:95.3011月20日15点24到15点59分sspack：11月21日8点59到9点18分sspack：11月21日9点18到9点34分sspack：11月22日9点08到9点46分sspack：Shared pool 的内存消耗已经达到了96，这已经是一个很高的比率，ORACLE 控制在90以下，否则会有ORA-04031 （shared pool内存耗尽）的风险。如果Shared建议pool的内存耗尽，就会导致hang、宕机等不可的。shared pool 中Shared pool所的sql语句的情况：的sql语句总数：29515Shared pool所的sq

15、l中，只执行过1次的sql语句总数：22022SQL select count(1) from v$sql where executions = 1; COUNT(1)22022SQL select count(1) from v$sql; COUNT(1)29515Shared Pool SisticsBeginEndMemory Usage %:96.6896.50% SQL with executions1:16.3418.68% Memory for SQL w/exec1:17.3418.59Shared Pool SisticsBeginEndMemory Usage %:96.4

16、396.69% SQL with executions1:15.1518.78% Memory for SQL w/exec1:16.7317.65Shared Pool SisticsBeginEndMemory Usage %:96.5496.43% SQL with executions1:15.4415.15% Memory for SQL w/exec1:16.5716.73Shared Pool SisticsBeginEndMemory Usage %:96.8996.71% SQL with executions1:14.6416.94% Memory for SQL w/ex

17、ec1:16.5016.18Shared Pool SisticsBeginEndMemory Usage %:96.6296.27% SQL with executions1:19.8722.74% Memory for SQL w/exec1:19.6421.43这些只执行过1次的语句被认为是非共享的语句，目前shared pool中80的语句都是非共享的语句，这违背了ORACLE设计shared pool的初衷：shared pool即共享池，目的是最大限度的缓存和共享sql对象，从而降低硬就会浪费很大的内存空间。的发生。如果shared pool中充斥着非共享的sql语句，导致非共享s

18、ql的原因有2种：1、业务复杂，应用本身就会产生很多不经常执行的sql语句，这种情况只能通过调大shared pool来解决问题；2、sql语句没有绑定变量，这样虽然sql语句结构相同，但是每一个条件值不同的sql语句都会作为一个单独的对象变量的方式来解决。到shared pool中，这种情况可以通过改写sql语句为绑定查看shared pool中非共享的sql语句：SQL select substr(sql text,1,40),count(1) substr(sql_text,1,40) order by 2;fromv$sqlwhereexecutions=1group bySUBSTR

19、(SQL TEXT,1,40)COUNT(1)select * from bill.v prod where servic (SELECT A3.NEW ACC NBR,A3.CHARGE111111111beginsys.dbms_application_infoinsertoernal event instance (in select * from bill.v prod where service合同号 select * from bill.v_accPM GROUP RELA select * from bill.v pmTOTAL MONTH select ,a.*fr

20、om-查计费状态。 中间略。 Update SELECT SELECTselect select select select select SELECT SELECT SELECT SELECTSTAILL PRSET PRPAGES EL ACCT ID ACCT I258277295295298299303310313328336355360368373375396422443456462DISTINCTVCOUNT(DISTINCTI.BILLING CYCLE IDacct_item_id,nvl(bill_id,0) bill_1 fromfcas log whereoir.serv

21、 ir.acct item id frommin(serv id)serv id,min(acct id)a acct_item_id,acct_id,latn_id,bill COUNT(*) FROM OWE DISMOUNT USER I A.OLD BC ID, DECODE(A.OLD BC IDI.*,AIT.NAME AIT_NAME,AIS/*+ RULE*/ DISTINCT(A.PRICING PARinsertoF OTHER TRANS LOG values(inserto JUDGE DUP SAVE BALANCE(SELECT_ACCT_ID ACCT_ID,_A

22、CCT_CSELECT * FROM ( SELECT a.PROD ID SERV IINSERT SELECT SELECT SELECT SELECTO TG OPERAOG(LOG ID,CODE I*FROM (SELECT DISTINCT BAD.ACC A.*, (SELECT COUNT(*) CT FROM V_DISTINCT0 SELECTED,AI.ACCTDISTINCTIST_VALUE,IST发现有大量的前40个字节完全相同的sql语句，进一步观察这些sql语句的内容：SQL select sql text from v$sql where substr(sql

23、 text,1,40)=INSERT (PRID, B;SQL_TEXTO BILLPRDATAINSERT SORT_ID) 1/1, 1) INSERTO BILL PRDATA (PRID,BILLITEMID,BILLFORMATID,PRCONTENT,VALUES(26491814, 5276, 0,O BILL PRDATA (PRID,BILLITEMID,BILLFORMATID,PRCONTENT,SORT ID)VALUES(26491413, 5024, 800,11, 1)INSERTO BILL PRDATA (PRID,BILLITEMID,BILLFORMATI

24、D,PRCONTENT,SORT ID) VALUES(26491596, 150004,830, 36.40, 2)INSERTO BILL PRDATA (PRID,BILLITEMID,BILLFORMATID,PRCONTENT,SORT ID) VALUES(26491888, 5028, 612,42.90, 1)INSERT SORT ID).中间略.O BILL PRDATA (PRID,BILLITEMID,BILLFORMATID,PRCONTENT,VALUES(26491708, 5020, 830,INSERTO BILL PRDATA (PRID,BILLITEMI

25、D,BILLFORMATID,PRCONTENT,SORT ID)VALUES(26491589, 150090,830, 15.00, 6)INSERTO BILL PRDATA (PRID,BILLITEMID,BILLFORMATID,PRCONTENT,SORT ID) VALUES(26492350, 5024, 800,11, 1)INSERTO BILL_PR_DATA (PR_ID,BILL_ITEM_ID,BILL_FORMAT_ID,PR_CONTENT,SORT ID) VALUES(26491894, 5022, 0,2007 年 10 月, 1)INSERTO BIL

26、L_PR_DATA (PR_ID,BILL_ITEM_ID,BILL_FORMAT_ID,PR_CONTENT,SORT ID) VALUES(26491384, 5027, 612,壹拾肆角整, 1)5567 rowected.SQL select sql textSERIAL ID = 2;from v$sql where substr(sqltext,1,40)=UPDATESETSQL TEXTUPDATESETID = 2504779798SETID = 2635878287_SERIAL_ID = 203792568, PR_COUNT = PR_COUNT+ 1 WHEREUPD

27、ATESERIAL ID = 203792652, PRCOUNT = PRCOUNT + 1 WHERE.中间略.SELECT A1.MDSE ID,A1.PRICING PARA544select min(fee cycle id) from one item r599INSERTO VOUCHRRESULT(VOU866UPDATESET_SERIAL_ID = 21801INSERTO BILL PRDATA (PRID, B5510691 rowected.上面列出的2个语句，语句前面的内容完全相同，就是输入的值不同，明显属于没有绑定变量的情况，这些sql语句所占用的共享内存情况：这

28、2个语句分别占用了81M和50M的共享内存，占整个sharedpool的12.8，如果能够对这2个语句进行绑定变量，就能极大程度的缓解shared pool目前内存高占用率（96）的情况，另外也能有效的降低硬次数。使用绑定变量的风险：对于insert 语句，不存在执行计划突变导致sql变慢。对于update语句，谓词列的分析情况：SQL select owner,endpo_number,endpo_value from dba_histograms where table_name =SQL select sum(SHARABLE MEM)/1024/1024 from v$sql wher

29、e substr(sql text,1,40)=INSERT O BILL PRDATA (PRID, B;SUM(SHARABLE MEM)/1024/102481.2722406SQL select sum(SHARABLE MEM)/1024/1024 from v$sql where substr(sql text,1,40)=UPDATE SETSERIAL ID = 2;SUM(SHARABLE MEM)/1024/102450.0693188SQL shorameter shared poolNAME_COL_PLUS_SHOW_PARAMTYPEVALUE COL PLUS S

30、HORAMshared poolsizebigeger106954752shared pool sizebigeger1073741824SQL select 1073741824/1024/1024 from dual; 1073741824/1024/10241024SQL select (81.2722406+50.0693188)/1024 from dual; (81.2722406+50.0693188)/1024.128263242UPDATESETSERIAL ID = 203792656, PRCOUNT = PRCOUNT + 1 WHEREID = 2255027122U

31、PDATESETSERIAL ID = 203792325, PRCOUNT = PRCOUNT + 1 WHEREID = 2643169453UPDATESETSERIAL ID = 203792495, PRCOUNT = PRCOUNT + 1 WHEREID = 2643168157UPDATESETSERIAL ID = 203792597, PRCOUNT = PRCOUNT + 1 WHERE_ID = 26163520542980 rowected.Update语句的条件列_ID 没有分析直方图，所以使用绑定变量和使用明文得到的执行计划是完全一样的，所以目前这种情况下使用绑定

32、变量不存在任何风险。总结：数据库硬过高主要是由部分sql语句没有绑定变量引起的，这些sql语句一方面在作硬的时候消耗了CPU资源，另一方面在的时候消耗了很多shared pool的内存资源。解决办法：按照上文提到的方法，定期查询shared pool中的非共享sql：对前40字节相同且数量超过1000的sql语句，以绑定变量的方式进行改写。通过这种方法消除系统中存在的执行次数多的非共享sql。性能预期：如果能够按照上述的办法硬，那么系统CPU资源空闲比例将至少提高5，sharedpool的共享内存使用率叫保持在90以下。SQL select substr(sql text,1,40),coun

33、t(1) from v$sql where executions = 1 group by substr(sql_text,1,40) order by 2; and column name = ID;OWNERENDPO_NUMBER ENDPO_VALUEBILL0371PRICING CONFIG0243541BILL_CONFIG0243541BILL12557544751PRICING CONFIG1244857BILL_CONFIG12448576 rowected.二、Pga在业务中率偏低时段数据库的PGA：11月20日9点08到9点31分sspack：11月20日15点24到1

34、5点59分sspack：11月21日8点59到9点18分sspack：11月21日9点18到9点34分sspack：11月22日9点08到9点46分sspack：PGA观察PGA低会影响中率。排序和哈希连接的执行性能，目前PGA为3G，建议将PGA调大为4G，并继续12PGA Cache Hit % W/A MB Prosed Extra W/A MB Read/Written89.9128,25814,386PGA Cache Hit % W/A MB Prosed Extra W/A MB Read/Written78.574,78120,472PGA Cache Hit % W/A MB

35、 Prosed Extra W/A MB Read/Written77.791,87226,391PGA Cache Hit % W/A MB Prosed Extra W/A MB Read/Written86.7107,07716,402PGA Cache Hit % W/A MB Prosed Extra W/A MB Read/Written87.991,62012,659三、Keep缓冲池很低在业务时段数据库的keep缓冲池中率：11月20日9点08到9点31分sspack11月20日15点24到15点59分sspack：11月21日8点59到9点18分sspack：11月21日9点

36、18到9点34分sspack：11月22日9点08到9点46分sspack：13FreeWrite Buffer Number of CacheBufferPhysicalPhysical Buffer CompleteBusyPBuffers Hit %GetsReadsWritesWaitsWaitsWaitsD1,434,750 100.6# 18,763,340 1,747,80500 77,9212k44,751000000K3,060,800 49.1 10,463,6825,328,364473,38400138FreeWrite Buffer Number of CacheBu

37、fferPhysicalPhysical Buffer CompleteBusyPBuffers Hit %GetsReadsWritesWaitsWaitsWaitsD1,434,750 99.0 470,052,9254,839,116607,96100 38,1582k44,751000000K3,060,800 77.33,886,414882,192165,9330030FreeWrite Buffer Number of CacheBufferPhysicalPhysical Buffer CompleteBusyPBuffers Hit %GetsReadsWritesWaits

38、WaitsWaitsD1,434,750 98.3 522,457,0038,978,107841,50600 41,4582k44,751000000K3,060,800 82.53,878,173677,058200,8500098FreeWrite Buffer Number of CacheBufferPhysicalPhysical Buffer CompleteBusyPBuffers Hit %GetsReadsWritesWaitsWaitsWaitsD1,434,750 98.7 743,952,537 10,043,235 1,107,12300 35,7022k44,75

39、1000000K3,060,800 88.85,911,211660,550336,61100112Buffool Sistics for DB: BILL Instance: bill2 Snaps: 9185 -9186- Standard block size Pools D: default, K: keep, R: recycle- Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32kFreeWrite Buffer Number of CacheBufferPhysicalPhysical Buffer Complete

40、BusyPBuffers Hit %GetsReadsWritesWaitsWaitsWaitsD1,434,750 98.1 546,084,510 10,390,790 1,000,83600 141,3622k44,751000000K3,060,800 39.9 12,322,8077,407,346239,023006,074指定KEEP池的对象：表BILL.ACCT_ITEM_TOTAL_MONTH BILL. ACCT_ITEMILL. UNQ_ACCT_ITEM_TOTAL_MONTH_ID BILL. IDX_ACCT_ITEM_A_ACCT_ID BILL. UNQ_ACC

41、T_ITEM_ID索目前KEEP缓冲池为26GB，而keep的对象为500多GB。由于业务方面不同时间的数据是不一样的，且keep的数据量远大于keep缓冲池的大小，所以导致keep缓冲池很低。跟keep对象相关的业务方面的等待主要是单块IO扫描等待，说明keep缓冲池的大小还不能满足业务需要，考虑在将来数据库扩容时继续调大KEPP缓冲池。四、部分耗费资源大的SQL语句有待优化在sspack中TOP SQL中，有部分耗费资源大的SQL语句可以优化：SQL语句1：SQL原文:资源消耗情况：占总体sql使用资源的5.5执行计划：全表扫描表的统计信息：数据字典中表有5个数据块。15SQL selec

42、t object_owner from v$sql_plan where hash_value = 2666110673; OBJECT OWNERSQLselectid,parent id,operation,options,object name,cost,cardinality,PARTITION START,PARTITION STOP from v$sql plan where hash value= 26661106732 /OPERATIONOPTIONS OBJECT NAMECOST CARDINALITY SELECT SEMENT2TABLE ACSFULLBILL_AC

43、CT_ITEM21CPUElapsdBuffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value30,960,555185,002167.45.5934.761604.30 2666110673SELECT BAI.BILL_ITEM_ID, DECODE(BAI.ACCT_ITEM_CLASS, :B5 , 2, 0, 1, 0) FLAG AIC, DECODE(BAI.PARTNER ID, :B4 , 2, 0, 1, 0) FLAG P, DECODE(BAI.MDSE SPEC ID, :B3 , 2

44、, 0, 1, 0) FLAG MS, DECODE(BAI.PREFER SPEC ID, :B2 , 2, 0, 1, 0) FLAG PS, DECODE(BAI.ACS CO DE_ID, :B1 , 2, 0, 1, 0) FLAG_AC FROM BILL_ACCT_ITEM BAI WHERE BSELECT BAI.BILL ITEM ID,DECODE(BAI.ACCT ITEM CLASS, :B5, 2, 0, 1, 0) FLAG AIC,DECODE(BAI.PARTNER ID, :B4, 2, 0, 1, 0) FLAG P,DECODE(BAI.MDSE SPE

45、C ID, :B3, 2, 0, 1, 0) FLAG MS,DECODE(BAI.PREFER_SPEC_ID, :B2, 2, 0, 1, 0) FLAG_PS,DECODE(BAI.ACS CO DE ID, :B1, 2, 0, 1, 0) FLAG AC FROM BILL ACCT ITEM BAIWHERE BAI.BILL ITEM DATA METHOD = 5DB AND BAI.ACCT ITEM TYPE ID = :B9AND BAI.ACCT ITEM SOURCE ID = :B8 AND BAI.REQUIRE ID = :B7AND BAI.BILL_FORM

46、AT_ID = :B6AND DECODE(BAI.ACCT ITEM CLASS, :B5, 2, 0, 1, 0) 0AND DECODE(BAI.PARTNER ID, :B4, 2, 0, 1, 0) 0AND DECODE(BAI.MDSE SPEC ID, :B3, 2, 0, 1, 0) 0AND DECODE(BAI.PREFER_SPEC_ID, :B2, 2, 0, 1, 0) 0AND DECODE(BAI.ACS_CO DE_ID, :B1, 2, 0, 1, 0) 0表的实际大小：表实际有256个数据块，12114条。分析可以看到表的实际大小与数据字典中的大小严重不符

47、，而CBO优化器是根据数据字典中的信息来执行计划的，由于数据字典中扫描。的表的高水标记之下只有5个数据块，所以CBO选择了全表考虑是否可以通过索引扫描来降低sql的数据表中创建索引的列：ACCT_ITEM_SOURCE_ID ACCT_ITEM_TYPE_ID BILL_REQUIRE_ID BILL_ITEM_ID量：其中，ACCT_ITEM_SOURCE_ID和ACCT_ITEM_TYPE_ID 被sql语句作为等值查询条件列的索引的效率：，这2SQL select ACCT ITEM SOURCE ID,count(1) from PRICING CONFIG.BILL ACCT ITE

48、M group by ACCT ITEM SOURCE ID;ACCT ITEM SOURCE IDCOUNT(1)-12901181011272148SQL select ACCT ITEM TYPE ID,count(1) from PRICING CONFIG.BILL ACCT ITEM group by ACCT_ITEM_TYPE_ID;ACCT ITEM TYPE IDCOUNT(1)0364101020933011402SQL select blocks from dba segments where segment name = BILL ACCT ITEM and owne

49、r= PRICING CONFIG;BLOCKS256SQL select count(1) from PRICING CONFIG.BILL ACCT ITEM; COUNT(1)12114PRICING CONFIGSQL select blocks from dba tables where table name = BILL ACCT ITEM and owner= PRICING CONFIG;BLOCKS560818310102010211181101010102010103010103020104010104013104014104020108310101010101001010

50、101011211131115124335101010103201013100201016100201019100202013100217703612931264424761033120121732778122001291402329963735412192581291065148400914612119203010100203020100301010100301010101301010102301013100302010100302020100303010100303013100304010100304020100305010100305010101305010102305010103305

51、013100306010100306010101306010102306010103306020100401010100401010101401010102401010103401010105401010106401010107401110100401210100401210102401210103401210104401210105401210106401210107401210108401210110401210111401210112401310101101010101017214881012493401410102401410103402010101402010102402010103

52、402010104402010105402010106402010108940201011264020101231212402010126402010128940201013224020101435111011111040201015140201015240201015340201015440201015540201015640201015740201015840201015940201016040201016140201016240201016340201016440201016540201016640201016740201016840201016940201017040201017140

53、201017240201017340201017440201017540201017640201017740201017840201017940201018040201018140201018240201018340201018540201018640201018740201018840201018940201019040201019140201019240201019340201019440201019540201019640201019740201019840201019940201020040201020140201020240201020340201020440201020540201

54、020640201020750101010050101010150101010250101010310101010510101010101012912121212121212121212121212121212121212121212666668942777777724014841212911450101010550101010650101010850101010960101011012126010101126010101136010101142425113025372327218010101018010101028010101038010101048010101058010101078010

55、101088010101092529312827262729243225262839272026248010101158010101168010101178010101188010101198010101208010101218010101228010101238010101248010101258010101268010101278010101288010101298010101308010101318010101323280101013580101013880101014080101014180101014280101014380101014480101014580101014680101

56、014780101014880101014980101015080101015180101015281101010082101000110102810101069433544808210100038210100048210100058210100068220101002231110951282301010082301010182301010282301010382301010482301010582301010683101010083101010183101010283101010410999910649112227991010983101010683101010783101010883101

57、010983101011083101011183101011283101011383101011483101011591010109999999743424983101011783101011883101011983101012083101012183101012283101012383101012483101012583101012683101012783101012883101012983101013190101010490101010590101010690101010790101011089010101129010101159010101169010101179010101189010

58、1012190101012290101012390101012490101012790101012890101012990101013044944210533048578126424638901010132901010133901010136901010137901010139901010140901010141901010142901010143发现ACCT_ITEM_TYPE_ID列的数值分布比较均匀，对这一列上面的索引进行扫描，效率会很高。优化方法：1、2、对表和索引进行分析，在分析之后，CBO会选择对ACCT_ITEM_TYPE_ID列的索引进行扫描；加提示强制对ACCT_ITEM_T

59、YPE_ID列上面的索引进行索引扫描。SQL语句2：SQL原文:资源消耗情况：逻辑读占总体sql的7.2CPUElapsdBuffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value40,484,304642,60763.07.214.76319.76 3637989402Module: CycleEvtGenbill app2 (TNS V1-V3)SELECT EVENT_ID, CYCLE_TYPE, CYCLE_OFFSET, EXEC_SQL FROM CYCLE_I NTERNAL_EVENT WHE

60、RE SE=00A AND NEXT_EVENT_DATE=SYSDATE ORDSELECT EVENT ID, CYCLE TYPE, CYCLE OFFSET, EXEC SQL FROM CYCLE I NTERNAL EVENT WHERE S E=00A AND NEXT EVENT DATE declarecursor v cur iect rowid from PRICING CONFIG.CYCLEERNAL EVENT order by rowid;v rowid varchar2(200);ls my rowidvarchar2(200);rowid typenumber