Script:Speed Up Large Index Create or Rebuild

以下脚本可以用于加速大表索引的创建或重建

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

-- Script Tested above 10g
-- Create a new temporary segment tablespace specifically for creating the index.
-- CREATE TEMPORARY TABLESPACE tempindex tempfile 'filename' SIZE 20G ;
-- ALTER USER username TEMPORARY TABLESPACE tempindex;

REM PARALLEL_EXECUTION_MESSAGE_SIZE can be increased to improve throughput.
REM but need restart instance,and should be same in RAC environment
REM this doesn't make sense,unless high parallel degree

-- alter system set parallel_execution_message_size=65535 scope=spfile;

alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;

alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;

--In conclusion, in order to have the least amount of direct operations and
--have the maximum possible read/write batches these are the parameters to set:

alter session set events '10351 trace name context forever, level 128';

REM set sort_area_size to 700M or 1.6 * table_size
REM 10g bug need to set sort_area_size twice
REM remember large sort area size doesn't mean better performance
REM sometimes you should reduce below setting,and then sort may benefit from disk sort
REM and attention to avoid PGA swap

alter session set sort_area_size=734003200;
alter session set sort_area_size=734003200;

REM set sort area first,and then set SMRC for parallel slave
REM Setting this parameter can activate our previous setting of sort_area_size
REM and we can have large sort multiblock read counts.

alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;

alter session enable parallel ddl;

create [UNIQUE] index ...     [ONLINE] parallel [Np] nologging;

alter index .. logging;
alter index .. noparallel;

--TRY below underscore parameter while poor performance 

--alter session set "_shrunk_aggs_disable_threshold"=100; 

REM   _newsort_type=2 only works if the patch for bug:4655998 has been applied
REM   The fix for bug:4655998 has been included in the 10.2.0.4 patchset.
REM   got worse in most cases

--alter session set "_newsort_type" = 2; 
OR  
--alter session set "_newsort_enabled"=false;                        then use Sort V1 algorithm,got worse in most cases

rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!IMPORTANT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
rem If the performance of a query has degraded and the majority of the
rem time is spent in the function kghfrempty, and the function that called
rem kghfrempty was kxsfwa called from kksumc, then you may be encountering
rem this problem.
rem Workaround:
rem Reducing sort_area_size may help by reducing the amount of memory that
rem each sort allocates, particularly if the IO subsystem is underutilized.
rem The performance of some queries that involved large sorts degraded due
rem to the memory allocation pattern used by sort.
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

REM setting below parameter only if you are loading data into new system
REM you should restore them after loading
--alter session set db_block_checking=false;
--alter system set db_block_checksum=false;

Comments

  1. admin says
    select x.ksppinm, y.ksppstvl
    from x$ksppi x , x$ksppcv y
    where x.indx = y.indx
    and x.ksppinm like '\_%db_file_direct_io_count%' escape '\' ;
    
    If this value is 1048576, increase the _hash_multiblock_io_count and 
    _sort_multiblock_read_count in such a way that db_block_size * 
    _hash_multiblock_io_count is equal to or half of _db_file_direct_io_count. 
    Similarly, use db_block_size * _sort_multiblock_io_count is equal to or half 
    of _db_file_direct_io_count. Note that workarea_size_policy should be manual.
    It is true that these values are calculated automatically since 8i but since 
    we are seeing only 7 blocks in the direct operations, you may want to check 
    these and see if they improve performance.
    Note that the read_count parameters can be set at session level:
    alter session set "_sort_multiblock_read_count" = 32;
    alter session set "_hash_multiblock_read_count" = 32;
    
  2. 有性能比较吗?使用或不使用这种方法。

    • 简单测试过,创建25g大小的索引,普通pc机不做任何优化大约要1个小时,优化后35分钟完成。
      因为之前经常有业务人员问我怎么加速索引创建或重建,所以萌发了把能优化的选项集中在一个脚本里的想法,但也仅是指导思想(绝大多数场景应当是有效的)。

      以上主要优化的几点:
      1.普通多块读和排序多块读的大小
      2.直接路径IO的大小,10351 event level 128
      3.内存排序空间的大小,10g中存在bug需要2次设置。在10g中针对parallel execution环境也需要设置_sort_multiblock_read_count。但是仅对能从内存获益的排序操作有利,适合大多数场景
      4.nologging
      5.并行,一般这个业务人员也会想到
      6.独立的临时表空间
      7.使用备选的排序算法_newsort_type或_newsort_enabled,一般不要用
      8.禁用block checksum/checking,不推荐,尽在新系统加载大量数据时使用

      是否有漏掉什么?给我提提建议

  3. maclean says

    注意 10g当中 很多人认为 设置 手动PGA 和 sort_area_size + Parallel 并行即可加快 索引创建/重建。

    其实是不对的, 10g中存在bug 需要手动设置alter session set sort_area_size 2次。
    且在10.2.0.3和10.2.0.4 11.1 存在另一个bug 会导致Parallel Slave子进程不继承之前设置的sort_area_size,该bug可能在所有操作系统平台上发生,见文档 After applying 10.2.0.3 PATCHSET, the value set for SORT_AREA_SIZE on a session level is not propagated to the slaves.

    该bug影响的版本有

    Oracle Server – Enterprise Edition – Version: 10.2.0.3 to 11.1
    This problem can occur on any platform.
    10.2.0.3 ,10.2.0.4
    11.1

    已验证的11.2没有该问题

    可以通过设置SMRC来解决这个问题, 文档中脚本注释了和解决了该问题。

    这可能和大多数人 认识的 parallel+ sort_area_size 增速索引create/recreate 存在差异。

    具体排序区域内存使用了多少 可以在sort发生时 使用Script:List SORT ACTIVITY脚本 检验
    以下脚本可以用于列出数据库内的排序活跃性能信息:
    http://www.oracledatabase12g.com/archives/script-list-sort-activity.html

  4. maclean says

    SQL> set timing on;
    SQL> create index large_index on sh.order_history(order_id,location,issue_date) tablespace users;

    Index created.

    Elapsed: 01:36:15.13
    SQL> select bytes/1024/1024/1024 from dba_segments where segment_name=’ORDER_HISTORY’ and owner=’SH’;

    BYTES/1024/1024/1024
    ——————–
    23.4970703

    SQL> select bytes/1024/1024/1024 from dba_segments where segment_name=’LARGE_INDEX’;

    BYTES/1024/1024/1024
    ——————–
    14.0009766

  5. maclean says

    SQL> alter session set workarea_size_policy=MANUAL;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL>
    SQL> alter session set db_file_multiblock_read_count=512;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session set db_file_multiblock_read_count=512;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session set events ‘10351 trace name context forever, level 128’;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session set sort_area_size=524288000;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session set sort_area_size=524288000;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session set “_sort_multiblock_read_count”=128;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session set “_sort_multiblock_read_count”=128;

    Session altered.

    SQL>
    SQL>
    SQL>
    SQL> alter session enable parallel ddl;

    Session altered.

    SQL>
    SQL> set timing on;

    io performance is poor here .

    SQL> create index large_index on sh.order_history(order_id,location,issue_date) parallel nologging tablespace users;

    Index created.

    Elapsed: 01:21:50.30

  6. maclean says

    SQL> alter session set workarea_size_policy=MANUAL;

    Session altered.

    SQL> alter session set workarea_size_policy=MANUAL;

    Session altered.

    SQL> alter session set db_file_multiblock_read_count=512;

    Session altered.

    SQL> alter session set db_file_multiblock_read_count=512;

    Session altered.

    SQL> alter session set events ‘10351 trace name context forever, level 128’;

    Session altered.

    SQL> alter session set sort_area_size=524288000;

    Session altered.

    SQL> alter session set sort_area_size=524288000;

    Session altered.

    SQL> alter session set “_sort_multiblock_read_count”=128;

    Session altered.

    SQL> alter session set “_sort_multiblock_read_count”=128;

    Session altered.

    SQL> alter session enable parallel ddl;

    Session altered.

    SQL> set timing on;
    SQL> create index large_index on sh.order_history(order_id,location,issue_date) nologging tablespace users;
    create index large_index on sh.order_history(order_id,location,issue_date) nologging tablespace users
    *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object

    Elapsed: 00:00:00.37
    SQL> drop index large_index;

    Index dropped.

    Elapsed: 00:00:02.01

    SQL> create index large_index on sh.order_history(order_id,location,issue_date) nologging tablespace users;

    Index created.

    Elapsed: 00:50:40.33

  7. maclean says

    just nologging

    SQL> set timing on;

    SQL> create index large_index on sh.order_history(order_id,location,issue_date) nologging tablespace users;

    Index created.

    Elapsed: 01:16:09.39

  8. yanggq says

    db_file_multiblock_read_count=512;
    这个设置的依据是什么?最大可以设置多大?
    另外有多少核/颗CPU?parallel ddl 的degree多少?

    • 这个参数实际受到OS的限制,设得过大会被自动降低; degree主要受到Io和CPu的影响,应当视乎情况具体决定。

Trackbacks

  1. […] Oracle Locks Script:列出用户表空间的定额 Backup Script:Expdp Schema to ASM Storage Script:Speed Up Large Index Create or Rebuild Script:列出失效索引或索引分区 Script:列出数据库中5%以上链式行的表 […]

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号