OraFAQ Forum: Server Administration » Issues after Upgrading to 10g

Home » RDBMS Server » Server Administration » Issues after Upgrading to 10g (Oracle 10g Rel 2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issues after Upgrading to 10g [message #287046]

Tue, 11 December 2007 00:22

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,

3 days back we moved from Oracle 9i Rel 2 to 10g (10.2.0.3) 2 Node RAC environment on Solaris
and the method was Take Full Export from 9i and import to 10g.

After that whole Schema level Stats was gathered by this method


BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS (
     OwnName           => 'APP_OWNER'
    ,Granularity       => 'ALL'
    ,Options           => 'GATHER'
    ,Gather_Temp       => TRUE
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO '
    ,DEGREE            => 8
    ,CASCADE           => TRUE
    ,No_Invalidate     => FALSE);
END;

On Sunday when there was not much activity so system was cool,
on monday morning we started gettin trouble.
1 )- All regular Queries which were fine in 9i well under 1 min were running for 8+ Minutes in 10g.

2)- DBA's noticed messages in Alert Log
1 File corrupted, on 1 Node with generic Error ORA 06000, and this was the instance heavily loaded where CPU utilization
was above 90%, We opened TAR with Oracle and they are working on it, and they say it's a Bud and PATCH need to be done,
(Will update once if i have PATCH #)

3 )- They restarted and oracle did file recovery and Node was Up , but Load was heavy.so we have to stop the APP,
all the searches on node1 were consuming resources, and on Node2 Insertions were fine,

Another DBA found error related to CACHE PIN .., so for that also they opened TAR.

During this i was searching on net and found some thing,

at the end of the day what we did is

Bring back the STATS from 9i database and imported on 10g,
the query morning which was taking 8+ min was back to 1-2 minutes, and then after this at session level i applied parameter changes , and that 1-2 min query came in less than 40 sec which was same as when we were on 9i Datbase,

But there is no guarantee that once we do at SYSTEM level will these parameters help.

Another thing was Oracle Support was pointing was the way STATS were gathered might have caused this,
but they have not given the exact method we should gather STATS on 10g

alter session set  "_gby_hash_aggregation_enabled" = FALSE
alter session set "_optimizer_cost_based_transformation" = OFF

Does any body have idea about any such situation like this, any thing happened related
like this after upgrading to 10g.Please share and Advice.

Thanks

Report message to a moderator

Re: Issues after Upgrading to 10g [message #287050 is a reply to message #287046]

Tue, 11 December 2007 00:31

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

After gather stats execution plan is changed.
after upgrade from 9i to 10g performance is down it is true(some cases)

but you set two "hidden" parameter with oracle support or without oracle support.
if without then why you set 'hidden' parameter.

Report message to a moderator

Re: Issues after Upgrading to 10g [message #287266 is a reply to message #287050]

Tue, 11 December 2007 14:37

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks for Replying,

Yes we revert back the 2 parameters changes i did, and these are the
changes Oracle has recommended and we will implement in PROD tonight.

1 alter system set "_optim_peek_user_binds" = FALSE --Orace Consultant Recommended

2 alter system set "_optimizer_cost_based_transformation" =OFF oracle recommendation

3 alter system set "_optimizer_rownum_pred_based_fkr"=FALSE ---- oracle recommendation

4 session_cached_cursors 200 --ADDR report

5 cursor_space_for_time TRUE --ADDR report

6 patch 6164976 ---- oracle recommendation ( only in dev so far).

We did implemented these changes in DEV region where data is 75% of the PROD size and things
are flying same as 9i Database and infact some were better. so after 1 more round of testing
we might implement in PROD .

Please share if any such expereices some one came across.

Thanks

Report message to a moderator

Previous Topic:	patching
Next Topic:	connect / as sysdba fails with ORA-09925 error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 14:14:39 CDT 2024