I am working for the Business Investigation Department. They requested
a database link to production so they can perform their research to
the production environment through this database link.
Their investigation consists of procedures that read files of any
number between 50,000 to 1,000,000 records and perform queries against
the production database - one by one. Since their customers make about
twenty to thirty different requests every day, they don't have time to
make up test data. Therefore they also wish to test their procedure
with production data using this same databalink link.

I believe this whole thing to be crazy. Can someone give me some very
good technical explanation and/or point me to somewhere I can get a
technical paper of something I can give to my boss to stop my boss?

You have a lot of newsgroups in your post. I am removing all except one.

--
"Rose" <rosebernardo@yahoo.com> wrote in message
news:d200c71d.0311081723.4a3acd71@posting.google.c om...
> I am working for the Business Investigation Department. They requested
> a database link to production so they can perform their research to
> the production environment through this database link.

The last sentence above seems to indicate the Business Investigation
Department (what is this department ? an computer/IT or client department?)
wants to create a database link in a non-production database to a production
database.

> Their investigation consists of procedures that read files of any
> number between 50,000 to 1,000,000 records and perform queries against
> the production database - one by one. Since their customers make about

Again trying to make sure I understand you correctly... performing queriES
over a database link 50,000 to 1,000,000 times via a procedure (Plsql?)!

> twenty to thirty different requests every day, they don't have time to
> make up test data. Therefore they also wish to test their procedure
> with production data using this same databalink link.

Everytime you use the database link , you are hitting/impacting! the
production environment.

>
> I believe this whole thing to be crazy. Can someone give me some very
> good technical explanation and/or point me to somewhere I can get a
> technical paper of something I can give to my boss to stop my boss?

Is the above setup strictly for testing some new procedure in a test
database and then later moving it to production where the production setup
would not use a database link? If so, then it might be ok assuming EVERYONE
knows this is obviously impacting (and probably severely impacting!) the
production database everytime they test it in the test database.

Seems you should propose a better solution like copying the production data
(or a subset of it) to the test environment? Or setup the test database
tables in production and test in production without a database link
(assuming they don't mind impacting production because that is what the
database link proposal will do).

If not possible to copy production data to the test database(size?), then
they should just create the procedure and test in the production database.
It might be easier to setup the test database tables in the production
database.

Anyway, the proposal will obviously impact production with the database link
(and this is ok?) and it will probably be more of an impact than if they
just do it staight into the production database without a database link
(assuming size is a problem and so data cannot be copied to the test
database).

I'd just make the link and try the queries to see how slow they are and if
they have any effect on production apps. (It sounds like you don't have a
test or development environment to try this in.). If the queries are slow
or impact production apps, you could suggest an alternative - perhaps
materialized views.


"Rose" <rosebernardo@yahoo.com> wrote in message
news:d200c71d.0311081723.4a3acd71@posting.google.c om...
> I am working for the Business Investigation Department. They requested
> a database link to production so they can perform their research to
> the production environment through this database link.
> Their investigation consists of procedures that read files of any
> number between 50,000 to 1,000,000 records and perform queries against
> the production database - one by one. Since their customers make about
> twenty to thirty different requests every day, they don't have time to
> make up test data. Therefore they also wish to test their procedure
> with production data using this same databalink link.
>
> I believe this whole thing to be crazy. Can someone give me some very
> good technical explanation and/or point me to somewhere I can get a
> technical paper of something I can give to my boss to stop my boss?

Rose wrote:

>I am working for the Business Investigation Department. They requested
>a database link to production so they can perform their research to
>the production environment through this database link.
>Their investigation consists of procedures that read files of any
>number between 50,000 to 1,000,000 records and perform queries against
>the production database - one by one. Since their customers make about
>twenty to thirty different requests every day, they don't have time to
>make up test data. Therefore they also wish to test their procedure
>with production data using this same databalink link.
>
>I believe this whole thing to be crazy. Can someone give me some very
>good technical explanation and/or point me to somewhere I can get a
>technical paper of something I can give to my boss to stop my boss?
>
>
Total insanity. Point you to something? How about bone-head IS/IT
managment. Course 100
for those that failed the Boolean logic question "When does 1 equal 1?"

Unless you think you can torpedo this idiocy without losing your job
tell them they can have
exactly what they want as soon as they respond to a tactfully worded
memo you send to them
appraising them of the risk, appraising them of the fact that your
advice is not to do it, and asking
them to accept full responsibility for the consequences of their actions.

Asking people to sign their names to pieces of paper on which they
accept "full responsibility" often
has more power than all the reasoned arguments that can be constructed.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Van Messner wrote:

>I'd just make the link and try the queries to see how slow they are and if
>they have any effect on production apps. (It sounds like you don't have a
>test or development environment to try this in.). If the queries are slow
>or impact production apps, you could suggest an alternative - perhaps
>materialized views.
>
>
>"Rose" <rosebernardo@yahoo.com> wrote in message
>news:d200c71d.0311081723.4a3acd71@posting.google. com...
>
>
>>I am working for the Business Investigation Department. They requested
>>a database link to production so they can perform their research to
>>the production environment through this database link.
>>Their investigation consists of procedures that read files of any
>>number between 50,000 to 1,000,000 records and perform queries against
>>the production database - one by one. Since their customers make about
>>twenty to thirty different requests every day, they don't have time to
>>make up test data. Therefore they also wish to test their procedure
>>with production data using this same databalink link.
>>
>>I believe this whole thing to be crazy. Can someone give me some very
>>good technical explanation and/or point me to somewhere I can get a
>>technical paper of something I can give to my boss to stop my boss?
>>
>>
The issue has nothing to do with speed. There might be zero impact on
speed. The issue is
entirely encapsulated in the phrase "they also wish to test their
procedure with production data using
this same databalink link."

In other words, at any moment, they reserve the right to throw in a
Cartesian join and bring the
entire production database to its knees.

Another possible solution Van. Look at giving them a profile that
tightly restricts their use of resources.
Also look at the DBMS_RESOURCE_MANAGER built-in package.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Rose wrote:

> They requested
> a database link to production so they can perform their research to
> the production environment through this database link.
> Their investigation consists of procedures that read files of any
> number between 50,000 to 1,000,000 records and perform queries against
> the production database - one by one. Since their customers make about
> twenty to thirty different requests every day, they don't have time to
> make up test data. Therefore they also wish to test their procedure
> with production data using this same databalink link.

I'd not do this. The risk of messing up the production database is too
high should they do more than reading, and depending on the machines and
the databases configuration you may run into serious performance
problems with big queries - which both are a definite nono for the
production database.

What I'd do is the following: Set up another seperate machine and run a
mirror database on it, either by doing a daily export/import from
production to mirror or by linking the mirror database directly to the
production database. (The latter has the additional advantage that,
should the production machine fail, you can switch to the mirror machine
quickly and call it the new production machine. It requires more
administrative effort though to set up.) Then let them do their research
on the mirror database, in an additional seperate tablespace and under
an additional seperate user.

Yours,
Stephan

You guys are all right. They obviously shouldn't be playing with production
this way. But I've seen a lot of Oracle at a lot of companies, and not
having test and/or devl and/or user acceptance is all too common. And not
having a proper backup schedule, and not practicing recovery, and having
business users with enough clout who always get their way, etc, etc.
The op didn't say what she could control and what she couldn't. BUT... if
all she's doing is selecting from a 50,000 to 1,000,000 table over a link 30
times a day, she probably won't get into too much trouble.


"FlameDance" <flamedance@gmx.de> wrote in message
news:bomh95$nt4$03$1@news.t-online.com...
> Rose wrote:
>
> > They requested
> > a database link to production so they can perform their research to
> > the production environment through this database link.
> > Their investigation consists of procedures that read files of any
> > number between 50,000 to 1,000,000 records and perform queries against
> > the production database - one by one. Since their customers make about
> > twenty to thirty different requests every day, they don't have time to
> > make up test data. Therefore they also wish to test their procedure
> > with production data using this same databalink link.
>
> I'd not do this. The risk of messing up the production database is too
> high should they do more than reading, and depending on the machines and
> the databases configuration you may run into serious performance
> problems with big queries - which both are a definite nono for the
> production database.
>
> What I'd do is the following: Set up another seperate machine and run a
> mirror database on it, either by doing a daily export/import from
> production to mirror or by linking the mirror database directly to the
> production database. (The latter has the additional advantage that,
> should the production machine fail, you can switch to the mirror machine
> quickly and call it the new production machine. It requires more
> administrative effort though to set up.) Then let them do their research
> on the mirror database, in an additional seperate tablespace and under
> an additional seperate user.
>
> Yours,
> Stephan
>

little bit of a late post but...

"they don't have time to make up test data" -- it takes less time than
discussing why they need to use the production database for testing

if all they need is realistic test data, just export the necessary tables
from the production database, then import them into the 'test' database --
the import can be rerun (after truncating the 'test' tables) anytime they
need a predictable test environment

"Van Messner" <vmessner@bestweb.net> wrote in message
news:vr32jasq6tf0ba@corp.supernews.com...
> You guys are all right. They obviously shouldn't be playing with
production
> this way. But I've seen a lot of Oracle at a lot of companies, and not
> having test and/or devl and/or user acceptance is all too common. And not
> having a proper backup schedule, and not practicing recovery, and having
> business users with enough clout who always get their way, etc, etc.
> The op didn't say what she could control and what she couldn't. BUT... if
> all she's doing is selecting from a 50,000 to 1,000,000 table over a link
30
> times a day, she probably won't get into too much trouble.
>
>
> "FlameDance" <flamedance@gmx.de> wrote in message
> news:bomh95$nt4$03$1@news.t-online.com...
> > Rose wrote:
> >
> > > They requested
> > > a database link to production so they can perform their research to
> > > the production environment through this database link.
> > > Their investigation consists of procedures that read files of any
> > > number between 50,000 to 1,000,000 records and perform queries
against
> > > the production database - one by one. Since their customers make
about
> > > twenty to thirty different requests every day, they don't have time
to
> > > make up test data. Therefore they also wish to test their procedure
> > > with production data using this same databalink link.
> >
> > I'd not do this. The risk of messing up the production database is too
> > high should they do more than reading, and depending on the machines and
> > the databases configuration you may run into serious performance
> > problems with big queries - which both are a definite nono for the
> > production database.
> >
> > What I'd do is the following: Set up another seperate machine and run a
> > mirror database on it, either by doing a daily export/import from
> > production to mirror or by linking the mirror database directly to the
> > production database. (The latter has the additional advantage that,
> > should the production machine fail, you can switch to the mirror machine
> > quickly and call it the new production machine. It requires more
> > administrative effort though to set up.) Then let them do their research
> > on the mirror database, in an additional seperate tablespace and under
> > an additional seperate user.
> >
> > Yours,
> > Stephan
> >
>
>

"mcstock" <mcstockspamplug@spamdamenquery.com> wrote in message news:<2IadnQAB98PiCiyiRVn-vg@comcast.com>...
> little bit of a late post but...
>
> "they don't have time to make up test data" -- it takes less time than
> discussing why they need to use the production database for testing
>
> if all they need is realistic test data, just export the necessary tables
> from the production database, then import them into the 'test' database --
> the import can be rerun (after truncating the 'test' tables) anytime they
> need a predictable test environment
>
> "Van Messner" <vmessner@bestweb.net> wrote in message
> news:vr32jasq6tf0ba@corp.supernews.com...
> > You guys are all right. They obviously shouldn't be playing with
> production
> > this way. But I've seen a lot of Oracle at a lot of companies, and not
> > having test and/or devl and/or user acceptance is all too common. And not
> > having a proper backup schedule, and not practicing recovery, and having
> > business users with enough clout who always get their way, etc, etc.
> > The op didn't say what she could control and what she couldn't. BUT... if
> > all she's doing is selecting from a 50,000 to 1,000,000 table over a link
> 30
> > times a day, she probably won't get into too much trouble.
> >
> >
> > "FlameDance" <flamedance@gmx.de> wrote in message
> > news:bomh95$nt4$03$1@news.t-online.com...
> > > Rose wrote:
> > >
> > > > They requested
> > > > a database link to production so they can perform their research to
> > > > the production environment through this database link.
> > > > Their investigation consists of procedures that read files of any
> > > > number between 50,000 to 1,000,000 records and perform queries
> against
> > > > the production database - one by one. Since their customers make
> about
> > > > twenty to thirty different requests every day, they don't have time
> to
> > > > make up test data. Therefore they also wish to test their procedure
> > > > with production data using this same databalink link.
> > >
> > > I'd not do this. The risk of messing up the production database is too
> > > high should they do more than reading, and depending on the machines and
> > > the databases configuration you may run into serious performance
> > > problems with big queries - which both are a definite nono for the
> > > production database.
> > >
> > > What I'd do is the following: Set up another seperate machine and run a
> > > mirror database on it, either by doing a daily export/import from
> > > production to mirror or by linking the mirror database directly to the
> > > production database. (The latter has the additional advantage that,
> > > should the production machine fail, you can switch to the mirror machine
> > > quickly and call it the new production machine. It requires more
> > > administrative effort though to set up.) Then let them do their research
> > > on the mirror database, in an additional seperate tablespace and under
> > > an additional seperate user.
> > >
> > > Yours,
> > > Stephan
> > >
> >
> >

I am sorry if I was not clear. "The production tables average about 80
million records". They are reading about 50,000 to 1,000,000 rows of
data from flat files and doing queries on the production tables using
this data as search criteria.

rosebernardo@yahoo.com (Rose) wrote in message news:<d200c71d.0311161701.4970a35@posting.google.c om>...
> "mcstock" <mcstockspamplug@spamdamenquery.com> wrote in message news:<2IadnQAB98PiCiyiRVn-vg@comcast.com>...
> > little bit of a late post but...
> >
> > "they don't have time to make up test data" -- it takes less time than
> > discussing why they need to use the production database for testing
> >
> > if all they need is realistic test data, just export the necessary tables
> > from the production database, then import them into the 'test' database --
> > the import can be rerun (after truncating the 'test' tables) anytime they
> > need a predictable test environment
> >
[and other good comments deleted]
>
> I am sorry if I was not clear. "The production tables average about 80
> million records". They are reading about 50,000 to 1,000,000 rows of
> data from flat files and doing queries on the production tables using
> this data as search criteria.

Rose,
Is the export idea not acceptable to you for some reason?

Just saying "about 80million records" doesn't really tell us much. Is
that all in one table? Or maybe 10 tables of 8million records each?

Depending on the bytes per record that can be anything from less than
1Gigabyte to potentially terebytes. Do you have any backup process.
From that backup, you should be able to create a development
environment for the testers to play in. (If the backup is too big, I'd
start wondering what plans you have for disaster recovery.)

Are they testing just the quality of the production data? (e.g.
verifying the existance of production records based on an external
source?) Then the separate DB instance seems the best solution to me.

Or are they testing performance on the production DB? If so, then how
do they plan to factor out the overhead of the DB link?

Or for some bizarre reason they feel the need to have a "real-time"
view of the production data? For TESTING? I don't believe it.

The test approach sounds more like something done in a Datawarehouse.
There's nothing I (and obviously others here) see in the information
you provide that justifies access to production data.

HTH,
ed