某支付系统存在系统争用,尤其是一张交易表tranaction,交易比较频繁,为数据库的瓶颈,和某移动boss交易系统类似

该系统的基本状况:

每天交易量10w笔/day

每年大约交易金额:3500亿/y

每天最高事务量:30tx/s

对数据库的实时响应高

 

数据库等待的相关信息

 

AMPLE_T EVENT                                                            SQL_ID           SQL_CNT

——– —————————————————————- ————- ———-

20120703 db file scattered read                                           2jg5bp5apj3gd         34

20120703 db file scattered read                                           4bxbcn9dv2xsu          4

20120703 db file scattered read                                           91g7g1u65kqr6          2

 

 

 

 

 

SQL_ID        SQL_TEXT

————- ——————————————————————————–

2jg5bp5apj3gd update pe_transaction set rec_state=:1 where rec_serial_no=:2

 

有绑定变量

 

 

 

走的是全表(无索引)

SQL_ID                ID OPERATION                      OBJECT_OWN OBJECT_NAME                     OBJECT_TYPE                COST    IO_COST

————- ———- —————————— ———- ——————————- ——————– ———- ———-

2jg5bp5apj3gd          0 UPDATE STATEMENT                                                                                      693

2jg5bp5apj3gd          1 UPDATE                         PEPP_PE    PE_TRANSACTION

2jg5bp5apj3gd          2 TABLE ACCESS                   PEPP_PE    PE_TRANSACTION                  TABLE                       693        691

 

 

之前统计信息被锁住(可能由于账号被锁)

1.优化方法,创建索引,搜集统计信息,由于数据分布不均匀(含有大量的null值),搜集直方图信息

 

OWNER      TABLE_NAME                INDEX_NAME                COLUMN_NAME          COL_LE DESC UNIQUENESS BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR

———- ————————- ————————- ——————– —— —- ———- —— ———– ———- ————- —————–

PEPP_PE    PE_TRANSACTION            IDX_TRX_REC_SER_NO        REC_SERIAL_NO        160    ASC  NONUNIQUE  2            34628    6509826          2798           2331852

 

 

 

 

2

创建索引

create index idx_trx_rec_ser_no on pe_transaction(rec_serial_no) nologging online;

3.搜集直方图信息

begin

dbms_stats.gather_table_stats(

ownname => ‘PEPP_PE’,

tabname => ‘PE_TRANSACTION’,

estimate_percent => 50,

method_opt => ‘FOR COLUMNS SIZE 10 rec_serial_no’,                ID列上创建10个直方图,默认创建75,直方图反映数据库分布情况,在数据分布倾斜的时候使得CBO更能有效的使用索引,而非Bind

degree => 4,

cascade => true);

end;

 

 

 

对比执行计划处理前

 

 

 

 

 

 

 

 

 

 

 

 

 

优化后AWR对比

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

经过优化,等待时间db file scattered read消失,同时,Load File大部分项目性能能有所提升

 

 结论:

1.不是任何场景都合适绑定变量(bind peek)

2.对于该案例包含大量null值的绑定谓词匹配,按照计算成本,对于更新的行(不包含null)更新最优化的方式是走索引,而目前没有走,因此搜集直方图的方式比较适合CBO选择执行计划(oracle sql profier类似功能,但不够智能)

3.创建直方图数据分布结合统计信息能够使得CBO智能的选择正确的执行计划

4.创建索引,更改操作都需要慎重,对于7*24小时交易业务的数据库,需要尽量保证业务高可用性的基础上做性能处理,避免减少block 争用等衍生带来的问题。