要生成一张测试表，记录为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%，至此数据插入的性能问题解决。感谢版主的鼎力支持；

看来真得注意这点了

学习！！！

学习！好文章！

xuexixuexi

围观！学习！

又是字符转换惹的祸

哈哈，不错，小陈，找到问题了吧。
报告中很明显，select hz into hzstr from d_py where py0000 =：n;花费了几乎所有的时间，insert等基本没有占用时间。
plsql内部的所有sql都算是递归调用，因此递归占用几乎全部的执行时间。