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

Home -> Community -> Usenet -> c.d.o.server -> Re: 9i streams vs triggers

Re: 9i streams vs triggers

From: servant <mjohns1_at_uhc.com>
Date: Fri, 10 Jan 2003 08:26:23 -0600
Message-ID: <avml6j$7k4$1@gabriel.uhc.com>


Part of the problem is that I am new to the project and do not yet understand the amount of traffic this database will support. It is running on a 2 processor RS6000 B80 under AIX which isn't the smallest machine but isn't the biggest either. Until I get a better understanding, I need to trust that the technical architect is accurately describing the need for reporting to hit another database. I agree that RDBMS's can handle A LOT of traffic. I am new to Oracle but have seen Sybase and DB2 do some awesome things.

As I understand ETL, basically that is what I am talking about. I am extracting data from one database, applying business logic to it, and loading a separate database. Unfortunately, the reporting requirements dictate that I do this in near real-time. Otherwise, I could write a script that kicks off at night and the architecture becomes much cleaner.

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:LjtT9.6039$%n.1146_at_sccrnsc02...
> Sybrand,
> I found in SQLServer I had to create a whole other machine and replicate
to
> it just for some frigging reports! The 4 CPU 4 GIG W2K server with 50 -
100
> concurrent (light users) users couldn't handle some basic reporting at the
> same time. Too much contention. So this "cheaper" RDBMS SQL Server is
> really much more expensive than a 2 processor Sun box w/ Oracle (1
machine,
> 1 license, 1 backup, less hardware)
>
> So perhaps this is where servant is coming from. Servant, let me assure
you
> that Oracle is a pretty robust puppy and can certainly handle OLTP and
some
> reports on the same box. If you need a Datawharehouse environment the
> schema is probably going to be much different anyway. In that case you
are
> going to do ETL to someother Oracle database anyway.
>
> Jim
>
>
>
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can
figure
> it out.
> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> news:77pr1v42q0kef48ad29cacqjvsinbqp6vl_at_4ax.com...
> > On Thu, 9 Jan 2003 13:06:43 -0600, "servant" <mjohns1_at_uhc.com> wrote:
> >
> > >I have been tasked with developing a reporting architecture for an
> > >application that has been developed over the last few months. I have
> been
> > >told there are 2 main concerns: performance (contention) and complexity
> of
> > >the data model. I am thinking of using Oracle's replication to
maintain
> a
> > >(near) real-time replica. That (basically) resolves the contention
> issue.
> > >The complexity issue is more difficult.
> > >
> > >I am thinking about using 1) streams against the redo logs or 2)
triggers
> to
> > >pull the data from the replica, apply business logic to it, and
populate
> a
> > >"logical" data model on which reports can be generated. I would like
to
> use
> > >the same abstraction layer to go from the physical to the logical data
> model
> > >as the application interface uses (Java objects with EJBs). In other
> words,
> > >I need the code inside the streams or triggers to call Java methods
(ie:
> > >loadCustomerData(cust_id)) which would get the data and apply any
> business
> > >logic and then populate the "logical" database.
> > >
> > >In essence, I think I am being asked to create a data warehouse, but I
> > >haven't convinced myself of that yet.
> > >
> > >Anyway, here is my question: Does anyone have an opinion on which
method
> > >(streams or triggers) would tax my replica database more--from a CPU,
> > >network, and/or contention perspective? Any opinions from other
> > >perspectives which option is better (maintenance, etc).
> > >
> > >
> >
> > First of all I would like to question the need of a replica database
> > for reporting purposes. As for contention, this is simply not an
> > issue, if the application has been designed properly. Your post has a
> > distinct smell of finding a 'hardware solution' read 'workaround' for
> > what is in reality a software design problem.
> > If you really think you need a hardware solution you could just as
> > well consider RAC, to have multiple servers running on the same
> > instance.
> > As for your option 1) you seem to be trying to re-invent the standby
> > database technology . One simple advice: don't. You would never get it
> > work, and provided your organisation has still a bag of $$ floating
> > arond: get Oracle Enterprise Edition, set up a standby database which
> > will use the redolog to keep the standby database up-to-date and start
> > living
> > As for your option 2) this seems like yet another solution which is
> > going to be designed in a bike-shed. Replication can be accomplished
> > by setting up the Advanced Replication option, and designing
> > 'home-grown' solutions will definitely make you bald sooner and cost
> > more.
> >
> > Regards
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
Received on Fri Jan 10 2003 - 08:26:23 CST

Original text of this message

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