Oracle性能优化05.docxVIP

29.Oracle 数据库优化：PGA调整与排序优化一、PGA大小设置参数如前文所述，PGA_AGGREGATE_TARGET是最值得关注的参数，该参数限制全局PGA分配和私有工作区内存分配。单个SQL操作内存存在如下限制。（适用于9I及10G R1）对串行操作而言，单个SQL操作能使用的PGA内存按以下原则分配：MIN(5%*PGA_AGGREGATE_TARGET,100MB)，5%*PGA_AGGREGATE_TARGET实际由隐式参数_smm_max_size决定，该参数限制自动工作区模式下最大的工作区使用。对并行操作而言，能够使用的PGA内存按照以下原则分配： 30%PGA_AGGREGATE_TARGET/DOP (DOP=Degree Of Parallelism，并行度)这个算式在10G R2中稍有不同对于串行操作而言P_A_T=500MB，则_smm_max_size=20%*P_A_T。P_A_T在500MB和1000MB之间，_smm_max_size=100MBP_A_T在1001MB和2560MB（2.5GB）之间，_smm_max_size=10%*P_A_TP_A_T2560MB则_smm_max_size=262,060(~0.25GB)。对于并行操作则是50%PGA_AGGREGATE_TARGET/DOP如果DOP不大于5则_smm_max_size生效，超过5则_smm_px_max_size生效。对于该参数的设置，Oracle提供这样一个建议方案（通常是一个预设方案，当系统运行一段时间之后就可以通过建议视图进行调整了）：对于OLTP系统，PGA_AGGREGATE_TARGET=(Total Physical Memory*80%)*20%对于DSS系统，PGA_AGGREGATE_TARGET=(Total Physical Memory*80%)*50%这个建议可以通过V$process来观察是否合理，联合v$process_memory可查询内存消耗在哪里。二、如何评价是否合理通过v$sysstat可以查询PGA的性能，这是因为SQL在工作区内以3种方式执行：Optimal:优化方式，处理在内存中完成Onepass：大部分在内存中完成Multipass：大量磁盘交互。这三种方式以第一种效果最好，我们的预期目标是：Workarea execution-optimal=90%Workarea execution-multipass=0%性能统计数据之SQL工作区是否合适：SelectName,Value,100 * (Value / Decode((SelectSum(Value)From V$sysstatWhereNameLikeworkarea executions%),0,Null, (SelectSum(Value)From V$sysstatWhereNameLikeworkarea executions%))) PctFrom V$sysstatWhereNameLikeworkarea executions%;三、排序区的大小与SQL调优在等待事件中，有一种叫direct path read/write（直接路径读写），它通常发生在Oracle直接读数据到进程PGA时，这个读取不需要经过SGA，该等待事件被归于User I/O一类。这类读取通常在以下情况下被使用：●磁盘排序IO操作●并行查询从属进程●预读操作第一种最为常见，在DSS系统中，存在大量的direct path read是很正常的，但是在OLTP系统中，通常显著的直接路径读都意味着系统应用存在问题，从而导致大量的磁盘排序读取操作。直接路径写(direct path write)通常发生在Oracle直接从PGA写数据到数据文件或临时文件，这个写操作可以绕过SGA。这类写入操作通常在以下情况下被使用：●直接路径加载●并行DML操作●磁盘排序●对未缓存的“LOB”段的写入，随后会记录为direct path write(lob)等待。由于常见的直接路径写多因为磁盘序导致，对于这一写入待待，我们应该找到I/O操作最频繁的数据文件（很可能是临时文件），分散负载，加快其写入操作。通常有为不同用户分配不同临时表空间，使用多个临时文件，写入不同磁盘或裸设备以降低竞争，对8i数据库应考虑使用本地管理的临时表空间，9i之前可适当增加sort_area_size的大小，9i后可适当增大pga_aggregate_target。30.Oracle 数据库优化：PGA与排序区一、PGA的层次关系PGA与CGA、UGA的关系可以这样描述：无论UGA存在于PGA还是SGA，CGA都