2. 首页 > Oracle教程 > 正文

Oracle教程FG293-Oracle SGA调优实战

教程整理：风哥教程  |  更新时间：2026-01-10  |  教程分类：Oracle教程  |  文档学习：7

内容大纲

内容简介：本文主要介绍Oracle数据库的SGA（系统全局区）调优，包括SGA的组成、配置、监控和优化。风哥教程参考Oracle官方文档SGA调优相关内容，为生产环境提供完整的SGA调优解决方案。

Part01-基础概念与理论知识

1.1 SGA概念

Oracle SGA（系统全局区）是Oracle数据库实例的共享内存区域，用于存储数据库实例的共享数据和控制信息。SGA是Oracle数据库性能的关键组件，其大小和配置直接影响数据库的性能。SGA主要包括共享池、数据库缓冲区缓存、重做日志缓冲区等组件。

1.2 SGA组件

  • 共享池（Shared Pool）：存储SQL语句、PL/SQL代码、数据字典缓存等
  • 数据库缓冲区缓存（Database Buffer Cache）：存储数据块的缓存
  • 重做日志缓冲区（Redo Log Buffer）：存储重做日志条目
  • 大型池（Large Pool）：用于RMAN备份恢复、并行查询等
  • Java池（Java Pool）：用于Java存储过程和Java应用
  • 流池（Streams Pool）：用于Oracle Streams和GoldenGate

1.3 SGA调优方法

  • 调整SGA大小：根据服务器内存和数据库负载调整SGA大小
  • 调整SGA组件大小：根据数据库负载调整各个SGA组件的大小
  • 监控SGA使用情况：定期监控SGA的使用情况，及时发现问题
  • 分析SGA性能：分析SGA的性能指标，识别瓶颈
  • 优化SGA配置：根据分析结果优化SGA配置

Part02-生产环境规划与建议

2.1 SGA调优规划

制定合理的SGA调优规划：

  • 评估服务器内存大小
  • 分析数据库负载情况
  • 制定SGA大小和组件配置方案
  • 建立SGA调优的流程和规范
  • 定期执行SGA调优
  • 跟踪SGA调优的效果

2.2 SGA调优建议

SGA调优建议：

  • 根据服务器内存大小设置合理的SGA大小
  • 根据数据库负载情况调整SGA组件大小
  • 定期监控SGA使用情况，及时发现问题
  • 结合其他性能工具，全面分析SGA性能
  • 根据数据库类型和负载调整SGA配置

2.3 SGA调优结果管理

SGA调优结果管理建议：

  • 保存SGA调优的历史数据
  • 建立SGA调优的审核机制
  • 跟踪SGA使用的变化趋势
  • 分析SGA性能的瓶颈
  • 与开发团队分享SGA调优结果，提高应用程序性能

Part03-生产环境项目实施方案

3.1 SGA配置与管理

# 1. 查看当前SGA配置
SQL> SHOW PARAMETER sga_target;
SQL> SHOW PARAMETER sga_max_size;

# 2. 调整SGA大小
SQL> ALTER SYSTEM SET sga_max_size=12G SCOPE=spfile;
SQL> ALTER SYSTEM SET sga_target=12G SCOPE=spfile;

# 3. 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 4. 调整SGA组件大小
SQL> ALTER SYSTEM SET shared_pool_size=3G SCOPE=spfile;
SQL> ALTER SYSTEM SET db_cache_size=6G SCOPE=spfile;
SQL> ALTER SYSTEM SET log_buffer=16M SCOPE=spfile;
SQL> ALTER SYSTEM SET large_pool_size=512M SCOPE=spfile;
SQL> ALTER SYSTEM SET java_pool_size=256M SCOPE=spfile;
SQL> ALTER SYSTEM SET streams_pool_size=256M SCOPE=spfile;

# 5. 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

3.2 SGA监控

# 1. 查看SGA使用情况
SQL> SELECT * FROM v$sga;

# 2. 查看SGA组件使用情况
SQL> SELECT * FROM v$sga_dynamic_components;

# 3. 查看共享池使用情况
SQL> SELECT * FROM v$shared_pool_reserved;
SQL> SELECT * FROM v$librarycache;

# 4. 查看数据库缓冲区缓存使用情况
SQL> SELECT * FROM v$buffer_pool_statistics;
SQL> SELECT * FROM v$db_cache_advice;

# 5. 查看重做日志缓冲区使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%redo%’;

# 6. 查看大型池使用情况
SQL> SELECT * FROM v$large_pool_advice;

# 7. 查看Java池使用情况
SQL> SELECT * FROM v$java_pool_advice;

# 8. 查看流池使用情况
SQL> SELECT * FROM v$streams_pool_advice;

3.3 SGA调优

# 1. 分析SGA使用情况
# 查看SGA各组件的使用情况，识别瓶颈

# 2. 调整共享池大小
# 根据共享池使用情况调整大小
SQL> ALTER SYSTEM SET shared_pool_size=4G SCOPE=spfile;

# 3. 调整数据库缓冲区缓存大小
# 根据数据库缓冲区缓存使用情况调整大小
SQL> ALTER SYSTEM SET db_cache_size=8G SCOPE=spfile;

# 4. 调整重做日志缓冲区大小
# 根据重做日志缓冲区使用情况调整大小
SQL> ALTER SYSTEM SET log_buffer=32M SCOPE=spfile;

# 5. 调整大型池大小
# 根据大型池使用情况调整大小
SQL> ALTER SYSTEM SET large_pool_size=1G SCOPE=spfile;

# 6. 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 7. 验证调优效果
# 查看调优后的SGA使用情况

3.4 SGA调优结果管理

# 1. 保存SGA调优历史数据
# 将SGA使用情况保存到表中，用于后续分析

# 2. 建立SGA调优的审核机制
# 定期审核SGA使用情况，确保SGA资源的合理使用

# 3. 跟踪SGA使用的变化趋势
# 分析SGA使用的变化趋势，预测SGA需求

# 4. 分析SGA性能的瓶颈
# 识别SGA性能的瓶颈，采取相应的优化措施

# 5. 与开发团队分享SGA调优结果
# 提供SGA调优结果给开发团队，帮助优化应用程序

Part04-生产案例与实战讲解

4.1 SGA配置与管理实战

# 1. 连接数据库
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Apr 4 01:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

# 2. 查看当前SGA配置
SQL> SHOW PARAMETER sga_target;

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 8G

SQL> SHOW PARAMETER sga_max_size;

NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 8G

# 3. 调整SGA大小
SQL> ALTER SYSTEM SET sga_max_size=12G SCOPE=spfile;
SQL> ALTER SYSTEM SET sga_target=12G SCOPE=spfile;

# 4. 调整SGA组件大小
SQL> ALTER SYSTEM SET shared_pool_size=3G SCOPE=spfile;
SQL> ALTER SYSTEM SET db_cache_size=6G SCOPE=spfile;
SQL> ALTER SYSTEM SET log_buffer=16M SCOPE=spfile;
SQL> ALTER SYSTEM SET large_pool_size=512M SCOPE=spfile;
SQL> ALTER SYSTEM SET java_pool_size=256M SCOPE=spfile;
SQL> ALTER SYSTEM SET streams_pool_size=256M SCOPE=spfile;

# 5. 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 6. 查看调整后的SGA配置
SQL> SHOW PARAMETER sga_target;

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 12G

SQL> SHOW PARAMETER sga_max_size;

NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 12G

4.2 SGA监控与分析实战

# 1. 查看SGA使用情况
SQL> SELECT * FROM v$sga;

NAME VALUE
—————————— ———-
Fixed Size 9137840
Variable Size 8589934592
Database Buffers 6442450944
Redo Buffers 251658240

# 2. 查看SGA组件使用情况
SQL> SELECT * FROM v$sga_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_TIME GRANULE_SIZE
————————— ———— ———- ———- ——————– ———- ——————– ——————– ————
shared pool 3221225472 16777216 3221225472 0 1 GROW 2026-04-04 01:10:00 16777216
large pool 536870912 16777216 536870912 0 1 GROW 2026-04-04 01:10:00 16777216
java pool 268435456 16777216 268435456 0 1 GROW 2026-04-04 01:10:00 16777216
streams pool 268435456 16777216 268435456 0 1 GROW 2026-04-04 01:10:00 16777216
SGA Target 12884901888 16777216 12884901888 0 1 GROW 2026-04-04 01:10:00 16777216
Default buffer cache 68719476736 16777216 68719476736 0 1 GROW 2026-04-04 01:10:00 16777216
KEEP buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
RECYCLE buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
DEFAULT 2K buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
DEFAULT 4K buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
DEFAULT 8K buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
DEFAULT 16K buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
DEFAULT 32K buffer cache 0 0 0 0 0 0000-00-00 00:00:00 16777216
Shared IO Pool 0 0 0 0 0 0000-00-00 00:00:00 16777216
ASM Buffer Cache 0 0 0 0 0 0000-00-00 00:00:00 16777216

# 3. 查看共享池使用情况
SQL> SELECT * FROM v$librarycache;

NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO RELOADS INVALIDATIONS
————————- ———- ———- ———– ———- ———- ———– ———- ————–
SQL AREA 123456 112345 0.91 234567 212345 0.90 123 45
TABLE/PROCEDURE 56789 52345 0.92 89012 82345 0.92 67 23
BODY 12345 11234 0.91 23456 21234 0.90 23 12
TRIGGER 8901 8234 0.92 12345 11234 0.91 12 5

# 4. 查看数据库缓冲区缓存使用情况
SQL> SELECT * FROM v$buffer_pool_statistics;

ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS FREE_BUFFER_OPTIONS FLUSH_LIST_LEN DIRTY_BUFFERS PINNED_BUFFERS STALE_BUFFERS FREE_BUFFERS DB_BLOCK_CHANGES CONSISTENT_GETS PHYSICAL_READS PHYSICAL_WRITES
— ——————– ———- ———— ———- ——————– —————– ————- ————– ————– ———— ————— ————— ————— —————
0 DEFAULT 8192 8388608 8388608 32768 0 123456 987654 12345 890123 12345678 98765432 1234567 890123

# 5. 查看重做日志缓冲区使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%redo%’;

STATISTIC# NAME CLASS VALUE
———- —————————————- ———- ———-
123 redo entries 1 1234567
124 redo size 1 1234567890
125 redo wastage 1 123456
126 redo buffer allocation retries 1 123

4.3 SGA调优实战

# 1. 分析SGA使用情况
# 查看SGA各组件的使用情况，识别瓶颈

# 2. 调整共享池大小
# 根据共享池使用情况调整大小
SQL> ALTER SYSTEM SET shared_pool_size=4G SCOPE=spfile;

System altered.

# 3. 调整数据库缓冲区缓存大小
# 根据数据库缓冲区缓存使用情况调整大小
SQL> ALTER SYSTEM SET db_cache_size=8G SCOPE=spfile;

System altered.

# 4. 调整重做日志缓冲区大小
# 根据重做日志缓冲区使用情况调整大小
SQL> ALTER SYSTEM SET log_buffer=32M SCOPE=spfile;

System altered.

# 5. 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 6. 验证调优效果
# 查看调优后的SGA使用情况
SQL> SELECT * FROM v$sga;

NAME VALUE
—————————— ———-
Fixed Size 9137840
Variable Size 10737418240
Database Buffers 8589934592
Redo Buffers 335544320

# 7. 验证数据库性能
# 执行SQL语句并记录执行时间

优化前执行时间：
SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;
10000 rows selected.
Elapsed: 00:00:01.23

优化后执行时间：
SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;
10000 rows selected.
Elapsed: 00:00:00.45

# 执行时间减少：63%

Part05-风哥经验总结与分享

5.1 SGA调优最佳实践

  • 合理设置SGA大小：根据服务器内存大小设置合理的SGA大小
  • 调整SGA组件：根据数据库负载情况调整SGA组件大小
  • 定期监控：定期监控SGA使用情况，及时发现问题
  • 分析瓶颈：分析SGA性能的瓶颈，采取相应的优化措施
  • 持续优化：根据数据库负载情况持续优化SGA配置

5.2 SGA调优注意事项

  • 确保服务器有足够的物理内存
  • 根据数据库负载情况调整SGA大小
  • 定期监控SGA使用情况，及时发现问题
  • 结合其他性能工具，全面分析SGA性能
  • 与开发团队分享SGA调优结果，提高应用程序性能

5.3 SGA调优建议

  • 建立SGA调优流程，定期执行SGA配置和分析
  • 培训DBA，提高SGA调优能力
  • 建立SGA调优结果的审核机制
  • 跟踪SGA使用的变化趋势
  • 与Oracle支持团队保持沟通，获取SGA调优的最佳实践

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息