Databases Reference
In-Depth Information
3.
Large data updates and/or additions will significantly change the statistics of the
database objects. These statistics are used by the query compiler during optimi-
zation and changes to them often result in new query execution plans. It is
common that performance problems can occur in a database system following
such large data changes for two reasons:
•
The data has changed and the old query execution plan is suboptimal for
the new data within the system.
•
The data has changed and the object statistics for one or more objects refer-
enced in the query were updated, resulting in the selection of a new query
execution plan that happens to be suboptimal due to poor choices by the
query compiler (i.e., during optimization).
4.
During product upgrade, the latest version of any relational database product
may include updates to the mathematical models used for query optimization.
Changes in modeling can result in changes in the selected query execution
plans. If the new plans perform worse than the old ones, it can be very benefi-
cial to debug the root cause of the problem if the before and after upgrade
query execution plans are available to compare and contrast. This is true for
both the database designer and the vendor customer service group should they
need to be called upon for assistance.
5.
Finally, there are database configuration settings that are used as part of query
execution plan selection. For example, the amount of memory available for data
caching is often used in a stochastic model to determine the likelihood of a data
page being cached at the time of the query execution (as opposed to requiring I/
O from disk). If the configuration changes result in performance reduction,
contrasting the query execution plans before and after the configuration change
can determine if a query execution plan change occurred that may be the root
cause of the performance change, and, if so, what specific changes have led to
the choice of a new query execution plan.
11.6
Exploring without Changing the Database
Many vendors allow users to perform what-if physical database design analysis without
actually making full-scale changes to their database. There are multiple ways this is
done.
First, users are able to extract a replica of the database design for creation in a new,
but data-free, database. The lack of data makes design exploration easy, since design
changes can be made without the actual expensive and time-consuming process of creat-
ing indexes or materialized views, converting tables to MDC, range or list partitioning,
or changing shared-nothing partitioning choices. All of these design changes are very
