Oracle 数据库调优

1. 数据库配置
- 1.1. 数据库配置文件
- 1.2. pfile 和 spfile 转换
2. 配置系统参数
- 2.1. 日期格式
- 2.2. 时区
3. 性能调优
4. 系统建议
- 4.1. 查看系统建议表

1 数据库配置

Oracle 中的参数文件是一个包含一系列参数以及参数对应值的操作系统文件，可以分为两种类型。它们是在数据库实例启动时候加载的，决定了数据库的物理结构、内存、数据库的限制及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名和路径等信息，是进行数据库设计和性能调优的重要文件。

1.1 数据库配置文件

在调优前，备份好 spfile，以防参数修改错误导致服务器重启不能起来，查看 spfile 所在的位置命令如下

show parameter spfile;

如果没有修改默认应该是如下路径

$ORACLE_HOME/dbs/spfile<sid>.ora

可以进到对应的路径下备份，并对

cd $ORACLE_HOME/dbs

1.2 pfile 和 spfile 转换

pfile 是文本文件的，而 spfile 是二进制格式的。pfile 文件可以用文本编辑器打开手工配置、而 spfile 不行，只能通过 SQL 命令在线修改。从操作系统上可以看到这两者的区别，初始化参数文件为文本文件，spfile 为数据文件

create pfile from spfile;
create spfile from pfile;

2 配置系统参数

2.1 日期格式

查看时间相关的参数

show parameters nls;

Oracle 不支持直接修改底层系统的时间格式，但是默认的时间格式为 DD-MON-RR 的字符串，显示得不是很友好，所以需要使用一些曲线救国的方式来修改日期时间的格式

方案一：在当前会话的启动后直接修改当前会话的时间格式

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

方案二：添加环境变量，每次启动 sqlplus 会直接读取环境变量从而达到修改日期格式的目的

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

方案三：在选取时间日期字段时显示使用 to_char 函数来格式化时间

select
  to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as now
from
  dual;

2.2 时区

查看数据库的时区

select dbtimezone from dual;

通过 tz_offset 转化数据库的时区和当前会话的时区

select
  tz_offset (dbtimezone) as "Remote Time Zone",
  tz_offset (sessiontimezone) as "Local Time Zone"
from
  dual;

3 性能调优

3.1 系统进程，会话和事务数目的设置

查看系统的进程数目，会话数目和最大事务数目分别

set heading off;
show parameter processes;
show parameter sessions;
show parameter transactions;

直接在 v$parameter 视图中查看进程数和会话数

column name format a32;
column value format a64;

select
  t.name,
  t.value
from
  v$parameter t
where
  name in ('sessions', 'processes', 'transactions');

一般讲 processes 的数值根据系统的性能来设置，但是 sessions 和 transactions 和 processes 有一定的换算关系

Oralce	sessions	transactions
11g (11.2.0.4)	1.5 × processes + 22	1.1 × sessions
10g	1.1 × processes + 5	1.1 × sessions

Oracle 10g 设置对应参数的快速对照表

	11g	11g	10g	10g
processes	sessions	transactions	sessions	transactions
100	172	110	115	110
128	214	141	146	141
200	322	220	225	220
256	406	282	287	282
300	472	330	335	330
512	790	563	568	563
800	1222	880	885	880
1000	1522	1100	1105	1100
1024	1558	1126	1131	1126
1200	1822	1320	1325	1320
1500	2272	1650	1655	1650

可以根据上述对应关系表格设置相应的参数

alter system set processes = 1024 scope = spfile;
alter system set sessions = 1568 scope = spfile;
alter system set transactions = 1126 scope = spfile;

3.2 CPU 数量

show parameters cpu;

3.3 内存相关参数

3.3.1 SGA 和 PGA 的查看

SGA 是 Oracle 数据库的全局内存，可以通过以下命令查看 SGA 的参数信息

set heading off;
show parameter sga;
show parameter pga;

一次性查看 SGA 和 Buffer Cache 的相关参数大小

column name format a32;
column value format a64;

select
  t.name,
  t.value
from
  v$parameter t
where
  t.name in ('sga_max_size', 'sga_target', 'pga_aggregate_target', 'workarea_size_policy')
order by
  t.name;

3.3.2 SGA 和 PGA 的设置

修改 SGA 和 PGA 参数的原则如下：一般物理内存 20% 用作操作系统保留，其他 80% 用于数据库，对于只作为数据库服务的机器可以加将 Oracle 的内存分配得更高。在 Oracle 数据库的内存确定后，SGA 可以分配可用内存 40% ~ 60% 之间，PGA 可以分配可用内存 20% ~ 40% 之间

-	parameter	range
OS	total_memory
OS	available_memory	(60% ~ 90%) × total_memory
SGA	sga_max_size	(60% ~ 80%) × available_memory
SGA	sga_target	(60% ~ 80%) × available_memory
PGA	pga_aggregate_target	(40% ~ 20%) × available_memory

alter system set sga_max_size = 30g scope = both;
alter system set sga_target = 30g scope = both;
alter system set pga_aggregate_target = 8g scope = both;

修改 PGA 的自动管理方式和大小

alter system set workarea_size_policy = auto scope = both;
alter system set pga_aggregate_target = 3072m scope = both;

3.4 缓冲区相关参数

3.4.1 查看缓冲区参数

column name format a32;
column value format a64;

select
  t.name,
  t.value
from
  v$parameter t
where
  t.name in ('db_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size')
order by
  t.name;

3.4.2 查看缓冲区命中率

缓冲区的命中率一般要达到 98% 以上才算正常

select
  100 * (1 - ((physical.value - direct.value - lobs.value) / logical.value)) as
    "Buffer Cache Hit Ratio (%)"
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';

3.4.3 推荐缓冲区值

获取推荐的值，SIZE_FOR_ESTIMATE 以 M 为单位

select
  name as "Name",
  size_for_estimate as "Adviced Size (M)",
  v$db_cache_advice.estd_physical_reads as "Estimed Physical Reads"
from
  v$db_cache_advice
where
  block_size = '8192'
  and advice_status = 'ON';

3.5 查看 SQL 执行的表

select
  s.*
from (
  select
    t.runtime_mem,
    t.executions,
    t.sql_text
  from
    v$sql t
  where
    t.executions > 10
  order by
    t.runtime_mem desc,
    t.executions desc) s
where
  rownum < 10;

select
  s.*
from (
  select
    t.runtime_mem,
    t.executions,
    t.sql_text
  from
    v$sql t
  where
    t.executions < 10
    and t.sql_text like 'select%'
  order by
    t.sql_text desc) s
where
  rownum < 10;

4 系统建议

4.1 查看系统建议表

select
  replace(lower(table_name), 'wrh$_', 'v$') as name
from
  all_tables
where
  lower(table_name) like '%advice%';

select t.* from v$sga_target_advice t;

select t.* from v$db_cache_advice t;