Oracle 9i: Setting sort_area_size at session level has no effect
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle 9i: Setting sort_area_size at session level has no effect

  1. #1
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29

    Oracle 9i: Setting sort_area_size at session level has no effect

    Dear All,

    Yesterday, I tried to add a new index to a table. The index size was about 600Mb. In order to speed up the process, I increased the sort_area_size to 400Mb. Here are the commands:

    alter session set sort_area_size=409600000;
    alter session set sort_area_retained_size=409600000;
    create index ind01 ....;

    In another session, I monitored the sort usage in v$sort_usage table, I noticed that the create index operation was still using over 600Mb in the temp tablespace. Does that mean sort_area_size has no effect on create index, rebuild index, or this kind of operations?

    Regards,

    -- Chris

  2. #2
    Join Date
    Jul 2006
    Posts
    195
    I usually try to take advanage of everything I can, memroy, I/O, CPU's.
    Of course, you need to tune the parameters for your machine.


    select systimestamp(1) from dual;
    alter system parallel_max_servers=16
    alter session set db_file_multiblock_read_count=128; -- or 64 if the storage is not optimized for 1M IO size
    alter session set workarea_size_policy='manual';
    alter session set sort_area_size=134217728;
    alter index test1.tab1_idx rebuild parallel 16;
    alter index test1.tab1_idx noparallel ;
    alter system parallel_max_servers=4
    select systimestamp(1) from dual;

    Also, you may want to consider getting off 9i and moving to a newer version
    of Oracle.

    Good luck

  3. #3
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29
    Thanks a lot for your suggestion. I will try that out.

    Regards,

    -- Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width