Comentarios sobre Curso de DB2 UDB for Application Performance and Tuning CF961MX - Presencial - Álvaro Obregón - CDMX - Ciudad de México
-
Objetivos del curso
Design better indexes Determine how to live with the optimizer (avoid pitfalls, help when necessary) Avoid locking problems Use accounting traces to find significant performance problems in an operational application
-
Prácticas
This a Hands-On Capacitation
-
Curso dirigido a
This is an advanced course for senior application programmers, senior application analysts, senior application developers, and database administrators.
-
Titulación
Certicicación Técnica Profesional de IBM de DB2 UDB for Application Performance and Tuning
-
Contenido
KEY TOPICS
Application performance issues and management methods
- Describe the most common DB2 application performance problems
- Evaluate different approaches for detecting the problems
- Describe different solutions
Towards better indexes
- Detect inadequate indexing with Very Quick Upper Bound Estimate (VQUBE) as soon as program specifications are completed
- Detect inadequate indexing with accounting trace
- Design the best possible index for a single-table SELECT
- Evaluate the cost of an index
DB2 index structure and basic access paths
- Perform basic access path classification
- Differentiate between a matching index scan with a non-clustering index, a matching index scan with a clustering index, and a non-matching index scan
- Identify how to recognize index only access and describe its benefits
- Differentiate between index matching and index screening
- Describe how to predict matching columns
- Evaluate the cost of a query based on random and sequential touches
- Use the VQUBE analysis to detect slow access paths early
Index design - part one
- List performance components that contribute to the response time perceived by the application user
- Determine acceptable worst input and average response times for applications
- Identify potential solutions when applications are not achieving the response time requirements specified
- Given a database implementation and application requirement, determine whether the current database design is efficient enough for the applications
- Identify how DB2 for z/OS Version 8 indexes can be exploited to improve performance
- Calculate VQUBE for indexes
- Consider the impact of leaf page splits on access via an index
- Describe techniques that can be employed to minimize the requirement for DB2 to perform an index page split
- Identify index considerations with respect to foreign key definitions
- Describe the performance impact of the use of OPTIMIZE FOR n ROWS and FETCH FIRST k ROWS ONLY with regard to sorts and index access
Index Design - part two
- Describe the steps to take to make improvements to the database design, given that inadequate indexes exist in the database
- Identify the top three characteristics to achieve with your index definition
- Choose the best possible index for your application situation
- Consider the costs implied by implementing indexes in your database design
- Detect inadequate indexing with accounting traces
Advanced access paths
- Describe the basic principles of the three kinds of prefetch
- Identify the implications of the three kinds of prefetch on index design
- Given a query that actually fetches a small number of rows from a large result set, identify two potential solutions to communicate this fact to the optimizer so that it can make a more informed decision
- Identify benefits and pitfalls that may occur with multiple index access
Towards better tables
- Evaluate clustering alternatives
- Relate the trade-offs in two kinds of denormalization
- Describe why tables for optional attributes are often not good for performance
Learning to live with optimizer
- Describe the limitations related to dangerous predicates
- Identify situations when the optimizer needs help with filter factor estimates
- Avoid the pitfalls with joins, subqueries, and unions
Dangerous predicates
- Recognize predicates that can cause the optimizer to miscalculate filter factors
- Determine predicates that can cause problems with the access path selected
- Identify common non-indexable predicates
- Differentiate between stage 1 and stage 2 predicates
Optimizer and filter factors
- Define filter factor
- Identify sources of information for the optimizer's calculation of filter factor
- Consider the implication of default filter factors
- Describe the impact of correlated columns used in the WHERE clause
- Use techniques to overcome filter factor miscalculations
Join issues
- Differentiate between the join methods and join types available to DB2
- Identify how to select optimal indexes for joins and subqueries
Subquery issues
- Differentiate between correlated and non-correlated subqueries
- Describe implications of non-correlated subqueries that return a single value versus those that return multiple values
Union issues
- Avoid three significant performance pitfalls related to UNION operations
Unpredictable transactions
- Design good cursors and indexes for a transaction with optional input fields
- Relate the problems the index designer and the optimizer face with star joins
Massive batch
- Detect the eventual performance problems with massive batch jobs
- Make batch jobs run faster
- Recommend design changes to reduce random disk Input/Output (I/O) and improve batch performance
- Identify design changes required to implement parallelism in a massive batch application
Massive delete
- Consider the implications of batch applications that must delete massive numbers of rows
Worried about Central Processing Unit (CPU) time?
- Predict CPU time with a rough formula
Avoiding locking problems
- Avoid locks that are too long and too strong
- Prevent wrong results caused by locks that are too short or too weak
Monitoring application performance
- Identify how traces work
- Define what an accounting trace is
- List the most important counters in an accounting trace
- Compare VQUBE and accounting traces
- Analyze an accounting trace
- Describe the most useful accounting reports
*Precio no incluye IVA
