Understanding Oracle Optimizer RBO versus CBO Tantra Invedy

Understanding Oracle Optimizer: RBO versus CBO Tantra Invedy UBSW Energy 1

2 List of discussion Query execution overview Optimizer basics Understanding RBO Understanding CBO Understanding Hints Best practices

Query Execution Overview 3

Query Execution Overview 1. Create a cursor 2. Parse a statement <<< sql optimization 3. Describe query results 4. Define query output 5. Bind variables <<< after sql optimization 6. Parallelize the statement 7. Execute the statement 8. Fetch rows of a query 9. Close the cursor 4

5 Step 1: Create Cursor A cursor is a handle or name for a private SQL area. Unique handle and particular cursor can only be opened by 1 process at a time It contains information for statement processing Programs must have an open cursor to process a SQL statement.

Step 2: Parse the Statement Parse statement from user process. It involves: Translation and verification Table and column check Parse lock to prevent structural changing. Check privileges on referenced objects Determine optimal execution path Load statement into shared SQL area Route distributed statement correctly 6

Step 2: Parse the statement cont. . . Parsing is only necessary if there is no identical SQL in shared area A new shared SQL area is allocated and statement is parsed if identical SQL exist then this can be reused. 7

8 SGA Buffer Cache PGA Non shareable is stored in PGA • user run time information Shared Pool Log buffer Shared pool maintains shareable parts of the cursor: • query text • Execution plan • Bind variable data types and lengths Shareable cursors saves resources from unnecessary parsing.

Steps 3 and 4: Describe and Define Describe provides information about the select list items; it is relevant when entering dynamic queries through an OCI application The define step defines location, size, and data type information required to store fetched values in variables. 9

Steps 5 and 6: Bind and Parallize 10 Bind any bind values: Memory address to store data values l Values do not have to be in place yet l Allows shared SQL even though bind values may change l Parallize the statement l Parse stage has determined if the SQL can be parallized, and parallel plan already built.

11 Steps 7 through 9 Execute: Puts values into all bind variables l Drives the SQL statement to produce the desired results l Fetch rows (for select statement only) Into defined output variables. l Array fetch mechanism l Close the cursor

12 To remember …. Query optimization is in parsing stage, which is before binding variables. Cost of Non-sharable SQL, out of scope Next what is query optimization. . .

Optimizer Basic 13

14 Optimizer Basic It is an Oracle ‘engine’ that would choose the most efficient method on retrieving data on a given query. The steps chosen is called execution plan. Two kind of Optimizer on Oracle: RBO or Rule Based Optimizer since v 6 l CBO or Cost Based Optimizer since v 7 l

15 QUERY PROCESSING Query OPTIMIZER Parse Query rewrite Result Query Execution Query Optimazation RBO / CBO QEP Generation

Parse phase simple transformation 16 Convert query to an equivalent but more efficient expression l l lastname like (‘JONES’) >>> lastname in (‘AL’, ’WATT’) >>> lastname=‘JONES’ lastname=‘AL’ OR lastname=‘WATT’ salary between 10 and 100 >>> salary >= 10 AND salary>=100 NOT(salary<100 and JOB IS NULL) >> salary>=100 and JOB IS NOT NULL Transform OR into UNION ALL if the derived query is cheaper.

Query Rewrite within Optimizer View Merging Subquery Merging Transitivity (CBO only) Materialized views 17

18 View Merging View merging rewrites queries containing views so that only base tables remain. It is only done when a correct results is guaranteed. Either view is pushed out to query, or query is pushed into the view. If it cannot be merged, then view must be executed separately. (VIEW and FILTER operator on explain plan)

19 Non-merge-able Views Group by clause All aggregate functions Rownum reference Start with / connect by clause All set operation (union, minus, …) Distinct (unless query also has distinct) Has join with tables in original query

20 Sub-query Merging sub-query to open up new access path and new join order Single row subqueries. (Oracle will evaluate subquery and store the result) l ‘IN’ or subqueries might be converted into EXISTS or NOT EXISTS or in line view l

Sub-query Merging Example Select … from emp e where e. emp_id = select … from … Select … from emp e where e. emp_id = <evaluated_value>; select … from courses where dev_id in (select emp_id from emp) This would be flattened or converted into a join 21

22 Transitivity CBO performs transitivity as the first step in optimization. It generates additional predicates based on existing predicates. This would open more access paths. Transitivity is NOT done for join predicates.

23 Transitivity Example A=5, A=B then it is concluded that B=5 A=B , B=C then oracle would NOT conclude that A=C

Understanding RBO 24

25 Understanding RBO Released with Oracle 6. Using an ordered list of access methods and join methods on relative cost or each operation. Has a very limited input in determining access paths. Will be removed from the Oracle database Server Normally, it chooses the path from right to left in the from clause. If hint (except RULE hint) is supplied, then it will run under CBO. On some complex queries, it outperforms CBO

26 RBO ranking 1. Single row by ROWID 2. Single row by cluster join. 3. Single row by hash cluster key with unique key. 4. Single row by unique index. 5. Cluster join. 6. Hash Cluster key. 7. Indexed cluster key. 8. Composite key. 9. Single-column non-unique index. 10. Bounded range search on indexed columns 11. Unbounded range search on indexed columns 12. Sort-merge join 13. Max or Min of indexed columns 14. Order by on indexed columns 15. Full table-scan. (cluster) (cluster)

RBO: Determining Access path First it would evaluate join predicates and filtering predicates. Assign scores to each of the predicates, starting from the last to the first. Pick the lowest score, and evaluate the next. If there were ties, then it would choose arbitrarily based on : l l Order based on the FROM clause Age of an index 27

Not so good things about RBO. . . RBO has a small number of possible access method. (it 28 does not recognize IOT, bitmap index, hash join, …) It will process the tables based on how they are ordered on the query. (can be good and most of the time is not so good) Always ranks execution plan based on relative cost in the list, regardless of the data stored in the table. Index scan will always better than table scan, which is not true. Coding for the RBO is halted. All new features require implementation of CBO. RBO uses poor information to break frequently occurring ties.

Understanding CBO 29

30 Understanding CBO It uses all available information. Dictionary , statistics, histogram, supplied parameter setting. CBO uses Oracle ‘intelligent’ formula to calculate the COST of a SQL statement. Constantly improving from version to version. It examines all possible access methods (defaulted to 80, 000 permutations) Evaluate the costs for each access plan, then simple choose the lowest one

Available information to CBO 31 Database statistics (partial list …) l DBA_TABLES (num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, last_analyzed, sample_size, avg_space_freelist_blocks) l DBA_TAB_COLUMNS or DBA_TAB_COL_STATISTICS (num_distinct, low_value, high_value, density, num_nulls, num_buckets) l DBA_INDEXES (blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows, avg_leaf_block_per_key)

What if there is no statistics. . . If there is no statistics, or bind variables are used, then CBO may use default statistics Default statistics for Oracle 7. 3. 3 an above l l l l selectivity for relations on indexed columns. 009 selectivity for = on indexed columns. 004 multiblock read factor 8 remote table average row length 100 # of blocks 100 Scan cost 13 Index levels 1 number leaf blocks/key 1 32

Available information to CBO Initialization parameters that influence CBO cost computation (partial list …) db_file_multiblock_read_count l hash_multiblock_io_count l hash_area_size l sort_area_size l bitmatp_merge_area_size l 33

34 Available information to CBO Parameters affecting Cost computation l l l sort_multiblock_read_count optimizer_index_caching optimizer_index_cost_adj optimizer_percent_parallel optimizer_mode (choose defaulted to all_rows) l First_row applies a heuristic bias to the cost model to promote the use of indexes and nested loop)

CBO: Cost of a SQL statement Cost is the estimated number of I/O, CPU, Network operations that a statement requires. CPU cost (parse) has little impact on a SQL tuning on most cases. While I/O is the most. Difference between logical and physical I/O It is affected by some parameters: l db_file_multiblock_read_count, sort_area_size, hash_multiblock_io_count, bitmap_merge_area_size 35

36 Cost of Accessing Data Table scan cost Number of blocks below HWM l Multiblock read factor (default 8) l Number of extents is also taken into consederation l Example l Cost: 103 / 8 = 12. 875 => 13

37 Cost of Accessing Data Index Scan Costs are based on: l Index access number or levels in the B*-tree l number of leaf blocks to examine l l Consequent table lookup (optional) l number of blocks accessed Cost Fast full scan depends on db_file_multiblock_read_count

38 Cost of Sorting Data may need to be sorted for: l l l Order by aggregation Join operation Sorts are typically CPU intensive, and can be I/O bound if the sort can not fit in memory. Cost depends on sort_area_size and # rows.

39 Cost of a Join (SM) Rows from row source 1 are sorted Rows from row source 2 are then sorted by the same sort key Sorted rows from both sides are then merged Cost: sorting, reading tables, I/O for temporary segments MERGE Sort Row source 1 Row source 2

40 Cost of a Join (NL) Row source 1 is scanned (outer /driving table) Each row returned drives a lookup in row source 2 (inner) Joining rows are then returned Cost: Read driving table and access on inner table. Performance is very dependent on index on inner table Outer Loop Inner Loop Check for a match Nested Loop Access A (Full) Access B (ROWID) Index Access

41 Cost of a Join (HJ) In theory, it is the most efficient joint method. The smaller row source is used to build a hash table and a bitmap The second row source is hashed and checked against the hash table The bitmap is used as a quick lookup to check if rows are in the hash table. It requires single pass for each row source , and more efficient than sorting and merging Row source 1 (build input) Row source 2 (probe) Hash table and bitmap filter in MEMORY Output rows DISK

42 Join Order Evaluation The initial permutation is generated by sorting the join order in ascending order of their computed cardinality, as listed in where clause predicates. For each permutation, compute the cost and keep the one with the lowest cost. At any time, keep the current and the best permutation so far. Defaulted to 80, 000 permutation Most of times the order will not matter, but sometimes it does matter. (running out of permutation limits ? )

43 Common CBO problem The skewness problem Analyzing with wrong data Mixing optimizer in joins Choosing inferior index Joining too many tables Incorrect INIT. ORA settings 30% 25% 20% < 5%

Using HINT 44

45 Why use hints. . . Sometimes the optimizer may give an optimal plan. Hint is supplied as a directive and may get ignored. Developers know more about the data. Hints can be used to influence: l l l The optimization approach (RULE, ALL_ROWS, FIRST_ROWS) The access path for a table accessed (FULL , INDEX, HASH, . . . ) The join order and method (ordered, leading, star, …use_nl, use_merge, use_hash. . . )

Hint: Optimization approach CHOOSE defaulted to ALL_ROWS “BEST” plan may mean: Use minimal resource to process all rows affected by the statement ===> ALL_ROWS. (prefer SMJ) l Returns the first row of a statement as quickly as possible ===> FIRST_ROWS. (prefer NLJ) l Optimizer has limited information available to determine the “BEST” plan. 46

Choosing which optimization System level, session level , statement level. First_rows returns the first rows as quick as possible. All_rows is for optimal throughput, batch oriented application. Rule, some cases outperform CBO on complex queries First_rows(xxx) or First_rows _nnn , new on Oracle 9 i 47

48 Hint: Access Paths FULL ROWID CLUSTER INDEX_ASC INDEX_DESC INDEX_JOIN INDEX_FFS NO_INDEX AND_EQUAL

Hint: Query Transformation USE_CONCAT NO_EXPAND REWRITE MERGE NO_MERGE START_TRANSFO RMATION FACT NO_FACT 49

HINT: Join Orders and Join Operation ORDERED STAR USE_NL USE_MERGE USE_HASH DRIVING_SITE LEADING HASH_AJ, MERGE_AJ, NL_AJ HASH_SJ, MERGE_SJ, NL_SJ 50

HINT: Parallel execution and others PARALLEL NOPARALLEL_INDEX NOPARALLEL_IND EX APPEND NOAPPEND CACHE NOCACHE PUSH_PRED NO_PUSH_PRED PUSH_SUBQ ORDERED_PREDICAT ES 51

52 Hint and View Not recommended to use hint inside a view, because view can be used in different context. Different behavior on Merge-able view and non merge-able views

53 Hint and Parallel query will perform direct I/O, bypassing buffer Cache Will be using ROWID hint will force a checkpoint on the object Be cautious on setting parallel degree, as there are producers and consumers processes for parallel query processes.

Best Practices 54

55 Best practices: General Know how CBO and RBO choose its plan. l l View / subquery merging, transitivity FIRST_ROWS vs ALL_ROWS vs RULE Different ‘logic’ from version to version. Dynamic plan based on statistics CBO behavior changes from version to version. Set by optimizer_features_enable Know the impact of using bind variables l l Do not compare with using literals Oracle 9 i, peeking bind variables before generating explain plan

56 Best practices: General Option to use OUTLN, to stabilize explain plan. Set INIT. ORA parameter appropriately. We know more information than CBO

Best practices: Database Objects 57 Avoid abusing views: l l Nested views and multi view joins. Unnecessarily using views. ( … as select * from snp_) Use index effectively l l Drop bad indexes , low ratio for distinct values / num rows For composite index, start with column that has more distinct values. Unique scan for unique index, use all index columns Generate Histogram for skewwed indexes High water mark on often deleted tables, and sparse indexes

Best practices: Statistics Representative Statistics. l l Up-to-date statistics High water mark of a table Optimal setting for init. ora parameter Use Histogram if necessary for skewed data Know when you are using default statistics l l l Bind variables Missing object statistics on some tables Remotely join tables Statistics can be exported to different environment Use DBMS_STATS instead of analyze command 58

Best practices: Using HINT 59 Use hint appropriately l l l Guide optimizer to start with the most selective predicates. Ordered hint might prevent CBO to find an alternative plan as data distribution change. Cautious with parallel degree. Make sure the load is no contention, and not overloading DB servers. Use RULE hint on complex query if it performs better. Understand the impact of putting HINT inside a view. Syntax error will be ignored. /*+ <HINT> */ Multiple HINTS can be applied, separate with space

60 Best practices: SQL Avoid using RBO techniques l Suppressing indexes ( || ‘’ or + 0) on the where clause. Most cases, order on from and where clause don't matter. In some complex query, it may influence CBO access path. CBO evaluates list of predicates (where) clause to start evaluating explain plan By default it is 80, 000 permutation generated Index scan is NOT always outperform Full table scan Join involves more than 5 table often confuse CBO.

61 References Oracle SQL Tuning: Pocket Reference, by Mark Gurry. Everything You Always wanted to know about optimizer: Student guide, by Oracle University. The Search for Intelligent Life in The Cost-Based Optimizer, Tim Gorman, Database Technologies, Inc Cost Based Optimizer (CBO)- A Technology Change or a New Feature? , Muthu Ramaswamy, GERS Inc. Inside the Oracle Cost Based Optimizer, Richard M. Slavik, S&P Solutions Note: 35934. 1: Cost Based Optimizer - Common Misconception and Issues, Metalink. oracle. com Sources from Oracle 8. 1. 7 documentation, performance tuning guide.

Q&A Thank you 62