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

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
> <ashoke.k.mandal_at_medtronic.com <mailto:ashoke.k.mandal_at_medtronic.com>>
> 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
>
> *From:*MARK BRINSMEAD [mailto:mark.brinsmead_at_gmail.com
> <mailto:mark.brinsmead_at_gmail.com>]
> *Sent:* Friday, January 09, 2015 11:20 AM
> *To:* Mandal, Ashoke
> *Cc:* oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>; oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>
> *Subject:* Re: Partitionining perfortmance degraded drastically
> after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3
>
> 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
> <ashoke.k.mandal_at_medtronic.com
> <mailto:ashoke.k.mandal_at_medtronic.com>> wrote:
>
> 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
>
>

-- 






---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

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