Hi,

I've got problem with merge statement and it has appeared exacly after
upgrade to 10.2.0.5
Executiom time before upgrade was about 1-1.5h and now it takes >4h.
Execution plan didn't change (full scan of small table and nested loops
using index on large table). Table has a mview log.

Some stats from awr comparing to the same merge before upgrade:
cpu time - 49,410 vs 3,253,170
physical reads/exec 29,623 vs 1,447,152
and it generates MUCH more undo now.

some stats from trace:
call count cpu elapsed disk query current rows
--- ------ -- --------- ---------- ---------- ---------- ---------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 4259.95 16036.05 417980 257456884 262050477 1973401
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------


Event waited on Times Max.Wait TotalWaitedWaited
----------
db file sequential read 331046 1.87 7066.03
latch: cache buffers lru chain 12837 1.40 36.09
PX Deq: Execute Reply 13612 1.24 592.62
log buffer space 1978 0.98 214.82
log file switch completion 196 0.98 24.14
buffer busy waits 239 1.01 79.76

Only change of db parameters was compatible 10.2.0.4 to 10.2.0.5
any idead what could be a reason ?

thanks, hs

I've found something interesting


select round(avg(activeblks)), to_char(begin_time,'YYYY-MM-DD') from
DBA_HIST_UNDOSTAT
where begin_time > to_date('2011-08-18','YYYY-MM-DD')
group by to_char(begin_time,'YYYY-MM-DD')
order by 2 desc

ROUND(AVG(ACTIVEBLKS)),TO_CHAR(BEGIN_TIME,'YYYY-MM-DD')
TO_CHAR(BEGIN_TIME,'YYYY-MM-DD'),ROUND(AVG(ACTIVEBLKS))
2011-08-26,6 209 636
2011-08-25,1 390 475
2011-08-24,992 609
2011-08-23,856 897
-----upgrade to 10.2.0.5
2011-08-22,8 042
2011-08-21,10 791
2011-08-20,14 372
2011-08-19,10 759
2011-08-18,9 126

no significant changes on the database, what could be a reason ?

thanks, HS


W dniu 2011-08-26 09:41, helter skelter pisze:

On Aug 26, 3:13*am, helter skelter <helterskel... (AT) gmail (DOT) com> wrote:
Does changing compatible back fix it?

May have nothing to do with anything, but you might want to rule out
the mview bug After Mview Logs Creation, See A Lot Of 'Dfs Lock
Handle' Waits On Dml. [ID 845886.1]

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...s-in-question/

I've changed optimizer compatible back to 10.2.0.4 - no results.
I've also tried:
-drop and recreated mview log
-drop mview log
-rebuild indexes on destination table (bitmap and btree)
....no results.

After 200k (from 2,6M) inserts to mview log it has more then 7GB of
active UNDO! and it's growing linear.

HS