Articles & Content

Archives

I can explain that! (D12)

Topic: DB2 for LUW

Subtopic: 2010



DATE: 2010-11-10 (14:15 - 15:15)
SPEAKERS: Leo Pedron (Fourth Millennium Technologies)

SQL data access plans are key to understanding database performance. This presentation will break down what the numbers mean, what all the SQL operators mean, and how to go about using this information to make improvements to the SQL. An in depth discussion will describe various join techniques and other SQL operators and their impact on overall SQL cost, the meaning of various numeric values in the explain plan, and how to take action to correct poor performing SQL. A description of data cardinality estimates, IO cost estimates, physical index design, sort operations, join operations, and impacts of different SQL predicates will be presented. The take away from this session is an in depth knowledge of what all the numbers presented by the explain tool mean, and how to take action to correct rouge SQL. Examples of real customer experiences demonstrating improved performance will be presented with the presentation.

EXP. LEVEL: Beginner,Intermediate

OBJECTIVES:

Read and understand the information in the access plan graph.

Describe different join operations for hash, merge scan, and nested loop joins, and how a DPF environment impacts table joins using collocated, directed, and broadcast join operations.

Explain the meaning and impact of information in the predicates block and how this data impacts the plan in regards to join order and index selection. Also provide information on how runstats can impact these values

How to spot trouble spots in an access plan and how to tune out of them. Avoiding list prefetch and sort operations, influencing the optimizer away from temp table joins.

Stressing the impact of explain on insert, update, and delete statements. How RI, triggers, and other constraints can impact the performance of these operations.



Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.