Oracle性能调优

小咪咪 2022-12-19 13:17 204阅读 0赞

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';