Anthony Reina - Accelerated Value Specialist
[email protected]
Performance Enhancements for DB2 V10.1
Information Management
© 2010 IBM Corporation
Information Management
IBM Software Accelerated Value Program •The IBM Software Accelerated Value Program delivers a proactive, cost-reducing, and productivity enhancing advisory service. The program pairs you with an assigned team who build a foundational understanding of your overall environment. Through that understanding, the trusted partner works to facilitate faster deployment, lifecycle leadership, risk mitigation, and more by identifying ways to improve your environment, staff skill set, and processes. http://www-01.ibm.com/software/support/acceleratedvalue/
2
© 2010 IBM Corporation
Information Management
Agenda : • RUNSTATS and database statistics improvements • Registry variables and inexact matching for Optimization profile
• Statistical view
3
© 2010 IBM Corporation
Information Management
RUNSTATS INDEXSAMPLE : Allows statistics collectio be done in sample mode. –SYSTEM –BERNOULLI Detailed Index Statistics default (DETAILED) now uses sampling method starting in V10.1. – SAMPLED DETAILED is still available for compatibility. – UNSAMPLE option still available VIEW command parameter can be used for better usability. No need to fully qualify tables by schema name AUTO_SAMPLING now possible for all auto_stats collection on large table and statiscial views
4
© 2010 IBM Corporation
Information Management
RUNSTATS Implementation :
RUNSTATS Syntax
Database Config
5
© 2010 IBM Corporation
Information Management
OPTIMIZATION PROFILE Selected OPT registry variables can now be set via optimization profile DB2_ANTIJOIN DB2_EXTENDED_OPTIMIZATION DB2_INLIST_TO_NLJN DB2_MINIMIZE_LISTPREFETCH DB2_NEW_CORR_SQ_FF DB2_OPT_MAX_TEMP_SIZE DB2_REDUCED_OPTIMIZATION DB2_RESOLVE_CALL_CONFLICT DB2_SELECTIVITY DB2_SELUDI_COM_BUFFER DB2_SORT_AFTER_TQ Registry variables can be set at the global and/or statement level. If set at statement level…applied to statement defined for. If set at global level…applied to all statements in the opt profile. If set at both level…statement level takes precedence. If not set…db2set setting is used. 6
© 2010 IBM Corporation
Information Management
OPTIMIZATION PROFILE Implementation : Will affect STMTPROFIL E ID=‘S2’
Will affect STMTPROFIL E ID=‘S1’
7
© 2010 IBM Corporation
Information Management
OPTIMIZATION PROFILE Implementation : Explain facility will reflect which registry variables are being used for a particular statement. It also indicates where the registry variables have been set ie. db2set level, Global Optimization Guideline Statement-level Optimization Guideline
8
© 2010 IBM Corporation
Information Management
OPTIMIZATION PROFILE Inexact matching used for flexible matching between compiling statements and statement within optimization profile.
Set at the STMTMATCH element of the OPTPROFILE via EXACT attribute. EXACT Attribute can be set TRUE or FALSE If set TRUE then exact matching is enabled. If set FALSE then inexact matching is enabled. INEXACT matching ignores constants, literals, parameter markers, and host variables. Exceptions/Future Considerations : – Order of column
9
© 2010 IBM Corporation
Information Management
OPTIMIZATION PROFILE Exceptions/Future Considerations : 1. Order of column lists, table lists and predicate list must be the same SELECT A,B FROM R,T WHERE A>B Does not match : SELECT B,A FROM T,R WHERE B
