Re: 9i streams vs triggers

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