Indexes, Performance, and Java oh my!

Ok, sorry for the cheezy title, but I've got a lot of questions.

I'm working on an application written in Java that is probably in the OLTP classification. Users will be shown lots of web pages. Each page will have several queries to check things and retrieve the appropraite information. Then when the user submits the page it will trigger a series of insert, update, and deletes.

I've written a set of stored procedures (3) that perform the following for each table:

1. I - Performs an insert statement
2. U - Performs an update statement
3. IU - Tries to perform an update statement, if the NO_DATA_FOUND exception is thrown then it calls the insert.

First off, I created a test table to do some benchmarking of JDBC drivers against just to see what kind of performance I would get. Well the good result was that the thin vs. oci vs weblogic had less than a 10% differance between any of them. The problem I noticed was that the insert/update was significantly slower than the insert on the connection. I was getting about 22 inserts/second and only about 3.5 insert/updates per second on a connection. Now granted the hardware I'm testing on is nothing spectacular or tuned and the table I'm testing against has about a dozen columns, but this seemed like a little bit too much of a difference. So less leads me to the question of indexing. Does a table have a "default" index based upon the primary/foreign keys of the table? If not is it a good idea to create an index based upon the keys since the keys are what are being checked in the where clause of the update.

On to the topic of "the proper disk for the proper drive". I'm reading some of the books and online guides about the layout of objects to different drives and starting to get a little bit freaked about the number of drives. What would be the typical configuration of a medium sized database as far as # of controls, # of drive arrays, and contents of these arrays with respect to which ones hold the OS, Oracle, Swap, Indexes, heavily selected data, and heavily updated data.

Thanks,
Jonathan Tew Received on Wed Feb 24 1999 - 22:34:43 CST