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

From: Mohamed Houri <>
Date: Fri, 9 Jan 2015 15:45:15 +0100
Message-ID: <>

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 = ''; – 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

2015-01-09 15:13 GMT+01:00 Lothar Flatz <>:

> 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 to Oracle recently and experiencing severe
>> poor query performance. The response time of the queries have increased by
>> 20-25 times.
>> Before the upgrade to 11g
>> After the upgrade to 11g
>> 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
>> 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:
>> --
> --


Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <>

Visit My         - Blog <>

Let's Connect -
Profile <>*

My Twitter <>      - MohamedHouri

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

Original text of this message