Hello,
Our production database is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.
I have function call in my package which does the below insert into A_LS_TABLE. This function does the same for every type we have (more than 200 types) and inserts the corresponding rows to A_LS_TABLE. I used to do this every 3 months as part of housekeeping. Till last time it was working fine. Now suddenly this query hangs at a particular table. It has processed some 189 types successfully and after that hangs at this particular table. Normally this step gets completed in seconds. But now I left it to run for 5 days and it did not do anything. This session information shows its wait event as db_sequential_read - for the index file of the tablespace containing this table. I tried the select query alone using SQL Worksheet and SQL * Plus and it was working fine. It brings results is 229s (which is a little longer than normal but still it works). But if i try the below query using SQL Worksheet the session hangs with the wait event db_sequential_read. I donot understand why the select query runs fine and not the insert query (wait event shows it is waiting the index file related to the source table - then it should wait during select as well right, are the locks different in this case)I have repeated the function call thrice and every time it hangs at the same table. We have not done any change to the meta definition for this table after last time.
INSERT
INTO A_LS_TABLE
(
EID ,
TID ,
Variante,
PZS ,
SeqNo ,
deleted ,
part
)
(
SELECT DISTINCT( t.col1 ) ,
70083 ,
0 ,
t.col4,
MAX( t.col5 ),
o.deleted ,
'YES'
FROM table1 t,
table2 o
WHERE
(
t.col1, t.col4
)
IN
(
SELECT DISTINCT( col1),
MAX( col4 )
FROM table1
WHERE col4 <= 1467331199
GROUP BY AGID
)
AND o.col2 = 70083
AND o.col1 = t.col1
GROUP BY t.col1 ,
70083 ,
0 ,
t.col4,
o.deleted ,
'YES'
)
Could anyone help me to resolve this issue?
Thanks in advance,
AGana