本文主要是介绍Oracle优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

核心：减少IO——只要将大多数磁盘操作转换成内存操作，数据库系统的效率就会显著提高。

一、内存优化

1.1 SGA

1. sga_max_size：分配给SGA的最大内存。

SGA不能太大，一般设置可以设置为当前内存大小即可。静态参数，改后重启生效。

SQL> show parameter sga_max_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 3200M

SQL> alter system set sga_max_size=3500M scope=spfile;
System altered.

SQL> shutdown immediate;

[遇到报错]：

SQL> startup;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 3674210304 cannot be set to more than MEMORY_TARGET 3355443200.

[原因]：修改sga_max_size=3500M之后，停机重启报错，因为设定的值过大，无法启动，甚至无法startup nomount;

[解决方法]：找到参数文件目录，由于spfile是二进制的无法直接修改，需要从动态文件生成一个静态文件，修改sga_max_size的值，并通过pfile启动数据库

① 创建一个pfile文件

SQL> create pfile from spfile;

② 修改pfile中的值

[oracle@test1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@test1 dbs]$ vim initorcl.ora 
...
*.sga_max_size=3000016000
...

③ 通过pfile启动Oracle

SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
ORACLE instance started.

④ 备份或删除原有的spfile，通过pfile生成新的spfile

[oracle@test1 dbs]$ mv spfileorcl.ora spfileorcl.ora.bak

SQL> create spfile from pfile;

2. lock_sga：将SGA锁定在物理内存，不使用swap。静态参数，改后重启生效。

SQL> show parameter lock_sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE

SQL> alter system set lock_sga=true scope=spfile;

3. sga_target：自动共享内存管理。

指定了SGA可以使用的最大内存大小，而SGA中各个内存的大小由Oracle自行控制，不需要人为指定。Oracle可以随时调节各个区域的大小，使之达到系统性能最佳状态的个最合理大小，并且控制他们之和在SGA_TARGET指定的值之内。一旦给SGA_TARGET指定值后（默认为0，即没有启动ASMM），就自动启动了ASMM特性。

SQL> show parameter sga_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

SQL> alter system set sga_target=3200m;

1.2 PGA

PGA优化就是将大规模数据排序放在PGA里运行。

1. PGA排序区自动管理

SQL> select name,value,isdefault 
from v$parameter 
where name in ('pga_aggregate_target','workarea_size_policy');

NAME                                  VALUE                    ISDEFAULT
------------------------------------------------------------------------------
pga_aggregate_target                      0                         TRUE
workarea_size_policy                   AUTO                         TRUE

pga_aggregate_target：定义了实例中所有服务器进程的PGA总和。

workarea_size_policy：是否为排序自动管理。

2. 查看PGA状态

SQL> select name,round(value/1024/1204,2) as "value(MB)" from v$pgastat 
where name in ('aggregate PGA target parameter','aggregate PGA auto target','cache hit percentage');    -- 三个重要参数

NAME                               value(MB)     
------------------------------------------------
aggregate PGA target parameter       1088.64      --当前系统PGA总和
aggregate PGA auto target             864.37      -- 排序区分配的内存大小
cache hit percentage                       0      -- 排序区完成比例

3. 调整PGA内存大小

alter system set PGA_aggregate_target=1500M
show parameter pga_aggregate_target;

二、IO优化

2.1 文件层面

1. 给I/O带来很大负荷的是重做日志文件、数据文件、索引文件的读写。

2. 数据文件与重做日志文件尽量放在不同的磁盘上；

　 原因：CKPT、DBWn、SERVER三大进程都对数据文件进行操作； LGWR、ARCn、SERVER三大进程都对重做日志文件进行操作； 若两个文件存放在同一磁盘上，会导致两个文件的I/O竞争非常大。

3. 表和索引分别存放在不同的表空间中，因为一个表和表上的索引是明显存在竞争的。

2.2数据文件IO信息

select a.file# "文件号",NAME "文件名",phyblkrd "读的物理块数",phyblkwrt "写的物理块数",readtim "读时间",writetim "写时间" from v$filestat a join v$datafile b
on a.FILE#=b.FILE#;

文件号           文件名                                读的物理块数   写的物理块数    读时间     写时间
-----------------------------------------------------------------------------------------------------
1      /u01/app/oracle/oradata/orcl/system01.dbf        19369      15060        119        906
2      /u01/app/oracle/oradata/orcl/sysaux01.dbf        69371     241200        280      13425
3      /u01/app/oracle/oradata/orcl/undotbs01.dbf       27206      78275         69       3092
4      /u01/app/oracle/oradata/orcl/users01.dbf          5272          0          7          0
5      /u01/app/oracle/oradata/orcl/example01.dbf         582          0          5          0
6      /u01/app/oracle/oradata/orcl/test01.dbf              2          0          0          0
7      /u01/app/oracle/oradata/orcl/undotbs02.dbf          22          0          0          0
8      /u01/app/oracle/oradata/orcl/testtbs01.dbf           2          0          0          0

8 rows selected

2.3 重做日志优化

redo-log不能设置太小，太小会造成日志组的频繁切。切换时还要触发CKPT。CKPT还要读写数据文件和控制文件。因此会产生大量的输入输出。

查看redo-log的切换历史

select sequence#,to_char(first_time,'RR-MM-DD HH:MM:SS') "Date Time" from v$log_history;
 
SEQUENCE#      Date Time
......
405            21-11-08 10:11:54
406            21-11-08 10:11:57
407            21-11-08 11:11:15
408            21-11-09 02:11:36
409            21-11-09 07:11:03
410            21-11-09 11:11:46

增加redo-log大小

ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M;

这篇关于Oracle优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！