Recommending Materialized Views and Indexes with the IBM DB2 Design Advisor
Download
1 / 15

Jarek Gryz - PowerPoint PPT Presentation


  • 119 Views
  • Uploaded on

Recommending Materialized Views and Indexes with the IBM DB2 Design Advisor (Automating Physical Database Design). Jarek Gryz. Agenda. Motivation Indexes in DB2 Materialized query tables in DB2 Problem definition How does the DB2 Design Advisor tool work ? Experiments. Motivation.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Jarek Gryz ' - gypsy


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Recommending Materialized Views and Indexes with the IBM DB2 Design Advisor(Automating Physical Database Design)

Jarek Gryz


Agenda
Agenda Design Advisor

  • Motivation

  • Indexes in DB2

  • Materialized query tables in DB2

  • Problem definition

  • How does the DB2 Design Advisor tool work ?

  • Experiments


Motivation
Motivation Design Advisor


Why have an index
Why have an index? Design Advisor

  • Performance, Performance, Performance

  • Provides order

    • for example : Joins, GROUP BY, ORDER BY, DISTINCT

  • Limits I/O and data retrieved due to filtering with predicates

    • Range of values (start/stop keys)

    • Join predicates

  • Provides index-only access

  • Enforces uniqueness or other constraints

  • Provides statistics useful to the optimizer for cardinality estimation

    • for example: statistics on number of keys


Why have materialized query tables
Why have Materialized query tables ? Design Advisor

  • The MQT feature is a powerful feature in DB2 that allows you to precompute and materialize a query result into a table

  • Full refresh or incremental refresh possible

  • Subsequently it allows similar queries to automatically use the precomputed data from the MQT to improve performance


Problem definition
Problem Definition Design Advisor

  • Given:

    • Workload information

    • System configuration

    • Database characteristics

  • Determine:

    • An Index and MQT set that will

      • lead to good workload performance

      • in a reasonable or specified maximum time

      • considering disk space and maintenance constraints

      • and be easy to use


The db2 design advisor
The DB2 Design Advisor Design Advisor

  • Automatically capture :

    • A representative query workload (potentially compressing it to reduce its size)

    • The existing database characteristics and environment

    • System information

  • Determines:

    • An Index and MQT set that will lead to good ESTIMATED workload response time

      • Using DB2's Query Rewriter/Optimizer to suggest candidates

      • Using DB2's Optimizer to provide cost / benefit information

      • Using a combinatorial algorithm to perform a cost-benefit analysis observing constraints of (1) advisor execution time, (2) disk space and (3) anticipated DB2 costs of creating the entities plus overhead during INSERT / UPDATE / DELETE activity.

      • Using sampled or estimated statistics of new entities

      • Providing both GUI and command line options for initiating


Issues in automating physical db design selection
Issues in automating physical DB design selection Design Advisor

  • When to initiate the design algorithm?

    • Health monitor with health indicators such as number of sort overflows to initiate the advisor

  • What data to use to make the decision?

    • Automatically capture workload, DB, and system information

    • Allow work on real data or just statistics

  • How to make the decision?

    • Method to be described

  • How are the recommendations implemented?

    • Little user interaction to ask if or when to initiate to gain DBA trust

    • Online methods to reduce implementation cost

      • E.g., online index creation



Index candidate generation
Index candidate generation Design Advisor

  • During optimization generate virtual candidates when:

    • Predicate exists but no index (e.g., R.A > 5 or R.A=S.B)

    • Ordering required

    • Uniqueness required

  • Winning candidates are the virtuals in the final optimized query plan

  • Provides candidates we know the optimizer will use


Mqt candidate generation
MQT Candidate Generation Design Advisor

  • Candidates are generated from original queries, logical views and common expressions which are formed by matching multiple queries.

  • Uses multiquery optimization (MQO)

    • Provides candidates we know the queries will use

  • Candidates can contain table references in a federated DB (tables on different servers)


Combinatorial search algorithm
Combinatorial search algorithm Design Advisor

  • The search phase uses a knapsack algorithm and random swap method to choose the recommended index and MQT set

  • Requires each candidate to have a cost-benefit ratio (cbratio)

    • Benefit based on estimated cost with and without MQT usage (updates have negative benefit)

    • Cost based on disk space usage

  • REFRESH DEFERRED or IMMEDIATE MQTs recommended.

    • Assumption (DEFERRED):

      • estimated time for population = full refresh cost

      • one refresh cost included in the calculation

    • IMMEDIATE changes added in plans for insert/update/deletes

  • If indexes on candidate MQT are selected, then the MQT must be selected as well


Experiments
Experiments Design Advisor

  • Detect what MQO candidates adds to performance improvement

  • OLAP DB and workload

  • Workload estimated execution time (WET)


Autonomic capabilities in db2 stinger
Autonomic capabilities in DB2 Stinger Design Advisor

  • Health Monitor

    • Recommendation Advisor

  • Automatic page write integrity checking

  • Automatic index reorganization

  • Recovery Expert

  • Fault Monitor

  • Backup

    • Self-tuning

    • Automated

  • HADR

  • DB2/Websphere Integration

    • log and trace analyzer

  • Configuration Advisor

  • Design Advisor

  • advises: Indexes, MDCs, MQTs, Partitioning

Self-configuring

Self-healing

  • Query compiler

    • query rewrite

    • cost based optimization

  • Automatic query parallelism degree

  • Self-configuring/optimizing utilities

  • Adaptive utility throttling

    • Runstats

  • Performance Expert

  • Query patroller workload manager

  • Self-tuning load

Self-optimizing

  • Automated Table Maintenance

    • Runstats

    • Reorg

    • Statistics profiling