1 / 129

The Do’s and Don’ts of CBO

by Jim Gillespie ORAMAIN Consulting Services, LLC emailid: jimgillespie@oramain.com website: www.oramain.com Phone: 608-848-8642 Cell : 608-217-4351. The Do’s and Don’ts of CBO. The Do’s and Don’ts of CBO. Agenda What is the Optimizer? Why Optimize? Available Optimizers

jeff
Download Presentation

The Do’s and Don’ts of CBO

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. by Jim Gillespie ORAMAIN Consulting Services, LLC emailid: jimgillespie@oramain.com website: www.oramain.com Phone: 608-848-8642 Cell : 608-217-4351 The Do’s and Don’ts of CBO ORAMAIN Consulting Services, LLC

  2. The Do’s and Don’ts of CBO Agenda What is the Optimizer? Why Optimize? Available Optimizers Why is RBO being removed? Why move to CBO? Initialization parameters that affect CBO Internal Oracle parameters that affect CBO Setup changes for migrating to CBO Generating Statistics DML Monitoring Hints Statistics for SYS schema How to analyze execution plans in CBO? ORAMAIN Consulting Services, LLC

  3. The Do’s and Don’ts of CBO WHAT IS THE OPTIMIZER? An ‘engine’ running in the database. It is dedicated to derive a list of execution plans. Cost Based Optimization (CBO) Rule Based Optimization (RBO) In CBO the plan with the lowest cost is used. In RBO the plan with the best ranking is used. ORAMAIN Consulting Services, LLC

  4. The Do’s and Don’ts of CBO WHY OPTIMIZE? To execute a SQL statement in the shortest time. To utilized the fewest resources. CPU I/O Memory Network operations One bad SQL statement can impact all process on the server. ORAMAIN Consulting Services, LLC

  5. The Do’s and Don’ts of CBO AVAILABLE OPTIMIZERS. Oracle has two modes (pre 10g); Rule based optimization (RBO) Cost based optimization (CBO) RBO Follows a ranking methodology. Fifteen ranking points in RBO. Was the preferred mode. The fifteen ranking points are; ORAMAIN Consulting Services, LLC

  6. The Do’s and Don’ts of CBO Single row by ROWID Single row by cluster join Single row by hash cluster with unique or primary key Single row by unique or primary key Cluster join Hash cluster key Indexed cluster key Composite key Single column indexes Bounded range on index columns Unbounded range on indexed columns Sort merge join MAX or MIN on indexed column ORDER BY on indexed columns Full table scan ORAMAIN Consulting Services, LLC

  7. The Do’s and Don’ts of CBO CBO Follows expense calculation methodology. All execution plans get a cost. The lower the cost the less resources used. Uses statistics and histograms that are in the dictionary. Also uses user supplied hints and initora parameters. Can build up to 80,000 permutations of execution plans. ORAMAIN Consulting Services, LLC

  8. The Do’s and Don’ts of CBO STATISTICS Critical input for the Optimizer. Generated on data storing objects. May be exact or estimated. The more accurate, the better CBO works. Estimate uses a provided sample size, either number of rows or a percent. Block sampling is an option – usually for huge tables. ORAMAIN Consulting Services, LLC

  9. The Do’s and Don’ts of CBO STATISTICS (cont.) Good execution plans depend on how close the estimate is to the exact values. Test using different sample sizes. More on this later. Statistics are stored in tables owned by the SYS user. The DBA_ views show the statistics used by the optimizer. ORAMAIN Consulting Services, LLC

  10. The Do’s and Don’ts of CBO DBA_TABLES NUM_ROWS - Number of rows. BLOCKS - Number of used blocks. EMPTY_BLOCKS - Number of empty blocks that have never been used. AVG_SPACE - Average free space (in bytes) in blocks allocated to the table. All empty and free blocks are considered for this. CHAIN_CNT - Number of chained or migrated rows. ORAMAIN Consulting Services, LLC

  11. The Do’s and Don’ts of CBO DBA_TABLES (cont.) AVG_ROW_LEN - Average row length in bytes. LAST_ANALYZED - Date when the table was last analyzed. SAMPLE_SIZE - Sample size provided for ESTIMATE statistics. Equal to NUM_ROWS if COMPUTE. Statistics for individual partitions of a table can be seen from DBA_TAB_PARTITIONS. Cluster statistics are available from DBA_CLUSTERS. ORAMAIN Consulting Services, LLC

  12. The Do’s and Don’ts of CBO DBA_TAB_COLUMNS NUM_DISTINCT - Number of distinct values. LOW_VALUE - Lowest value HIGH_VALUE - Highest value DENSITY - Density of the column NUM_NULLS - Number of records with null value for the concerned column. LAST_ANALYZED - Date when the table was last analyzed. ORAMAIN Consulting Services, LLC

  13. The Do’s and Don’ts of CBO DBA_INDEXES BLEVEL - Depth of the index, from root to leaf. LEAF_BLOCKS - Number of leaf blocks. DISTINCT KEYS - Number of distinct keys. AVG_LEAF_BLOCKS_PER_KEY - Average number of leaf blocks in which each distinct key appears, should be 1 for unique indexes. AVG_DATA_BLOCKS_PER_KEY - Average number of blocks in the table that are pointed to by a distinct key. ORAMAIN Consulting Services, LLC

  14. The Do’s and Don’ts of CBO DBA_INDEXES (cont.) NUM_ROWS - Number of rows indexed. SAMPLE_SIZE - Sample size provided for ESTIMATE statistics. Equal to NUM_ROWS if COMPUTE. LAST_ANALYZED - Date when the table was last analyzed. GLOBAL_STATS - For partitioned indexes, YES - statistics collected as a whole, NO - statistics are estimated from statistics. ORAMAIN Consulting Services, LLC

  15. The Do’s and Don’ts of CBO AVAILABLE CBO MODES CBO has two available modes in which to run; ALL_ROWS FIRST_ROWS FIRST_ROWS aims at returning the first row(s) of the statement as soon as possible. Prefers nested-loops. Best for OLTP. As of 9i, FIRST_ROWS_n can be used. ORAMAIN Consulting Services, LLC

  16. The Do’s and Don’ts of CBO AVAILABLE CBO MODES (cont.) ALL_ROWS processes all rows for a given query before returning the output. It forces the optimizer to consider minimal use of resources and best throughput. ALL_ROWS prefers sort-merge joins. Good for batch type processing. ORAMAIN Consulting Services, LLC

  17. The Do’s and Don’ts of CBO CBO is dynamic and tunes its execution plans as the database grows in size. Do not be taken aback if the same query that works perfectly in one database setup is behaving badly in some other database of the same application. This would happen if the setup and statistics differ between the two databases. To prevent such behavior, you may consider using optimizer plan stability, which is covered later. ORAMAIN Consulting Services, LLC

  18. The Do’s and Don’ts of CBO BASIC CBO TERMS The following terms will be used quite often when analyzing statements in CBO. CostThe COST computed in CBO is a unit of expense involved with each operation. The logic as to how the cost is actually derived is not documented or made external. Moreover, this may change across releases. CardinalityThe number of rows in the table or number of distinct row links in the index. The cardinality of a query is the number of rows that is expected to be returned by it. ORAMAIN Consulting Services, LLC

  19. The Do’s and Don’ts of CBO BASIC CBO TERMS (cont.) StatisticsMuch required information gathered for various data holding objects. This information is vital for the CBO to decide on execution plans. Join MethodsOracle uses joins like Hash, sort-merge and nested loops. A query may run faster using one type of join as compared to other methods. This should be evaluated for individual queries. ORAMAIN Consulting Services, LLC

  20. The Do’s and Don’ts of CBO BASIC CBO TERMS (cont.) FTSFTS or Full Table Scan relates to a query sequentially scanning a table from the first block to the last allocated block. This could be very expensive for big tables and should be avoided. Index scanRelates to random access of a table by use of one or more indexes on the table.. ORAMAIN Consulting Services, LLC

  21. The Do’s and Don’ts of CBO WHY IS RBO BEING REMOVED? The existence of RBO prevents Oracle from making key enhancements to its query-processing engine. Its removal will permit Oracle to improve performance and reliability of the query-processing components of the database engine. Oracle 9i release 2 is the last version that supports RBO. Switch to CBO before this version is no longer supported. RBO will be available in Oracle 10g, but not supported. Presently, Oracle support for RBO is limited to bug fixesonly and no new functionality will be added to RBO. ORAMAIN Consulting Services, LLC

  22. The Do’s and Don’ts of CBO WHY MOVE TO CBO? Oracle stopped developing for RBO environment. RBO will be removed from the Oracle database. RBO has a limited number of access methods compared to CBO. All new features require CBO. CBO is enabled to identify these features, and how to evaluate their cost. These features will be of importance for any setup; e.g. IOTs, bitmap indexes, Function-based indexes, reverse-key indexes, Partitioning, Hash joins, Materialized views, parallel query, star joins, etc. ORAMAIN Consulting Services, LLC

  23. The Do’s and Don’ts of CBO WHY MOVE TO CBO? (cont.) Once RBO is no longer supported, Oracle support will not be available. CBO has matured. Distributed and remote queries are more reliable. RBO performed poorly joining tables across links. CBO is aware of statistics on the remote tables. ORAMAIN Consulting Services, LLC

  24. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER_MODE Valid values are RULE, CHOOSE, ALL_ROWS, FIRST_ROWS (_n). Default: choose. Dynamic If set to CHOOSE. The optimizer tries to run the query in either CBO or RBO depending on the availability or unavailability of statistics. Therefore, if the tables present in the query have statistics generated on them, CBO (ALL_ROWS only) is preferred else RBO is used. ORAMAIN Consulting Services, LLC

  25. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER_FEATURES_ENABLE Set to a version number such as- 8.1.5, 8.1.7, 9.0.0. Setting it to a lower version will prevent the use of new features that have come in later versions. Default is current release. Static. e.g.: optimizer_features_enable = 8.1.7 ORAMAIN Consulting Services, LLC

  26. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER_MAX_PERMUTATIONS Specifies the maximum number of permutations that should be considered for queries with joins, to choose an execution plan. Influences the parse time of queries. Set to a lower value. Default is 80000 in Oracle 8, in Oracle 9i it is defaulted to 2000. Dynamic. e.g.: optimizer_max_permutations = 3000 ORAMAIN Consulting Services, LLC

  27. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER COMPATIBLE Used to provide backward compatibility with earlier releases. May restrict the use of some new features. Only three digits are required to be specified, however, you can specify more for documentation purposes. Default is current release. Static. e.g.: compatible = 8.1.7 ORAMAIN Consulting Services, LLC

  28. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER HASH_JOIN_ENABLED Values, true or false. If set to false, hash joins will not be considered by the Optimizer. Default is true. Dynamic e.g.: hash_join_enabled = false ORAMAIN Consulting Services, LLC

  29. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER HASH_AREA_SIZE This specifies the maximum amount of memory in bytes to be used for a hash join per process. Oracle recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i. Default is 2 times SORT_AREA_SIZE. Dynamic. e.g.: hash_area_size = 2097152 Setting this to a very low number may sometimes result in the following error. ORA-6580: Hash Join ran out of memory while keeping large rows in memory. ORAMAIN Consulting Services, LLC

  30. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER_DYNAMIC_SAMPLING Is used in situations where tables are not analyzed. As CBO depends heavily on statistics, the parameter tells the optimizer to sample the unanalyzed tables that are being used in a query. A level of 0 to 10 can be specified, the higher the value the more time optimizer spends in sampling. Default is 1 in 9.2 and above, 0 if below. Dynamic. e.g.: optimizer_dynamic_sampling = 4 ORAMAIN Consulting Services, LLC

  31. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER CURSOR_SHARING Determines what kind of SQL statements can share the same cursor. It can be set to FORCE, SIMILAR or EXACT. FORCE will try to squeeze statements that may differ in some literals to share the same cursor. SIMILAR is somewhat the same but will try to maintain the plan optimization for identical statements. EXACT allows statements with exact identical text to share a cursor. Using FORCE may sometimes result in unexpected results. Default is exact. Dynamic e.g.: cursor_sharing = force ORAMAIN Consulting Services, LLC

  32. The Do’s and Don’ts of CBO INITORA PARMS THAT AFFECT THE OPTIMIZER PGA_AGGREGATE_TARGET Introduced in Oracle 9i, this parameter specifies the aggregate PGA memory available to all server processes attached to an instance. This parameter can be set for automatic sizing of SQL working areas. It replaces other existing parameters like SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and HASH_AREA_SIZE. Default is 0, automatic memory management is off. Dynamic. It can be set to a value between 10 MB to 4096 GB-1, depending on the setup requirement. Determine how much memory you have, subtract the SGA and give the rest to PGA, if possible. ORAMAIN Consulting Services, LLC

  33. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Undocumented, set by Oracle. Should not change unless recommended by Oracle Support. Begin with underscore (_). e.g.: ALTER SESSION SET “_COMPLEX_VIEW_MERGING” = TRUE; The following parameters changed the default from false to true when upgrading from 8i to 9i. They may have impact on response time. Negative or positive. ORAMAIN Consulting Services, LLC

  34. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _COMPLEX_VIEW_MERGING This parameter is related to improving the SQL performance on complex views (including inline views). Oracle tries to merge the query criteria with the existing view criteria that would result in a faster single query. For example, if a view is created with a GROUP BY clause in it and a query is executed on the view having a where clause, Oracle tries to merge the two and create a single query that would run the where clause prior to grouping it, thus giving better performance. ORAMAIN Consulting Services, LLC

  35. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _PUSH_JOIN_PREDICATE This enables the push join predicate feature that allows the optimizer to push join predicates inside a non-mergeable view(s). This would achieve something similar to a complex view merging feature, but in this case the join conditions provided in the query are pushed into the view. The view in this case cannot be merged with the query. ORAMAIN Consulting Services, LLC

  36. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _PUSH_JOIN_UNION_VIEW Same as above, but this parameter allows optimizer to push join predicates inside non-merge able views that contain UNION ALL set operators. _TABLE_SCAN_COST_PLUS_ONE This parameter increases the cost of a full table scan by one, in order to eliminate ties between a full table scan on a small lookup table and unique or range scan on the lookup table. ORAMAIN Consulting Services, LLC

  37. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _OR_EXPAND_NVL_PREDICATE This feature expands the NVL function predicates to evaluate the use of an index that may be present on the column used in the function. For example, if the expression is of the type "column1 = nvl(:b1, column1)" and column1 has an index on it, then optimizer may transform it to a new expression that uses the OR operator. This new expression will again be further transformed to make use of the UNION operator. ORAMAIN Consulting Services, LLC

  38. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _USE_COLUMN_STATS_FOR_FUNCTION Allows the use of column statistics for columns that are involved in non-operative expressions in query, such as: numcol + 0 charcol || '' Such expressions were mainly used in RBO to prevent the use of indexes. ORAMAIN Consulting Services, LLC

  39. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _ORDERED_NESTED_LOOP This reduces the cost of a nested loop join when the left side of the join is using an index or sort row source. _NEW_INITIAL_JOIN_ORDERS This parameter enables join permutation optimization. New ordering directives have been added to CBO for better processing of joins, setting this parameter will allow use of these directives. ORAMAIN Consulting Services, LLC

  40. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _B_TREE_BITMAP_PLANS Enables creation of interim bitmap representation for tables in a query with only binary index(es). _UNNEST_SUBQUERY This enables un-nesting of correlated sub-queries. Such queries may undergo MERGE join operations. ORAMAIN Consulting Services, LLC

  41. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Changed default (cont.). _INDEX_JOIN_ENABLED Enables the use of index joins wherever feasible, rather than at table level. ORAMAIN Consulting Services, LLC

  42. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Response time implications. _SQLEXEC_PROGRESSION_COST This controls the population of V$SESSION_LONGOPS view by long running queries. This view is used to monitor the progress of queries that are running for long duration. Queries that cost more than the value that has been set are identified for monitoring. Progression monitoring involves overhead and may affect the performance. Default is 1000, which may prevent SQL statements from being shared! Setting it to 0 will turn off the monitoring. ORAMAIN Consulting Services, LLC

  43. The Do’s and Don’ts of CBO INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER. Response time implications (cont.). _OPTIMIZER_MODE_FORCE This parameter decides the optimizer mode for users recursive SQL, for example, queries running from the PL/SQL block. In CBO, recursive SQL is executed in CHOOSE mode if this parameter is set to FALSE. If this parameter is set to TRUE, then recursive SQL inherits the session's optimizer mode. Hence, if the session is running in FIRST_ROWS, then all SQL processing carried out will be done in the same optimizer mode. Default is false. ORAMAIN Consulting Services, LLC

  44. The Do’s and Don’ts of CBO Setup changes for migrating to CBO Here we highlight a number of key points to consider when moving to CBO. In addition, we highlight a number of good maintenance practices. Tuning in CBO is an ongoing process and proper analysis should be done. You may encounter scenarios specific to your environment that are not mentioned here. Make it a point to refer to the documentation and check with Oracle support for any kind of anomalies. ORAMAIN Consulting Services, LLC

  45. The Do’s and Don’ts of CBO Setup changes for migrating to CBO (cont.) OPTIMIZER_MODE FIRST_ROWS(_n) for OLTP. ALL_ROWS for DSS, batch, wharehous. CHOOSE for mixed modes, i.e., Some applications or modules are in RBO mode and some in CBO mode. In CHOOSE mode CBO defaults to ALL_ROWS. Use alter session to force FIRST_ROWS(_n). ORAMAIN Consulting Services, LLC

  46. The Do’s and Don’ts of CBO Generate adequate statistics at proper intervals. Use DBMS_STATS to generate periodic statistics. ESTIMATE using 5 to 10% is usually adequate. Use COMPUTE for indexes and indexed-organized tables. Statistics are not incremental. Frequency of change determines frequency of analyzing. Global Temporary Tables can not be analyzed. Use DBMS_STATS.SET_TABLE_STATS. ORAMAIN Consulting Services, LLC

  47. The Do’s and Don’ts of CBO Generate adequate statistics at proper intervals (cont.). SQL> exec dbms_stats.set_table_stats(ownname => 'SYS', tabname => ‘EMP', numrows => 3000, numblks => 300, avgrlen => 50); PL/SQL procedure successfully completed. SQL> select num_rows, blocks, avg_row_len, temporary, user_stats from dba_tables where table_name = 'EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN T USE ---------- ---------- ----------- - --- 3000 300 50 Y YES ORAMAIN Consulting Services, LLC

  48. The Do’s and Don’ts of CBO Provide sufficient time for each site to settle down If you are supporting multiple client installations, my recommendation is to consider migrating each setup on different dates. Each site may have its own unique issues relating to individual setups, and this will give you more time to examine performance issues at each site. Make sure that testing is done on individual site test boxes before moving the production box to CBO. ORAMAIN Consulting Services, LLC

  49. The Do’s and Don’ts of CBO Change your scripts!!! Most DBAs rely on scripts. These may be outdated. For example, include columns such as LAST_ANALYZED, MONITORING, GLOBAL_STATS and USER_STATS in scripts that look at the object information. Modify your tuning scripts to find out in what mode the database, session or particular queries are running. ORAMAIN Consulting Services, LLC

  50. The Do’s and Don’ts of CBO Coding habits and Technical Guidelines Moving to CBO opens many new features for developing and designing. Your Technical Documentation Guidelines (if you have one) that developers rely on for standards. Liaise with the complete team to update your conventions. In RBO, the last table in the where clause is the driver. The first table is the driver in CBO. ORAMAIN Consulting Services, LLC

More Related