Fw: Re: Flash for TEMP
Date: Thu, 20 May 2010 16:10:44 -0700 (PDT)
Message-ID: <953037.71408.qm_at_web114509.mail.gq1.yahoo.com>
Jared and others had some very valuable questions that I finally got out of meetings and was able to reply to him, (somewhat ungracefully...:)) and thought I would just forward this along that would also shed light on the fact of how much parallel, (yep, there's that PQ!!) that I have going on in my environments, (please feel free to cringe if you wish...I understand completely!! :))
I always build my temp tablespaces with files that alternate between drives so that when the parallel does stripe across the tablespaces involved, that they are able to benefit from this. Even if parallel is not present, I still have processes that will choose different temp tablespaces, which still out-performs a single temporary tablespace that will write to one tempfile, fill, then move to the next...
I'm still for keeping my temp tablespace use as light as possible- actually having an automated script that checks temp usage and if anyone goes over 100GB, it emails the distribution list. The developers refer to it as "Kellyn's Email of Shame"... :)
Kellyn Pedersen
Sr. Database Administrator
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, 5/20/10, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Subject: Fw: Re: Flash for TEMP
To: "Jared Still" <jkstill_at_gmail.com>
Date: Thursday, May 20, 2010, 5:03 PM
My turn to apologize, I keep forgetting that if I tab, Yahoo just decides you want to send the email, ready or not! :)
Another example of how this benefits though, even if I don't have parallel going is that different processes will choose different tablespaces for single processes-
SQL_TEXT SQL_ID
SID TABLESPACE OPERATION_TYPE PGA MB Mem MB Temp MB
create table LOAD.NI_tbl tablespace ram_ld_data1 98vjr70m4g7ft
461 TEMP_2 SORT (v2) 22 1023 37517
21 create table sj_tbl COMPRESS PCTFREE 0 tablespace sj_da b8w074wt86tm7
663 TEMP_1 GROUP BY (HASH) 996 979 870
22 create table sj_tbl COMPRESS PCTFREE 0 tablespace sj_da b8w074wt86tm7
671 TEMP_3 GROUP BY (HASH) 996 979 875
24 create table sj_tbl COMPRESS PCTFREE 0 tablespace sj_da b8w074wt86tm7
771 TEMP_2 GROUP BY (HASH) 996 979 874
25 create table sj_tbl COMPRESS PCTFREE 0 tablespace sj_da b8w074wt86tm7
703 TEMP_3 GROUP BY (HASH) 996 979 872
So temp_2 is being hit by the first process shown above, then the parallel has one on temp_1 and temp_2 and two processes hitting temp_3....
Hope this helps!
Kellyn
--- On Thu, 5/20/10, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Subject: Re: Flash for TEMP
To: "Jared Still" <jkstill_at_gmail.com>
Date: Thursday, May 20, 2010, 4:53 PM
Very important point, I should let the rest of the group know- I'll answer your two questions first about parallel- yes and yes... :) About 80% of our daily processing is parallel, (the original DBA group did NOT give this group good advice about WHEN to use parallel! )
So of course, my striping across the temp tablespaces appear something like this:
SQL_TEXT SQL_ID
SID TABLESPACE OPERATION_TYPE PGA MB Mem MB Temp MB
create table load.tbla as select * from ( select /*+ use_ 0zjs5t0qj244k 487 TEMP_2 HASH-JOIN 87 74 880 create table load.tbla as select * from ( select /*+ use_ 0zjs5t0qj244k546 TEMP_1 HASH-JOIN 87 74 880
create table load.tbla as select * from ( select /*+ use_ 0zjs5t0qj244k 455 TEMP_3 HASH-JOIN 87 74 880 Kellyn Pedersen
Sr. Database Administrator
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, 5/20/10, Jared Still <jkstill_at_gmail.com> wrote:
From: Jared Still <jkstill_at_gmail.com>
Subject: Re: Flash for TEMP
To: kjped1313_at_yahoo.com
Date: Thursday, May 20, 2010, 4:26 PM
And here I am bothering you again.
Please see the end - I'm keeping it all in order
On Thu, May 20, 2010 at 3:01 PM, Jared Still <jkstill_at_gmail.com> wrote:
On Thu, May 20, 2010 at 12:45 PM, Jared Still <jkstill_at_gmail.com> wrote:
On Thu, May 20, 2010 at 12:01 PM, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
I just moved from a temp tablespace of 700GB to a temp tablespace group, comprised of three tablespaces, 90GB each in our main production instance. Keep in mind, same drives, same I/O issues, right? Because I'm able to stripe across the tempfiles in each of the temp tablespaces instead of one tempfile sequentially, we saw the following improvements, (yes, these are real numbers just gathered from our analysts this morning...)
I'm wondering how that is different that making a temp tablespace with multiple files.
A followup on this from the docs:
"A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces."
Do you have parallel operations that are using the TBS group?
MOS Doc 272360.1 was more clear than the documentation
A tablespace group enables PARALLEL EXECUTION SERVERS
in a SINGLE PARALLEL OPERATION operation to use multiple temporary tablespaces
Really, should tech writers be required to have a solid grasp of grammar?
Jared
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 20 2010 - 18:10:44 CDT