When we define the value of memory_target then oracle will define automatically sga_target and pga_aggregate_target , its called automatic memory management. But we can also give the size of sga_target and pga_aggregate_target by using
alter system set sga_target = 500m or by
alter system set pga_aggregate_target = 500m
and we can also give the scope both or other
we can see the currently size of pga_agggregate_target by
show parameter pga_aggregate_target;
Mainly Three memory component in oracle are SGA, PGA and UGA.
SGA is System Global Area
PGA is Precess Global Area
UGA is User Global Area
UGA is to store user variable that we are using during writing procedure or programs and these variables require some memory and that memory is UGA. This is a part of PGA in dedicated server mode.
SGA consists of
we have to define one component and then oracle will automatically assign the memory as if oracle query needs some more shared pool then it takes from buffer cache and give to the shared pool. if requires more in java pool then take the memory from large pool and assign to buffer cache and as a DBA you are not in the risk and oracle will define these things what portion of memory is more require then take memory from other components which want lesser memory.
show parameter shared_pool_size
show parameter java_pool_size
show parameter large_pool_size
If you want to run some java program within the oracle then you need some memory for the same and for that java pool is working.
There is a tool called RMAN, if some data file is corrupted then we can use RMAN tool can be used for taking recovery, we can restore corrupted file from the backup using RMAN tool so for RMAN some memory required.
Any data that is stored in oracle memory stored in oracle buffer cache.
db_cache_size tells the buffer pool size
show parameter db_cache_size
But we can see that it is zero, it is due to assignment is not done by use oracle internally define it. But it is not 0. This value says that DBA did not set the value of this parameter.
Using oracle enterprises manager we can see that what memory actually assigned by oracle. Oracle enterprises manager is a GUI.
If you require more queries then you require more shared pool because queries shored in shared pool and if you require more data then you require more buffer cache. And so for what thing is more require it leaves on oracle software to decide what component requires more or what requires less memory.
PGA is memory area used in sorting, order by, joins like operations
apart from that if you created cursor then that is also oracle objects and requires certain variables and record set so that is also requires PGA.
memory_max_target could not be changed during run time because it a static variable but it can be changed and changes take place after replace it.
alter system set memory_max_target = 1200M scope=spfile;
We have to restart/ shutdown db to take changes in effect.
Oracle block size is also a topic that tells about smallest size of oracle block to store data.
By default size of the oracle block is 8 kb and if 1 row of data requires 1 kb then one block can contain
8 rows. Oracle read data from datafiles in the form of db blocks and store in buffer cache in the form of data blocks.
Currently we have 5 different types of db_block_size 2 kb, 4 kb, 8 kb, 16 kb and 32 kb in sizes.
But before 9i we have only one option 8 kb that is default.
show parameter db_block_size
show parameter db_2k_cache_size
show parameter db_4k_cache_size
show parameter db_16k_cache_size
show parameter db_32k_cache_size
Now a question arises that why we need different size of data blocks?
Answer is when we require to work on a small set of data then it is very good to use 2 or 4kb and when the size is very large in TB or 10 or 100 GB because it is very large data then it become very benefial to use 16 or 32 kb block size.
And OS block size is 512 bytes and when oracle work it usage oracle blocks and in 1 block of oracle db is equivalent to 64 blocks of OS.
db_cache_size is the size of buffer cache and when we go and check it it shows that it is 0, it tells us DBA does not define it by self but it is not mean that it is not set, it is set by oracle automatically.
And we can see it through "Oracle Enterprises Manager" that what actually oracle set for it.
Keep Cache: It is the memory area used to store tables permanently till the shutdown. It is used for storing the objects that is most frequently used and it will exist in it till the oracle restart or shutdown. If we have 100 tables and in that if 5 tables is of used very frequently then we can put them in it and it will save the CPU time to read from data file.
Recycle Cache: It is the memory area used to store tables that is very big and rarely used.and after use the table immediately removed from recycled cache.
We can not see graphicaly or as picture which table is in the memory but can know which table in the memory area.
v$rowcache is tell us which object is in the memory.
Buffer cache is the default cache.
Redo log buffer: Memory are having all the transaction (insert, update or delete) those keep in redolog buffer and log writer writes it to the redolog file.
Shared Pool is the memory area used to store sql queries' compiled code whatever queries you are running their compiled code stored in shared pool.PL Sql programs functions, procedures all goes compiled and stored into the shared pool.
Large Pool: certain backups if you are running
Java Pool: want to do some java code to execute then this memory area can be used
------------------------------------------------------------------------------------------
PGA and UGA
As we know that there are two modes in which we can configure oracle, one is shared server mode and other is dedicated server mode.
When we install oracle on server then it is by default configure in dedicated server mode and for it means that there is an oracle server process for each session or user i.e. dedicated connection for a user.
Shared server mode or multi thread mode: In this one server process for each session or user is not true here. We create a group of shared servers and what it means is
For oracle if there is millions of users for oracle and for each user there is a server process then in this case oracle can not serve the request of all users.
In this we have to create share server and one oracle process can handle thousands of request at a time.
But most of the industry are using dedicated server process.
And one thing I also want to mention that when we are going to ICICI bank or railway reservation system then we can see that we did not go to the database, in between there is interface from where user can connect the db. Interface of that site has so many users thousands or it may be in millions. So they use connection pooling concept in which they create multiple connections to oracle and from them one connection to the oracle from the site can be used by thousands of users, one user can use a connection at a time from the webserver and when the query passed by site to the webserver, it take a connection of oracle and pass the query to it and get the query result and give it to the web server and connection is available for other user and webserver displayed the result.
In a question when you go to the website of ICICI bank and use it about 20 minutes in this duration how much time actually you are getting data from there. first query is validating your user name and password and second want to your current statement and third is for statement of previous month and transfer money to other account then in during these four work and in these work if 30 minutes are taken by the bank site then in this 30 minute you sitting ideal and looking at the data and when you click on submit or online transfer then you intract with the oracle so the oracle connection used by you is very less during 30 minutes.
------------------------------------------------------------------------------------------------------
THE ORACLE SGA
> SGA = System global area
> Shared memory area
> It contains:
> Shared Pool
> Large Pool
> Java Pool
> Buffer Cache
> Redo Log Buffer
> Overall size limited by SGA_MAX_SIZE(oracle 9i/10g)
> Oracle 11g Total Oracle Memory = MEMORY_TARGET= SGA+PGA
In oracle 9i or prior to it, the concept was to define each memory component manually (Shared Pool, Large Pool, Java Pool, Buffer Cache etc.)
In oracle 10g, the concept was automatic shared memory management in which setting of SGA_MAX_SIZE parameter.
In oracle 11g, the concept is automatic memory management in which setting MEMORY_MAX_TARGET and MEMORY_TARGET
And after setting of MEMORY_TARGET and MEMORY_MAX_TARGET, oracle will manage all the settings (SGA, PGA, Buffer cache, Large Pool and any other memory pools) automatically and we have not to worry about it, oracle internally do the thing for us.
Memory management is very complex thing before 10g.
MEMORY_MAX_TARGET is static variable but
MEMORY_TARGET is dynamic parameter
In case of static variable changes will effective after shutdown but in dynamic it it applicable instantly after change.
MEMORY_MAX_TARGET = maximum memory can give to oracle
MEMORY_TARGET could be less or equal to MEMORY_MAX_TARGET.
----------------------------------------------------------------------------------------------------------
SIZE OF SGA AREAS
Fixed Size: It is used for the oracle internal management
Variable Size: It is used for Shared Pool, Large Pool, Java Pool
Database Buffers: Buffer Cache
Redo Buffers: Redolog_buffer
-------------------------------------------------------------------------------------------------------------
> Libarary Cache is the part of Shared Pool in SGA.
> Libarary Cache is used to store Parsed SQL statement in memory area called cursor.
> Parsed statements can be reused: One time you run the query then query is parsed and stored in it and again you want to run the same query then oracle will not go to parse it again so the parsing time will saved. So if you reuse the query then the performance of system will increase. One thing to remember that the query should be exactly same as previous if find any changes in it (even case could not be changed) it will reparse and execute.
As we know that procedures are group of queries.
One thing we have always keep in mind that make your code reusable and by this we can avoid parsing and accesive cpu usage. So when we avoid compilation you are actually reducing the CPU usage.
How long the parsed query retain in the library is called library cache retention.
There are four ways to flush parsed SQL:
2. If we run a query then the parsed query go in to the library cache and if we alter the table then all the queries related to this table present in library cache are flushed from library cache and when we go to run it again it will parse again.
In production server it is always say that any alter related work should be done during the lowest activities time on it. If 1000 users are running their queries and you perform the alter command at same time then all the parsed code gone from library cache and again have to parse all 1000 queries in seconds and in that your system performance is drastically decrease and users will start complaining.
3. You want to remove everything from your shared pool then the command is
"ALTER SYSTEM FLUSH SHARED_POOL"
Its DBAs responsibility to remove the older version of queries from library cache so that newer version of queries can take place so we have to use "ALTER SYSTEM FLUSH SHARED_POOL",
When we capture stats of a table at that time compiled code of that table removed from library cache and command is
SQL> analyze table scott.emp compute statistics;
This command is actually calculate how many number of rows in the table internally and flush the all the queries related to this table from library cache.
4. When you shut down the instance then not only library cache, all the other buffer pool, library cache, java pool, large pool, shared pool memory area(all memory area swapped out) are swiped out.
Here we will describe how query is on progressing
Procedure is an object that does a job in oracle(update, delete and insert in employee) without returning any value but function is also doing a job but returning some value(function to get the maximum salary of employee), package is a set of procedure and function e.g. employee management package. Once you have created these objects it stored in data dictionary and library cache and next time when we use these, we will reuse them that maximise the reuse.
Data Dictionary Cache:
1. Its part of shared pool
2. It caches portions of data dictionary
> information of segment : segment is a oracle term that store some disk storage space, any table you create it takes diskspaces and it called segment.When one is asking that how much storage required for the table then calculate the size of segments will be the storage and usage of storage required is stored in data dictionary. Total size of table stored in the data dictionary.
How many segment you have this dictionary have that information so we can say that size of table is equal to total number of segments required have the table.
> Space availability Information :Free space will calculate using a view dba_free_space.
SQL>select tablespace_name, sum(bytes)/1024/1024 Availed_total_free_space from dba_free_space group by tablespace_name;
If you want to know the queries exists in library cache then use v$sql, v$sqlarea. v$sqlarea stores all queries the queries that exist in library cache.
Object privilege information:
It stores what user have what table access permission. Like scott user has access privilege of emp table and if other user want to access that table then need permisssion so whatever permistions or grants in the database are also stored in dictionary cache.
When run a DDL statement then data dictionary get validated, when we want to add space to a table then add in terms of extents then data dictionary validate.
Table name and column names get validated through data dictionary so have the information for the same.
alter system set sga_target = 500m or by
alter system set pga_aggregate_target = 500m
and we can also give the scope both or other
we can see the currently size of pga_agggregate_target by
show parameter pga_aggregate_target;
Mainly Three memory component in oracle are SGA, PGA and UGA.
SGA is System Global Area
PGA is Precess Global Area
UGA is User Global Area
UGA is to store user variable that we are using during writing procedure or programs and these variables require some memory and that memory is UGA. This is a part of PGA in dedicated server mode.
SGA consists of
- Shared Pool
- Java Pool
- Large Pool
- Buffer Cache
- Sub caches
- Keep cache and Recycle cache
we have to define one component and then oracle will automatically assign the memory as if oracle query needs some more shared pool then it takes from buffer cache and give to the shared pool. if requires more in java pool then take the memory from large pool and assign to buffer cache and as a DBA you are not in the risk and oracle will define these things what portion of memory is more require then take memory from other components which want lesser memory.
show parameter shared_pool_size
show parameter java_pool_size
show parameter large_pool_size
If you want to run some java program within the oracle then you need some memory for the same and for that java pool is working.
There is a tool called RMAN, if some data file is corrupted then we can use RMAN tool can be used for taking recovery, we can restore corrupted file from the backup using RMAN tool so for RMAN some memory required.
Any data that is stored in oracle memory stored in oracle buffer cache.
db_cache_size tells the buffer pool size
show parameter db_cache_size
But we can see that it is zero, it is due to assignment is not done by use oracle internally define it. But it is not 0. This value says that DBA did not set the value of this parameter.
Using oracle enterprises manager we can see that what memory actually assigned by oracle. Oracle enterprises manager is a GUI.
If you require more queries then you require more shared pool because queries shored in shared pool and if you require more data then you require more buffer cache. And so for what thing is more require it leaves on oracle software to decide what component requires more or what requires less memory.
PGA is memory area used in sorting, order by, joins like operations
apart from that if you created cursor then that is also oracle objects and requires certain variables and record set so that is also requires PGA.
memory_max_target could not be changed during run time because it a static variable but it can be changed and changes take place after replace it.
alter system set memory_max_target = 1200M scope=spfile;
We have to restart/ shutdown db to take changes in effect.
Oracle block size is also a topic that tells about smallest size of oracle block to store data.
By default size of the oracle block is 8 kb and if 1 row of data requires 1 kb then one block can contain
8 rows. Oracle read data from datafiles in the form of db blocks and store in buffer cache in the form of data blocks.
Currently we have 5 different types of db_block_size 2 kb, 4 kb, 8 kb, 16 kb and 32 kb in sizes.
But before 9i we have only one option 8 kb that is default.
show parameter db_block_size
show parameter db_2k_cache_size
show parameter db_4k_cache_size
show parameter db_16k_cache_size
show parameter db_32k_cache_size
Now a question arises that why we need different size of data blocks?
Answer is when we require to work on a small set of data then it is very good to use 2 or 4kb and when the size is very large in TB or 10 or 100 GB because it is very large data then it become very benefial to use 16 or 32 kb block size.
And OS block size is 512 bytes and when oracle work it usage oracle blocks and in 1 block of oracle db is equivalent to 64 blocks of OS.
db_cache_size is the size of buffer cache and when we go and check it it shows that it is 0, it tells us DBA does not define it by self but it is not mean that it is not set, it is set by oracle automatically.
And we can see it through "Oracle Enterprises Manager" that what actually oracle set for it.
Keep Cache: It is the memory area used to store tables permanently till the shutdown. It is used for storing the objects that is most frequently used and it will exist in it till the oracle restart or shutdown. If we have 100 tables and in that if 5 tables is of used very frequently then we can put them in it and it will save the CPU time to read from data file.
Recycle Cache: It is the memory area used to store tables that is very big and rarely used.and after use the table immediately removed from recycled cache.
We can not see graphicaly or as picture which table is in the memory but can know which table in the memory area.
v$rowcache is tell us which object is in the memory.
Buffer cache is the default cache.
Redo log buffer: Memory are having all the transaction (insert, update or delete) those keep in redolog buffer and log writer writes it to the redolog file.
Shared Pool is the memory area used to store sql queries' compiled code whatever queries you are running their compiled code stored in shared pool.PL Sql programs functions, procedures all goes compiled and stored into the shared pool.
Large Pool: certain backups if you are running
Java Pool: want to do some java code to execute then this memory area can be used
------------------------------------------------------------------------------------------
PGA and UGA
- Private memory areas
- Each server process has their own
- Program Global Area
- Private memory for a process
- Global variables, data structures and control information
- Not part of SGA
- Size has performance impact, especially for complex long running queries
Each user has its own PGA and total PGA is sum of all the memory used by all users PGA.
SGA is shared but each user has its own PGA.
------------------------------------------------------------------------------------------
Cursor is an object that stores the result set.Cursor in PGA
When you are running the query you make the memory area of PGA is busy used for your query
and if you write a query which is slow and it take 200MB or 300MB memory to execute then this area can not be used by other users and for that memory other users have to wait.
The main work of DBA is to always have look for smooth and fast run database and always try to improve the performance of DB and query.
As we know that managing PGA can do by two ways. First is by defining the indivisual components like sort_area_size and hash_area_size(this is manual PGA tuning) and other is automatic define PGA and for that WORKAREA_SIZE_POLICY set to auto and pga_aggregate_target define by value. Now a days PGA is maintained by automatic setting through oracle.
UGA: User global area
Cursor is an object that stores the result set.Cursor in PGA
When you are running the query you make the memory area of PGA is busy used for your query
and if you write a query which is slow and it take 200MB or 300MB memory to execute then this area can not be used by other users and for that memory other users have to wait.
The main work of DBA is to always have look for smooth and fast run database and always try to improve the performance of DB and query.
As we know that managing PGA can do by two ways. First is by defining the indivisual components like sort_area_size and hash_area_size(this is manual PGA tuning) and other is automatic define PGA and for that WORKAREA_SIZE_POLICY set to auto and pga_aggregate_target define by value. Now a days PGA is maintained by automatic setting through oracle.
UGA: User global area
- Private memory for session state
- UGA within PGA in dedicated server mode
- UGA within SGA in shared server mode
As we know that there are two modes in which we can configure oracle, one is shared server mode and other is dedicated server mode.
When we install oracle on server then it is by default configure in dedicated server mode and for it means that there is an oracle server process for each session or user i.e. dedicated connection for a user.
Shared server mode or multi thread mode: In this one server process for each session or user is not true here. We create a group of shared servers and what it means is
For oracle if there is millions of users for oracle and for each user there is a server process then in this case oracle can not serve the request of all users.
In this we have to create share server and one oracle process can handle thousands of request at a time.
But most of the industry are using dedicated server process.
And one thing I also want to mention that when we are going to ICICI bank or railway reservation system then we can see that we did not go to the database, in between there is interface from where user can connect the db. Interface of that site has so many users thousands or it may be in millions. So they use connection pooling concept in which they create multiple connections to oracle and from them one connection to the oracle from the site can be used by thousands of users, one user can use a connection at a time from the webserver and when the query passed by site to the webserver, it take a connection of oracle and pass the query to it and get the query result and give it to the web server and connection is available for other user and webserver displayed the result.
In a question when you go to the website of ICICI bank and use it about 20 minutes in this duration how much time actually you are getting data from there. first query is validating your user name and password and second want to your current statement and third is for statement of previous month and transfer money to other account then in during these four work and in these work if 30 minutes are taken by the bank site then in this 30 minute you sitting ideal and looking at the data and when you click on submit or online transfer then you intract with the oracle so the oracle connection used by you is very less during 30 minutes.
------------------------------------------------------------------------------------------------------
THE ORACLE SGA
> SGA = System global area
> Shared memory area
> It contains:
> Shared Pool
> Large Pool
> Java Pool
> Buffer Cache
> Redo Log Buffer
> Overall size limited by SGA_MAX_SIZE(oracle 9i/10g)
> Oracle 11g Total Oracle Memory = MEMORY_TARGET= SGA+PGA
In oracle 9i or prior to it, the concept was to define each memory component manually (Shared Pool, Large Pool, Java Pool, Buffer Cache etc.)
In oracle 10g, the concept was automatic shared memory management in which setting of SGA_MAX_SIZE parameter.
In oracle 11g, the concept is automatic memory management in which setting MEMORY_MAX_TARGET and MEMORY_TARGET
And after setting of MEMORY_TARGET and MEMORY_MAX_TARGET, oracle will manage all the settings (SGA, PGA, Buffer cache, Large Pool and any other memory pools) automatically and we have not to worry about it, oracle internally do the thing for us.
Memory management is very complex thing before 10g.
MEMORY_MAX_TARGET is static variable but
MEMORY_TARGET is dynamic parameter
In case of static variable changes will effective after shutdown but in dynamic it it applicable instantly after change.
MEMORY_MAX_TARGET = maximum memory can give to oracle
MEMORY_TARGET could be less or equal to MEMORY_MAX_TARGET.
----------------------------------------------------------------------------------------------------------
SIZE OF SGA AREAS
Fixed Size: It is used for the oracle internal management
Variable Size: It is used for Shared Pool, Large Pool, Java Pool
Database Buffers: Buffer Cache
Redo Buffers: Redolog_buffer
-------------------------------------------------------------------------------------------------------------
> Libarary Cache is the part of Shared Pool in SGA.
> Libarary Cache is used to store Parsed SQL statement in memory area called cursor.
> Parsed statements can be reused: One time you run the query then query is parsed and stored in it and again you want to run the same query then oracle will not go to parse it again so the parsing time will saved. So if you reuse the query then the performance of system will increase. One thing to remember that the query should be exactly same as previous if find any changes in it (even case could not be changed) it will reparse and execute.
As we know that procedures are group of queries.
One thing we have always keep in mind that make your code reusable and by this we can avoid parsing and accesive cpu usage. So when we avoid compilation you are actually reducing the CPU usage.
How long the parsed query retain in the library is called library cache retention.
There are four ways to flush parsed SQL:
- Ages out with LRU (Least Recently Used) algorithm
- Referenced objects altered
- ALTER SYSTEM FLUSH SHARED_POOL
- instance is shutdown
2. If we run a query then the parsed query go in to the library cache and if we alter the table then all the queries related to this table present in library cache are flushed from library cache and when we go to run it again it will parse again.
In production server it is always say that any alter related work should be done during the lowest activities time on it. If 1000 users are running their queries and you perform the alter command at same time then all the parsed code gone from library cache and again have to parse all 1000 queries in seconds and in that your system performance is drastically decrease and users will start complaining.
3. You want to remove everything from your shared pool then the command is
"ALTER SYSTEM FLUSH SHARED_POOL"
Its DBAs responsibility to remove the older version of queries from library cache so that newer version of queries can take place so we have to use "ALTER SYSTEM FLUSH SHARED_POOL",
When we capture stats of a table at that time compiled code of that table removed from library cache and command is
SQL> analyze table scott.emp compute statistics;
This command is actually calculate how many number of rows in the table internally and flush the all the queries related to this table from library cache.
4. When you shut down the instance then not only library cache, all the other buffer pool, library cache, java pool, large pool, shared pool memory area(all memory area swapped out) are swiped out.
Here we will describe how query is on progressing
Data Dictionary Cache:
1. Its part of shared pool
2. It caches portions of data dictionary
> information of segment : segment is a oracle term that store some disk storage space, any table you create it takes diskspaces and it called segment.When one is asking that how much storage required for the table then calculate the size of segments will be the storage and usage of storage required is stored in data dictionary. Total size of table stored in the data dictionary.
How many segment you have this dictionary have that information so we can say that size of table is equal to total number of segments required have the table.
> Space availability Information :Free space will calculate using a view dba_free_space.
SQL>select tablespace_name, sum(bytes)/1024/1024 Availed_total_free_space from dba_free_space group by tablespace_name;
If you want to know the queries exists in library cache then use v$sql, v$sqlarea. v$sqlarea stores all queries the queries that exist in library cache.
Object privilege information:
It stores what user have what table access permission. Like scott user has access privilege of emp table and if other user want to access that table then need permisssion so whatever permistions or grants in the database are also stored in dictionary cache.
When run a DDL statement then data dictionary get validated, when we want to add space to a table then add in terms of extents then data dictionary validate.
Table name and column names get validated through data dictionary so have the information for the same.
No comments:
Post a Comment