Re: Problem with Insert statement

"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. Received on Sat Jun 11 2005 - 10:43:09 CDT