ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Fagan <mfa...@tde.com>
Subject Re: Performance issue with CustomTypeHandler
Date Thu, 20 Oct 2005 02:54:03 GMT
Satish,

I am using the same and suspect that the problem you are experiencing is 
with the proc.
try this is sqlplus

set timing on
var results refcursor;
exec your_procedure( your_params, :results ); -- for a procedure
exec :results := your_function( your_params ); -- for a function
print :results;

I am curious how long the  print :results takes.

This time should closely match what you are seeing with your JDBC call 
(assuming you run both from the same machine).

I assume your refcursor is defined selecting  non-lob or cursor types 
and you are not building it on the fly in a loop.

Regards,
Mike Fagan


Rao, Satish wrote:

> Hi Mike
>  
> We are using Oracle 9i and I am using ojdbc14.jar. But my classpath 
> also has classes12.jar. I removed classes12.jar, but that did not make 
> any difference.
> What Oracle version and driver are you using?
>  
> Also I am using "oracle.jdbc.OracleDriver" as my driver classname and 
> database URL is jdbc:oracle:thin:@hostname:port:sid
>  
> I don't believe there are any other settings required.
>  
> Thanks,
> Satish
>
>     -----Original Message-----
>     *From:* Mike Fagan [mailto:mfagan@tde.com]
>     *Sent:* Wednesday, October 19, 2005 11:49 AM
>     *To:* user-java@ibatis.apache.org
>     *Subject:* Re: Performance issue with CustomTypeHandler
>
>     Rao, Satish wrote:
>
>>     Hi Mike,
>>      
>>     Thanks for the prompt response.
>>      
>>     If we run the PL/SQL via SQL Plus, we don't experience the same
>>     delay (there are about a million records in the database)
>>     As far as the database is concerned, we don't have much insight
>>     into the configuration. Can you please provide me a list of
>>     possible database parameters that we need to take a look at?
>>      
>>     Please note that even though there are a few thousand rows we
>>     always fetch only 10 records. In other words, the cursor should
>>     have only 10 records at any point in time.
>>      
>>     I debugged the code and here's the behavior I see. Do you see a
>>     similar pattern?
>>     1. Procedure executes
>>     2. control gets transferred to RefCursorHandler (for OUT
>>     parameter of ORACLECURSOR jdbcType). The following statement gets
>>     called         
>>     *                this*.result = (ResultSet)arg0.getObject();
>>     3. next, ResultGetterImpl getObject() gets called. The following
>>     statement within that method gets executed
>>     *                return* rs.getObject(index);
>>     4. next, CallableStatementResultSet getObject(int columnIndex)
>>     gets called.
>>     5. next, oracle.jdbc.driver.OracleCallableStatement gets called
>>     (*/At this point it takes a long time/*) and I can see NetSocket
>>     statements.
>>     Does it mean the statement is making a call to the database
>>     again? Assuming it is fetching data from the cursor, there should
>>     be only 10 records in the cursor and it shouldn't be taking that
>>     long. Do you think it is looping thru thousands of records again?
>>      
>>     Thanks,
>>     Satish
>>      
>>      
>>
>>         -----Original Message-----
>>         *From:* Mike Fagan [mailto:mfagan@tde.com]
>>         *Sent:* Tuesday, October 18, 2005 4:40 PM
>>         *To:* user-java@ibatis.apache.org
>>         *Subject:* Re: Performance issue with CustomTypeHandler
>>
>>         Satish,
>>
>>         I am also using oracle ref cursors and do not see the delay
>>         you are experiencing.
>>
>>         Has something possibly changed in your database and the delay
>>         you see is really in the pl/sql?
>>
>>         Regards,
>>         Mike Fagan
>>
>>
>>         Rao, Satish wrote:
>>
>>>         Is anyone facing a similar issue. Please suggest.
>>>
>>>             -----Original Message-----
>>>             *From:* Rao, Satish
>>>             *Sent:* Tuesday, October 18, 2005 1:44 PM
>>>             *To:* user-java@ibatis.apache.org
>>>             *Subject:* Performance issue with CustomTypeHandler
>>>
>>>
>>>             I am using ORACLECURSOR in my sqlmap and using the
>>>             following TypeHandler code.
>>>
>>>             Here's the sqlmap entry
>>>                             <parameter property="offerRS"
>>>             jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet"
>>>             mode="OUT" typeHandler="RefCursorHandler"/>
>>>
>>>             And here's the handler code
>>>             *public** class* RefCursorHandler* implements*
>>>             TypeHandlerCallback {
>>>
>>>                    * public* Object getResult(ResultGetter arg0)*
>>>             throws* SQLException {
>>>
>>>                            * this*.result =
>>>             (ResultSet)arg0.getObject();
>>>                            * return** this*.result;
>>>                     }
>>>
>>>             The statement* (ResultSet)arg0.getObject();* takes a
>>>             long time to return (even for 2 rows).
>>>
>>>             Do you see any issue with the code?
>>>             I debugged this and eventually found that the above
>>>             statement calls the following method within
>>>             CallableStatementResultSet
>>>
>>>              * public* Object getObject(*int* columnIndex)* throws*
>>>             SQLException {
>>>                * return* cs.getObject(columnIndex);
>>>               }
>>>
>>>             This in turn calls OracleCallableStatement. This part
>>>             takes a long time to return.
>>>
>>>             I am using thin driver.
>>>
>>>             Please suggest. I need to get this resolved ASAP.
>>>
>>>             Thanks,
>>>             Satish
>>>
>>
>     Yes, It is talking to oracle and I beleive it is now actually
>     executing the cursor reference it returned to you.
>     If you are using the thin driver make sure you have the latest
>     version.
>     I am puzzed why is runs slower in JDBC than SQL Plus, unless there
>     are some user or complex oracle object defined in the cursor or
>     character conversion is happening across the wire.
>
>     Regards,
>     Mike Fagan
>
>


Mime
View raw message