Re: Flash for TEMP
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: jhthomp@xxxxxxxxx, martin.a.berger@xxxxxxxxx
- Date: Thu, 20 May 2010 12:01:48 -0700 (PDT)
OK, here I go again...hand in fire... :)
Since I can't get everyone at my office to tune their SQL, (yes, use lots of
temp, first place I go look is at the code and find out where they are working
hard instead of smart...) I do not condone this as a replacement for good
ol'fashioned tuning, so please, PLEASE- investigate the code- Look at your
PGA, what's "not fitting" or multi-pass, i.e.
large hash joins you have that might process more efficiently if a "step
through" table was created.
Unnecessary "order by" statements, (you would not believe how often folks paste
in sql to code, not realizing how data had to be sorted when they were testing
is not necessary once they place it into a load or data process.. i.e. insert
into, select...order by...;)
Missing indexes, the old stand by tuning measure..who forgot one...
Too many indexes or incorrect indexing causing extra support and elapsed time,
domino effect...
Queries that do too much in a single step that broken down to multiple passes
would complete in record time and much less "swapping" to temp.
There are so many others, but it's a start...
Now, I have my mart on fusion I/O cards- not flash, but pretty darn good
performance vs. standard disk on reads/writes. The one thing I'm going to say
though, the most impressive feature to my efficient temp usage is temp
tablespace groups. This has granted the queries that are not tuned to what I
might like or tuned as much as we can to improve performance anywhere from
20-60%.
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...)
Two previous records before I implemented temp tablespace group:
- 30 feeds for a total of 16988371 records.
- 47 feeds for a total of 89102658 records.
Yesterday with the new temp tablespace group:
Wednesday- 78 feeds for a total of 154231551 records.
We are seeing the same improvements today and expect to see another record....
This did not count the other heavy processing that occurred in the environment
on some of the transactional tables that these feeds also need access to, so
these numbers really blew my manager's mind of how much I/O striping of temp on
top of LUN striping can benefit a I/O bound system for temp read/write waits...
You may want to, (after tuning, please, please...) look into temp tablespace
group feature if you do have waits on temp for reads/writes...
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 Wed, 5/19/10, Martin Berger <martin.a.berger@xxxxxxxxx> wrote:
From: Martin Berger <martin.a.berger@xxxxxxxxx>
Subject: Re: Flash for TEMP
To: jhthomp@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Wednesday, May 19, 2010, 1:46 PM
How much time spends your Application in IOs on the TempTS?
That's the maximum boost you can gain.
Martin
Am 19.05.2010 um 19:03 schrieb John Thompson:
> Anyone see any issues using an SSD mounted as a file system and used for the
> temporary tablespace? We have an application that uses temporary tables
> extensively, and I'm hoping to get a performance boost.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
