VIEWS: 6 PAGES: 31 POSTED ON: 12/9/2011
Practical Examples to Optimizing Your Queries DB2 UDB for the iSeries Shantan Kethireddy firstname.lastname@example.org 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 query. 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. SHOW ME THE METHODOLOGY! 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 available 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 later 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 system Importing Performance SQL Performance Monitors - Import Monitors 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 systems ƒ 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 monitor 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 Explained Option is available only for detailed monitors List of Explainable Statements 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 statement Stages of the access plan are shown as icons Detailed information for each stage Flyover help available Several diagram customization options 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 30 40 111 Broadway 5 N 7th St Rochester Austin MN TX 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 in 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 query Statistics are collected automatically. They are created and maintained by database Cardinality, most common values New in V5R2 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: SELECT A, B, C, COUNT(*) FROM TABLEA GROUP BY A, B, C ORDER BY 4 DESC 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 used 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 used 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 indexes ƒ Allows for more efficient index access, and potentially faster queries ƒ 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 Collection 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 sources ƒ 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 Conclusion Collect Monitor Data Run performance analyzing queries Explain offending statements Implement a strategy based upon the results Optimization Time? Open Time? Run Time? Roadmap 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 ?
Pages to are hidden for
"Methodology Practical Examples to Optimizing Your Queries DB2 UDB"Please download to view full document