hi
our problem is differnence execution time between pl/sql developer and jboss application server.

same select statments executed in 5 seconds at pl/sql devoleper or oracle j-developer , but selfsame excecuted in 30 seconds at jboss application server.

i checked the index , strings etc. but i dont see any problems. sample select statments is bottom of the page. but we have lots of same problems on different statements.

have you got any idea , can we change any jboss or java parameter , configuration settings , etc ?


thanks

--

select
/*+ INDEX (atd, acc_tran_detail_ind1) */
TO_CHAR(atd.field1) as CUST_ID,
c.cust_name as CUST_NAME,
decode(atd.debit_credit_flag, 'A', (sum(amount)*(-1)), sum(amount)) as DOCUMENT_DEBIT,
TO_CHAR(atd.segment2) as ACCOUNT_TYPE
from acc_transaction atn
join acc_tran_detail atd on atd.tran_id = atn.tran_id
join gen_customer c on c.cust_id = TO_NUMBER(atd.field1)

-- ACC_TRAN_DETAIL_IND3 (SEGMENT4, ACC_TYPE, DEBIT_CREDIT_FLAG, SEGMENT2)
and atd.segment4 = '1043'
and atd.acc_type = 'D'
and atd.debit_credit_flag in ('B','A')
and atd.segment2 in ('1','3')

-- ACC_TRANSACTION_INDX1 (TRAN_DATE, UNIT_ID, TRAN_CODE)
and atn.tran_date = '20050916'
and atn.unit_id = '1043'
and atn.tran_code in (select temp.tran_code from acc_transaction_template temp )
-- and nvl(atn.cancel_flag, 0) = 0
-- and nvl(atn.cancelled_tran_id, 0) = 0
and atn.status = '1'

where
--atd.segment1 = 1
atd.segment1 in(1,4)
--and atd.field1 = '2011906'
--and c.cust_name like 'I%'
group by atd.field1, atd.debit_credit_flag, atd.segment2, c.cust_name