Download - Understanding the Oracle Optimizer
-
8/4/2019 Understanding the Oracle Optimizer
1/62
1
Understanding Oracle
Optimizer:
RBO versus CBO
Tantra Invedy
UBSW Energy
-
8/4/2019 Understanding the Oracle Optimizer
2/62
2
List of discussion
Query execution overview
Optimizer basics
Understanding RBO
Understanding CBO
Understanding HintsBest practices
-
8/4/2019 Understanding the Oracle Optimizer
3/62
3
Query Execution Overview
-
8/4/2019 Understanding the Oracle Optimizer
4/62
4
Query Execution
Overview1. Create a cursor2. Parse a statement
-
8/4/2019 Understanding the Oracle Optimizer
5/62
5
Step 1: Create Cursor
A cursor is a handle or name for aprivate SQL area. Unique handle and
particular cursor can only be opened by1 process at a time
It contains information for statementprocessing
Programs must have an open cursor toprocess a SQL statement.
-
8/4/2019 Understanding the Oracle Optimizer
6/62
6
Step 2: Parse the
StatementParse statement from user process. It involves:
Translation and verification
Table and column checkParse lock to prevent structural changing.
Check privileges on referenced objects
Determine optimal execution pathLoad statement into shared SQL area
Route distributed statement correctly
-
8/4/2019 Understanding the Oracle Optimizer
7/62
7
Step 2: Parse the
statement cont...Parsing is only necessary if there is noidentical SQL in shared area
A new shared SQL area is allocatedand statement is parsed
if identical SQL exist then this can be
reused.
-
8/4/2019 Understanding the Oracle Optimizer
8/62
8
Shared Pool
Buffer Cache
Log buffer
SGA
PGA
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.
Non shareable is stored in PGA
user run time information
-
8/4/2019 Understanding the Oracle Optimizer
9/62
9
Steps 3 and 4: Describe
and DefineDescribe provides information about theselect list items; it is relevant when
entering dynamic queries through anOCI application
The define step defines location, size,
and data type information required tostore fetched values in variables.
-
8/4/2019 Understanding the Oracle Optimizer
10/62
10
Steps 5 and 6: Bind and
ParallizeBind any bind values: Memory address to store data values
Values do not have to be in place yet Allows shared SQL even though bind
values may change
Parallize the statement Parse stage has determined if the SQL can
be parallized, and parallel plan alreadybuilt.
-
8/4/2019 Understanding the Oracle Optimizer
11/62
11
Steps 7 through 9
Execute: Puts values into all bind variables
Drives the SQL statement to produce thedesired results
Fetch rows (for select statement only) Into defined output variables.
Array fetch mechanism
Close the cursor
-
8/4/2019 Understanding the Oracle Optimizer
12/62
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 ...
-
8/4/2019 Understanding the Oracle Optimizer
13/6213
Optimizer Basic
-
8/4/2019 Understanding the Oracle Optimizer
14/62
14
Optimizer Basic
It is an Oracle engine that wouldchoose the most efficient method on
retrieving data on a given query.The steps chosen is called executionplan.
Two kind of Optimizer on Oracle: RBO or Rule Based Optimizer since v6
CBO or Cost Based Optimizer since v7
-
8/4/2019 Understanding the Oracle Optimizer
15/62
15
Query rewriteParseQuery Optimazation
RBO / CBO
Query
Execution
QEP
Generation
OPTIMIZER
QUERY PROCESSING
Query
Result
-
8/4/2019 Understanding the Oracle Optimizer
16/62
16
Parse phase simple
transformationConvert query to an equivalent but moreefficient expression lastname like (JONES) >>> lastname=JONES
lastname in (AL,WATT) >>> lastname=AL ORlastname=WATT
salary between 10 and 100 >>> salary >= 10 ANDsalary>=100
NOT(salary> salary>=100 andJOB IS NOT NULL
Transform OR into UNION ALL if thederived query is cheaper.
-
8/4/2019 Understanding the Oracle Optimizer
17/62
17
Query Rewrite within
OptimizerView Merging
Subquery Merging
Transitivity (CBO only)
Materialized views
-
8/4/2019 Understanding the Oracle Optimizer
18/62
18
View Merging
View merging rewrites queries containingviews so that only base tables remain.
It is only done when a correct results isguaranteed.
Either view is pushed out to query, or query ispushed into the view.
If it cannot be merged, then view must beexecuted separately. (VIEW and FILTERoperator on explain plan)
19
-
8/4/2019 Understanding the Oracle Optimizer
19/62
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
-
8/4/2019 Understanding the Oracle Optimizer
20/62
20
Sub-query Merging
Merging sub-query to open up newaccess path and new join order
Single row subqueries. (Oracle willevaluate subquery and store the result)
IN or subqueries might be converted into
EXISTS or NOT EXISTS or in line view
21
-
8/4/2019 Understanding the Oracle Optimizer
21/62
21
Sub-query Merging
ExampleSelect from emp e where e.emp_id = select from
Select from emp e where e.emp_id =
;
select from courses where dev_id in (select emp_id
from emp)
This would be flattened or converted into a join
22
-
8/4/2019 Understanding the Oracle Optimizer
22/62
22
Transitivity
CBO performs transitivity as the firststep in optimization.
It generates additional predicates basedon existing predicates. This would openmore access paths.
Transitivity is NOT done for joinpredicates.
23
-
8/4/2019 Understanding the Oracle Optimizer
23/62
23
Transitivity Example
A=5, A=B then it is concluded that B=5
A=B , B=C then oracle would NOTconclude that A=C
-
8/4/2019 Understanding the Oracle Optimizer
24/62
24
Understanding RBO
25
-
8/4/2019 Understanding the Oracle Optimizer
25/62
25
Understanding RBO
Released with Oracle 6.
Using an ordered list of access methods and joinmethods 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 thefrom clause.
If hint (except RULE hint) is supplied, then it will rununder CBO.
On some complex queries, it outperforms CBO
26
-
8/4/2019 Understanding the Oracle Optimizer
26/62
26
RBO ranking
1. Single row by ROWID2. Single row by cluster join. (cluster)
3. Single row by hash cluster key with unique key. (cluster)
4. Single row by unique index.
5. Cluster join. (cluster)
6. Hash Cluster key. (cluster)
7. Indexed cluster key. (cluster)
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 join13. Max or Min of indexed columns
14. Order by on indexed columns
15. Full table-scan.
27
-
8/4/2019 Understanding the Oracle Optimizer
27/62
27
RBO: Determining
Access pathFirst it would evaluate join predicates andfiltering 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 : Order based on the FROM clause
Age of an index
28
-
8/4/2019 Understanding the Oracle Optimizer
28/62
28
Not so good things
about RBO...RBO has a small number of possible access method.(it 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 thetime is not so good)
Always ranks execution plan based on relative cost inthe list, regardless of the data stored in the table.Index scan will always better than table scan, whichis not true.
Coding for the RBO is halted. All new featuresrequire implementation of CBO.
RBO uses poor information to break frequently
occurring ties.
-
8/4/2019 Understanding the Oracle Optimizer
29/62
29
Understanding CBO
30
-
8/4/2019 Understanding the Oracle Optimizer
30/62
30
Understanding CBO
It uses all available information. Dictionary ,statistics, histogram, supplied parametersetting.
CBO uses Oracle intelligent formula tocalculate 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, thensimple choose the lowest one
31
-
8/4/2019 Understanding the Oracle Optimizer
31/62
31
Available information to
CBODatabase statistics (partial list )
DBA_TABLES (num_rows, blocks, empty_blocks,avg_space, chain_cnt, avg_row_len,
last_analyzed, sample_size,avg_space_freelist_blocks)
DBA_TAB_COLUMNS orDBA_TAB_COL_STATISTICS (num_distinct,low_value, high_value, density, num_nulls,num_buckets)
DBA_INDEXES (blevel, leaf_blocks, distinct_keys,clustering_factor, num_rows,avg_leaf_block_per_key)
32
-
8/4/2019 Understanding the Oracle Optimizer
32/62
32
What if there is no
statistics ...If there is no statistics, or bind variables areused, then CBO may use default statistics
Default statistics for Oracle 7.3.3 an above 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
33
-
8/4/2019 Understanding the Oracle Optimizer
33/62
33
Available information to
CBOInitialization parameters that influenceCBO cost computation (partial list )
db_file_multiblock_read_count hash_multiblock_io_count
hash_area_size
sort_area_size bitmatp_merge_area_size
34
-
8/4/2019 Understanding the Oracle Optimizer
34/62
34
Available information to
CBOParameters affecting Cost computation
sort_multiblock_read_count
optimizer_index_caching optimizer_index_cost_adj
optimizer_percent_parallel
optimizer_mode (choose defaulted to all_rows)
First_row applies a heuristic bias to the cost model topromote the use of indexes and nested loop)
35
-
8/4/2019 Understanding the Oracle Optimizer
35/62
35
CBO: Cost of a SQL
statementCost is the estimated number of I/O, CPU,Network operations that a statement requires.
CPU cost (parse) has little impact on a SQLtuning on most cases. While I/O is the most.
Difference between logical and physical I/O
It is affected by some parameters:
db_file_multiblock_read_count, sort_area_size,hash_area_size, hash_multiblock_io_count,bitmap_merge_area_size
36
-
8/4/2019 Understanding the Oracle Optimizer
36/62
36
Cost of Accessing Data
Table scan cost
Number of blocks below HWM
Multiblock read factor (default 8) Number of extents is also taken into
consederation
Example Cost: 103 / 8 = 12.875 => 13
37
-
8/4/2019 Understanding the Oracle Optimizer
37/62
37
Cost of Accessing Data
Index Scan Costs are based on:
Index access
number or levels in the B*-tree number of leaf blocks to examine
Consequent table lookup (optional)
number of blocks accessed
Cost Fast full scan depends ondb_file_multiblock_read_count
38
-
8/4/2019 Understanding the Oracle Optimizer
38/62
38
Cost of Sorting
Data may need to be sorted for:
Order by
aggregation Join operation
Sorts are typically CPU intensive, and can beI/O bound if the sort can not fit in memory.
Cost depends on sort_area_size and # rows.
39
-
8/4/2019 Understanding the Oracle Optimizer
39/62
39
Cost of a Join (SM)
Rows from row source 1are sorted
Rows from row source 2are then sorted by thesame sort key
Sorted rows from bothsides are then merged
Cost: sorting, readingtables, I/O for temporarysegments
Row
source 1
Row
source 2
Sort Sort
MERGE
40
-
8/4/2019 Understanding the Oracle Optimizer
40/62
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 thenreturned
Cost: Read driving table andaccess on inner table.
Performance is verydependent on index on innertable
Outer Loop
Inner Loop
Check for a match
Nested Loop
Access A
(Full)
Access B
(ROWID)
Index Access
41
-
8/4/2019 Understanding the Oracle Optimizer
41/62
41
Cost of a Join (HJ)In theory, it is the mostefficient joint method.
The smaller row source isused to build a hash tableand a bitmap
The second row source ishashed and checked againstthe hash table
The bitmap is used as a quicklookup to check if rows are inthe hash table.
It requires single pass foreach row source , and moreefficient than sorting andmerging
Row source 1
(build input)
Row source 2
(probe)
Hash table
and bitmap filter
in MEMORY
Output rows
DISK
42
-
8/4/2019 Understanding the Oracle Optimizer
42/62
42
Join Order Evaluation
The initial permutation is generated by sorting the joinorder in ascending order of their computedcardinality, as listed in where clause predicates.
For each permutation, compute the cost and keep theone with the lowest cost.
At any time, keep the current and the bestpermutation so far.
Defaulted to 80,000 permutation
Most of times the order will not matter, but sometimesit does matter. (running out of permutation limits ?)
43
-
8/4/2019 Understanding the Oracle Optimizer
43/62
43
Common CBO problem
The skewness problem 30%
Analyzing with wrong data 25%
Mixing optimizer in joins 20%
Choosing inferior index 20%
Joining too many tables < 5%
Incorrect INIT.ORA settings < 5%
-
8/4/2019 Understanding the Oracle Optimizer
44/62
44
Using HINT
45
-
8/4/2019 Understanding the Oracle Optimizer
45/62
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:
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 ...)
46
-
8/4/2019 Understanding the Oracle Optimizer
46/62
46
Hint: Optimization
approachCHOOSE defaulted to ALL_ROWS
BEST plan may mean:
Use minimal resource to process all rowsaffected by the statement ===> ALL_ROWS.(prefer SMJ)
Returns the first row of a statement as quicklyas possible ===> FIRST_ROWS. (prefer NLJ)
Optimizer has limited informationavailable to determine the BEST plan.
47
-
8/4/2019 Understanding the Oracle Optimizer
47/62
47
Choosing which
optimizationSystem level, session level , statement level.
First_rows returns the first rows as quick aspossible.
All_rows is for optimal throughput, batchoriented application.
Rule, some cases outperform CBO on
complex queriesFirst_rows(xxx) or First_rows _nnn , new onOracle 9i
48
-
8/4/2019 Understanding the Oracle Optimizer
48/62
48
Hint: Access Paths
FULL
ROWID
CLUSTERINDEX
INDEX_ASC
INDEX_DESCINDEX_JOIN
INDEX_FFS
NO_INDEX
AND_EQUAL
49
-
8/4/2019 Understanding the Oracle Optimizer
49/62
49
Hint: Query
TransformationUSE_CONCAT
NO_EXPAND
REWRITEMERGE
NO_MERGE
START_TRANSFORMATION
FACT
NO_FACT
50
-
8/4/2019 Understanding the Oracle Optimizer
50/62
50
HINT: Join Orders and
Join OperationORDERED
STAR
USE_NL
USE_MERGE
USE_HASHDRIVING_SITE
LEADING
HASH_AJ,
MERGE_AJ, NL_AJHASH_SJ,MERGE_SJ, NL_SJ
51
-
8/4/2019 Understanding the Oracle Optimizer
51/62
51
HINT: Parallel execution
and othersPARALLEL
NOPARALLEL
PARALLEL_INDEXNOPARALLEL_INDEX
APPEND
NOAPPEND
CACHE
NOCACHE
PUSH_PRED
NO_PUSH_PRED
PUSH_SUBQORDERED_PREDICATES
52
-
8/4/2019 Understanding the Oracle Optimizer
52/62
52
Hint and View
Not recommended to use hint inside aview, because view can be used in
different context.Different behavior on Merge-able viewand non merge-able views
53
-
8/4/2019 Understanding the Oracle Optimizer
53/62
53
Hint and Parallel query
Parallel query will perform direct I/O,bypassing buffer Cache
Will be using ROWID hint.ROWID hint will force a checkpoint onthe object
Be cautious on setting parallel degree,as there are producers and consumersprocesses for parallel query processes.
-
8/4/2019 Understanding the Oracle Optimizer
54/62
54
Best Practices
55
-
8/4/2019 Understanding the Oracle Optimizer
55/62
55
Best practices: General
Know how CBO and RBO choose its plan. 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. Setby optimizer_features_enable
Know the impact of using bind variables Do not compare with using literals
Oracle 9i, peeking bind variables before generating explainplan
56
-
8/4/2019 Understanding the Oracle Optimizer
56/62
56
Best practices: General
Option to use OUTLN, to stabilizeexplain plan.
Set INIT.ORA parameter appropriately.We know more information than CBO
57
-
8/4/2019 Understanding the Oracle Optimizer
57/62
5
Best practices:
Database ObjectsAvoid abusing views: Nested views and multi view joins.
Unnecessarily using views. ( as select * from snp_)
Use index effectively Drop bad indexes , low ratio for distinct values / num rows
For composite index, start with column that has more distinctvalues.
Unique scan for unique index, use all index columns Generate Histogram for skewwed indexes
High water mark on often deleted tables, and sparseindexes
58
-
8/4/2019 Understanding the Oracle Optimizer
58/62
Best practices:
StatisticsRepresentative Statistics. 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 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
59
-
8/4/2019 Understanding the Oracle Optimizer
59/62
Best practices: Using
HINTUse hint appropriately
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. /*+ */
Multiple HINTS can be applied, separate with space
60
-
8/4/2019 Understanding the Oracle Optimizer
60/62
Best practices: SQL
Avoid using RBO techniques
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 CBOaccess path.
CBO evaluates list of predicates (where) clause tostart 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
-
8/4/2019 Understanding the Oracle Optimizer
61/62
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 NewFeature?, Muthu Ramaswamy, GERS Inc.
Inside the Oracle Cost Based Optimizer, Richard M. Slavik, S&PSolutions
Note:35934.1: Cost Based Optimizer - Common Misconception andIssues, Metalink.oracle.com
Sources from Oracle 8.1.7 documentation, performance tuning guide.
-
8/4/2019 Understanding the Oracle Optimizer
62/62
Q & A
Thank you