OBIEE – Cursor Sharing

When performance is an issue with obiee there are a number of things that you can do to try and improve things.
Oracle should notice when applications send similar SQL statements to the database and mark the relevant SQL area as shared.  The setting to enforce behaviour in this situation is controlled within the init.ora file on the database.  This setting can have a huge impact on BI, determining the explain plan used; we can force BI requests to use the shared SQL area to improve perfomance.

• Expand the relevant database in the Physical Layer
• View the Properties of the relevant connection pool by double clicking it

In the Connection Scripts tab we can issue commands directly against the Database

•  Navigate to the Connection Scripts tab
• Click New to add a new command to the Execute on connect script
• Enter the SQL ALTER SESSION SET CURSOR_SHARING=’EXACT’ and click OK
• Click OK to close the Properties Window

 You may also set this variable to SIMILAR or FORCE.  I would play with the different values and take a view on which improves performance for you.