Oracle Automatic Memory Sharing Management test. Modify Oracle 11g sga_max_size.

Objective: using Oracle Automatic Memory Sharing Management is basically a modification process. Leave a message if you want to sort it out.

 

SQL> show parameters target;

Name type value
------------------------------------------------------
Archive_lag_target integer 0
Db_flashback_retention_target integer 1440
Fast_start_io_target integer 0
Fast_start_mttr_target integer 0
Memory_max_target big integer 0
Memory_target big integer 0
Parallel_servers_target integer 64
Pga_aggregate_target big integer 4612 m
Sga_target big integer 1536 m

SQL> show parameter statistics_level;

Name type value
------------------------------------------------------
Statistics_level string typical (all)

 

SQL> show parameter sga_max_size;

Name type value
-----------------------------------------------------
Sga_max_size big integer 1536 m

SQL> show SGA;

Total system global area 1603411968 bytes
Fixed size 2176168 bytes
Variable Size 520096600 bytes
Database buffers 1073741824 bytes
Redo buffers 7397376 bytes

 

 

Step 1: Find the pfile File

Modify the desired size. Note that the sga_max_size in Win2000 cannot exceed 1.7 GB .. On server2008, I changed it to 31 GB.

Otherwise, refer to: http://blog.csdn.net/xiaojian/archive/2008/07/29/2732149.aspx after an error

 

######################################## ###
# Pools
######################################## ###
S ga_max_size = 31000000000

 

SQL> startup pfile = 'd:/APP/Administrator/admin/XXX/pfile/init. ora.20171201018371 ';
The Oracle routine has been started.

Total system global area 3.1868e + 10 bytes
Fixed size 2191456 bytes
Variable Size 3.0870e + 10 bytes
Database buffers 939524096 bytes
Redo buffers 56213504 bytes

 

SQL> show SGA;

Total system global area 3.1868e + 10 bytes
Fixed size 2191456 bytes
Variable Size 3.0870e + 10 bytes
Database buffers 939524096 bytes
Redo buffers 56213504 bytes

SQL & gt; select sum (value)/1024/1024 from V $ SGA;

Sum (value)/1024/1024.
--------------------
30391.7031

 

SQL> show parameters target;

Name type value
------------------------------------------------------------
Archive_lag_target integer 0
Db_flashback_retention_target integer 1440
Fast_start_io_target integer 0
Fast_start_mttr_target integer 0
Memory_max_target big integer 0
Memory_target big integer 0
Parallel_servers_target integer 64
Pga_aggregate_target big integer 4612 m
Sga_target big integer 1536 m

SQL> show parameter sga_max_size;

Name type value
-------------------------------------------------------
Sga_max_size big integer30528 m

SQL> alter system set sga_target =30000 m;

The system has been changed.

 

SQL> show parameters target;

Name type value
-------------------------------------------------------
Archive_lag_target integer 0
Db_flashback_retention_target integer 1440
Fast_start_io_target integer 0
Fast_start_mttr_target integer 0
Memory_max_target big integer 0
Memory_target big integer 0
Parallel_servers_target integer 64
Pga_aggregate_target big integer 4612 m
Sga_target big integer 30016 m

Http://blog.csdn.net/johnstrive/article/details/6271103