[code]CREATE OR REPLACE PROCEDURE PMDAT.ANALYZE_DWH_TABS(P_TABNAME VARCHAR2) IS
CURSOR C1 IS
SELECT
USER
FROM
DUAL
WHERE
ROWNUM = 1;

V_OWNER VARCHAR2(32) := NULL;
V_TABNAME VARCHAR2(32) := UPPER(P_TABNAME);

CURSOR C2 IS
SELECT
CASE
WHEN PARTITION_NAME IS NULL THEN
TABLE_NAME
ELSE
CASE
WHEN SUBPARTITION_NAME IS NULL THEN
PARTITION_NAME
ELSE
SUBPARTITION_NAME
END
END
ANALYZE_PARTITION
,CASE
WHEN PARTITION_NAME IS NULL THEN
'GLOBAL'
ELSE
CASE
WHEN SUBPARTITION_NAME IS NULL THEN
'PARTITION'
ELSE
'SUBPARTITION'
END
END
GRANULARITY
FROM
USER_TAB_STATISTICS
WHERE
COALESCE(STALE_STATS, 'YES') = 'YES'
AND TABLE_NAME = V_TABNAME
ORDER BY
2 DESC;


CURSOR C3 IS
SELECT
DISTINCT S.INDEX_NAME
,CASE
WHEN PARTITION_NAME IS NULL THEN
TABLE_NAME
ELSE
CASE
WHEN SUBPARTITION_NAME IS NULL THEN
PARTITION_NAME
ELSE
SUBPARTITION_NAME
END
END
ANALYZE_PARTITION
,CASE
WHEN PARTITION_NAME IS NULL THEN
'GLOBAL'
ELSE
CASE
WHEN SUBPARTITION_NAME IS NULL THEN
'PARTITION'
ELSE
'SUBPARTITION'
END
END
GRANULARITY
FROM
USER_IND_STATISTICS S
WHERE
S.STALE_STATS <> 'NO'
AND S.TABLE_NAME = V_TABNAME
order by 3 desc ;

BEGIN
FOR C1_REC IN C1 LOOP
V_OWNER := C1_REC.USER;
END LOOP;

FOR C2_REC IN C2 LOOP
IF C2_REC.GRANULARITY = 'GLOBAL' THEN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => V_OWNER
,TABNAME => V_TABNAME
,METHOD_OPT => 'FOR ALL INDEXED COLUMNS'
,GRANULARITY => C2_REC.GRANULARITY
,NO_INVALIDATE => FALSE
,DEGREE => 5
,CASCADE => TRUE
);
ELSE
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => V_OWNER
,TABNAME => V_TABNAME
,PARTNAME => C2_REC.ANALYZE_PARTITION
,METHOD_OPT => 'FOR ALL INDEXED COLUMNS'
,GRANULARITY => C2_REC.GRANULARITY
,NO_INVALIDATE => FALSE
,DEGREE => 5
,CASCADE => TRUE
);
END IF;
END LOOP;

FOR C3_REC IN C3 LOOP
IF C3_REC.GRANULARITY = 'GLOBAL' THEN
DBMS_STATS.GATHER_INDEX_STATS(
OWNNAME => V_OWNER
,INDNAME => C3_REC.INDEX_NAME
,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
,DEGREE => 5
,GRANULARITY => C3_REC.GRANULARITY
,NO_INVALIDATE => FALSE
);
ELSE
DBMS_STATS.GATHER_INDEX_STATS(
OWNNAME => V_OWNER
,INDNAME => C3_REC.INDEX_NAME
,PARTNAME => C3_REC.ANALYZE_PARTITION
,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
,DEGREE => 5
,GRANULARITY => C3_REC.GRANULARITY
,NO_INVALIDATE => FALSE
);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, SQLCODE || SQLERRM);
END;
/
[/pre]

Afaik (please correct me if I'm wrong), STALE means, that Oracle thinks that the statistic of how the values are spread out over the object in focus (table / partion / index / local index) has changed so much, that it is no longer a reliable source for the oracle optimizer to base its estimations on. 10% is the default value of changes in an object that can be varied before Oracle sets the Status to stale. This percentage can be set by the DBA.