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根据经纬查距离