| Slow Insert APPEND into Temporary Table [message #424438] |
Fri, 02 October 2009 07:13  |
spv73
Messages: 1
Registered: March 2008
|
Junior Member |
|
|
Hello,
We did the following test on Oracle11g 11.1.0.7 database:
create global temporary table test_tab
as select * from tab1 where rownum <= 1;
insert into test_tab select * from tab1 where rownum <= 500000;
commit;
Elapsed time: 00:00:04.56
Statistic
----------------------------------------------------------
80 recursive calls
26360 db block gets
10606 consistent gets
4729 physical reads
2543400 redo size
399 bytes sent via SQL*Net to client
340 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
500000 rows processed
truncate table test_tab;
insert /*+ append */ into test_tab select * from tab1 where rownum <= 500000;
commit;
Elapsed time: 00:00:09.35
Statistic
----------------------------------------------------------
84 recursive calls
4900 db block gets
4738 consistent gets
4698 physical reads
1128 redo size
376 bytes sent via SQL*Net to client
354 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
500000 rows processed
Note that insert APPEND generates much less redo size: 1128 vs 2543400 . Now the question: why insert APPEND into the temporary table is two times slower than the ordinary insert? Potentially, it should run faster because of lower redo size... Any ideas?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
