根據(jù)statspack報(bào)表優(yōu)化oracle數(shù)據(jù)庫(kù)實(shí)例之“DB file scattered read”

oracle的等待事件是衡量oracle運(yùn)行狀況的重要依據(jù)及指標(biāo)。

等待事件的概念是在oracle7.0.1.2中引入的，大致有100個(gè)等待事件。在oracle8.0中這個(gè)數(shù)目增加到了大約150個(gè)，在oracle8i中大約有200個(gè)事件，到oracle9i時(shí)，等待事件增加到360個(gè)。

Oracle的等待事件主要有兩種類(lèi)型，即空閑（idle）等待事件和非空閑（non-idle）等待事件?？臻e事件指oracle正在等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫(kù)的時(shí)候，我們不用過(guò)多注意這部分事件。

非空閑等待事件專(zhuān)門(mén)針對(duì)oracle的活動(dòng)，指數(shù)據(jù)庫(kù)任務(wù)或應(yīng)用運(yùn)行過(guò)程中發(fā)生的等待，這些等待事件是我們?cè)谡{(diào)整數(shù)據(jù)庫(kù)的時(shí)候應(yīng)該關(guān)注與研究的。

常見(jiàn)的非空閑等待事件有：db file scattered read; db file sequential read; buffer busy waits; free buffer waits; enqueue; latch free; log file parallel write; log file sync.

Db file scattered read的產(chǎn)生

本文主要解釋了db file scattered read-DB文件分散讀取等待事件產(chǎn)生的原因與優(yōu)化的方法。

Db file scattered read等待事件通常顯示與全表掃描相關(guān)的等待。

當(dāng)數(shù)據(jù)庫(kù)進(jìn)行全表掃描時(shí)，基于性能的考慮，數(shù)據(jù)會(huì)分散（scattered）讀入buffer cache。如果這個(gè)等待事件筆記哦顯著，可能說(shuō)明對(duì)于某些全表掃描的表，沒(méi)有創(chuàng)建索引或者索引沒(méi)有有效利用。我們可能需要檢查這些數(shù)據(jù)表以便確定是否進(jìn)行了正確的設(shè)置。

然而這個(gè)等待事件并不總意味著性能底下，在某些條件下oracle會(huì)主動(dòng)使用全表掃描來(lái)替換索引掃描以提高性能，這和訪問(wèn)的數(shù)據(jù)量有關(guān)，在CBO下oracle會(huì)進(jìn)行更為智能的選擇，在RBO下oracle更傾向于使用索引。

因?yàn)槿頀呙璞恢糜贚RU（least recently used）列表的冷端（cold end），對(duì)于頻繁訪問(wèn)的較小的數(shù)據(jù)表，可以選擇把他們cache到內(nèi)存中，以避免反復(fù)讀取。

有兩種方法可以幫助我們找出全表掃描較多的sql語(yǔ)句。

Statspack的報(bào)表

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~???????????????????????????????????????????????????? % Total

Event?????????????????????????????????????????????? Waits??? Time (s) Ela Time

-------------------------------------------- ------------ ----------- --------

CPU time??????? ??????????????????????????????????????????????????246??? 50.79

db file sequential read??????????????????????????? 98,012???????? 208??? 43.01

db file scattered read????????????????????????????? 1,001????????? 11???? 2.20

direct path write????????????????? ?????????????????2,171?????????? 7???? 1.52

control file parallel write???????????????????????? 1,404?????????? 3????? .56

―――――――――

Buffer Gets??? Executions? Gets per Exec? %Total Time (s)? Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

????? 4,392,146??????????? 1??? 4,392,146.0?? 39.6??? 64.73??? 247.08? 719265629

Module: msmdsrv.exe

SELECT "DW"."D_TIME_DAY"."DAY_ID" , "DW"."D_PRODUCT_FUNCTION_SIM

S"."PRODUCT_FUNCTION_ID" , "DW"."D_PRODUCT_SCREEN_SIMS"."PRODUCT

_SCREEN_ID" , "DW"."D_PRODUCT_BRAND_SIMS"."ALL_ID" , "DW"."D_PRO

DUCT_BRAND_SIMS"."PRODUCT_BRAND_ID" , "DW"."D_ORG_STORE_CHANNEL_

TYPE_SIMS"."ALL_ID" , "DW"."D_ORG_STORE_CHANNEL_TYPE_SIMS"."CHAN

?

????? 1,092,126??????????? 6????? 182,021.0??? 9.8???? 8.54????? 8.33 2845961438

Module: JDBC Thin Client

??????????????????? select

???? bs.model_name,??????????????????????? decode(grouping(bs.fu

nction_name)+grouping(bs.spec_name)+ grouping(bs.screen_name)+gr

ouping(bs.model_name),????????????????? ??????1,bs.function_name

?||bs.spec_name ||bs.screen_name ||'小計(jì)',

?

??????? 625,322??????????? 2????? 312,661.0??? 5.6??? 16.21???? 24.55 4097549484

Module: JDBC Thin Client

??????????????????? select

???? bs.spec_name,??????????????????????? decode(grouping(bs.fun

ction_name)+grouping(bs.spec_name)+ grouping(bs.screen_name),

??????????????????????? 1 ,bs.function_name ||bs.spec_name ||'小

計(jì)',??????????????????????? 2, bs.function_name ||'小計(jì)',

?

??????? 604,296?????????? 14?????? 43,164.0??? 5.4??? 15.89???? 15.55 3794571418

Module: JDBC Thin Client

?select???????????? ??decode(grouping(ttt.category_name)+groupin

g(ttt.function_name)+grouping(ttt.spec_name)+grouping(ttt.model_

name)+grouping(ttt.is_master),0,ttt.model_name,2,ttt.category_na

me||'-'||ttt.function_name||'-'||ttt.spec_name||'-小計(jì)',3,ttt.ca

tegory_name||'-'||ttt.function_name||'-小計(jì)',4,ttt.category_name

?

??????? 560,367?????????? 29?????? 19,323.0??? 5.1???? 2.72????? 2.74 1125417254

select r.ID,r.NAME,r.INFORMATION from USER_USERGROUP u,ROLE r,US

ERGROUP_ROLE

d u.USERID= :1

?

??????? 520,226?????????? 90??????? 5,780.3??? 4.7??? 11.52???? 14.24? 645606369

Module: JDBC Thin Client

BEGIN ctl.pkg_public_int.get_data(:1,:2,:3,:4) ; END;

?

??????? 367,777??????????? 2????? 183,888.5??? 3.3???? 3.10????? 3.75 1644183172

Module: JDBC Thin Client

??????????????????? select

???? bs.model_name,??????????????????????? decode(grouping(bs.fu

nction_name)+grouping(bs.spec_name)+ grouping(bs.screen_name)+gr

ouping(bs.model_name),??????????????????????? 1,bs.function_name

?||bs.spec_name ||bs.screen_name ||'小計(jì)',

?

??????? 360,107??????????? 2????? 180,053.5??? 3.2???? 2.80????? 2.74 2652674913

―――――――――――――――

注意到以上很多查詢導(dǎo)致的Buffer Gets都非常龐大，我們非常有理由懷疑索引存在問(wèn)題，甚至缺少必要的索引。以上記錄的是sql的片段，通過(guò)hash value值結(jié)合v$sqltext我們可以獲得完整的sql語(yǔ)句。

?

SELECT * FROM v$sqltext WHERE hash_value = 4097549484

ORDER BY piece

?

V$session_longops動(dòng)態(tài)性能視圖

??? V$session_longops動(dòng)態(tài)性能視圖中記錄了長(zhǎng)時(shí)間運(yùn)行（超過(guò)6秒）的事務(wù)，可能很多是全表掃描操作（不管怎樣，這部分信息都值得我們注意）。當(dāng)db file scattered read等待時(shí)間比較顯著時(shí)，可以結(jié)合v$session_longops視圖來(lái)進(jìn)行診斷。

1，? 檢索出長(zhǎng)時(shí)間運(yùn)行事件相關(guān)表

??? SELECT target,COUNT(*) c FROM v$session_longops

GROUP BY target

ORDER BY c DESC

Target?????????? c

ODS.SM_SALES??? 26

DW.FS_DISTRIBUTION_BRANCH_PRODUCT 16

ODS.TL_PICKLISTITEM???? 13

ODS.CR_SHIPMENT_ITEM 12

ODS.CR_SHIPMENT_STATUS??? 12

ODS.CR_ARP_PLAN??? 11

ODS.CR_FACILITY_DAILY_PSI_SUMMARY??? 11

ODS.CR_RECEIPT_BALANCE_DAILY_D 11

ODS.CR_SHIPMENT_ATTRIBUTE???? 11

ODS.CR_ORDER_INFO????? 11

ODS.CR_ORDER_HEADER 11

TODS.CR_RECEIPT_ATTRIBUTE???? 11

TODS.CR_PARTY_ATTRIBUTE?? 11

ODS.CR_SHIPMENT??? 9

ODS.CR_CUSTOMER_DAILY_PSI_SUMMARY?????? 6

ODS.FI_REPORT_DATA_H 6

FBI.LOG_AP 3

CTL.ETL_LOG????? 2

ODS.FI_V_HUIKUAN?? 2

(stale) obj# 303378?????? 1

ODS.CR_INVENTORY_ITEM_VARIANCE 1

DW.F_EXPIATION_CRM???? 1

2，? 檢索得到長(zhǎng)時(shí)間執(zhí)行事務(wù)的具體sql語(yǔ)句的hash_value

SELECT DISTINCT sql_hash_value FROM v$session_longops

WHERE target = 'ODS.SM_SALES'

375479500

3850935052

3，? 找出具體執(zhí)行sql語(yǔ)句

SELECT * FROM v$sqltext WHERE hash_value = 375479500

ORDER BY piece

整理得到的sql語(yǔ)句結(jié)果，

SELECT TO_CHAR(S.ENTRY_DATE, 'yyyymmdd'),

???????? IO.STORE_ID,

???????? IPI.PRODUCT_ID,

???????? SUM(S.QUANTITY),

???????? SUM(S.TOTAL_AMOUNT),

???????? SUBSTR(IPI.PRODUCT_ID, 1, 10),

???????? SUM(SUM(S.QUANTITY)) OVER(PARTITION BY TO_CHAR(S.ENTRY_DATE, 'yyyymm'), IO.STORE_ID, IPI.PRODUCT_ID ORDER BY TO_CHAR(S.ENTRY_DATE, 'yyyymmdd')),

???????? SUM(SUM(S.TOTAL_AMOUNT)) OVER(PARTITION BY TO_CHAR(S.ENTRY_DATE, 'yyyymm'), IO.STORE_ID, IPI.PRODUCT_ID ORDER BY TO_CHAR(S.ENTRY_DATE, 'yyyymmdd'))

??? FROM ODS.I_PRODUCT_INFO?????????? IPI,

???????? ODS.I_ORG_STORE_RELATIONSHIP IO,

???????? ODS.SM_SALES???????????????? S

?? WHERE S.PRODUCT_ID = IPI.PRODUCT_ID

???? AND S.MARKET_PLACE_ID = IO.STORE_ID

???? AND TO_CHAR(S.ENTRY_DATE, 'yyyymmdd') BETWEEN '20081108' AND '20081110'

?? GROUP BY S.ENTRY_DATE, IPI.PRODUCT_ID, IO.STORE_ID

4，并在pl/sql develop開(kāi)發(fā)工具中獲得執(zhí)行語(yǔ)句的查詢執(zhí)行計(jì)劃如下。

SELECT STATEMENT, GOAL = CHOOSE?????????????????????????????

?WINDOW SORT????????????????????????????

? SORT GROUP BY????????????????????????????????

?? NESTED LOOPS OUTER?????????????????????????????????

??? NESTED LOOPS??????????????????????????????

???? TABLE ACCESS FULL?????? ODS?????? SM_SALES?????????????????

???? INDEX UNIQUE SCAN????? ODS?????? I_ORG_STORE_RELATIONSHIP_PK??????????????

??? INDEX UNIQUE SCAN ODS?????? I_PRODUCT_INFO_PK????????????????????

5，? 分析查詢相關(guān)的幾個(gè)源數(shù)據(jù)表

表名稱

Row number

關(guān)聯(lián)字段索引

ODS.I_PRODUCT_INFO??????? IPI

23779

Yes

ODS.I_ORG_STORE_RELATIONSHIP IO

9632

Yes

ODS.SM_SALES???????????????? S

6147142

Yes

??? 從對(duì)源表的分析數(shù)據(jù)我們可以看到，目前執(zhí)行方式的問(wèn)題有兩個(gè)，首先是使用大表（擁有600萬(wàn)條以上記錄的ods.sm_sales）做了嵌套循環(huán)的驅(qū)動(dòng)表；其次，就是這個(gè)大表上的索引并沒(méi)有得到合理的利用。從而導(dǎo)致本語(yǔ)句的執(zhí)行時(shí)間25秒。

6，優(yōu)化方法

??? 首先我們可以使用oracle的hint，強(qiáng)制在大表ods.sm_sales上使用索引。其次由于在這三個(gè)表關(guān)聯(lián)時(shí)，另外兩個(gè)小表ipi和io都是需要跟中間大表s進(jìn)行關(guān)聯(lián)，所以使用nested loop將無(wú)法有效使用更多的索引進(jìn)行關(guān)聯(lián)，所以建議使用ordered，use_hash結(jié)合swap_join_input使得查詢按照hash join方式，并在兩次hashjoin時(shí)都將小表放在驅(qū)動(dòng)表的位置上。執(zhí)行優(yōu)化后的語(yǔ)句入下。優(yōu)化后執(zhí)行時(shí)間為5秒 。

SELECT /*+ index(s SM_SALES_I5) ordered use_hash(ipi,s,io) swap_join_inputs(io)*/

???????? TO_CHAR(S.ENTRY_DATE, 'yyyymmdd'),

???????? IO.STORE_ID,

???????? IPI.PRODUCT_ID,

???????? SUM(S.QUANTITY),

???????? SUM(S.TOTAL_AMOUNT),

???????? SUBSTR(IPI.PRODUCT_ID, 1, 10),

???????? SUM(SUM(S.QUANTITY)) OVER(PARTITION BY TO_CHAR(S.ENTRY_DATE, 'yyyymm'),IO.STORE_ID, IPI.PRODUCT_ID ORDER BY TO_CHAR(S.ENTRY_DATE, 'yyyymmdd')),

???????? SUM(SUM(S.TOTAL_AMOUNT)) OVER(PARTITION BY TO_CHAR(S.ENTRY_DATE, 'yyyymm'),IO.STORE_ID, IPI.PRODUCT_ID ORDER BY TO_CHAR(S.ENTRY_DATE, 'yyyymmdd'))

??? FROM ODS.I_PRODUCT_INFO?????????? IPI? ,

???????? ODS.SM_SALES???????????????? S,

???????? ODS.I_ORG_STORE_RELATIONSHIP IO??????

?? WHERE S.MARKET_PLACE_ID = IO.STORE_ID

???? AND S.PRODUCT_ID = IPI.PRODUCT_ID

???? AND TO_CHAR(S.ENTRY_DATE, 'yyyymmdd') BETWEEN '20081108' AND '20081110'

?? GROUP BY S.ENTRY_DATE, IPI.PRODUCT_ID, IO.STORE_ID