IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
MEMORY_TARGET is a database initialization parameter that can be used for automatic PGA and SGA memory sizing.
MEMORY_TARGET provides the following:
A single parameter for total SGA and PGA sizes
Automatically sizes SGA components and PGA
Memory is transferred to where most needed
Uses workload information
Uses internal advisory predictions
Can be enable by DBCA at the time of Database creation.
By using one parameter we don’t need to use all other SGA and PGA parameters like.
DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
PGA_AGGREGATE_TARGET
10:37:48 SQL> SHOW PARAMETER TARGET NAME |TYPE |VALUE ------------------------------------|-----------|------------------------------ archive_lag_target |integer |0 db_big_table_cache_percent_target |string |0 db_flashback_retention_target |integer |1440 fast_start_io_target |integer |0 fast_start_mttr_target |integer |0 memory_max_target |big integer|50G memory_target |big integer|50G parallel_servers_target |integer |256 pga_aggregate_target |big integer|0 sga_target |big integer|0
Now we can change the value of parameter memory_target and memory_max_target
10:37:41 SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET = 80G SCOPE = SPFILE;
10:39:57 SQL> alter system set memory_target=80G SCOPE = SPFILE;
and we close the database$ sqlplus / as sysdba SQL> shutdown immediate
and we open the database
10:41:03 SQL> startup; ORACLE instance started. Total System Global Area|5.1540E+10|bytes Fixed Size | 5870816|bytes Variable Size |5909777184|bytes Database Buffers |4.5617E+10|bytes Redo Buffers | 6709248|bytes Database mounted. Database opened.
10:41:49 SQL> SHOW PARAMETER TARGET NAME |TYPE |VALUE ------------------------------------|-----------|------------------------------ archive_lag_target |integer |0 db_big_table_cache_percent_target |string |0 db_flashback_retention_target |integer |1440 fast_start_io_target |integer |0 fast_start_mttr_target |integer |0 memory_max_target |big integer|80G memory_target |big integer|80G parallel_servers_target |integer |256 pga_aggregate_target |big integer|0 sga_target |big integer|0 10:41:57 SQL>
