— 输出结果
NAME TYPE VALUE
———————————— ———– ——————————
memory_max_target big integer 0
memory_target big integer 0

— 步骤2：启用自动内存管理
ALTER SYSTEM SET memory_max_target = 32G SCOPE=SPFILE;
ALTER SYSTEM SET memory_target = 32G SCOPE=SPFILE;

— 步骤3：重启数据库使配置生效
SHUTDOWN IMMEDIATE;
STARTUP;

— 步骤4：验证内存配置
SHOW PARAMETER memory;

— 输出结果
NAME TYPE VALUE
———————————— ———– ——————————
memory_max_target big integer 32G
memory_target big integer 32G

— 步骤5：检查SGA和PGA的自动分配情况
SELECT name, value FROM v$parameter WHERE name IN (‘sga_target’, ‘pga_aggregate_target’);

— 输出结果
NAME VALUE
———————————— ——————————
sga_target 0
pga_aggregate_target 0

— 步骤2：配置SGA参数
ALTER SYSTEM SET sga_max_size = 24G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 24G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size = 4G SCOPE=SPFILE;
ALTER SYSTEM SET db_cache_size = 16G SCOPE=SPFILE;
ALTER SYSTEM SET log_buffer = 16M SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size = 1G SCOPE=SPFILE;
ALTER SYSTEM SET java_pool_size = 512M SCOPE=SPFILE;

— 步骤3：配置PGA参数
ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=SPFILE;

— 步骤4：重启数据库使配置生效
SHUTDOWN IMMEDIATE;
STARTUP;

— 步骤5：验证内存配置
SHOW PARAMETER sga;
SHOW PARAMETER pga;

— 输出结果
NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 24G
sga_target big integer 24G
shared_pool_size big integer 4G
db_cache_size big integer 16G
log_buffer big integer 16777216
large_pool_size big integer 1G
java_pool_size big integer 512M
pga_aggregate_target big integer 8G

— 输出结果
COMPONENT Size (GB)
———————————— ———-
shared pool 4.00000000
buffer cache 16.00000000
large pool 1.00000000
java pool 0.50000000
streams pool 0.00000000

— 步骤2：查看PGA内存使用情况
SELECT name, value/1024/1024/1024 “Value (GB)” FROM v$pgastat;

— 输出结果
NAME Value (GB)
———————————— ———-
aggregate PGA target parameter 8.00000000
aggregate PGA auto target 6.40000000
global memory bound 0.80000000
total PGA allocated 1.20000000
total PGA used for auto workareas 0.10000000
total PGA used for manual workareas 0.00000000
over allocation count 0
bytes processed 10.50000000
total extra bytes read/written 0.00000000
cache hit percentage 100.00000000

— 步骤3：查看内存命中率
SELECT name, 1 – (physical_reads / (db_block_gets + consistent_gets)) “Hit Ratio”
FROM v$buffer_pool_statistics;

— 输出结果
NAME Hit Ratio
———————————— ———-
DEFAULT 0.99876543

— 步骤4：查看共享池命中率
SELECT namespace, pins, reloads, (1 – (reloads / pins)) “Hit Ratio”
FROM v$librarycache;

— 输出结果
NAMESPACE PINS RELOADS Hit Ratio
———————————— ——— ——— ———-
SQL AREA 100000 100 0.99900000
TABLE/PROCEDURE 50000 50 0.99900000
BODY 10000 10 0.99900000
TRIGGER 5000 5 0.99900000

— 步骤5：创建内存监控视图
CREATE OR REPLACE VIEW fgedu.v_memory_monitor AS
SELECT
SYSTIMESTAMP AS check_time,
(SELECT value/1024/1024/1024 FROM v$pgastat WHERE name = ‘total PGA allocated’) AS pga_used_gb,
(SELECT value/1024/1024/1024 FROM v$pgastat WHERE name = ‘aggregate PGA target parameter’) AS pga_target_gb,
(SELECT SUM(current_size)/1024/1024/1024 FROM v$sga_dynamic_components) AS sga_used_gb,
(SELECT value/1024/1024/1024 FROM v$parameter WHERE name = ‘sga_target’) AS sga_target_gb,
(SELECT 1 – (physical_reads / (db_block_gets + consistent_gets)) FROM v$buffer_pool_statistics WHERE name = ‘DEFAULT’) AS buffer_cache_hit_ratio,
(SELECT (1 – (reloads / pins)) FROM v$librarycache WHERE namespace = ‘SQL AREA’) AS shared_pool_hit_ratio
FROM dual;

— 步骤6：查看内存监控结果
SELECT * FROM fgedu.v_memory_monitor;

— 输出结果
CHECK_TIME PGA_USED_GB PGA_TARGET_GB SGA_USED_GB SGA_TARGET_GB BUFFER_CACHE_HIT_RATIO SHARED_POOL_HIT_RATIO
———————————– ———– ————- ———– ————- ———————– ——————–
2025-01-21 10:00:00.000000000 +08:00 1.2 8.0 21.5 24.0 0.99876543 0.99900000

#!/bin/bash
# memory_tuning.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 配置信息
USERNAME=”sys”
PASSWORD=”sys123″
SID=”fgedudb”

# 收集内存使用情况
sqlplus -S ${USERNAME}/${PASSWORD}@${SID} as sysdba << EOF SPOOL /yashandb/app/scripts/memory_report.txt SET LINESIZE 150 SET PAGESIZE 100 -- 内存参数 SELECT name, value FROM vparameter WHERE name LIKE '%memory%' OR name LIKE '%sga%' OR name LIKE '%pga%'; -- SGA使用情况 SELECT component, current_size/1024/1024/1024 "Size (GB)" FROM vsga_dynamic_components; -- PGA使用情况 SELECT name, value/1024/1024/1024 "Value (GB)" FROM vpgastat; -- 内存命中率 SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio" FROM vbuffer_pool_statistics; SELECT namespace, pins, reloads, (1 - (reloads / pins)) "Hit Ratio" FROM vlibrarycache; -- 内存相关等待事件 SELECT event, total_waits, time_waited FROM vsystem_event WHERE event LIKE '%memory%'; SPOOL OFF EXIT; EOF # 分析内存报告 echo "Memory tuning report generated at /yashandb/app/scripts/memory_report.txt" -- 步骤2：设置脚本执行权限 chmod +x /yashandb/app/scripts/memory_tuning.sh -- 步骤3：执行内存调优脚本 /yashandb/app/scripts/memory_tuning.sh -- 输出结果 Memory tuning report generated at /yashandb/app/scripts/memory_report.txt -- 步骤4：分析内存报告 cat /yashandb/app/scripts/memory_report.txt -- 输出结果（示例） NAME VALUE ------------------------------------ ------------------------------ memory_max_target 0 memory_target 0 sga_max_size 24G sga_target 24G shared_pool_size 4G db_cache_size 16G log_buffer 16777216 large_pool_size 1G java_pool_size 512M pga_aggregate_target 8G COMPONENT Size (GB) ------------------------------------ ---------- shared pool 4.00000000 buffer cache 16.00000000 large pool 1.00000000 java pool 0.50000000 streams pool 0.00000000 NAME Value (GB) ------------------------------------ ---------- aggregate PGA target parameter 8.00000000 aggregate PGA auto target 6.40000000 global memory bound 0.80000000 total PGA allocated 1.20000000 total PGA used for auto workareas 0.10000000 total PGA used for manual workareas 0.00000000 over allocation count 0 bytes processed 10.50000000 total extra bytes read/written 0.00000000 cache hit percentage 100.00000000 NAME Hit Ratio ------------------------------------ ---------- DEFAULT 0.99876543 NAMESPACE PINS RELOADS Hit Ratio ------------------------------------ --------- --------- ---------- SQL AREA 100000 100 0.99900000 TABLE/PROCEDURE 50000 50 0.99900000 BODY 10000 10 0.99900000 TRIGGER 5000 5 0.99900000 EVENT TOTAL_WAITS TIME_WAITED ------------------------------------ ----------- ------------ PGA memory operation 100 10