监控SQL使用的内存
???select sql_text,sum(onepass_executions) onepass_cnt,
?????? sum(multipasses_executions) multipass_cnt
???from v$sql s,v$sql_workarea wa
???where s.address=wa.address
???group by sql_text
???having sum(onepass_executions+multipasses_executions)>0;
???
???select to_number(decode(sid,65535,NULL,sid)) sid,
????? operation_type operation,
????? trunc(expected_size/1024) esize,
????? trunc(actual_mem_used/1024) mem,
????? trunc(max_mem_used/1024) maxmem,
????? number_passes?????????? pass,
????? trunc(tempseg_size/1024)? tsize
???? from v$sql_workarea_active
???? order by 1,2;
????
???? select operation ,options,object_name name,
???? trunc(bytes/1024/1024) "Input(MB)",
????? trunc(last_memory_used/1024) last_mem,
????? trunc(estimated_optimal_size/1024) optimal_mem,
????? trunc(estimated_onepass_size/1024) onepass_mem,
????? decode(optimal_executions,null,null,
????????????? optimal_executions || '/' ||onepass_executions
??????? ||'/'|| multipasses_executions ) "0/1/M"
??????? from v$sql_plan p, v$sql_workarea w
??????? where p.address=w.address(+)
????????? and p.hashvalue=w.hash_value(+)
?????? and p.id=w.operation_id(+)
?????? and p.address='88BB460C'
?????? and p.hashvalue=3738161960;
??????
????? v$pga_target_advice:
???????? pga区大小和pga命中率的关系
???????? select round(pga_target_for_estimate/1024/1024) as target_mb,
????????????? estd_pga_cache_hit_percentage as cache_hit_percent,
??????? estd_overalloc_count
??????? from v$pga_target_advice
??????? order by target_mb;
?????这个命中率至少高于60%,
?????PGA调优
?????1.PGA_AGGREGATE_TARGET至少375MB.
?????2.分析PGA和命中率的关系
????? v$pgastat
????? statistics_level必须设置级别为typical.
????? select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage
???????? from ( select name ,value cnt, (sum(value) over()) total
????? from v$sysstat
????? where name like 'workarea exec%');
????? OEM PGA区的信息.
????v$PGA_TARGET_ADVICE_HISTORGRAM显示V$SQL_WORKAREA_HISTOGRAM.
????排序所需的空间:
??????? 在专用服务器模式,为PGA中UGA的一部分.
?????在共享服务器模式,为共享池中UGA的一部分.
????Oracle 不推荐使用SORT_AREA_SIZE,建议使用pga_aggregate_target
????????? alter session ....deferred;
????alter system ....deferred;
????
????sort_area_retained_size:排序结束之后,UGA保留的数值.
???排序的区域的参数
????? 一个执行可能包含多个排序.
????? 一个单个服务器需要一个排序.
???????? 需要SORT_AREA_SIZE字节大小.
????? 需要join sort的最小区域sort_area_retained_size.
????每一个并行查询服务需要sort_area_size大小.
???排序调优:
?????? 1.使用自动排序区管理
????2.尽可能的避免排序.
????2.尽量排序时候交换区和page的频繁交换.
???
???临时表空间:
????临时表空间只有一个sort segment;
????不用更新数据字典,更新基于内存的.
????在第一次使用排序,开始申请表空间.
????临时表空间的exetents不需要在数据字典管理.
????在SGA中有一个Sort exetent pool(SEP0.
????
???创建临时表空间
???create temporary tablespace temp tempfile
???? '$ORACLE_HOME/ORADATA/u01/temp.dbf' size 200m;
????? 创建临时表空间,只是创建一个临时文件的头部和尾部,在使用的时
???候才开始真正的分配表空间.在创建之后df -k文件系统的大小不变.
???
???需要排序的情形
????索引需要排序.
????并行插入表.
????order by 或者group by.
????distinct去除重复.
????union,intersect,minus操作.
????sort-merge join.
????analyze命令分析表.
???
???查看系统排序的情况(内存,磁盘,行数)
????? select * from v$sysstat where name like '%sort%';
???
???避免排序方法:
????? 1.create indexes 使用nosort;
????? 2.使用union all替代union.
????? 3.使用index访问表的joins
????? 4.在排序列上创建索引
????? 5.针对large objects使用estimate而不是compute
?????
???排序的性能工具
????服务器进程排序在UGA中排序在内存,和临时表空间排序,使用情况的为:v$sort_usage;
????临时表空间相关的排序临时段:v$sort_segment;
????磁盘上排序情况看v$sysstat中sort字段.
???诊断和指导线
????在OLTP系统中磁盘排序占内存排序比率应该小于5%;如果高于5%需要增加SORT_AREA_SIZE/PGA_AGGREGATE_TARGET的值.
????select d.value " DISK" ,m.value "MEM",
?????? (d.value/m.value)*100 "Ratio"
????FROM v$sysstat m ,v$sysstat d
????WHERE m.name='sorts(memory)'
????and d.name ='sorts(disk)';
???
???监控临时表空间
?????? 默认存储参数应用在排序segment.
????sort