Private strand flush not complete ?
64bit Red Hat
11.1.0.6.0 db
Hi all.
I have 3 redo groups with 1 member each of 150MB, this is my test instance , so there is almost none read/write activity,
but,
my alert log is full with Thread 1 cannot allocate new log, Checkpoint not complete and even with Private strand flush not complete messages .
I resized those logs today , from 50MB to 150MB , primarily because package that it seems to hang every time i execute it .
During its execution, i see the most dominant wait events(almost only wait events) to be log file switch (checkpoint incomlete) , log buffer space and
log file switch (private strand flush incomplete) while the current SQL is always merge clause of the package.
Also i would mention that this package executes successfully ,in about 30 secs, when that merge statement is replaced with corresponding update/insert.
And the funny thing also is that although session that exec package with merge inside , is constatly showing that current SQL is merge ,
+ table that needs to be merged is merged within 30secs after package is executed.(But session stiil remains active/hanged)
I read that merge is having issues if it is within plsq and some other statements that are happening after and/or before are not commited. So i did
set autocommit on before i started package. Didnt help.
Please help. :)