Re: RAMSAN Experience
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: Rob.Dempsey@xxxxxxxxxx
- Date: Wed, 9 Sep 2009 13:54:53 -0700
First, I will award you negative DBA tuning points for even suggesting
to use underscore parameters as tuning "knobs". This is the wrong
approach. And for the others that do the same, take heed (yes, I am
on a soap box now).
Let's first understand some things about PX: IIRC if the table is
smaller than the small table threshold, it can be put into the db
cache and read serially, but most tables in a DW do not fit this
requirement so then will be physically read off of disk each and every
time a PX scan is done. This means the system needs to support a
fairly high number of MB/s (GB/s) of disk throughput, especially if
there is either a large number of concurrent queries or a high (for
the system cpu count) DOP or both. This changes some with 11gR2 and
the in-memory PX option but lets skip that for now.
With a 26GB and a 3GB table we're really not talking very big amounts
of data here, so what is the physical memory size and how are you
using it?
How many and what kind of CPUs?
What is your storage connectivity (#HBAs and speed) and number of
spindles the db is on? Dedicated or shared storage?
How much disk bandwidth (GB/s) can you drive from this host to the storage?
How many concurrent queries and at what DOP is the workload?
If you plan is to add SSD for temp space, my recommendation would be
not to bother. What you need to investigate is why the HJ is spilling
to disk and see if you can prevent it. First, I would check the
execution plans and validate the cardinality of the row sources. Then
you may need to add extra memory to pga_aggregate_target or to
increase the DOP. By increasing the DOP you will give the query more
PGA memory to address as it is partly limited by the number of PX
slaves; so 8 slaves can address 2x the memory that 4 slaves can, given
its availability. It also could be a data skew issue or a join skew
issue.
If you plan to add SSD for redo logs in a DW I would ask: Why are you
not loading it via parallel direct path and avoiding redo? Why write
redo to expensive SSD when you can avoid writing it at all?
So in the end I don't think SSD will give you anything but a hole in
your company's pocketbook.
On Wed, Sep 9, 2009 at 8:29 AM, Rob Dempsey<Rob.Dempsey@xxxxxxxxxx> wrote:
> 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.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
