Oracle JDBC Performance : Query Tuning
Posted on 2002-04-23
Oracle 8.1.6, JDBC, JSP, Tomcat3.2.3, JDK1.3.1, 10-15 Concurrent Users
My application is like this ...
"JSP -> JDBC -> Oracle_Thin/OCI8_Driver -> Oracle8.1.6"
Facing performance issue. Don't know what specific is causing it. I have following questions...
. When the application is running for some time the performance keeps going down. How to know that what queries is consuming what time. Is there any way to know from Oracle as what queries (select, insert, update, trigger etc) have taken what time (the history table), so that I could analyze it.
. Since my application allows the users to build the query, sometime user end up writing querries that takes huge time causing system to almost hangs down and hence the other users suffer. How to set the query timeout at Oracle as well as Java level (setQueryTimeout(int seconds)?).
. Will the Oracle resources be automatically released when a query execution is timedout.
. How to know from Oracle as how many Cursors are open at a given point of time.
. In general what steps one should follow to fine tune the database communications (Java + Oracle). I have around 15 main tables that is being populated. Currently the maximum records in few of these tables are only 3000 (3K). There are 15 other corresponding audit tables that I populate for any record-update in the previous 15 tables. Apart from this I have 40 master tables having 5-20 records each used for choice listing in the GUI and for referencing in the main 15 tables. I have generated no index etc etc. Just plain vanilla tables. Please suggest the performance tuning points, tools etc.