FAQ
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

Search Discussions

  • Herring Dave - dherri at Aug 25, 2011 at 1:08 pm
    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 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
  • Kellyn Pot'Vin at Aug 25, 2011 at 1:17 pm
    Explain plan? Can you provide one with temp usage estimates, ( if you aren't sure how, feel free to email me, I'll be glad to help...)
    Kellyn Pot'Vin
    Sr Database Adminstrator and Developer
    Dbakevlar.com

    Sent from T-Mobile G2 with Google

    "Shastry(DBA)" wrote:
    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
    †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 25, '11 at 11:55a
activeAug 25, '11 at 1:17p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase