Re: Multiple databases - best performance scenario

From: Paul <>
Date: Sat, 12 Apr 2008 03:42:32 -0700 (PDT)
Message-ID: <>

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.

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

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 - 05:42:32 CDT

Original text of this message