-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|