Re: performance issue after upgrade to 10.2.0.5
From: joel garry <joel-garry_at_home.com>
Date: Fri, 26 Aug 2011 09:34:24 -0700 (PDT)
Message-ID: <0ff49774-90e0-4d27-996b-7809f27a9749_at_e20g2000prn.googlegroups.com>
On Aug 26, 3:13 am, helter skelter <helterskel..._at_gmail.com> wrote:
> 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
>
>
Date: Fri, 26 Aug 2011 09:34:24 -0700 (PDT)
Message-ID: <0ff49774-90e0-4d27-996b-7809f27a9749_at_e20g2000prn.googlegroups.com>
On Aug 26, 3:13 am, helter skelter <helterskel..._at_gmail.com> wrote:
> 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
>
>
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
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2011/aug/25/tri-city-board-members-degree-is-in-question/Received on Fri Aug 26 2011 - 11:34:24 CDT