RE: RAMSAN Experience

A few questions for you, Rob:

1.  Are expectations of the users, in terms of response time, documented
so you know when they say "slow", what it means?
2.  Are all queries slow or just a few, against the tables you listed?
3.  The way the query is written, would it ever finish within the user's
expected time, regardless of hardware?

Sometimes it's a good idea to understand what's the best your hardware
could produce, in terms of throughput, and then check what's needed to
be read in a given query and compare that to the user's expectation.  If
what they wrote needs 6GB/s and your system can't do more than 5GB/s,
you need faster hardware, do less I/O, or change expectations.

There are a number of settings related to parallelism that can help, but
like other references to partition management, reducing I/O is your best
weapon.  

Are inline views involved and if so, is every column in the column list
necessary?  Sometimes people get lazy and just type "SELECT *" when they
really only need 1 or 2 columns.

Could a MV be used to do any work ahead of time?  For example, have the
MV do a join or column aggregation or provide a column subset of the
base table(s).

Just a few thoughts.

David C. Herring  | DBA, Acxiom Database Services
 
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rob Dempsey
Sent: Wednesday, September 09, 2009 10:29 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: RAMSAN Experience

Hi Tim / Greg

Thanks for the quick responses

I guess I should try and explain the problem a little bit more. This is
a simplified version (it will make a much longer email to detail all our
constraints and resources). We have a read only reporting application
that allows users to query raw row level data.  There are a number of
combinations a query can have be it date period, type of products etc
etc which makes it near on impossible for us to summaries the data -
trust me I would if I could. For our large system the two main tables
are in size

A       25690.25M
B      2955.25M

We use Oracle compression, pctfree 0, parallel query and partitioning on
date.  As our users seem to be addicted to response time being as low as
possible and not having enough expertise in storage one solution was to
set a db_cache_size that could accommodate all the table data and throw
it into memory. Simply a large in memory database. This solution has
worked very well for our smaller database, however as the data got
larger hash joins, group bys are spilling to disk. PGA is set to a large
value and my next point of call is to test different value for the
underscore parameters that control it.

We use EMC storage however the latest idea is to use RAMSAM for the
temporary files. I always thought it might be a good idea for the redo
logs but I am not sure about the TEMP files.

Like I said we have a number of constraints, but any help would be
welcome.

Rob 

-----Original Message-----
From: Greg Rahn [mailto:greg@xxxxxxxxxxxxxxxxxx] 
Sent: 09 September 2009 13:45
To: Rob Dempsey
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: RAMSAN Experience

Out of curiosity, how large is your DW and what is workload today?  Do
you use parallel query?

I ask because SSD is not really that beneficial for a DW, especially
when it comes to price/performance over spinning rust (HDDs).  SSD is
(mostly) about IOPS, not MB/s throughput which is where most DW are
sized inadequately.

On Wed, Sep 9, 2009 at 4:54 AM, Rob Dempsey<Rob.Dempsey@xxxxxxxxxx>
wrote:
> I have been asked to consider a RANSAN for our Oracle DW database.
>
> I was wondering if anyone had any experience of the technology? Pros
and the
> Cons.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l


***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

--
http://www.freelists.org/webpage/oracle-l

Other related posts: