Hi! > - Using NOLOGGING mode
Yes, definitely use nologging (if not having other constraints such physical standby database issues) > - Using PARALLEL option => PARALLEL 4 (the server has 4 CPU's) I would experiment with even more slaves. 6 for example. Then there would be more slaves per slave set for doing actual sorting (which is quite CPU intensive operation). Those slaves who are reading table data from disk are mostly waiting anyway, thus not using any CPU. You have to test it in your environment, I'm not 100% sure whether this could help in your case. > - Using NOSORT option => not applicable because the rows are not stored in the right order > - Creating an index first as unusable and then rebuilding it => this was tested after > the QA cutover but the duration remained unchanged The rebuild only helps in performace, when you already have a valid index on a table, which is fully synchronized with relevant columns in table. So, setting an index unusable will state that this index might not be in sync with data anymore. Thus for index rebuilding the whole table has to be scanned. But when doing a normal rebuild of a healthy index, only the index has to be scanned (with fast full scan), but sorting still occurs. The benefit comes from that index rows are usually lot smaller than table rows because they index only a subset of a row. Try to make an index on all columns of a table and rebuild it then, you shouldn't see any performance benefit compared to index building using full table scan. > - Increasing sort_area_size => has not yet been tested > Something else, any help will be appreciated... 1) Increasing db_file_multiblock_read_count *could* help a bit - that way full table scan can be done with fewer number of scattered reads, *if* your extent sizes permit it (multiblock reads aren't done over extent boundaries). 2) Increasing parameter CREATE_BITMAP_AREA_SIZE can help if you are building bitmap indexes. 3) Increasing SORT_AREA_SIZE will definitely help if it's currently small and you're dealing with huge data. If possible, avoid multi-pass sorts. You can check either statistic "workarea executions - multipass" to see if you're doing multipass sort or event 10032 trace and search for "intermediate runs". If you see them, you're doing a multipass sort which is hitting your temp TS really lot. 4) Also remember, you should mind about SORT_AREA_RETAINED_SIZE (the buffer for passing sorted results back to consumer), especially in case of parallel execution. Note that a single parallel slave can have SORT_AREA_SIZE allocated for sorting and the same time it can have a SORT_AREA_RETAINED_SIZE buffer allocated for passing results to other slaves at a given time. You don't want to overload your memory, so calculate these carefully. I'm not sure whether the parallel slaves inherit settings like sort_area_size from parent session correctly, thus you should verify it from v$sesstat statistics "session uga memory" and "session pga memory". 5) If in 9i, use WORKAREA_SIZE_POLICY=AUTO and set PGA_AGGREGATE_TARGET to amount of memory you want to allocate for all your sessions. And forget worrying about sort_* parameters. That makes your life a lot easier. 6) I think starting from some 8i version the parallel query slaves can read data directly to PGA when performing full table scans - relieving contention on buffer cache LRU lists, thus possibly increasing performance a little. You should verify that your compatible parameter isn't set too low for direct reads to occur. 7) If possible, you also might want to create a temporary temporary tablespace on dedicated or less loaded disks during the sorting and temporarily alter the index building user's temp tablespace to that one. Also you might want to match this temp tablespaces extent size with sort_area_size (Actually I'm not sure whether this is relevant with temporary LMT tablespaces anymore, but the optimal extent size used to be sort_area_size + standard db_block_size). If anyone recommends you putting temporary tablespace to a memory file system, don't do it - for regular sorting it's more reasonable to allocate this additional memory for sort_ & other areas. (note that this trick might help with global temp tables and I believe in some parallel execution cases as well). I hope that helped a bit, Tanel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
