ORACLE9i_性能调优基础六(Optimizing Sort Operations)
来源:互联网 发布:文化部网络直播黑名单 编辑:程序博客网 时间:2024/04/25 12:52
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 0bytes processed 70356992 bytesextra bytes read/written 0 bytescache hit percentage 100 percentrecompute 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) 19162sorts (disk) 0sorts (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
- ORACLE9i_性能调优基础六(Optimizing Sort Operations)
- ORACLE9i_性能调优基础十四(LOCK)
- optimizing sort operations
- Oracle optimizing sort operations
- ORACLE9i_性能调优基础一(Diagnostic and Tuning Tools)
- ORACLE9i_性能调优基础三(Tuning the Shared Pool)
- ORACLE9i_性能调优基础四(Buffer Cache )
- ORACLE9i_性能调优基础五(Sizing Other SGA structures)
- ORACLE9i_性能调优基础七(Using Resource Manager)
- ORACLE9i_性能调优基础八(SQL tuning)
- ORACLE9i_性能调优基础九(Managing Statistics)
- ORACLE9i_性能调优基础十(Oracle Blocks Efficiently)
- ORACLE9i_性能调优基础十一(Data Storage Structures)
- ORACLE9i_性能调优基础十二(Application Tuning)
- ORACLE9i_性能调优基础十三(Materialized Views)
- ORACLE9i_性能调优基础二(Database Configuration and IO Issues)
- 优化锁操作 Optimizing Locking Operations
- Spark性能调优(六)
- 浅谈php安全
- 面向对象(匿名对象的使用)
- VB.NET 章鱼哥 自定义控件的建立和使用
- 概率论与数理统计(2)
- HDU 1372(只是复习一下BFS)
- ORACLE9i_性能调优基础六(Optimizing Sort Operations)
- 敏捷开发只是工具; 热爱产品, 热爱客户才是真正该做的事
- pthread_key_t和pthread_key_create()详解
- 算法导论—红黑树(还没写完,先备份一个)
- Linux学习笔记
- tomcat deploy path
- 《linux从入门到精通》刘忆智 (我再次说, 这类书,入门是可以的, 精通则完全是忽悠骗人的)
- [十天学会Linux Shell]第一天:小试牛刀
- 第三章第六题