Fw: Re: Flash for TEMP
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 May 2010 16:10:44 -0700 (PDT)
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@xxxxxxxxx> wrote:
From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
Subject: Fw: Re: Flash for TEMP
To: "Jared Still" <jkstill@xxxxxxxxx>
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@xxxxxxxxx> wrote:
From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
Subject: Re: Flash for TEMP
To: "Jared Still" <jkstill@xxxxxxxxx>
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_ 0zjs5t0qj244k
546 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@xxxxxxxxx> wrote:
From: Jared Still <jkstill@xxxxxxxxx>
Subject: Re: Flash for TEMP
To: kjped1313@xxxxxxxxx
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@xxxxxxxxx> wrote:
On Thu, May 20, 2010 at 12:45 PM, Jared Still <jkstill@xxxxxxxxx> wrote:
On Thu, May 20, 2010 at 12:01 PM, Kellyn Pedersen <kjped1313@xxxxxxxxx> 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
Other related posts:
- » Fw: Re: Flash for TEMP - Kellyn Pedersen
