Oracle: Performance Problems Due to Index Not Used

If some SQL requests with a WHERE clause are slow in Oracle databases, this may be a character encoding issue. In some situations, Unicode mode together with usage of US2 may prevent the Oracle query planner using indexes.

Database Setup

1. Open model oracleIndexUse to setup the database preferences of you oracle instance in the component diagram:

   • dbConnectionString

   • user credentials

2. Compile and run the model.

3. Run the prepared test cases createTable and insertBooks to create test data. createTable creates a database table to store books and some indexes, insertBooks inserts some test data into the table of books.

Query Tests

Now you can try out querying the database with divergent settings in the database alias:

To apply these settings go to the component diagram and edit the specification of the database alias. To change the unicode mode, change tagged value Unicode Mode. To change the usage of UCS2, set the following option in tagged value Options:
UseUCS2=True or UseUCS2=False
The example model comes with Unicode Mode = Platform Default and Options = UseUCS2=True.

To be able to find the SQL statements in the Oracle database monitor (see Inspecting the Query Plan) that correspond to your settings, we recommend to add a comment to the SQL statement in activity diagram Select Books. Just replace the comment template by your comment.

Run test cases with operation selectByTitlefor each setting and check the query plan on the Oracle database whether the index that has been created in step 4 of the database setup, has been used.
By setting indexHint to true, you can give an index hint to Oracle (see How to Suggest The Usage of a Specific Index?).

Inspecting the Query Plan

You can inspect the query plan Oracle used to access the database table via the web interface from Administration > Monitor > Top SQL. On Oracle Database 10g Express this looks like:

A list of performed SQL statements will appear:

Select module bridgeserver.exe to filter the list for integration component (Bridge) requests. If the list is still to long, you can additionally filter for SQL text ISBN.
Click on the magnifier icon of the SQL statement you want to inspect. The database monitor will show the query plan:

Although an index IDX_BOOKS_TITLE is available, it has not been used. This may be caused by the encoding used to access the database.

Whereas if the index is used, the query plan should look like:

By this procedure, you can test Oracle's reaction to the different database preferences, find a set-up that uses the index, and thus improve the performance.

How to Suggest The Usage of a Specific Index?

Last but not least: By an addition to your SQL statement, you can suggest to Oracle the usage of a specific index.

SELECT /*+ INDEX(BOOKS IDX_BOOKS_TITLE) */ 
	ISBN, 
	AUTHOR_FIRST_NAME , 
	AUTHOR_INITIALS, 
	AUTHOR_LAST_NAME, 
	TITLE 
FROM BOOKS 
WHERE TITLE = :0

E.g. by adding /*+ INDEX(BOOKS IDX_BOOKS_TITLE) */ to your select statement, you can suggest to use the index IDX_BOOKS_TITLE. This is called "giving an index hint". In most cases Oracle will follow the hint, but not always.