# 查看当前内存配置
SQL> show parameter memory;NAME TYPE VALUE
———————————— ———– ——————————
memory_max_target big integer 0
memory_target big integer 0

# 配置MEMORY_TARGET和MEMORY_MAX_TARGET
SQL> alter system set memory_max_target = 12G scope=spfile;System altered.

SQL> alter system set memory_target = 10G scope=spfile;System altered.

# 重启数据库使配置生效
SQL> shutdown immediate;Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;ORACLE instance started.

Total System Global Area 12884901888 bytes
Fixed Size 8797296 bytes
Variable Size 3758096144 bytes
Database Buffers 9059696640 bytes
Redo Buffers 58341376 bytes
Database mounted.
Database opened.

# 验证AMM配置
SQL> show parameter memory;NAME TYPE VALUE
———————————— ———– ——————————
memory_max_target big integer 12G
memory_target big integer 10G

# 查看内存组件分配
SQL> select component, current_size/1024/1024 as size_mb
from v$memory_dynamic_components
order by component;COMPONENT SIZE_MB
—————————————- ———-
ASM Buffer Cache 0
buffer pool 8192.000
fixed sga 8.3916626
java pool 64.000000
large pool 64.000000
processes 3.3251953
redolog buffers 55.656250
shared pool 1664.0000
streams pool 16.000000

# 禁用AMM
SQL> alter system set memory_target = 0 scope=spfile;System altered.

SQL> alter system set memory_max_target = 0 scope=spfile;System altered.

# 配置SGA_TARGET和SGA_MAX_SIZE
SQL> alter system set sga_max_size = 8G scope=spfile;System altered.

SQL> alter system set sga_target = 6G scope=spfile;System altered.

# 配置PGA_AGGREGATE_TARGET
SQL> alter system set pga_aggregate_target = 4G scope=both;System altered.

# 重启数据库
SQL> shutdown immediate;SQL> startup;# 验证ASMM配置
SQL> show parameter sga;NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 8G
sga_target big integer 6G

SQL> show parameter pga;NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_target big integer 4G

# 查看SGA组件分配
SQL> select component, current_size/1024/1024 as size_mb
from v$sga_dynamic_components
order by component;COMPONENT SIZE_MB
—————————————- ———-
ASM Buffer Cache 0
buffer pool 5120.000
fixed sga 8.3916626
java pool 32.000000
large pool 32.000000
redolog buffers 55.656250
shared pool 768.00000
streams pool 16.000000

# 查看共享池信息
SQL> select pool, name, bytes/1024/1024 as mb
from v$sgastat
where pool = ‘shared pool’
order by bytes desc
fetch first 20 rows only;POOL NAME MB
———— —————————— ———-
shared pool free memory 200.56
shared pool sql area 300.78
shared pool library cache 150.34
shared pool row cache 80.12

# 查看库缓存命中率
SQL> select sum(pins) as pins,
sum(reloads) as reloads,
sum(reloads)/sum(pins)*100 as reload_pct
from v$librarycache;PINS RELOADS RELOAD_PCT
———- ———- ———-
12345678 123 .01

# 查看数据字典缓存命中率
SQL> select sum(gets) as gets,
sum(getmisses) as getmisses,
(1 – sum(getmisses)/sum(gets))*100 as hit_pct
from v$rowcache;GETS GETMISSES HIT_PCT
———- ———- ———-
23456789 2345 99.99

# 刷新共享池（谨慎使用）
SQL> alter system flush shared_pool;System altered.

# 调整共享池大小（使用ASMM时自动调整）
SQL> alter system set shared_pool_size = 1G scope=both;System altered.

# 查看缓冲区缓存信息
SQL> select name, block_size, size_for_estimate,
buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
from v$db_cache_advice
order by size_for_estimate;NAME BLOCK_SIZE SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
——————– ———- —————– ——————– ————————- ——————-
DEFAULT 8192 64 8192 12.34 12345678
DEFAULT 8192 128 16384 8.76 8765432
DEFAULT 8192 256 32768 5.43 5432109

# 查看缓冲区缓存命中率
SQL> select
(1 – (physical_reads / (db_block_gets + consistent_gets))) * 100 as hit_ratio
from v$buffer_pool;HIT_RATIO
———-
98.76

# 查看等待事件
SQL> select event, total_waits, time_waited
from v$system_event
where event like ‘db file%’
order by time_waited desc;EVENT TOTAL_WAITS TIME_WAITED
—————————— ———– ———–
db file sequential read 12345 654321
db file scattered read 6789 234567
db file parallel write 3456 123456

# 调整缓冲区缓存大小（使用ASMM时自动调整）
SQL> alter system set db_cache_size = 4G scope=both;System altered.

# 查看PGA信息
SQL> select name, value/1024/1024 as mb
from v$pgastat
order by name;NAME MB
—————————————————————- ———-
aggregate PGA auto target 2048.00000
aggregate PGA target parameter 4096.00000
bytes processed 1234567.8906
cache hit percentage 99.50
extra bytes read/written 0
global memory bound 102.400000
maximum PGA allocated 2560.00000
maximum PGA used for auto workareas 512.000000
recompute count (total) 123
total PGA allocated 2048.00000
total PGA inuse 1536.00000
total PGA used for auto workareas 256.000000

# 查看PGA工作区使用情况
SQL> select low_optimal_size/1024 as low_kb,
high_optimal_size/1024 as high_kb,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram
order by low_optimal_size;LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
———- ———- —————— —————— ——————–
0 256 123456 0 0
256 1024 23456 5 0
1024 4096 3456 12 0
4096 16384 456 23 2

# 查看PGA内存 advisor
SQL> select pga_target_for_estimate/1024/1024 as target_mb,
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count
from v$pga_target_advice
order by pga_target_for_estimate;TARGET_MB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
———- —————– —————————— ——————–
1024 .25 95.67 12
2048 .5 98.34 3
3072 .75 99.21 1
4096 1 99.50 0
5120 1.25 99.67 0
6144 1.5 99.78 0

# 调整PGA_AGGREGATE_TARGET
SQL> alter system set pga_aggregate_target = 6G scope=both;System altered.

# 查看SGA当前大小
SQL> select sum(value)/1024/1024 as sga_total_mb
from v$sga;SGA_TOTAL_MB
————-
8192

# 查看PGA当前大小
SQL> select sum(value)/1024/1024 as pga_total_mb
from v$pgastat
where name = ‘total PGA allocated’;PGA_TOTAL_MB
————-
2048

# 查看系统内存使用情况
$ free -h
total used free shared buff/cache available
Mem: 62G 12G 45G 8.5M 5.0G 47G
Swap: 32G 0B 32G

# 查看SGA各组件使用情况
SQL> select component,
current_size/1024/1024 as current_mb,
min_size/1024/1024 as min_mb,
max_size/1024/1024 as max_mb
from v$sga_dynamic_components
where component in (‘buffer pool’, ‘shared pool’, ‘large pool’, ‘java pool’)
order by component;COMPONENT CURRENT_MB MIN_MB MAX_MB
—————————— ———— ———- ———-
buffer pool 5120 1024 7168
java pool 32 16 128
large pool 32 16 256
shared pool 768 256 1536

# 查看PGA使用top会话
SQL> select s.sid,
s.serial#,
s.username,
s.program,
p.pga_used_mem/1024/1024 as pga_used_mb,
p.pga_alloc_mem/1024/1024 as pga_alloc_mb
from v$session s, v$process p
where s.paddr = p.addr
order by p.pga_alloc_mem desc
fetch first 20 rows only;SID SERIAL# USERNAME PROGRAM PGA_USED_MB PGA_ALLOC_MB
———- ———- ———- —————– ———– ————
123 45678 FGAPP_USER app.exe 256 512
124 45679 FGAPP_USER app.exe 128 256
125 45680 SYS sqlplus.exe 64 128

# 场景：共享池碎片化，出现ORA-04031错误

# 1. 检查共享池空闲内存
SQL> select name, bytes/1024/1024 as mb
from v$sgastat
where pool = ‘shared pool’
and name = ‘free memory’;NAME MB
—————————— ———-
free memory 15.67

# 2. 检查共享池碎片化情况
SQL> select ksmchcls,
count(*) as chunks,
sum(ksmchsiz)/1024/1024 as total_mb
from x$ksmsp
group by ksmchcls;KSMCHCLS CHUNKS TOTAL_MB
——– ———- ———-
free 1234 15.67
recr 567 8.90
perm 234 12.45

# 3. 查看库缓存重装载率
SQL> select namespace,
pins,
reloads,
invalidations,
reloads/pins*100 as reload_pct
from v$librarycache
where pins > 0
order by reload_pct desc;NAMESPACE PINS RELOADS INVALIDATIONS RELOAD_PCT
————— ———- ———- ————- ———-
SQL AREA 12345678 1234 56 .01
TABLE/PROCEDURE 2345678 89 23 .00

# 4. 如果重装载率高，考虑增加共享池
SQL> alter system set shared_pool_size = 2G scope=both;System altered.

# 5. 如果碎片化严重，可以刷新共享池（谨慎使用）
SQL> alter system flush shared_pool;System altered.

# 6. 配置CURSOR_SHARING减少硬解析
SQL> alter system set cursor_sharing = FORCE scope=both;System altered.

# 7. 配置SESSION_CACHED_CURSORS缓存游标
SQL> alter system set session_cached_cursors = 200 scope=both;System altered.

# 场景：缓冲区缓存命中率低，I/O等待高

# 1. 检查缓冲区缓存命中率
SQL> select
(1 – (physical_reads / (db_block_gets + consistent_gets))) * 100 as hit_ratio
from v$buffer_pool;HIT_RATIO
———-
85.23

# 2. 查看I/O等待事件
SQL> select event, total_waits, time_waited, average_wait
from v$system_event
where event in (‘db file sequential read’, ‘db file scattered read’)
order by time_waited desc;EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ———– ———– ————
db file sequential read 12345 654321 53
db file scattered read 6789 234567 34

# 3. 查看缓冲区缓存advisor
SQL> select size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
from v$db_cache_advice
order by size_for_estimate;SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
—————– ——————– ————————- ——————-
512 65536 2.34 23456789
1024 131072 1.56 15678901
2048 262144 1.23 12345678
4096 524288 1.00 10000000
6144 786432 .87 8765432
8192 1048576 .76 7654321

# 4. 根据advisor建议增加缓冲区缓存
SQL> alter system set db_cache_size = 8G scope=both;System altered.

# 5. 验证命中率改善
SQL> select
(1 – (physical_reads / (db_block_gets + consistent_gets))) * 100 as hit_ratio
from v$buffer_pool;HIT_RATIO
———-
96.78

# 场景：出现ORA-04031错误

# 1. 查看错误信息
ORA-04031: unable to allocate 4096 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”modification “)

# 2. 检查SGA_TARGET设置
SQL> show parameter sga;NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 4G
sga_target big integer 4G

# 3. 检查共享池使用情况
SQL> select pool, name, bytes/1024/1024 as mb
from v$sgastat
where pool = ‘shared pool’
order by bytes desc
fetch first 10 rows only;POOL NAME MB
———— —————————— ———-
shared pool sql area 300.78
shared pool library cache 150.34
shared pool free memory 5.67

# 4. 检查是否有大池设置
SQL> show parameter large_pool;NAME TYPE VALUE
———————————— ———– ——————————
large_pool_size big integer 0

# 5. 解决方案1：刷新共享池（临时解决）
SQL> alter system flush shared_pool;System altered.

# 6. 解决方案2：增加共享池大小
SQL> alter system set shared_pool_size = 1G scope=both;System altered.

# 7. 解决方案3：增加SGA_TARGET（如果使用ASMM）
SQL> alter system set sga_target = 6G scope=both;System altered.

# 8. 解决方案4：配置大池（如果需要）
SQL> alter system set large_pool_size = 256M scope=both;System altered.

# 9. 解决方案5：增加SGA_MAX_SIZE并重启（如果需要）
SQL> alter system set sga_max_size = 8G scope=spfile;System altered.

# 重启数据库
SQL> shutdown immediate;SQL> startup;

# Oracle自带工具
1. V$视图
– 功能：查看内存使用情况
– 优点：实时、详细
– 适用：日常监控

2. Memory Advisor
– 功能：提供内存调整建议
– 优点：Oracle官方建议
– 适用：内存调优

3. AWR报告
– 功能：生成内存使用历史报告
– 优点：全面、历史数据
– 适用：性能分析

4. ADDM
– 功能：自动内存诊断
– 优点：自动分析、提供建议
– 适用：问题诊断

# OS工具
1. free/top
– 功能：查看系统内存使用
– 优点：简单、实时
– 适用：系统监控

2. vmstat
– 功能：虚拟内存统计
– 优点：详细的内存信息
– 适用：性能分析

3. sar
– 功能：系统活动报告
– 优点：历史数据
– 适用：趋势分析

# 第三方工具
1. Enterprise Manager
– 功能：图形化内存管理
– 优点：界面友好、功能强大
– 适用：生产环境管理

2. TOAD
– 功能：内存监控和分析
– 优点：功能丰富
– 适用：开发和DBA使用

3. SolarWinds/OEM
– 功能：全面的内存监控
– 优点：企业级功能
– 适用：大型环境