RE: High Temp Usage - need help to do findings
From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Thu, 25 Aug 2011 13:08:57 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB420450E7C_at_LITIGMBCRP02.Corp.Acxiom.net>
Are you sure it's your db's temp segment in TEMP or a temporary segment in TAXDATA? When you run a CTAS the segment being built is listed as a temporary segment until the command completes, but storage throughout the process is in your destination tablespace. Also, with parallelism each PX slave creates it's own temporary segment that is merged in the end as the final table.
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
Thanks,
Ann
Date: Thu, 25 Aug 2011 13:08:57 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB420450E7C_at_LITIGMBCRP02.Corp.Acxiom.net>
Are you sure it's your db's temp segment in TEMP or a temporary segment in TAXDATA? When you run a CTAS the segment being built is listed as a temporary segment until the command completes, but storage throughout the process is in your destination tablespace. Also, with parallelism each PX slave creates it's own temporary segment that is merged in the end as the final table.
An xplan should make some of this more clearly seen.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Shastry(DBA)
Sent: Thursday, August 25, 2011 6:55 AM
To: oracle-l
Subject: High Temp Usage - need help to do findings
Hi All,
I am having the below query running on one of my prod instance and consuming 8GB of temp space and failing sometimes. Would you please help and suggest some approach to find why its consuming so much of TEMP usage?
CREATE TABLE /*+APPEND PARALLEL(8, 4) */ COOKINGS_W_264_c
TABLESPACE taxdata PARALLEL (DEGREE 8 INSTANCES 4) COMPRESS FOR ALL OPERATIONS AS SELECT /*+ APPEND PARALLEL(v, 8, 4)*/ v.* FROM vw_COOKINGS_W v WHER E comparison_basis = 'Current' AND v.fiscal_month_key =264
Thanks,
Ann
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 25 2011 - 08:08:57 CDT