利用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级别是可以动态修改的)。

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Comments

  1. admin says:

    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.

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569