Wednesday, April 30, 2025

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.

No comments:

Post a Comment