Re: Partitionining perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 9 Jan 2015 15:45:15 +0100
Message-ID: <CAJu8R6guxhbExWqes6UVGUp6oS4ZrqTOXDpwon70Ta2WJhUryg_at_mail.gmail.com>

I would have started by setting back the optimizer to its 10g version and checked if, with this change, I am back to the 10g database query response time

11g> alter session set optimizer_features_enable = '10.2.0.4'; – put your correct 10g version

11g> execute one of your queries

 If the 10g response time is not back then, as already suggested by Lothar, get a SQL monitoring report and start by checking in what operation time and resources are spent. It could be that statistics are not adequately gathered or that a 10g full segment scan was done via a db file scattered read and switched with the upgrade to a direct path read bypassing the buffer cache, or a table cardinality that was correctly estimated by the CBO in 10g is not anymore correctly estimated following the upgrade etc...

 Best regards

Mohamed Houri

www.hourim.wordpress.com

2015-01-09 15:13 GMT+01:00 Lothar Flatz <l.flatz_at_bluewin.ch>:

> Hi Ashoke,
>
> your memory issue look kind of wierd and certainly not correct. It looks
> like the amount of memory used to be 12 GB in 1o and is now 6 GB. That is
> certainly not the idea. I usually advise people to set PGA and SGA manually.
> (Not use memory target.) There are better people than me to answer the
> memory stuff however. I am no DBA.
> I am performance specialist.
> If you have an issue with the PGA your temp waits will have increased.
>
> However it is very difficult to advise if we have no evidence at all.
> Ideally I would have a sql monitor from one of your slow queries and that
> would tell me everything.
> Or as a minum send the top 5 wait events from AWR.
>
> Thanks
>
> Lothar
>
>
> Am 09.01.15 um 14:58 schrieb Mandal, Ashoke:
>
> Greetings All,
>>
>> I have upgraded one of my 2TB databases with partitioned tables & indexes
>> from Oracle 10.2.0.4 to Oracle 11.2.0.3 recently and experiencing severe
>> poor query performance. The response time of the queries have increased by
>> 20-25 times.
>>
>> Before the upgrade to 11g
>> PGA_AGGREGATE_TARGET=6GB
>> SGA_TARGET=4GB
>> SGA_MAX_SIZE=6GB
>>
>> After the upgrade to 11g
>> MEMORY_TARGET=6GB
>> PGA_AGGREGATE_TARGET=0
>> SGA_TARGET=0
>> SGA_MAX_SIZE=1GB
>>
>> My understanding is that if I set SGA_TARGET and PGA_AGGREGATE_TARGET
>> parameters to 0 then Oracle manages theses as per the MEMORY_TARGET
>> parameter in 11g.
>> Q. Is it true that the SGA can't grow more than 1GB since SGA_MAX_SIZE is
>> still set to 1GB?
>> Q2. Will the performance improve by setting SGA_TARGET to 4GB by default?
>> Q3. Should I also set the PGA_AGGREGATE_TARGET to GB as it was prior the
>> upgrade?
>> Q4. Any known issue of partitioning performance impact after you upgrade
>> a 10g database to 11g?
>>
>> Please let me know if you any suggestions to resolve this issue
>>
>> Appreciate your help in advance,
>> Ashoke
>>
>>
>> [CONFIDENTIALITY AND PRIVACY NOTICE]
>>
>> Information transmitted by this email is proprietary to Medtronic and is
>> intended for use only by the individual or entity to which it is addressed,
>> and may contain information that is private, privileged, confidential or
>> exempt from disclosure under applicable law. If you are not the intended
>> recipient or it appears that this mail has been forwarded to you without
>> proper authority, you are notified that any use or dissemination of this
>> information in any manner is strictly prohibited. In such cases, please
>> delete this mail from your records.
>> To view this notice in other languages you can either select the
>> following link or manually copy and paste the link into the address bar of
>> a web browser: http://emaildisclaimer.medtronic.com
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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

Received on Fri Jan 09 2015 - 15:45:15 CET