Re: JDBC Driver extremely slow??

From: vahapt <vahapt[at'no'Spam]gmx.com.tr>
Date: Mon, 6 Mar 2006 23:07:48 +0200
Message-ID: <440ca44f$0$32309$9a6e19ea@unlimited.newshosting.com>

"Joe Weinstein" <joeNOSPAM_at_bea.com> wrote in message news:440c60c1_at_news.bea.com...
>
>
> unlimited.newshosting.com wrote:
>
>> Hi,
>> I try to run a query on Oracle Enterprise v10.2.0.1.0 when I execute it
>> on iSQL+ it returns the results in milisecs. However when I execute the
>> same query using the JDBC driver Oracle tries too hard to process it (150
>> secs). Query advisor warns me about the execution speed and I obviously
>> see the cpu usage increases.
>> Does anyone has an idea?
>>
>> Thanks..
>> V. Oguz TOKMAK
>
> Are you sending the same SQL? *Exactly* the same SQL? Or are you sending
> the JDBC as a prepared statement with parameters? Try a plain statement
> first with the SQL you send via SQL-PLUS and say if it's slower. If you
> were doing a parameterized PreparedStatement, show us that JDBC, and
> tell us the declared data types being referred to in the DBMS. The issue
> may be that there is an implicit data conversion, which unless it's done
> during the compilation of the SQL (such as with SQL-PLUS or a plain
> Statement) it will prevent the DBMS from using the best indexes.
> HTH,
> Joe Weinstein at BEA Systems
>

"Or are you sending the JDBC as a prepared statement with parameters?" That is correct, I execute the query with the parameters replaced on iSQL+ however they are sent as a prepared statement from jdbc.

I'm sending a sample query, when I execute it on iSQL+ by replacing the parameters I got immediate results, but when I execute it as a prepared statement I got the results about in 7-8 secs. As seen on execution plan, the query requires two full table scans, both tables have about 1000 entries, I know that indexing will further improve the execution but I cannot understand the difference between iSQL+ and JDBC. However I got the idea, I'll keep experimenting using the information you gave me and post the results.
Thanks.

SELECT /*+ USE_NL(WFAPPLICATIONWPOPS,CULTEXT) INDEX_ASC(WFAPPLICATIONWPOPS I_APPWPOPS_02)*/ DISTINCT
 WFAPPLICATIONWPOPS.LOGICALREF AS LogicalRef, WFAPPLICATIONWPOPS.OPID AS OpId, WFAPPLICATIONWPOPS.CODE AS Code, WFAPPLICATIONWPOPS.OPCATEGORY AS OpCategory, WFAPPLICATIONWPOPS.OPTYPE AS OpType, WFAPPLICATIONWPOPS.OPPERFORMER AS OpPerformer, WFAPPLICATIONWPOPS.ISINTERNAL AS IsInternal, WFAPPLICATIONWPOPS.ISINTERACTIVE AS IsInteractive, WFAPPLICATIONWPOPS.WFSPECIFIC AS WFSpecific, WFAPPLICATIONWPOPS.TE_WPIID,

CULTEXT.OWNERCODE AS OwnerCode, CULTEXT.OWNERTYPE AS OwnerType, 
CULTEXT.OWNERNAME AS OwnerName, CULTEXT.OWNERDESC AS OwnerDesc, 
CULTEXT.OWNERTYPEDESC AS OwnerTypeDesc, CULTEXT.CULTURE AS Culture

FROM
 W_APPWPOPS WFAPPLICATIONWPOPS LEFT OUTER JOIN W_APPWPOPCULTEXTS CULTEXT ON ((CULTEXT.OWNERCODE = WFAPPLICATIONWPOPS.CODE) AND (CULTEXT.OWNERTYPE = 2)) WHERE
 ((CULTEXT.CULTURE = ?) AND (WFAPPLICATIONWPOPS.ISINTERNAL = ?) AND (WFAPPLICATIONWPOPS.WFSPECIFIC = ?))
ORDER BY
 CULTEXT.OWNERNAME ASC, WFAPPLICATIONWPOPS.CODE ASC

[06.03.2006 22:38:44:003 EET] 63be63be SystemOut     O Parameters :
[06.03.2006 22:38:44:003 EET] 63be63be SystemOut     O P_CULTURE = 'TRTR' 
==>  CULTURE,  NVARCHAR2,  5
[06.03.2006 22:38:44:003 EET] 63be63be SystemOut     O P_INTERNAL = 0 ==> 
ISINTERNAL,   NUMBER,    10
[06.03.2006 22:38:44:003 EET] 63be63be SystemOut     O P_WFSPECIFIC = 0  ==> 

WFSPECIFIC, NUMBER, 5 V. Oguz TOKMAK Received on Mon Mar 06 2006 - 15:07:48 CST