Re: temp tablespace groups
From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Thu, 25 Feb 2010 17:33:10 -0800 (PST)
Message-ID: <620637.97531.qm_at_web111714.mail.gq1.yahoo.com>
I LOVE this feature! :) I use temp tablespace groups, consisting of three temp tablespaces, spanned across my three fusion I/O drives. My waits on temp reads and writes and now 0 for all of my marts since moving to this configuration.
The key is first to ensure that the right "schtuff" is using temp, so I always tune first before moving to this configuration and then ensure that your tempfiles are all the same size and striped evenly between the tablespaces.
I've found significant benefits to parallel hash joins that have "swapped" to temp using temp tablespace groups, but not as crazy about the bizarre long op times I initially receive for some buffer sorts and rowid range scans. Given a minute, the estimated times come back to something a bit more accurate, but it can be disconcerting when first experienced.
Let me know if you need any specifics from me, glad to share!
Date: Thu, 25 Feb 2010 17:33:10 -0800 (PST)
Message-ID: <620637.97531.qm_at_web111714.mail.gq1.yahoo.com>
I LOVE this feature! :) I use temp tablespace groups, consisting of three temp tablespaces, spanned across my three fusion I/O drives. My waits on temp reads and writes and now 0 for all of my marts since moving to this configuration.
The key is first to ensure that the right "schtuff" is using temp, so I always tune first before moving to this configuration and then ensure that your tempfiles are all the same size and striped evenly between the tablespaces.
I've found significant benefits to parallel hash joins that have "swapped" to temp using temp tablespace groups, but not as crazy about the bizarre long op times I initially receive for some buffer sorts and rowid range scans. Given a minute, the estimated times come back to something a bit more accurate, but it can be disconcerting when first experienced.
Let me know if you need any specifics from me, glad to share!
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
- On Thu, 2/25/10, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
From: Niall Litchfield <niall.litchfield_at_gmail.com>
Subject: temp tablespace groups
To: "ORACLE-L" <oracle-l_at_freelists.org>
Date: Thursday, February 25, 2010, 3:23 PM
I'm interested in how, if at all, folk use this feature for dw environments.
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 25 2010 - 19:33:10 CST