How to Use Synopsis

How to Use Synopsis

What do you need to do to use new-style synopses? Nothing! They are used by default if you choose to use incremental statistics and you don’t need to do anything different in Oracle Database 12c Release 2 compared to earlier releases.

Controlling Synopses

You can control the type of that will be created using a DBMS_STATS preference called APPROXIMATE_NDV_ALGORITHM.

The default is REPEAT OR HYPERLOGLOG: if a table is using old-style synopses then it will continue to do so, and tables using new-style synopses will continue to use those!

There’s no reason to use anything other than the default unless you are upgrading a database to Oracle Database 12c Release 2. If this is the case then you might want to consider the options

Upgrading

If you are upgrading a database that’s using incremental statistics, then you will want to migrate to using the new-style synopses. How do you go about doing that? It’s worth noting from the outset that it’s possible to have partitions with old-style and new-style synopses in the same table. Also, the good news is that you can control when and how to transition from one type of synopses to the other.

There is a DBMS_STATS preference called INCREMENTAL_STALENESS. It the controls whether or not you want to allow partitions within an individual table to have different types of synopses during the transition period from old-style to new-style.

You have some very large partitioned tables. They are using old-style synopses and you want to gradually replace the old with the new.

EXEC DBMS_STATS.SET_TABLE_PREFS(

????????<table_owner>,

????????<table_name>,

????????'APPROXIMATE_NDV_ALGORITHM',

????????'HYPERLOGLOG')

Note that INCREMENTAL_STALENESS preference must have the value ALLOW_MIXED_FORMAT, but it does not need to be set explicity (unless you've changed it) because it is the default setting.

If you have a window of time to completely re-gather statistics for partitioned tables, then this is the recommended approach.

EXEC DBMS_STATS.SET_TABLE_PREFS(

????????<table_owner>,

????????<table_name>,

????????'APPROXIMATE_NDV_ALGORITHM',

????????'HYPERLOGLOG')

You also need to specify that you don’t want a mix of old synopses and new synopses in the same table:

EXEC DBMS_STATS.SET_TABLE_PREFS (

???<table_owner>,

???<table_name>,

???'INCREMENTAL_STALENESS',

?????'NULL')

You need to take some care here. The preference value should be set to 'NULL' (in quotes) and not NULL (without quotes). NULL (without quotes) sets a preference to its default value, which in this case is ALLOW_MIXED_FORMAT.

Once these preferences are set you will need to re-gather the table’s statistics.

Remember that you can also set DBMS_STATS preferences (such as APPROXIMATE_NDV_ALGORITHM) at the database, global and schema-level as well as at the table level?

The synopsis format in Oracle Database 12c Release 2 is much more compact than the previous format. If your database is very large, expect to save a lot of space in SYSAUX while maintaining very good accuracy for your statistics. You can expect the system overhead required to manage synopses to drop too (for example, when you exchange partitions).

Some useful scripts courtesy of Mike Dietrich

rem How many tables have incremental stats on?

rem =============================

?select count(distinct bo#) from sys.wri$_optstat_synopsis_head$;

rem How many partitions does your WRI$_OPSTATS_SYNOPSIS$ have?

rem ======================================

?select partition_count from dba_part_tables where table_name='WRI$_OPTSTAT_SYNOPSIS$';

rem How large is your synopsis table?

rem ======================

?select sum(bytes/(1024*1024)) "MB" , sum(bytes/(1024*1024*1024)) "GB" from dba_segments where segment_name='WRI$_OPTSTAT_SYNOPSIS$';

rem Tables where inc stats are ON?

rem ====================

?select u.name "OWNER" ,o.name "TABLE_NAME" ,p.valchar

?from sys.OPTSTAT_USER_PREFS$ p

??inner join sys.obj$ o on p.obj#=o.obj#

??inner join sys.user$ u on o.owner#=u.user#

?where p.PNAME = 'INCREMENTAL';

rem Synopsis for tables which don’t exist anymore?

rem ===============================

?select distinct h.bo#?

?from sys.wri$_optstat_synopsis_head$ h?

?where not exists (select 1 from sys.tab$ t where t.obj# = h.bo#);

Script to show global DBMS STATS Preferentials

set lines 200

set pages 62

set wrap on

column sname format a40

column spare4 format a60

select sname,spare4

from sys.OPTSTAT_HIST_CONTROL$

order by sname