Re: Problem with Insert statement

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 11 Jun 2005 18:50:37 +0200
Message-ID: <d8f4ol$4ks$02$1@news.t-online.com>

IANAL_VISTA schrieb:

> "barraboombarrabin" <barraboombarrabing_at_yahoo.com> wrote in 
> news:1118502680.924312.188180_at_g49g2000cwa.googlegroups.com:
> 
> 

>>I have an insert statement in this format.
>>
>>INSERT /*+ APPEND */ INTO <insert_into_table>
>> (column1,
>>column2,
>>column3 .......
>>)
>>SELECT /*+ PARALLEL (msit 5)*/ /*+ PARALLEL (sosi 5)*/
>>column1, column2, column3 ........
>>FROM <select_table1> msit,
>> <select_table2> sosi
>>WHERE <joins>
>>GROUP BY column1, column2
>>
>>This insert statement generally completes in a few minutes daily.
>>However on certain days it takes hours and just does not complete. I
>>need some guidance on what to look for - I am trying to figure out the
>>difference between the time when the insert functions well and when it
>>functions poorly. If I compare dbms_stats reports what is it that I
>>need to look for to pin point the problem.
>>
>>THanks
>>
>>

> 
> 
> -- HOLDER_WAITER SQL script
> 
> When the INSERT is "stuck", try running this query
> 
> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
> id1, id2, lmode, request, type
> FROM V$LOCK
> WHERE (id1, id2, type) IN
> (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
> ORDER BY id1, request
> /
> 
> If it returns rows, they may provide clue WRT source of problem.

In addition, i would look on the explain plan, maybe your statistics are   stale and your plan is wrong, also parallel hint not necessarily force the parallel execution. Look on os activity as well - maybe your memory is exhausted and you are running into swapping.

Best regards

Maxim Received on Sat Jun 11 2005 - 11:50:37 CDT