优化排序操作
??SQL内存使用:
???? 排序操作:sort, group-by ,rollup
???? hash-join
???? bitmap 操作(merge ,bitmap索引等)
?? 性能调优
??? 1.将数据放在缓存中.
??? 2.将一次扫描数据放在磁盘上
??? 3.将多次扫描的数据放在磁盘(尽量避免)
??PGA是一个私有内存区域,被服务器进程控制.PGA内存管理可以自动管理.
??oracle动态调整PGA内存的大小.大约为SGA的20%,最小为10MB.
??
??自动排序区管理
??动态调整SQL内存
??? PGA内存可用
??? SQL操作可用.
??? 系统负载
??动态排序区域管理
???不需要设置set*_area_size(不推荐使用):
???dba只需要设置pga_aggregate_target为初始化参数,取决分配oracle
???实例的内存大小,
????? 其中20%内存提供给其他进程使用.
????????? 80%内存给Oracle实例.
?????? OLTP系统:
????????? pga_aggregate_target=(total_mem*80%)*20%
?????DSS系统:
?????? pga_aggregate_target=(total_mem*80%)*50%
???WORKAREA_SIZE_POLICY=AUTO| MANUAL
?
???? PGA资源管理
?????? 监控PGA的视图
????? v$pgastat:pga的统计信息
?????over allocation count:统计PGA,过度申请(超过pga_aggregate_target)内存的次数.
??????????????????? total bytes processed:
?????cache hit percentage:命令率百分比?????????
??????? extra bytes read/written:不能允许理想状态时候,读写磁盘的总的字节数.
????? select * from v$pgastat where name ='cache hit percentage';
?????
????? v$process_memory:
????? v$sysstat:
?????
????? v$sql_workarea_histogram
????? v$sql_workarea_active
????? v$sql_workarea
????? v$tempseg_usage
????? v$sql_plan
????? v$sql

?????
??? 查看pga的建议信息
??? v$pga_target_advice
??? v$pga_target_advice_histogram
???
??? PGA 工作负载工作状态
??? select low_optimal_size/1024 as low_kb,
???????? (high_optimal_size+1)/1024 as high_kb,
????? round(100*optimal_executions/total_executions) as optiomal,
????? round(100*onepass_executions/total_executions) as onepass,
????? round(100*multipasses_executions/total_executions) as multipass
????? from v$sql_workarea_histogram
????? where total_executions!=0
????? order by low_kb;
?????
???2.查询工作区的执行情况分布图
?????? select low_optimal_size "Low" ,high_optimal_size "Hight" ,
????????????????????? optimal_executions "Opt",
????????????????????? onepass_executions "One" ,
????????????????????? multipasses_executions "Multi",
????????????????????? total_executions "total"
???????????????????? from v$sql_workarea_histogram;
?????
?????? 11g的查询总的内存分布情况
??? select optimal_count,round(optimal_count*100/total,2) optimal_perc,
?????????? onepass_count,round(onepass_count*100/total,2) onepass_perc,
?????multipasses_count,round(multipasses_count*100/total,2) multipass_perc
???? from
?????? ( select decode(sum(total_executions),0,1,sum(total_executions)) total,
?????????????????????? sum(optimal_executions) optimal_count,
?????????????????????? sum(onepass_executions) onepass_count,
?????????????????????? sum(multipasses_executions) multipasses_count
??????? from v$sql_workarea_histogram
????????? where low_optimal_size>64*1024????????
?????? )
?????? /
????
????v$sql_workarea_active:当前活动工作区
?????????? 查看排序操作的当预期大小大于实际大小,那么一次(onepass).
????预期大小小于实际大小,那么多次(multipass).
????select to_number(decode(sid,65535,null,sid)) sid,
????? operation_type oper,trunc(expected_size/1024) esize,
????? trunc(actual_mem_used/1024) mem,
????? trunc(max_mem_used/1024) " max mem"
????? from v$sql_workarea_active
????? order by 1,2;