OraFAQ Forum: Performance Tuning » Direct path load insert take much longer than query inside it

Home » RDBMS Server » Performance Tuning » Direct path load insert take much longer than query inside it (12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Direct path load insert take much longer than query inside it [message #668997]

Wed, 28 March 2018 05:39

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

Report message to a moderator

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

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

If you set temp_undo_enabled=true it might help.

Report message to a moderator

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

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.

Report message to a moderator