2 Replies Latest reply on Sep 21, 2005 4:35 AM by tar?k parlayan

    sql performance difference

    tar?k parlayan Newbie

      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 ?



      /*+ 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)

      and atd.segment4 = '1043'
      and atd.acc_type = 'D'
      and atd.debit_credit_flag in ('B','A')
      and atd.segment2 in ('1','3')

      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'

      --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

        • 1. Re: sql performance difference
          Frank Langelage Master

          This seems to be an oracle problem rather than a jboss prob.
          You'll have to compare the execution plan to get some hints.

          How do you execute the query from jboss ?
          Do you use parameters/placeholders in this query ?

          • 2. Re: sql performance difference
            tar?k parlayan Newbie


            connection url is:
            <connection-url>jdbc:oracle:thin:@(DESCRIPTION = (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP) (HOST = kopsdb1_vip.yurticikargo.com.tr) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = kopsdb2_vip.yurticikargo.com.tr)(PORT = 1521)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME = KOPS)))</connection-url>

            we use oracle10g rack system and try to do load balancing.

            so i think its application server problem. because lots of sql statement execute in system and some of then slower in application server.
            same sql statement execute in pl/sql and jdeveloper faster than application server. these sql staments contains more than joins and hints (for index)