Re: Multiple databases - best performance scenario

On Sat, 12 Apr 2008 03:42:32 -0700 (PDT), Paul <paul.flew_at_globaladdress.net> wrote:

>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.

This a problem in your application.

 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.

That's the most ridiculous nonsense there has been posted here in a while.
I have never had any problems.
Especially the second problem you describe is utter crap. Are you sure you are running Oracle or some toy product like MySQL? It looks like the time for a developer beating is nigh.

>
> 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. 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.'

Again this is also utter crap. Taking a hot backup is no problem using Rman.

>
>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.

-- 
Sybrand Bakker
Senior Oracle DBA