| 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
Tanel & Rafiq,
Thanks for your suggestions. The server has 6GB physical RAM, 10GB SWAP so you are probably right about the sort_area_size being too large when combined with parallel hints. I will look at other approaches to hash joins however given the window sort that is required I doubt I can process the entire table in one piece. In terms of temp tablespace we currently have 10GB configured.
I will compare the hash join approach to the nested loop approach if I have time today - And it looks like I should give more consideration to the parallel settings and their impact on memory usage.
Regards,
Mark.
"M Rafiq"
<rafiq9857_at_hotmai To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
l.com> cc:
Sent by: Subject: Re: Tuning ideas requested
ml-errors_at_fatcity
.com
05/08/2003 22:14
Please respond to
ORACLE-L
I shall add one more thing..In case of hash join/parallel sorts also watch usage of temp tablespace which may go very high with large sorts and may require a alrge temp tablespace.
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 05 Aug 2003 03:34:24 -0800
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.
> 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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- 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). _________________________________________________________________ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: rafiq9857_at_hotmail.com 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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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 - 18:49:24 CDT
 |
 |