Re: Sniffing redo logs to maintain cache consistency?

"Andrej Gabara" <andrej_at_kintana.com> wrote in message news:11a3a163.0302231515.4c1db66d_at_posting.google.com...
> We currently have an application server written in Java,
> but most of the business logic is in PL/SQL (workflow engine,
> for example). Our goal is to enhance our architecture such
> that we can perform and scale better. Our performance tests
> have shown very high CPU usage of Oracle as well as high
> JDBC overhead. High cpu because most of our business logic is
> in PL/SQL, and high JDBC overhead because we have to ship
> lots of data from database to app server. (This architecture
> was not designed by Java programmers, btw.)

Oracle problems

Do you have any in-house Oracle expertise (a DBA for example). The reason for my question is that I suspect most DBA's wouldn't put high oracle server CPU usage down to the use of PL/SQL per se. I'd expect to see an analysis of what specifically was causing the CPU usage i.e where the database was spending its time. A common cause of this is as Jim says using literals rather than bind variables. Assuming you have at least an 8i db, run your performance tests again, but use the Oracle supplied statspack utility to measure oracle performance, you can upload the results to www.oraperf.com for free and get a pretty good analysis of your db issues.

The other cause of this is making the database do too much io, you can see this by looking at your most expensive SQL statements and concentrate on improving either the SQL, or the access paths, or both. This means looking at indexes, data structures (eg materialized views,bitmap indexes), partitioning, optimiser stats and hints etc etc.

JDBC Problems

Are you passing too much data to the application? are you making too many database calls. These are the app design questions you should be asking. Do you use autocommit, if so don't? What is the network latency/bandwidth like? can it be improved

Business Logic
It seems to me that where this should sit depends. If it is complex coding (for example a lot of arithmetic/case expressions etc etc) then you'd probably be daft to put it in the DB, that is what programming laguages do well. If on the other hand it is relatively simple and data oriented (check that there is a customer record for this order and what their credit limit is) then it should sit as close to the data as possible. The one caveat I would add, as a DBA, is that if you are going to do data access outside the application (data loads for example) then putting at the very least the constraint logic in the database is a must.

--
Niall Litchfield
Oracle DBA
Audit Commission UK