Analyzing index impact

Use indexing recommendations in your tuning jobs to analyze the impact of indexing on several data sources, such as database packages, dynamic statement cache, and workloads. This allows you to make the most informed decision before proceeding with index creation.

Analyzing the impact of indexing on packages

  1. Create a tuning job for a catalog plan or package:
    1. Click Capture statements that match criteria for packages.
    2. Filter the capturing of SQL statements from the catalog package by NAME, and click Next.
    3. Click Tune All Statements to tune all queries as a workload.
    4. Enter the Job name and SQLID.
    5. Ensure the Advanced options for Statistics and Indexes recommendations are checked, and click Done.
  2. When the tuning job returns a status of Succeeded, click View Results.

    In the Index Script page, you will see the recommendations determined by the QWTz Index Analyzer for the packages in the selected database.

  3. Click Impact Analysis to see how these recommendations would affect other queries in your database.
  4. Select Packages as the source of impact analysis and click OK.
  5. When the status of the impact analysis changes to Succeeded, click View Results.

    All of the influenced packages are displayed in the result table, identified by name, token, and version. The Impact Level tells you the extent of influence on the selected package, which could change the access plan or invalidate it. The entry in the Reason column provides a more detailed explanation of the reasoning behind the recommendation.

    You can now make the most informed decision about which recommendations to implement when tuning your indexes.

Analyzing the impact of indexing on a dynamic statement cache

  1. Create a tuning job on a workload, ensuring the Advanced options for Statistics and Indexes recommendations are selected.
  2. Run the job and click View Results.

    In the Index Script page, you will see the recommendations determined by the QWTz Index Analyzer for the packages in the selected database.

  3. Click Impact Analysis to see how these recommendations would affect other queries in your database.
  4. Select Dynamic Statement Cache as the source of impact analysis and click OK.
  5. When the status of the impact analysis changes to Succeeded, click View Results.

    All the original recommended indexes are listed in the result table, along with the tables they created, their key columns, and the estimated performance gained by them. The number of statements impacted by each index are also listed, showing the influence on queries in the statement cache.

  6. Click View Statements again to see detailed statement information, listing every query impacted by the specified index.

Analyzing the impact of indexing on a workload

  1. Create a tuning job as you did in Analyzing the impact of indexing on a dynamic statement cache, using queries from a local file and tuning them as a workload.
  2. Click Impact Analysis to see how these recommendations would affect other queries in your database.
  3. Choose Analyze statements in existing jobs, select one workload job from the table as the source of impact analysis, and click OK.
  4. When the status of the impact analysis changes to Succeeded, click View Results.
  5. Click View Statements to see detailed statement information, listing every query impacted by the specified index.

    As the statements are captured from a workload, we can see their source key as FILEDIR, as opposed to the CACHE value displayed when analyzing the dynamic statement cache.