欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

倾斜值传入导致sql资源消耗升高的案例分析

程序员文章站 2022-06-09 14:05:22
...

局方监控系统反馈2014-12-31 19：30：00-20：00：00这段时间db time上升较大，sql_id 88wdzpr9mv2wy消耗了12%以上的db time sql在shared pool的执行计划 sys@CRMDB4select * from table(dbms_xplan.display_cursor('88wdzpr9mv2wy')); PLAN_TABLE_OUTPUT ---

局方监控系统反馈2014-12-31 19：30：00-20：00：00这段时间db time上升较大，sql_id 88wdzpr9mv2wy消耗了12%以上的db time

sql在shared pool的执行计划

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 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 4 - filter(ROWNUM 5 - filter(TO_DATE(:ENDTDATA,'yyyymmdd')+1>=TO_DATE(:STARTDATA,'yyyymmdd'))
6 - filter("T"."DEAL_TIME" "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的历史执行计划：

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 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历史中只出现过一种执行计划，这个表示该sql在awr中没有出现多种执行计划而导致性能出现差异。

相关表的统计信息：

CVS_RECEPTION表的统计信息：
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表的统计信息：
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的历史执行信息：

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.

通过对比每半个小时的平均逻辑读部分时间段有较大的波动，在2014-12-31 19:30:01到2014-12-31 20:00:01时间段这个sql执行次数达到了3057次，每次平均逻辑读达到了76万以上，而有些时间段的这个sql的平均逻辑读只有几万，这个表示通过绑定变量传递过来的值会有倾斜值。

对比执行计划造成逻辑读在不同时间段存在差异的只可能是tbcs.cvs_reception表，而这个表是作为nested loop循环的驱动表，对应的执行计划和谓词部分如下：

|* 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这列根据表的统计信息只有一组distinct value，那么只可能是org_id这个对应的绑定变量：SITEID存在有倾斜值，造成了平均逻辑读在这个时间段特别大，然而sql在这个时间段执行频率又特别高，进而导致消耗了较多的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
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

优化器评估的INDEX SKIP SCAN IDX_CVS_RECEPTION 部分返回的rows是1239（在没有直方图的情况下，优化器计算等值谓词的选择selectivy公式是1/distinct*((num_rows-null_rows）/num_rows))

oracle抓取绑定变量的规律有两种：
1 硬解析的sql被执行时，oracle会抓取该sql的绑定变量
2 软解析/软软解析的sql重复执行时，oracle也会抓取绑定变量，不过这里oracle只会每隔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

这里运气可能比较好，oracle这里在2014/12/31 20:00:15抓取的bind value刚好是倾斜值11001259（如果刚好故障时间段抓到是一个没有倾斜性的值，大家也不要判定觉得这个sql在这个时间段是没有传入倾斜值的），这个值实际通过index skip scan部分要返回310378条数据，而这里又要走nested loop的方式，相当于底层的被驱动表CVS_REC_BANKTASK要走310378次index range scan，正是循环次数的增多导致这个sql会消耗较多的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值，来验证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 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 4 - filter(ROWNUM 5 - filter(TO_DATE(:ENDTDATA,'yyyymmdd')+1>=TO_DATE(:STARTDATA,'yyyymmdd'))
6 - filter("T"."DEAL_TIME" "T"."DEAL_TIME">=TO_DATE(:STARTDATA,'yyyymmdd') AND "T"."ACCOUNT_TYPE"=TO_NUMBER(:ACCOUNTTYPE

相关标签： 倾斜 传入 导致 sql 资源 消耗 高的 案例分析 监控系

上一篇： php上传文件并存储到mysql数据库的方法

下一篇： PHP+Msql+Ajax实现便签条

推荐阅读