Oracle simplified memory management over the last few versions of the
database. Oracle 9i automated PGA management by introducing
PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by
automating SGA management using the SGA_TARGET parameter. Oracle 11g
takes this one step further by allowing you to allocate one chunk of
memory, which Oracle uses to dynamically manage both the SGA and PGA.
Automatic memory management is configured using two new initialization parameters:
•MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".
•MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.
When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.
When you create your database, you can set the parameter that takes nearly all memory tuning out of your hands: MEMORY_TARGET.
After you type this parameter in SQL*Plus — show parameter memory_target — (the SQL command-line interface available in Oracle), you see this output on the screen:
NAME TYPE VALUE
------------------------------------ -----------
Automatic memory management lets you take hold of the amount of memory on the system and then decide how much you want to use for the database.
It’s never obvious what value you should choose as a starting point. Answer these questions to help set the value:
✓ How much memory is available?
✓ How many databases will ultimately be on the machine?
✓ How many users will be on the machine? (If many, we allocate 4MB per
user for process overhead.)
✓ What other applications are running on the machine?
Before the users get on the machine, consider taking no more than 40 percent of the memory for Oracle databases. Use this formula:
(GB of memory × .40) / Number of Eventual Databases = GB for MEMORY_TARGET per database
For example, if your machine had 8GB of memory and will ultimately house two databases similar in nature and only 100 users each, we would have this equation: (8 × .40) / 2 = 1.6GB for MEMORY_TARGET per database.
To help determine whether you have enough memory, Oracle gives you some pointers if you know where to look. It’s called the Memory Target Advisor.
Find it from the command line in the form of the view V$MEMORY_TARGET_ADVICE.
Automatic memory management is configured using two new initialization parameters:
•MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".
•MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.
When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.
When you create your database, you can set the parameter that takes nearly all memory tuning out of your hands: MEMORY_TARGET.
After you type this parameter in SQL*Plus — show parameter memory_target — (the SQL command-line interface available in Oracle), you see this output on the screen:
NAME TYPE VALUE
------------------------------------ -----------
memory_target big integer 1560M
Automatic memory management lets you take hold of the amount of memory on the system and then decide how much you want to use for the database.
It’s never obvious what value you should choose as a starting point. Answer these questions to help set the value:
✓ How much memory is available?
✓ How many databases will ultimately be on the machine?
✓ How many users will be on the machine? (If many, we allocate 4MB per
user for process overhead.)
✓ What other applications are running on the machine?
Before the users get on the machine, consider taking no more than 40 percent of the memory for Oracle databases. Use this formula:
(GB of memory × .40) / Number of Eventual Databases = GB for MEMORY_TARGET per database
For example, if your machine had 8GB of memory and will ultimately house two databases similar in nature and only 100 users each, we would have this equation: (8 × .40) / 2 = 1.6GB for MEMORY_TARGET per database.
To help determine whether you have enough memory, Oracle gives you some pointers if you know where to look. It’s called the Memory Target Advisor.
Find it from the command line in the form of the view V$MEMORY_TARGET_ADVICE.
Badhiyaan sir !!
ReplyDeletePadha tha pr samajh ab aya iske bare me :) Jazak Allah khair...
Wese itne sare blog posts ek sath?? 8 posts in the time span of 29 mins :)
Aisi speed se likhoge sir to google khud k blog k liye hire kr lega ek din :)
This article is really helpful...thanks mudassar. Keep it up.
ReplyDeleteAdil: Actually post bahut pehle likh liye the.. Aaj sabko consoidate kar liya apne Technical Lead ke Validation ke baad ... Dont worry itni jaldi GOOGLE mein nahi jaane waala mein :)
ReplyDeleteThinking Thread: Thanks Your Words Mean a lot :)
ReplyDelete