Re: performance issue after upgrade to 10.2.0.5

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_at_gmail.com>
To: helter.skelterr_at_yahoo.com
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_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l