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

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

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

Anti patterns or performance pitfalls

• Implicit conversions (e.g. data defined as VARCHAR but queried with a numeric value => the VARCHAR will always be converted)
• Inefficient index
  • Incomplete coverage of the index compared to query patterns

• Stale or missing optimizer statistics

• Suboptimal execution plan
• Poorly constructed SQL
  • e.g. correlated sub queries, like:
    • Alias on subqueries
    • Nested sub selects: select (select from B where B.A=A) from A
      • If "select from A" retrieves 1M rows, the nested select from B will be executed 1M times !

Examples

• Correlated sub query used in WHERE clause, it will run every time the parent query returns one row => will do a loop
  • Better use SELECT FROM T1, (sub query) => Join rather than Loop

• Many functions usage (conversion, substrings, etc), especially on JOINs because it often skips the indexes

• Implicit conversion on columns (which are indexed) instead of converting a constant (input) value => skips the index
  • The implicit conversion can be seen in the execution plan

• UNION instead of UNION ALL => searches for duplicates to remove from the result, while often not required

SQL Operators

In select statements, UNION is often better than OR because it enables the usage of the index, whereas OR disables it.

However UNION is an operator for sets, which forces the DB to make distinct operations, so use UNION ALL instead if no distinct is wanted (e.g. concatenate 2 tables which don't have duplicates).

Commit policy

The only limit for waiting before commit is the UNDO log size, so you can adjust your behavior in regards to that, waiting the most possible but avoid filling up the UNDO log table space entirely.

Advices & comments

• There is nothing straightforward on the topic of SQL tuning (no magic recipe).
• Test your query and check the plans, theory is never enough. Use the actuals vs estimates in execution plans + execution statistics to check the theory can apply.
• To compare 2 queries do flush the buffer cache before AND if comparing 2 execution plans do change something to the query text so Oracle assigns it with a different sql_id (thus a different plan).
• Spot long running operations running by checking the V$SESSION_LONGOPS but beware that only few ops go in this table (full table scans, etc.).
• Be careful that SQLPLUS and SQLDevelopper query parameters are not the same as bind variables ! An execution plan can change when parameters are used, while it will not change after bind variable usage (it will be computed upon 1st execution and then kept for a while). Thus if the 1st execution leads to a bad execution plan this one can apply for a while.

Indexes

Index wisely !

Limit the number of indexes, otherwise you kill the write performances as each index has to be updated upon update/insert/delete.

It's not possible to increase writer speed with indexes, it's the opposite actual (more indexes to update = slower writes).

You can increase readers speed only.

With small tables (100 rows) it's often more efficient to scan the table than using an index, because of the index b-tree structure which is more adapted to random reads.

Also full table scans are faster than indexes range scans for large amount of data. For this reason when selectivity is bad Oracle prefers going for this access path.

Warning that creating an index can invalidate all the execution plans and compiled queries that target the concerned table, so apply it at the right timing (not at peak time).

Dynamic bitmap index

Since Oracle 10g Oracle is able to dynamically combine separate indexes of a same table into a new (in memory) bitmap index to optimize the OR predicate ("WHERE col1=... OR col2=...").

This can be seen in the execution plan, but there again it depends on the statistics.

Btree indexes vs. bitmap indexes

Btree indexes are more suitable for OLTP as they are more suitable for concurrent updates.

Bitmap indexes are excellent for select but very bad for updates, more suitable for reporting systems or data warehouses, if using it on OLTP it can block everything as every writer will be queued.

Index usage for sorts

Because indexes are sorted, it also means they can be used for sorting (order by), in this case you can see full scan for the index in the execution plan.

This can be unfortunately counter productive as the number of block read will be increased via the index (read the index blocks + access each pointed table block one by one, possibly multiple times) vs table scan (multi blocks read on table blocks + sort in memory).

Indexes efficiency

Actually the limit of the efficiency of the index (where full table scan is better) is when you read as many table blocks as there are rows in the table.

E.g. suppose 5000 records and 50 records per block, so the table is over 100 blocks. If the count of records to retrieve is 100 then it's faster to read the full table using multi blocks rather than going through the index (reading blocks of the index + reading table blocks using random access blocks).

The problem is that even if the table block is in the buffer cache there will still be a cost to access it (CPU cost for instance).

Indexes vs Variables/Literals

When a query retrieves more than 5% of the rows the full table scan is better than the index (story of blocks & rows). This Oracle knowledge is based on the internal statistics about the table data.

Often the case when using:

• The "<>" operator => values for determining the selectivity are not known

• Variables (bind or dynamic query results) => execution plan cannot apply since value is not known before binding or sub-result obtained at query execution based on inputs
  • Eg. WHERE X > ?1 => cannot anticipate usage of index at query compile time

• Typical case of a flag column (value is 'Y' or 'N'):
  • If 50% of the time the value is Y and 50% N, index is useless.
  • Opposite case: 99% of time = Y, 1% = 1% N => index pays off when we query for rows having N values, but only if using literals (using bind variable would fail !).

Warning: execution plan on queries with bind variables is computed once after 1st execution thus it can be based on bad assumption that using the index is better (if 1st value for the bind variable was relevant to the index). Also when selectivity is bad on most cases, then it's counter productive to go through the index even when data is in buffer cache.

Indexes and NULL

NULLs are not stored into index, so any "WHERE IS NULL" or other predicate which does not eliminate NULL values of nullable columns goes for full table scan (even with index hints).

To increase the chances of using the index, we have to use either "WHERE xxx = <not null value>" or "AND xxx IS NOT NULL".

Index on nullable columns is more efficient that way (the index is updated only when not null value is inserted).

There is still a trick to index NULL values, this is to index an additional column which cannot be null, for example if "deptno" is nullbale, then also include the pkey : INDEX(deptno, pkey). Or even INDEX(deptno, <constant expression>).

Access paths

Full table scan

Sometimes scanning a table is faster than going through an index because you do less reads and accesses on blocks.

Both operations have a cost actually:

• Reading a block consumes I/O and CPU.
• Accessing a block consumes CPU, even if the block is loaded into the buffer cache (in memory storage).

This is because of 2 things:

• You need to read and access the index blocks to get the identifiers of the table rows to retrieve.
• After that you need to access the table data for each row given by the index.

There the access will be by direct rowid which is the fastest possible access to a single row (because it directly points to a physical file + block number + row offset).

However when you have many rows to retrieve it is counter productive because 2 optimizations are bypassed when doing direct row access:

• Multi blocks I/Os (read several blocks into one I/O operation) .
• Sequential accesses to table data which are into same block.

Illustration:

• An index gives us 20 result rows and these 20 rows are stored in the same table data block.

• The block will be read once from disk to be put in buffer cache (and in that case we are lucky because it's a single block that we must fetch from disk).

• Then the block in buffer cache will be accessed 20 times (once for each rowid) , while a table scan would have sequentially retrieved all the 20 rows in a single block read+access.

• On top of that we need to add the cost of reading the index blocks.

Common causes are:

• No suitable index

• Low selectivity filter (or no filter), which is often the case with '>' or '<' or '<>' predicates*

• Small table

• High degree of parallelism is available

• Hint FULL is used inside the query

*Note the Oracle internal statistics on each column contain the min and max values of an indexed column (+ sometimes the values distribution inside the column), thus depending on the input value Oracle may choose to do table scan instead of index range scan when it determines it will save I/Os using that method.

Index fast full scan

Standard index access is to go through root then branch then leaf.

Fast full scan is used when access to every value without any order is wanted (e.g. select deptno where deptno is not null).

In that case multi-block I/O on the index can be used and all leaves are read, discarding the root and branches when read.

This is the case where the index is used for the SELECT instead of the WHERE, this technic can be used for improving the access speed on a table: it's faster than scanning the full table as the index is necessarily smaller, but it can be done only when the index contains all the requested data (eg. "select pkey from T").

Index join scan

Combines the results of several index fast full scans using a hash join on rowids.

Applicable only when all the requested columns are indexed, and relevant only when reading these indexes is faster than reading the full table.

Index skip scan

In old versions of Oracle an index would be used only if the 1st column in the query predicate is the same 1st one as in the index. This is not true anymore.

IX1(A, B, C) will be used with:

WHERE A = … and B = … and C > …

IX2(GENDER, AGE) can be used with index skip scan with:

WHERE age between 20 and 29