sort_area_size參數的一些表現

我們來看看該sort_area_size參數對建立索引時排序的具體影響:

<code>SQL> </code><code>select</code> <code>* </code><code>from</code> <code>v$version;</code>

<code>BANNER</code>

<code>Oracle </code><code>Database</code> <code>10g Enterprise Edition Release 10.2.0.4.0 - 64bi</code>

<code>PL/SQL Release 10.2.0.4.0 - Production</code>

<code>CORE 10.2.0.4.0 Production</code>

<code>TNS </code><code>for</code> <code>Linux: Version 10.2.0.4.0 - Production</code>

<code>NLSRTL Version 10.2.0.4.0 - Production</code>

<code>/* 測試使用版本10.2.0.4 */</code>

<code>SQL> archive log list;</code>

<code>Database</code> <code>log mode </code><code>No</code> <code>Archive Mode</code>

<code>Automatic archival Disabled</code>

<code>Archive destination /s01/arch</code>

<code>Oldest online log </code><code>sequence</code> <code>27</code>

<code>Current</code> <code>log </code><code>sequence</code> <code>34</code>

<code>/* 為了不受影響我們采用非歸檔模式 */</code>

<code>SQL> conn maclean/maclean</code>

<code>Connected.</code>

<code>SQL> </code><code>alter</code> <code>session </code><code>set</code> <code>workarea_size_policy=MANUAL;</code>

<code>Session altered.</code>

<code>/* 隻有使用手動PGA管理時sort_area_size等參數才生效 */</code>

<code>SQL> </code><code>alter</code> <code>session </code><code>set</code> <code>db_file_multiblock_read_count=128;</code>

<code>/* 加大多塊讀參數幫助我們節約時間 */</code>

<code>SQL> </code><code>alter</code> <code>session </code><code>set</code> <code>"_sort_multiblock_read_count"</code><code>=128;</code>

<code>/* 10g中sort_multiblock_read_count成為隐式參數，我們嘗試手動固定它 */</code>

<code>SQL> </code><code>set</code> <code>timing </code><code>on</code><code>;</code>

<code>SQL> </code><code>alter</code> <code>session </code><code>set</code> <code>events </code><code>'10032 trace name context forever ,level 10'</code><code>;</code>

<code>Elapsed: 00:00:00.00</code>

<code>/* 在session級别設定10032事件，該事件幫助輸出排序相關的統計資訊*/</code>

<code>SQL> </code><code>drop</code> <code>index</code> <code>ind_youyus;</code>

<code>alter</code> <code>session </code><code>set</code> <code>sort_area_size=1048576;</code>

<code>/* 注意10g目前存在一個bug，sort_area_size需要手動設定2次方能生效，否則無效！ */</code>

<code>create</code> <code>index</code> <code>ind_youyus </code><code>on</code> <code>youyus(t1,t2) nologging;</code>

<code>Index</code> <code>dropped.</code>

<code>Elapsed: 00:00:00.07</code>

<code>Index</code> <code>created.</code>

<code>Elapsed: 00:00:35.70</code>

<code>/* 以下為對應建立索引排序的10032 trace * /</code>

<code>---- Sort Parameters ------------------------------</code>

<code>sort_area_retained_size 1048576</code>

<code>sort_multiblock_read_count 29</code>

<code>max</code> <code>intermediate merge width 2</code>

<code>---- Sort Statistics ------------------------------</code>

<code>Initial runs 1</code>

<code>Input records 10000001</code>

<code>Output</code> <code>records 10000001</code>

<code>Disk blocks 1st pass 58690</code>

<code>Total disk blocks used 58692</code>

<code>Total number </code><code>of</code> <code>comparisons performed 10104798</code>

<code> </code><code>Comparisons performed </code><code>by</code> <code>in</code><code>-memory sort 10098798</code>

<code> </code><code>Comparisons while searching </code><code>for</code> <code>key</code> <code>in</code><code>-memory 6000</code>

<code>Temp</code> <code>segments allocated 1</code>

<code>Extents allocated 459</code>

<code>Uses version 2 sort</code>

<code>Does </code><code>not</code> <code>use asynchronous IO</code>

<code> </code><code>---- Run Directory Statistics ----</code>

<code>Run directory block reads (buffer cache) 2</code>

<code>Block pins (</code><code>for</code> <code>run directory) 1</code>

<code>Block repins (</code><code>for</code> <code>run directory) 1</code>

<code> </code><code>---- Direct Write Statistics -----</code>

<code>Write slot </code><code>size</code> <code>49152</code>

<code>Write slots used during </code><code>in</code><code>-memory sort 2</code>

<code>Number </code><code>of</code> <code>direct writes 10011</code>

<code>Num blocks written (</code><code>with</code> <code>direct write) 58690</code>

<code>Block pins (</code><code>for</code> <code>sort records) 58690</code>

<code>Cached block repins (</code><code>for</code> <code>sort records) 667</code>

<code> </code><code>---- Direct Read Statistics ------</code>

<code>Size</code> <code>of</code> <code>read</code> <code>slots </code><code>for</code> <code>output</code> <code>524288</code>

<code>Number </code><code>of</code> <code>read</code> <code>slots </code><code>for</code> <code>output</code> <code>2</code>

<code>Number </code><code>of</code> <code>direct sync reads 58627</code>

<code>Number </code><code>of</code> <code>blocks </code><code>read</code> <code>synchronously 58690</code>

<code>---- End of Sort Statistics -----------------------</code>

<code>/* 可以看到這裡實際的sort_multiblock_read_count是29，而非我們設定的128，而</code><code>max</code> <code>intermediate merge width最大中間運作合并寬度為2；我們知道實際的SMRC有公式</code><code>MIN</code><code>(ROUND(SORT_AREA_SIZE/(2*2*BLOCK_SIZE))-1, _sort_multiblock_read_count)=</code><code>MIN</code><code>(32-1,128)=31，而此處的實際值為29； merge width = ROUND(SORT_AREA_SIZE/(2*SMRC*BLOCK_SIZE))-1= round( 1048576/(2*29*8k))-1= round(2.2)-1=2-1=1；看起來實際的公式有所修正。*/</code>

<code>alter</code> <code>session </code><code>set</code> <code>sort_area_size=524288000;</code>

<code>Elapsed: 00:00:00.04</code>

<code>Elapsed: 00:00:36.82</code>

<code>sort_area_retained_size 524288000</code>

<code>sort_multiblock_read_count 128</code>

<code>max</code> <code>intermediate merge width 225</code>

<code>Initial runs 2</code>

<code>Number </code><code>of</code> <code>merges 1</code>

<code>Total number </code><code>of</code> <code>comparisons performed 17571986</code>

<code> </code><code>Comparisons performed </code><code>by</code> <code>in</code><code>-memory sort 10098438</code>

<code> </code><code>Comparisons performed during merge 7473532</code>

<code> </code><code>Comparisons while searching </code><code>for</code> <code>key</code> <code>in</code><code>-memory 16</code>

<code>Run directory block reads (buffer cache) 3</code>

<code>Block repins (</code><code>for</code> <code>run directory) 2</code>

<code>Write slot </code><code>size</code> <code>1048576</code>

<code>Write slots used during </code><code>in</code><code>-memory sort 50</code>

<code>Number </code><code>of</code> <code>direct writes 460</code>

<code>Cached block repins (</code><code>for</code> <code>sort records) 1</code>

<code>Size</code> <code>of</code> <code>read</code> <code>slots </code><code>for</code> <code>output</code> <code>1048576</code>

<code>Number </code><code>of</code> <code>read</code> <code>slots </code><code>for</code> <code>output</code> <code>500</code>

<code>Number </code><code>of</code> <code>direct sync reads 58563</code>

<code>/* 10g中引入了新的排序算法，排序初始化運作完成後，會儲存初始資料集的鍵值到記憶體中，在進行資料集進行合并時，會根據鍵值來選擇資料集。從trace檔案中可以看到這樣的統計資訊:Comparisons while searching </code><code>for</code> <code>key</code> <code>in</code><code>-memory 16;*/</code>

<code>/* 可以看到write slot的大小也随sort_area_size變化，sort_area_size增大的同時Number </code><code>of</code> <code>direct writes由10011次下降到460次,此外</code><code>read</code> <code>slots的總大小(524288*2=1048576=sort_area_size，1048576*500=sort_area_size) */</code>

<code>/* 在合并merge階段，因為讀取緩存足夠大，是以合并次數下降到1 */</code>

to be continued ..............

本文轉自maclean_007 51CTO部落格，原文連結:http://blog.51cto.com/maclean/1277650

sort_area_size參數的一些表現

繼續閱讀

oracle中的start with connect by用法

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離