Re: RAMSAN Experience
Date: Wed, 9 Sep 2009 13:54:53 -0700
Message-ID: <a9c093440909091354v4ac5d9cawe898751bf3e30112_at_mail.gmail.com>
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_at_5one.co.uk> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 09 2009 - 15:54:53 CDT