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@xxxxxxxxxx>: > 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 >> -- >> //www.freelists.org/webpage/oracle-l >> >> >> > -- > //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>