|
Microsoft Windows [版本 ] (C) 版权所有 Microsoft Corp C:\Documents and Settings\Administrator>systeminfo 主机名: ORACLETEST OS 名称: Microsoft(R) Windows(R) Server Enterprise Edition OS 版本: Service Pack Build 系统类型: Xbased PC 处理器: 安装了 个处理器 []: x Family Model Stepping GenuineIntel ~ Mhz []: x Family Model Stepping GenuineIntel ~ Mhz 物理内存总量: MB 可用的物理内存: MB 页面文件: 最大值: MB 页面文件: 可用: MB 页面文件: 使用中: MB 页面文件位置: c:\pagefilesys d:\pagefilesys e:\pagefilesys f:\pagefilesys 环境其实就是一台很普通的PC机 双核的CPU+G内存 G的表 W row的量 目的:测试如何以最快的方式online建立索引 C:\Documents and Settings\Administrator>sqlplus sys@FUTEST_ as sysdba SQL*Plus: Release Production on 星期五 月 :: Copyright (c) Oracle All rights reserved 输入口令: 连接到: Oracle Database g Enterprise Edition Release Production With the Partitioning OLAP Data Mining and Real Application Testing options SYS TEST SQL> set linesize SYS TEST SQL> set timing on SYS TEST SQL> set autot on SYS TEST SQL> alter session SET workarea_size_policy=MANUAL; 会话已更改 已用时间: : : SYS TEST SQL> alter session set sort_area_size=; 会话已更改 已用时间: : : SYS TEST SQL> alter session set sort_area_retained_size=; 会话已更改 已用时间: : : SYS TEST SQL> alter session set db_file_multiblock_read_count=; 会话已更改 已用时间: : : SYS TEST SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) parallel(degree ) ONLINE NOLOGGING; 索引已创建 已用时间: : : SYS TEST SQL>
上面的测试用了分钟 对比下面的测试很晕 设置的参数为 sort_area_size=; sort_area_retained_size=; db_file_multiblock_read_count=; 还有并行系数
查看当前的并行状况 select decode(aQCSERIAL# null PARENT CHILD) stmt_level aSID aSERIAL# bUSERNAME bOSUSER bSQL_HASH_VALUE bSQL_ADDRESS aDEGREE aREQ_DEGREE from v$px_session a v$session b where aSID = bSID order by aQCSID stmt_level desc; C:\Documents and Settings\Administrator>sqlplus sys@futest_ as sysdba SQL*Plus: Release Production on 星期一 月 :: Copyright (c) Oracle All rights reserved 输入口令: 连接到: Oracle Database g Enterprise Edition Release Production With the Partitioning OLAP Data Mining and Real Application Testing options SYS TEST SQL> set timing on SYS TEST SQL> set linesize on SP: linesize 选项的编号无效 SYS TEST SQL> set linesize SYS TEST SQL> set autot on SYS TEST SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING; 索引已创建 已用时间: : : 什么都不设置的情况下仅仅是online+nologging 才耗用了分钟 当然 这是测试环境整个DB没有模拟生产压力的 SYS TEST SQL> drop index IX_ALOTPRM_MTRL_LOT_ID; 索引已删除 已用时间: : : SYS TEST SQL> alter session SET workarea_size_policy=MANUAL; 会话已更改 已用时间: : : SYS TEST SQL> alter session set sort_area_size=; 会话已更改 已用时间: : : SYS TEST SQL> alter session set sort_area_retained_size=; 会话已更改 已用时间: : : SYS TEST SQL> alter session set db_file_multiblock_read_count=; 会话已更改 已用时间: : : SYS TEST SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) parallel(degree ) ONLINE NOLOGGING; 索引已创建 已用时间: : : 这次又用了分钟 sort_area_size=; sort_area_retained_size=; db_file_multiblock_read_count=; 并行系数; 这测试结果太不稳定了如果只要分钟的话 !那直接用就是了还整这么费劲干啥? 咳咳多测试几次吧刨根问底~ SYS TEST SQL> drop index IX_ALOTPRM_MTRL_LOT_ID; 索引已删除 我决定drop掉INDEX然后回收tablespace再测试一次 SYS TEST SQL> select sum(bytes)/// sumbytes from dba_extents a where tablespace_name=USERS / SUMBYTES
SYS TEST SQL> select nameround(to_number(bytes///)) as bytes from v$datafile; NAME BYTES E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_SYSTEM _TJTPGN_DBF E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_UNDOTB S_TJTPL_DBF E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_SYSAUX _TJTPJ_DBF E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_USERS_ TJTPL_DBF NAME BYTES E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_EXAMPL E_TJTRPY_DBF 收缩之~ SYS TEST SQL> ALTER DATABASE DATAFILE E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_USERS_TJTPL_DBF RESIZE M; 数据库已更改 接着继续~ 我想了想会不会与这个测试DB的内存分配有关系? SYS TEST SQL> show sga Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes SYS TEST SQL> show parameter pga NAME TYPE VALUE pga_aggregate_target big integer M SYS TEST SQL> 把参数改小点 M+ DB_file_multiblock_read_count+并行 sort_area_size=; sort_area_retained_size=; db_file_multiblock_read_count=; 还有并行系数 把DB的内存分配得稍微大一点 SYS TEST SQL> show sga Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes SYS TEST SQL> show parameter sga NAME TYPE VALUE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer M sga_target big integer M SYS TEST SQL> show parameter pga SYS TEST SQL> CREATE INDEX FU_DBIX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) parallel(degree ) ONLINE NOLOGGING; 索引已创建 已用时间: : : 搞来搞去还是整了分钟 再来一次不并行看看如何~ sort_area_size=; sort_area_retained_size=; db_file_multiblock_read_count=; SYS TEST SQL> alter session SET workarea_size_policy=MANUAL; 会话已更改 已用时间: : : SYS TEST SQL> alter session set sort_area_size=; 会话已更改 已用时间: : : SYS TEST SQL> alter session set sort_area_retained_size=; 会话已更改 已用时间: : : SYS TEST SQL> alter session set db_file_multiblock_read_count=; 会话已更改 已用时间: : : SYS TEST SQL> CREATE INDEX FU_DBIX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING; 索引已创建 已用时间: : : 再多测一次再去吃饭 删索引 回收tablespace 总结一下 我很晕了为什么不并行反而更快? 我估计和具体的场景 硬件条件 系统并发负载 还是有所联系不然大牛们一直都说大表建索引必须 session级别增大sort_area_size + nologging + 并行 看样子过段时间 找台 CPU的再试试吧 条件 时间
手动G+Multi block read count +并行 workarea_size_policy=MANUAL; alter session set sort_area_size=; alter session set sort_area_retained_size=; : : alter session set db_file_multiblock_read_count=; parallel(degree ) ONLINE NOLOGGING;
啥事不干直接建 CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING; : :
我手多又测试了一遍第一种情况 workarea_size_policy=MANUAL; alter session set sort_area_size=; alter session set sort_area_retained_size=; : : alter session set db_file_multiblock_read_count=; parallel(degree ) ONLINE NOLOGGING;
手动M+Multi block read count +并行 workarea_size_policy=MANUAL; sort_area_size=; sort_area_retained_size=; : : db_file_multiblock_read_count=; parallel(degree ) ONLINE NOLOGGING;
手动M+Multi block read count +无并行 试试 workarea_size_policy=MANUAL; sort_area_size=; sort_area_retained_size=; : : db_file_multiblock_read_count=; 无并行
再来一次手动M+Multi block read count +无并行 workarea_size_policy=MANUAL; sort_area_size=; sort_area_retained_size=; : : db_file_multiblock_read_count=; 无并行
再来一次啥事不干直接建 CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING; : :
若是不并行的话Process 里会看到一个 CPU使用蛮高的TID而且只有一个核在激烈的WORK 若是并行的话这里会看到好几个PID在work |
