performance issue after upgrade to 10.2.0.5
From: Helter Skelter <helter.skelterr_at_yahoo.com>
Date: Fri, 26 Aug 2011 00:50:37 -0700 (PDT)
Message-ID: <1314345037.9319.YahooMailNeo_at_web114114.mail.gq1.yahoo.com>
Date: Fri, 26 Aug 2011 00:50:37 -0700 (PDT)
Message-ID: <1314345037.9319.YahooMailNeo_at_web114114.mail.gq1.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 26 2011 - 02:50:37 CDT