Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Monday, July 4, 2011

Oracle automatically keeps the old, historical table statistics

After Oracle version 10g, Oracle started to keep the old, historical table statistics in the database automatically so that you can use them, in case you need them later.
If a specific SQL starts to perform badly in the database and you realized that the reason is the statistics of the table changed a lot since the last statistics collected on that table, you can use the previous old statistics of this table by restoring them in a very short time, without any need to collect them from scratch which could take a long time.

Below is how you can accomplish to query and restore those old table statistics.

Firs connect to a sample schema in database.
[oracle@localhost uural]$ sqlplus u0001/u0001

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 4 04:03:56 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Then create a sample table and collect statistics on this table by using "dbms_stats.gather_table_stats" procedure.
SQL> create table t1 as
  2  select
  3          rownum                          id,
  4          dbms_random.string('U',6)       v1,
  5          lpad(rownum,10)                 v2,
  6          rpad('x',100)                   padding
  7  from
  8          all_objects
  9  where
 10          rownum <= 1000
 11  ;

Table created.
You can query the old statistics info in a view in the database called "user_tab_stats_history". Since there is no statistics collected on this table we will not see any rows returning from this view.
SQL> select * from user_tab_stats_history;

no rows selected
Now collect the statistics on the new table.
SQL> begin
  2          dbms_stats.gather_table_stats(
  3                  ownname          => user,
  4                  tabname          => 'T1',
  5                  cascade          => true,
  6                  estimate_percent => null,
  7                  granularity      => 'default',
  8                  method_opt       => 'for all columns size 1'
  9          );
 10  end;
 11  /

PL/SQL procedure successfully completed.
Now check the "num_rows" info of table in "user_tables" view.
SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                   1000
Check the "user_tab_stats_history" view again.
SQL> set lines 250
SQL> r
  1* select * from user_tab_stats_history

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T1                                                                                           04-JUL-11 04.10.24.567434 AM -07:00

Now truncate the table and collect the statistics again, and check the "user_tab_stats_history" view.
SQL> truncate table t1;

Table truncated.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                   1000

SQL> begin
        dbms_stats.gather_table_stats(
  2    3                  ownname          => user,
  4                  tabname          => 'T1',
  5                  cascade          => true,
  6                  estimate_percent => null,
  7                  granularity      => 'default',
  8                  method_opt       => 'for all columns size 1'
  9          );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

SQL> select * from user_tab_stats_history;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T1                                                                                           04-JUL-11 04.10.24.567434 AM -07:00
T1                                                                                           04-JUL-11 04.16.39.947784 AM -07:00

This sample table has only 1000 rows being relatively small, but suppose you have a big table with 100 million rows in it, and it takes quiet a long time to collect the statistics. If this table is truncated and statistics refreshed, then 100 million rows are inserted back to this table and new table statistics are not refreshed yet and an SQL in database is performing bad because the statistics of this table is not up-to-date. In that case if you have the old statistics from the time that this table had 100 million rows you can try to restore that statistics and try to tune the SQL. Below is how you can restore the old statistics. First insert 1000 rows back to that table, check the "num_rows" column in "user_tables" view.
SQL> insert into t1
  2  select
  3          rownum                          id,
  4          dbms_random.string('U',6)       v1,
  5          lpad(rownum,10)                 v2,
  6          rpad('x',100)                   padding
  7  from
  8          all_objects
  9  where
 10          rownum <= 1000
 11  ;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

Restore the old statistics back.
SQL> begin
  dbms_stats.restore_table_stats (ownname         => 'U0001',
  2    3                                    tabname         => 'T1',
  4                                    as_of_timestamp =>'04-JUL-11 04.10.24.567434 AM -07:00');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                   1000

SQL> select * from user_tab_stats_history;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T1                                                                                           04-JUL-11 04.10.24.567434 AM -07:00
T1                                                                                           04-JUL-11 04.16.39.947784 AM -07:00
T1                                                                                           04-JUL-11 04.31.24.129217 AM -07:00
As you can see, we restored the statistics of first row in the above "user_tab_stats_history" output, and Oracle created a new row stating that the statistics of the table has changed after the restore operation. Oracle keeps those old statistics for 31 days and after that automatically removes them which are older than 31 days. You can query that parameter "the history retention value" with the following SQL.
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31
Below function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.
SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
03-JUN-11 12.46.58.682468000 AM -07:00
Automatic purging is enabled when STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If automatic purging is disabled, the old versions of statistics need to be purged manually using the PURGE_STATS procedure. You can query that parameter like below.
[oracle@localhost uural]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 4 04:45:47 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL>
You can change the history retention value like below. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges. retention parameter : The retention time in days. The stats history will be retained for at least these many number of days.The valid range is [1,365000]. Also the following values can be used for special purposes. 0 - old stats are never saved. The automatic purge will delete all stats history -1 - stats history is never purged by automatic purge. null - change stats history retention to default value
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> exec dbms_stats.alter_stats_history_retention(retention => 45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         45
Set it back to default value.
SQL> exec dbms_stats.alter_stats_history_retention(retention => null);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL>

4 comments:

Anish Jayadevan said...

Very Nice explanation.

Anish
Oracle DBA
IBM

Jean Rodrigues said...

Congratz. Very productive and objective explanation.

Invinci said...

Thanks for the explanation.

Ural Ural said...

Hi guys, you're all welcome to my Oracle blog page.

Cheers,
Ural