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

From: Lothar Flatz <>
Date: Sun, 11 Jan 2015 12:57:51 +0100
Message-ID: <>

I would go a bit further a question the point in saving memory. & GB is less than most PCs have. And memory is license free.

Am 10.01.2015 um 01:21 schrieb Ls Cheng:
> I always have a doubt, why people set SGA_TARGET smaller than
> SGA_MAX_SIZE. What is the objective of doing so?
> Regarding your issues, as many already pointed out you have cut your
> memory assignment to half, that is first thing to look at. The second
> is obviously the execution plans. Are the statistics refreshed in
> 11.2? Can you provide a couple of AWR in 10g and in 11g so we can compare?
> Thanks
> On Fri, Jan 9, 2015 at 7:02 PM, Mandal, Ashoke
> < <>>
> wrote:
> Hello Mark, Originally PGA_AGGREGATE_TARGET value of 6GB was too
> high. I meant to use total memory(MEMORY_TARGET) of 6GB to manage
> everything.
> Leaving SGA_MAX_SIZE=1G was a typo error. I wanted to set this to
> 0 so that Oracle allocate as needed basis. There is also another
> thing about this upgraded database. We haven’t changed the
> compatible parameter to to 11.2.0 yet and it is still at 10.2.0.
> Do you see any negative impact due to the compatible setting as
> 10.20.0 for the 11g database?
> I will set the SGA_MAX_SIZE to 0 and the compatible to ’11.2.0’
> and bounce the database and see if that helps the performance.
> Thanks,
> Ashoke
> <>]
> *Sent:* Friday, January 09, 2015 11:20 AM
> *To:* Mandal, Ashoke
> *Cc:*
> <>;
> <>
> *Subject:* Re: Partitionining perfortmance degraded drastically
> after upgrading the database from Oracle 102.0.4 to Oracle
> It rather looks like you have cut your SGA from 6 GB (which may
> already have been rather lean) to 1GB, and reduced the total
> available memory from 12 GB to 6GB.
> Why are you surprised that this affects performance? I doubt the
> problem you are encountering has anything to do with
> Partitioning. There is probably almost NO memory available for
> buffer cache, and quite likely too little for the shared pool,
> too. The (more modest) reduction in memory available for the PGA
> may affect execution plans by causing a bias from HASH joins
> toward NESTED LOOPS.
> Commonly, people choose (need) to *increase* the memory available
> to the SGA when upgrading. At the very least, they will keep
> memory unchanged. Slashing the memory footprint in half is an
> uncommon move during an upgrade, and one that you will probably
> want to rethink.
> If you want more help here, let's start with some DATA. Something
> like the "Top-5 Waits" for pre- and post-upgrade might be a good
> place to start. After that, perhaps execution plans and wait
> information for the (most) affected queries. Without this, people
> here can only guess at the actual cause of your problem, although
> if enough of us guess for a long enough time, somebody will
> undoubtedly find the right answer eventually. (My money is on the
> changes to memory; I suggest we eliminate THAT as a cause before
> we move on to other possibilities, since we KNOW that was changed.)
> On Fri, Jan 9, 2015 at 8:58 AM, Mandal, Ashoke
> <
> <>> wrote:
> 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:
> --


Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.

Received on Sun Jan 11 2015 - 12:57:51 CET

Original text of this message