My Oracle Support Banner

RDBPROD: Cardinality Drift and Optimizer Strategy (Doc ID 1620148.1)

Last updated on FEBRUARY 05, 2020

Applies to:

Oracle Rdb Server on OpenVMS - Version 7.0 and later
HP OpenVMS Alpha
HP OpenVMS Itanium
HP OpenVMS VAX

Purpose

Several cardinality values are stored in the database system tables, giving the estimated number of rows in a table, the estimated number of unique rows in an index, and the estimated number of unique values for each segment of an index. The algorithm to update these estimates changed in Oracle Rdb 7.0.

The Oracle Rdb optimizer uses these stored, estimated cardinalities when choosing the retrieval strategy, for example Index A or Index B, sequential or indexed. Inaccurate cardinalities may lead to a less optimal strategy being chosen. Often there are reported inconsistencies in the performance of queries, updates or deletes against an Oracle Rdb database. In some cases the queries will take a few seconds to execute. In other cases the same queries will take many minutes to execute, sometimes a half an hour or more.

This article discusses the circumstances under which the estimated cardinality can become inaccurate, and recommends some regular preventative maintenance.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Details
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.