Databases Reference
In-Depth Information
Figure 12.8
DTA session-based tuning.
•
Users have the ability to select
multiple databases
as part of a single session. The
users also have the ability to
restrict tuning to specific tables
or perform analysis
for a
projected data size
(e.g., find a recommendation if the number of rows in a
fact table in a data warehouse increases by a factor of 10) at the granularity of
individual tables.
Figure 12.9 shows a session where the user has selected unlimited tuning time.
The recommendation can have both indexes and indexed views (materialized views)
and all existing structures can be dropped while optimizing for performance. DTA
can (re)partition existing tables and indexes; the restriction is that the indexes and
underlying tables be aligned (i.e., partitioned identically for manageability reasons).
DTA returns the expected improvement as a percentage of the current workload
cost (as estimated by the query optimizer) as shown in Figure 12.10. The recommenda-
tion consists of indexes, indexed views, and statistics along with whether it was recom-
mended to be created or dropped. The respective table and index partitions are also pre-
sented to the user. The tool allows a user to highlight a specific recommended structure
and generate the script.
DTA generates a set of reports to supplement its recommendation. A tuning
summary that includes total tuning time taken by DTA, expected percentage
improvement, storage requirements of the recommendation, how many structures
