Ramdisk vs tmpfs on Solaris for Oracle temp datafile

Does anyone have experience with using a ramdisk or tmpfs on Solaris SPARC 10. This filesystem space will be used for a 2hr test of Oracle global temp table performance to help prove in/out a io bottleneck. Looking for any benefit of ramdisk vs tmpfs command. Also any mount recommendations. Will be a 2gb filesystem. Oracle 9i. Temp setup for just a few hours of testing then back to the regular datafile setup for Oracle. Thanks Chris
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I cannot find the doc links to confirm this but a GTT should stay in memory.  I'm not sure is if uses TEMP if it cannot store it all or if it errors out.  Never ran into a situation and never bothered to look it up.

Sorting will take place in TEMP if necessary.

What I'm hinting at is if you have the free memory available to give to a TEMP segment in a ramdisk, then can't you give it to the PGA so TEMP isn't needed?

Stating in 9i sort_area_size is really no longer used if you use PGA_AGGREGATE_TARGET.

Also check out workarea_size_policy.

if you are using DATAFILES for TEMP,  you should consider switching to TEMPFILES.

Especially if you are going with ramdisks
slightwv (䄆 Netminder) Commented:
>> test of Oracle global temp table performance

Global temporary tables are already stored in memory.  What i/o performance issue are you wanting to prove or disprove?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

cbowman1Author Commented:
My mistake, it will be tempfiles (TEMPSEGS2) used by batch jobs, not a datafile.

------------------------------ --------- ---------  
SYSTEM                         ONLINE    PERMANENT
TABLES                         ONLINE    PERMANENT
TEMPSEGS                       ONLINE    TEMPORARY
TEMPSEGS2                      ONLINE    TEMPORARY   <<< This one used by batch jobs.
slightwv (䄆 Netminder) Commented:
I've seen debates over the years on using RAM disks for Oracle data files and they all typically end with:

If you have the RAM available for a temp datafile, then it's free for the SGA/PGA and you might not really need the temp datafile in the first place.
cbowman1Author Commented:
We have an extra large batch job that does a lot of sorting and uses a global temp table for its processing.

My thought was it starts in either real memory defined by the sort area size or the pga aggregate target  for the session.  Then if it get larger it would start using  tempsegs to use for sorting outside of what it can contain in real memory.  But my knowledge if GTT is limited so please advise. Thanks.
slightwv (䄆 Netminder) Commented:
sdstuber is a nuts and bolts tuning guy.  He will dig into these types of things for fun.

I'm sure he'll be back to this question as soon as he can.
You're both correct!

GTT's will be stored in TEMP segments.

However, if you do have memory to allocate to oracle, it will often be better used there than relegated to ramdisk for TEMP tablespace support.

BUT... it's definitely worth a 2 hour test.

A GTT will almost assuredly use some TEMP, especially if it might get large,  so putting that activity on ramdisk could help you.

One thing you want to watch out for is the "memory is fast trap"

Fast is still greater than zero.  If you're doing lots and lots of work, even if it's in memory you might still have a performance issue.  IO might not really be the problem.

Also,  if you write to a GTT, that generates undo and undo generates redo.  So, if your process iterates over lots of records inserting, updating and deleting them with the expectation that a temp table won't be incurring those logging costs you're mistaken.   They will be less than a normal table, but again, not-zero.

If you run your test and find your process didn't improve much, then IO isn't really the problem.  Check the app logic.  Are you doing lots of pl/sql looping through cursors updating a row or two at a time?

Can you do all that work in a SQL statement? processing all of the rows at once?
cbowman1Author Commented:
Wow, this site is great glad I subscribed. I'll read up on how to participate with the comments helpful, etc.

Quick background is the application moved from premise to hosted a month ago. Same Oracle rev (, same app rev (4 years on current version), OS went from Solaris 5.8 premise to 5.10 hosted and the hardware/disk/memory/cpu is all better and faster. Note: Server is T series now, not sure on old server. Read a white paper that T series may need tweaks for running Oracle well, especially batch.

Since the move to hosted everything, especially batch jobs, are running about 50% slower.  Have been working with the hosting vendor on tuning things (they have their own Oracle expert) but all these steps have done little to very minor increases in performance.  Went down this road once before with this vendor and it turned out to be an IO issue.  

The way they zone their servers, our hands are somewhat tied on what we can see. They claim everything is running great, not hardware or OS issue. My gut feeling is the Solaris 10 was staged odd and is not working well with They tend to not do actual installs of the OS but do some type of cloning.

Hosting vendor are now looking for some major app code rewrite but our app and Oracle has not changed.  The biggest rewrite push was against a batch job that writes to a GTT.  They want that moved into a plsql gen'd actual table. The code is not perfect but its fine in all other installations including the clients premise server.

I did a 10046 trace and tkprof from old/new and all the same execution plans are being used (its still using RBO). Same GTT statement at the top of the tkprofs but it runs a lot faster on old machine.

So to avoid the re-write, looking to show the batch writing to a GTT on a ramdisk has little performance improvement on the batch job. The move to rewrite it to a PLSQL block is unfounded.

Sorry long winded and too broad a comment but if you have any suggestions I'm listening. The initial question ramdisk or tmpfs is still the core of what I'm looking to understand for the exercise tonight.
slightwv (䄆 Netminder) Commented:
Can't help with the original question as it was posted.  My Oracle on Unix is old and it was HP only.  Hopefully some Solaris Experts can stop by later.

I guess as sdstuber stated, a quick test couldn't hurt to help troubleshoot a performance problem.  I would never attempt it for any other reason.

sdstuberConnect With a Mentor Commented:
If the only thing that changed is the host, then it's safe to say the host is the problem.
Just be sure that's the "only" thing that changed though.

>>>  They want that moved into a plsql gen'd actual table.

If by this they mean they want a pl/sql procedure to create a table on the fly and then manipulate it like you do the GTT and then destroy the table.  That's a HORRIBLE idea.
First, you'll incur the overhead of the table create/drop DDL.
Second, all activity on the table will generate redo directly, not just via undo.
Third, you lose concurrency safety

If that statement means something else, then I'll reserve judgment until I understand what it might mean.

Back to the core question, even if I could tell you page XXXX of manual YYYYY says your results will be ZZZZ.  I would still qualify it with "test it, verify the expectation for your exact hardware/software/data"

Since no such manual exists, best we can do is take educated guesses, which we've done above and general consensus is you won't see much difference; but of course, with all performance claims always

test it, verify the expectation for your exact hardware/software/data

good luck!

cbowman1Author Commented:
Thanks, a lot of good info. I ended up using a tmpfs below.  Couldn't find any diff between this and ramdiskadm. ramdiskadm wasn't loaded on the machine so really didn't end up being an option.

Peformance was slightly better on the older server with a smaller PGA. New sever didn't end up using the temporary tablespace for the sort so the perf was the same.

mkdir /tempram_dir
mount -o size=2048m -F tmpfs swap /tempram_dir
chmod 777 tempram_dir
create temporary
tablespace tempram
tempfile '/tempram_dir/tempram.1'
size 128M reuse
autoextend on next 128M maxsize unlimited
extent management local uniform size 128k

alter user dev temporary tablespace tempram;  

cd /tempram_dir
ls -lt
-rw-rw----   1 oracle   dba      134225920 Mar  8 18:33 tempram.1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.