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-17

SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES WHERE owner = 'SYS';

MAX(LAST_ANALYZED)
------------------
27-JUN-17

======================================
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$;

NAME                 PNAME                     PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_INFO        STATUS                          COMPLETED

SYSSTATS_INFO        DSTART                          10-26-2008 13:08

SYSSTATS_INFO        DSTOP                           10-26-2008 13:08

SYSSTATS_INFO        FLAGS                         1

SYSSTATS_MAIN        CPUSPEEDNW           1108.95499

SYSSTATS_MAIN        IOSEEKTIM                    10

SYSSTATS_MAIN        IOTFRSPEED                 4096

SYSSTATS_MAIN        SREADTIM

SYSSTATS_MAIN        MREADTIM

SYSSTATS_MAIN        CPUSPEED

SYSSTATS_MAIN        MBRC

SYSSTATS_MAIN        MAXTHR

SYSSTATS_MAIN        SLAVETHR

How to execute DBMS_STATS.GATHER_SYSTEM_STATS?

Option A. - noworkload

All databases come bundled with a default set of noworkload statistics, but they can be replaced with more accurate information. 

When gathering noworkload 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 noworkload 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?

======================================

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
- When there was a change to init.ora Instance parameters

DBMS_STATS.GATHER_DICTIONARY_STATS

- When there was a change to dictionary structure - new schema, etc.

DBMS_STATS.GATHER_SYSTEM_STATS

- When there was a major change to the host hardware.

======================================

Get Execution Plan

======================================

SET LINESIZE 200
SET PAGESIZE 0
SELECT sql_text, sql_id from V$SQL WHERE SQL_TEXT LIKE '%XXXX%';
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1ddfkrjxhvpt8'));