1、排序進(jìn)程：
1）如果排序請(qǐng)求使用的memory不大于參數(shù)SORT_AREA_SIZE的設(shè)置值，則sort操作在內(nèi)存中進(jìn)程。反之，如果超過(guò)該值：
①數(shù)據(jù)將被分隔成更小的pieces，被稱(chēng)作sort runs；每個(gè)sorted先被分別的sort。
②server進(jìn)程會(huì)將pieces寫(xiě)入臨時(shí)表空間segment中；這些segments用于存儲(chǔ)中間的sort data。
③sorted pieces被合并從而產(chǎn)生最終結(jié)果。如果SORT_AREA_SIZE大小不足以一次merge所有sorted runs，sorted runs的子集會(huì)經(jīng)歷幾次merge過(guò)程。
2）sort area的大小是由SORT_AREA_SIZE設(shè)置的，它可動(dòng)態(tài)設(shè)置（alter session、alter system deferred），其默認(rèn)值根據(jù)OS的不同而不同。其默認(rèn)值可以滿足一般的OLTP需求，對(duì)于DSS應(yīng)用、批量jobs或是較大操作需要適當(dāng)?shù)臈l件。
3）另一個(gè)相關(guān)的參數(shù)是SORT_AREA_RETAINED_SIZE。當(dāng)sorting操作結(jié)束，sort area仍保存了部分等待取出的sorted rows，sort area可以shrink到最小為SORT_AREA_RETAINED_SIZE的大小。該部分memory仍然是被釋放到UGA中。其默認(rèn)值等于 SORT_AREA_SIZE。
4）關(guān)于位圖索引的初始化參數(shù)：
CREATE_BITMAP_AREA_SIZE：此參數(shù)是靜態(tài)的，指明了用于創(chuàng)建位圖索引可以分配的memory，默認(rèn)值是8MB（較大的memory設(shè)置會(huì)加快bitmap的創(chuàng)建速度，如果位圖索引的基數(shù)比較小，所需的memory也相對(duì)小）
BITMAP_MERGE_AREA_SIZE：該參數(shù)也是靜態(tài)的。默認(rèn)值是1MB。oracle為索引位圖段建立一張位圖。在進(jìn)行位圖索引掃描時(shí)，需要將掃描到的位圖索引排序后與位圖合并（Merge
），Oracle會(huì)在PGA中開(kāi)辟一片區(qū)域用于排序和合并。它就指定了這片區(qū)域的大小。
5）sort Area的新參數(shù)：
PGA_AGGREGATE_TARGET：指明了連接instance的所有進(jìn)程的PGA的總區(qū)域大小，大小可從10MB到4000GB。設(shè)置時(shí)，應(yīng)該先考慮system總的memory，以及分配給SGA的memory大小，將剩余的部分分配給該參數(shù)。它指明了自動(dòng)sort area管理。
WORKAREA_SIZE_POLICY：該參數(shù)可設(shè)置為（i）AUTO：只有定義了PGA_AGGREGATE_TARGET參數(shù)后才可被設(shè)置為 AUTO；（ii）MANUAL：對(duì)work areas的Sizing是手動(dòng)的，是基于*_AREA_SIZE參數(shù)設(shè)置的值來(lái)分配的。如果設(shè)置為MANUAL，可能會(huì)降低PGA內(nèi)存的利用率。
6）一般SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE應(yīng)該設(shè)為相同的大小，除非system memory不足或使用的是Oracle shared server模式。
7）內(nèi)存的需求：
在single server process中：一個(gè)執(zhí)行計(jì)劃可能會(huì)包含多個(gè)排序。例如，一個(gè)用sort-merge方式join兩個(gè)tables并使用order 不用子句的sql，包含了三個(gè)sort。對(duì)此，在運(yùn)行order by時(shí)，一個(gè)SORT_AREA_SIZE大小的memory用于當(dāng)前的sort；兩個(gè)SORT_AREA_RETAINED_SIZE大小的 memory用于join sorts。
在parallel query 進(jìn)程中：假設(shè)有2個(gè)server，則會(huì)使用（SORT_AREA_SIZE*2*并發(fā)度）大小的memory，此外如果需要，還要使用 （SORT_AREA_RETAINED_SIZE*并發(fā)度*事先做的排序次數(shù)）的memory。實(shí)驗(yàn)表明分配更大的memory不會(huì)對(duì)性能有更好的提 高。
2、Tuning sorts
需要注意的問(wèn)題有：
* 如果數(shù)據(jù)已經(jīng)使用索引進(jìn)行了排序，盡量避免再次排序
* 如果sort操作本身并不大，但是設(shè)定的sort area過(guò)小使得其不得不進(jìn)行頁(yè)面的交換。
* 此外使用大的內(nèi)存chunks用sort也會(huì)造成paging核swapping，從而降低系統(tǒng)性能。
* 避免在頻繁分配和收回磁盤(pán)上的臨時(shí)表空間操作
所以應(yīng)該：盡可能的避免排序操作；盡量使sort操作在內(nèi)存中完成，減少swapping和paging的操作；盡可能的減少對(duì)臨時(shí)空間的請(qǐng)求。
1）臨時(shí)表空間的優(yōu)化：
明確指明sort操作使用的臨時(shí)表空間，可以有效的避免在分配與收回sort空間時(shí)的序列化操作。在臨時(shí)表空間中，不能包含任何永久的object，在 Oracle Parallel Server中，對(duì)于每個(gè)instance，會(huì)有一個(gè)單獨(dú)的臨時(shí)表空間。臨時(shí)表空間的datafile在備份時(shí)時(shí)不需要備份的。
對(duì)于臨時(shí)sort segment：
* 在第一次需要申請(qǐng)臨時(shí)表空間的sort操作申請(qǐng)時(shí)，sort segment才被創(chuàng)建；
* 在DB被關(guān)閉時(shí)才被drop，可以用命令行對(duì)其進(jìn)行擴(kuò)容
* 它也是由extents組成的，可以用于不同的sort 操作
* 在SGA中存在一個(gè)叫sort extents pool（SEP）的數(shù)據(jù)結(jié)構(gòu)，用于管理臨時(shí)表空間。當(dāng)進(jìn)程需要申請(qǐng)sort space時(shí)，會(huì)先在SEP中查找臨時(shí)表空間中空閑的extents。
2）需要進(jìn)行排序的命令請(qǐng)求有：
* 索引的創(chuàng)建：在建立b-tree之前，必須先將索引列進(jìn)行排序操作；
* order by和group by子句：必須先對(duì)該子句使用的字段進(jìn)行排序；
* distinct關(guān)鍵字：必須為消除重復(fù)行先進(jìn)行排序；
* UNION、INTERSECT或MINUS操作符：server需要為了消除重復(fù)rows先對(duì)表進(jìn)行sort。
* 兩表之間的sort-merge連接：如果沒(méi)有相應(yīng)的索引用于兩表的連接，對(duì)于等值連接，如果使用此方法join，則先需要對(duì)兩表進(jìn)行全表掃描，并分別進(jìn)行排序，再合并兩表。
對(duì)于server的排序操作的監(jiān)控，可以從v$sysstat中查看：
select name, value from v$sysstat where name = ’sorts (rows)’;
可以使用analyze或是dbms_utility.analyze_*等方法對(duì)tables、indexes或是cluster進(jìn)程統(tǒng)計(jì)，從而更好的指導(dǎo)CBO，從而產(chǎn)生更好的執(zhí)行計(jì)劃。
3）避免使用sort：在任何可能的地方盡量避免使用sort
* 使用nosort關(guān)鍵字創(chuàng)建索引：
默認(rèn)情況下，在表中創(chuàng)建索引的時(shí)候，會(huì)對(duì)表中的記錄進(jìn)行排序，排序成功后再創(chuàng)建索引。但是當(dāng)記錄比較多的是，這個(gè)排序作業(yè)會(huì)占用比較多的時(shí)間，這也就增加了索引建立的時(shí)間(排序作業(yè)是在索引創(chuàng)建作業(yè)中完成)。有時(shí)候，我們導(dǎo)入數(shù)據(jù)的時(shí)候，如采用insert into 語(yǔ)句插入數(shù)據(jù)過(guò)程中同時(shí)采用Order by子句對(duì)索引字段進(jìn)行了排序。此時(shí)如果在索引創(chuàng)建過(guò)程中再進(jìn)行排序的話，就有點(diǎn)脫褲子放屁，多此一舉了。為此在重新創(chuàng)建索引時(shí)，如果表中的數(shù)據(jù)已經(jīng)排好序了(按索引字段排序)，那么在創(chuàng)建索引時(shí)就不需要為此重新排序。此時(shí)在創(chuàng)建索引時(shí)，數(shù)據(jù)庫(kù)管理員就可以使用NOSORT可選項(xiàng)，告訴數(shù)據(jù)庫(kù)系統(tǒng)不需要對(duì) 表中當(dāng)記錄進(jìn)行重新排序了。
采用了這個(gè)選項(xiàng)之后，如果表中的記錄已經(jīng)按順序排列，那么在重新創(chuàng)建索引的時(shí)候，就不會(huì)重新排序，可以提高索引創(chuàng)建的時(shí)間，節(jié)省內(nèi)存中的排序緩存空 間。相反，如果表中的記錄是不按索引關(guān)鍵字排序的話，那么此時(shí)采用NOSORT關(guān)鍵字的話，系統(tǒng)就會(huì)提示錯(cuò)誤信息，并拒絕創(chuàng)建索引。所以在使用 NOSORT可選項(xiàng)的時(shí)候，數(shù)據(jù)庫(kù)管理員盡管放心大膽的使用。因?yàn)槠鋵?shí)在不能夠使用這個(gè)選項(xiàng)的時(shí)候，數(shù)據(jù)庫(kù)也會(huì)明確的告知。為此其副作用就比較少，數(shù)據(jù)庫(kù)管理員只需要把這個(gè)可選項(xiàng)去掉然后重新執(zhí)行一次即可。不過(guò)這里需要注意的是，如果表中的記錄比較少的話，那么使用NOSORT選項(xiàng)的效果并不是很明顯。
* 使用UNION ALL代替UNION：因?yàn)?span lang="EN-US">UNION ALL不會(huì)消除重復(fù)的rows，所以也無(wú)需進(jìn)行sort操作。
* 使用Nested loop join代替sort-merge join
* 在經(jīng)常使用order by子句的列上創(chuàng)建索引。
* 使用analyze時(shí)，只統(tǒng)計(jì)所需字段的數(shù)據(jù)：
ANALYZE … FOR COLUMNS或ANALYZE … FOR ALL INDEXED COLUMNS
* 對(duì)于ANALYZE COMPUTE，其統(tǒng)計(jì)結(jié)果更精確，但是需要進(jìn)行一定的sort，對(duì)此，可以使用ESTIMATE子句來(lái)替代大表或cluster中的ANALYZE。
4）診斷工具：
* 在視圖v$sysstat中顯示的信息中有：
sorts (memory)：完全在內(nèi)存中進(jìn)行的排序的次數(shù)
sorts (disk)：請(qǐng)求臨時(shí)segments的I/O進(jìn)行sorts的次數(shù)
sorts (rows)：當(dāng)前已經(jīng)進(jìn)行過(guò)的sort的rows的數(shù)量
* 在statspack輸出的report中也存在上述信息，并計(jì)算了部分平均值
* 視圖v$sort_segment和v$sort_usage顯示了當(dāng)前臨時(shí)segment中的使用情況，以及那些user占用了這些臨時(shí)segment。
@@ 在具體診斷時(shí)，可計(jì)算磁盤(pán)sort和memory sort的比率，它應(yīng)該小于5%。否則，如果此比率顯示了較大的磁盤(pán)排序，則需要考慮是否可以增大SORT_AREA_SIZE的設(shè)置。
@@ 注意的是：如果增大了sort area，則每個(gè)需要sort的進(jìn)程所占用的memory可能都會(huì)增加，在一定程度上影響了OS的memory分配及paging和swapping。所 以當(dāng)增加了sort_area_size后，考慮是否可以適當(dāng)減小SORT_RESERVED_AREA_SIZE的值，不過(guò)此值的減小，在一定程度上減 少了內(nèi)存的使用，但也可能附加著造成了I/O的可能性。
6） 監(jiān)控臨時(shí)表空間：主要是查看V$SORT_SEGMENT視圖：
select tablespace_name, current_users, total_extents, used_extents, extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment;
具體字段的意義如下：
CURRENT_USERS：Number of active users
TOTAL_EXTENTS ：Total number of extents
USED_EXTENTS ：Extents currently allocated to sorts
EXTENT_HITS： Number of times an unused extent was found in the pool
MAX_USED_BLOCKS： Maximum number of used blocks
MAX_SORT_BLOCKS ：Maximum number of blocks used by an individual sort
7）臨時(shí)表空間的設(shè)置：
默認(rèn)臨時(shí)表空間的存儲(chǔ)參數(shù)對(duì)于sort segment都是適用的，只是它們有無(wú)限的extents。
設(shè)置臨時(shí)表空間的參數(shù)時(shí)，先要考慮sort_area_size的值。temporary tablespace的initial和next參數(shù)應(yīng)該是sort_area_size的整數(shù)倍，并要考慮額外的segment header的空間。將PCTINCREASE設(shè)置為0 。
可以為users指定不同的temporary tablespace，并且可以將temp tablespace放在不同的磁盤(pán)上，對(duì)于查看使用情況，可以看v$sort_usage
select username, tablespace, contents, extents, blocks from v$sort_usage;
此外，還可查看V$TEMPFILE和DBA_TEMP_FILES可以獲得temporary tablespace的相關(guān)信息。