Objective
1.List the operations that use temporary space
2.Create and monitor temporary tablespaces
3.Identify actions that use the temporary tablespace
4.Describe and differentiate disk sorts and memory sorts
5.Identify the SQL operations that require sorts
6.List ways to reduce total sorts and disk sorts
7.Determine the number of memory sorts performed
8.Set parameters to optimize sorts
SQL Memory Usage
Memory-intensive SQL operators:
a.Sort-based( sort, group-by, rollup,window, and so on)
b.Hash-join
c.Bitmap operators(merge and inversion)
2.Concept of work area: Memory allocated by a memory-intensive operator to process its input data
3.Performance impact of memory:
a.Optimal: Input data fits into the work area(cache)
b.One-pass:Perform one extra pass over input data
c.Multi-pass:Perform several extra passes over input data
Auto Sort Area Management
1.Parameter for automatic sort area management:
a.PGA_AGGREGATE_TARGET(Ranges from 10 MB to 4000GB)
b.WORKAREA_SIZE_POLICY
c.AUTO|MANUAL
2.Replaces all *_area_size parameters
Setting Initially
The value of the PGA_AGGREGATE_TARGET initialization parameter(for example 100000KB,2500MB, or 50GB)should be set based on the total amount of memory available for the Oracle instance.This value can then be tuned and dynamically modified at instance level
1.Leave 20% of the available memory to other applications
2.Leave 80% of memory to the Oracle instance
3.For OLTP
PGA_AGGREGATE_TARGET=(totao_mem*80%)*20%
4.For DSS:
PGA_AGGREGATE_TARGET=(totao_mem*80%)*50%
PGA Management Resources
v$pgastat Statistics
over_allocation_count: PGA空间不够 突破PGA_AGGREGATE_TARGET限制的次数
total bytes processed: 一共处理了多少字节
extra bytes read/written: 如果工作区空间太小,就需要把数据而外的读写入到磁盘上的次数
cache hit percentage: 累计值,如果是100% 所有的读写操作都是在 内存完成的
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------- ---------- -----
over allocation count 0
bytes processed 70356992 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 2672
v$pga_target_advice
SQL> select round(pga_target_for_estimate/1024/1024) as target_mb,estd_pga_cache_hit_percentage as hit_ratio,estd_overalloc_count from v$pga_target_advice order by target_mb;
TARGET_MB HIT_RATIO ESTD_OVERALLOC_COUNT
---------- ---------- --------------------
11 96 2
23 96 2
45 96 2
68 100 0
90 100 0
108 100 0
126 100 0
144 100 0
162 100 0
180 100 0
270 100 0
TARGET_MB HIT_RATIO ESTD_OVERALLOC_COUNT
---------- ---------- --------------------
360 100 0
540 100 0
720 100 0
避免排序,排序很占性能
Sorting and Temp Space
排序过程中数据内存不够 ,会利用 temporary tablespace来排序
临时表空间管理 不会更新数据字典,只在一个segment上
只有一个setment,只有在第一次sort的时候 才分配磁盘
Operations Requiring Sorts
Some of the operations that may require sorts are:
1.index creation
2.Parallel insert operations involving index maintenance
3.ORDER BY or Group by clauses
4.DISTINCT values selction
5.UNION, INTERSECT,or MINUS operators
6.SORT-merge joins
7.ANALYZE command execution
SQL> select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------- ----------
sorts (memory) 19162
sorts (disk) 0
sorts (rows) 177907
Avoiding Sort
1.use NOSORT to create indexes
2.Use union all instead of union
3.Use index access for table joins
4.Create indexes on columns referenced in the ORDER BY clause
5.select the columns for analysis
6.Use ESTIMATE rather thanCOMPUTE for large objects
Diagnostic Tools
磁盘排序 /内存排序<5% ,如果大于则应该增大PGA_AGGREGATE_TARGET 或 SORT_AREA_SIZE