ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rao, Satish" <satish....@fmr.com>
Subject RE: Performance issue with CustomTypeHandler
Date Wed, 19 Oct 2005 15:53:59 GMT
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