Useful Gather Statistics Commands In Oracle
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, DBMS_STATS.GATHER_DICTIONARY_STATS and DBMS_STATS.GATHER_SYSTEM_STATS
======================================
General
======================================
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS
DBMS_STATS.GATHER_SYSTEM_STATS
======================================
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
======================================
This would collect statistics about
Fixed objects.
These are the X$ and K$ tables and
their indexes.
The V$ views in Oracle are defined in
top of X$ tables (for example V$SQL and V$SQL_PLAN).
How
to identify when DBMS_STATS.GATHER_FIXED_OBJECTS_STATS was
executed in the database ?
SELECT v.name, ts.analyzetime
FROM V$FIXED_TABLE v, SYS.TAB_STATS$ ts
WHERE v.object_id = ts.obj#;
no rows returned
SELECT COUNT(*) FROM sys.tab_stats$
count(*) was 0.
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);
This takes few minutes.
SELECT COUNT(*) FROM sys.tab_stats$
returns 761
======================================
DBMS_STATS.GATHER_DICTIONARY_STATS
======================================
This procedure gathers statistics for dictionary schemas 'SYS', 'SYSTEM'.
When
was it last run?
Check the the last_analyzed column for tables owned by SYS.
SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES WHERE owner = 'SYSTEM';
MAX(LAST_ANALYZED)
------------------
27-JUN-20
SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES WHERE owner = 'SYS';
MAX(LAST_ANALYZED)
------------------
27-JUN-20
======================================
DBMS_STATS.GATHER_SYSTEM_STATS
======================================
This procedure gathers system statistics.
The actual gathered statistics would depend upon system being under workload, or not.
DBMS_STATS.GATHER_SYSTEM_STATS procedure gathers statistics relating to the performance of your systems I/O and CPU.
Giving the optimizer this information makes its choice of execution plan more accurate, since it is able to weigh the relative costs of operations using both the CPU and I/O profiles of the system.
When was DBMS_STATS.GATHER_SYSTEM_STATS last run?
The output from DBMS_STATS.GATHER_SYSTEM_STATS is stored in the AUX_STATS$ table.
SELECT * FROM SYS.AUX_STATS$;
How to execute DBMS_STATS.GATHER_SYSTEM_STATS?
Option A. – no workload
All databases come bundled with a default set of no-workload statistics, but they can be replaced with more accurate information.
When gathering no-workload stats, the database issues a series of random I/Os and tests the speed of the CPU.
As you can imagine, this puts a load on your system during the gathering phase.
EXEC DBMS_STATS.GATHER_SYSTEM_STATS;
Option B. - Workload
When initiated using the start/stop or interval parameters, the database uses counters to keep track of all system operations, giving it an accurate idea of the performance of the system.
If workload statistics are present, they will be used in preference to no-workload statistics.
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- Wait some time, say 120 minutes, during workload hours
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');
or
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval => 120);
======================================
When to run these procedures?
======================================
- When there was a change to init.ora Instance parameters
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
- When there was a change to dictionary structure - new schema, etc.
DBMS_STATS.GATHER_DICTIONARY_STATS
- When there was a major change to the host hardware.
DBMS_STATS.GATHER_SYSTEM_STATS
No comments:
Post a Comment