Oracle 效率 Cost Based Optimizer （第二部分）

严禁转载，使用请注明出处：http://alex2012-c.j.overblog.com/

参加公司Oracle Optimization的三天培训，总结了报告。第二部分。

CBO and execution plans

Execution plan & statistics

Plan to access the data (access path), Oracle evaluate several and selects the cheapest (cost based).

Time shown in plan is not HH:MM:SS, it's MM:SS:ss (hundredth of second).

Plan is selected according to cost (CBO), it is determined at query hard parse time based on the statistics.

The plan is not revised afterwards, thus Oracle can make mistakes !

User can force an execution plan by:

• Changing the query etc. in order to get a query that is more expensive (higher cost) BUT that is faster, because Oracle does only a cost based selection.
• Put an explicit execution plan as hint (after the select use /*+ */)

In textual representation of the execution plan, the predicate is referenced by the asterisk character in front of the step number.

Estimated number of rows and actual number of rows can be displayed by using the additional command. With such display the "Buffers" columns shows how many blocks are read by the operations.

In DB V$SQL_PLAN contains actual plans used, in opposite to PLAN_TABLE which contains theoretical plans.

Also V$SQL_PLAN_STATISTICS contains execution statistics (it does not have the actual CPU cost though, which is only in V$SQL as cumulated other all executions).

How to interpreting big execution plans ?

Split into small pieces and check for places where the time is lost (e.g. reading of lot of blocks).

Optimizer

Oracle can try rewriting the query to enhance it (e.g. transform OR into UNION ALL + exclusions)

Based on statistics + oracle version the optimizer can rewrite queries but this is not guaranteed, so writing the right version of the query is always preferable.

Simplest is to test it but optimization can change depending on the Oracle version as well, and this introduces instability (e.g. bug in statistics of v11.2.x) !

Optimizer can eliminate irrelevant stuff like order by, unused table, etc.

Some examples:

-- Remove OR to use index (or combine indexes into bitmap indexes since Oracle 11g)

Select * from emp where job = 'CLERK' OR depno = 10;

Becomes

Select * from emp where job = 'CLERK'

UNION ALL

Select * from emp WHERE depno = 10 AND job <> 'CLERK';

-- Un-nesting to enable direct join, so enable the choice between the different strategies (nested loop vs merge join vs hash join) and also choose the best order for join operations

Select * from accounts where custno IN (select custno FROM customer)

Becomes

Select accounts.* from accounts, customers where accounts.custno = customers.custno;

-- View merging

Will execute query on view directly on the target table using both predicates of the view query + user query on view. This avoids creating the 1st temp result based on the view query, then apply user query on it.

Means that often the views are performance neutral.

-- Transitivity

Select * from emp, dept where emp.deptno = 20 and emp.deptno = dept.deptno;

Becomes

Select * from emp, dept where emp.deptno = 20 and emp.deptno = dept.deptno AND dept.deptno = 20;

-- Filters out on dept.deptno first (pkey => index) then join with emps.

-- Does not even filter twice on the criterion deptno = 20 for emps, as the JOIN guarantees we already have this filter.

Selectivity

Is the ratio between the total number of rows and a filter. It depends on data distribution.

Cardinality

Expected number of rows retrieved by a particular operation in the execution plan (column "Rows" and derived "Byte" column which is the average size of a row).

= selectivity * total number of rows

Vital for determining cost of join, filters, and sort operations

Select * from A where B = 'value'

If total rows = 1000 and distinct values for B = 200:

Selectivity = 1/200

Cardinality = 1/200*1000

CBO

CBO started with Oracle 7. Before that there was only the Rule Based Optimizer (RBO) which is now not supported anymore officially by Oracle.

Cost

Best estimate "a priori" of the number of I/O to execute a particular statement.

Cost unit is a single random block read (default is 10ms).

Cost = (single block I/O + multi blocks I/O + CPU cost) / single block read time

Certain I/Os allow reading multiple blocks at once when they are contiguous (case for scans).

It is possible to bias the cost thanks to system statistics, for instance by favoring CPU usage over I/Os, this allows to reproduce the behavior of the optimizer in PRD conditions for instance.

System statistics should be collected under heavy/normal load conditions, usually they are triggered by DBAs.

This also means that CPU consumption is not necessarily a cons IF consuming more CPU to accelerate queries is what we want WITHOUT then creating contention on the buffer cache (which as a CPU access cost even if in memory).

However high CPU usage is often the sign of bad SQL optimization (e.g. excessive access to buffer cache).

Joins cost

Cost for joins which depends on the order of the joins can be evaluated in different combinations by Oracle but it won't go beyond 2000 permutations by default.

Usually it won't even try all permutations when the cost is determined to be good enough after a number of combinations tried, so it saves on the execution plan computation.

Nb of permutations = factorielle(nb of tables to join).

You can influence or force the order thanks to hints.

Normally the 3 merge strategies (nested loop / merge join / hash join) should be evaluated but when the cost of the 1st method is low enough the others can be skipped.

So because it may not evaluate all the options it is hard to get the best possible plan when there are many joins.

A hidden parameter to force it to evaluate all the permutations may exist.

Beware that the cost for merge join and hash join is rather independent from the order of joins, while nested loop is heavily dependent.

Warning on cost

The weakness in the system is the formula for computing the cost is fixed.

Thus the CBO can fail when evaluation of one of the 3 components is incorrect (because of statistics the cardinality can be wrong for instance).

Cardinality is worth verifying by executing the query for real, and anyway testing against real data is best practice (low costs which are determined while testing on non representative data can be wrong since they are just estimates !).

Cardinality feedback

New feature of 11.2 "cardinality feedback" helps to correct bad execution plans after 1st execution.

With this feature Oracle detects when the execution plan was too long on previous run, and thus try a different one on the next execution.

Issue with this feature is that the execution plan can change over time, changing the time of execution.

Warning on optimizer parameters

Changing parameters default values is probably a bad idea, in opposite to various articles that can be found on the web, as it will change the execution plans on a lot of queries.

Oracle adjusts these parameters live and for every query. Making them static is bad idea as it changes for all queries, and although it may improve certain specific queries (that could be tuned individually) and may degrade some others which would normally run fine.

Especially avoid changing the OPTIMIZER_INDEX_* ones as it may result in Oracle bypassing indexes after cost evaluation, and also it can spoil the selectivity evaluation which then results in bad execution plan.

Warnings on CPU and buffer cache

Do not forget that reading from the buffer cache still consumes CPU.

AWR

Automated Workload Repository contains reports about CPU consumption, elapsed time, etc.

By default taken every hour.

Can be triggered manually:

http://druid.muc.amadeus.net => search for APT, then click onto one of the FOM graphs, then

• "AWR" button at bottom gives the AWR report
• "SQL report" at bottom gives a summary linking to the detailed report for each query which contains the execution plan

Joins

About joins order

To force the consideration of the max cardinality of a table in a series of joins, one can use the cardinality hint.

This is applicable when the join criterion is a variable and not a literal (which would immediately make the optimizer select the index usage for it and thus change the join order).

-- Cardinality hint

SELECT /*+cardinality(<TABLE_NAME> <max estimated nb of rows>) */ FROM ...

Beware of the pitfall for this hint: if the cardinality changes then the execution plan becomes sub optimal !

Using a sub select will force Oracle to select first from the sub select, it can be a very good solution in order to let Oracle still apply other optimizations to the whole query, instead of using hints.

Questions asked

Performances

• Lock on multiple tables is not a significant overhead. It will put a lock indicator into each table data block where a locked row is stored, but the block is read to fetch the table data anyway.
• Lock is an information in the header of a block. It's a lock at row level, not at block level. However each block has a max number of locks that is 255 by default.
• When this max number is reached, updaters are queued. This is a block contention.
• The contention on a block will depend on the number of concurrent accesses/updates on it, but when records are big generally you have a low number of records per block (reminder: block default size is 8K). Thus block contention rarely happens on data table BUT can often happen on indexes since these contain little information for each record (and thus have more entries per block). This is another reason for creating indexes wisely.
• The max number of locks that can be put into a block header is controlled by the ITL_TRANS parameter that is part of the definition of the object that will stored into these blocks (TABLE, INDEX, etc.).
• Contention on blocks can be seen in AWR reports (see wait events or ITL related entries).
• At runtime, contention on blocks can be seen with the V$ view, thus under load conditions one can see live where much time is spent on waits.
• Still it's possible to tell Oracle to lock only one table by specifying the columns in the "FOR UPDATE" clause.

Efficiency will depend on the number of rows stored inside blocks. The more blocks will be needed to store a single record, the slower the reading will be.

So making the rows bigger by adding columns may lead to more inefficient solutions.

For instance a separate table with a lot of rows but few columns will be efficient, especially on low figures (order of magnitude of "low figure" = several 10K of rows).

Also don't forger that Oracle was built to do joins on tables. So you don't have to make effort to avoid joins at any cost.

Where joins can impact performances the most it’s on CPU consumption on the DB server side (which may not be a problem unless you are CPU bound).

However testing the different cases is the best, there is no black/white categorization like de-normalization is always good or bad.