3 Replies Latest reply on Aug 8, 2012 4:27 PM by Neeraj_Gulia

    Small table read multiple times and having performance issues

      Hello All,

      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
      ------------------------------- ---------------- -------------------
      CONST_NAME          VARCHAR2(200)
      CONST_VALUE          VARCHAR2(4000)
      CREATED_ON          DATE
      LAST_MOD_ON          DATE

      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.

      Thanks in advance.
        • 1. Re: Small table read multiple times and having performance issues
          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.
          • 2. Re: Small table read multiple times and having performance issues
            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.
            • 3. Re: Small table read multiple times and having performance issues
              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.