No question at this time
DBA Top 10
1 B. Vroman 11100
2 M. Cadot 10000
3 T. Boles 6850
4 A. Kavsek 6300
5 J. Schnackenberg 5100
6 P. Wisse 2500
7 M. Hidayathullah ... 1900
8 T. P 1200
9 G. Lambregts 1100
9 . Lauri 1100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48336
Total active users1567
Act. users last 24h2
Act. users last hour0
Registered user hits last week225
Registered user hits last month1036
Go up

After upgrade DB Performance is slow
Next thread: Transparent instance failover?
Prev thread: IDLE_TIME

Message Score Author Date
Dear Experts , After upgrade to 12c from 11g,...... Jill Salalila Feb 07, 2019, 13:42
Hi Philippines, 4 approaches: 1) Start from th...... Philip Wisse Feb 08, 2019, 14:39
Hi Jill, in addition to Philip suggestions, I w...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Feb 09, 2019, 20:37
Also, if you made a mistake and upgraded your prod...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek Feb 09, 2019, 20:59
The latest oracle patch january 2019 should have a...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Rob Pattyn Feb 13, 2019, 11:49

Follow up by mail Click here


Subject: After upgrade DB Performance is slow
Author: Jill Salalila, Philippines
Date: Feb 07, 2019, 13:42, 9 days ago
Os info: RHEL 6.4
Oracle info: 12.x
Message: Dear Experts ,


After upgrade to 12c from 11g,
Database performance is not good as previous version.

Scheduled Jobs are taking more time but previously it took 2 hours,
after upgrade jobs are running more than 6 hours.
Why this behavior ?
We are NOT using multitenant Feature ?

Thanks
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: After upgrade DB Performance is slow
Author: Philip Wisse, Netherlands
Date: Feb 08, 2019, 14:39, 8 days ago
Message: Hi Philippines,

4 approaches:
1) Start from the job. Isolate the SQL and look at the explain plan
Try to delete or refresh statistics
2) Start from AWR report (or statspack if you don't have the license)
Espessially AWR is full of advices
3) Use buildin advisories:
https://docs.oracle.com/database/121/ADMQS/GUID-8DE70F1D-E161-45D2-BE01-E9972883BCEC.htm#ADMQS1031
4) Have a look a changed init.ora parameters
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/initialization-parameter-changes-oracle-database-12c-r2.html#GUID-315015C7-EBC3-4AB4-B2E3-EB4A2F2A9452

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: After upgrade DB Performance is slow
Author: Ales Kavsek, Slovenia
Date: Feb 09, 2019, 20:37, 7 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Jill,

in addition to Philip suggestions, I wonder why're you hiding RDBMS version from us? You were very exact with OS version RHEL 6.4, can you be as precise with RDBMS version (12.1.0.2 ?, 12.2.0.1, ...). For example, Oracle made some cardinal mistakes in 12cR1, so we might pin point some things to check. It's a poor practice to hold back such basic and important info while seeking technical help....

But in general, moving from one major version to the other brings changes to CBO behavior, this was discussed many times on this site, for example in this thread:

https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=28238

we listed several changes that can hit us going from 9i to 10g. Upgrading 11g to 12c brings new CBO features (and bugs). Going from 11g to 12c can speed some queries and/or bring down performance for others. I hope you have old configuration in place, so you'll be able to compare execution plans of (now) slow queries.

I don't believe that performance went down for *ALL* queries compared to 11g! If this is the case, then probably something really bad happened at OS/HW configuration level...(storage mount points, memory, OS kernel parameters,...).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: After upgrade DB Performance is slow
Author: Ales Kavsek, Slovenia
Date: Feb 09, 2019, 20:59, 7 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Also, if you made a mistake and upgraded your production database from 11g to 12c without prior proper testing you're probably working under a stress, looking for a short term rescue....
In situation like this, when the same query performs radically different (worse) on newer DB release, I'm fond of using OPTIMIZER_FEATURE_ENABLE

https://docs.oracle.com/database/121/REFRN/GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C.htm#REFRN10141

...most often as a hint in a "slow" query, the way I described in this thread:

https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=91686

...and the same time we should be aware that this must NOT be a permanent fix...

https://blogs.oracle.com/upgrade/why-you-shouldnt-set-optimizerfeaturesenable

Using this parameter can give us some time for a research to find a true origin for poor performance.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: After upgrade DB Performance is slow
Author: Rob Pattyn, Belgium
Date: Feb 13, 2019, 11:49, 3 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: The latest oracle patch january 2019 should have a fix for histograms that are used for query evaluation

See https://jonathanlewis.wordpress.com/2018/01/15/histogram-hassle/
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here