Home » RDBMS Server » Performance Tuning » After updating memory_target, performance is degraded (11g, 11.2.0.3, solaris10)
After updating memory_target, performance is degraded [message #612962] Mon, 28 April 2014 05:36 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

Last day, I made memory parameters to automatic by updating memory_target & memory_max_target. But still show sga_max_size values.

While long running sql stmt, performance is degraded. Since long running is executing, pga are taking memories from sga. Is this the issue?

Do i need to revert the changes for long running sql stmt?

Waiting for your valuable response on the same...

Regards,
Ashish Kumar Mahanta
Re: After updating memory_target, performance is degraded [message #612967 is a reply to message #612962] Mon, 28 April 2014 06:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Those two parameters are just a part of Automatic memory management. In order to let Oracle completely take control over the memory management, even the SGA_TARGET and PGA_AGGREGATE_TARGET should be set to default(zero).
Re: After updating memory_target, performance is degraded [message #612973 is a reply to message #612967] Mon, 28 April 2014 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, in 11g you only have memory_target & memory_max_target, as OP said, if you want to "let Oracle completely take control over the memory management".

You can still use the older parameters (shared_pool_size, db_cache_size...) if you want to set minimal value for shared pool, buffer cache and so on.


Re: After updating memory_target, performance is degraded [message #612974 is a reply to message #612962] Mon, 28 April 2014 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Mixing sga_max_size and memory*_target is not a good idea, remove sga_max_size.
If you can't make it work with only memory*_target then go back to your previous settings.
Have a look at V$MEMORY_RESIZE_OPS and V$SGA_RESIZE_OPS views to check if something wrong happened in memory allocation.

Re: After updating memory_target, performance is degraded [message #612981 is a reply to message #612973] Mon, 28 April 2014 07:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Mon, 28 April 2014 17:52

No, in 11g you only have memory_target & memory_max_target, as OP said, if you want to "let Oracle completely take control over the memory management".


Yes, I know. Since OP is facing performance issues after setting the memory parameters, I guess the values might not have been set properly for the parameters. And to set the memory target, sga_target and pga_aggregate_target has to be determined.

@OP, if you want to let Oracle completely take control over the memory management, have a look at this link from documentation about Enabling Automatic Memory Management.
Re: After updating memory_target, performance is degraded [message #613048 is a reply to message #612981] Tue, 29 April 2014 02:41 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

I set sga_aggregate & pga_aggregate values to "0". And after getting sum of it, set memory_target values to little higher side.

It was showing "0" on sga_target, sga_aggregate, pga_aggregate values. But also sga_max_size was appearing values as similiar to memory_max_size. I ignored it and proceeded.

Yestarday night, I changed it from Automatic to manual and user are able to execute long update queries. They dont have any problem now. But my worries is, why automatic is not performing well for long running updates query?

Thanks for providing the links. I have gone through it but still, confused.


Regards,
Ashish Kumar Mahanta
Re: After updating memory_target, performance is degraded [message #613053 is a reply to message #613048] Tue, 29 April 2014 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Automatic memory is designed to optimize the whole database workload not some specific statements.
You can always switch to manual settings for your session using "ALTER SESSION SET workarea_size_policy=MANUAL" and setting the sort/hash/... area sizes.

Re: After updating memory_target, performance is degraded [message #613125 is a reply to message #613053] Wed, 30 April 2014 05:42 Go to previous message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

Thanks for clarifying my doubt.

Regards,
Ashish kumar Mahanta
Previous Topic: performance Tuning
Next Topic: Full table scan - Queries
Goto Forum:
  


Current Time: Thu Mar 28 10:45:27 CDT 2024