教程分析oracle-練習題

1、現(xiàn)場支持文檔控制修改審閱分發(fā)SR拷貝No.1職位日期作者版本修改1目錄背景概述 系統(tǒng)環(huán)境 問題描述 一、硬硬 軟 硬過高 情況： 比率： 對于數(shù)據(jù)庫的危害主要有幾方面： 中的語句的情況： 總結： 解決辦法： 性能預期： 二、PGA中率偏低 三、KEEP緩沖池很低 四、部分耗費資源大的SQL語句有待優(yōu)化 SQL語句1： SQL語句2： SQL語句3： 背景概述帳務庫（BILL）并提出優(yōu)化建議。進行為期1周的性能分析，對目前帳務庫存在的性能瓶頸作出系統(tǒng)環(huán)境硬件& 操作系統(tǒng)IBM AIX操作系統(tǒng)版本5.3內存91GB硬盤CPU個數(shù)3Oracle產(chǎn)品及版本第產(chǎn)品及版本業(yè)務類型帳務問題描述

2、帳務庫采用2節(jié)點RAC的運行方式，目前一號機平均會話連接數(shù)為500，二號機平均會話連接數(shù)為1000。二號機壓力較大，系統(tǒng)資源相對繁忙，業(yè)務通過分析，找到下面幾點性能問題：期CPUIDLE小于10。1、硬 2、Pga過高；中率偏低；3、Keep緩沖池很低；4、部分耗費資源大的sql語句有待優(yōu)化。一、高在業(yè)務時段對數(shù)據(jù)庫作sspack快照，分析數(shù)據(jù)庫性能：硬情況：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：軟比率：通過觀察上面列出的數(shù)據(jù)庫期負載情況，可以看到數(shù)據(jù)庫正在歷經(jīng)很高的硬的比率：，高峰時達到每秒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次硬。這已經(jīng)超出了合理建議軟比率要=99。硬對于數(shù)據(jù)庫的危害主要有幾方面：1、消耗服務器的CPU資源；2、引起3、占用的LATCH爭用；的shared pool 空間。1、消耗服務器的CPU

11、資源觀察上面藍色標識的 -Parse CPU:計算公式為(1-parse cpu time/exeu time)100公式的含義是非操作所使用的CPU百分比，目前數(shù)據(jù)庫的這個比率為95，這意味著有5的CPU資源浪費在了上面，如果能夠有效的降低硬，數(shù)據(jù)庫的整體性能將會5。2、目前數(shù)據(jù)庫的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 的內存消耗已經(jīng)達到了96，這已經(jīng)是一個很高的比率，ORACLE 控制在90以下，否則會有ORA-04031 （shared pool內存耗盡）的風險。如果Shared建議pool的內存耗盡，就會導致hang、宕機等不可的。shared pool 中Shared pool所的sql語句的情況：的sql語句總數(shù)：29515Shared pool所的sq

15、l中，只執(zhí)行過1次的sql語句總數(shù)：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這些只執(zhí)行過1次的語句被認為是非共享的語句，目前shared pool中80的語句都是非共享的語句，這違背了ORACLE設計shared pool的初衷：shared pool即共享池，目的是最大限度的緩存和共享sql對象，從而降低硬就會浪費很大的內存空間。的發(fā)生。如果shared pool中充斥著非共享的sql語句，導致非共享s

18、ql的原因有2種：1、業(yè)務復雜，應用本身就會產(chǎn)生很多不經(jīng)常執(zhí)行的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-查計費狀態(tài)。 中間略。 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發(fā)現(xiàn)有大量的前40個字節(jié)完全相同的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）的情況，另外也能有效的降低硬次數(shù)。使用綁定變量的風險：對于insert 語句，不存在執(zhí)行計劃突變導致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 沒有分析直方圖，所以使用綁定變量和使用明文得到的執(zhí)行計劃是完全一樣的，所以目前這種情況下使用綁定

32、變量不存在任何風險?？偨Y：數(shù)據(jù)庫硬過高主要是由部分sql語句沒有綁定變量引起的，這些sql語句一方面在作硬的時候消耗了CPU資源，另一方面在的時候消耗了很多shared pool的內存資源。解決辦法：按照上文提到的方法，定期查詢shared pool中的非共享sql：對前40字節(jié)相同且數(shù)量超過1000的sql語句，以綁定變量的方式進行改寫。通過這種方法消除系統(tǒng)中存在的執(zhí)行次數(shù)多的非共享sql。性能預期：如果能夠按照上述的辦法硬，那么系統(tǒng)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在業(yè)務中率偏低時段數(shù)據(jù)庫的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低會影響中率。排序和哈希連接的執(zhí)行性能，目前PGA為3G，建議將PGA調大為4G，并繼續(xù)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緩沖池很低在業(yè)務時段數(shù)據(jù)庫的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。由于業(yè)務方面不同時間的數(shù)據(jù)是不一樣的，且keep的數(shù)據(jù)量遠大于keep緩沖池的大小，所以導致keep緩沖池很低。跟keep對象相關的業(yè)務方面的等待主要是單塊IO掃描等待，說明keep緩沖池的大小還不能滿足業(yè)務需要，考慮在將來數(shù)據(jù)庫擴容時繼續(xù)調大KEPP緩沖池。四、部分耗費資源大的SQL語句有待優(yōu)化在sspack中TOP SQL中，有部分耗費資源大的SQL語句可以優(yōu)化：SQL語句1：SQL原文:資源消耗情況：占總體sql使用資源的5.5執(zhí)行計劃：全表掃描表的統(tǒng)計信息：數(shù)據(jù)字典中表有5個數(shù)據(jù)塊。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表的實際大?。罕韺嶋H有256個數(shù)據(jù)塊，12114條。分析可以看到表的實際大小與數(shù)據(jù)字典中的大小嚴重不符

47、，而CBO優(yōu)化器是根據(jù)數(shù)據(jù)字典中的信息來執(zhí)行計劃的，由于數(shù)據(jù)字典中掃描。的表的高水標記之下只有5個數(shù)據(jù)塊，所以CBO選擇了全表考慮是否可以通過索引掃描來降低sql的數(shù)據(jù)表中創(chuàng)建索引的列：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發(fā)現(xiàn)ACCT_ITEM_TYPE_ID列的數(shù)值分布比較均勻，對這一列上面的索引進行掃描，效率會很高。優(yōu)化方法：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