Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance between SQL*PLUS and web browser

Re: Performance between SQL*PLUS and web browser

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/03/16
Message-ID: <953233745.15114.0.pluto.d4ee154e@news.demon.nl>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US