DB2 Developers Guide (5th Edition)

 <  Day Day Up  >  

Chapter 25. Using EXPLAIN

IN THIS CHAPTER

  • How EXPLAIN Works

  • Access Paths and the PLAN_TABLE

  • Cost Estimates and the DSN_STATEMNT_TABLE

  • Function Resolution and the DSN_FUNCTION_TABLE

  • EXPLAIN Guidelines

You can use the EXPLAIN feature to detail the access paths chosen by the DB2 optimizer for SQL statements. EXPLAIN should be a key component of your performance monitoring strategy. The information provided by EXPLAIN is invaluable for determining the following:

  • The work DB2 does "behind the scenes" to satisfy a single SQL statement

  • Whether DB2 uses available indexes and, if indexes are used, how DB2 uses them

  • The order in which DB2 tables are accessed to satisfy join criteria

  • Whether a sort is required for the SQL statement

  • Intentional table space locking requirements for a statement

  • Whether DB2 uses query parallelism to satisfy an SQL statement

  • The performance of an SQL statement based on the access paths chosen

  • The estimated cost of executing an SQL statement

  • The manner in which user -defined functions are resolved in SQL statements

 <  Day Day Up  >  
Related

Категории