Oracle系统调优详解

Oracle系统调优在Oracle数据库系统中，起到调节作用的参数叫初始化参数，在Oracle 8i及以前的版本中，这些初始化参数记录在INITsid.ora文件中；而Oracle 9i/10g/11g中将这些参数记录在SPFILEsid.ora二进制文件中基本参数：一组可调整的参数，如control_files,db_block_size,process高级参数：一组精细调整的参数,如shared_servers在进行系统优化时，可以使用alter system(整个数据库实例) /alter session（当前会话）可以使用show parameter+参数名查看系统全局区（SGA）优化，虽然11g增加了内存的自动调整调整内存分配一般内存的调整在应用程序和SQL语句做完调整后进行的。

另外，建议用户在调整I/O 前先调整内存分配。

调整日志缓冲区如何记录日志文件呢？方式一：使用逻辑的记录方式，也就是用描述性的语句记录整个变化过程。

如，对某个update，只记录delete旧值和insert新值优点：节省空间确定：一旦需要进行恢复就会非常消耗资源方式二：使用物理记录方式，也就是说，把每个数据块的改变前的镜像和改变后的镜像都记录下来优点：恢复速度快，缺点：占用很大的磁盘空间而Oracle在记录日志的方式上，采用了逻辑和物理相结合的方式。

查询当前Oracle实例的日志缓冲区大小Sys sysdba角色登录：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 sum(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$statnamewhere name='session uga memory' and v$sesstat.statistic#=v$statname.statistic#;session uga memory用于显示分配给对话的内存字节数如：显示曾经分配给所有会话的最大内存数select sum(value)|| '字节' 曾经分配给所有会话的最大内存数from v$sesstat,v$statnamewhere name='session uga memory max' and v$sesstat.statistic#=v$statname.statistic#;调整数据库缓冲区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=65535sort_area_retained_size=65535排序活动排序是SQL语法中很小的方面，但却很重要，在Oracle的调整中，它常常被忽略。

当使用create index,order by ,group by等语句时，Oracle数据库将自动执行排序的操作。

通常，在以下情况Oracle会进行排序操作：1.创建索引时2.使用order by3.使用group by4.进行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)值变得很高，从而令其他任务的数据块由缓冲区移走。

专用模式下排序区的调整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会清理现场，释放相关的资源。