Oracle系统参数调整【数据库实例优化系列一】-CSDN博客

图1

Oracle实例是：内存组件和相关的后台进程组成。这些内存组件提高了数据库的运行，而后台进程负责管理系统和内存组件。

一、SGA和实例优化

Oracle的SGA是指的系统全局区。sga是数据库运行期间使用的一段公有内存，即数据库用户都可以访问这段内存，包括：

共享池、重做日志缓冲区、java池、大池、流池等。

优化SGA就是调整这些数据库组件的参数。这些组件就是实例优化的对象。从而提高系统的执行效率。比如太高用户查询的相应时间等。

1、数据库缓冲区：

存放的是用户从数据库中读取的数据。当用户查找数据的时候首先在这个数据库缓冲区中搜索，如果没有，才会读取数据文件。所以这个区域不能设置的太小。不然频繁的读取文件会增加IO磁盘的压力，耗时。

2、重做日志缓冲区：

存放的是用户改变的数据，所以当数据发生变化、数据需要回滚什么的都暂时放在这个缓冲区中。这个缓冲区也不能设置的太小。涉及的参数 log_buffer

sys@ORCL>show parameter log_buffer;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_buffer big integer 7456K

sys@ORCL>alter system set log_buffer = 250m scope=spfile;

扩大重做日志缓冲区的大小，重启生效

sys@ORCL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORCL>startup mount;

ORACLE instance started.

Total System Global Area 3221225472 bytes

Fixed Size 8797928 bytes

Variable Size 754975000 bytes

Database Buffers 2181038080 bytes

Redo Buffers 276414464 bytes

Database mounted.

sys@ORCL>alter database archivelog;

Database altered.

sys@ORCL>alter database open;

Database altered.

sys@ORCL>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

sys@ORCL>

sys@ORCL>show parameter log_buffer;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_buffer big integer 250M

3、共享池：

其中包括数据字典高速缓存和库高速缓存。

库高速缓存，存放的是Oracle的解析SQL语句、PL/SQL过程、包以及各种控制结构。比如锁、库缓存句柄等。

数据字典高速缓存，存放的是高速缓存执行sql语句所需要的各种数据字典定义，比如表、列的定义，用户访问的表权限等。

4、java池：

执行java代码的区域。

5、大池：

改内存区提供大型的内存分配，在共享服务器连接模式下提供会话区，在使用RMAN备份时也是使用该内存区作为IO磁盘的数据缓冲区。

6、流池：

也被称为流内存，是Oracle流专用的内存池。可以通过streams_pool_size 动态设置大小

综上。在Oracle11g以及更高版本中，sga中的内存参数是可以动态设置修改，但是总的内存大小受到参数 sga_max_size 的限制。

在安装Oracle的时候，这个参数值是默认的。

实际生产中的数据库中，是需要设置一个新的参数值，

图2

sys@ORCL>show parameter sga_max_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_max_size big integer 3G

sys@ORCL>show sga;

Total System Global Area 3221225472 bytes

Fixed Size 8797928 bytes

Variable Size 620757272 bytes

Database Buffers 2315255808 bytes

Redo Buffers 276414464 bytes

修改sga_max_size ，重启数据后生效

alter system set sga_max_size = 700m scope=spfile;

查看sga相关静态参数

sys@ORCL>show parameter sga;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

allow_group_access_to_sga boolean FALSE

lock_sga boolean FALSE

pre_page_sga boolean TRUE

sga_max_size big integer 3G

sga_min_size big integer 0

sga_target big integer 3G

unified_audit_sga_queue_size integer 1048576

二、对系统参数的思考

这些参数对sga有什么干扰作用呢？

lock_sga：

作用是锁定sga在物理内存，这样就不会发生sga使用虚拟内存的情况。这样可以提高数据的读取速度。记住磁盘IO操作永远是计量避免或者减少的。

安装后默认是false

开启sga内存锁定：

alter system set lock_sga = true scope=spfile; # 注意修改参数，重启db服务生效

pre_page_sga：

作用是启动数据实例时，将sga读入物理内存，对于内存充足的系统而言。这样的设置显然可以提高系统运行效率。默认是false，我们设置为true

alter system set pre_page_sga = true scope=spfile; # 注意修改参数，重启db服务生效

sga_target：

该参数和系统的sga_max_size 是一样大的。当该参数不为0时。则启动SGA的自动管理，该参数可以自动修改。下面我们

sys@ORCL>alter system set sga_max_size =700m scope=spfile;

sys@ORCL>alter system set sga_target = 700m scope=spfile;

sys@ORCL>alter system set lock_sga = true scope=spfile;

sys@ORCL>alter system set pre_page_sga=true scope=spfile;

1、修改参数后重启DB故障排查

*************************************************************************

修改后报错：

sys@ORCL>startup mount;

ORA-27126: unable to lock shared memory segment in core

Linux-x86_64 Error: 12: Cannot allocate memory

Additional information: 3943

Additional information: 4

Additional information: 8798208

错误原因：

1.确保sga_target,sga_max_size比可用物理内存小

2.检查kernel.shmmax，kernel.shmall是否设置得太小

一般出现ORA-27102：out of memory，是/etc/sysctl.conf种内核参数kernel.shmmax，kernel.shmall设置得小了。

mount失败的原因是，sga的内存被lock了，参数设置的顺序错了

设置原则：

建议设置kernel.shmmax为物理内存的一半;

建议设置kernel.shmall为物理内存/pagesize;

--pagesize这样查：getconf PAGESIZE

确保kernel.shmmax比rac集群种所有实例的sga_max_size/sga_target的和要大；

3.检查是否设置了lock_sga=TRUE,如果设置为TRUE的话，需要调大操作系统对oracle用户

max locked memory的大小。

该值默认是64K

解决方案：

方法一

vi /etc/security/limits.conf，新增如下配置：

oracle hard memlock 237621175

oracle soft memlock 237621175

修改后重启服务器，问题解决

sys@ORCL>show parameter sga;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

allow_group_access_to_sga boolean FALSE

lock_sga boolean TRUE

pre_page_sga boolean TRUE

sga_max_size big integer 700M

sga_min_size big integer 0

sga_target big integer 700M

unified_audit_sga_queue_size integer 1048576

sys@ORCL>

还原设置。注意设置参数的顺序，顺序错了，可能导致sga被lock，导致mount失败

sys@ORCL>alter system set lock_sga = false scope=spfile;

sys@ORCL>alter system set pre_page_sga=false scope=spfile;

sys@ORCL>alter system set sga_max_size =3g scope=spfile;

sys@ORCL>alter system set sga_target = 3g scope=spfile;

方法二

将lock_sga=TRUE改为lock_sga=FALSE,设置memory_target大小。这样就不用改/etc/security/limits.conf文件了，还能使用memory_target自动调整内存的特性

*************************************************************************

sga可以自动管理，但是不是所有的内存组件（重做日志缓冲区、不能自动调整）都可以自动调整，sga可以自动调整一下组件：

共享池

Java池

大池

数据库缓冲区

流池

使用SQL查看自动调整内存组件的信息：

select name ,value ,isdefault from v$parameter

where name in ('sharad_pool_size','large_pool_size','java_pool_size');

sys@ORCL>col name for a30

sys@ORCL>col value for a20

sys@ORCL>select name ,value ,isdefault

2 from v$parameter

3 where name in ('shared_pool_size','large_pool_size','java_pool_size');

NAME VALUE ISDEFAULT

------------------------------ -------------------- ---------

shared_pool_size 0 TRUE

large_pool_size 0 TRUE

java_pool_size 0 TRUE

虽然这些参数可以由sga自动管理，但是用户也能自定义设置。比如设置java_pool_size 的大小。

alter system set java_pool_size = 20m;

sys@ORCL>alter system set java_pool_size =20m;

System altered.

sys@ORCL>show parameter java_pool_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

java_pool_size big integer 32M

为神马自己设置的和查询的不一致呢，这是因为Oracle对系统自身的情况作了自动调整。是数据库自己的行为。