Examine the initializing parameters: 
An index exists on the column used in the WHERE of a query. You execute the query for the first
time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows.
View the exhibit and examine the query and its execution plan.
What can be the two reasons for full table scan?
A.
The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.
B.
The blocks fetched by the query are greater than the value specified by the
DB_FILE_MULTIBLOCK_READ_COUNT parameter.
C.
The statistics for the CUSTOMERS table and the indexes stale.
D.
The OPTIMIZER_MODE parameter is set to ALL_ROWS.
E.
Histogram statistics for CUST_CITY_ID are missing.
F.
Average number of rows per block for the CUSTOMERS table is low.
Explanation:
C: Old statistics could cause this problem.
D:Histograms are feature in CBO and it helps to optimizer to determine how data are
skewed(distributed) with in the column. Histogram is good to create for the column which are
included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer
to decide whether to use an index or full-table scan or help the optimizer determine the fastest
table join order.
Incorrect:
A: 100 is the maximum value of OPTIMIZER_INDEX_COST_ADJ
Note: 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.
B: DB_FILE_MULTIBLOCK_READ_COUNT does not apply: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.
F: High (not low) row per block could make a table scan preferable.
The query returns 437 out of 55000 rows. Less than 1%.
The data is skewed.
Histogram is missing, or stats are stale or missing.
C and E.
I agree with vasya.
A is not correct OPTIMIZER_INDEX_COST_ADJ with a low value favors index use.
B is not correct If number of block fetched was lower than DB_FILE_MULTIBLOCK_READ_COUNT, a full table scan could be prefer because it’ll generate only 1 I/O. But the inverse is not true.
C is correct
D is not correct. ALL_ROWS hint can influence optimizer to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). However if an index is performant (it seems to be the case here), the optimizer should choose index instead of full table scan even with optimizer mode = ‘ALL_ROWS’
E is correct.
F. is not correct.
Errata it’s seems that D is correct. I’ve execute this example by setting the optimizer_mode to ‘FIRST_ROWS’ and the optimizer choose now an index full scan
SQL> select /*+ FIRST_ROWS */ * from sh.customers where cust_city_id=51166;
331 ligne(s) sÚlectionnÚe(s).
Plan d’exÚcution
———————————————————-
Plan hash value: 237813812
—————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————–
| 0 | SELECT STATEMENT | | 437 | 79097 | 2367 (1)| 00:00:29 |
|* 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 437 | 79097 | 2367 (1)| 00:00:29 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP INDEX FULL SCAN | CUSTOMERS_GENDER_BIX | | | | |
—————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“CUST_CITY_ID”=51166)
Statistiques
———————————————————-
1 recursive calls
0 db block gets
2251 consistent gets
0 physical reads
0 redo size
41736 bytes sent via SQL*Net to client
661 bytes received via SQL*Net from client
24 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
331 rows processed
SQL>
It’s not a reason :). When optimizer can’t evaluate that number of returning rows is small, but you know this you can use FIRST_ROWS hint. But if you set parameter to first rows optimizer will choose plans with nested loops and with index access ALWAYS!!!!, because they give first row quicker. Even Profile can be with first_rows hint. In 8i it was one of solutions for bad plan. If your application don’t have pause between fetching first and all next portions of rows – no reason to use FIRST_ROWS for parameter in session or in instance level.