DATABASE STATISTICS
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);
DBMS_STATS.DELETE_DATABASE_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
-- GATHER:
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => 20);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => 20, CASCADE => TRUE, DEGREE => 12);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE => TRUE, DEGREE=>12);
-- DELETE:
EXEC DBMS_STATS.DELETE_DATABASE_STATS;
-- VIEW:
-- To view all the objects in the database that have stale stats or even no stats at all:
SELECT 'Total Number of Stale Tables: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, TABLE_NAME OBJECT_NAME, 'TABLE' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NULL AND
(STALE_STATS IS NULL OR STALE_STATS='YES') AND
OWNER NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' ) AND
OWNER NOT LIKE 'FLOW%'
UNION ALL
SELECT 'Total Number of Stale Indexes: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, INDEX_NAME OBJECT_NAME, 'INDEX' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_IND_STATISTICS
WHERE STATTYPE_LOCKED IS NULL AND
(STALE_STATS IS NULL OR STALE_STATS='YES') AND
OWNER NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' ) AND
OWNER NOT LIKE 'FLOW%';
SCHEMA STATISTICS
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
DBMS_STATS.DELETE_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
-- GATHER:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', ESTIMATE_PERCENT => 20);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE => TRUE, GRANULARITY=>'ALL', DEGREE=>12);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', OPTIONS => 'GATHER AUTO');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', OPTIONS => 'GATHER EMPTY');
-- DELETE:
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
-- VIEW:
-- To view all the objects in a specific schema that have stale stats or even no stats at all:
SELECT 'Total Number of Stale Tables: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, TABLE_NAME OBJECT_NAME,'TABLE' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NULL AND
(STALE_STATS IS NULL OR STALE_STATS='YES') AND
OWNER IN ('SCOTT')
UNION ALL
SELECT 'Total Number of Stale Indexes: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, INDEX_NAME OBJECT_NAME,'INDEX' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_IND_STATISTICS
WHERE STATTYPE_LOCKED IS NULL AND
(STALE_STATS IS NULL OR STALE_STATS='YES') AND
OWNER IN ('SCOTT');
TABLE STATISTICS
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT 'GATHER');
DBMS_STATS.DELETE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
cascade_indexes BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
-- GATHER:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', ESTIMATE_PERCENT => 20, CASCADE => TRUE, DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', METHOD_OPT => 'FOR COLUMNS (EMPNO, ENAME)');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', ESTIMATE_PERCENT=> 100, METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS(EMPNO,ENAME)', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCOTT', TABNAME =>'EMP', ESTIMATE_PERCENT => 20, CASCADE => TRUE, DEGREE=>8, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
-- DELETE:
EXEC DBMS_STATS.DELETE_TABLE_STATS('SCOTT', 'DEPT');
-- VIEW:
To view stats information about a specific table:
SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE OWNER='SCOTT' AND TABLE_NAME='DEPT';
INDEX STATISTICS
DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
DBMS_STATS.DELETE_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'ALL',
force BOOLEAN DEFAULT FALSE);
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
-- GATHER:
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT', 'DEPT_PK', ESTIMATE_PERCENT => 20);
-- DELETE:
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT', 'DEPT_PK');
-- VIEW:
To view stats information about a specific index:
SELECT OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM DBA_IND_STATISTICS
WHERE OWNER='SCOTT' AND INDEX_NAME='EMP_PK';
DICTIONARY STATISTICS
DBMS_STATS.GATHER_DICTIONARY_STATS (
comp_id VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER AUTO',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL, get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);
DBMS_STATS.DELETE_DICTIONARY_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'ALL',
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
-- Dictionary statistics are sometimes confused with system statistics. The difference is that; system statistics record and represent the performance of the underlying hardware that a database resides upon. Dictionary statistics are statistics collected on the Oracle data dictionary tables.
--
-- When and Why? Data dictionary would not be expected to change significantly in terms of the distribution of data. Gathering statistics once or a few times over the year should be enough for most cases. However; if big changes were made like any upgrades or installation of Oracle components or a new application along with new schemas and many Oracle objects, then it might be reasonable to collect dictionary statistics.
-- GATHER:
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(estimate_percent => 20, degree=>12, cascade=> TRUE);
-- Statistics on the dictionary tables are normally maintained with the automatic statistics gathering job. If one day, you choose to disable the automatic statistics gathering job, you may still choose to collect the dictionary statistics only as follows:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');
END;
/
-- DELETE:
EXEC DBMS_STATS.DELETE_DICTIONARY_STATS;
-- VIEW:
SELECT NVL(TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD'), 'NO STATS') LAST_ANALYZED, COUNT(*) DICTIONARY_TABLES
FROM DBA_TABLES
WHERE OWNER = 'SYS'
GROUP BY TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD')
ORDER BY 1 DESC;
FIXED OBJECT STATISTICS
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')));
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
-- Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Many X$ tables for instance the ones behind DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS are protected by latches. Latch contention on large fixed objects is expensive, so without proper statistics, performance degradation is expected when sub-optimal plans happen.
--
-- Fixed object stats are not gathered automatically, so you need to gather them manually at a time when the database is in a representative level of activity.
-- GATHER:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
-- DELETE:
EXEC DBMS_STATS.DELETE_FIXED_OBJECTS_STATS;
-- VIEW:
SELECT NVL(TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD'), 'NO STATS') LAST_ANALYZED, COUNT(*) FIXED_OBJECTS
FROM DBA_TAB_STATISTICS
WHERE OBJECT_TYPE = 'FIXED TABLE'
GROUP BY TO_CHAR(LAST_ANALYZED, 'YYYY-MON-DD')
ORDER BY 1 DESC;
SYSTEM STATISTICS
DBMS_STATS.GATHER_SYSTEM_STATS (
gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD',
interval INTEGER DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
DBMS_STATS.DELETE_SYSTEM_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
-- System statistics provide information to the optimizer about the hardware and environment that the database is running on to allow it to adjust some core figures to reflect the specifics and loading of that environment.
--
-- There are mainly 2 modes of gathering system statistics; while there is "No Workload" and in the presence of a "Workload"
--
-- NO WORKLOAD
-- Will capture characteristics of the I/O system and the basic system information such as CPU performance. Oracle recommends to run GATHER_SYSTEM_STATS ('NOWORKLOAD') after creation of the database and tablespaces.
--
-- WORKLOAD
-- Workload statistics are not automatically gathered but need to be manually collected during representative workload.
-- GATHER:
-- No Workload
EXEC GATHER_SYSTEM_STATS ('NOWORKLOAD');
-- Workload Manually Starting $ Stopping
EXEC GATHER_SYSTEM_STATS (gathering_mode=>'START');
wait for some time for the representative workload
EXEC GATHER_SYSTEM_STATS (gathering_mode=>'STOP');
-- Gather for an interval... ex. 180 minutes.
DBMS_STATS.gather_system_stats('INTERVAL', INTERVAL => 180);
-- You can always stop the gathering earlier than scheduled by:
EXEC GATHER_SYSTEM_STATS (gathering_mode=>'STOP');
-- DELETE:
EXEC DBMS_STATS.DELETE_SYSTEM_STATS;
-- VIEW:
SELECT * FROM SYS.AUX_STATS$;