Performance issues with function based indexes

Recently I encountered an interesting performance issue while working with Oracle EBS seeded components and  these issues are tied to

wrong use of ‘Function Based’  indexes

Function Based index: Function based indexes are great way to enhance performance and provide capability for case insenstive searches or sorts, search on complex equations. But creating function based indices may not prove fruitful always.It may be a “trap” and can choke performance if not wisely considered.

I will site an example of such a case I recently encountered. The issue encountered is with a functionality delivered by Oracle as a part of their EBS.

The page was Customer Search and it has a search on shipment_name .The issue reported is when customer tries to do a search a shipment_type the page hangs and ultimately times out.

On investigation from analyzing traces and tkprof it was observed that the problematic query was using a filter like

upper(shipment_name) like :B1

Now further analyzing the trace it was observed that the table which was queried was indexed on upper(shipment_name).

It all looked perfect …you are doing a case insensitive search you have a function based index and optimizer was picking the index in its access path why in the world it will have such poor performance.

On a closer look on how that form is used it was observed that the table with column “shipment_name” was a huge table ..OK you would thought that is the reason you would create indexes..

I took a closer look on how the form is used and I found majority of the users access the search form using ‘%’ ‘!!!  i.e they do a blind search..and they don’t pass any filter .. wow I thought I know what is the issue now..because you are passing a blind search and optimizer is picking Function Based index on upper(shipment_name)  unaware of bind and resulting in doing 2X logical I/O as the index is not giving any hits in this case due to no filter and because it was a huge table it is just not able to fetch results and thus timing out

One interesting observation though is although you don’t pass a bind variable optimizer doesn’t seem to have intelligence to evaluate alternative plan although I had unique indexes and those were used in additional where condition but optimizer always took function based index if applicable in explain plan.

Once the problem is identified we have to ensure that the clients using blind search should not be using the function based index and the connections which are using bind should still be benefiting from the index(function based)

So given the situation both dropping the index or hinting the query with non-function based index was out ruled.

To make both the parties happy I used “invisible” index feature.Invisible index is a feature in 11g(available in R1 also) where you can make an index unavailable for optimizer so it won’t pick the index .So making the index invisble made my ‘blind’ searches happy but still the users who are using bind were not able to leverage the index .

The beauty of invisible index feature is you can made the index visible for sessions using parameter

“optimizer_use_invisible_indexes=TRUE” this is a session modifiable parameter and can be altered using a alter session command.

I evaluated the user community in bit detail and realized that users from a specific org is only passing bind for this query rest all are doing blind search…So I get my user sets and now I was set to execute my action plan i.e

1.make the function based index invisble

2.Use profile option ‘Initialization SQL Statement – Custom’ to set the parameter optimizer_use_invisible_indexes=TRUE at the responsibility level by using

BEGIN FND_CTL.FND_SESS_CTL(”,”, ”, ‘TRUE’,”,’ALTER SESSION SET set optimizer_use_invisible_indexes=TRUE’); END;

and it gave me optimum performance for both the set of users.

Initialization SQL Statement – Custom  is a very common profile and we Apps DBAs use it for tracing web based session but the same profile could be used for enabling session level events.
So..in nutshell function based index is  a great performance enhancer but again if they are used wrongly they can complicate matters.