Home » RDBMS Server » Performance Tuning » Direct path load insert take much longer than query inside it (12.1.0.2.0)
Direct path load insert take much longer than query inside it [message #668997] Wed, 28 March 2018 05:39 Go to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
Hy Guys

i have a global temporary table temp_tab and i want to insert to that table from a query

select a.* from tab_a a, tab_b
where a.id = b.a_id => takes 1 minute

insert /*+ APPEND */ into temp_tab
select a.* from tab_a a, tab_b
where a.id = b.a_id => takes 10 minutes more

the rows of the query is 100000 rows
no index in temp_tab

what could be the problem?

thank you
Re: Direct path load insert take much longer than query inside it [message #668998 is a reply to message #668997] Wed, 28 March 2018 05:47 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
If you set temp_undo_enabled=true it might help.
Re: Direct path load insert take much longer than query inside it [message #669018 is a reply to message #668998] Thu, 29 March 2018 02:27 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Usually when you see these issues it is one of three things:

>The select is run in a GUI and the first N rows are coming back quickly, NOT the whole result set.
>There are triggers or shedloads of indexes on the table inserted to
>Something is locking the object

Other possibilities exist, but I'd check these first.
Re: Direct path load insert take much longer than query inside it [message #669029 is a reply to message #669018] Thu, 29 March 2018 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
given it's a gtt options 2 and 3 seem unlikely.
Re: Direct path load insert take much longer than query inside it [message #669034 is a reply to message #669029] Thu, 29 March 2018 08:33 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I never trust anything I'm told rather than shown Wink
Previous Topic: Incosistent Wait Event
Next Topic: Fine Tune Big SQL Reporting Query
Goto Forum:
  


Current Time: Tue May 26 13:13:51 CDT 2020