Solved

Ramdisk vs tmpfs on Solaris for Oracle temp datafile

Posted on 2011-03-08
13
1,509 Views
Last Modified: 2013-12-28
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
0
Comment
Question by:cbowman1
  • 5
  • 4
  • 4
13 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you are using DATAFILES for TEMP,  you should consider switching to TEMPFILES.

Especially if you are going with ramdisks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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?
0
 

Author Comment

by:cbowman1
Comment Utility
My mistake, it will be tempfiles (TEMPSEGS2) used by batch jobs, not a datafile.

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------  
SYSTEM                         ONLINE    PERMANENT
TABLES                         ONLINE    PERMANENT
TEMPSEGS                       ONLINE    TEMPORARY
TEMPSEGS2                      ONLINE    TEMPORARY   <<< This one used by batch jobs.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 

Author Comment

by:cbowman1
Comment Utility
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.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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.

http://www.dba-oracle.com/oracle_tips_sorts.htm
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.






0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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?
0
 

Author Comment

by:cbowman1
Comment Utility
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 (9.2.0.8), 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 9.2.0.8. 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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
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!


0
 

Author Comment

by:cbowman1
Comment Utility
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now