Re: performance issue after upgrade to 10.2.0.5
- From: Helter Skelter <helter.skelterr@xxxxxxxxx>
- To: Nilo Segura <nilosegura@xxxxxxxxx>
- Date: Fri, 26 Aug 2011 03:09:26 -0700 (PDT)
Hi,
I've did it before for whole instance;
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
no results :/
I am wondering how could this genereate about 40GB! undo. Source table for
merge has about 2,5mln records/700MB
I think this could be a reason for this
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
any ideas?
thanks, HS
________________________________
From: Nilo Segura <nilosegura@xxxxxxxxx>
To: helter.skelterr@xxxxxxxxx
Sent: Friday, August 26, 2011 10:55 AM
Subject: Re: performance issue after upgrade to 10.2.0.5
A first test would be to do
alter session set optimizer_features_enable='10.2.0.4';
and run the merge.
if you get the same bad results then it is not the optimizer. If you get the
good results again, then yes, it is optimizer related.
On Fri, Aug 26, 2011 at 9:50 AM, Helter Skelter <helter.skelterr@xxxxxxxxx>
wrote:
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
--
Nilo Segura
Oracle Support - IT/DB
CERN - Geneva
Switzerland
Other related posts:
