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

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

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

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

非空閑等待事件專門針對oracle的活動，指數(shù)據(jù)庫任務(wù)或應(yīng)用運行過程中發(fā)生的等待，這些等待事件是我們在調(diào)整數(shù)據(jù)庫的時候應(yīng)該關(guā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ù)庫進(jìn)行全表掃描時，基于性能的考慮，數(shù)據(jù)會分散（scattered）讀入buffer cache。如果這個等待事件筆記哦顯著，可能說明對于某些全表掃描的表，沒有創(chuàng)建索引或者索引沒有有效利用。我們可能需要檢查這些數(shù)據(jù)表以便確定是否進(jìn)行了正確的設(shè)置。

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

因為全表掃描被置于LRU（least recently used）列表的冷端（cold end），對于頻繁訪問的較小的數(shù)據(jù)表，可以選擇把他們cache到內(nèi)存中，以避免反復(fù)讀取。

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

Statspack的報表

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 ||'小計',

?

??????? 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 ||'小

計',??????????????????????? 2, bs.function_name ||'小計',

?

??????? 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||'-小計',3,ttt.ca

tegory_name||'-'||ttt.function_name||'-小計',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 ||'小計',

?

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

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

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

?

SELECT * FROM v$sqltext WHERE hash_value = 4097549484

ORDER BY piece

?

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

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

1，? 檢索出長時間運行事件相關(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í)行事務(wù)的具體sql語句的hash_value

SELECT DISTINCT sql_hash_value FROM v$session_longops

WHERE target = 'ODS.SM_SALES'

375479500

3850935052

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

SELECT * FROM v$sqltext WHERE hash_value = 375479500

ORDER BY piece

整理得到的sql語句結(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開發(fā)工具中獲得執(zhí)行語句的查詢執(zhí)行計劃如下。

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)的幾個源數(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

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

6，優(yōu)化方法

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