要生成一张测试表，记录为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 Time | SQL Id | SQL Module | SQL Text | 6,338 | 6,326 | 0 |
| 97.98
| 20xdw4f509kvy | PL/SQL Developer | declare sqlstr varchar2(400... | 5,871 | 5,871 | 2,500,975 | 0.00 | 90.76 | c15zb55qqbf9y | PL/SQL Developer | SELECT HZ FROM D_PY WHERE PY00... | 157 | 154 | 833,667 | 0.00 | 2.43 | csnvmt51hb18a | PL/SQL Developer | INSERT /*+ append*/ INTO RY_JB... | 83 | 82 | 2,501,002 | 0.00 | 1.28 | 3svqc8upa471p | PL/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 | 9f7qzj53kqyn4 | PL/SQL Developer | SELECT DECODE(MOD(FLOOR(DBMS_R... | 28 | 28 | 833,658 | 0.00 | 0.43 | cfc7huzym29k6 | PL/SQL Developer | SELECT 116+ROUND(DBMS_RANDOM.V... | 26 | 16 | 335 | 0.08 | 0.40 | 2zwjrv2186835 | Oracle Enterprise Manager.rollup | DELETE FROM MGMT_METRICS_RAW W... | 15 | 2 | 1 | 14.73 | 0.23 | bbxd3y9z81uhg | PL/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 Time | SQL Id | SQL Module | SQL Text | 3,496 | 3,363 | 0 |
| 96.16
| 5rx86f0vkamfh | PL/SQL Developer | declare sqlstr varchar2(400... | 1,057 | 932 | 5,807,637 | 0.00 | 29.08 | csnvmt51hb18a | PL/SQL Developer | INSERT /*+ append*/ INTO RY_JB... | 511 | 511 | 17,423,080 | 0.00 | 14.06 | 3svqc8upa471p | PL/SQL Developer | SELECT CASE WHEN RN <= 6986... | 428 | 428 | 17,423,076 | 0.00 | 11.76 | fjgxuk83xw5nr | PL/SQL Developer | SELECT HZ FROM D_PY WHERE PY00... | 238 | 238 | 5,807,510 | 0.00 | 6.54 | 9f7qzj53kqyn4 | PL/SQL Developer | SELECT DECODE(MOD(FLOOR(DBMS_R... | 161 | 161 | 5,807,696 | 0.00 | 4.44 | cfc7huzym29k6 | PL/SQL Developer | SELECT 116+ROUND(DBMS_RANDOM.V... | 72 | 8 | 2 | 35.89 | 1.97 | bbxd3y9z81uhg | PL/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 | gra6zat0x5q8t | sqlplus.exe | select group#, status from v$l... | 9 | 8 | 118 | 0.08 | 0.25 | 2b064ybzkwf1y | OEM.SystemPool | BEGIN EMD_NOTIFICATION.QUEUE_R... |
从90.76%降到了11.76%，至此数据插入的性能问题解决。感谢版主的鼎力支持；
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖
