What are two possible reasons for the optimizer to use full table scans instead of index unique scans and index range scans?

Examine the utilization parameters for an instance:

You notice that despite having an index on the column used in the where clause, queries use full
table scans with highly selective filters.
What are two possible reasons for the optimizer to use full table scans instead of index unique
scans and index range scans?

Examine the utilization parameters for an instance:

You notice that despite having an index on the column used in the where clause, queries use full
table scans with highly selective filters.
What are two possible reasons for the optimizer to use full table scans instead of index unique
scans and index range scans?

A.
The OPTIMIZER_MODE parameter is set to ALL_ROWS.

B.
The clustering factor for the indexes is high.

C.
The number of leaf blocks for the indexes is high.

D.
The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to 100.

E.
The blocks fetched by the query are greater than the value specified by the
DB_FILE_MULTIBLOCK_READ_COUNT parameter.

Explanation:
D: OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access

path selection to be more or less index friendly—that is, to make the optimizer more or less prone
to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths
at the regular cost. Any other value makes the optimizer evaluate the access path at that
percentage of the regular cost. For example, a setting of 50 makes the index access path look half
as expensive as normal.
E: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O
during table scans. It specifies the maximum number of blocks read in one I/O operation during a
sequential scan. The total number of I/Os needed to perform a full table scan depends on such
factors as the size of the table, the multiblock read count, and whether parallel execution is being
utilized for the operation.
As of Oracle Database 10g release 2, the default value of this parameter is a value that
corresponds to the maximum I/O size that can be performed efficiently. This value is platformdependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be
set to a value that is equal to the maximum I/O size that can be performed efficiently divided by
the standard block size. Note that if the number of sessions is extremely large the multiblock read
count value is decreased to avoid the buffer cache getting flooded with too many table scan
buffers.
Even though the default value may be a large value, the optimizer will not favor large plans if you
do not set this parameter. It would do so only if you explicitly set this parameter to a large value.
Online transaction processing (OLTP) and batch environments typically have values in the range
of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from
maximizing the value of this parameter. The optimizer is more likely to choose a full table scan
over an index if the value of this parameter is high.
Note:
* OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for
the instance.
Values:
first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to
return the first n rows (where n = 1, 10, 100, 1000).
first_rows

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few
rows.
all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes
with a goal of best throughput (minimum resource use to complete the entire statement).



Leave a Reply 7

Your email address will not be published. Required fields are marked *


raka

raka

For me A and B are correct.

A: because ALL_ROWS tends to favorite Full-table scan

B: Low clustering factor indicates that the order of table block is not nearly the same as index block. So the number of I/O when use index scan is near the number of block in the table. So high clustering factor indicate that block are scattered and not in the same order than index block. So the number of I/O when use index scan is near the number of rows in the table. In this case, a full table scan could be prefered by the optimizer

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html

PS

PS

yes, agreed with Raka, A&B are correct

raka

raka

I’m not sure for A. Despite of setting optimizer mode to ALL_ROWS, i’ve note that index should be use if filter are highly selective. See this example:

SQL> EXPLAIN PLAN FOR SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
2 FROM hr.employees
3 WHERE employee_id = 7566;

ExplicitÚ.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————
—–
Plan hash value: 1833546154

———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – access(“EMPLOYEE_ID”=7566)

14 ligne(s) sÚlectionnÚe(s).

raka

raka

I think that B and C are correct.