国产99久久精品_欧美日本韩国一区二区_激情小说综合网_欧美一级二级视频_午夜av电影_日本久久精品视频

最新文章專(zhuān)題視頻專(zhuān)題問(wèn)答1問(wèn)答10問(wèn)答100問(wèn)答1000問(wèn)答2000關(guān)鍵字專(zhuān)題1關(guān)鍵字專(zhuān)題50關(guān)鍵字專(zhuān)題500關(guān)鍵字專(zhuān)題1500TAG最新視頻文章推薦1 推薦3 推薦5 推薦7 推薦9 推薦11 推薦13 推薦15 推薦17 推薦19 推薦21 推薦23 推薦25 推薦27 推薦29 推薦31 推薦33 推薦35 推薦37視頻文章20視頻文章30視頻文章40視頻文章50視頻文章60 視頻文章70視頻文章80視頻文章90視頻文章100視頻文章120視頻文章140 視頻2關(guān)鍵字專(zhuān)題關(guān)鍵字專(zhuān)題tag2tag3文章專(zhuān)題文章專(zhuān)題2文章索引1文章索引2文章索引3文章索引4文章索引5123456789101112131415文章專(zhuān)題3
問(wèn)答文章1 問(wèn)答文章501 問(wèn)答文章1001 問(wèn)答文章1501 問(wèn)答文章2001 問(wèn)答文章2501 問(wèn)答文章3001 問(wèn)答文章3501 問(wèn)答文章4001 問(wèn)答文章4501 問(wèn)答文章5001 問(wèn)答文章5501 問(wèn)答文章6001 問(wèn)答文章6501 問(wèn)答文章7001 問(wèn)答文章7501 問(wèn)答文章8001 問(wèn)答文章8501 問(wèn)答文章9001 問(wèn)答文章9501
當(dāng)前位置: 首頁(yè) - 科技 - 知識(shí)百科 - 正文

傾斜值傳入導(dǎo)致sql資源消耗升高的案例分析

來(lái)源:懂視網(wǎng) 責(zé)編:小采 時(shí)間:2020-11-09 13:01:17
文檔

傾斜值傳入導(dǎo)致sql資源消耗升高的案例分析

傾斜值傳入導(dǎo)致sql資源消耗升高的案例分析:局方監(jiān)控系統(tǒng)反饋2014-12-31 19:30:00-20:00:00這段時(shí)間db time上升較大,sql_id 88wdzpr9mv2wy消耗了12%以上的db time sql在shared pool的執(zhí)行計(jì)劃 sys@CRMDB4select * from table(dbms_xplan.display_curs
推薦度:
導(dǎo)讀傾斜值傳入導(dǎo)致sql資源消耗升高的案例分析:局方監(jiān)控系統(tǒng)反饋2014-12-31 19:30:00-20:00:00這段時(shí)間db time上升較大,sql_id 88wdzpr9mv2wy消耗了12%以上的db time sql在shared pool的執(zhí)行計(jì)劃 sys@CRMDB4select * from table(dbms_xplan.display_curs

局方監(jiān)控系統(tǒng)反饋2014-12-31 19:30:00-20:00:00這段時(shí)間db time上升較大,sql_id 88wdzpr9mv2wy消耗了12%以上的db time sql在shared pool的執(zhí)行計(jì)劃 sys@CRMDB4select * from table(dbms_xplan.display_cursor('88wdzpr9mv2wy')); PLAN_TABLE_OUTPUT ---

局方監(jiān)控系統(tǒng)反饋2014-12-31 19:30:00-20:00:00這段時(shí)間db time上升較大,sql_id 88wdzpr9mv2wy消耗了12%以上的db time

sql在shared pool的執(zhí)行計(jì)劃

sys@CRMDB4>select * from table(dbms_xplan.display_cursor('88wdzpr9mv2wy'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 88wdzpr9mv2wy, child number 0
-------------------------------------
SELECT RECEPTION_ID,
TRADE_CODE,
AMOUNT,
BANK_TYPE,
ACCOUNT_TYPE,
SRC_ACCOUNT_ID,
DEST_ACCOUNT_ID,
DEAL_TIME,
RESULT,
REC_TYPE,
STATUS,
entity_id,
balance,
is_rollback
FROM (SELECT b.RECEPTION_ID,
b.TRADE_CODE,
b.AMOUNT,
b.BANK_TYPE,
b.ACCOUNT_TYPE,
b.SRC_ACCOUNT_ID,
b.DEST_ACCOUNT_ID,
b.DEAL_TIME,
b.RESULT,
b.REC_TYPE,
b.STATUS,
b.entity_id,
b.balance,
b.is_rollback,
rownum AS rn
FROM (SELECT t.RECEPTION_ID,
t.TRADE_CODE,
t.AMOUNT,
t.BANK_TYPE,
t.ACCOUNT_TYPE,
t.SRC_ACCOUNT_ID,
t.DEST_ACCOUNT_ID,
t.DEAL_TIME,
t.RESULT,
m.REC_TYPE,
m.STATUS,
m.entity_id,
m.balance,
m.is_rollback
FROM cvs_rec_banktask t, cvs_reception m
WHERE t.RECEPTION_ID = m.RECEPTION_ID
AND t.DEAL_TIME BETWEEN to_date(:StartData, 'yyyymmdd') AND
to_date(:EndtData, 'yyyymmdd') + 1
AND t.ACCOUNT_TYPE = :AccountType
AND m.org_id = :SiteId
AND m.region = t.region
AND m.region = :Region
ORDER BY t.DEAL_TIME DESC) b
WHERE rownum <= to_number(:up) * to_number(:down))
WHERE rn > to_number(:up) * to_number(:down) - to_number(:down);

Plan hash value: 511419205

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1239 | 245K| 4582 (1)| 00:00:55 |
|* 1 | VIEW | | 1239 | 245K| 4582 (1)| 00:00:55 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1239 | 229K| 4582 (1)| 00:00:55 |
|* 4 | SORT ORDER BY STOPKEY | | 1239 | 168K| 4582 (1)| 00:00:55 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | CVS_REC_BANKTASK | 1 | 77 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1239 | 168K| 4581 (1)| 00:00:55 |
|* 8 | TABLE ACCESS BY INDEX ROWID| CVS_RECEPTION | 1239 | 76818 | 862 (1)| 00:00:11 |
|* 9 | INDEX SKIP SCAN | IDX_CVS_RECEPTION | 1239 | | 101 (0)| 00:00:02 |
|* 10 | INDEX RANGE SCAN | IDX_REC_BANKTASK | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">TO_NUMBER(:UP)*TO_NUMBER(:DOWN)-TO_NUMBER(:DOWN))
2 - filter(ROWNUM<=TO_NUMBER(:UP)*TO_NUMBER(:DOWN))
4 - filter(ROWNUM<=TO_NUMBER(:UP)*TO_NUMBER(:DOWN))
5 - filter(TO_DATE(:ENDTDATA,'yyyymmdd')+1>=TO_DATE(:STARTDATA,'yyyymmdd'))
6 - filter("T"."DEAL_TIME"<=TO_DATE(:ENDTDATA,'yyyymmdd')+1 AND
"T"."DEAL_TIME">=TO_DATE(:STARTDATA,'yyyymmdd') AND "T"."ACCOUNT_TYPE"=TO_NUMBER(:ACCOUNTTYPE)
AND "T"."REGION"=TO_NUMBER(:REGION))
8 - filter("M"."REGION"=TO_NUMBER(:REGION))
9 - access("M"."ORG_ID"=:SITEID)
filter("M"."ORG_ID"=:SITEID)
10 - access("T"."RECEPTION_ID"="M"."RECEPTION_ID")

sql的歷史執(zhí)行計(jì)劃:

sys@CRMDB4>select * from table(dbms_xplan.display_awr('88wdzpr9mv2wy'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 88wdzpr9mv2wy
--------------------
SELECT RECEPTION_ID,
TRADE_CODE,
AMOUNT,
BANK_TYPE,
ACCOUNT_TYPE,
SRC_ACCOUNT_ID,
DEST_ACCOUNT_ID,
DEAL_TIME,
RESULT,
REC_TYPE,
STATUS,
entity_id,
balance,
is_rollback
FROM (SELECT b.RECEPTION_ID,
b.TRADE_CODE,
b.AMOUNT,
b.BANK_TYPE,
b.ACCOUNT_TYPE,
b.SRC_ACCOUNT_ID,
b.DEST_ACCOUNT_ID,
b.DEAL_TIME,
b.RESULT,
b.REC_TYPE,
b.STATUS,
b.entity_id,
b.balance,
b.is_rollback,
rownum AS rn
FROM (SELECT t.RECEPTION_ID,
t.TRADE_CODE,
t.AMOUNT,
t.BANK_TYPE,
t.ACCOUNT_TYPE,
t.SRC_ACCOUNT_ID,
t.DEST_ACCOUNT_ID,
t.DEAL_TIME,
t.RESULT,
m.REC_TYPE,
m.STATUS,
m.entity_id,
m.balance,
m.is_rollback
FROM cvs_rec_banktask t, cvs_reception m
WHERE t.RECEPTION_ID = m.RECEPTION_ID
AND t.DEAL_TIME BETWEEN to_date(:StartData, 'yyyymmdd') AND
to_date(:EndtData, 'yyyymmdd') + 1
AND t.ACCOUNT_TYPE = :AccountType
AND m.org_id = :SiteId
AND m.region = t.region
AND m.region = :Region
ORDER BY t.DEAL_TIME DESC) b
WHERE rownum <= to_number(:up) * to_number(:down))
WHERE rn > to_number(:up) * to_number(:down) - to_number(:down);


Plan hash value: 511419205

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3453 (100)| |
| 1 | VIEW | | 913 | 180K| 3453 (1)| 00:00:42 |
| 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 913 | 169K| 3453 (1)| 00:00:42 |
| 4 | SORT ORDER BY STOPKEY | | 913 | 123K| 3453 (1)| 00:00:42 |
| 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | CVS_REC_BANKTASK | 1 | 77 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 913 | 123K| 3452 (1)| 00:00:42 |
| 8 | TABLE ACCESS BY INDEX ROWID| CVS_RECEPTION | 913 | 56606 | 711 (0)| 00:00:09 |
| 9 | INDEX SKIP SCAN | IDX_CVS_RECEPTION | 913 | | 74 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | IDX_REC_BANKTASK | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------


36 rows selected.

sql歷史中只出現(xiàn)過(guò)一種執(zhí)行計(jì)劃,這個(gè)表示該sql在awr中沒(méi)有出現(xiàn)多種執(zhí)行計(jì)劃而導(dǎo)致性能出現(xiàn)差異。

相關(guān)表的統(tǒng)計(jì)信息:

CVS_RECEPTION表的統(tǒng)計(jì)信息:
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
CVS_RECEPTION 2,257,580 305,08 0 0 90 YES 112,879 01-03-2015

Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
REGION 1 1.00000000 1 0 112,879 01-03-2015
ORG_ID 1,822 .00054885 1 0 112,879 01-03-2015
REC_TYPE 9 .11111111 1 0 112,879 01-03-2015
ENTITY_ID 1,228,762 .00000081 1 5,820 112,588 01-03-2015

Index Leaf Distinct Number AV Av Cluster Date
Name BLV Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
PK_CVS_RECEPTION 2 14,508 2,175,180 2,175,180 1 1 2,086,155 01-03-2015
IDX_CVS_REGION_REC_DATE 2 13,965 1,671,330 2,279,948 1 1 2,181,132 01-03-2015
IDX_CVS_RECEPTION 2 18,852 1,822 2,296,873 23 1,694 1,384,453 01-03-2015
IDX_CVS_RECEPTION_ENTITY_ID 2 12,855 1,228,762 2,324,396 1 1 2,318,265 01-03-2015
IDX_CVS_RECEPTION_FORMNUM 2 6,760 1 2,225,924 6,760 35,358 35,358 01-03-2015

Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
IDX_CVS_RECEPTION REC_TYPE 1 VARCHAR2(10) NOT NULL
ORG_ID 2 VARCHAR2(8) NOT NULL

CVS_REC_BANKTASK表的統(tǒng)計(jì)信息:
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
CVS_REC_BANKTASK 3,899,140 452,98 0 0 77 YES 194,957 01-04-2015

Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
REGION 1 1.00000000 1 0 194,957 01-04-2015
RECEPTION_ID 3,899,140 .00000026 1 0 194,957 01-04-2015
ACCOUNT_TYPE 1 1.00000000 1 0 194,957 01-04-2015
DEAL_TIME 2,644,869 .00000038 1 0 194,957 01-04-2015

Index Leaf Distinct Number AV Av Cluster Date
Name BLV Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
IDX_REC_BANKTASK 2 25,300 3,721,460 3,721,460 1 1 3,503,660 01-04-2015

Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
IDX_REC_BANKTASK RECEPTION_ID 1 VARCHAR2(32) NOT NULL

sql的歷史執(zhí)行信息:

sys@CRMDB4>@sqlhis_add.sql
Enter value for sql_id: 88wdzpr9mv2wy
old 27: and a.sql_id = '&sql_id'
new 27: and a.sql_id = '88wdzpr9mv2wy'

BEGIN_TIME INSTANCE_NUMBER MODULE PLAN_HASH_VALUE EXEC PER_GET PER_ROWS TIME_S PER_READ
------------------- --------------- ------------------------------ ---------------- ---------- ---------- ---------- ---------- ----------
2015-01-05 08:00:30 1 tpengine@winftux1 (TNS V1-V3) 511419205 70 93172 8.9 14.91 719.01
2015-01-04 20:30:29 1 tpengine@winftux1 (TNS V1-V3) 511419205 119 98962 9.1 4.61 473.61
2015-01-04 19:00:34 1 tpengine@winftux1 (TNS V1-V3) 511419205 727 32261 28.2 .58 47.44
2015-01-04 17:30:13 1 tpengine@winftux1 (TNS V1-V3) 511419205 180 78200 21.1 4.24 425.64
2015-01-04 16:30:54 1 tpengine@winftux1 (TNS V1-V3) 511419205 70 277336 9.8 11.37 584.23
2015-01-04 09:30:18 1 tpengine@winftux1 (TNS V1-V3) 511419205 74 181718 14.5 15.93 988.64
2015-01-04 07:30:17 1 tpengine@winftux1 (TNS V1-V3) 511419205 12 17221 9.2 18.77 2430.75
2015-01-03 22:00:19 1 tpengine@winftux1 (TNS V1-V3) 511419205 47 0 9.8 15.35 867.7
2015-01-03 18:00:39 1 tpengine@winftux1 (TNS V1-V3) 511419205 93 9879 8.9 6 344.46
2015-01-03 17:30:32 1 tpengine@winftux1 (TNS V1-V3) 511419205 143 94887 9.2 3.04 248.31
2015-01-03 15:30:04 1 tpengine@winftux1 (TNS V1-V3) 511419205 40 467928 9.6 6.06 272.18
2015-01-03 15:00:40 1 tpengine@winftux1 (TNS V1-V3) 511419205 88 38890 7.7 8.39 669.23
2015-01-02 18:00:28 1 tpengine@winftux1 (TNS V1-V3) 511419205 436 63315 26.1 1.02 82.67
2015-01-02 10:30:13 1 tpengine@winftux1 (TNS V1-V3) 511419205 740 707283 27 6.15 9.71
2015-01-02 10:00:06 1 tpengine@winftux1 (TNS V1-V3) 511419205 256 531298 22.4 5.26 138.29
2015-01-02 07:30:31 1 tpengine@winftux1 (TNS V1-V3) 511419205 29 12594 7.2 11.02 1600.21
2015-01-01 19:00:04 1 tpengine@winftux1 (TNS V1-V3) 511419205 143 24895 12.3 6.37 234.63
2015-01-01 17:30:13 1 tpengine@winftux1 (TNS V1-V3) 511419205 447 46359 19.2 1.05 70.47
2015-01-01 16:00:53 1 tpengine@winftux1 (TNS V1-V3) 511419205 447 45576 19.5 .68 48.81
2015-01-01 11:30:11 1 tpengine@winftux1 (TNS V1-V3) 511419205 376 46110 21.5 1.63 116.57
2015-01-01 10:30:36 1 tpengine@winftux1 (TNS V1-V3) 511419205 416 47588 21.4 1.21 79.76
2014-12-31 20:00:08 1 tpengine@winftux1 (TNS V1-V3) 511419205 1071 726326 28 6.75 21.55
2014-12-31 19:30:01 1 tpengine@winftux1 (TNS V1-V3) 511419205 3057 760690 28.9 7.35 18.57
2014-12-31 14:30:51 1 tpengine@winftux1 (TNS V1-V3) 511419205 150 514830 21.1 12.26 438.05
2014-12-30 20:30:03 1 tpengine@winftux2 (TNS V1-V3) 511419205 74 11074 8.8 9.14 631.47
2014-12-30 19:30:26 1 tpengine@winftux2 (TNS V1-V3) 511419205 94 12433 8.4 8.28 545.99
2014-12-30 18:30:12 1 tpengine@winftux2 (TNS V1-V3) 511419205 12578 135489 30 1.59 1.25
2014-12-30 18:00:05 1 tpengine@winftux2 (TNS V1-V3) 511419205 7251 132103 29.8 1.57 7.55
2014-12-30 14:30:17 1 tpengine@winftux2 (TNS V1-V3) 511419205 70 17396 8.3 11.17 814.97

29 rows selected.

通過(guò)對(duì)比每半個(gè)小時(shí)的平均邏輯讀部分時(shí)間段有較大的波動(dòng),在2014-12-31 19:30:01到2014-12-31 20:00:01時(shí)間段這個(gè)sql執(zhí)行次數(shù)達(dá)到了3057次,每次平均邏輯讀達(dá)到了76萬(wàn)以上,而有些時(shí)間段的這個(gè)sql的平均邏輯讀只有幾萬(wàn),這個(gè)表示通過(guò)綁定變量傳遞過(guò)來(lái)的值會(huì)有傾斜值。

對(duì)比執(zhí)行計(jì)劃造成邏輯讀在不同時(shí)間段存在差異的只可能是tbcs.cvs_reception表,而這個(gè)表是作為nested loop循環(huán)的驅(qū)動(dòng)表,對(duì)應(yīng)的執(zhí)行計(jì)劃和謂詞部分如下:

|* 8 | TABLE ACCESS BY INDEX ROWID| CVS_RECEPTION | 1239 | 76818 | 862 (1)| 00:00:11 |
|* 9 | INDEX SKIP SCAN | IDX_CVS_RECEPTION | 1239 | | 101 (0)| 00:00:02 |

8 - filter("M"."REGION"=TO_NUMBER(:REGION))
9 - access("M"."ORG_ID"=:SITEID)
filter("M"."ORG_ID"=:SITEID)

看來(lái)造成邏輯讀存在差異的只可能是org_id和region兩列,而region這列根據(jù)表的統(tǒng)計(jì)信息只有一組distinct value,那么只可能是org_id這個(gè)對(duì)應(yīng)的綁定變量:SITEID存在有傾斜值,造成了平均邏輯讀在這個(gè)時(shí)間段特別大,然而sql在這個(gè)時(shí)間段執(zhí)行頻率又特別高,進(jìn)而導(dǎo)致消耗了較多的db time

來(lái)看看表tbcs.cvs_reception的org_id傾斜值

SQL> select * from (select org_id,count(*) from tbcs.cvs_reception group by org_id order by count(*) desc) where rownum<20;

ORG_ID COUNT(*)
-------- ----------
11001259 310378
11001012 54970
11921362 45549
11001413 43398
11001585 32380
11001721 31680
11001709 30608
11001711 30524
11001586 30341
11001710 29909
11001708 29734
11001707 29733
11001715 29332
11001705 28501
11001716 27750
11001361 27555
11001712 27412
11001713 26680
11001360 26611

19 rows selected


SQL> select 2257580*0.00054885 from dual;

2257580*0.00054885
------------------
1239.072783

優(yōu)化器評(píng)估的INDEX SKIP SCAN IDX_CVS_RECEPTION 部分返回的rows是1239(在沒(méi)有直方圖的情況下,優(yōu)化器計(jì)算等值謂詞的選擇selectivy公式是1/distinct*((num_rows-null_rows)/num_rows))

oracle抓取綁定變量的規(guī)律有兩種:
1 硬解析的sql被執(zhí)行時(shí),oracle會(huì)抓取該sql的綁定變量
2 軟解析/軟軟解析的sql重復(fù)執(zhí)行時(shí),oracle也會(huì)抓取綁定變量,不過(guò)這里oracle只會(huì)每隔15分鐘抓取一次綁定變量,這里抓取的值不一定具有代表性。

SQL> select value_string, last_captured
2 from dba_hist_sqlbind
3 where sql_id = '88wdzpr9mv2wy'
4 and name = ':SITEID'
5 order by last_captured desc
6 ;

VALUE_STRING LAST_CAPTURED
-------------------------------------------------------------------------------- ------------------------------
11876365 2015/1/4 20:58:23
11791996 2015/1/4 19:25:19
11863035 2015/1/4 17:51:34
11001259 2015/1/4 16:49:52
11972820 2015/1/4 9:54:02
11167400 2015/1/4 7:50:47
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11877553 2015/1/1 1:01:37
11257437 2014/12/31 20:30:32
11001259 2014/12/31 20:00:15
11001259 2014/12/31 15:00:11
11289153 2014/12/30 20:49:37
11587654 2014/12/30 19:47:02
11001012 2014/12/30 18:53:50
11001585 2014/12/30 18:23:50
11001262 2014/12/30 14:54:52
11001454 2014/12/30 12:44:47
11001418 2014/12/30 7:53:54
11872880 2014/12/27 23:29:56
11872880 2014/12/27 23:29:56
11872880 2014/12/27 23:29:56
11872880 2014/12/27 23:29:56
11872880 2014/12/27 23:29:56
11872880 2014/12/27 23:29:56

36 rows selected

這里運(yùn)氣可能比較好,oracle這里在2014/12/31 20:00:15抓取的bind value剛好是傾斜值11001259(如果剛好故障時(shí)間段抓到是一個(gè)沒(méi)有傾斜性的值,大家也不要判定覺(jué)得這個(gè)sql在這個(gè)時(shí)間段是沒(méi)有傳入傾斜值的),這個(gè)值實(shí)際通過(guò)index skip scan部分要返回310378條數(shù)據(jù),而這里又要走nested loop的方式,相當(dāng)于底層的被驅(qū)動(dòng)表CVS_REC_BANKTASK要走310378次index range scan,正是循環(huán)次數(shù)的增多導(dǎo)致這個(gè)sql會(huì)消耗較多的IO資源。

SQL> select name, last_captured, value_string, datatype_string
2 from dba_hist_sqlbind
3 where sql_id = '88wdzpr9mv2wy'
4 and last_captured =
5 to_date('2014/12/31 20:00:15', 'yyyy-mm-dd hh24:mi:ss')
6 ;

NAME LAST_CAPTURED VALUE_STRING DATATYPE_STRING
------------------------------ ----------------------------------- ------------------------------ ---------------
:DOWN 2014/12/31 20:00:15 30 VARCHAR2(32)
:DOWN 2014/12/31 20:00:15 30 VARCHAR2(32)
:UP 2014/12/31 20:00:15 101 VARCHAR2(32)
:DOWN 2014/12/31 20:00:15 30 VARCHAR2(32)
:UP 2014/12/31 20:00:15 101 VARCHAR2(32)
:REGION 2014/12/31 20:00:15 11 VARCHAR2(32)
:SITEID 2014/12/31 20:00:15 11001259 VARCHAR2(32)
:ACCOUNTTYPE 2014/12/31 20:00:15 30 VARCHAR2(32)
:ENDTDATA 2014/12/31 20:00:15 20141231 VARCHAR2(32)
:STARTDATA 2014/12/31 20:00:15 20141231 VARCHAR2(32)

10 rows selected

帶入具體的bind value值,來(lái)驗(yàn)證sql的資源消耗

variable down varchar2(32);
variable up varchar2(32);
variable region varchar2(32);
variable siteid varchar2(32);
variable ACCOUNTTYPE varchar2(32);
variable ENDTDATA varchar2(32);
variable STARTDATA varchar2(32);
exec :down:='30';
exec :up:='101';
exec :region:='11';
exec :siteid:='11001259';
exec :ACCOUNTTYPE:='30';
exec :ENDTDATA:='20141231';
exec :STARTDATA:='20141231';

sys@CRMDB4>SELECT RECEPTION_ID,
2 TRADE_CODE,
3 AMOUNT,
4 BANK_TYPE,
5 ACCOUNT_TYPE,
6 SRC_ACCOUNT_ID,
7 DEST_ACCOUNT_ID,
8 DEAL_TIME,
9 RESULT,
10 REC_TYPE,
11 STATUS,
12 entity_id,
13 balance,
14 is_rollback
15 FROM (SELECT b.RECEPTION_ID,
16 b.TRADE_CODE,
17 b.AMOUNT,
18 b.BANK_TYPE,
19 b.ACCOUNT_TYPE,
20 b.SRC_ACCOUNT_ID,
21 b.DEST_ACCOUNT_ID,
22 b.DEAL_TIME,
23 b.RESULT,
24 b.REC_TYPE,
25 b.STATUS,
26 b.entity_id,
27 b.balance,
28 b.is_rollback,
29 rownum AS rn
30 FROM (SELECT t.RECEPTION_ID,
31 t.TRADE_CODE,
32 t.AMOUNT,
33 t.BANK_TYPE,
34 t.ACCOUNT_TYPE,
35 t.SRC_ACCOUNT_ID,
36 t.DEST_ACCOUNT_ID,
37 t.DEAL_TIME,
38 t.RESULT,
39 m.REC_TYPE,
40 m.STATUS,
41 m.entity_id,
42 m.balance,
43 m.is_rollback
44 FROM tbcs.cvs_rec_banktask t, tbcs.cvs_reception m
45 WHERE t.RECEPTION_ID = m.RECEPTION_ID
46 AND t.DEAL_TIME BETWEEN to_date(:StartData, 'yyyymmdd') AND
47 to_date(:EndtData, 'yyyymmdd') + 1
48 AND t.ACCOUNT_TYPE = :AccountType
49 AND m.org_id = :SiteId
50 AND m.region = t.region
51 AND m.region = :Region
52 ORDER BY t.DEAL_TIME DESC) b
53 WHERE rownum <= to_number(:up) * to_number(:down))
54 WHERE rn > to_number(:up) * to_number(:down) - to_number(:down);

30 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 511419205

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1239 | 245K| 4582 (1)| 00:00:55 |
|* 1 | VIEW | | 1239 | 245K| 4582 (1)| 00:00:55 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1239 | 229K| 4582 (1)| 00:00:55 |
|* 4 | SORT ORDER BY STOPKEY | | 1239 | 168K| 4582 (1)| 00:00:55 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | CVS_REC_BANKTASK | 1 | 77 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1239 | 168K| 4581 (1)| 00:00:55 |
|* 8 | TABLE ACCESS BY INDEX ROWID| CVS_RECEPTION | 1239 | 76818 | 862 (1)| 00:00:11 |
|* 9 | INDEX SKIP SCAN | IDX_CVS_RECEPTION | 1239 | | 101 (0)| 00:00:02 |
|* 10 | INDEX RANGE SCAN | IDX_REC_BANKTASK | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">TO_NUMBER(:UP)*TO_NUMBER(:DOWN)-TO_NUMBER(:DOWN))
2 - filter(ROWNUM<=TO_NUMBER(:UP)*TO_NUMBER(:DOWN))
4 - filter(ROWNUM<=TO_NUMBER(:UP)*TO_NUMBER(:DOWN))
5 - filter(TO_DATE(:ENDTDATA,'yyyymmdd')+1>=TO_DATE(:STARTDATA,'yyyymmdd'))
6 - filter("T"."DEAL_TIME"<=TO_DATE(:ENDTDATA,'yyyymmdd')+1 AND
"T"."DEAL_TIME">=TO_DATE(:STARTDATA,'yyyymmdd') AND "T"."ACCOUNT_TYPE"=TO_NUMBER(:ACCOUNTTYPE

聲明:本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí),若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系,我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

文檔

傾斜值傳入導(dǎo)致sql資源消耗升高的案例分析

傾斜值傳入導(dǎo)致sql資源消耗升高的案例分析:局方監(jiān)控系統(tǒng)反饋2014-12-31 19:30:00-20:00:00這段時(shí)間db time上升較大,sql_id 88wdzpr9mv2wy消耗了12%以上的db time sql在shared pool的執(zhí)行計(jì)劃 sys@CRMDB4select * from table(dbms_xplan.display_curs
推薦度:
標(biāo)簽: 資源 上升 sql
  • 熱門(mén)焦點(diǎn)

最新推薦

猜你喜歡

熱門(mén)推薦

專(zhuān)題
Top
主站蜘蛛池模板: 国产精品高清久久久久久久 | 亚洲欧美日韩中文v在线 | 亚洲三级电影网站 | 国产a精品 | 国产精品一区二区综合 | 卡通动漫亚洲 | 国产精品伊人 | 欧美aⅴ在线 | 国产一区在线播放 | 欧美日韩视频一区二区三区 | 国产人成久久久精品 | 国产一区在线看 | 国产99视频在线观看 | 精品国产欧美一区二区三区成人 | 欧美人与禽zoz0性伦交 | 九九久久亚洲综合久久久 | 国产一区二区精品久久91 | 亚洲欧美h| 在线播放真实国产乱子伦 | 久久精品国产亚洲aa | 日本高清一区二区三区不卡免费 | 一区二区不卡久久精品 | 国产一区精品在线观看 | 亚洲国产成人久久99精品 | 影音先锋女人aa鲁色资源 | 国产精品大全国产精品 | 欧美在线一区二区 | 欧美精品久久久久久久久大尺度 | 国产成人久久精品二区三区 | 欧美一级全黄 | 日本特级淫片免费看 | 成人a毛片免费视频观看 | 日韩有码第一页 | 亚洲欧洲高清有无 | 一区二区三区四区电影 | 久久精品无码一区二区日韩av | www.com黄色| 国产欧美曰韩一区二区三区 | 午夜视频久久久久一区 | 国产黑色丝袜一区在线 | 亚洲欧美另类在线观看 |