只是记录一个过程,比较冗长,各位若是对标题感兴趣的话,直接拉至文章结尾,看结果吧.
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>systeminfo
主机名: ORACLETEST
OS 名称: Microsoft(R) Windows(R) Server 2003, Enterprise Edition
OS 版本: 5.2.3790 Service Pack 2 Build 3790
系统类型: X86-based PC
处理器: 安装了 2 个处理器。
[01]: x86 Family 6 Model 23 Stepping 6 GenuineIntel ~2493 Mhz
[02]: x86 Family 6 Model 23 Stepping 6 GenuineIntel ~2493 Mhz
物理内存总量: 3,062 MB
可用的物理内存: 1,156 MB
页面文件: 最大值: 5,500 MB
页面文件: 可用: 2,588 MB
页面文件: 使用中: 2,912 MB
页面文件位置: c:\pagefile.sys
d:\pagefile.sys
e:\pagefile.sys
f:\pagefile.sys
环境其实就是一台很普通的PC机..
1双核的CPU+3G内存
15G的表
5500W row的量
目的:测试如何以最快的方式online建立索引
C:\Documents and Settings\Administrator>sqlplus sys@FUTEST_3237 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 9 17:06:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS TEST 3.237 SQL> set linesize 200
SYS TEST 3.237 SQL> set timing on
SYS TEST 3.237 SQL> set autot on
SYS TEST 3.237 SQL> alter session SET workarea_size_policy=MANUAL;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> alter session set sort_area_size=1073741824;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> alter session set sort_area_retained_size=1073741824;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> alter session set db_file_multiblock_read_count=128;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DB.ALOTPRM(MTRL_LOT_ID) parallel(degree 2) ONLINE NOLOGGING;
索引已创建。
已用时间: 00: 19: 12.34
SYS TEST 3.237 SQL>
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
上面的测试用了20分钟...
- -对比下面的测试很晕
设置的参数为
sort_area_size=1073741824;
sort_area_retained_size=1073741824;
db_file_multiblock_read_count=128;
还有并行系数"2"
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--查看当前的并行状况
select decode(a.QCSERIAL#, null, 'PARENT', 'CHILD') stmt_level,
a.SID,
a.SERIAL#,
b.USERNAME,
b.OSUSER,
b.SQL_HASH_VALUE,
b.SQL_ADDRESS,
a.DEGREE,
a.REQ_DEGREE
from v$px_session a, v$session b
where a.SID = b.SID
order by a.QCSID, stmt_level desc;
C:\Documents and Settings\Administrator>sqlplus sys@futest_3237 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 12 15:23:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS TEST 3.237 SQL> set timing on
SYS TEST 3.237 SQL> set linesize on
SP2-0268: linesize 选项的编号无效
SYS TEST 3.237 SQL> set linesize 200
SYS TEST 3.237 SQL> set autot on
SYS TEST 3.237 SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DB.ALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING;
索引已创建。
已用时间: 00: 05: 36.36
--什么都不设置的情况下,仅仅是online+nologging 才耗用了5分钟 ,当然 这是测试环境,整个DB没有模拟生产压力的
SYS TEST 3.237 SQL> drop index IX_ALOTPRM_MTRL_LOT_ID;
索引已删除。
已用时间: 00: 00: 00.01
SYS TEST 3.237 SQL> alter session SET workarea_size_policy=MANUAL;
会话已更改。
已用时间: 00: 00: 00.03
SYS TEST 3.237 SQL> alter session set sort_area_size=1073741824;
会话已更改。
已用时间: 00: 00: 00.01
SYS TEST 3.237 SQL> alter session set sort_area_retained_size=1073741824;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> alter session set db_file_multiblock_read_count=128;
会话已更改。
已用时间: 00: 00: 00.01
SYS TEST 3.237 SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DB.ALOTPRM(MTRL_LOT_ID) parallel(degree 2) ONLINE NOLOGGING;
索引已创建。
已用时间: 00: 12: 48.12
--这次又用了12分钟
sort_area_size=1073741824;
sort_area_retained_size=1073741824;
db_file_multiblock_read_count=128;
并行系数"2";
这测试结果太不稳定了...如果只要5分钟的话- -!那直接用就是了,还整这么费劲干啥?
咳咳,多测试几次吧,刨根问底~
SYS TEST 3.237 SQL> drop index IX_ALOTPRM_MTRL_LOT_ID;
索引已删除。
我决定drop掉INDEX,然后回收tablespace再测试一次...
SYS TEST 3.237 SQL> select sum(bytes)/1024/1024/1024 sumbytes
2 from dba_extents a
3 where tablespace_name='USERS'
4 /
SUMBYTES
----------
14.935791
SYS TEST 3.237 SQL> select name,round(to_number(bytes/1024/1024/1024),5) as bytes from v$datafile;
NAME BYTES
-------------------------------------------------- ----------
E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O1_MF_SYSTEM 2.68555
_5TJTP2GN_.DBF
E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O1_MF_UNDOTB .34668
S1_5TJTP2L2_.DBF
E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O1_MF_SYSAUX .26367
_5TJTP2J3_.DBF
E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O1_MF_USERS_ 16
5TJTP2L2_.DBF
NAME BYTES
-------------------------------------------------- ----------
E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O1_MF_EXAMPL .09766
E_5TJTR0PY_.DBF
收缩之~
SYS TEST 3.237 SQL> ALTER DATABASE DATAFILE 'E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O1_MF_USERS_5TJTP2L2_.DBF'
2 RESIZE 15360M;
数据库已更改。
接着继续~
我想了想,会不会与这个测试DB的内存分配有关系?
SYS TEST 3.237 SQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1298160 bytes
Variable Size 146800912 bytes
Database Buffers 457179136 bytes
Redo Buffers 7090176 bytes
SYS TEST 3.237 SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------
pga_aggregate_target big integer 194M
SYS TEST 3.237 SQL>
把参数改小点 - -
100M+32 DB_file_multiblock_read_count+并行2
sort_area_size=102400000;
sort_area_retained_size=102400000;
db_file_multiblock_read_count=32;
还有并行系数"2"
把DB的内存分配得稍微大一点...
SYS TEST 3.237 SQL> show sga
Total System Global Area 805306368 bytes
Fixed Size 1299316 bytes
Variable Size 205524108 bytes
Database Buffers 591396864 bytes
Redo Buffers 7086080 bytes
SYS TEST 3.237 SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 768M
sga_target big integer 768M
SYS TEST 3.237 SQL> show parameter pga
SYS TEST 3.237 SQL> CREATE INDEX FU_DB.IX_ALOTPRM_MTRL_LOT_ID on FU_DB.ALOTPRM(MTRL_LOT_ID) parallel(degree 2) ONLINE NOLOGGING;
索引已创建。
已用时间: 00: 10: 50.43
搞来搞去,还是整了10分钟...
再来一次,不并行看看如何~
sort_area_size=102400000;
sort_area_retained_size=102400000;
db_file_multiblock_read_count=32;
SYS TEST 3.237 SQL> alter session SET workarea_size_policy=MANUAL;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> alter session set sort_area_size=102400000;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> alter session set sort_area_retained_size=102400000;
会话已更改。
已用时间: 00: 00: 00.01
SYS TEST 3.237 SQL> alter session set db_file_multiblock_read_count=32;
会话已更改。
已用时间: 00: 00: 00.00
SYS TEST 3.237 SQL> CREATE INDEX FU_DB.IX_ALOTPRM_MTRL_LOT_ID on FU_DB.ALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING;
索引已创建。
已用时间: 00: 05: 52.60
再多测一次,再去吃饭..
删索引,
回收tablespace
总结一下
我很晕了,为什么不并行反而更快?
我估计和具体的场景 硬件条件 系统并发负载 还是有所联系,不然大牛们一直都说大表建索引,必须
session级别增大sort_area_size + nologging + 并行
看样子,过段时间 找台4 CPU的3850再试试吧...
条件 时间
-------------------------------------------------------------------------------------------------------------------------------
手动1G+Multi block read count 128+并行2
workarea_size_policy=MANUAL;
alter session set sort_area_size=1073741824;
alter session set sort_area_retained_size=1073741824; 00: 19: 12
alter session set db_file_multiblock_read_count=128;
parallel(degree 2) ONLINE NOLOGGING;
-------------------------------------------------------------------------------------------------------------------------------
啥事不干直接建
CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID
on FU_DB.ALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING; 00: 05: 36
-------------------------------------------------------------------------------------------------------------------------------
我手多,又测试了一遍第一种情况
workarea_size_policy=MANUAL;
alter session set sort_area_size=1073741824;
alter session set sort_area_retained_size=1073741824; 00: 12: 48
alter session set db_file_multiblock_read_count=128;
parallel(degree 2) ONLINE NOLOGGING;
-------------------------------------------------------------------------------------------------------------------------------
手动100M+Multi block read count 32+并行2
workarea_size_policy=MANUAL;
sort_area_size=102400000;
sort_area_retained_size=102400000; 00: 10: 50
db_file_multiblock_read_count=32;
parallel(degree 2) ONLINE NOLOGGING;
-------------------------------------------------------------------------------------------------------------------------------
手动100M+Multi block read count 32+无并行 试试
workarea_size_policy=MANUAL;
sort_area_size=102400000;
sort_area_retained_size=102400000; 00: 05: 52
db_file_multiblock_read_count=32;
无并行
-------------------------------------------------------------------------------------------------------------------------------
再来一次手动100M+Multi block read count 128+无并行
workarea_size_policy=MANUAL;
sort_area_size=102400000;
sort_area_retained_size=102400000; 00: 05: 55
db_file_multiblock_read_count=128;
无并行
-------------------------------------------------------------------------------------------------------------------------------
再来一次啥事不干直接建
CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID
on FU_DB.ALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING; 00: 05: 29
-------------------------------------------------------------------------------------------------------------------------------
若是不并行的话,Process 里会看到一个 CPU使用蛮高的TID,而且只有一个核在激烈的WORK.
若是并行的话,这里会看到好几个PID在work.
而且两个核都在work.