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;
