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;
