The SGA area of the Oracle 9i database instance is mainly composed of the following sections:
· Database Buffer Cache
Redo log buffer
· Shared pool
· Java pool
Large Pool (optional)
Data Dictionary Cache
· Other Miscellaneous Information Information
Oracle Specifies how much virtual memory used when started, and allows instances at runtime to adjust the SGA by adjusting the size of Buffer Cache, Shared Pool, Large Pool, and PGA. If the value of SGA_MAX_SIZE is specified in the parameter file, and this value is less than the sum of each SGA component, then this parameter will be ignored; if the value is greater than the sum of each SGA component, then the maximum value of SGA can increase is SGA_MAX_SIZE.
The minimum unit allocated by the dynamic SGA is called Granule, and Oracle always adjusts the integer multiple of Granule. The size of the Granule is determined by the size of the SGA size. If the SGA is less than 128m, the Granule size is 4m; if SGA is greater than 128m, the GRANULE size is 16m. However, it is also related to the operating system platform, such as a platform for 32-bit Winx, if the SGA size is greater than 128m, and the Granule size is 8m.
If the SGA is changed through the alter system command, if the specified size is not an integer multiple of the Granule, the allocation principle is tied upward based on the specified value. All components of dynamic SGA use the same size Granule. The information of each dynamic SGA component can be viewed via V $ SGA_DYNAMIC_COMPONENTS:
Let's take a look at the size of the initial granule_size and the current shared_pool_size:
SQL> Select Component, Current_Size,
2 Granule_Size
3 from V $ SGA_DYNAMIC_COMPONENTS;
Component Current_size Granule_size
------------------------------------------
Shared pool 12582912 4194304
Large pool 8388608 4194304
Buffer cache 25165824 4194304
It can be seen that groom_size is 4MB, current shared_pool_size is 12MB
Now set shared_pool_size to 10MB, then look at the actual changes of Shared_Pool_Size:
SQL> ALTER system set shared_pool_size = 10m;
The system has changed.
SQL> Select Component, Current_Size,
2 Granule_Size
3 from V $ SGA_DYNAMIC_COMPONENTS;
Component Current_size Granule_size
------------------------------------------
Shared pool 12582912 4194304lage pool 8388608 4194304
Buffer cache 25165824 4194304
It can be seen that the current shared_pool_size is still 12MB, not a specified 10MB. This is because 10MB is tied upward according to Granule_Size, that is, allocated or 12MB.
Now set shared_pool_size to 3MB, then look at the actual changes of shared_pool_size:
SQL> ALTER system set shared_pool_size = 3m;
The system has changed.
SQL> Select Component, Current_Size,
2 Granule_Size
3 from V $ SGA_DYNAMIC_COMPONENTS;
Component Current_size Granule_size
------------------------------------------
Shared pool 4194304 4194304
Large pool 8388608 4194304
Buffer cache 25165824 4194304
The current shared_pool_size has changed to 4MB, not the specified 3MB. This is because 3MB is tied upward according to Granule_size, so that it is actually assigned 4MB.
Now set Shared_Pool_SIZE to 5MB, then see the actual changes of Shared_Pool_Size:
SQL> ALTER SYSTEM SET Shared_pool_size = 5m;
The system has changed.
SQL> Select Component, Current_Size,
2 Granule_Size
3 from V $ SGA_DYNAMIC_COMPONENTS;
Component Current_size Granule_size
------------------------------------------
Shared pool 8388608 4194304
Large pool 8388608 4194304
Buffer cache 25165824 4194304
SQL>
The current shared_pool_size has changed to 8MB, not the specified 5MB. This is because 5MB is fetched upward according to the granule_size, i.e., actually allocated 8MB.
The smallest SGA configuration is 3 Granule, one for securing SGA (including Redo Buffers), one for Buffer Cache, one for shared pool.
When the instance is run, we can dynamically modify the size of the Buffer Cache, Shared Pool, and Large Pool memory through the alter system command:
SQL> SELECT Component, Current_Size, Min_Size, Max_Size, Granule_Size
2 from V $ SGA_DYNAMIC_Components;
Component Current_size min_size max_size granule_size
-------------------------------------------------------------- - ------------ Shared pool 8388608 8388608 8388608 4194304
Large pool 8388608 8388608 8388608 4194304
Buffer cache 25165824 25165824 25165824 4194304
SQL> ALTER system set shared_pool_size = 4m scope = memory;
The system has changed.
SQL> SELECT Component, Current_Size, Min_Size, Max_Size, Granule_Size
2 from V $ SGA_DYNAMIC_Components;
Component Current_size min_size max_size granule_size
-------------------------------------------------------------- - ----------------
Shared pool 4194304 4194304 8388608 4194304
Large pool 8388608 8388608 8388608 4194304
Buffer cache 25165824 25165824 25165824 4194304
SQL> ALTER SESTEM SET DB_CACHE_SIZE = 25M Scope = Memory;
The system has changed.
SQL> SELECT Component, Current_Size, Min_Size, Max_Size, Granule_Size
2 from V $ SGA_DYNAMIC_Components;
Component Current_size min_size max_size granule_size
-------------------------------------------------------------- - ----------------
Shared pool 4194304 4194304 8388608 4194304
Large pool 8388608 8388608 8388608 4194304
Buffer cache 29360128 25165824 29360128 4194304
SQL> ALTER system set large_pool_size = 4m scope = memory;
The system has changed.
SQL> Show parameter large_pool_size
Name Type Value
----------------------------------- --- ----------------------------
Large_pool_size big integer 4194304
SQL> SELECT Component, Current_Size, Min_Size, Max_Size, Granule_Size
2 from V $ sga_dynamic_components; Component Current_size min_size max_size granule_size
-------------------------------------------------------------- - ----------------
Shared pool 4194304 4194304 8388608 4194304
Large pool 4194304 4194304 8388608 4194304
Buffer cache 29360128 25165824 29360128 4194304
SQL>
In addition, in Oracle 9i, the parameters of the high-speed buffer pool have some changes:
· DB_CACHE_SIZE parameter replaces the DB_BLOCK_BUFFERS parameter of the previous version of the database, and the db_cache_size parameter is in bytes (DB_BLOCK_BUFFERS parameters are in units of blocks), which specifies the default pool in the high-speed buffer (Default). ;
· DB_KEEP_CACHE_SIZE parameter replaces the buffer_pool_keep parameters, you can specify frequently used small tables in the buffer;
· DB_RECYCLE_POOL_SIZE Parameters instead of the buffer_pool_recycle parameter, you can put the big meter that you want to quickly remove the memory in this area;
When the DB_KEEP_CACHE_SIZE parameter and the db_recycle_pool_size parameter are not set, the high-speed buffer pool will only have the default area, namely the area specified by the DB_CACHE_SIZE parameter:
SQL> SELECT NAME, Block_size from v $ buffer_pool_statistics
2 /
Name Block_size
------------------------------
DEFAULT 8192
SQL>
In Oracle 9i, PGA is logically divided into two parts:
· Adjustable area, namely SQL workspace, etc.
· Do not adjust the area, such as Sort Area, etc.
In order to adjust the PGA, we need to introduce two new parameters: workarea_size_policy and pga_aggregate_target.
Workarea_size_policy's value can be Auto or Manual, which means using the previous method to manage PGA's use (e.g., sort_area_size, have_area_size, etc.) or use new methods to manage PGA usage. If the parameter PGA_AGGREGATE_TARGET is not set, the default value of Workarea_Size_Policy is Manual.
The PGA_AGGREGATE_TARGET parameter means that the maximum number of memory can be divided into the SGA, which includes all Session adjustable and non-adjustable parts, this parameter does not have the default value, when we set Workarea_SIZE_POLICY, you must set the PGA_AGGREGATE_TARGET parameter .
We can view the use of PGA through the following manner:
SQL> SELECT SUM (PGA_USED_MEM), SUM (PGA_alloc_mem), SUM (PGA_MAX_MEM) 2 from V $ Process;
SUM (PGA_USED_MEM) SUM (PGA_ALLOC_MEM) SUM (PGA_MAX_MEM)
--------------------------------------------------- -
14569292 28619756 28619756
SQL>
among them:
• PGA_USED_MEM: The current PGA size currently used;
• PGA_alloc_mem: The total PGA size currently assigned to the process;
• PGA_MAX_MEM: The largest PGA size that has been assigned to this process
