Re: performance issue after upgrade to 10.2.0.5

From: helter skelter <helterskelter_at_gmail.com>
Date: Fri, 26 Aug 2011 12:13:30 +0200
Message-ID: <j37rlg$p3i$1_at_mx1.internetia.pl>



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:
> 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 - 05:13:30 CDT

Original text of this message