傾斜值傳入導(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_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)過一種執(zhí)行計(jì)劃，這個(gè)表示該sql在awr中沒有出現(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.

通過對(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萬以上，而有些時(shí)間段的這個(gè)sql的平均邏輯讀只有幾萬，這個(gè)表示通過綁定變量傳遞過來的值會(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)

看來造成邏輯讀存在差異的只可能是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

來看看表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（在沒有直方圖的情況下，優(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ì)抓取綁定變量，不過這里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è)沒有傾斜性的值，大家也不要判定覺得這個(gè)sql在這個(gè)時(shí)間段是沒有傳入傾斜值的），這個(gè)值實(shí)際通過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值，來驗(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)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com