oracle数据库性能调整与优化

Oracle9i数据库性能调整与优化V1.0在数据库成熟应用的时代，数据库的性能优化已经演变为一项相当严密的系统工程。

作为企业IT基础设施的核心部件之一，数据库并不是孤立的系统，它与网络、操作系统、存储等硬件系统紧密相连，这种与其他IT部件的多重连接特性决定了数据库性能优化是一门综合技术。

数据库性能优化的实现路径和IT系统管理架构越来越密不可分。

Oracle9i数据库在内部特性方面有着非常大增强，其中一个最令Oracle DBA兴奋莫过于可以动态设置全部Oracle SGA控制参数。

与8i不同是，原来都将初始化参数放到一个文本文件中，并且在数据库启动时候读取，Oracle9i却可以通过ALTER DATABASE 和ALTER SYSTEM命令复位全部Oracle参数。

在9i前，如果想对Oracle数据库处理模式作一些改变话，Oracle管理员必须关闭数据库并且重新设置INIT.ORA文件中参数，然后重新启动数据库。

对于白天使用OLTP 模式运作，晚上切换到数据仓库模式Oracle数据库来说，这种重新设置是经常做。

对于需要停止和重新启动Oracle数据库来修改参数来说，Oracle9i在这方面有明显加强，它令实现数据库连续可用目标变得更加简单。

定义：SGA：（System Global Area）是Oracle Instance的基本组成部分，在实例启动时分配。

是一组包含一个Oracle实例的数据和控制信息的共享内存结构。

主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。

它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

理论上SGA可占OS系统物理内存的1/2——1/3。

SGA几个很重要的特性：1、SGA的构成——数据和控制信息，我们下面会详细介绍；2、SGA是共享的，即当有多个用户同时登录了这个实例，SGA中的信息可以被它们同时访问（当涉及到互斥的问题时，由latch和enquence控制）；3、一个SGA只服务于一个实例，也就是说，当一台机器上有多个实例运行时，每个实例都有一个自己的SGA尽管SGA来自于OS的共享内存区，但实例之间不能相互访问对方的SGA区。

PGA：进程全局区(PGA)即可以理解为Process Global Area,也可以理解为Program Global Area.它的内存段是在进程私有区(Process Private Memory)而不是在共享区(Shared Memory).它是个全局区意味着它包含了所有代码有可能进入的全局变量和数据结构,但是它是不被所有进程共享的.每个Oracle的服务器进程都包含有属于自己的PGA,它只包含了本进程的相关特定信息.PGA中的结构不需要由latches来保护,因为其它的进程是不能进入到这里面来访问的。

PGA包括Fixed PGA和Variable PGA两个区域。

1 数据库调整概述oracle的内存可以按照共享和私有的角度分为系统全局区和进程全局区，也就是SGA和PGA(process global area or private global area)。

对于SGA区域内的内存来说，是共享的全局的，在UNIX上，必须为oracle设置共享内存段（可以是一个或者多个），因为oracle在UNIX上是多进程；而在WINDOWS上oracle是单进程（多个线程），所以不用设置共享内存段。

PGA是属于进程（线程）私有的区域。

在oracle使用共享服务器模式下（MTS）,PGA中的一部分，也就是UGA会被放入共享内存large_pool_size 中。

数据库性能优化包括如下几个部分：1调整数据结构的设计这一部分在开发信息系统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

2. 调整应用程序结构设计这一部分也是在开发信息系统之前完成的。

程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。

不同的应用程序体系结构要求的数据库资源3. 调整数据库SQL语句应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。

Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁管理器（Row-Level Manager）来调整优化SQL语句。

4. 调整服务器内存分配内存分配是在信息系统运行过程中优化配置的。

数据库管理员根据数据库的运行状况不仅可以调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小，而且还可以调整程序全局区（PGA区）的大小。

5. 调整硬盘I/O 这一步是在信息系统开发之前完成的。

数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O 负载均衡。

6. 调整操作系统参数例如：运行在Unix操作系统上的 Oracle数据库，可以调整 Unix数据缓冲区的大小、每个进程所能使用的内存大小等参数。

1.1 调整目标深刻理解oracle初始化参数的含义是DBA必不可少的功课，但不以把数据库参数的调整作为提高数据库性能的救命稻草，不适合的参数配置必将带来性能上的下降，甚至引起数据的丢失，也不要以使用隐藏参数为荣。

做事也不要贪多，数据库性能达到最后才是正理。

没有任何公式可以满足SGA调整的需要，OLAP应用与OLTP应用初始化参数的调整是有很大区别的，参数经过多次调整，才能达到和谐的应用效果。

在32位操作系统中，SGA有1.7GB的限制，如果想在32位操作系统上突破1.7GB的限制，必需使用特殊的手段。

如果有足够的内存，可不可以将SGA设置的足够大？答案是不可以。

那样数据库几乎全部缓冲到SGA中，倒致系统很慢。

运行良好的数据库，它的数据量与SGA有一定的比例关系，如果在数据量一定的情况下，SGA大超过了那个比例，系统的性能不会的很大的提升。

下面我们介绍一下服务器性能调优之服务器内存分配。

1.2 调整过程和步骤点开始——程序找到如下目录弹出如下图界面点击下一步择创建针对事务处理（transaction processing）进行优化的数据库模板。

如下图选择好后，点击下一步，出现下图选择共享服务器模式，具体事项，请看下图在上图中选择内存项，选择典型数据库类型，后后点击显示内存分配情况按钮，出现下图界面，界面中显示了服务器最佳配置。

查看后，点击完成，创建数据库完成。

完成后启动数据库，查看数据库的使用情况。

如下图对于SGA部分，我们通过sqlplus中查询可以看到：SQL> select * from v$sga;NAME VALUE-------------------- ----------Fixed Size 454032Variable Size 109051904Database Buffers 385875968Redo Buffers 667648Fixed Sizeoracle 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了SGA各部分组件的信息，可以看作引导建立SGA的区域。

Variable Size包含了shared_pool_size、java_pool_size、large_pool_size等内存设置和用于管理数据缓冲区等内存结构的hash table、块头信息(比如x$bh消耗内存)等Database Buffers指数据缓冲区，在8i中包含default pool、buffer_pool_keep、buffer_pool_recycle 三部分内存。

在9i中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。

这里要注意在8i中三部分内存总和为db_block_buffers*db_block_size。

Redo Buffers指日志缓冲区，log_buffer。

在这里要额外说明一点的是，对于v$parameter、v$sgastat、v$sga查询值可能不一样。

v$parameter里面的值，是指用户在初始化参数文件里面设置的值，v$sgastat是oracle实际分配的日志缓冲区大小（因为缓冲区的分配值实际上是离散的，也不是以block为最小单位进行分配的），v$sga里面查询的值，是在oracle 分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大小大约是11k(不同环境可能不一样)。

参考如下内容SQL> select substr(name,1,10) name,substr(value,1,10) value from v$parameter where name = 'log_buffer';NAME VALUE------------------- --------------------log_buffer 524288SQL> select * from v$sgastat ;POOL NAME BYTES----------- -------------------fixed_sga 454032buffer_cache 385875968log_buffer 656384SQL> select * from v$sga;NAME VALUE-------------------- ----------Fixed Size 454032Variable Size 109051904Database Buffers 385875968Redo Buffers 667648UGA主要包含以下部分内存设置SQL> show parameters area_sizeNAME TYPE VALUE------------------------------------ ----------- -----------bitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608hash_area_size integer 1048576sort_area_size integer 524288workarea_size_policy string AUTO在这部分内存中我们最关注的通常是sort_area_size，这是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。