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

[cleegt]

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

[BeefStu]

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

[cleegt]

Thanks a lot for your suggestion. I will try that out.

Regards,

-- Chris