Modification of Oracle parameter files and modification of SGA PGA parameters

1. Settings for parameter files

(1) View the dynamic parameter file

SELECT * from sys.props$ wherename= ' Nls_characterset ';

Dynamic parameter file: SPFile: Dynamic parameter modification in which can take effect immediately

(2) Modify dynamic parameters

Alter system SET parameter = value scope=<memory><spfile><both>

#memory only currently in effect; (applicable to dynamic parameters)

#spfile: Not currently in effect, after reboot; (applicable to dynamic, static parameters)

#both: Currently in effect, restart successor is effective; (for dynamic parameters) default value

#还原修改动态参数方法

Alter system Reset Undo_suppress_errorsscope=both;

(3) Static parameters

#任何参数修改必须重启数据库才能生效

#根据动态参数文件创建静态参数文件:

Create Pfile from SPFile;

System Check order at startup: Spfile-pfile-init.ora

(4) commonly used parameters

Show Parameter Db_cache_size

#标准块缓冲区高速缓存的大小

Show parameter db_files;

# Maximum number of database files opened by database 5

Maximum number of OS user processes #可同时连接到 Oracle servers

Show parameter processes

Show Parameter SGA

5. SGA parameter setting

(1) Query

#了分配到与实例挂接的所有服务器进程的程序全局区 (PGA) memory

Show Parameter Pga_aggregate_target

Show parameter SGA;

#除了SGA_MAX_SIZE, all others are dynamic parameters, that is, parameters that can be modified dynamically during database operation using the ALTER SYSTEM statement

Show Parameter Sga_max_size

#sga颗粒查询

#所有的SAG (each pool) with a minimum of memory unit particles (granule, also called the District Group)

sql> Select Component,granule_size fromv$sga_dynamic_components;

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

COMPONENT granule_size

----------------------------------------------------------------------------

Shared Pool 4194304

Large Pool 4194304

Java Pool 4194304

Streams Pool 4194304

DEFAULT Buffer Cache 4194304

KEEP Buffer Cache 4194304

Recycle buffer Cache 4194304

DEFAULT 2K Buffer Cache 4194304

DEFAULT 4K Buffer Cache 4194304

DEFAULT 8K Buffer Cache 4194304

DEFAULT 16K Buffer Cache 4194304

Note: When the size is modified, it must be a multiple of the CRANULE_SIZE=4MB

#show parameter Sga_info information more

#select * from V$sgastat view each process in each small pool

#查看剩余情况

Show Sga_dynamic_free_memory