System statistics in oracle database ?

 

In oracle database system statistics provide the optimizer with information about the system hardware and workload information , such cpu speed , i/o performance .

this statistic help the cost based optimizer make better decision when generating the execution plan  .

Why Gather System Statistics?

• To provide the optimizer with accurate information about the system's performance.
• To improve query execution plans by accounting for CPU and I/O costs.
• To adapt the optimizer's behavior to the specific hardware and workload of the database.

Types of System Statistics

NOWORKLOAD Statistics:

Collected when the database is idle or under minimal workload.

Provides baseline hardware characteristics like CPU speed and I/O performance.

Default system statistics provided by Oracle.

WORKLOAD Statistics:

Collected during a representative workload period.

Provides information about the system's performance under actual workload conditions.

Useful for databases with specific workload patterns.

Start gathering statistics:

BEGIN

  DBMS_STATS.GATHER_SYSTEM_STATS('START');

END;

/

Stop gathering statistics:

BEGIN

  DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

END;

/

Best Practices

Choose the Right Type of Statistics:

Use NOWORKLOAD statistics for general-purpose systems.

Use WORKLOAD statistics for systems with specific workload patterns.

Gather Statistics During Representative Workloads:

For WORKLOAD statistics, ensure the workload period reflects typical database usage.

Monitor and Update Regularly:

Gather system statistics periodically or after significant hardware changes (e.g., CPU or storage upgrades).

Backup Existing Statistics:

Before gathering new statistics, back up the current system statistics to restore them if needed.