RDBMS -- V$INDEXED_FIXED_COLUMN a useful view

V$INDEXED_FIXED_COLUMN is a useful fixed view.

As we know v$session and similar fixed views get their data through x$tables (or lets say through some special memory structures). Note that, most of the time, v$ views fetch their data from multiple X$ memory structures.

This V$INDEXED_FIXED_COLUMN view shows which columns of these x$ tables have index.

Probably, the indexes in these memory structures are not exactly like the indexes that we know on our database world .. For instance; probably they don't look like b*tree index structures.. I guess these indexes are only based on some offsets, but for us it provides the same benefit (actually I guess they are little les useful) as our traditional indexes.. Besides, they look like traditional indexes in the execution plans. 

Therefore, if we get the data from x$ structures through those indexed columns, we can achieve performance increase especially in queries (on v$ views) , especially in environments with large v$ data.

In this context, if a performance problem appears in a query on some v$ views, it would be useful to look at the execution plan. If we see FIXED TABLE FULL when accessing x$ tables,  we can get the information from V$INDEXED_FIXED_COLUMN which columns of the related x$ table are indexed and then we may change our query to make the optimizer use that index and thus solve the problem. 

The condition we use in our query for the value of an indexed column is also important. For example sid column in v$session .. If we use sid = 5 as a condition, our query uses index to reach x$ data, but if we use sid = userenv ('SID'), optimizer doesn't choose the indexed way.. ( this is seen even in the cases where there is no data type mismatch)

Of course, in such problems that we have long running v$ queries on big sized v$ data, we need to check whether the size of the data in the relevant v$/x$ should be that big or not. Having unexpectedly big sized v$ data should be analyzed.

In addition to that, we need to evaluate the performance bugs and patches, if any, about the related v$ views in the relevant DB release.

This is the tip of the day. Stay tuned :)