This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

IBM DB2 10.1 Statistics Replication

Scenario: You would like to test a new index in an IBM DB2 10.1 database.  Unfortunately your development system doesn’t have anywhere near the same number of records in the database table on which you are creating the new index.

Like many other RDBMSs, DB2 uses table and column statistics to influence the optimiser’s decision as to which access path to choose at execution time.
By replicating only the statistics, it’s possible to fool the optimiser into thinking the table has more records present, than it really does.  This means that it’s likely to choose a different access path.

When performance tuning a database, it’s useful to use this method of fooling the optimiser, because you can emulate a larger table in a small development system with little actual data.

The process in DB2 is like this:
– Generate (or export) the statistics for a table in a production database system (PRD) schema DBA.
– Modify the export file.
– Upload the contents of the export file into a development database system (DEV) schema DBB.
– Test.

Step 1 – Export the statistics for a table in production.

Connect into the production database (DBA), then use the db2look command to create an export file containing the UPDATE commands for adjusting the statistics:

db2prd> db2 connect to PRD

db2prd> db2look -d PRD -e -c -m -r -t DBA.TABLE1 -o table1_STATS.sql

The output will be written to the table1_STATS.sql file in the current directory.

Step 2 – Modify the export file.
You should edit the output file to remove all lines before the line “– Mimic table TABLE1”, failure to do this could mean dropping the TABLE1 table in development.

You must also edit the file and replace all instances of schema “DBA” with “DBB” to ensure that the correct development database schema is found.
The modified file will look like:

— Mimic table TABLE1

UPDATE SYSSTAT.TABLES
SET CARD=2341434,
NPAGES=14636,
FPAGES=14645,
OVERFLOW=9473,
ACTIVE_BLOCKS=0
WHERE TABNAME = ‘TABLE1’ AND TABSCHEMA = ‘DBB’;

UPDATE SYSSTAT.COLUMNS
SET COLCARD=1,
NUMNULLS=0,

Step 3 – Upload the statistics into the development database.

db2dev> db2 connect to DEV
db2dev> db2 -tf ikpf_STATS.sql

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

You’re now ready to test.

To reset (re-gather) the statistics on the development database table, you simply need to re-collect statistics using the RUNSTATS command: “db2 RUNSTATS ON TABLE TABLE1 WITH DISTRIBUTION AND INDEXES ALL”.

HowTo: Using DBMS_STATS to Restore Stats

Scenario: You’re about to implement some major changes to the Oracle database that will adjust the database statistics.
Oracle provide the DBMS_STATS package to help administer stats.  The package includes some procedures that can be used to export/import stats, plus restore them to a previous point in time.

When statistics are updated using DBMS_STATS.GATHER_*_STATS, it saves the previous version in the database (can be changed with DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure).  Also, see table DBA_TAB_STATS_HISTORY.

These versions are retained for a specific retention period, which you can check using the GET_STATS_HISTORY_RETENTION procedure:

SQL> set serveroutput on
SQL> DECLARE
v number;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE(‘Stats history retention: ‘ || v || ‘ days.’);
END;
/

Stats history retention x days.

PL/SQL procedure successfully completed.

You can also check the date of the oldest stats history:

SQL> set serveroutput on
SQL> DECLARE
v timestamp;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
DBMS_OUTPUT.PUT_LINE(‘Oldest stats history: ‘ || v);
END;
/

Oldest stats history: 15-DEC-13 11.29.32.143688 PM

PL/SQL procedure successfully completed

To restore the statistics you can use one of the relevant procedures:

DBMS_STATS.RESTORE_DICTIONARY_STATS
DBMS_STATS.RESTORE_FIXED_OBJECT_STATS
DBMS_STATS.RESTORE_SCHEMA_STATS
DBMS_STATS.RESTORE_SYSTEM_STATS
DBMS_STATS.RESTORE_TABLE_STATS

See here for parameters:
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#insertedID2
As an example, the RESTORE_SCHEMA_STATS procedure takes the following parameters:

ownname   Schema owner,
timestamp   Timestamp,
force   TRUE|FALSE   Restore even if stats are locked, default TRUE,
no_invalidate   TRUE|FALSE   Invalidate cursors, default get_param(‘NO_INVALIDATE’).

If the stats are restored to a specific timestamp, it means that whatever statistics values were applicable to a specific table at a specific point in time, are applied to the tables.  If the table’s statistics are not changed then there will be gaps in the history.
You can imagine this being like a roll-forward through the DBA_TAB_STATS_HISTORY table, until the timestamp specified.

WARNING: If the table’s statistics are not changed then there will be gaps in the history.  In which case, you may not be able to restore previous statistics if the table stats have not changed within the last history window (default 31 days).

Some great examples are here: https://www.morganslibrary.org/reference/pkgs/dbms_stats.html

You should also note, that under an SAP system, the Oracle stats gatherer is called by BR*Connect, and note that it calls the GATHER_TABLE_STATS procedure for each table that is mentioned in table DBSTATC for tables that have stats enabled in DBSTATC.
If the table is not enabled to collect stats, then it may have stats delivered by SAP (see SAP note 1020260), in which case, there may not be any history.

Also see my blog post on SAP statistics and DBSTATC.

SAP HANA – Migrate Statistics Server 1917938

Since SAP note “1917938 – Migration of statistics server with upgrade to SPS 7” seems to be going missing rather a lot, I’ve noted the content here for reference based on v10 05-05-2014.

If you do not monitor or administrate the upgraded SAP HANA database with the DBA Cockpit or Solution Manager, you can activate the new statistics server. If the DBA Cockpit or Solution Manager is active, you are only allowed to activate the new statistics server if you observe SAP Note 1925684.

A configuration change is required to activate the new statistics server:

nameserver.ini -> [statisticsserver]->active=true

The data held in the persistence of the statistics server is now transferred to the persistence of the master index server. At the end of the migration, the statistics server is automatically stopped and removed from the database configuration (topology). The functions of the statistics server are distributed to other services.

The migration of the statistics server is carried out without interrupting the backup history, which means that data and log backups created before the migration can still be used to restore the SAP HANA database.

The HANA instance must not be restarted during the migration.
The migration is completed when no statisticsserver process is running in the HANA instance.
It is not necessary to restart the HANA instance following the migration.

SAP Statistics & DBSTATC

As I’ve blogged about before, SAP recommend that you disable the Oracle stats gathering job in Oracle 10g databases, and use their own job controlled through DB13 (or DB13c).

The reason for this, is that some of the SAP database tables have specific requirements when it comes to statistics gathering.  These tables are registered in the SAP table DBSTATC.
This table controls which tables should have statistics collected (active or not active) and the method used to collect the stats (estimate or full).
All other tables not in DBSTATC, will have their stats collected using the default BRConnect settings.

If you have custom “Z” tables, you can insert your own entry into the DBSTATC table if you know for sure that your table has specific requirements.

You can use transaction DB20 to check the status of statistics on tables.

When BRConnect runs, you will see in the log file how many tables have stats gathered using the default setting, vs those that are in DBSTATC.

Entries in DBSTATC which are disabled (should have not stats gathered) will register in BRConnect’s DBCHECK log as having harmful statistics if those tables are found to have statistics on them.  You can then use BR*Tools to remove the harmful statistics, but you should attempt to work out why you have got statistics on a table that you shouldn’t, otherwise they may re-appear.

You need to be aware that SAP provide a specific SAP note, 403704 that contains updates/revisions for entries in DBSTATC.  It may be worth reviewing this note once a year, as you may find some performance benefit from updated statistics, or removal of statistics on tables.

Take a look at Tom Cenens’ blog for additional information on this subject: https://scn.sap.com/people/tom.cenens3/blog/2011/01/24/improving-oracle-performance-by-maintaining-exception-table-dbstatc

SAP help for BRConnect: https://help.sap.com/saphelp_nw04/helpdata/en/88/fce73a86e99c77e10000000a114084/content.htm

Gathering Oracle Statistics – the SAP way

Gathering stats on a table in an Oracle database, can significantly change the SQL excution plan.
In some cases this can be detrimental to the system performance.
I have seen this in a real production system, where the indexed column was 38 characters long.  The system had just been upgraded from 9i, the original table stats carried over and left up to Oracle.
Performance was dreadful, the 9i stats had been refreshed but not re-created.

See SAP note 365480 (“CBO: Field filled with leading “0” aligned to left”).
It suffered from an Oracle “bug” (773462) where only the first 32 characters are used to measure the distinct number of values for the column.  In a 38 character field, left padded with zeros, using the index can cause a problem.
If we had removed all stats and re-created them, no problem, as Oracle 10g would have probably generated histograms.
However, the real issue was that the stats shouldn’t have been there according to DBSTATC.

NOTE: SAP notes say to specifically disable the default Oracle 10g/11g GATHER_STATS_JOB.

As noted, if you let Oracle gather statistics it overrides the settings in the SAP DBSTATC table.
The DBSTATC table (see transaction DB20) controls where stats are gathered and how stats are gathered.
In actual fact, this is why you sometimes see “Harmful statistics” in the BR*Connect DBcheck results.

The stats are gathered using the Oracle DBMS_STATS package with Netweaver 7.0’s version of BR*Tools (more specifically, BR*Connect).

Below are some SQL and PL/SQL commands useful for analysing statistics problems in a SAP Oracle 10g environment.

/* Check if gather stats auto-job is enabled */
SELECT JOB_NAME,
OWNER,
ENABLED,
PROGRAM_NAME,
TO_CHAR(last_start_date,'DD-MM-YYYY HH24:MI:SS') last_start_date
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME='GATHER_STATS_JOB';

/* Get GATHER_STATS_JOB program details */
SELECT PROGRAM_TYPE,
PROGRAM_NAME,
PROGRAM_ACTION
FROM dba_scheduler_programs
WHERE PROGRAM_NAME='GATHER_STATS_PROG';

/* Get the last time the GATHER_STATS_JOB ran and log info */
set linesize 400
col ADDITIONAL_INFO FORMAT A100
col LOG_DATE FORMAT a20
col JOB_NAME FORMAT A20
col RUN_DURATION FORMAT A25
col ACTUAL_START_DATE FORMAT A40
SELECT log_id,
actual_start_date,
run_duration,
job_name,
status,
log_date,
additional_info
FROM dba_scheduler_job_run_details
WHERE owner='SYS'
AND job_name='GATHER_STATS_JOB';

/* VERIFY STATS ON A COLUMN */
SELECT num_distinct,
density,
num_buckets,
sample_size,
avg_col_len,
histogram
FROM dba_tab_col_statistics
WHERE table_name = &TABNAME
AND column_name=&COLNAME;

/* VERIFY STATS ON A TABLE */
SELECT sample_size,
TO_CHAR(last_analyzed,'DD-MM-YYYY HH24:MI:SS') last_analzed,
global_stats,
user_stats,
stattype_locked,
stale_stats
FROM dba_tab_statistics
WHERE table_name = &TABNAME;

/* VERIFY HISTOGRAMS ON A TABLE */
SELECT *
FROM dba_tab_histograms
WHERE table_name=&TABNAME;

/* GATHER COLUMN STATS FOR A COLUMN ONLY */
BEGIN
       DBMS_STATS.GATHER_TABLE_STATS (
                                OWNNAME => 'SAPR3',
                                TABNAME => '< A TABLE>',
                                METHOD_OPT => 'FOR COLUMNS < A COLUMN> SIZE AUTO');
END;

/* GATHER TABLE STATS USING ESTIMATE 10% */
BEGIN
        DBMS_STATS.GATHER_TABLE_STATS (
                                 OWNNAME => 'SAPR3',
                                 TABNAME => '< A TABLE>',
                                 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
                                 ESTIMATE_PERCENT => 10);
END;

/* ORACLE'S DEFAULT JOB TO GATHER STATS */
dbms_stats.gather_database_stats_job_proc;

/* DELETE ALL THE TABLE STATS CASCADE TO INDEXES and COLUMNS */
BEGIN
        DBMS_STATS.DELETE_TABLE_STATS (
                                   OWNNAME => 'SAPR3',
                                   TABNAME => '< A TABLE>',
                                   cascade_parts => TRUE,
                                   cascade_columns => TRUE,
                                   cascade_indexes => TRUE);
END;

/* DELETE COLUMN STATS ONLY */
BEGIN
         DBMS_STATS.DELETE_COLUMN_STATS (
                                   OWNNAME => 'SAPR3',
                                   TABNAME => '< A TABLE>',
                                   COLNAME => '< A COLUMN>',
                                   FORCE => TRUE);
END;