如何高效 online 创建大表的索引
发布网友
发布时间：2022-04-20 05:08
我来回答
共1个回答
热心网友
时间：2022-04-14 04:25
目的:测试如何以最快的方式online建立索引
C:\Documents and Settings\Administrator>sqlplus sys@FUTEST_3237 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Proction 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 - Proction
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 - Proction 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 - Proction
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;
