ORACLE数据库分析型应用的调优策略

1.调优的目标1.1 分析型应用的特点分析型应用、批量处理应用、数据仓库应用说的都是相似的一类应用，它们与联机类应用（OLTP）相比，有如下的区别：1.2 分析型应用的调优目标分析型应用系统的调优目标就是在有限的资源环境下，在要求的时间窗口内，能够正确、稳定、及时的完成批量处理任务和业务目标。

一个经过优化的分析型应用系统应该具有以下特征：1）能够充分调动利用系统资源，使系统资源的利用率稳定在较高的水平。

如果系统资源利用率始终较低，可能是处理逻辑的某处有瓶颈，需要找出结症，采取必要的手段，如改单次循环操作为批量操作、改串行操作为并行操作，甚至调整处理逻辑，以提高系统利用率。

2）系统的最大等待时间应该在I/O操作上，且各种资源的使用率比较均衡。

如果最大等待时间消耗在其它资源上，而I/O相对空闲，一般需要调整应用，加大对I/O资源的利用。

3）每次完成任务的时间窗口比较稳定，且能随着数据量的增加呈线性变化，而不是报错。

2.调优的层次和方向2.1 系统级调优即对系统资源一级的调优，可由管理DBA或厂商支持完成，通过分析应用运行中的资源使用状况，找出ORACLE资源配置方面的问题，一般是通过调整系统和数据库参数达到优化，如加大OS和ORACLE每次读取的数据块参数等；也能够通过分析ORACLE的等待事件等各种统计信息，找出问题SQL，提出SQL优化建议。

系统级调优偶尔能达到较好的效果，但经过初次诊断优化之后，如果仍有性能问题，很难再进一步优化。

这一级调优是纯技术的，也是最表层的，完全不需要理解业务。

2.2 语句级调优当经过最初的系统级诊断找出问题SQL之后，可以在SQL级对应用进行调优。

这主要也是利用SQL和PLSQL编程技术，对个别语句进行优化改造，并不动及应用的整体处理逻辑，因此也属于技术级别的调优，不一定要求熟悉业务，通常由管理DBA来完成。

这种调优对个别语句的优化效果通常较大，但不一定能够突破应用设计上的瓶颈（如果有），因此如果应用设计逻辑有问题，这一级调优也很难达到最终目标。

2.3 应用设计级调化一个应用系统经过分析、设计、实现，达到业务目标，最后的系统性能相差巨大，最有可能就是应用程序设计的问题。

这一层的调优是对应用处理逻辑进行重新优化设计，需要完全业务需求和加工逻辑。

如果系统性能问题的确出现在设计层，经过这一层优化之后，一般能达到戏剧性的性能提高，但难度和限制条件也较大。

2.4 数据库设计级调优一般数据库设计人员都比较熟悉关系型数据库，纯性能问题一般不大，但数据库设计（包括逻辑模型设计和物理存储设计等）如果出自非常业余的人士，性能问题也可能出现这一层。

这一层的调优在技术是可能的，但限制条件和涉及面最大，一般难以实现。

另外在创建数据库时，要尤其注意DB_BLOCK_SIZE这个参数，默认值是8K，对分析型应用应该设为16K或32K，这个参数是数据库的最基础参数之一，在数据库创建之后不能再更改。

2.5 通用的关键几招数据库逻辑模型设计在使用关系结构的基础上以清晰易懂、访问流畅为关键。

物理模型设计以结构简洁、含义明确、存取快速、高效利用资源为目标。

本招对现有系统调优意义不大。

减少使用游标循环，将游标中的处理逻辑提取到SQL语句层通过使用多子查询关联等技术批量完成处理；如果用一条SQL语句完成处理比较困难，可以考虑分成几步，使用临时表存放过程数据，最后生成目标数据，再清空临时表。

这种方法能够充分利用和发挥资源优势，通过增大数据吞吐量而提高速度。

如果资源有限，满足不了一个大SQL语句的运行，有时还需要将它拆成几个较小的语句分步完成。

如果处理逻辑复杂而必须使用游标，尽量将循环中对表的访问转为在游标定义层通过表连接进行访问，以极大程序的减少对表的访问次数。

同理，要尽量设法将循环中调用的函数（特别是函数中又访问表）提取到游标定义层完成其功能。

在数据量巨大的分析型数据库中，UPDATE速度难以难受，要尽量设法UPDATE语句转为使用INSERT语句完成其处理目的。

结合数据分区设计并行处理，充分利用资源获得效率。

使用NOLOGGING操作，减少写日志文件的压力。

使用BULK批操作（如批量FETCH、INSERT等）替换单条记录操作。

调优一个长时间没反应的存储过程，先单独分析调试其中的SQL，找出瓶颈，重点优化。

同理，对一个很大很慢的SQL，先分别调试其中的子句，找出结症，重点突破。

对具体的SQL调优，尝试多种SQL方法，反复分析其执行计划，注重实验，以实际结果为准。

3.调优技术概述从上一章的内容可以看出，对于一个已经实现的应用系统，调优的方向主要是前三个层次，这三个方向的调优通常是综合运用的，如在系统级诊断出有性能问题的程序或具体语句，首先分析是否能够从语句级进行调优，尔后可能需要考虑优化程序实现逻辑。

本章对常用的ORACLE调优技术做一个概述。

3.1 内存配置和使用合理设置和高效使用ORACLE缓存能大幅改善数据库性能，物理I/O不仅速度大大慢于内存访问，而且由于设备驱动的路径长度和操作系统的事件调度会增加CPU负载。

调优目标就是尽可能减小I/O，通过尽可能把数据放入内存或尽可能少量的访问数据而尽可能减少物理I/O。

ORACLE建议使用SGA_TARGET和PGA_AGGREGATE_TARGET两个初始化参数开启自动内存配置。

然而也可以手工调整内存池。

ORACLE缓存主要包括SGA（buffer cache、shared pool、large pool、java pool、streams pool、log buffer等）和PGA。

其中log_buffer 是静态参数，其它可以通过alter system命令动态调整。

3.1.1配置使用ASMM（自动共享内存管理）ORACLE建议对数据库配置使用ASMM。

与使用AUM（自动回退管理）效果类似，使用ASMM简化DBA对SGA的管理，DBA一般只需要定义好期望使用的总空间，然后把SGA的分配管理工作基本留给ORACLE自已完成。

以前DBA负责手工分配SGA的各组件内存，检查其指标的利用效率，再进行人工调整，是一项很烦琐的工作。

要使用ASMM，需要设置SGA_TARGET初始参数为非零值，并且设置STATISTICS_LEVEL初始参数为TYPICAL或ALL（缺省值是TYPICAL）；SGA_TARGET参数设为希望使用的内存量。

ASMM 会根据系统负载，按需自动调整以下内存池以合理分配使用内存：DATA BUFFER CACHE（DEFAULT POOL）、SHARED POOL，LARGE POOL，JA V A POOL，STREAMS POOL。

如果这些内存池设置了非零值，这些值被ASMM使用为最小值。

SGA_TARGET是一个动态可调的参数，可以通过查询V$SGA_TARGET_ADVICE视图获得建议值然后使用ALTER SYSTEM命令进行更改。

可以被设置为小于等于SGA_MAX_SIZE参数值。

如果SGA_TARGET被动态设置为零，ASMM将被禁用，以上各内存池的大小就固定使用其当前值；如果需要，还可以手工调整DB_CACHE_SIZE，SHARED_POOL_SIZE，LARGE_POOL_SIZE，JA V A_POOL_SIZE和STREAMS_POOL_SIZE初始参数。

以下的内存池只能手工调整，不受ASMM的影响：Log buffer，Other buffer caches (such as KEEP，RECYCLE，and other non-default block size)、Fixed SGA and other internal allocations。

这些内存池从SGA_TARGET总量中进行分配。

3.1.2设置共享内存主要参数SGA_MAX_SIZE参数：SGA_MAX_SIZE初始化参数设置ORACLE实例生命周期内SGA最大值。

如果此参数没有设置，缺省为SGA中各池空间的合计。

SGA_MAX_SIZE设置语法：SGA_MAX_SIZE = integer [K|M|G] 此参数不能动态更改，只能在参数文件中使用ALTER SYSTEM SET…SCOPE=SPFILE语法设置。

DB_CACHE_SIZE参数：对很多类型的操作，Oracle使用buffer cache来存储从Disk读出的数据，仅对一些特殊操作（如sorting and parallel reads），Oracle会绕过使用buffer cache。

为了高效使用buffer cache，应确保最经常执行的且执行很多buffer gets操作的SQL语句得到重点优化。

对于一个新实例，很难知道其正确的Buffer Cache值。

一般是，先设置一个估计值，然后在实例上运行有代表性的工作负载，再借助V$DB_CACHE_ADVICE视图和Buffer cache hit ratio指标找到一个合理值。

而如果使用ASMM，可以省去这个繁琐工作。

如果设置了SGA_TARGET，此参数默认为0，如果设置了此参数，被SGA_TARGET使用为最小值。

如果没有设置SGA_TARGET，此参数默认为48MB和4M*CPUs*granule size其中较大者。

注：SGA中的动态内存组件以granule为单位分配，在大多数平台上，如果SGA小于等于128MB，granule大小是4MB，否则是16MB。

V$SGA_DYNAMIC_COMPONENTS视图中显示了各内存组件granule大小。

SHARED_POOL_SIZE参数：SHARED POOL主要包括LIBRARY CACHE和DICTIONARY CACHE。

发生在LIBRARY CACHE或DICTIONARY CACHE的CACHE MISS代价要比发生在BUFFER CACHE的代价高得多。

因此SHARED POOL的设置要确保最近使用的数据都能被CACHE。

在使用ASMM后，也可以省去调整此参数的繁琐工作。

此参数在ORACLE9I缺省值为8MB（32位系统）或64MB（64位系统）。

在10G缺省值依赖于SGA_TARGET参数的设置，如果设置了SGA_TARGET，SHARED_POOL_SIZE缺省为0，由ORACLE 内部自动调整，如果指定了SHARED_POOL_SIZE，此值作为SHARED POOL的最小值；如果没有设置SGA_TARGET，SHARED_POOL_SIZ缺省为32MB（32位系统）或84MB（64位系统）。

LARGE_POOL_SIZE参数：LARGE_POOL_SIZE用于共享服务器下的SESSION内存、并行MESSAGE BUFFER（仅当参数PARALLEL_AUTOMATIC_TUNING 设置为TRUE）和BACKUP进程的DISK I/O BUFFER。