Re: JDBC Driver extremely slow??

The point is, resulting set contains 12 records. And I've monitored network activity while executing the query, the query is sent, server dedicates one of the XEON 2.8Ghz CPUs for 150 secs to the server, then we get the results :) But no significant network activity is seen between jdbc client and oracle in that period.
I've executed the query by setting the array size to 1 on sql+ results came in a blink of an eye. I might have sent the query wrong so I paste it here. Additionally, I cut WHERE (ROWNUM <= 12) clause and executed it again, it returned 328 records immediately.

set array 1;
 SELECT * FROM ( SELECT /*+ USE_NL(WPS,CULTEXT,BASES) INDEX_ASC(WPS I_APPWPS_02)*/ DISTINCT
    WPS.LOGICALREF AS LogicalRef, WPS.WPID AS WPId, WPS.CODE AS Code, WPS.WPTYPE AS WPType, WPS.WPTYPENAME AS WPTypeName, WPS.TE_WPIID, CULTEXT.LOGICALREF AS CLogicalRef, CULTEXT.OWNERNAME AS OwnerName, CULTEXT.OWNERDESC AS OwnerDesc, CULTEXT.OWNERTYPEDESC AS OwnerTypeDesc FROM
    W_APPWPS WPS LEFT OUTER JOIN W_APPWPOPCULTEXTS CULTEXT ON ((CULTEXT.OWNERCODE = WPS.CODE) AND (CULTEXT.OWNERTYPE = 1)) INNER JOIN W_APPWPBASES BASES ON (BASES.SPECIFICWP = WPS.CODE) WHERE
    ((CULTEXT.CULTURE = 'TRTR') AND (WPS.ISINTERNAL = 0)) ORDER BY
    CULTEXT.OWNERNAME ASC, CULTEXT.OWNERTYPEDESC ASC, CULTEXT.LOGICALREF ASC, WPS.CODE ASC )
WHERE
     (ROWNUM <= 12);

Thank you..
V. Oguz TOKMAK

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:6bap02t8rafpaj3sod0uamhoo2qg0fho8r_at_4ax.com...
> On Mon, 6 Mar 2006 23:07:48 +0200, "vahapt"
> <vahapt[at'no'Spam]gmx.com.tr> wrote:
>
>>tables have about 1000 entries, I know that indexing will further improve
>>the execution but I cannot understand the difference between iSQL+ and
>>JDBC.
>
> Just guessing with hindsight:
> sql*plus uses array fetching by default. The default array size is 15,
> so after every 15 records there will be a roundtrip between client and
> database, and there will be consistent gets to retrieve the next n
> records.
> Jdbc doesn't use array fetching by default so the array size will be
> 1. This means you get a roundtrip after every records, and free
> consistent gets.
> The impact of this can be huge. You can demonstrate this in sql*plus
> by issuing
> set array 1
> prior to your query.
> If my guess is correct, make sure you set the prefetch count when
> opening the jdbc connection.
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Mon Mar 06 2006 - 23:17:46 CST