Oracle Cardinality hint

Recently I came across a query where the cost was comparatively high. I was wondering what could be the issue for a simple query to have such a high cost. This query has been called many times during the execution and this has considerably increased over all execution time of the process. The query was placed inside a procedure which has a collection as input parameter. This collection is used to filter data from a table with 544K rows.

The business logic was such that we can pass only 5 members to the collection at a time. I was surprised to see oracle does a FTS on the table to fetch 5 rows from an indexed column. I thought of digging a little bit deeper and find the root cause.

Original Query

pic1

Plan

pic2

Here, oracle calculates the size of collection as 8168 but we have only 5 elements in the collection. I thought of adding the cardinality hint and provide the optimizer some more information.

Modified Query with cardinality hint.

pic3

Plan

pic4

We can clearly see that oracle is calculating the collection size properly and able to generate the best plan. we were able to get a great performance difference with this small changes.

Advertisements

2 thoughts on “Oracle Cardinality hint

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s