Performance between SQL*PLUS and web browser

I just got a big performance problem lately, and I tried all the possible ways, still can't fix it. Could you help me out or give me more suggestions?

Oracle 8i for Solaris 2.6

A web application with back end is Oracle database, developed by Java, use JDBC driver. It also uses Servelet.
When I click some link to generate report in browser, it will run the corresponding SQL script, then return the result to browser. The problem is it takes long long time to get the result. For simple query, it takes around 2 minutes. But if I run the same SQL script in SQL*Plus, it only takes 4-5 seconds. So I think the index for this query is fine. And all the hit ratios in SGA are also OK.

The problem happened when I do the following: the two tables joined in the simple query have 1.2m and 900k rows respectively. In the beginning the performance between the browser and SQL*Plus is almost the same
(around 4 seconds). After I delete 200k rows from both tables
(constraints are still OK), and do the 'analyze' for all tables and
indexes. The when I run the query in problem, the problem happens!! That means take 4-5 seconds in SQL*Plus and take 2 minutes from browser. And I have to delete records from both tables regularly, 'cause both of them grow very fast.

I really have no idea why this happens. But I think the Oracle DB is fine, 'cause query is run normally in SQL*Plus. The problem may related to the JDBC driver or JDBC connection. The developers also have no clue about this. When the Java app run the query, does it use the same way to access the tables and indexes as used in SQL*Plus?

Any idea or suggestions?

Thanks a lot and have a good day!

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 16 2000 - 00:00:00 CST