Home » RDBMS Server » Performance Tuning » Slow Insert APPEND into Temporary Table
Slow Insert APPEND into Temporary Table [message #424438] Fri, 02 October 2009 07:13 Go to next message
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?
Re: Slow Insert APPEND into Temporary Table [message #424441 is a reply to message #424438] Fri, 02 October 2009 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since space allocation for global temps is completely different to normal tables I really wouldn't expect append to behave the same.
Re: Slow Insert APPEND into Temporary Table [message #424442 is a reply to message #424438] Fri, 02 October 2009 07:44 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check Metalink there are known bugs on insert append into GTT.

Regards
Michel
Previous Topic: Query tuning
Next Topic: Does "alter system flush shared_pool" statement impact to performance?
Goto Forum:
  


Current Time: Thu Sep 29 22:40:58 CDT 2016

Total time taken to generate the page: 0.10686 seconds