Re: Multiple databases - best performance scenario

"Paul" <paul.flew_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.
>
> 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.

> On the backup front, we generally take cold backups as there's a
> great deal of complexity taking hot ones and there's usually a window
> during which a cold one is possible.

Complexity? You use rman, pretty straightforwad and simple. It is more complex to take down the db to take a cold backup. (and lose all the sga caching etc.)

If there are multiple schemas in
> one instance then this would cause issues with scheduling - that's
> another reason why we run separate instances on the same host.'
>
> Based on that I suspect we'll have to look at separate machines.
> Ideally I'd like to look at virtualization for easy management, but
> from experience the i/o is extremely poor compared to real hardware,
> so that may not be practical.
Received on Sat Apr 12 2008 - 06:04:47 CDT