利用dbms_system包加速imp导入数据时的索引建立

imp数据导入时往往大多数的时间都消耗在了索引建立上,我们可以通过为导入会话设置一系列session级别的参数来加速索引的建立:

begin
  dbms_system.set_int_param_in_session(&sid,
                                       &serial,
                                       'db_file_multiblock_read_count',
                                       64);
  dbms_system.set_int_param_in_session(&sid,
                                       &serial,
                                       'sort_area_size',
                                       209715200);
  dbms_system.set_int_param_in_session(&sid,
                                       &serial,
                                       '_sort_multiblock_read_count',
                                       64);
end;

注意在PGA自动管理模式下(即当workarea_size_policy=AUTO时),自行指定的sort_area_size参数将无法生效。MOS上Bug 8939043叙述了目前dbms_system包的功能仅能修改session级别的布尔和数值类型参数,而无法修改字符串类型参数的问题;所以目前也还无法动态修改其他会话中的workarea_size_policy参数(虽然这个参数在session/system级别是可以动态修改的)。


Posted

in

by

Tags:

Comments

One response to “利用dbms_system包加速imp导入数据时的索引建立”

  1. admin Avatar
    admin

    Hdr: 8939043 11.1.0.7 RDBMS 11.1.0.7 SQL LANGUAGE PRODID-5 PORTID-267
    Abstract: SET A CHARACTER-STRING VALUED PARAMETER IN ANOTHER USERS SESSION

    Using DBMS_SYSTEM you can set a Boolean parameter with the
    SET_BOOL_PARAM_IN_SESSION procedure. Similarly, you can also set an Integer
    parameter with the SET_INT_PARAM_IN_SESSION procedure.

    There is no procedure in dbms_system or in other package or any other direct
    method to set this parameter.
    so, it should be a direct method to simulate something like

    alter session set cursor_sharing=’EXACT’;

    We have a specific process that is running very slowly since upgrading to
    11g. If we set optimizer_features_enabled =11.1.0.7 the process runs well.
    We can not set this parameter at the database level though as it causes
    online errors in other parts of the system.

Leave a Reply

Your email address will not be published. Required fields are marked *