Oracle-L: Tuning ideas requested

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Mon, 04 Aug 2003 23:39:23 -0800
Message-ID: <F001.005C8E89.20030804233923@fatcity.com>

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:

A history table containing ~150million records needs two attributes added.
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.
The opportunity to change tablespace and redefine one index is being incorporated

The approach so far is as follows:

Rename existing table
Define empty table in new tablespace, do not create any indexes
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
Cursor through the products applying the statement below in batches
Insert records into new table
Delete products from temporary driving table
Commit steps 5 and 6 and repear cursor in step 4 until complete
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: mrichard_at_transurban.com.au

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: ListGuru_at_fatcity.com (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).

Received on Tue Aug 05 2003 - 02:39:23 CDT