oracle 排序性能优化,Oracle 性能调优学习札记(九)- 优化排序操作A

Oracle 性能调优学习笔记(九)-- 优化排序操作A

优化排序操作

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;