Re: Performance between SQL*PLUS and web browser

JDBC is real slow due to all the java string processing to create and SQL and interpret the results. Maybe you can change it to a stored procedure in Oracle that just returns the result directly instead of having JDBC be involved. The 30:1 isn't too much higher than what I see.

I think JDBC does use the same plan.

If what you are seeing is high CPU by the java part while it's waiting, it's probably the string stuff. Try doing -prof.

In article <8aracf$2m$1_at_nnrp1.deja.com>, ibm_97_at_yahoo.com wrote:
> 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.
>

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