02-08 10g PGA&排序调整(35 页)

Oracle10g排序区与PGA调整赵元杰中程在线(北京)科技有限公司2009.82020年1月20日Oracle数据库优化设计2/35内容提要排序概念排序有关的参数设置OraclePGA概念OraclePGA调整监视PGA使用排序与临时表空间监控排序与分析参考资料2020年1月20日Oracle数据库优化设计3/35关于排序操作Oracle许多SQL操作都会产生排序处理：处理OrderBY外，创建索引等都要排序；操作说明ORDERBY查询中的排序，多个列排序会使性能严重下降GROUPBY分组操作SELECTDISTINCT取不同的列值。排除相同就要排序才能得到UNION联合，将两个操作结果拼接在一起INTERSECT两个以上结果的交（即相同部分）MINUS两个结果的相减(将相同的去掉)ANALYZE分析表的数据量或分析索引的数据量CREATEINDEX创建索引，在创建索引时要对表进行扫描并排列才能将每个行的ROWID些到索引中连接两个没有索引的表连接两个没有索引的表也要产生排序操作2020年1月20日Oracle数据库优化设计4/35关于排序操作与内存排序操作数据-临时段：主要是在内存里操作；少量在临时表空间(磁盘)中进行；…selecta.valuedisksort,b.valuememorysort,round(a.value/(b.value+a.value)*100,2)disksortpercentagefromV$SYSSTATa,V$SYSSTATbwherea.name='sort(disk)'andb.name='sort(memory)';disksortmemorysortdisksortpercentage-------------------------------------------------------------1135786402020年1月20日Oracle数据库优化设计5/35排序在内存操作有关参数SORT_AREA_SIZE参数：每个会话可用于排序操作的内存数；较大的值防止交换到磁盘；值(以字节):至少=2Oracle块；默认与OS有关,最大无定义。SORT_AREA_RETAINED_SIZE参数：参数以字节为单位，表示在一个排序运行完毕后保留的用户全局区（UGA）内存量的最大值；最后一行从排序空间中提取后，该内存将被释放回UGA，而不是释放给操作系统；SORT_AREA_RETAINED_SIZE=SORT_AREA_SIZE；当SORT_AREA_SIZE参数调为较大值时，建议不要将SORT_AREA_RETAINED_SIZE参数也调大。2020年1月20日Oracle数据库优化设计6/35修改内存排序有关参数排序参数的修改：可用ALTERSYSTEM修改影响整个实例；用ALTERSESSION修改只影响到该会话；…SQLselectname,value,ISSES_MODIFIABLEfromv$parameterwherename='sort_area_size';NAMEVALUEISSES--------------------------------------------------------------------------------sort_area_size65536TRUE...SQLSQLALTERSESSIONSETSORT_AREA_SIZE=20m;2020年1月20日Oracle数据库优化设计7/35监视内存操作排序操作可从数据字典中查询：从V$SYSSTAT动态数据字典中查到磁盘与内存排序的比例：SELECTmem.value/(disk.value+mem.value)In-memorySortRatioFROMv$sysstatmem,v$sysstatdiskWHEREmem.name='sorts(memory)'ANDdisk.name='sorts(disk)';In-memorySortRatio--------------------.992332020年1月20日Oracle数据库优化设计8/35内容提要排序概念排序有关的参数设置OraclePGA概念OraclePGA调整监视PGA使用排序与临时表空间监控排序与分析参考资料现在2020年1月20日Oracle数据库优化设计9/35PGA概念PGA概念：程序全局区(processglobalarea或privateglobalarea)；PGA是进程专用的内存区。这是Oracle专用或共享服务器需要的一组独立于会话的变量；PGA包含一个服务进程数据和控制信息的内存区域。Oracle在一个服务进程启动是创建(非共享)。一个Oracle进程拥有一个PGA内存区。PGA存储：SORT操作；HASH数据；PL/SQL变量与光标内存。2020年1月20日Oracle数据库优化设计10/35SGAPGA概念-Oracle内存模式P1P2P3P4PGASORTHASHPGABIGSORTcursorspl/sqlvar.cursorspl/sqlvar.PGASortcursorspl/sqlvar.PGAHashcursorspl/sqlvar.2020年1月20日Oracle数据库优化设计11/35PGA概念-虚拟内存映射P1P2SGA32/64bitaddressingspacePGASortcursorspl/sqlvar.PGAHashcursorspl/sqlvar.0gb1gb2gb3gb4gb2020年1月20日Oracle数据库优化设计12/35Oracle9i开始:WORKAREA_SIZE_POLICY=MANUALUseSORT_AREA_SIZEWORKAREA_SIZE_POLICY=AUTOUsePGA_AGGREGATE_TARGETPGA调整2020年1月20日Oracle数据库优化设计13/35PGA调整当参数workarea_size_policy=AUTO:9i或更高版本，如果Workarea_size_policy=auto，则所有的会话共用一块内存，该内存在参数Pga_aggregate_target设置。pga_aggregate_target指定所有会话可用内存排序大小Oracle确定为每个会话分配可用的内存PGA自动问题：当运行在自动PGA管理模式下时，对于所有的专有会话的工作区的大小都变为自动，则*_AREA_SIZE参数的就被省略；任何一个给定的时间内，可用的PGA内存的总数自动来源于PGA_AGGREGATE_TARGET初始参数值；PGA_AGGREGATE_TARGET参数值减去其它系统部件（如会话分配的PGA内存）所分配的PGA内存的总数，作为PGA内存的结果，就分配给个别活动的工作区。2020年1月20日Oracle数据库优化设计14/35PGA设置问题设置PGA_AGGREGATE_TARGET初始值：PGA_AGGREGATE_TARGET的初始参数（比如100000KB，2500MB或50GB）应该基于Oracle实例可用的内存总数。这些值可以调整或动态修改；PGA设置问题：假设Oracle实例是在物理内存为4GB的内存下配置；物理内存的一部分应该保留给操作系统，而另一部分分配给Oracle系统。你可以决定将80%分配给Oracle实例（即3.2GB）。然后将这块内存分为SGA和PGA两部分。2020年1月20日Oracle数据库优化设计15/35PGA设置问题设置PGA_AGGREGATE_TARGET经验：在物理内存为4GB的内存下：对于OLTP系统，典型的PGA内存可分配总内存的20%；留出80%给SGA区；对于大的DSS系统，要求强烈的，则分配70%给PGA(即2.2GB给PGA)；PGA设置算法：OLTP:PGA_AGGREGATE_TARGET=(total_mem*80%)*20%DSS:PGA_AGGREGATE_TARGET=(total_mem*80%)*50%这个例子中，TOTAL_MEM=4GB,对于DSS，你可设置1600M给PGA；而对于OLTP，你可以设置655MB给PGA。2020年1月20日Oracle数据库优化设计16/35监视PGA使用-查询统计查询PGA_AGGREGATE_TARGET使用情况：V$PGASTATV$PROCESSV$SQL_WORKAREA_HISTOGRAMV$SQL_WORKAREA_ACTIVEV$SQL_WORKAREA图形界面EM：点击选择“性能”进入：2020年1月20日Oracle数据库优化设计17/35监视PGA使用-查询统计查询V$PGASTAT视图：SQLselect*fromv$pgastat;--结果：SQLSELECT*FROMV$PGASTAT;NAMEVALUEUNIT----------------------------------------------------------aggregatePGAtargetparameter96468992bytesaggregatePGAautotarget60926976bytesglobalmemorybound19293184bytestotalPGAinuse28768256bytestotalPGAallocated59564032bytesmaximumPGAallocated82093056bytestotalfreeablePGAmemory0bytesprocesscount23maxprocessescount35PGAmemoryfreedbacktoOS0bytestotalPGAusedforautoworkareas0bytesmaximumPGAusedforautoworkareas2024448bytestotalPGAusedformanualworkareas0bytesmaximumPGAusedformanualworkareas0bytesoverallocationcount0bytesprocessed186574848bytesextrabytesread/written0bytescachehitpercentage100percentrecomputecount(total)8349已选择19行。2020年1月20日Oracle数据库优化设计18/35监视PGA使用-查询详细查询V$PGASTAT视图：加条件查询：SQLselectname,round(value/1024/1024,1)asMbfromv$pgastatwhereunit='bytes';NAMEMB-----------------------------------------------aggregatePGAtargetparameter2000aggregatePGAautotarget1779.7globalmemorybound100totalPGAinuse22.5totalPGAallocated126.7maximumPGAallocated538.7totalfreeablePGAmemory4PGAmemoryfreedbacktoOS2065058.6totalPGAusedforautoworkareas0maximumPGAusedforautoworkareas106.3totalPGAusedformanualworkareas0maximumPGAusedformanualworkareas0.5bytesprocessed507256.6extrabytesread/writt