Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance between SQL*PLUS and web browser
One difference probably is array fetches in Sql*plus and no array fetches in
JDBC.
Secondly, without seeing EXPLAIN PLAN results your story is really to vague
to provide any final answer.
The problem must be in the driver, there shouldn't be any difference on the
server side.
Hth,
Sybrand Bakker, Oracle DBA
<ibm_97_at_yahoo.com> wrote in message news:8aracf$2m$1_at_nnrp1.deja.com...
> 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