init parameter

我们来看看该sort_area_size参数对创建索引时排序的具体影响:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

/* 测试使用版本10.2.0.4 */

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /s01/arch
Oldest online log sequence     27
Current log sequence           34

/* 为了不受影响我们采用非归档模式 */

SQL> conn maclean/maclean
Connected.

SQL> alter session set workarea_size_policy=MANUAL;
Session altered.

/* 只有使用手动PGA管理时sort_area_size等参数才生效 */

SQL> alter session set db_file_multiblock_read_count=128;
Session altered.

/* 加大多块读参数帮助我们节约时间 */

SQL> alter session set "_sort_multiblock_read_count"=128;
Session altered.

/* 10g中sort_multiblock_read_count成为隐式参数，我们尝试手动固定它 */

SQL> set timing on;

SQL> alter session set events '10032 trace name context forever ,level 10';
Session altered.
Elapsed: 00:00:00.00

/* 在session级别设置10032事件，该事件帮助输出排序相关的统计信息*/

SQL> drop index ind_youyus;
alter session set sort_area_size=1048576;
alter session set sort_area_size=1048576;
[Read more…]
sort_area_size参数的一些表现
