performance issue after upgrade to 10.2.0.5
- From: Helter Skelter <helter.skelterr@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Aug 2011 00:50:37 -0700 (PDT)
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 actual trace:
call count cpu elapsed disk query current
rows
--- ------ -- --------- ---------- ---------- ---------- ---------
Parse 0 0.00 0.00 0 0 0
0
Execute 1 4259.95 16036.0 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
Other related posts:
