Oracle性能调优
数据写入Oracle过程中性能不高，系统负载显示正常，尝试寻找Oracle写入性能瓶颈。
首先，执行下面的查询，通过 V$SYSTEM_EVENT 视图查看数据库中某些常见的等待事件：
select * from v$system_event where event in (
'buffer busy waits','db file sequential read',
'db file scattered read','enqueue',
'free buffer waits','latch free',
'log file parallel write','log file sync');
接着，执行下面命令，对 V$SESSION 和 V$SESSION_EVENT 视图进行查询，分析是否存在对上面显示的内容有贡献的等待事件的会话：
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.username is not null
and se.sid=s.sid
and s.status='ACTIVE'
and se.event not like '%SQL*Net%';
结果发现了瓶颈原因，下面信息引起了我的注意：
SID USERNAME EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
--- --------- ------------------------------------------- ------------ ------------ ------------
212 TEST buffer busy waits 4 0 1735
212 TEST log file switch (checkpoint incomplete) 43 6239 145.1
。。。。
有很多耗时长的事务都是log file switch（日志切换），看来瓶颈在日志切换上。
突然想起来了，Oracle默认日志组大小50M，尝试调整日志组大小来进行优化，方法参考：
Oracle数据库redolog日志调整大小：https://blog.csdn.net/sunny05296/article/details/58591826/
调整后，果然性能明显提升了。
========================
其他相关调优排查相关命令（在终定位到是上面的原因之前，我尝试了很多，但是没效果）：
关于 shmall & shmax
shmall: 该参数控制可以使用的共享内存的总页数。 Linux 共享内存页大小为4KB, 共享内存段的大小都是共享内存页大小的整数倍。
一个共享内存段的最大大小是16G，那么需要共享内存页数是16GB/4KB=4194304页
当内存为 12G 时， kernel.shmall = 3145728
当内存为 16G 时， kernel.shmall = 4194304
当内次为 32G 时， kernel.shmall = 8388608
当内存为 64G 时， kernel.shmall = 16777216
当内存为 128G 时， kernel.shmall = 33554432
shmmax: 是核心参数中最重要的参数之一，用于定义单个共享内存段的最大值。
64 位 linux 系统：可取的最大值为物理内存值 -1byte ，建议值为多于物理内存的一半，一般取值大于 SGA_MAX_SIZE 即可，可以取物理内存 -1byte 。
* 内存为 12G 时， 该值为 12*1024*1024*1024-1 = 12884901887
* 内存为 16G 时， 该值为 16*1024*1024*1024-1 = 17179869183
* 内存为 32G 时， 该值为 32*1024*1024*1024-1 = 34359738367
* 内存为 64G 时， 该值为 64*1024*1024*1024-1 = 68719476735
* 内存为 128G 时，该值为 128*1024*1024*1024-1 = 137438953471
调整 shmall & shmmax 的原因：
如果我们的sga为16GB。物理服务器是32GB。那么这两个参数设置值如下:
kernel.shmall = 8388608 #32*1024*1024*1024/(4*1024)
kernel.shmmax = 17179869184 #32*1024*1024*1024 * 50%
为什么要设置大一点的shmall和shmmax，因为想让Oracle的一个instance包含在一个共享内存段中。
可以看，上述服务器的共享内存段分配了14个，这样会出现内存地址断层(GAP)。当数据库进行IPC通信时，会出现跨共享内存段的内部数据交互。这样会降低内存段间的数据交互效率。
因此，1个共享内存段包含数据库instance实例所需要的内存，可以减少跨内存段的交互，提高数据库的性能。
SQL> show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
parallel_servers_target integer 256
pga_aggregate_target big integer 103321M
sga_target big integer 1536M
SQL>
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 103321M
SQL>
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1536M
sga_target big integer 1536M
SQL>
SQL> SELECT * FROM V$PGASTAT;
...
aggregate PGA auto target : 当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET小
over allocation count : 实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值
修改pga,sga命令：
alter system set sga_max_size=4096M scope=spfile;
alter system set sga_target=4096M scope=spfile;
alter system set sga_max_size=16384M scope=spfile;
alter system set sga_target=16384M scope=spfile;
过程中我调整内核参数导致数据库启动失败，定位到的原因是sga的设置太大、超出了内核参数shmmax的值。
如果是RAC环境，需要加 sid='*';
alter system set pga_aggregate_target=512m scope=both;
SQL> show parameters policy;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
workarea_size_policy string AUTO
SQL>
workarea_size_policy ：PGA内存管理模式。PGA分两种管理模式：手动PGA内存管理，自动PGA内存管理。
手动PGA内存管理（MANUAL）：用户指定排序区和散列区所使用的内存，每个连接使用相同的内存。
自动PGA内存管理（AUTO）：告诉Oracle可以使用的PGA的总量，由Oraclce根据系统负载决定具体分配。即PGA内存可以动态扩大和回收
9iR1时默认为手动PGA内存管理，9iR2以后默认为自动PGA内存管理。
workarea_size_policy 设为MANUAL，使用手动管理方式；设为AUTO，并且pga_aggregate_target不为0时，启用自动内存管理。
PGA的大小分配原则：
PGA_AGGREGATE_TARGET 的值应该基于Oracle实例可利用内存的总量来设置。
假定当前服务器可分配物理内存为4GB，且仅一个实例运行在该服务器，则可以考虑分配80％的可用内存给Oracle实例，即3.2G。
剩下的内存分配给操作系统和其它应用程序。此时必须在内存中划分SGA和PGA区域。
在OLTP系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在DSS系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
在这个例子中，总内存4GB，DSS系统，你可以设置PGA_AGGREGATE_TARGET为1600MB，OLTP则为655MB。
Oracle官方文档推荐:
MEMORY_TARGET=物理内存 x 80%
MEMORY_MAX_SIZE=物理内存 x 80%
对于OLTP系统：
SGA_TARGET=(物理内存 x 80%) x 80%
SGA_MAX_SIZE=(物理内存 x 80%) x 80%
PGA_AGGREGATE_TARGET=(物理内存 x 80%) x 20%
对于DSS系统：
SGA_TARGET=(物理内存 x 80%) x 50%
SGA_MAX_SIZE=(物理内存 x 80%) x 50%
PGA_AGGREGATE_TARGET=(物理内存 x 80%) x 50%
查看当前的SGA中的动态信息
select * from v$sga;
select * from v$sgainfo;
查看共享池内存的大小
show parameter shared_pool_size;
查看共享池命中率
select sum(pinhits) / sum(pins) * 100 "hitPercent" from v$librarycache;
查看数据缓冲区
show parameter db_cache_size;
查看数据缓冲区命中率
select (1 - ((physical.value - direct.value - lobs.value) / logical.value)) * 100 "hitPercent"
from v$sysstat physical,
v$sysstat direct,
v$sysstat lobs,
v$sysstat logical
where physical.name = 'physical reads'
and direct.name = 'physical reads direct'
and lobs.name = 'physical reads direct (lob)'
and logical.name = 'session logical reads';
查看PGA命中率：
SELECT a.VALUE "hitPercent" FROM V$PGASTAT a WHERE a.NAME = 'cache hit percentage';
1642
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
