IBATIS issue while using Oracle stored proc returning output cursor with null column

[Sankar Reddy <ma...@yahoo.co.in>]

Hi,

We are seeing a weird (potential) memory issue while using IBATIS for JAVA.
We call an Oracle 10g stored proc via IBatis. The proc returns a single
cursor with 10 columns.
Out of the 10 columns, 3 column has been hardcoded to NULL (for business
functionality reasons) in the SELET Statement of the CURSOR. 

When we set a column explicitly NULL we see that IBATIS seems to take a huge
amount of memory and this leads to FULL GCs – which block our application –
hence degrading its performance.
But, if we set the column to a hardcoded string value (say ‘xyz’), we don’t
see this high memory usage and hence no FULL GCs and hence does not degrade
our performance.

To give an idea, when the sproc is with explicit NULL, the java service that
calls this proc (via ibatis) takes 10 seconds (on a 60 concurrent user
load). This 10 seconds is because of multiple FULL GCs.
But, when we change to a hardcoded string ‘xyz’, we see the same service
return in less 500 ms (for the same load)

Is this a bug or are we configuring ibatis the incorrect way? Any help is
much appreciated.

Please note, we are a Spring 3.0 based java web app, running on Weblogic 9.2
MP3.We are using IBATIS for JAVA version 2.3.0

We have checked and ensured that our JVM settings are appropriate (around
712MB) and the amount of data returned is very little ( 2 records per call,
each with 10 string columns).

Say for example:
OPEN emp FOR 
SELECT 
First_name as FIRSTNAME,
Last_name as LASTNAME,
            NULL as DESIGNATION
FROM
            EMPLOYEE

Now the resultMap would look like this:
<resultMap id="getEmployees" class="employeesDTO" >
                                    <result property="firstName"
column="FIRSTNAME" />
                                    <result property="lastName"
column="LASTNAME" />                              
                                    <result property="desig"
column="DESIGNATION" />
</resultMap>

-- 
View this message in context: http://www.nabble.com/IBATIS-issue-while-using-Oracle-stored-proc-returning--output-cursor-with-null-column-tp25225848p25225848.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org

Re: IBATIS issue while using Oracle stored proc returning output cursor with null column

[Jason King <jh...@airmail.net>]

Sankar,
You might want to try select first_name firstname, last_name lastname , 
'' as designation from employee

In Oracle the empty string '' is also null.  My suspicion is the problem 
here is the Oracle jdbc driver not knowing what the designation column's 
datatype is, is setting up something bizarre to account for any 
possibility, hence the memory consumption.  The empty string ('') while 
null, should wind up getting typed as a varchar so that may alleviate 
the problem.
If that doesn't work, I'd try joining employees to some other (small) 
table and grabbing a column you know is null from that table. 
For example.
create table party_ethics ( party_name, varchar2(10) , 
ethical_constraints varchar2(10));
insert into party_ethics values ('Democrat',NULL);
insert into party_ethics values ('Republican',NULL);
select first_name,
         last_name
         ethical_constraints as designation
from employees ,
        party_ethics
where party_name = 'Democrat'

That will get you a varchar2(10) column with a null value in it.

Sankar Reddy wrote:
> Say for example:
> OPEN emp FOR 
> SELECT 
> First_name as FIRSTNAME,
> Last_name as LASTNAME,
>             NULL as DESIGNATION
> FROM
>             EMPLOYEE
>
> Now the resultMap would look like this:
> <resultMap id="getEmployees" class="employeesDTO" >
>                                     <result property="firstName"
> column="FIRSTNAME" />
>                                     <result property="lastName"
> column="LASTNAME" />                              
>                                     <result property="desig"
> column="DESIGNATION" />
> </resultMap>
>
>   



---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org