Re: Multiple databases - best performance scenario

On Apr 12, 4:04 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "Paul" <paul.f..._at_globaladdress.net> wrote in message
>
> news:36ea1691-4890-4f2e-9f5b-9a67dede9f13_at_m71g2000hse.googlegroups.com...
>
>
>
> > Thanks for the detailed information guys.
>
> > I got the following from one of our technical guys:
>
> > 'The type of processing that we undertake is both high-volume OLTP (3
> > to 5m records an hour) or batch. Changing parameters to improve
> > performance for either one type or the other seems to make absolutely
> > no difference. I prefer OLTP where I can use it as the redo/undo/temp
> > space requirements are almost zero, whereas batch can use a lot of
> > space and, if there's a problem, the process is unlikely to be
> > restartable from that point. With OLTP we can service transactions
> > with multiple threads and highly optimised PreparedStatements and the
> > only time that this goes wrong is when the main dbf tables run out of
> > space. OLTP can be restarted from just prior to the point at which it
> > failed and can be replayed with absolutely no negative effects. If
> > this code were SQL batch then we would have to start from scratch.
> > OLTP is also a lot faster than batch when you're handling 20m records.
> > OLTP can take 6 hours but batch can be 24 - and has the greatest risk.
>
> >    Multiple instances don't work as well when both databases are busy
> > - there's definitely a conflict even though they're separate.
> > Performance is less than half so it's actually faster to run jobs
> > sequentially. I suspect that I/O is the bottleneck as the Cpus are
> > never flat out and there's plenty of memory.

So when you run in the same instance you'll max the cpus - you can't win! :-)

>
> >    Multiple schemas have other issues as well as general performance.
> > There appears to be some sort of 'bug' in Oracle that causes it to
> > become confused. We have, on occasions, seen it using the wrong
> > compiled code to access a table that has the same name across two
> > schemas. We can resolve this by prefixing all table names with the
> > user name. The second problem is more pervasive and we don't have a
> > fix. When two schemas are active and updating tables Oracle may throw
> > a wobbly stating that it can no longer maintain database state for one
> > instance and the SQL terminates. It's clearly a bug as either schema
> > will work correctly when running solo.
>
> Never seen this happen in any version of Oracle I worked with (6,7,8,9,10).
> We currently use multiple schemas and I have done so in the past and never
> had this problem. (Oracle updating the wrong table or using hte wrong
> package)  I suspect this is an application problem. (and I don't put the
> schema name on my sql either.)    Never see the other problem either.  I
> think you have some application problem.
>

See the + signs in Note 401436.1, particularly Bug 5686711. I think this is the nasty that Noons was complaining about for years.

jg

--
@home.com is bogus.
Absolut LOL
http://latimesblogs.latimes.com/laplaza/2008/04/mexico-reconque.html
http://absolut.com/iaaw/blog/in-an-absolut-world-according-to-mexico