How to generate statistics on a table in Oracle

It is usual that any production application will have performance issues and it could be due to a slow performing query. As a first step in resolving the issue, we regenerate stats on the tables which are used in the functionality.

Beginning Oracle 9i, use the following syntax to generate statistics.

begin
DBMS_STATS.gather_table_stats('SCOTT', 'EMP', estimate_percent => 30, cascade => TRUE);
END;

I put this in an anonymous PL/SQL block otherwise it gives error

ORA-00900: invalid SQL statement

This uses dbms_stats procedure and it replaces ANALYZE TABLE used in Oracle 8i and before. As per Oracle from version 9i onwards ANALYZE TABLE is deprecated and should not be used.

What it does is regenerating stats on the table EMP on schema SCOTT. The option estimate_percent says that collect stats by using 30 percent of records. Option cascade tells to generate stats on any dependent tables and indexes.

Note that by default Oracle runs nightly jobs to generate stats. I have seen that nightly Oracle jobs regenerate statistics only when there is a change in data. If there were no updates to the table it won't regenerate stats.

In some cases stats generation can be locked and running the above SQL will not generate stats. We can see when was the last time stats was generated from the table ALL_TABLES. Look column LAST_ANALYZED.

SELECT TABLE_NAME, LAST_ANALYZED FROM ALL_TABLES WHERE OWNER = 'SCOTT' ORDER BY LAST_ANALYZED DESC

Alternatively table ALL_TAB_STATISTICS can be checked to see same detail and additionally it has column to show table is locked.

SELECT TABLE_NAME,STATTYPE_LOCKED,LAST_ANALYZED FROM  ALL_TAB_STATISTICS WHERE OWNER = 'SCOTT' ORDER BY LAST_ANALYZED;

Comments

Popular posts from this blog

java.util.logging - Bad level value for property: org.openqa.level

Solaris : Send mail with attachment

Suppressing JExcel warnings