数据库]Oracle 内存结构和参数调整
Oracle内存结构与参数调优
- Oracle 内存结构概览
- oracle参数配置概览
- 重要参数(系统运行前配置):
- 次要参数(可在系统运行后再优化调整):
- Oracle数据库服务器参数如何调整
- OLTP内存分配
- 操作系统核心参数配置
- Disabling ASMM(禁用自动共享内存管理)
- PGA(Program Global Area)
- PGA_AGGREGATE_TARGET
- PROCESSES
- OPEN_CURSORS & SESSION_CACHED_CURSORS
- 不用重新启动而修改初始参数文件
- 启动大页(vm.nr_hugepages)
Oracle 内存结构概览
oracle 体系结构:
Oracle 内存结构:
- System Global Area (SGA,系统全局区)
- Share Pool (共享池)
- Database Buffer Cache (数据库缓冲区缓存)
- Redo Log Buffer (重做日志缓冲区)
- Large Pool (大池)
- Java Pool(Java池)
- Streams Pool(流池)
- Fixed SGA(固定SGA区域)
- Program Global Area (PGA,程序全局区域)
- User Global Area(UGA,用户全局区域)
- Cursor Area(游标区)
- User Session Data Storage_Area (用户会话数据存储区)
- SQL Work Areas(SQL工作区)
- 1). Sort Area(排序区)
- 2). Hash Area(哈希区)
- 3). Create Bitmap Area(位图索引创建区)
- 4). Bitmap Merge Area (位图索引融合区)
- Stack Space(堆栈空间)
- User Global Area(UGA,用户全局区域)
oracle参数配置概览
重要参数(系统运行前配置):
带MAX的参数不能在线修改,需要重启后才生效,所以一定要提前规划好
-
内存管理参数:
MEMORY_MAX_TARGET
(11g): 定义了Oracle数据库实例允许使用的最大内存量,包括SGA和PGA。动态调整可用内存,无需重启实例。MEMORY_TARGET
(11g): 指定数据库实例使用的内存目标,包括SGA和PGA。与MEMORY_MAX_TARGET
结合使用,实现动态内存管理。 -
SGA参数:
SGA_TARGET
(10g): 自动管理SGA的总内存大小,包括共享池、数据缓存等。通过自动调整子组件大小,适应变化的工作负载需求。 -
PGA参数:
PGA_AGGREGATE_TARGET
(9i): 指定PGA的目标大小,由系统动态调整,根据工作负载需求自动分配内存给各个会话的PGA。 -
数据缓存参数:
DB_CACHE_SIZE
(10g): 指定数据库缓存的大小,用于缓存数据块,影响数据的读取性能。 -
共享池参数:
SHARED_POOL_SIZE
(10g): 定义共享池的大小,用于缓存SQL语句、PL/SQL块和执行计划。影响SQL执行性能。 -
连接和进程参数:
PROCESSES
(10g): 控制并发用户连接的最大数量。OPEN_CURSORS
(10g): 设置一个会话可以打开的最大游标数。SESSION_CACHED_CURSORS
(10g): 缓存在会话中的游标数,减少游标重新解析的开销。
次要参数(可在系统运行后再优化调整):
-
大池参数:
LARGE_POOL_SIZE
: 用于备份和并行操作的大型I/O的合并。适用于特定工作负载需求。 -
Java虚拟机参数:
JAVA_POOL_SIZE
: 指定Java虚拟机使用的内存大小。影响Java相关操作的性能。 -
数据流参数:
STREAMS_POOL_SIZE
: 为Oracle Streams提供的内存池,用于处理数据流操作。 -
保留缓存参数:
DB_KEEP_CACHE_SIZE
: 为频繁访问的对象提供的保留缓存,提高这些对象的性能。 -
循环缓存参数:
DB_RECYCLE_CACHE_SIZE
: 为一次性或不经常访问的对象提供的循环缓存。 -
日志缓冲区参数:
LOG_BUFFER
: 控制数据库日志缓冲区的大小,适应不同的日志写入需求。 -
结果缓存参数:
RESULT_CACHE_SIZE
: 指定结果集缓存的大小,用于缓存SQL查询结果,提高重复查询性能。 -
自动PGA管理:
PGA_AGGREGATE_LIMIT
(11g R2以后):指定PGA的硬限制,防止PGA使用过多内存。 -
自动诊断和调整:
ADDM(Automatic Database Diagnostic Monitor)
:自动数据库诊断监控,提供关于性能问题和建议的报告。AWR(Automatic Workload Repository)
:自动工作负载存储库,用于存储性能统计信息。 -
SQL执行计划管理:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
:启用或禁用自动捕获SQL执行计划基线的功能。OPTIMIZER_USE_SQL_PLAN_BASELINES
:启用或禁用使用SQL执行计划基线的功能。 -
自动调整:
ADAPTIVE_PLAN_MANAGEMENT
:启用或禁用自适应计划管理,允许数据库自动调整执行计划。 -
分区表和分区索引:
PARTITION_TABLE
:用于启用或禁用分区表。PARTITION_INDEX
:用于启用或禁用分区索引。 -
数据压缩:
COMPATIBLE
:指定数据库版本,决定数据库是否支持特定的功能,例如表级数据压缩。 -
连接管理:
SHARED_SERVERS
:指定共享服务器的数量,用于连接管理。 -
并行执行:
PARALLEL_DEGREE_POLICY
:用于设置并行度策略,可以是 MANUAL(手动)或 AUTO(自动)。PARALLEL_MIN_SERVERS
和PARALLEL_MAX_SERVERS
:用于设置最小和最大并行服务器的数量。 -
数据库缓存:
DB_nK_CACHE_SIZE
:根据不同块大小设置数据库缓存大小。 -
RAC (Real Application Clusters) 参数:
CLUSTER_DATABASE_INSTANCES
:指定在RAC环境中的数据库实例数量。CLUSTER_DATABASE
:用于启用或禁用RAC功能。
Oracle数据库服务器参数如何调整
以 IBM3850 服务器为例,在安装好oracle软件且数据库建好之后,投入使用之前需要调整好相关参数
- CPU数量:4颗
- 内存容量:64G
OLTP内存分配
-
工作区(Work Area,排序、Hash Join、位图连接):6G
在OLTP(联机事务处理)环境下,数据库工作区用于临时存放排序、哈希连接、位图连接等操作产生的中间数据。这里的6GB是指为这些操作预留的内存空间,确保在处理复杂查询时有足够的内存供运算使用,减少对磁盘I/O的需求,从而提高系统性能。 -
Oracle服务器进程:一个连接 5M~10M,processes参数 * 10M,2000 * 10M=20G
在Oracle数据库中,每个客户端连接都会对应一个服务器进程。这里假定每个服务器进程需要5MB至10MB的内存。如果数据库参数processes
设置为可以同时接受2000个并发连接,那么所有服务器进程所需的总内存大约在10M * 2000 = 20GB左右。这部分内存是为了支持并发用户的活动,包括处理SQL查询、事务管理等操作。 -
OS系统自用:6G
这里的6GB内存是留给操作系统自己使用的,包括内核内存、缓存、以及其他系统组件的内存需求。这部分内存确保操作系统稳定运行,并可以处理各种系统级别的任务。 -
SGA = 64 - 6G - 20G - 6G = 32G
SGA(系统全局区)是Oracle数据库中用于存储共享数据结构的一个内存区域,包括数据缓冲区、重做日志缓冲区、共享池、大池等。根据给定的内存分配,64GB总内存减去工作区的6GB、服务器进程所需的20GB以及系统自用的6GB之后,剩余的32GB内存会被分配给SGA,以确保数据库的高效运作。
操作系统核心参数配置
cp /etc/sysctl.conf /etc/sysctl.conf.bak # 将当前的sysctl配置文件做一个备份,以防万一需要回滚
MemTotal=`grep MemTotal /proc/meminfo | awk '{print $2}'` # 从/proc/meminfo中提取系统总的内存大小(以KB为单位)
PAGE_SIZE=`getconf PAGE_SIZE` # 获取操作系统中一页内存的大小(以字节为单位)
shmmax=`expr $MemTotal \* 1024` # 根据总内存大小计算 shmmax 参数的最大值,此处将其设为总内存的四倍(转换为KB后再乘以1024转为字节)
shmall=`expr $shmmax / $PAGE_SIZE` # 计算 shmall 参数的值,表示系统范围内共享内存段的总页数上限,这里的计算方法是将 shmmax 值除以一页的大小得出
cat >> /etc/sysctl.conf << EOF # 向sysctl配置文件追加以下配置项
fs.file-max = 6815744 # 设置系统允许的最大文件句柄数
kernel.sem = 10000 10240000 10000 1024 # 设置信号量参数,分别代表SEMMSL(一个信号集中的信号量最大数)、SEMMNS(系统信号量总数)、SEMOPM(一次操作中的信号量操作数)、SEMMNI(系统信号集总数)
kernel.shmmni = 4096 # 设置系统范围内共享内存段标识符的最大数目
kernel.shmall = $shmall # 设置共享内存段页数上限,参考前面计算的结果
kernel.shmmax = $shmmax # 设置共享内存段的最大大小,参考前面计算的结果
net.ipv4.ip_local_port_range = 9000 65500 # 设置本地TCP/UDP端口范围
net.core.rmem_default = 16777216 # 设置接收缓冲区的默认大小
net.core.rmem_max = 16777216 # 设置接收缓冲区的最大大小
net.core.wmem_max = 16777216 # 设置发送缓冲区的最大大小
net.core.wmem_default = 16777216 # 设置发送缓冲区的默认大小
fs.aio-max-nr = 6194304 # 设置异步I/O请求数量上限
vm.dirty_ratio=20 # 设置脏页比例阈值,当系统中脏页占总内存的比例超过这个值时,开始写入磁盘
vm.dirty_background_ratio=3 # 设置后台脏页刷新比率,后台线程开始刷新脏页前,系统中脏页占总内存的比例
vm.dirty_writeback_centisecs=100 # 设置脏页写回时间间隔(以百分秒计)
vm.dirty_expire_centisecs=500 # 设置脏页过期时间间隔(以百分秒计)
vm.swappiness=10 # 控制系统换出内存到硬盘的速度,值越低,尽量避免换出,值越高,倾向于换出
vm.min_free_kbytes=524288 # 设置系统试图保留的最小空闲内存(以KB为单位)
net.core.netdev_max_backlog = 30000 # 设置网络设备接收数据包的最大队列长度
net.core.netdev_budget = 600 # 设置每次软中断处理的数据包数量
net.ipv4.conf.all.rp_filter = 2 # 开启IPv4反向路径过滤(RFC 3704),防止IP欺骗攻击
net.ipv4.conf.default.rp_filter = 2 # 对新建的网络接口开启默认的反向路径过滤
# vm.nr_hugepages =16384 # 若物理内存太小(<64G),则不需要开启大页,可以直接注释掉该行
# 注意:此处暂时先注释掉,后面启动大页会解释,需要根据实际需求和内存使用情况决定是否启用和设置正确的 HugePages 数量
EOF
# 配置生效
sysctl -p # 执行sysctl命令使得配置文件中所做的更改立即生效
#kernel.shmall:系统任意时刻可以分配的所有共享内存段的总和的最大值(以页为单位),其值应不小于shmmax/page_size,推荐设置为物理内存大小除以分页大小。
#kernel.shmmax:单个内存段最大,设置为内存大小
Disabling ASMM(禁用自动共享内存管理)
- SGA_TARGET 设置为0时,将禁用自动调优
- 自动调优参数设置为当前大小
- SGA的大小作为一个整体不受影响
PGA(Program Global Area)
PGA是Oracle数据库中每个服务器进程独有的内存区域,用于存储会话数据、排序区、临时变量以及其他与特定会话相关的数据结构。
PGA_AGGREGATE_TARGET
- 在OLTP系统中,典型PGA内存设置应该是总内存(物理内存)的较小部分(例如20%),剩下80%分配给SGA 。OLTP:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
- 在DSS系统中,由于会运行一些很大的查询,典型的PGA内存最多分配70%的内存。DSS:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
- 设置 PGA 自动分配
alter system set workarea_size_policy = auto scope = both;
alter system set pga_aggregate_target = 2048M scope = both;
show parameter pga
show parameter area
show parameter work
- 监控 PGA
select * from v$pgastat;
# 看 over allocation count(排序时,PGA不够用时需要换页,这个值就是换页次数):如果=0 就正常 PGA够用;若 >0 则PGA不够用需要调整
PROCESSES
- 在Oracle数据库中,设置参数 PROCESSES 和 SESSIONS 是非常重要的,以确保系统能够处理足够的并发连接。这只是一个基本的起点,实际的设置应该根据你的具体应用、系统硬件和负载情况来调整。建议在设置参数之前先进行性能测试,确保系统能够处理预期的并发连接数。
典型的设置是:
10g: sessions = processes + 10% * processes + 5
11g+: sessions = processes + 15 % * processes + 22 - 查看内存使用情况
select p.spid from v$session s,v$process p where p.addr=s.paddr and s.sid in (select sid from v$mystat where rownum=1);
select pga_used_mem,pga_alloc_mem,pga_freeable_mem from v$process where spid = &spid;
- Processes参数优化
create pfile='/tmp/pfileORCL_20240307.ora' from SPFILE; --备份配置文件
alter system set processes = 200 scope = spfile;
OPEN_CURSORS & SESSION_CACHED_CURSORS
- OPEN_CURSORS
- 指定了保存(打开)用户语句的专用区域的大小。如果获得“ORA-01000:maximun open cursors exceeded” 错误,则可能需要增大该参数,但是要确保关闭不在需要的游标。
- 如果 OPEN_CURSORS 设置得过高,则有时也会造成问题:ORA-4031错误(共享池)
- SESSION_CACHED_CURSORS
- 不要将参数 SESSION_CACHED_CURSORS 设置的和 OPEN_CURSORS 一样高,否则就可能产生 ORA-4031 或 ORA-7445 错误(软软解析)
不用重新启动而修改初始参数文件
- spfile<SID>.ora (大部分不需要重启)
- spfile.ora (用的少)
- init<SID>.ora (一定要重启)
-
v$parameter 视图
ISSES_MODIFIABLE 表名拥有 ALTER SESSION 特权的用户是否可以修改这个参数
ISSYS_MODIFIABLE 表名拥有 ALTER SYSTEM 特权的用户是否可以修改这个参数
启动大页(vm.nr_hugepages)
什么是大页?
在操作系统层面,默认情况下内存页大小通常为4KB。大页是一种内存管理机制,将标准内存页尺寸增大,例如从4KB改为2MB。使用大页可以减少TLB(Translation Lookaside Buffer)未命中的情况,从而降低页交换频率和I/O开销,提高系统的性能,特别是在大规模内存管理和Oracle数据库等场景下。
什么时候需要启动大页?
当服务器物理内存>=64GB,启用大页可以带来显著的性能提升。
如何启动大页?
-
关闭Oracle Database中的AMM(Automatic Memory Management)
如果尚未手动管理内存,请确保禁用自动内存管理功能。 -
计算hugepages的大小
根据SGA_MAX_SIZE的值来决定hugepages的数量。hugepages的总量应略大于SGA的需求量,同时也要注意避免过大导致内存浪费。例如,如果SGA_MAX_SIZE为32G,而每个大页是2MB,那么至少需要16384个大页(32GB / 2MB)。 -
设置hugepages
编辑内核参数文件/etc/sysctl.conf
,加入以下行:vi /etc/sysctl.conf vm.nr_hugepages = 16384 #<计算得出的大页数量>
然后执行
sysctl -p
命令使更改生效。 -
设置用户进程内存锁定限制
在/etc/security/limits.conf
文件中为Oracle数据库用户设置内存锁定限制,确保其能够锁定足够大的内存以支持大页。[root@mydb ~]# vi /etc/security/limits.conf oracle soft memlock 2056000 oracle hard memlock 2056000
-
重启数据库
重启Oracle数据库以便新的内存设置生效。 -
查看大页使用情况
使用如下命令实时监控大页的使用情况:[root@mydb ~]# watch -n1 'cat /proc/meminfo | grep -i HugePages'
-
设置pre_page_sga参数
需要在数据库实例启动前预分配并初始化SGA,以利用大页优化性能:alter system set pre_page_sga = true scope = spfile;
参考链接:
- Oracle 23c 官方文档
- 【Oracle】内存结构详解(万字长文,建议收藏,蚕食为佳)
上一篇: 常见的前端面试问题-vue
推荐阅读
-
数据库]Oracle 内存结构和参数调整
-
Grid++Report 锐浪报表开发常见问题解答集锦-报表设计 问:怎样在设计时打印预览报表? 答:为了及时查看报表的设计效果,Grid++Report 报表设计应用程序提供了四种查看视图:普通视图、页面视图、预览视图与查询视图。通过窗口下边的 Tab 按钮可以在四种视图中任意切换。在预览视图中查看报表的打印预览效果,在查询视图中查看报表的查询显示效果。如果在报表的记录集提供了数据源连接串与查询 SQL,在进入预览视图与查询视图时会利用数据源连接串与查询 SQL 从数据源中自动取数,否则 Grid++Report 将自动生成模拟数据进行模拟打印预览与查询显示。注意:在预览视图与查询视图中看到的报表运行结果有可能与在你程序中的最终运行结果有差异,因为在报表的生成过程中我们可以在程序中对报表的生成行为进行一定的控制。 问:怎样用 Grid++Report 设计交叉表? 答:Grid++Report 没有提供专门实现交叉表的功能,其它的报表构件提供的交叉表功能一般也比较死板和功能有限。利用 Grid++Report 的编程接口可以做出灵活多变,功能丰富的交叉表。示例程序 CrossTab 就是一个实现交叉表的例子程序,认真领会此例子程序,你就可以做出自己想要各种交叉表,并能提取一些共用代码,便于重复使用。 问:怎样设置整个报表的缺省字体? 答:设置报表主对象的字体属性,也就是设置了整个报表的缺省字体。如果改变报表主对象的字体属性,则没有专门的设置字体属性的子对象的字体属性也跟随改变。同样每个报表节与明细网格也有字体属性,他们的字体属性也就是其拥有的子对象的缺省字体。 问:怎样在打印时限制一页的输出行数? 答:设定明细网格的内容行的‘每页行数(RowsPerPage)’属性即可。另外要注意‘调节行高(AdjustRowHeight)’属性值:为真时根据页面的输出高度自动调整行的高度,使整个页面的输出区域充满。为假时按设计时的高度输出行。 问:怎样显示中文大写金额? 答:将对象的“格式(Format)”属性设为 “$$” 及可,可以设置格式的对象有:字段(IGRField)、参数(IGRParameter)、系统变量(IGRSystemVarBox)与综合文字框(IGRMemoBox),其中综合文字框是在报表式上设格式。 问:能否实现自定义纸张与票据打印? 答:Grid++Report 完全支持自定义纸张的打印,只要在报表设定时在页面设置中选定自定义纸张,并指定准确的纸张尺寸。当然要在最终输出时得道合适的打印结果,输出打印机必须支持自定义纸张打印。Windows2000/XP/2003 操作系统上可以在打印机上定义自定义纸张,也可以采用这种方式实现自定义纸张打印。 问:怎样实现 0 值不打印? 答:直接设置格式串就可以,在“数字格式”设置对话框中选定“0 不显示”,就会得到合适的格式串。也可以通过直接录入格式串来指定 0 不显示,但格式串必须符合 Grid++Report 的规定格式。另一种实现办法是在报表获取明细记录数据时,在 BeforePostRecord 事件中将值为零的字段设为空,调用字段的 Clear 方法将字段置为空。 问:怎样实现多栏报表? 答:在明细网格上设‘页栏数(PageColumnCount)’属性值大于 1 即可。通过 Grid++Report 的“页栏输出顺序”还可以指定多栏报表的输出顺序是“先从上到下”还是“先从左到右”。 问:如何实现票据套打? 答:Grid++Report 为实现票据套打做了很多专门的安排:报表设计器提供了页面设计模式,按照设定的纸张尺寸显示设计面板,如果将空白票据的扫描图设为设计背景图,在定位报表内容的输出位置会非常方便。报表部件可以设定打印类别,非套打输出的内容在套打打印模式下就不会输出。 问:Grid++Report 有没有横向分页功能? 答:回答是肯定的,在列的总宽度超过打印页面的输出宽度时,Grid++Report 可以另起新页输出剩余的列,如果左边存在锁定列,锁定列可以在后面的新页中重复输出,这样可以保证关键数据列在每一页都有输出。仔细体会 Grid++Report 提供的多种打印适应策略,选用最合适的方式。Grid++Report 的多种打印适应策略为开发动态报表提供了很好的支持。 问:怎样实现报表本页小计功能? 答:定义一个报表分组,将本分组定义为页分组,在本分组的分组头与分组尾上定义统计。页分组就是在每页产生一个分组项,在每页的上端与下端都会分别显示页分组的分组头与分组尾,页分组不用定义分组依据字段。 报表运行 问:怎样与数据库建立连接? 答:如果在设计报表时指定了数据集的数据源连接串与查询 SQL 语句,Grid++Report 采用拉模式直接从数据源取得报表数据,Grid++Report 利用 OLE DB 从数据源取数,OLE DB 提供了广泛的数据源操作能力。如果 Grid++Report 的数据来源采用推模式,即 Grid++Report 不直接与数据库建立连接,各种编程语言/平台都提供了很好的数据库连接方式,并且易于操作,应用程序在报表主对象(IGridppReport)的 FetchRecord 事件中将数据传入,例子程序提供了各种编程语言填入数据的通用方法,对C++Builder 和 Delphi 还进行了专门的包装,直接关联 TDataSet 对象也可以将 TDataSet 对象中的数据传给报表。 问:打印时能否对打印纸张进行自适应?支持表格的折行打印吗? 答:Grid++Report 在打印时采用多种适应策略,通过设置明细网格(IGRDetailGrid)的‘打印策略(PrintAdaptMethod)’属性指定打印策略。(1)丢弃:按设计时列的宽度输出,超出范围的内容不显示。(2)绕行:按设计时列的宽度输出,如果在当前行不能完整输出,则另起新行进行输出。(3)缩放适应:对所有列的输出宽度进行按比例地缩放,使总宽度等于页面的输出宽度。(4)缩小适应:如果列的总宽度小于页面的输出宽度,对所有列的输出宽度进行按比例地缩小,使总宽度等于页面的输出宽度。(5)横向分页:超范围的列在新页中输出。(6)横向分页并重复锁定列。 问:如何改变缺省打印预览窗口的窗口标题? 答:改变报表主对象的‘标题(Title)’属性即可。 问:利用集合对象的编程接口取子对象的接口引用,但不是自己期望的结果。 答:Grid++Report中所有集合对象的下标索引都是从 1 开始,另按对象的名称查找对象的接口引用时,名称字符是不区分大小写的。 问:怎样在运行时控制报表中各个对象的可见性?即怎样在运行时显示或隐藏对象? 答:在报表主对象(GridppReport)的 SectionFormat 事件中设定相应报表子对象的可见(Visible)属性即可。 问:报表主对象重新载入数据,设计器中为什么没有反映新载入的数据? 答:应调用 IGRDesigner 的 Reload 方法。 问:怎样实现不进入打印预览界面,直接将报表打印出来?