Hi Everyone, I apologise in advance for the long post - I want to explain the situation clearly though. If tuning large queries isn't your thing you can probably skip this.
I have to do some large data conversion activities and I'm looking for suggestions to tune a fairly average query. Let me try to briefly explain the situation: 1) A history table containing ~150million records needs two attributes added. 2) One of these attributes has a constant value assigned for all existing records, the other attribute is populated based on a rule which can be achieved using a pair of analytical functions. 3) The opportunity to change tablespace and redefine one index is being incorporated The approach so far is as follows: 1) Rename existing table 2) Define empty table in new tablespace, do not create any indexes 3) Create a temporary table listing the unique products (around 1.4million products exist in the table having on average 100 records each) and a required value 4) Cursor through the products applying the statement below in batches 5) Insert records into new table 6) Delete products from temporary driving table 7) Commit steps 5 and 6 and repear cursor in step 4 until complete 8) Create indexes, drop temporary table, etc The temporary table deletion is used to provide a restart capability in the event of failure. The statement to create records is: SELECT /*+ use_hash(m th) parallel(m 4) parallel(th 4) */ th.<several fields>, m.version - COUNT (1) OVER (PARTITION BY sourceguid) + ROW_NUMBER () OVER (PARTITION BY sourceguid ORDER BY creationdate) VERSION FROM history th, driving m WHERE th.sourceguid = m.productguid AND m.rownumber >= 1 AND m.rownumber <= <magic number> Before execution I changed the sort_area_size to 1GB. I tried using a "magic number" of 100,000 which resulted in about 17million records being created in ~40 minutes, I then tried a magic number of 200,000 which caused the following error after ~20 minutes: ERROR at line 8: ORA-12801: error signaled in parallel query server P001 ORA-04030: out of process memory when trying to allocate 8192 bytes (sort subheap,sort key) An autotrace of the 100,000 execution provided the following: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=739255 Card=99979 By tes=5998740) 1 0 WINDOW* (SORT) :Q607440 03 2 1 HASH JOIN* (Cost=738220 Card=99979 Bytes=5998740) :Q607440 02 3 2 TABLE ACCESS* (FULL) OF 'DRIVING' (Cost=761 Card=99979 By :Q607440 tes=1499685) 00 4 2 TABLE ACCESS* (FULL) OF 'HISTORY' (Cost=737374 :Q607440 Card=149171321 Bytes=6712709445) 01 Statistics ---------------------------------------------------------- 229 recursive calls 2811 db block gets 4875215 consistent gets 5055303 physical reads 652 redo size 1297355183 bytes sent via SQL*Net to client 117980660 bytes received via SQL*Net from client 1062887 SQL*Net roundtrips to/from client 12 sorts (memory) 2 sorts (disk) 15943284 rows processed This is being executed on Oracle 8.1.7.4 on a 6-CPU Sun E4500. The test query was executed using SQL*Plus locally on the server using "set autotrace traceonly". During the actual execution another similarly demanding, yet different, script may also be executed. Questions: 1) Does my approach (cursoring through several iterations to manage sort size) seem valid? Is there a better approach? 2) Are there other parameters to consider tuning to suit this type of query? Normally this is a busy OLTP system with a 2M sort area size so the system isn't configured for this type of query normally. 3) Is there anything in particular I should be monitoring? I was watching "DML Processes" using TOAD during the first execution and it appeared that only 1 or 2 parallel slaves were reading at any one time - is this expected? Thanks for your advice. In return, I will write a summary when the exercise is complete. Regards, Mark. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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).