I've got a question, just searching for suggestions here.

So, my application is 'modernizing' a desktop application by transforming it to the net, by having an ICEFaces UI and server side designed in Java. However, they're keeping round the same Oracle database, which at current count has about 700-900 tables and most likely a billion total records within the tables. Some individual tables have 250 million rows, many have over 25 million.

Obviously, the database isn't scaling well. Consequently, the performance from the application is searching to become abysmal. The designers / decision makers-that-be have either declined or are reluctant to restructure the persistence. So, essentially we're placing a fresh coat of fresh paint on the functional desktop application that presently serves most user needs and achieves this with relative ease. The particular database performance is fairly slow within the desktop application now. The fast performance I known to earlier was non-database related stuff (sorry I misspoke there). I'm getting sleep problems during the night considering how poorly this application will perform and just how difficult it will be for everyday customers to get the job done.

So, my real question is, what options must i mitigate this impending disaster? Can there be some form of intermediate layer I'm able to place in between your database and also the Java code to accelerate performance yet still time keeping the database structure intact? Caching is clearly a choice, however i aren't seeing that like a cure-all. Can you really layer a NoSQL DB among or something like that?

I do not learn how to reconcile a couple of things you stated.

Obviously, the database isn't scaling well

and

presently serves most user needs and achieves this with relative ease and quick performance.

You do not say you're adding new customers or new function, just making exactly the same function accessible using a web interface.

Why it is possible to problem. Your Internet Application is going to be doing pretty much exactly the same database act as before.

Actually presenting an internet tier may give new caching possibilities so lowering the work the DB does.

In case your early bits of web application development are showing poor performance i quickly would begin by attempting to know how the queries you do within the web application vary from individuals made by the present application. Is it feasible that you're with a couple pedaling that is going for a somewhat naive method of producing queries?

When the current application works well as well as your new java application does not, the issue is not within the database layer, however in the application layer. If performance is really as bad while you say, they ought to notice fairly early and also have the use of returning towards the Desktop application.

The DBA should have the ability to readily identify the extra workload around the database out of your application. Presuming the logic has not transformed it rarely is in doing more creates. It may be reads or it may be 'chattier' (moving the equivalent information however in more compact parcels). Chatty programs may use lots of CPU. Lots of designers attempt to move processing in the database layer in to the application layer because "focus on the database is costly" but really worsen because of the overhead from the "to-and-fro".

PS.

There is nothing 'bad' about getting 250 million rows inside a table. Generally you access a table with an index. You will find typically two or three hops from the top a catalog towards the bottom (after which yet another towards the table). I have got a 20 million row table having a BLEVEL of two along with a 120+ million row table having a BLEVEL of three.

Indexing implies that you rarely hit greater than a small proportion of the data blocks. The commonly used index blocks (and data blocks) get cached within the database server's memory. The DBA would have the ability to find out if this memory area is simply too small for that workload (ie lots of physical disk IO).

In case your application gets enough detailed information online it does not actually need, this could put pressure around the storage. You shouldn't be greedy. should you just have three posts from the row, don't grab the entire row.