Re: Performance between SQL*PLUS and web browser

A copy of this was sent to ibm_97_at_yahoo.com (if that email address didn't require changing) On Wed, 22 Mar 2000 23:05:45 GMT, you wrote:

>Since no one reply me, I post again. :(
>
>
>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. Report generated in browser is
>using dynamic SQL.
>
>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-3 minutes. But if I run the same SQL script in
>SQL*Plus, it only takes 4-5 seconds, or even less. So I think the index
>for this query is fine. (I also rebuild all indices, some result.) And
>all the hit ratios in SGA are also OK. When browser generate reports, I
>didn't see high CPU usage or I/O activity.
>
>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!
>
>
>
>

see the document "server tuning manual".

http://technet.oracle.com/doc/server.815/a67775/toc.htm

Read about SQL_TRACE, TIMED_STASTICS, and TKPROF.

then, put a:

alter session set sql_trace=true;
alter session set timed_statistics=true;

in your jdbc app -- run it, use tkprof to format the resulting trace file and compare the plan you get when running the same query in SQLPlus (use autotrace to get the plan and stats see
 http://osi.oracle.com/~tkyte/article1/autotrace.html for setting up autotrace)

That'll tell you if the problem is in the way the query is being processed.

>
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

-- 
http://osi.oracle.com/~tkyte/
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation