3 Replies Latest reply on Feb 19, 2009 2:40 PM by Peter Johnson

    CURSOR_SHARING option help needed

    domenico porto Newbie

      Hi all.
      I'm working on an Hibernate project with Oracle database and I'm experimenting a huge performance problem on same queries.
      Our dba engineer suggested us to set the CURSOR_SHARING option to FORCE to solve these problems.
      My difficulty is that I need to set this option only for the single Hibernate transaction and not for all the system\sessions of the schema because I want to avoid to impact the other systems based on the same schema.
      Can you help me to find an option/solution (add something in the datasource configuration?) I can use?
      Thank you in advance.
      Best regards from Italy,
      Domenico

        • 1. Re: CURSOR_SHARING option help needed
          Peter Johnson Master

          If I recall by Oracle connection options correctly (it's been a while since I used Oracle), you should be able to add this option to the connection URL string.

          In that case, I would define two data sources, one that has this option, the other that does not. Then set your apps to use the appropriate data source.

          • 2. Re: CURSOR_SHARING option help needed
            domenico porto Newbie

            Can you help me in modifying the data source I use introducing the option you refer?
            My current data source is something like:


            <xa-datasource>
            <jndi-name>jdbc/SOCRATEDS</jndi-name>
            <track-connection-by-tx/>
            <isSameRM-override-value>false</isSameRM-override-value>
            <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
            <xa-datasource-property name="URL">jdbc:oracle:thin:@//xxx.yyy.zzz.aaa:pppp/myschema</xa-datasource-property>
            <xa-datasource-property name="User">myuser</xa-datasource-property>
            <xa-datasource-property name="Password">mypwd</xa-datasource-property>
            <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
            <no-tx-separate-pools/>

            <type-mapping>Oracle9i</type-mapping>

            </xa-datasource>

            <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager



            Have I to introduce a new row like this:

            <xa-datasource-property name="CURSOR_SHARING">FORCE</xa-datasource-property>

            Thanks again.

            Domenico

            • 3. Re: CURSOR_SHARING option help needed
              Peter Johnson Master

              That should work.

              P.S. When posting XML text or source code, please enclose the text in UBBCode "code" tags - you can do this by selecting the text and clicking the Code button above the editor window. Also, click the Preview button to ensure that the formatting is correct and the XML text shows up before posting.