Oracle的实例占用内存调整

时间：2023-01-31 20:10:21
       1、操作 （oracle使用内存约等于 SGA+PGA，所以可以减少SGA与PGA解决你的问题，生产库慎用）
alter system set sga_max_size=100m scope=spfile; --减少SGA大小，静态参数，重启后生效
alter system set sga_target=80m scope=both; --动态参数；oracle推荐：启动时修改此参数，不要动态设置此参数
alter system set pga_aggregate_target=40m sope=both; ---减少pga大小
2、拓展
SGA_MAX_SIZE的大小不是随意指定的，必须满足一定条件的。
sga_max_size=100M,必须满足SGA所有组件的最小和；至少满足db_cache_size,log_buffer,shared_pool_size,large_pool_size,java_pool_size总和的大小)
oracle内存结构介绍：
Introduction to Oracle Memory Structures
Oracle uses memory to store information such as the following:

Program code
Information about a connected session, even if it is not currently active
Information needed during program execution (for example, the current state of a query from which rows are being fetched)
Information that is shared and communicated among Oracle processes (for example, locking information)
Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries)
The basic memory structures associated with Oracle include:
System Global Area (SGA), which is shared by all server and background processes.
Program Global Areas (PGA), which is private to each server and background process; there is one PGA for each process.

1.sqlplus “/ as sysdba"  2. 3.create pfile='d:/cj.ora' from spfile;  然后去你的oracle安装目录

　　找一个init数据库名。ora这样一个文件

　　把几个oracle内存参数改小点

　　一个2G的内存是如下配置的：

　　*.sga_max_size=712M

　　*.large_pool_size=8M

　　*.shared_pool_size=250M

　　*.sort_area_size=0.5M

　　*.db_cache_size=350M

　　然后shutdown数据库

　　1.shutdown immediate;  2. 3.create spfile from pfile='d:/cj2.ora';  4. 5.startup;  然后启动，就可以了。

　　以上就是调整oracle内存参数，减少数据库内存占用的方法介绍。

相关文章