DB2性能优化.ppt

1,DB2性能优化,2,The DB2 Optimizer,Determines database navigationParses SQL statements for tables and columns which must be accessedQueries statistics from DB2 Catalog(populated by RUNSTATS utility)Determines least expensive access path Since it is a Cost-Based Optimizer-it chooses the lease expensive access path,3,The DB2 Optimizer,SQL,Optimized Access Path,DB2 OptimizerCost-Based,Query Cost Formulas,DB2Catalog,4,Optimizer Access Path Selection,1.Gets the current statistics from DB2 catalog for the columns and tables identified in the SQL statements.These statistics are populated by the Runstats utility.2.Computes the estimated percentage of qualified rows for each predicate-which becomes the filter factor for the predicate.,5,Optimizer Access Path Selection,3.Chooses a set of reasonable access paths.4.Computes each potential access paths estimated cost based on:CPU CostI/O Cost,6,Access Path Cost Based On:,CPU CostApplying predicates(Stage 1 or Stage 2)Traversing pages(index and tablespace)SortingI/O CostDB2 Catalog statisticsSize of the bufferpoolsCost of work files used(sorts,intermediate results,and so on),7,Will a Scan or an Index Be Used?,A tablespace Scan sequentially reads all of the tablespace pages for the table being accessed.Most of the time,the fastest way to access DB2 data is with an Index.For DB2 to consider using an index-the following criteria must be met:At least one of the predicates for the SQL statement must be indexable.One of the columns(in any indexable predicate)must exist as a column in an available index.,8,Will a Scan or an Index Be Used?,An index will not be used in these circumstances:When no indexes exist for the table and columns being accessedWhen the optimizer determines that the query can be executed more efficiently without using an index-the table has a small number of rows or using the existing indexes might require additional I/O-based on the cardinality of the index and the cluster ratio of the index.,9,Types of Indexed Access,Direct Index Lookupvalues must be provided for each column in the indexMatching Index Scan(absolute positioning)can be used if the high order column(first column)of an index key is provided Nonmatching Index Scan(relative positioning)can be used if the first column of the index is not provided can be used for non-clustered indexescan be used to maintain data in a particular order to satisfy the ORDER BY or GROUP BYIndex Only Access can be used with if a value is supplied for all index columns-avoids reading data pages completely,10,Sequential Prefetch,A read-ahead mechanism invoked to prefill DB2s buffers so that data is already in memory before it is requested.Can be requested by DB2 under any of these circumstances:A tablespace scan of more than one pageAn index scan in which the data is clustered and DB2 determines that eight or more pages must be accessed.An index-only scan in which DB2 estimates that eight or more leaf pages must be accessed.,11,Database Services Address Space,The DBAS,or Database Services Address Space,provides the facility for the manipulation of DB2 data structures.The DBAS consists of three components:Relational Data System(RDS)Set-Level Orientation Stage 2 predicatesSQL statement checkingSortingOptimizer,12,Database Services Address Space,Data Manager(DM)Row-Level OrientationStage 1 predicatesIndexable predicatesLockingVarious data manipulationsBuffer Manager(BM)Physical Data AccessData movement to and from DASD,Bufferpools,13,Database Services Address Space,RelationalData Manager,DataManager,BufferManager,SQL,OptimizedSQL,Read BufferorRequest data,Results,Apply stage 2predicatesand sort data,Apply stage 1predicates,Data,14,Database Services Address Space,When an SQL statement requesting a set of columns and rows is passed to the RDS,the RDS determines the best mechanism for satisfying the request.The RDS can parse an SQL statement and determine its needs.When the RDS receives an SQL statement,it performs these steps:1.Checks authorization2.Resolves data element names into internal identifiers3.Checks the syntax of the SQL statement4.Optimizes the SQL statement and generates an access path,15,Database Services Address Space,The RDS then passes the optimized SQL statement to the Data Manager for further processing.The function of the DM is to lower the level of data that is being operated on.The DM analyzes the request for table rows or index rows of data and then calls the Buffer Manager to satisfy the request.The Buffer Manager accesses data for other DB2 components.It uses pools of memory set aside for the storage of frequently accessed data to create an efficient data access environment.,16,Database Services Address Space,The BM determines if the data is in the bufferpool already.If so-the BM accesses the data and send it to the DM.If not-it calls the VSAM Media Manager to read and send back the data to the BM,so it can be sent to the DM.The DM receives the data and applies as many predicates as possible to reduce the answer set.Only Stage 1 predicates are applied in the DM.,17,Database Services Address Space,Finally,the RDS receives the data from the DM.All Stage 2 predicates are applied,the necessary sorting is performed,and the results are returned to the requestor.Considering these steps,realize that Stage 1 predicates are more efficient because they are evaluated earlier in the process,by the DM instead of the RDS,and thereby reduce overhead during the processing steps.,18,SQL Coding Strategies and Guidelines,Understand Stage 1 and Stage 2 PredicatesTune the queries that are executed more frequently first!It Depends!Know Your Data!Static vs.Dynamic SQLBatch vs.Interactive(CICS vs.web),19,Unnecessary SQL,Avoid unnecessary execution of SQLConsider accomplishing as much as possible with a single call,rather than multiple calls,20,Rows Returned,Minimize the number of rows searched and/or returned Code predicates to limit the result to only the rows neededAvoid generic queries that do not have a WHERE clause,21,Column Selection,Minimize the number of columns retrieved and/or updated Specify only the columns neededAvoid SELECT*Extra columns increases row size of the result set Retrieving very few columns can encourage index-only access,22,Singleton SELECT vs.Cursor,If a single row is returnedSingleton SELECT.INTOoutperforms a Cursorerror when more than 1 row is returnedIf multiple rows are returnedCursorrequires overhead of OPEN,FETCH,and CLOSEWhat is an example of a singleton select and a select requiring a cursor?,23,Singleton SELECT vs.Cursor,For Row Update:When the selected row must be retrieved first:Use FOR UPDATE OF clause with a CURSORUsing a Singleton SELECTthe row can be updated by another program after the singleton SELECT but before the subsequent UPDATE,causing a possible data integrity issue,24,Use For Fetch Only,When a SELECT statement is used only for data retrieval-use FOR FETCH ONLYFOR READ ONLY clause provides the same function-and is ODBC compliantEnables DB2 to use block fetchMonitor the performance to decide which is best for each situation,25,Avoid Sorting,DISTINCT-always results in a sortUNION-always results in a sortUNION ALL-does not sort,but retains any duplicates,26,Avoid Sorting,ORDER BY may be faster if columns are indexeduse it to guarantee the sequence of the dataGROUP BYspecify only columns that need to be groupedmay be faster if the columns are indexeddo not include extra columns in SELECT list or GROUP BY because DB2 must sort the rows,27,Subselects,DB2 processes the subselect(inner select)first before the outer selectYou may be able to improve performance of complex queries by coding a complex predicate in a subselectApplying the predicate in the subselect may reduce the number of rows returned,28,Use Inline Views,Inline views allow the FROM clause of a SELECT statement to contain another SELECT statementMay enhance performance of the outer select by applying predicates in the inner selectUseful when detail and aggregated data must be returned in a single query,29,Indexes,Create indexes for columns you frequently:ORDER BYGROUP BY(better than a DISTINCT)SELECT DISTINCTJOINSeveral factors determine whether the index will be used,30,Avoid Data Conversion,When comparing column values to host variables-use the same Data TypeLengthWhen DB2 must convert data,available indexes are sometimes not used,31,Join Predicates,Response time-determined mostly by the number of rows participating in the joinProvide accurate join predicatesNever use a JOIN without a predicateJoin ON indexed columnsUse Joins over subqueries,32,Join Predicates(cont.),When the results of a join must be sorted-limiting the ORDER BY to columns of a single table can avoid a sortspecifying columns from multiple tables causes a sortFavor coding explicit INNER and LEFT OUT joins over RIGHT OUTER joinsEXPLAIN converts RIGHT to LEFT join,33,Example:Outer Join With A Local Predicate,SELECT emp.empno,emp.lastname,dept.deptnameFROM emp LEFT OUTER JOIN deptON emp.workdept=dept.deptnoWHERE emp.salary 50000.00;Works correctly but the outer join is performed first,before any rows are filtered out.,34,Example:Outer Join Using An Inline View,SELECT emp.empno,emp.lastname,dept.deptnameFROM(SELECT empno,lastname FROM emp WHERE salary 50000.00)as eLEFT OUTER JOIN deptON emp.workdept=dept.deptnoWorks better applies the inner join predicates first,reducing number of rows to be joined,35,OR vs.UNION,OR requires Stage 2 processingConsider rewriting the query as the union of 2 SELECTs,making index access possibleUNION ALL avoids the sort,but duplicates are includedMonitor and EXPLAIN the query to decide which is best,36,Use BETWEEN,BETWEEN is usually more efficient than=predicateExcept when comparing a host variable to 2 columnsStage 2:WHERE:hostvar BETWEEN col1 and col2Stage 1:WHERE Col1=:hostvar,37,Use IN Instead of Like,If you know that only a certain number of values exist and can be put in a listUse IN or BETWEENIN(Value1,Value2,Value3)BETWEEN:valuelow AND:valuehighRather than:LIKE Value_,38,Use LIKE With Care,Avoid the%or the _ at the beginning because it prevents DB2 from using a matching index and may cause a scanUse the%or the _ at the end to encourage index usage,39,Avoid NOT,Predicates formed using NOT are Stage 1But they are not indexableFor Subquery-when using negation logic:Use NOT Exists DB2 tests non-existenceInstead of NOT INDB2 must materialize the complete result set,40,Use EXISTS,Use EXISTS to test for a condition and get a True or False returned by DB2 and not return any rows to the query:SELECT col1 FROM table1WHERE EXISTS(SELECT 1 FROM table2 WHERE table2.col2=table1.col1),41,Code the Most Restrictive Predicate First,After the indexes,place the predicate that will eliminate the greatest number of rows firstKnow your dataRace,Gender,Type of Student,Year,Term,42,Avoid Arithmetic in Predicates,An index is not used for a column when the column is in an arithmetic expression.Stage 1 but not indexableSELECT col1FROM table1WHERE col2=:hostvariable+10,43,Limit Scalar Function Usage,Scalar functions are not indexableBut you can use scalar functions to offload work from the application programExamples:DATE functionsSUBSTRCHARetc.,44,Other Cautions,Predicates that contain concatenated columns are not indexableSELECT Count(*)can be expensiveCASE Statement-powerful but can be expensive,45,With OPTIMIZE for n ROWS,For online applications,use With OPTIMIZE for n ROWS to attempt to influence the access path DB2 choosesWithout this clause,DB2 chooses the best access path for batch processing With this clause,DB2 optimizes for quicker response for online processingTry Optimize for 1,for 10,for 100,46,Review DB2 Optimizer,DB2 is a Cost-based optimizerRUNSTATS populates the DB2 CatalogDB2 Catalog used to determine access pathCreate Indexes for columns you frequently select and sort Avoid Unnecessary Sorts in SQLCode the SQL predicates thoughtfully,47,DB2 Catalog,SYSTABLESSYSTABLESPACESYSINDEXESFIRSTKEYCARDF,SYSCOLUMNSHIGH2KEYLOW2KEYSYSCOLDISTSYSCOLDISTSTATS,48,Filter Factors for Predicates,Filter factor is based on the number of rows that will be filtered out by the predicateA ratio that estimates I/O costs The lower the filter factor,the lower the cost,and in general,the more efficient the queryReview the handout as we discuss this topic,49,Filter Factors for DB2 Predicates,Filter Factor Formulas-use FIRSTKEYCARDF column from the SYSINDEXES table of the CatalogIf there are no statistics for the indexes,the default filter factors are usedThe lowest default filter factor is.01:Column BETWEEN Value1 AND Value2Column LIKE char%Equality predicates have a default filter factor of.04:Column=valueColumn=:hostvalueColumnA=ColumnB(of different tables)Column IS NULL,50,Filter Factors for DB2 Predicates,Comparative Operators have a default filter factor of.33Column,=value IN List predicates have a filter factor of.04*(list size)Column IN(list of values)Not Equal predicates have a default filter factor of.96:Column valueColumn:hostvalueColumnA ColumnB(of different tables),51,Filter Factors for DB2 Predicates,Not List predicates have a filter factor of 1-(.04*(list size)Column NOT IN(list of values)Other Not Predicates that have a default filter factor of.90Column NOT BETWEEN Value1 and Value2Column NOT IN(non-correlated subquery)Column ALL(non-correlated subquery),52,Column Matching,With a composite index,the column matching stops at one predicate past the last equality predicate.See Example in the handout that uses a 4 column index.(C1=:hostvar1 AND C2=:hostvar2 AND C3=(non column expression)AND C4:hostvar4)Stage 1-Indexable with 4 matching columns(C1=:hostvar1 AND C2 BETWEEN:hostvar2 AND:hostvar3 AND C3=:hostvar4)Stage 1-Indexable with 2 matching columns,53,Column Matching,(C1 value1 AND C2=:hostvar2 AND C2 IN(value1,value2,value3,value4)Stage 1-Indexable with 1 matching column(C1=:hostvar1 AND C2 LIKE ab%xyz_1 AND C3 NOT BETWEEN:hostvar3 AND:hostvar4 AND C4=value1)Indexable with C1=:hostvar1 AND C2 LIKE ab%xyz_1 Stage 1-LIKE ab%xyz_1 AND C3 NOT BETWEEN:hostvar3 AND:hostvar4 AND C4=value1,54,Column Matching-2 Indexes,With two indexes:C1.C2 and C3.C4(C1=:hostvar1 AND C2 LIKE:hostvar2)OR(C3=(non column expression)AND C4:hostvar4)Multiple Index Access1 column matching of first index2 columns matching on second indexLIKE will be Stage 2,55,Order of Predicate Evaluation,1.Indexed predicates2.Non-indexed predicates-Stage 1 then Stage 2 Within each of the groups above,predicates are evaluated in this sequence:1.Equality predicates,including single element IN list predicates2.Range and NOT NULL predicates3.All other predicatesIf multiple predicates are of the exact same type,they are evaluated in the order in which they are coded in the predicate.,56,Review Filter Factors for Predicates,DB2 CatalogFilter FactorsColumn MatchingOrder of Predicate Evaluation,57,Runstats and Reorg,Runstats Utilityupdates the catalog tables with information about the tables in your systemu