performance issue after upgrade to 10.2.0.5

From: helter skelter <helterskelter_at_gmail.com>
Date: Fri, 26 Aug 2011 09:41:10 +0200
Message-ID: <j37ioc$ug4$1_at_mx1.internetia.pl>

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 Received on Fri Aug 26 2011 - 02:41:10 CDT