Oracle8i中SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的理解
在Oracle中,一個process執行一個邏輯複雜的SQL時,可能這個SQL包含了眾多的SORT工作,Oracle也只最多最多分配一個SORT_AREA_SIZE大小的空間來進行SORT.但是Oracle可以為我們分配多個SORT_AREA_RETAINED_SIZE來臨時儲存SORT結束後的結果.
SORT_AREA_SIZE
-------------------------
SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never occur. Only one memory area of SORT_AREA_SIZE exists for each user process at any time.SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes.
Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.
Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.
SORT_AREA_RETAINED_SIZE
-----------------------------------------
SORT_AREA_RETAINED_SIZE specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes.
The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory.
This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.
Oracle may allocate multiple sort spaces of this size for each query.
Usually, only one or two sorts occur at one time, even for complex queries.
In some cases, however, additional concurrent sorts are required, and each sort keeps its own memory area.
the SORT_AREA_RETAINED_SIZE is the amount of memory retained when a sort completes and results are being returned.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12361284/viewspace-114504/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sort_area_retained_size與sort_area_sizeAI
- Oracle8i 與Oracle9i 中對pctused 和pctfree, pctincrease 的理解Oracle
- javascript 中{}和[] 的理解JavaScript
- 三、Quartz中Scheduler的理解和使用quartz
- iOS中copy和strong的個人理解iOS
- 【Oracle】排序與sort_area_sizeOracle排序
- javascript中的prototype和__proto__的理解JavaScript
- 我在oracle8i中,在資料庫和頁面顯示的都是亂碼?Oracle資料庫
- 如何理解Spring中的IOC和AOPSpring
- 深入理解Swift中的Class和StructSwiftStruct
- Objective-C中的self和super理解Object
- 理解JavaScript中BOM和DOM的關係JavaScript
- Python中__init__的用法和理解Python
- 深入理解JavaScript中的WeakMap和WeakSetJavaScript
- 理解和使用SQL Server中的並行SQLServer並行
- 理解 RxJava 中的 Single 和 CompletableRxJava
- 對javascript中的call()和apply()的理解JavaScriptAPP
- Python 中的引用和類屬性的理解Python
- jquery中dom元素的attr和prop方法的理解jQuery
- 深入理解python中的類和物件Python物件
- 理解Nginx中Server和Location的匹配邏輯NginxServer
- 關於Vue中插槽的理解和總結Vue
- Qt中對js和HTML通訊的理解QTJSHTML
- java學習中對類和物件的理解Java物件
- 理解JavaScript中的作用域和上下文JavaScript
- Python中關於++和—(自增和自減)的理解Python
- hive中的表、外部表、分割槽和桶的理解Hive
- 不妨這樣去理解JavaScript中的dom和bomJavaScript
- 深入理解 Go 中的 new() 和 make() 函式Go函式
- 正確理解和使用JAVA中的字串常量池Java字串
- 簡單快速理解js中的this、call和applyJSAPP
- 深入理解java中的組合和繼承Java繼承
- Java中HashMap和TreeMap的區別深入理解JavaHashMap
- 理解Maven中的SNAPSHOT版本和正式版本Maven
- C++中引用和匿名物件的理解和本質剖析C++物件
- 物件的使用處理,作用域的和ajax中this的理解物件
- 理解 JavaScript 中的 thisJavaScript
- 理解JS中的thisJS