Pro-active Statistics Maintenance using PL/SQL

Customizing DBMS_STATS to Fix Stale Statistics

When a database is indicating a certain level of stale statistics that the DBA might have encountered when running logical backups, it is important to ensure that an operating procedure is put in place to achieve congruency and maintain a pattern of performance tuning, which is reasonably good to support a production environment.

Typically, stale statistics could remain undetected in a production environment when many processes are automated, and this can take place in spite of the usage of pro-active database administration in Oracle10g and in the recently release Oracle11g. Since the policy framework is not necessarily focused on detecting these stale statistics pro-actively, and in most cases no specific policies are established, it is important to customize available tools and PL/SQL API, such as DBMS_STATS to monitor and fix those performance threatening statistics.

Indeed, as an Oracle DBA and Consultant, I have presented several approaches to deal with stale statistics and other related performance issues, which have been resolved with the usage of tools or packages such as STATSPACK and DBMS_STATS. The following sample code can be used to fix stale statistics and in general to gather statistics consistently in general.

If you would like to test my code, you many need to explicitly grant the SELECT_CATALOG_ROLE and specific system object privileges for the SYS objects used in the package to the package owner that you use, e.g., PERFSTAT.

Once the package is compiled, you can be tested through its main entry point, at the bottom of the package body, in many different ways. For instance:

SQL> BEGIN

2 pkg_maintain_stats.std_fix_db_stats();

3 EXCEPTION

4 WHEN OTHERS THEN null;

5 END;

6 /

PL/SQL procedure successfully completed.

This is the PKG_MAINTAIN_STATS package code:

CREATE OR REPLACE PACKAGE PKG_MAINTAIN_STATS IS

-- Written by Anthony D. Noriega, OCP-DBA
-- Date: 12/21/2005
-- Updated 01/04/2006
-- Last Updated 01/11/2006
-- Last Tested: 01/18/2006

FUNCTION funTableExists(
ipTabName IN dba_tables.table_name%TYPE,
ipTabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT'
) RETURN BOOLEAN;

FUNCTION funGetTbs(ipSchema IN dba_users.username%TYPE) RETURN
dba_users.default_tablespace%TYPE;

FUNCTION funColIsIndexed(
ipSchema IN dba_tables.owner%TYPE,
ipTabName IN dba_tables.table_name%TYPE,
ipIndName IN dba_ind_columns.index_name%TYPE,
ipColName IN dba_ind_columns.column_name%TYPE
) RETURN BOOLEAN;

FUNCTION funAvgSchColLength(ipSchema IN dba_tables.owner%TYPE) RETURN NUMBER;

FUNCTION funAvgTabColLength(
ipSchema IN dba_tables.owner%TYPE,
ipTableName IN dba_tables.table_name%TYPE
) RETURN NUMBER;

FUNCTION funAvgIndColLength(
ipSchema IN dba_tables.owner%TYPE,
ipTableName IN dba_tables.table_name%TYPE
) RETURN NUMBER;

FUNCTION funGenSizeOpt(
ipOpt IN VARCHAR2 DEFAULT 'T',
ipChoice IN VARCHAR2 DEFAULT 'C',
ipSize IN INTEGER DEFAULT 100
) RETURN VARCHAR2;

FUNCTION funGetColSize(
ipSchema IN dba_tables.owner%TYPE,
ipTableName IN dba_tables.table_name%TYPE,
ipColumnName IN dba_tab_columns.column_name%TYPE
) RETURN dba_tab_columns.data_length%TYPE;

PROCEDURE grant_obj_priv(
ipGrantPrivs IN VARCHAR2,
ipGrantor IN dba_objects.owner%TYPE,
ipObject IN dba_objects.object_name%TYPE,
ipGrantee IN dba_objects.owner%TYPE
);
PROCEDURE grant_sys_priv(
ipSysPrivs IN VARCHAR2,
ipGrantee IN dba_objects.owner%TYPE
);

PROCEDURE revoke_sys_priv(
ipSysPrivs IN VARCHAR2,
ipGrantee IN dba_objects.owner%TYPE
);

PROCEDURE pop_grant_Revoke_plstabInit(
ipOpt IN VARCHAR2 DEFAULT 'G',
ipStabOwner IN dba_tables.owner%TYPE,
ipSchema IN dba_tables.owner%TYPE,
ipTabObj IN dba_objects.object_name%TYPE,
ipGrantee IN dba_tables.owner%TYPE DEFAULT 'PUBLIC'
);

PROCEDURE popStatTab(
ipSchema IN dba_tables.owner%TYPE,
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipTabName IN dba_tables.table_name%TYPE
);

PROCEDURE create_stat_table(
ipSchema IN dba_tables.owner%TYPE,
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipTbs IN dba_tables.tablespace_name%TYPE DEFAULT 'PERFSTAT'
) ;

PROCEDURE export_schema_stats(
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipSchema IN dba_tables.owner%TYPE
);
PROCEDURE purge_older_stats (
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipSchema IN dba_tables.owner%TYPE,
ipRetroDays IN NUMBER DEFAULT 1
);

PROCEDURE fix_schema_stats(
ipSchema IN dba_tables.owner%TYPE,
ipOpt IN VARCHAR2 DEFAULT 'T',
ipChoice IN VARCHAR2 DEFAULT 'C',
ipEstPct IN NUMBER DEFAULT 100
);

PROCEDURE std_fix_db_stats(
ipOpt IN VARCHAR2 DEFAULT 'T',
ipChoice IN VARCHAR2 DEFAULT 'C',
ipEstPct IN NUMBER DEFAULT 100,
ipStatsHistoryFlag IN BOOLEAN DEFAULT TRUE
);
END PKG_MAINTAIN_STATS;
/
CREATE OR REPLACE PACKAGE BODY PKG_MAINTAIN_STATS IS
FUNCTION funTableExists(
ipTabName IN dba_tables.table_name%TYPE,
ipTabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT'
) RETURN BOOLEAN IS
l_tab_name dba_tables.table_name%TYPE;
BEGIN
SELECT table_name
INTO l_tab_name
FROM dba_tables
WHERE table_name = ipTabName
AND owner = ipTabOwner;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;

FUNCTION funGetTbs(ipSchema IN dba_users.username%TYPE) RETURN
dba_users.default_tablespace%TYPE IS
l_tbs dba_users.default_tablespace%TYPE;
BEGIN
SELECT default_tablespace
INTO l_tbs
FROM dba_users
WHERE username = ipSchema;
RETURN l_tbs;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;

FUNCTION funColIsIndexed(
ipSchema IN dba_tables.owner%TYPE,
ipTabName IN dba_tables.table_name%TYPE,
ipIndName IN dba_ind_columns.index_name%TYPE,
ipColName IN dba_ind_columns.column_name%TYPE
) RETURN BOOLEAN IS
lv_ind_col_count INTEGER;
BEGIN
SELECT count(*)
INTO lv_ind_col_count
FROM dba_ind_columns
WHERE index_owner = ipSchema
AND table_name = ipTabName
AND index_name = ipIndName
AND column_name = ipColName;
IF lv_ind_col_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;

FUNCTION funAvgSchColLength(ipSchema IN dba_tables.owner%TYPE) RETURN NUMBER IS
lv_avg_sch_length NUMBER;
BEGIN
SELECT AVG(data_length)
INTO lv_avg_sch_length
FROM dba_tab_columns
WHERE owner = ipSchema;
RETURN lv_avg_sch_length;
EXCEPTION
WHEN OTHERS THEN RETURN 100;
END;

FUNCTION funAvgTabColLength(
ipSchema IN dba_tables.owner%TYPE,
ipTableName IN dba_tables.table_name%TYPE
) RETURN NUMBER IS
lv_avg_tab_length NUMBER;
BEGIN
SELECT AVG(data_length)
INTO lv_avg_tab_length
FROM dba_tab_columns
WHERE owner = ipSchema
AND table_name = ipTableName;
RETURN lv_avg_tab_length;
EXCEPTION
WHEN OTHERS THEN RETURN 65;
END;

FUNCTION funAvgIndColLength(
ipSchema IN dba_tables.owner%TYPE,
ipTableName IN dba_tables.table_name%TYPE
) RETURN NUMBER IS
lv_avg_ind_length NUMBER;
BEGIN
SELECT AVG(column_length)
INTO lv_avg_ind_length
FROM dba_ind_columns
WHERE index_owner = ipSchema
AND table_name = ipTableName;
RETURN lv_avg_ind_length;
EXCEPTION
WHEN OTHERS THEN RETURN 100;
END;

FUNCTION funGenSizeOpt(
ipOpt IN VARCHAR2 DEFAULT 'T',
ipChoice IN VARCHAR2 DEFAULT 'C',
ipSize IN INTEGER DEFAULT 100
) RETURN VARCHAR2 IS
BEGIN
IF ipOpt = 'I' THEN
CASE
WHEN ipChoice = 'C' THEN
RETURN 'FOR ALL INDEXED COLUMNS SIZE '||TO_CHAR(ipSize);
WHEN ipChoice = 'A' THEN
RETURN 'FOR ALL INDEXED COLUMNS SIZE AUTO';
WHEN ipChoice = 'R' THEN
RETURN 'FOR ALL INDEXED COLUMNS SIZE REPEAT';
WHEN ipChoice = 'S' THEN
RETURN 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY';
ELSE RETURN 'FOR ALL INDEXED COLUMNS SIZE 100';
END CASE;
ELSIF ipOpt IN ('S','T') THEN
CASE
WHEN ipChoice = 'C' THEN
RETURN 'FOR ALL COLUMNS SIZE '||TO_CHAR(ipSize);
WHEN ipChoice = 'A' THEN
RETURN 'FOR ALL COLUMNS SIZE AUTO';
WHEN ipChoice = 'R' THEN
RETURN 'FOR ALL COLUMNS SIZE REPEAT';
WHEN ipChoice = 'S' THEN
RETURN 'FOR ALL COLUMNS SIZE SKEWONLY';
ELSE RETURN 'FOR ALL COLUMNS SIZE 100';
END CASE;
ELSE RETURN 'FOR ALL COLUMNS SIZE 100';
END IF;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;


FUNCTION funGetColSize(
ipSchema IN dba_tables.owner%TYPE,
ipTableName IN dba_tables.table_name%TYPE,
ipColumnName IN dba_tab_columns.column_name%TYPE
) RETURN dba_tab_columns.data_length%TYPE IS
lv_csize dba_tab_columns.data_length%TYPE;
BEGIN
SELECT data_length
INTO lv_csize
FROM dba_tab_columns
WHERE owner = ipSchema
AND table_name = ipTableName
AND column_name = ipColumnName;
RETURN lv_csize;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;
PROCEDURE grant_obj_priv(
ipGrantPrivs IN VARCHAR2,
ipGrantor IN dba_objects.owner%TYPE,
ipObject IN dba_objects.object_name%TYPE,
ipGrantee IN dba_objects.owner%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'GRANT '||ipGrantPrivs||' ON '||ipGrantor||'.'||ipObject||' TO '||ipGrantee;
EXCEPTION
WHEN OTHERS THEN null;
END;

PROCEDURE grant_sys_priv(
ipSysPrivs IN VARCHAR2,
ipGrantee IN dba_objects.owner%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'GRANT '||ipSysPrivs||' TO '||ipGrantee;
EXCEPTION
WHEN OTHERS THEN null;
END;

PROCEDURE revoke_sys_priv(
ipSysPrivs IN VARCHAR2,
ipGrantee IN dba_objects.owner%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'REVOKE '||ipSysPrivs||' FROM '||ipGrantee;
EXCEPTION
WHEN OTHERS THEN null;
END;

PROCEDURE pop_grant_Revoke_plstabInit(
ipOpt IN VARCHAR2 DEFAULT 'G',
ipStabOwner IN dba_tables.owner%TYPE,
ipSchema IN dba_tables.owner%TYPE,
ipTabObj IN dba_objects.object_name%TYPE,
ipGrantee IN dba_tables.owner%TYPE DEFAULT 'PUBLIC'
) IS

TYPE g_SysPrivs_t IS TABLE OF dba_sys_privs.privilege%TYPE;
TYPE g_ObjPrivs_t IS TABLE OF dba_tab_privs.privilege%TYPE;
gTabSysPrivs g_SysPrivs_t;
gTabObjPrivs g_ObjPrivs_t ;

BEGIN

gtabSysPrivs(1) := 'ANALYZE ANY';
gtabSysPrivs(2) := 'SELECT_CATALOG_ROLE';
gtabSysPrivs(3) := 'SELECT ANY DICTIONARY';
gtabObjPrivs(1) := 'SELECT';
gtabObjPrivs(2) := 'INSERT';
gtabObjPrivs(3) := 'UPDATE';
gtabObjPrivs(4) := 'DELETE';

IF ipOpt = 'G' THEN
FOR i IN gTabObjPrivs.FIRST..gTabObjPrivs.LAST LOOP
grant_obj_priv(
ipGrantPrivs => gtabObjPrivs(i),
ipGrantor => ipStabOwner,
ipObject => ipTabObj,
ipGrantee => ipGrantee
);
grant_obj_priv(
ipGrantPrivs => gtabObjPrivs(i),
ipGrantor => ipSchema,
ipObject => ipTabObj,
ipGrantee => ipGrantee
);
END LOOP;
ELSE
IF ( ipGrantee NOT IN ('SYS','SYSTEM','PERFSTAT') ) THEN
FOR i IN gTabSysPrivs.FIRST..gTabSysPrivs.LAST LOOP
revoke_sys_priv(
ipSysPrivs => gtabSysPrivs(i),
ipGrantee => ipGrantee
);
END LOOP;
END IF;
END IF;

EXCEPTION
WHEN OTHERS THEN
null;
END;

PROCEDURE popStatTab(
ipSchema IN dba_tables.owner%TYPE,
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipTabName IN dba_tables.table_name%TYPE
) IS

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||ipStabOwner||'.'||ipTabName||' DROP STORAGE';
EXECUTE IMMEDIATE 'INSERT INTO '||ipStabOwner||'.'||ipTabName||' SELECT * FROM '||ipSchema||'.'||ipTabName;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Could not replicate statistics to '||ipStabOwner);
END;

PROCEDURE create_stat_table(
ipSchema IN dba_tables.owner%TYPE,
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipTbs IN dba_tables.tablespace_name%TYPE DEFAULT 'PERFSTAT'
) IS
l_stat_tab dba_tables.table_name%TYPE;
BEGIN
l_stat_tab := 'STATS$'||SUBSTR(ipSchema,1,17)||'_'||TO_CHAR(SYSDATE,'YYYYMMDD');
DBMS_STATS.create_stat_table(
ownname => ipSchema,
stattab => l_stat_tab,
tblspace => funGetTbs(ipSchema)
);
DBMS_STATS.create_stat_table(
ownname => ipStabOwner,
stattab => l_stat_tab,
tblspace => NVL(ipTbs,funGetTbs(ipStabOwner))
);

pop_grant_Revoke_plstabInit(
ipOpt => 'G',
ipStabOwner => ipStabOwner,
ipSchema => ipSchema,
ipTabObj => l_stat_tab,
ipGrantee => 'PUBLIC'
);

EXCEPTION
WHEN OTHERS THEN
null;
END;

PROCEDURE export_schema_stats(
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipSchema IN dba_tables.owner%TYPE
) IS
l_stat_tab dba_tables.table_name%TYPE;
BEGIN
l_stat_tab := 'STATS$'||SUBSTR(ipSchema,1,17)||'_'||TO_CHAR(SYSDATE,'YYYYMMDD');
dbms_stats.export_schema_stats(
ownname => ipSchema,
stattab => l_stat_tab
);
IF (
(ipSchema <> ipStabOwner)
AND
(ipSchema <> 'PERFSTAT')
) THEN
IF funTableExists(l_stat_tab,ipStabOwner) THEN
popStatTab( ipSchema => ipSchema,
ipStabOwner => NVL(ipStabOwner,'PERFSTAT'),
ipTabName => l_stat_tab
);
ELSE
EXECUTE IMMEDIATE 'create table '||ipStabOwner||'.'||l_stat_tab||' as select * from '||ipSchema||'.'||l_stat_tab;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN null;
DBMS_OUTPUT.put_line('Could not export statistics for schema '||ipSchema);
DBMS_OUTPUT.put_line(SQLERRM);
end;

PROCEDURE purge_older_stats (
ipStabOwner IN dba_tables.owner%TYPE DEFAULT 'PERFSTAT',
ipSchema IN dba_tables.owner%TYPE,
ipRetroDays IN NUMBER DEFAULT 1
) IS

CURSOR purgeCur(
cvStabOwner dba_tables.owner%TYPE,
cvSchema dba_tables.owner%TYPE,
cvRetroDays NUMBER
)
IS
SELECT table_name
FROM dba_tables
WHERE owner = cvStabOwner
AND ( table_name LIKE '%'||
DECODE(
TO_CHAR(SYSDATE,'DD'),'01',
DECODE(TO_CHAR(SYSDATE,'MM'),'01',
TO_CHAR(
TO_NUMBER(
TO_CHAR(SYSDATE,'YYYY')
)-1
)||'12',
TO_CHAR(SYSDATE,'YYYYMMDD')
),
TO_CHAR(SYSDATE,'YYYYMM')
)||'%'
AND
table_name LIKE 'STATS$%'||cvSchema||'%'
)
AND
table_name IN
(
SELECT object_name
FROM dba_objects
WHERE created > trunc(sysdate,'mm')
AND created < sysdate - cvRetroDays
);

l_stat_tab dba_tables.table_name%TYPE;

BEGIN

FOR purgeRec IN purgeCur(ipStabOwner,ipSchema,ipRetroDays) LOOP
BEGIN
DBMS_STATS.DROP_STAT_TABLE(
ownName => ipStabOwner,
statTab => purgeRec.table_name
);
DBMS_STATS.DROP_STAT_TABLE(
ownName => ipSchema,
statTab => purgeRec.table_name
);
EXCEPTION
WHEN OTHERS THEN null;
END;
END LOOP;
END;

PROCEDURE fix_schema_stats(
ipSchema IN dba_tables.owner%TYPE,
ipOpt IN VARCHAR2 DEFAULT 'T',
ipChoice IN VARCHAR2 DEFAULT 'C',
ipEstPct IN NUMBER DEFAULT 100
) IS
cursor psTabCur(
cv_tab_owner dba_tables.owner%TYPE
) IS
SELECT '"'||table_name||'"' table_name
FROM dba_tables
WHERE owner = cv_tab_owner
UNION
SELECT '"'||cluster_name||'"'
FROM dba_clusters
WHERE owner = cv_tab_owner;

cursor tabIndCur(
cv_tab_owner dba_tables.owner%TYPE,
cv_tab_name dba_tables.table_name%TYPE
) IS
SELECT DISTINCT index_owner,
index_name
FROM dba_ind_columns
WHERE table_name = LTRIM(RTRIM(cv_tab_name,'"'),'"')
AND table_owner = cv_tab_owner
ORDER BY 1;

lv_csize NUMBER;

lv_first_pass BOOLEAN := TRUE;

BEGIN

FOR psTabRec IN psTabCur(ipSchema) LOOP

BEGIN

CASE

WHEN ipOpt = 'I' THEN

lv_csize := funAvgIndColLength(ipSchema,psTabRec.table_name);

WHEN ipOpt = 'T' THEN

lv_csize := funAvgTabColLength(ipSchema,psTabRec.table_name);

WHEN ipOpt = 'S' AND lv_first_pass THEN

lv_csize := funAvgSchColLength(ipSchema);

lv_first_pass := FALSE;

ELSE
null;
END CASE;
BEGIN
dbms_stats.delete_table_stats(
ownname => ipSchema,
tabname => psTabRec.table_name
);
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
dbms_stats.gather_table_stats(
ownname => ipSchema,
tabname => psTabRec.table_name,
estimate_percent => ipEstPct,
method_opt => funGenSizeOpt(ipOpt,ipChoice, lv_csize)
);
EXCEPTION
WHEN OTHERS THEN
null;
END;

FOR tabIndRec IN tabIndCur(ipSchema,psTabRec.table_name)
LOOP
BEGIN
dbms_stats.delete_index_stats(
ownname => tabIndRec.index_owner,
indname => tabIndRec.index_name
);

dbms_stats.gather_index_stats(
ownname => tabIndRec.index_owner,
indname => tabIndRec.index_name,
estimate_percent => ipEstPct
);
EXCEPTION
WHEN OTHERS THEN null;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN null;
END;
END LOOP;
END;

PROCEDURE std_fix_db_stats(
ipOpt IN VARCHAR2 DEFAULT 'T',
ipChoice IN VARCHAR2 DEFAULT 'C',
ipEstPct IN NUMBER DEFAULT 100,
ipStatsHistoryFlag IN BOOLEAN DEFAULT TRUE
) IS
CURSOR dbSchemaCur IS
SELECT DISTINCT owner
FROM dba_tables
ORDER BY 1 desc ;

lv_verify_tab boolean;

lv_stat_tab dba_tables.table_name%TYPE;

BEGIN

FOR dbSchemaRec IN dbSchemaCur LOOP

IF ipStatsHistoryFlag THEN

create_stat_table(ipSchema => dbSchemaRec.owner);

export_schema_stats(ipSchema => dbSchemaRec.owner);

purge_older_stats(ipSchema => dbSchemaRec.owner);

END IF;

fix_schema_stats(
ipSchema => dbSchemaRec.owner,
ipOpt => ipOpt,
ipChoice => ipChoice,
ipEstPct => ipEstPct
);
END LOOP;
END;

END PKG_MAINTAIN_STATS;