批量insert测试

CDL_DO_LC_TEST_20130777 有500w条数据，5点多g数据量， 测试如下语句

INSERT /*+ append */ INTO CDL_DO_LC_TEST_20130888 select * from CDL_DO_LC_TEST_20130777;  

tkprof格式化10046level12后的部分

INSERT /*+ append */ INTO CDL_DO_LC_TEST_20130888 select * from

  CDL_DO_LC_TEST_20130777

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.18       0.61          1          1          0           0

Execute      1    372.44     495.94     706205    5713756     708612     5000000

Fetch        0      0.00       0.00          0          0          0           0

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

total        2    372.62     496.55     706206    5713757     708612     5000000

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 43

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         0          0          0  LOAD AS SELECT  (cr=5713926 pr=706203 pw=706086 time=495829077 us)

   5000000    5000000    5000000   PARTITION RANGE ALL PARTITION: 1 2 (cr=5713722 pr=706065 pw=0 time=274003522 us cost=194038 size=

24759019575 card=4345975)

   5000000    5000000    5000000    TABLE ACCESS FULL CDL_DO_LC_TEST_20130777 PARTITION: 1 2 (cr=5713722 pr=706065 pw=0 time=2712018

51 us cost=194038 size=24759019575 card=4345975)

set autotrace on后的部分

Statistics

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

        119  recursive calls

     708855  db block gets

    5714249  consistent gets

     706789  physical reads

    1364520  redo size

        828  bytes sent via SQL*Net to client

        847  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

    5000000  rows processed

===

上面两个的结果一致，可以看到cr读很大，正常理解，这里的cr应该就是读777表的，但是即使做777的全表也不应该这么多，如下，莫非是undo？

select count(*)

from

 CDL_DO_LC_TEST_20130777

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2     10.26      31.20     706086     706089          0           1

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

total        4     10.26      31.21     706087     706090          0           1

===

另外direct path write这个等待事件这里一次是写32个块，看什么因素影响这个（表空间块大小？）然后看加大一次读的块数会不会有性能提高  WAIT #18446744071465955504: nam='direct path write' ela= 19591 file number=1575 first dba=861120 block cnt=32 obj#=8999833 tim=42257

38182953

其他的类似 WAIT #18446744071465955504: nam='db file scattered read' ela= 18258 file#=1575 block#=93483 blocks=128 obj#=8999833 tim=422573843148

5

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28952551/viewspace-767481/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28952551/viewspace-767481/