oracle系统调优

在Oracle数据库系统中，起到调节作用的参数叫初始化参数，在Oracle 8i及以前的版本中，这些初始化参数记录在INITsid.ora文件中；而Oracle 9i/10g/11g中将这些参数记录在SPFILEsid.ora二进制文件中

　　基本参数：一组可调整的参数，如control_files,db_block_size,process

　　高级参数：一组精细调整的参数,如shared_servers

 

主要系统条优参数

参数	说明
buffer_pool_keep	保留池大小（从db_block_buffers分配）目的是将对象保留在内存中，以减少I/O
buffer_pool_recycle	循环池大小（从db_block_buffers分配）目的是使用对象后将其清除，以便重复使用内存
control_file_record_keep_time	控制文件中可重新使用部分中的记录必须保留的最短时间（天数）
cursor_space_for_time	在一个游标引用共享SQL区时，确定将SQL区保留在共享池中还是从中按过期作废处理
db_block_buffers	缓冲区高速缓存中Oracle块中的数量，该参数会显著影响一个例程的SGA总大小
db_keep_cache_size	指定keep缓冲池中的缓冲区数，keep缓冲池中的缓冲区大小是主要块大小（db_block_size)
db_recycle_cache_size	指定recycle缓冲池的大小，recycle池中的缓冲区大小是主要块大小（db_block_size）
java_max_sessionsapce_size	以字节为单位，指定可供在服务器中运行的Java程序所使用的最大内存量。它用于存储每次数据库 调用Java的状态。如果用户的会话持续时间超过了该值，则该会话会由于内存不足而终止
java_pool_size	以字节为单位，指定Java存储池大小，它用于存储Java的方法和类定义在共享内存中的表示方法 以及调用结束时移植到Java会话空间的Java对象
large_pool_size	指定大型池的分配堆得大型，它可被共享服务器用作会话内存，用作并行执行的消息缓冲区以及用 作RMAN备份和恢复的磁盘I/O缓冲区
log_buffer	以字节为单位,指定在LGWR将重做日志条目写入重做日志文件之前，用于缓存这些条目的内存量。 重做条目保留对数据库块所作更改的一份记录。如果该值大于65536，就能减少重做日志文件I/O， 特别是在有长时间事物处理或大量事务处理的系统上
log_checkpoint_interval	指定在出现检查点之前，必须写入重做日志文件的OS块（不是数据库块）的数量。无论该值如何， 在切换日志是都会出现检查点。较低的值可以缩短例程恢复所需的时间，但可能导致磁盘操作过多
log_checkpoint_timeout	指定距下一个检查点出现的最大时间间隔（秒数）。将该时间指定为0，将禁用以时间为基础的 检查点。较低的值可以缩短例程恢复时间，但可能导致磁盘操作过量
max_dump_file_size	指定每个跟踪文件的最大大小。如果担心跟踪文件会占用太多空间，可更改该限制。如果转储文件 可以达到操作系统允许的最大大小，请设置为“无限制”
object_cache_max_size_percent	指定会话对象的高速缓存增长可超过最佳高速缓存打下的百分比，最大大小等于最佳大小加上该 百分比与最佳大小的成绩。如果高速缓存大小超过了这个最大大小，系统视图调整到最佳大小
optimizer_index_caching	调整基于成本的优化程序的假定值，即在缓冲区高速缓存中期望用于嵌套循环连接的索引块的百分比 它将影响使用索引的嵌套循环连接的成本。将该参数设置为一个较高的值，可以是嵌套循环连接相对 于优化程序来说成本更低
optimizer_index_cost_adj	在考虑太多或太少索引访问路径的情况下，可以用来优化程序的性能。该值越低，优化程序越容易 选择一个索引。也就是说，如果将该值设置为50，索引访问路径的成本就是正常情况下的一半
query_rewrite_enabled	启用或禁用实体化视图的查询重写。一个特定实体化视图只在如下条件下启用：会话参数和单独实体 化视图均已启用，并且基于成本的优化已启用
read_only_open_delayed	用于加速某些操作，如：启动一个很大的数据库而其中大多数数据库存储在只读的表空间中。如果 设置为TRUE，从表空间中读取数据时，将首先访问只读表空间中的数据文件
shared_pool_reserved_size	指定要为较大连续共享池内存请求而保留的空间，以避免由碎片引起的性能下降。该池的大小应符合 这样的条件，能存储为防止对象那从共享池刷新而普遍要求的所有大型过程和程序包
shared_pool_size	以字节为单位，指定共享池的大小。共享池包含如：共享游标、存储过程、控制结构和并行消息缓冲 区等对象。较大的值能改善多用户系统的性能
sort_area_size	以字节为单位，指定排序所使用的最大内存量。排序完成后，各行将返回，并且内存将释放。增大该 值可以提高大型排序的效率，如果超过了该内存量，将使用临时磁盘段

在进行系统优化时，可以使用alter system(整个数据库实例) /alter session（当前会话）

可以使用show parameter+参数名查看

 

 

系统全局区（SGA）优化

虽然11g增加了内存的自动调整

 

调整内存分配

　　一般内存的调整在应用程序和SQL语句做完调整后进行的。另外，建议用户在调整I/O前先调整内存分配。

调整日志缓冲区

　　如何记录日志文件呢？

方式一：使用逻辑的记录方式，也就是用描述性的语句记录整个变化过程。

　　如，对某个update，只记录delete旧值和insert新值

　　优点：节省空间

　　确定：一旦需要进行恢复就会非常消耗资源

方式二：使用物理记录方式，也就是说，把每个数据块的改变前的镜像和改变后的镜像都记录下来

　　优点：恢复速度快，

　　缺点：占用很大的磁盘空间

 

而Oracle在记录日志的方式上，采用了逻辑和物理相结合的方式。

 

查询当前Oracle实例的日志缓冲区大小 

select distinct lebsz as 日志缓冲区大小 from x$kccle   --必须以sys身份登录

　　插入知识介绍：x$表

x$表包含了特定实例的各方面信息，在不同版本的oracle版本中很可能是不一样的，时Oracle数据库的运行基础，包含当前的配置信息，连接实例的会话，以及丰富价值的性能信息。它并不是主流在数据库文件的永久或临时表

　　x$表仅仅驻留在内存中，当实例启动时，由Oracle应用程序动态创建，在内存中进行实时的维护。它们中的大多数至少需要装载或已经打开的数据库。x$表为sys用户所拥有，只读，x$表对于数据库来说很重要，所以不允许sysdba之外的用户直接访问，显示的授权也不被允许

 

 

 调整共享池

主要组件有：

库缓存（library cache)：主要缓存共享SQL和PL/SQL语句的相关信息

数据字典缓存(data dictionary cache):缓存数据字典表（dba_tables,dba_users）等信息，用于解释权限、表结构等

UGA（user globa area）:在共享服务器模式下，当没有配置大池时（large_pool_size=0）UGA会占用共享池

　在SGA中，首先应该考虑调整shared pool，调整shared pool时，应该首先集中在库缓存上。如果共享池很小时，会消耗很多CPU资源并引起竞争；如果共享池很大，会消耗很多内存资源，缓冲的内容较多，此时查找也会变得较缓慢

 

如：通过v$librarycache来查询当前实例的库缓存在调用阶段的“请求存取数”和“不命中数"

select sumj(pins) 请求存取数，sum(reloads) 不命中数 from v$librarycache;

 

一般来说，库高速缓冲总不命中数与总存取数之比应当接近0。当该比率如果接近或大于1%时，就应当立即采取措施减少这种不命中，通常有以下两种方法：

1.增加初始化参数shared_pool_size，提高库高速缓存中可用的内存数量，同时为了取得好的效果，还要增加初始化参数open_cursors的值，以提高对话允许的光标数。需要注意的是：为库缓存分配太多的内存可能引起调页或交换

2写等价的SQL语句，尽可能让SQL语句和PL/SQL块共享一个SQL区，以减少库缓存的不命中。SQL语句或PL/SQL块的文本每一个字符都必须等价，包括大小写和空格

 

v$rowcache查询数据字典高速缓存的活动状况

如：通过v$rowcache动态性能视图查询当前实例的数据字典高速缓存在调用阶段的“请求存取数”和“不命中数”

select sum(gets) 请求存取数，sum(getmisses) 不命中数 from v$rowcache;

 

一般来说，数据字典高速缓存总不命中数与总存取数之比应当接近0，改比率如果大于10%，甚至在应用过程中该比率还在增长，就应当立即通过增加shared_pool_size，以提高数据字典可用的内存数量，从而减少这种不命中。

 

v$sesstat v$statname查询Oracle收集对话信息使用的总内存统计

如：显示当前分配给所有会话的内存数

select sum(value)||'字节' 当前分配给所有会话的内存数
from v$sesstat,v$statname
where name='session uga memory' and v$sesstat.statistic#=v$statname.statistic#
/

本例：5273276字节

 

session uga memory用于显示分配给对话的内存字节数

 

如：显示曾经分配给所有会话的最大内存数

select sum(value)|| '字节' 曾经分配给所有会话的最大内存数
from v$sesstat,v$statname
where name='session uga memory max' and v$sesstat.statistic#=v$statname.statistic#
/

本例：9894388字节

 

 

调整数据库缓冲区

　　Oracle启动后不断地收集和统计数据存取的情况，并将其存放在v$sysstat中，

有：

db block gets:该统计值为数据请求的总数

consistent gets:该统计值为通过内存缓冲区存取既能满足的请求书

physical reads:该统计值为磁盘文件存取的总数

 

如：查询一段时间内v$sysstat表中的统计信息

select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads')
/

计算缓冲区高速缓存的命中率可以使用

1-physical reads/(db block gets+consistent gets)

 

如果命中率还不错，可以适当减少初始化参数db_block_buffers的值减少高速缓存的大小，从而可以将省出来的内存用于其他Oracle内存结构。如果命中率低于70%，就会造成性能下降，这时应该立即通过增加初始化参数db_block_buffers的值（最大为65535）来增加缓冲区高速缓存的大小

 

排序区优化

　　系统使用专用的内存区域进行数据排序，这部分空间就是排序区。在Oracle中，用户数据的排序可使用两个区域：一个是内存排序区；另一个是磁盘临时段，系统优先使用内存排序区进行排序，如果内存不够，Oracle会自动使用磁盘临时表空间进行排序，为了提高数据库排序的速度，建议尽量使用内存排序区，而不使用临时段。sort_area_size用来设置排序区大小。

 　　如果增大排序区则会提高大规模排序的性能，因为可以借此在查询处理期间在内存中执行排序。排序区的大小十分重要，因为某时用于每个连接的排序区仅有一个。此init.ora参数默认值通常为6-8个数据块的大小，此值通常主要用于OLTP操作，此时应该将其增大，以便执行决策支持操作、大批量操作，或者大量索引相关操作（如重建索引）。执行上述操作，应该调整下列init.ora参数：

sort_area_size=65535

sort_area_retained_size=65535

 

排序活动

　　排序是SQL语法中很小的方面，但却很重要，在Oracle的调整中，它常常被忽略。当使用create index,order by ,group by等语句时，Oracle数据库将自动执行排序的操作。通常，在以下情况Oracle会进行排序操作：

1.创建索引时

2.使用order by

3.使用group by

4.进行table join时，由于现有索引的不足而导致SQL优化器调用merge sort

 

　　当在Oracle实例中建立起一个session时，在内存中就会为session分配一个私有的排序区域。如果该连接是一个专用连接，那么就会根据init.ora中的sort_area_size参数的大小在内存中分配一个PGA.如果连接时通过多线程服务器建立的，那么排序的空间就在large_pool中分配。然而，对所有的session用做排序的内存量必须是一致的，不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须做出平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序的同时，对于那些并不需要进行很大排序的任务，就会出现浪费。当然，当排序的空间需求超出了sort_area_size所设置的大小时，将会在temp表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14000倍

　　私有排序区域大小是由init.ora中的sort_area_size参数决定的。每个排序所占用的大小由init.ora中的sort_area_retained_size参数决定的。当排序不能在分配的空间中完成时，就会使用磁盘排序方式，即在Oracle实例中的临时表空间中进行

　　磁盘排序的开销是很大的，主要有以下几个原因：首先，与内存排序相比，磁盘排序特别慢，而其磁盘排序会消耗临时表空间的资源。其次，Oracle必须分配缓冲池以保持临时表空间中的块。无论何时，内存排序都比磁盘排序好，磁盘排序将会使任务变慢，并且会影响Oracle实例当前任务的执行。此外，过多的磁盘排序会使空闲缓存的等待（free buffer waits)值变得很高，从而令其他任务的数据块由缓冲区移走。

 

 

专用模式下排序区的调整

　　PGA内部不同部分的分配

内存区	专有服务
会话内存	私有的
永久所在区域	PGA
select语句运行所在区域	PGA
dml/ddl运行所在区域	PGA

参数：

sort_area_size:

　　除非在共享模式下，一般不推荐设置该参数，而推荐使用pga_aggregate_target进行PGA内存自动管理。如果需要设置此参数，可以考虑设置在1-3MB之间

sort_area_retained_size:

　　它指定了在排序操作完成后，继续保留在uga(用户全局区)内存的最大大小，以维护内存中的排序，直到所有数据行被返回才释放（，sort_area_size在排序完成，数据行返回之前被释放）回uga(不会被操作系统回收)。

　　sort_area_retained_size在共享服务中是从SGA中分配的（因为此时uga是从sga中分配的）,在专用服务模式中是从pga中分配的，而sort_area_size无论在哪种模式下都从pga中分配

同样设置了pga_aggregate_target后，该参数无效

　　当连接处于空闲时，后台进程PMON每隔一段时间，就会测试用户的连接状况，如果连接已断开，PMON会清理现场，释放相关的资源。专用连接相当于一对一的连接，能够快速响应用户的请求。当然，连接时，首先要创建pga,参数pga_aggregate_target决定可以由所有服务器进程使用的内存的总量，参数workarea_size_policy决定采用手动管理还是自动管理

 

连接的方式：专用连接和共享连接

 

共享模式下排序区的调整

　　对于长事务或大事务，使用专用连接，可以有效提高系统的性能，减少用户等待和事务的排队，提高系统的利用率；对于超短事务和短事务、小事务，使用专用连接，而对于网站等可以使用共享连接。

在共享连接中，sort_area_size将在SGA的large_pool中分配

 

共享连接和长事务时背道而驰（长而大的OLTP）的

 

转载于:https://www.cnblogs.com/xcnblog3035/p/5284282.html