Collecting statistics using DBMS_STATS

When Oracle introduced cost based optimizer, the only statement to gather statistics was

SQL> Analyze table EMP compute statistics;

Starting with Oracle8 the DBMS_STATS package as been introduced to offer many useful functions to gather and manage statistics, in this article we’ll take a quick look.

Functions contained in DBMS_STATS allow us to

Gather Statistics

Following procedures allows us to gather stats:

GATHER_DATABASE_STATS
GATHER_DICTIONARY_STATS
GATHER_FIXED_OBJECTS_STATS
GATHER_INDEX_STATS
GATHER_SCHEMA_STATS
GATHER_SYSTEM_STATS
GATHER_TABLE_STATS

These procedures allow us to gather stats for the whole db, at schema level or for single objects.
The GATHER_SYSTEM_STATS is the only one that doesn’t gather object statistics but hardware (CPU and disks) ones .
Statistics can be written in the data dictionary (ed used by the optimizer), or in a database table (created using the CREATE_STAT_TABLE procedure).

Manage Statistics

The following procedures:

PREPARE_COLUMN_VALUES
PREPARE_COLUMN_VALUES_NVARCHAR2
PREPARE_COLUMN_VALUES_ROWID
SET_COLUMN_STATS
SET_INDEX_STATS
SET_SYSTEM_STATS
SET_TABLE_STATS
GET_COLUMN_STATS
GET_INDEX_STATS
GET_SYSTEM_STATS
GET_TABLE_STATS

Allow us to manage stats setting or getting specific values.
PREPARE* procedures are useful to convert values to oracle internal formats to set them using SET_COLUMN_VALUE.

Delete Statistics

Some procedures allow us to delete stats:

DELETE_COLUMN_STATS
DELETE_DATABASE_STATS
DELETE_DICTIONARY_STATS
DELETE_FIXED_OBJECTS_STATS
DELETE_INDEX_STATS
DELETE_SCHEMA_STATS
DELETE_SYSTEM_STATS
DELETE_TABLE_STATS

Export/Import Statistics

As already seen stats can be written either in the data dictionary or into a specific database table.
Stats collected into the table can’t be used by the optimizer.
To create a database table suited to contain stats we have to use the following procedure

CREATE_STAT_TABLE

That can be dropped using

DROP_STAT_TABLE

There are some procedures useful to export stats from the data dictionary to the table:

EXPORT_COLUMN_STATS
EXPORT_DATABASE_STATS
EXPORT_DICTIONARY_STATS
EXPORT_FIXED_OBJECTS_STATS
EXPORT_INDEX_STATS
EXPORT_SCHEMA_STATS
EXPORT_SYSTEM_STATS
EXPORT_TABLE_STATS

Or from the table to the data dictionary:

IMPORT_COLUMN_STATS
IMPORT_DATABASE_STATS
IMPORT_DICTIONARY_STATS
IMPORT_FIXED_OBJECTS_STATS
IMPORT_INDEX_STATS
IMPORT_SCHEMA_STATS
IMPORT_SYSTEM_STATS
IMPORT_TABLE_STATS

Lock and unlock statistics

If we need to freeze an object’s stats we can use
LOCK_SCHEMA_STATS
LOCK_TABLE_STATS

The unlock can be achieved using
UNLOCK_SCHEMA_STATS
UNLOCK_TABLE_STATS

This feature is really useful when we don’t want Oracle to gather statistics for a specific table.
We can achieve this deleting the stats and then calling LOCK_TABLE_STATS:


SQL> BEGIN
  2  DBMS_STATS.DELETE_TABLE_STATS('MAXR','EMP');
  3  DBMS_STATS.LOCK_TABLE_STATS('MAXR','EMP');
  4  END;
  5  /

After this the stats are and will remain empty.

Purge and Restore statistics

The PURGE_STATS procedure purges all the stats deleted before a given timestamp.

If, on the other hand, we need to restore statistics as they were at a given timestamp we can use:

RESTORE_DICTIONARY_STATS
RESTORE_FIXED_OBJECTS_STATS
RESTORE_SCHEMA_STATS
RESTORE_SYSTEM_STATS
RESTORE_TABLE_STATS

Pending Statistics

Sometimes we need to have a preview of the effect of gathered statistics before activating them.
This is why we can use “Pending Statistics”.
To mark gathered statistics as “pending”, hence temporarily inactive, we hate to set to FALSE the PUBLISH parameter using one of the following:

SET_DATABASE_PREFS
SET_GLOBAL_PREFS
SET_SCHEMA_PREFS
SET_TABLE_PREFS

For example, to mark as pending the stats on the EMP table:


SQL> begin
  2  DBMS_STATS.SET_TABLE_PREFS ('MAXR', 'EMP', 'PUBLISH', 'FALSE');
  3  end;
  4  /

Once gathered the stats and evaluated their impact on execution plans we can decide to delete them:
DELETE_PENDING_STATS

Export them in a table:
EXPORT_PENDING_STATS

Mark them as published (used by optimizer):
PUBLISH_PENDING_STATS

Comparing Statistics

We can obtain a comparative report between two version of the stats using
DIFF_TABLE_STATS_IN_HISTORY
DIFF_TABLE_STATS_IN_PENDING
DIFF_TABLE_STATS_IN_STATTAB

These are pipelined functions that can be used in the FROM clause of a query statement.
Let’s see an example.

First of all let’s create a test table:


SQL> create table testtab as select * from emp;

It’s like the EMP table, even as data content.
Let’s gather statistics:


SQL> exec DBMS_STATS.GATHER_TABLE_STATS('MAXR','TESTTAB')

SQL> select systimestamp  from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
28-FEB-10 21:44:41,859000 +01:00

Now let’s insert more records:


SQL> insert into testtab
  2  select * from testtab;

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> r
  1  insert into testtab
  2* select * from testtab

SQL> commit;

And gather stats again:


SQL> exec DBMS_STATS.GATHER_TABLE_STATS('MAXR','TESTTAB');

Now we are ready to compare:


SQL> set long 5000
SQL> select * from
  2* table(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY(
           'MAXR',
           'TESTTAB',
           to_timestamp('20100228214442','yyyymmddhh24miss'),
           systimestamp,
           0)
           );

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################      819100

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : TESTTAB
OWNER         : MAXR
SOURCE A      : Statistics as of 28-FEB-10 21:44:42,000000 +01:00
SOURCE B      : Statistics as of 28-FEB-10 21:50:16,359000 +01:00
PCTTHRESHOLD  : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

TESTTAB                     T   A   14         4          37         14
                                B   114688     751        37         114688
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

COMM            A   4       ,25        NO   10      2    80    C20F  4
                B   4       ,25        NO   81920   2    80    C20F  32768
MGR             A   6       ,166666666 NO   1       4    C24C4 C2500 13
                B   6       ,166666666 NO   8192    4    C24C4 C2500 106496
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

Extended statistics

DBMS_STATS allows us also to gather statistics on groups of columns or expressions. These are known as “extended statistics”.
Statistics on groups of columns are used by optimizer when, in a query statement, there are conditions on more than one column. A relation between columns (evaluated reading extended stats) can cause the optimizer to choose a different execution plan.

To create extended statistics we can use
CREATE_EXTENDED_STATS

to drop them:
DROP_EXTENDED_STATS

To see extensions’ names:
SHOW_EXTENDED_STATS_NAME

For example, to create an extended statistic on the correlation between EMPNO and ENAME:


SQL> exec dbms_output.put_line(DBMS_STATS.CREATE_EXTENDED_STATS('MAXR','TESTTAB','(EMPNO,ENAME)'))
SYS_STU7CJ_LJ#28ARDED17#AUWYJZ

I’ve printed the return value that is the name of the extension created.

If I lose this name I can always get it using SHOW_EXTENDED_STATS_NAME:

SQL> select dbms_stats.SHOW_EXTENDED_STATS_NAME('MAXR','TESTTAB','(ENAME, EMPNO)')
  2  from dual;

DBMS_STATS.SHOW_EXTENDED_STATS_NAME('MAXR','TESTTAB','(ENAME,EMPNO)')
---------------------------------------------------------------------------------------
SYS_STU7CJ_LJ#28ARDED17#AUWYJZ

SQL> select dbms_stats.SHOW_EXTENDED_STATS_NAME('MAXR','TESTTAB','(DEPTNO, ENAME)')
  2  from dual;
select dbms_stats.SHOW_EXTENDED_STATS_NAME('MAXR','TESTTAB','(DEPTNO, ENAME)')
       *
ERROR at line 1:
ORA-20000: extension "(DEPTNO, ENAME)" does not exist
ORA-06512: a "SYS.DBMS_STATS", line 25416

That, as you can see in the last example, gives error if we try to get the name of an undefined extension.

Another kind of extension that we can create is on an expression, for example:

SQL>  exec dbms_output.put_line(DBMS_STATS.CREATE_EXTENDED_STATS('MAXR','TESTTAB','(TO_CHAR(EMPNO))'))
SYS_STUM9HJB83_TSA6P91FJMZ84U9

That’s all, by now, about the DBMS_STATS package.

Massimo

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s


%d bloggers like this: