Methodology Practical Examples to Optimizing Your Queries DB2 UDB by wuzhenguang


									Practical Examples to
Optimizing Your
DB2 UDB for the iSeries

           Shantan Kethireddy
Why am I here?
Learn how to identify a problematic query within an application or
 remote connection.

Learn how to capture the diagnostic information associated with a

Learn how to analyze a query and its associated tables using the
 iSeries Navigator's Visual Explain and Database Navigator tools.

Learn how to apply a concrete methodology to creating the perfect
 index for the offending query.
A typical model for
 examining and
 tuning poor
 performing queries

    Gather     Query the     Visually    Create any
   Monitor     data with   explain the    relevant
 Performance    iSeries     offending    indices or
     Data      Navigator     queries        stats
What are the DB
Performance Monitors?
Integrated tools used to gather database performance related
 statistics for SQL-based requests running on the iSeries
Applicable to both batch jobs and interactive work
Monitor data dumped into collected into table(s) where it can
 be queried to help identify and tune performance problem areas
Collected data can be analyzed to look at SQL performance at a
 global system level, job-level or, specific SQL statement level
Monitor data most useful given basic understand of DB2 UDB
 engine and query optimization techniques
What can the DB Monitor do
for me?
Collected information and stats can be analyzed later to analyze
 things such as:
How many queries rebuild access plans?
How many temporary indexes have been created over a
 particular table?
Which queries are the most time consuming?
Which user is running the longest running queries?
Which queries were implemented using reusable ODPs?
Has the implementation of a particular query changed with the
 application of a PTF or a new release? (Before/After
 comparison of the monitor data)
What can the DB Monitor do
for me cont.
Use to diagnose SQL statement performance problems
Both summary and detailed SQL Performance monitors
Interface to the memory resident performance monitor APIs
 (summary monitor)
Interface to the detailed performance monitor DBMON
 (detailed monitor)
Built-in queries make analyzing the data much simpler
SQL Performance Monitor output files can be analyzed on a
 different system than they were created on
Can import SQL Performance Monitor files from V4R4 and
Creating a New Detailed SQL
Performance Monitor

  Right-click on SQL Performance Monitors, and select New--> Detailed
  Enter a name for the monitor, the library to contain the results and the job(s) to monitor
  Only one monitor at a time can monitor all jobs on the system
Creating a New Detailed SQL
Performance Monitor cont.

Right-click on SQL Performance Monitors, and select
 New--> Detailed
Enter a name for the monitor, the library to contain the
 results and the job(s) to monitor
Only one monitor at a time can monitor all jobs on the
 Importing Performance
SQL Performance Monitors - Import
Importing Performance
Monitors cont.
To import, right click on the SQL Performance Monitors folder, and
 select Import
   ƒ Both detailed and summary SQL Performance Monitors can be imported.
     For detailed monitors, there is only one data file to enter. For summary
     monitors, you only need to enter one data file of all the files that were
     created (we'll figure out the rest)
   ƒ To see the data file(s), right-click on the monitor, select Properties, and
     click on the "Saved Data" tab
   ƒ Monitor files imported from pre-V4R5 systems will have new fields
     added, primed with zeros or blanks
Allows for moving SQL performance monitor output files between
   ƒ Just need to save and restore output files, and do the import
Analyzing SQL Performance
Monitor Results
 Analyzing SQL Performance
 Monitor Results cont.

Right-click on the SQL performance monitor and select
 "Analyze Results"
Select the type of information you want to see, then press
 "View Results"
To display or modify the selected query before executing, press
 "Modify Selected Queries"
Detailed SQL performance

 Entry into the Visual Explain function from List Explainable Statements
     ƒ Shows SQL statements that can be Visually Explained
     ƒ Option is available only for detailed monitors
Detailed SQL performance
monitor cont.

ƒ Entry into the Visual Explain function from
  List Explainable Statements
  Shows SQL statements that can be Visually
  Option is available only for detailed
 List of Explainable
List of Explainable Statements...

      All statements that are explainable will appear in the list
         ƒ Statements can be selected and shown in SQL statement selected area
         ƒ Statement can then be Visually Explained
List of Explainable
Statements cont.

All statements that are explainable will
 appear in the list
  ƒ Statements can be selected and shown in SQL
    statement selected area
  ƒ Statement can then be Visually Explained
   Visual Explain
The query access plan is
 diagrammed for the selected SQL
Stages of the access plan are shown
 as icons
Detailed information for each stage
Flyover help available
Several diagram customization
Highlight expensive icons and paths

Optimizer messages shown
   Visual Explain Advisors
Index Advisor
                                                      Table                                              Index

 Analyze and suggest                 Name             Age         Street          City        State
                                                                                                       Key Value   Code      First   Last   Count
                                                                                                                             Row     Row
  recommendations for                 John Smith

                                      Mike Johnson

                                                                   111 Broadway

                                                                   5 N 7th St


                                                                                                       MN              1         1      4      2
                                      Pete Peterson           35   1234 E Main     Dallas      TX       1
                                                                                                       NY     3    3   2 1      25      5      1

 performance improvements             Debra Jones             33   1133
                                                                   Washington Av
                                                                                   Austin      MN
                                                                                                       TX              3         2      3      2
                                                                                                                                                        Index Advisor
                                      Angie Smith             25   111 Broadway    Rochester   NY                                                   Analyzes statistics
                                                                                                   Additional                                       related to a query to
                                     Statistics                                                  statistics and                                     determine which
                                     Manager                                                   indexes help the                                     indexes should be
Statistics Manager               Background                                                     query optimizer                                     created
                                 automatic                                                      pick the optimal
                                 analysis of table                                             access methods
 Additional statistics to aid   and indexes to
                                 produce statistics
 query optimization                                           Statistics
                                                              Cardinality (unique key values)
   Estimated Value Ranges                                    By column:
   Estimated Count of                                         Estimated value ranges (histograms)
                                                               Estimated most common values
    Most Common Values
 Stats provide alternative to
  creating new indexes
  Visual Explain - Index           New

  Advisor                          V5R2

Advisor shows indexes that would
help specific query

Use advice along with Database
Navigator map to determine if
index would be helpful

Create prompts the Create Index
dialog with recommended columns
 Visual Explain -
 Stats Advisor

Advisor in Visual Explain shows
statistics that would help specific

Statistics are collected
automatically. They are created
and maintained by database

Cardinality, most common values
Art - The Perfect Index
A "perfect" index is a radix index that is permanent and can provide:
   ƒ Good, useful statistics to the optimizer
      Index contains appropriate selection, joining, grouping, ordering fields
      Applicable key fields are contiguous
      Equal predicate fields first, one non-equal predicate field last
   ƒ Multiple implementation methods
      Index ANDing / ORing with dynamic bitmaps
      Single key and multi-key row positioning
      Index scan
      Index only access
      Nested loop join (with multi-key row positioning)
      Index grouping
      Index ordering
   ƒ Multi-key index that provides very narrow range of values
      Think in terms of lower and upper bounds
Creating Indexes
Helpful to know how data in the table is populated and how selective certain key
 columns are
   ƒ Can help you create indexes that will be used for a large number of queries
   ƒ Can also help you know why existing indexes were not used in some situations
       For example, if query has WHERE A = ? AND B = ? AND C = ? and there is an index
         over A, B and C but the optimizer decides not to use it, it may be because these columns
         are not very selective - knowing the data can help you quickly detect this
   ƒ If you are not sure of how selective certain columns are, you can query them to find out:


Try to create indexes that are used more globally
   ƒ Use selective columns that are commonly used in WHEREs, and where applicable, use
     them in combination with common join, order by and group by keys
   ƒ Remember tips from query optimization section on creating these
Creating Indexes
Do not create lots of permanent indexes trying to cover every combination
   ƒ Create one or two, run the job again (or run STRSQL for single query) and see if they are
       If they are used and run time is noticeably improved, consider them for permanent use
       If they are not used, delete them and try a different combination
   ƒ Do not create indexes just to solve single instance of a full open or query unless it accounts
     for a significant amount of time
Each additional index created for a table will cause overhead when:
   ƒ Updates to the table include the index keys
   ƒ Rows are inserted or deleted for the table
   ƒ index evaluation during optimization
Use a naming convention when creating indexes for permanent use
   ƒ During performance debug & test, SQL CREATE INDEX or Operations Navigator can be
New indexes are created as 4 byte or maxsize = *1TB           (vs. 3 byte / *4GB)

   ƒ Consider recreating SQL indexes and keyed LFs that are migrated from earlier releases
Creating Indexes

New 64K logical page size for 4-byte / *MAX1TB radix
  ƒ Allows for more efficient index access, and potentially faster
  ƒ Larger logical page size automatically used for...
     SQL created indexes on SQL created tables
     SQL created indexes on CRTPF/DDS created tables
     SQL created constraints on SQL created tables
Why are stats important?
"The cornerstone of SQL optimization is accurately
 determining the number of rows which will qualify
 for an SQL statement. If the correct statistics are
 collected, the optimizer will more accurately
 estimate the number of rows each table will join.
 Collecting these statistics should result in better join
 order, join method, more appropriate sort decisions,
 as well as improved table access." Patrick Bossman,
 IDUG August 1999
Creating Statistics
All query optimizer's rely upon statistics to make
 plan decisions
  ƒ DB2 UDB for the iSeries has always relied upon
    indexes as its source for stats
  ƒ Other databases rely upon manual stats collection for
    their source
SQE offers a hybrid approach where stats will be
 automatically collected for cases where indexes do
 not already exist
  ƒ Stats do not have maintenance overhead
Types of Stats Questions
Selectivity: How many records will be selected by a given
 selection predicate or combination of predicates
Cardinality: How many distinct occurrences of a value exist for
 a single column or multiple columns in a query
Meta-data: How many records exist within a table? What
 indexes exist over a given table and what keys are interesting
I/O Estimation: How many I/O's will be required to process this
 table or index
Automatic Statistics
Allows Stats Manager to self-manage the collection of stats needed to
 answer the questions more accurately
   ƒ Automatic collection is performed by default
   ƒ Statistics On-Demand!
As queries are run, the stats manager will request a background
 collection on columns where the answers are derived from default
   ƒ The goal is the stat will be available for future executions of the query
When an index is not available to answer a stats question, the query may
 run significantly longer on the first execution
This warm-up effect can be prevented by proactively creating indexes
 and manual stats collections

Collect Monitor Data
Run performance analyzing queries
Explain offending statements
Implement a strategy based upon the results

    Optimization Time?        Open Time?       Run Time?

440101 - Using DB2 Monitors to Analyze and
Improve SQL & Query Performance
410036 - OPEN LAB: DB2 UDB, Get Your "Hands-
On" the iSeries Relational Database
407111 - The Art of Creating the Perfect Index
Final Questions

To top