Overview of Cost Based Optimizer

Let us get an overview of cost-based optimizer.

  • Databases use cost-based optimizer to generate explain plans. In the earlier days, they used to use rule based optimizer.

  • For cost based optimizer to generate optimal explain plan, we need to ensure statistics of our data in tables are collected at regular intervals.

  • We can analyze tables to collect statistics. Typically DBAs schedule to collect statistics at regular intervals.

  • In some cases we might have to compute statistics on the tables that are used in the query which we are trying to tune. The database user need to have permissions to compute statistics.

  • Here are some of the basic statistics typically collected.

    • Approximate number of records at table level.

    • Approximate number of unique records at index level.

  • When explain plans are generated, these statistics will be used by cost based optimizer to provide us with the most optimal plan for our query.