I have a small table that will be read by multiple applications (just select). It has only 293 rows. I got a request from users that they are having peformance issues with this table, their queries are waiting on this table( I don't know when and what exactly was the wait event, since I was not monitoring this when they had issues). A ticket to tune this table is sitting in my queue now since couple of months. I am not sure what kind of issues they will be having with such a small table and how to get info about it. Below is the table description. I was wondering if some one can provide me any suggestion like where to start.
column_name Null? Type
------------------------------- ---------------- -------------------
SYS_CONSTANT_ID NOT NULL NUMBER
I just checked the dba_tab_statistics, stats were gathered on this table in August 2010. I gather stats on it now. Do you think this is going to be a solution or any important things I can check.
There may be many reasons for just a small table to give performance issue. Is the performance issue only for this table or for many other objects?
Initiallay you can Schedule to regularly Gather statistics for that table,
You can also pin the table to the DB Buffer so that the IO will be faster.
Create an index on a column that is there in the WHERE clause of the SQL Select statement.
May be you need to create a unique index or some other index on SYS_CONSTANT_ID column.
You need to check all these to resolve the performance issue for this small table.
What version? When asking questions, always include version. The answer you get may vary depending on the version.
What does the explain plan look like for the table? It could be that the table is small enough that it is always read into memory in one read. How frequently is it updated? Are there indexes? are they used? have they been analyzed? Are you having issues with the filesystem? Using OEM, look at the sqlid and look at the statistics.