PL/SQL: When Were Your Tables Last Analysed?

As I'm sure any DBA will tell you one sure-fire way to have terrible-performing queries is not to update the statistical information Oracle is using to work out how to efficiently execute your query. Luckily Oracle keeps track of when the information was last updated for each column in the DBA_Table_Columns view.

Here is a simple query that will give you a list of tables that have not been analysed in the last week;

SELECT dtc.OWNER, dtc.TABLE_NAME, dtc.LAST_ANALYZED
  FROM dba_tab_columns dtc
 WHERE dtc.OWNER NOT IN ('SYS', 'SYSTEM')
   AND dtc.COLUMN_ID = 1
   AND dtc.TABLE_NAME NOT LIKE 'BIN$%'
   AND dtc.TABLE_NAME NOT LIKE '%$%'
   AND dtc.OWNER NOT LIKE '%$%'
   AND dtc.LAST_ANALYZED IS NOT NULL
   AND dtc.LAST_ANALYZED < TRUNC(SYSDATE) - 7
 ORDER BY dtc.LAST_ANALYZED DESC

This gives you a list of tables that haven't been analysed but, if you're looking at a large complex system like Oracle e-Business Suite (which I am) this isn't terribly useful as it's quite possible for table to not be analysed simply because it's not in use.

I've written this simple script that produces (via DBMS_OUTPUT) a list of the tables which contain records which haven't been analysed in the last week;

DECLARE
  v_RowCount  NUMBER;
  v_Startdate DATE;
  v_Enddate   DATE;
BEGIN
  dbms_output.put_line('"Table Owner","Table Name","Row Count","Last Analysed Date", "Time To Query"');
  FOR v_Table IN (SELECT dtc.OWNER, dtc.TABLE_NAME, dtc.LAST_ANALYZED
                    FROM dba_tab_columns dtc
                   WHERE dtc.OWNER NOT IN ('SYS', 'SYSTEM')
                     AND dtc.COLUMN_ID = 1
                     AND dtc.TABLE_NAME NOT LIKE 'BIN$%'
                     AND dtc.TABLE_NAME NOT LIKE '%$%'
                     AND dtc.OWNER NOT LIKE '%$%'
                     AND dtc.LAST_ANALYZED IS NOT NULL
                     AND dtc.LAST_ANALYZED < TRUNC(SYSDATE) - 7
                   ORDER BY dtc.LAST_ANALYZED DESC) LOOP
    v_Startdate := SYSDATE;
    BEGIN
      EXECUTE IMMEDIATE 'select count(*) from ' || v_Table.Owner || '.' ||
                        v_Table.Table_Name
        INTO v_RowCount;
    EXCEPTION
      WHEN OTHERS THEN
        v_RowCount := -1;
    END;
    v_Enddate := SYSDATE;
    IF v_RowCount > 0 THEN
      dbms_output.put_line(v_Table.Owner || ',' || v_Table.Table_Name || ',' ||
                           to_char(v_RowCount) || ',' ||
                           to_char(v_Table.Last_Analyzed, 'DD-MON-YYYY') || ',' ||
                           to_char((v_EndDate - v_StartDate),
                                   '0.9999999999'));
    END IF;
  END LOOP;
END;

Read More