每日一题（易错）丨Oracle SQL优化与调优技术

墨墨导读：本文出自墨天轮“每日一练”专栏，此专栏已连更91天，欢迎关注https://www.modb.pro/topic/26446(复制到浏览器中打开或者点击“阅读原文”直达)，本文主要描述实例优化中内存ASMM管理方式。

1 ASMM:Automatic Shared Memory Management

如果不能使用AMM，因为需要一个固定的PGA，请考虑使用自动共享内存管理(ASMM)，它可以简化SGA内存管理。通过使用SGA_TARGET初始化参数，可以指定一个实例可用的SGA内存总量，然后Oracle数据库服务器将这些内存自动分配到各个SGA组件中，以确保最有效地利用内存。

例如,在系统运行的大型联机事务处理(OLTP)工作在白天运行，需要较多的buffer cache晚上运行批作业(大量)需要一个较大的大型缓冲区,需要同时配置缓冲区缓存和大量适应峰值需求。

使用ASMM，当OLTP作业运行时，缓冲区缓存会使用大部分内存来实现良好的I/O性能。当数据分析和报告批处理作业稍后启动时，内存将自动迁移到大池，以便并行查询操作可以使用它，而不会产生内存溢出错误。

如果使用服务器参数文件(SPFILE)， Oracle数据库服务器会记住在实例关闭时自动调优的组件的大小。因此，每次启动一个实例时，系统确实需要再次了解工作负载的特征。它可以从过去实例的信息开始，并继续评估上次关闭时停止的工作负载。

2 相关的参数

如果启用了AMM，自动共享内存管理将自动启用。如果在配置数据库时没有启用AMM或没有启用ASMM，可以通过指定总的SGA大小来启用自动共享内存管理稍后可以通过增加总SGA_MAX_SIZE或SGA_TARGET初始化参数来增加总SGA大小。但是，不能将其设置为高于SGA最大的大小或SGA_MAX_SIZE参数指定的值。
当AMM被禁用时，PGA advisor是可访问的。建议使用PGA advisor设置PGA内存值。

3 ASMM的管理

自动共享内存管理特性使用SGA内存代理，该内存代理由两个后台进程实现:可管理性监视器(MMON)和内存管理器(MMAN)。MMON定期在内存中捕获统计信息和内存咨询数据。MMAN根据MMON决策协调内存组件的大小。SGA memory broker跟踪组件的大小并且改变大小。SGA memory broker观察系统和工作负载，以确定理想的内存分布。它每隔几分钟执行一次检查，这样内存就可以随时出现在需要的地方。在没有自动共享内存管理的情况下，必须对组件进行大小调整，以预测它们各自的最坏情况下的内存需求。

基于工作负载信息，自动共享内存管理:
在后台定期捕获统计信息
使用内存顾问
执行分析以确定内存的最佳分布
移动内存到最需要它的地方
如果使用了SPFILE，则在关闭时保存组件大小(可以从上次关闭前恢复大小)

4 Automatic Shared Memory Advisor

在启用ASMM之前，应该从SPFILE中删除各个内存区域参数，因为设置它们可能会对ASMM施加限制。启用ASMM后，可以使用SGA大小顾问来帮助选择总SGA大小的最佳值。

如果在看到ASMM分配的效果之后，决定要调整特定的组件分配，那么可以为这些组件指定值。如果指定的值小于当前值，则将这些值视为各自组件的最小内存大小。如果指定的值大于当前值，则只要有可用的空闲内存，内存组件的大小就向上调整为提供的值。设置限制会减少用于自动调整的可用内存量，但该功能是可用的，有助于提高总体性能。

所关注的初始化参数如下:
· SHARED_POOL_SIZE
· LARGE_POOL_SIZE
· JAVA_POOL_SIZE
· DB_CACHE_SIZE
· STREAMS_POOL_SIZE

要在启用ASMM时调整这些参数，必须使用ALTER SYSTEM命令。

5 启用ASMM

启用ASMM的过程是不同的，这取决于是从手动共享内存管理还是从自动内存管理切换到ASMM。

从手动共享内存管理改为ASMM:

1. 执行以下查询来获取SGA_TARGET的值:
   SELECT (SELECT SUM(value) FROM V

   SGA)−(SELECTCURRENTSIZEFROMV

   SGA)−(SELECTCURRENTSIZEFROMVSGA_DYNAMIC_FREE_MEMORY))

2. 设置SGA_TARGET的值:
   ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
   其中value是在步骤1中计算的值，或者是所有SGA组件大小和SGA_MAX_SIZE之间的某个值。

3. 将自动调整大小的SGA组件的值设置为0。通过编辑文本初始化参数文件或发出ALTER SYSTEM语句来做到这一点。如果需要，重新启动实例。
   从自动内存管理改为ASMM:

4. 将MEMORY_TARGET初始化参数设置为0。
   ALTER SYSTEM SET MEMORY_TARGET = 0;
   数据库根据当前的SGA内存分配设置SGA_TARGET。

5. 将自动调整大小的SGA组件的值设置为0。完成后重新启动实例。
   

6 禁用ASMM

通过将SGA_TARGET设置为0，可以动态选择禁用自动共享内存管理。所有自动调优参数的值都被设置为对应组件的当前大小，即使用户之前已经为自动调优参数指定了不同的非零值。

如图SGA_TARGET的值是8 GB, SHARED_POOL_SIZE的值是1 GB。如果系统在内部将共享池组件的大小调整为2 GB，那么将SGA_TARGET设置为0将导致SHARED_POOL_SIZE被设置为2 GB，从而覆盖原始用户指定的值。

7 练习

SGA_TARGET 不能大于如下哪个参数的值？
A LOG_BUFFER
B SGA_MAX_SIZE
C STREAMS_POOL_SIZE
D PGA_AGGREGATE_TARGET
答案：B

推荐阅读：144页！分享珍藏已久的数据库技术年刊

推荐下载：2020数据技术嘉年华PPT下载

2020数据技术嘉年华近50个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“DTC2020”获得！

视频号，新的分享时代，关注我们，看看有什么新发现？

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂

  点个“在看”

你的喜欢会被看到❤