CURSOR_SHARING option help needed

[mimmo67]

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

[peterj]

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.

[mimmo67]

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

[peterj]

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.