| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tuning ideas requested
Hi!
How much memory do you have in server? With sort_area_size of 1GB,
hash_area_size will default to 2GB.
When looking at your execution plan, I think that there are two slaves for
reading data (one for each table), two slaves for doing hash join (both can
allocate up to 2GB for hashing!) and after first slaves have finished, these
can start order by (can allocate additional 1GB memory per slave, or even
more in some circumstances!).
Thus, if you don't have that much memory in your server, go with smaller sort_area_size. But, since you are forcing hash join, setting hash_area_size too small will degrade joins performance and hit your temporary tablespace IO a lot. You should have analyzed your data well and should trace hash joins with event 10104, to at least verify you don't have "Number of rows iterated over" lines in your trace file (I means that because of incorrect statistics and small hash_area_size even a single build partition doesn't fit into memory and the same probe partition has to be re-read again and again for every part of build partition).
You can get parallel execution & table queue information from v$pq_tqstat - but I believe you have to run your query (with small amount of rows) first, then check the view in the same session where you executed your query.
Btw, is your IO fast enough to cope with this kind of parallel degree? You should also test, what happens with smaller degree, or modifying your cursors to perform serial operations in parallel on different sets of data. Actually, you should first look at insert /*+ APPEND PARALLEL */ select .... statement without any pl/sql cursors and cursor loops to move data to another tablespace and create additional columns, it can be a lot faster, especially with parallel execution.
Btw, I wouldn't delete records from temporary product listing table, I'd just update some rows status to say that it's corresponding values have been processed...
Tanel.
> 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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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 - 06:34:24 CDT
 |
 |