This content has been marked as final. Show 3 replies
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.
Please share the explain plan of the Query.
IF you are using 11g then please use awrsqlrpt.sql ,it will give you hint regarding the bottlenecks of the query and some options how to improve its performance.