Examine the Exhibit and view the structure of an indexes for the EMPLOYEES table.
Which two actions might improve the performance of the query?
A.
Use the ALL_ROWS hint in the query.
B.
Collect the histogram statistics for the EMPLOYEE_ID column.
C.
Decrease the value for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
D.
Decrease the index on the EMPLOYEE_ID if not being used.
E.
Set the OPTIMIZER_MODE parameter to ALL_ROWS.
Explanation:
A: The ALL_ROWS hint instructs the optimizer to optimize a statement block with a
goal of best throughput, which is minimum total resource consumption.
E: optimizer_mode=all_rows – This optimizer mode favors full-table scans (especially parallel fulltable-scans) in cases where the server resources will be minimized. The all_rows mode is
generally used during batch-oriented processing and for data warehouses where the goal is to
minimize server resource consumption.
For me C and D seems correct.
For single row we have high value of disk read.
The full table scan occured (instead of index unique scan) which points to high value of DB_FILE_MULTIBLOCK_READ_COUNT or index is required shrinking space.
For me B and C are correct.
We have unique index on EMPLOYEE_ID column, so we can expect that index EMP_EMP_ID_PK will be used. When we look at number of read blokc we can assume that index wasn’t used, so it means that we have to do something to use this index.
In my opinion gathering histograms and decreasing DB_FILE_MUTLIBLOCK_READ_COUNT should help to achieve it.
Decreasing DB_FILE_MUTLIBLOCK_READ_COUNT will cause that FTS will be less preferred
B is not so good, because EMPLOYEE_ID column is primary key – no skew data, but if we have bad statistics is not so bad