一个索引导致的性能问题

要生成一张测试表,记录为1000W条,但是插入的速度很慢,前期做的优化工作
1、测试表RY_JBXX创建时指定nologging方式;
2、字典表D_PY已经KEEP到buffer pool中;
3、对D_PY的PY0000字段创建索引,并收集了索引的统计信息;
4、执行测试数据的插入程序(插入数据时指加入append提示),程序如下:
declare
  sqlstr varchar2(4000);
  hzstr varchar2(10);
  xmstr varchar2(100);
  x varchar2(30);
  y varchar2(30);
  cnt number := 0;
  n number;
begin
  execute immediate 'truncate table ry_jbxx';
  for i in 1..10000000 loop
  --for i in 1..1000 loop
    xmstr := null;
    for j in 1..3 loop
      select case when rn <= 6986 then rn else rn - 6986 end rn into n
        from (select floor(dbms_random.value*10000) rn from dual);
      if n = 0 then
        n := 1;
      end if;
      select hz into hzstr from d_py where py0000 = n;
      xmstr := xmstr || hzstr;
    end loop;
    select 116+round(dbms_random.value,8) into x from dual;
    select decode(mod(floor(dbms_random.value*10),2),0,39,40)+round(dbms_random.value,8) into y from dual;
    insert /*+ append*/ into RY_JBXX (OBJECTID, RYBH, GMSFHM, XM, XMPY, CYM, CYMPY, YWX, YWM, ZJZL, ZJHM, BMCH, BMCHPY, XB, MZ, CSRQ, CSSJ, CSZMBH, CSDQ, CSQX, CSXZ, JGDQ, JGQX, JGXZ, ZJXY, ZZMM, WHCD, HYZK, BYZK, SG, XX, SF, ZC, ZW, ZY, ZYLB, FWCS, LXDH, ZWSP, SWRQ, SWZMBH, GJDQ, HJQH, HJZRQ, HJXZ, XXJB, RYLB, RYSX, RYZZBH, ZWBH, DNABH, DJR, DJSJ, DJDW, XZZZRQ, XZZQH, XZZXZ, DJDWMC, DJRXM, XGSJ, XGR, XGDW, XGRXM, XGDWMC, HJQHMC, XZZQHMC, SJDZBM, SJHUID, SJCJRYBH, SJCJSJ, CLBS, X, Y, WCRYBS, JZRYBS, SQDM, GZDXBS, RYZT)
  values (45, sys_guid(),sys_guid(), xmstr, 'yrl~m;', null, null, null, null, null, null, null,
  null, '1', '1', '19761118', null, null, null, '320481', '上沛镇', null, '320481', null, '0', null, '30', '20', '0', '165',
   '9', null, null, null, '不便分类的其他劳动者', '998', '苏州市迎新科技有限公司', '13222265352', null, null, null, 'CHN',
   '320481', '320481520120', '南环路12号', null, '1', '0', 'D3204810000002664033', null, null, '0', '20090812164847',
   '320481520000', '320481520120', '320481', '江苏省溧阳市南环路12号', null, null, null, null, null, null, null, null, null,
    '8067B4C396807044E0437F0000017044', '8067B4C396807044E0437F0000017044-1-1-1', null, to_date('03-03-2010 01:21:02',
    'dd-mm-yyyy hh24:mi:ss'), '1', x, y, '0', '0', '320481520120', '0', '0');
    cnt := cnt + 1;
    if mod(cnt,10000) = 0 and cnt >= 10000 then
      commit;
    end if;
  end loop;
  commit;
  
  exception
    when others then
      dbms_output.put_line(n);
      
end;
/
现象:插入速度非常慢,将近三个小时才插入了128W条记录;生成的AWR报告中有如下提示
SQL ordered by Elapsed Time


    Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text
6,338
6,326
0

97.98
20xdw4f509kvyPL/SQL Developer declare sqlstr varchar2(400...
5,871
5,871
2,500,975
0.00
90.76
c15zb55qqbf9yPL/SQL Developer SELECT HZ FROM D_PY WHERE PY00...
157
154
833,667
0.00
2.43
csnvmt51hb18aPL/SQL Developer INSERT /*+ append*/ INTO RY_JB...
83
82
2,501,002
0.00
1.28
3svqc8upa471pPL/SQL Developer SELECT CASE WHEN RN <= 6986...
55
14
1
54.73
0.85
24b3xmp4wd3tu  delete from sys.wri$_optstat_h...
54
6
12
4.50
0.83
6gvch1xu9ca3g  DECLARE job BINARY_INTEGER := ...
36
36
833,658
0.00
0.56
9f7qzj53kqyn4PL/SQL Developer SELECT DECODE(MOD(FLOOR(DBMS_R...
28
28
833,658
0.00
0.43
cfc7huzym29k6PL/SQL Developer SELECT 116+ROUND(DBMS_RANDOM.V...
26
16
335
0.08
0.40
2zwjrv2186835Oracle Enterprise Manager.rollup DELETE FROM MGMT_METRICS_RAW W...
15
2
1
14.73
0.23
bbxd3y9z81uhgPL/SQL Developer select count(*) from ry_jbxx t...


注意前两行,花费的CPU时间非常多,这里列出的SQL语句都是执行的程序包里面的,所以可以基本断定是由于第二个语句导致的性能问题SELECT HZ FROM D_PY WHERE PY00...转而再次检查索引,才发现原来PY0000是varchar2类型,程序里面的语句(黑体)部分没有用到索引,因为N是数值类型,修改程序如下
select hz into hzstr from d_py where py0000 = to_char(n);
再次执行程序,生成awr报告后观察的结果如下
SQL ordered by Elapsed Time


    Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text
3,496
3,363
0

96.16
5rx86f0vkamfhPL/SQL Developer declare sqlstr varchar2(400...
1,057
932
5,807,637
0.00
29.08
csnvmt51hb18aPL/SQL Developer INSERT /*+ append*/ INTO RY_JB...
511
511
17,423,080
0.00
14.06
3svqc8upa471pPL/SQL Developer SELECT CASE WHEN RN <= 6986...
428
428
17,423,076
0.00
11.76
fjgxuk83xw5nrPL/SQL Developer SELECT HZ FROM D_PY WHERE PY00...
238
238
5,807,510
0.00
6.54
9f7qzj53kqyn4PL/SQL Developer SELECT DECODE(MOD(FLOOR(DBMS_R...
161
161
5,807,696
0.00
4.44
cfc7huzym29k6PL/SQL Developer SELECT 116+ROUND(DBMS_RANDOM.V...
72
8
2
35.89
1.97
bbxd3y9z81uhgPL/SQL Developer select count(*) from ry_jbxx t...
13
0
55
0.23
0.35
6gvch1xu9ca3g  DECLARE job BINARY_INTEGER := ...
9
1
176
0.05
0.25
gra6zat0x5q8tsqlplus.exe select group#, status from v$l...
9
8
118
0.08
0.25
2b064ybzkwf1yOEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_R...

从90.76%降到了11.76%,至此数据插入的性能问题解决。感谢版主的鼎力支持;
标签: 暂无标签
chenyu

写了 7 篇文章,拥有财富 171,被 3 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

P3 | 发表于 2011-3-18 10:19:30
看来真得注意这点了
回复

使用道具

P4 | 发表于 2011-3-14 14:54:07
学习!!!
回复

使用道具

P3 | 发表于 2011-3-14 10:58:49
学习!好文章!
回复

使用道具

P3 | 发表于 2011-3-1 22:04:07
xuexixuexi
回复

使用道具

P4 | 发表于 2011-3-1 09:39:33
围观!学习!
回复

使用道具

P3 | 发表于 2011-2-28 11:10:25
又是字符转换惹的祸
回复

使用道具

P6 | 发表于 2010-11-3 13:08:33
哈哈,不错,小陈,找到问题了吧。
报告中很明显,select hz into hzstr from d_py where py0000 =:n;花费了几乎所有的时间,insert等基本没有占用时间。
plsql内部的所有sql都算是递归调用,因此递归占用几乎全部的执行时间。
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈