oracle insert into select 性能,高并发insert into select 效率问题

SQL> set autotrace on

SQL> INSERT INTO TCALC_GROUP_INSTRUMENT

2      (P_ID, I_CODE, A_TYPE, M_TYPE, ACCT_CODE, G_CODE, G_VALUE, BEG_DATE,

3       END_DATE)

4      SELECT B.P_ID, A.I_CODE, A.A_TYPE, A.M_TYPE, A.ACCT_CODE, A.G_CODE,

5             A.G_VALUE, '1901-01-01', '2050-12-31'

6      FROM   TMP_GROUP_INSTRUMENT_ALL A, TMP_GROUP_INSTRUMENT_INSERT B

7      WHERE  A.I_CODE = B.I_CODE AND

8             A.A_TYPE = B.A_TYPE AND

9             A.M_TYPE = B.M_TYPE AND

10             A.ACCT_CODE = B.ACCT_CODE AND

11             A.G_CODE IN

12             (SELECT G_CODE FROM TMP_RULE_ITEM WHERE ITEM_ORDER = 0);

已创建460554行。

已用时间:  00: 13: 28.20

执行计划

----------------------------------------------------------

Plan hash value: 2737438152

------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT     |                             |  1697K|   433M|       |  2169   (2)| 00:00:27 |

|*  1 |  HASH JOIN RIGHT SEMI|                             |  1697K|   433M|       |  2169   (2)| 00:00:27 |

|*  2 |   TABLE ACCESS FULL  | TMP_RULE_ITEM               |     6 |   240 |       |     2   (0)| 00:00:01 |

|*  3 |   HASH JOIN          |                             |  1697K|   369M|  8088K|  2155   (1)| 00:00:26 |

|   4 |    TABLE ACCESS FULL | TMP_GROUP_INSTRUMENT_INSERT | 88979 |  7038K|       |   132   (2)| 00:00:02 |

|   5 |    TABLE ACCESS FULL | TMP_GROUP_INSTRUMENT_ALL    |   155K|    21M|       |   446   (1)| 00:00:06 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."G_CODE"="G_CODE")

2 - filter("ITEM_ORDER"=0)

3 - access("A"."I_CODE"="B"."I_CODE" AND "A"."A_TYPE"="B"."A_TYPE" AND "A"."M_TYPE"="B"."M_TYPE"

AND "A"."ACCT_CODE"="B"."ACCT_CODE")

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

613  recursive calls

3173801  db block gets

20310  consistent gets

6644  physical reads

432465788  redo size

547  bytes sent via SQL*Net to client

1001  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

460554  rows processed

SQL>