Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: need opinion on design

Re: need opinion on design

From: oracle dba <oracle12i_at_hotmail.com>
Date: Mon, 04 Feb 2002 19:57:00 -0800
Message-ID: <F001.00405B0D.20020204193031@fatcity.com>

Hi Stephane,

Thanks for the input. I was also considering letting the queries hit the OLTP database for up to minute information. But, according to the developers and management, they insist that there will be many instances and lots of queries require up to minute stuff. To give you a bit of background, I am new to this place and they spend a year developing this thing without a database architect or DBA, and now want this thing tuned within 3 weeks. None of the developers are database literate. They commit on every insert during a large load, they didn't close open cursors, they select max() from the id column to figure out what the next id should be instead of using a sequence, etc. It's quite a mess. Without looking at anything, I made a query that takes 3 minutes down to 0.6 sec. To me this is an perfect example of how you should not write a SQL and database application. But they did.

Yes, you are right that I will be the one to keep this thing ticking, but I am also the one that need to tune EVERYTHING in 3 weeks. And what's worse, some developers are pretty defensive when it comes to talk about their code. :(

Rich

>From: Stephane Faroult <sfaroult_at_oriole.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: need opinion on design
>Date: Mon, 04 Feb 2002 11:51:22 -0800
>
>oracle dba wrote:
> >
> > Hi fellow DBAs,
> >
> > I trying to figure out the best way to do this and would like to
> > hear what you think of it.
> >
> > I took over this database which has a ACTIVITY table in it. This is
> > a very large table which get's million records per day. The records
> > coming from another server. And we also need to keep like 2 months
> > worth of the records in it for billing purposes.
> >
> > So, because of the two very different types of activities on the table.
>I
> > am splitting it into two databases. One is the LIVE database which I
> > will only keep like 1 day worth of data and tune it for handling
> > high volumn of transaction. Then I am building a DW
> > which will store up to 2 months of activities for billing purposes and
> > provisioning, and tune that for big queries. So far so good. Right?
>
>Right.
>
> > Now, there is a requirement that the provision application which will
> > use the DW needs to see (some times) up to minute data for the
> > ACTIVITY table. So here is where I am trying to figure out the best
> > way to do this.
> >
> > o I don't want the application to hit the LIVE database for getting
> > up to minute data, fearing that it will impact the performance on the
> > LIVE side.
> > o That means a daily load into the DW is not good enough.
> > I will need to replicate the data into DW more frequently, like at
> > minutes interval.
> >
> > So here is my main question: If I do that, should I in the DW split the
> > ACTIVITY table into two like the ACTIVITY and ACTIVITY_HISTORY or just
> > keep one ACTIVITY table? Now one thing, regardless whether I split
> > the table or not. The large table has to be partitioned.
>
>Do not split. Partitions are just for that.
>
> > The reason I am thinking about splitting the table is so most of
> > the long running queries will be running against the A_HISTORY
> > table. And only when the up to minute data is needed, then the
> > app will use the ACTIVITY table in the DW. Or maybe this is not
> > necessary since if I partition the table, then the replicated data
> > coming from the LIVE database will go to one partition while most
> > of the queries will be hitting other partitions?
> >
> > Please let me know what do you think if this. Any feedbacks are
> > welcome.
> >
> > Rich
>
>Rich,
>
> Are you really sure that your 'up-to-the-minute' queries need to run
>on the DW database? 'up-to-the-minute' doesn't seem to me of the same
>order of magnitude. A bit like those people who size multi-million rows
>table by specifying that the size will be 3,786,975,908,345 bytes when
>the table is full.
>If those 'up-to-the-minute' query only bear, as it is likely, on the
>most recent data, I think that you *can* hit the OLTP database, because
>it's a kind of OLTP query. Simply, avoid joins of death trough database
>links, they are performance killers. Try either to define on the OLTP
>database a (simple) view which returns what will be needed, or possibly
>a procedure which you can call remotely.
>Try to see with your users what they exactly need, they may realise that
>it's simpler than what you are ready to put in place. Remember that YOU
>will have to keep everything ticking.
>
>--
>Regards,
>
>Stephane Faroult
>Oriole Ltd
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).



Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: oracle dba
  INET: oracle12i_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Feb 04 2002 - 21:57:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US