Re: Best Practices for MultiTerrabyte TDW

On such a large Oracle database, without doing any partitioning the odds
are infinitesimal that any of TCRs gnarly joins would complete sufficiently
quickly for on-the-fly report snapshotting to not cause a brain aneurysm.
As Frank pointed out, the incredible wasted space from the TDW's purely
flat schema--where in extreme cases upwards of 75% of the row length could
be keyed off of via a join to a dimension table and thus written once
instead of millions of times--makes table scanning Oracle's M.O. for most
SARGable columns.  Might as well get an easel, a paper flip chart and some
colored markers and draw them yourself at that point.  ;P

If it must be on Oracle, I think you get the most bang for buck by
partitioning the hourly and daily tables by range using WRITETIME (a
modified form of it of course) as a key; maybe range the hourly by day and
the daily by week.  From there, anything you can do to ensure indexes are
entirely in cache is paramount.  Having full table scans on eight or
nine-figure tables is insane enough, but then you have to fetch 40% of the
data pages from disk each time?  That's just beyond Thunderdome.

Toben

On Thu, Jan 24, 2013 at 5:04 PM, Gregory Mendes
<[email protected]>wrote:

>
> Frank, that is another great solution.  Would I be correct to say, what
> I'm hearing about the WPA and SPA seems to be, they will most likely have
> issues with Oracle and this size of DB?    If so, then this is good to
> know because it could allow me to understandable future issues or problems
> with operations.  The main problem they have with TDW being on DB2 revolves
> around not having those resources.
>
> I need to think about how efficiently TCR can pull reports from a large
> database with this design.  They keep reminding me "life of the mission".
> :)  So, for me tuning the WPA and SPA and knowing the issues I can run in
> to with Oracle, is valualbe information.  If you know of any gotchas, let
> me know.
>
> I'll see you at Pulse.
>
> Thanks,
>
> Gregory R. Mendes
> 770-317-8593
>
>
> ------------------------------
> Date: Thu, 24 Jan 2013 16:46:10 -0500
> Subject: Re: [TME10] Best Practices for MultiTerrabyte TDW
> From: frank.tate-9LkaxTQlAnxWk0Htik3J/[email protected]
> To: [email protected]
>
>
> Ya know what? The entire datadase is flat, with absolutely NO
> relationships, so you *could* use the DB2 that you're licensed to use for
> the "live" TDW, then just copy whatever tables you want into Oracle. In
> this type of situation I've found that what the DBA and/or reporting group
> of the customer wants to do is something new/sexy/cool/experimental that
> the WPA and especially SPA have absolutely no chance of dealing with. So
> separating the live DB from the one they want to mess with can make a lot
> of sense. Just set up the documented maintenance scripts for db2 to run
> via.cron and it won't really need any other work.
>
> Just a thought.
>
> Frank - from my phone
> On Jan 24, 2013 11:54 AM, "Gregory Mendes" <[email protected]>
> wrote:
>
>  Hello All,
>
> I have a TDW (Oracle) that will be over 25 TBs.  I have the white paper or
> best practices for TDW but it doesn't address handling these types of large
> databases.
>
> I know about the TDW Load Projection spreadsheet but my customer refuses
> to use it.  So, they have calculated in their head what they think the size
> should be.
>
> So, do you guys/gals have any suggestion or tips to give?
>
> Regards,
>
> Gregory R. Mendes
> 770-317-8593
>
>
> _______________________________________________
> TME10 mailing list
> [email protected]
> Unsubscribe:[email protected]
>
>
> _______________________________________________ TME10 mailing list
> [email protected] Unsubscribe:[email protected]
>
> _______________________________________________
> TME10 mailing list
> [email protected]
> Unsubscribe:[email protected]
>
>


-- 
"Sometimes I think that's the only right thing to do:
To dream. to live in the world of dreams.
But it doesn't last forever--wakefulness always comes to take me back..."

_______________________________________________
TME10 mailing list
[email protected]
Unsubscribe:[email protected]