New Features for Administrators - Question 021

The CURSOR_SHARING parameter is set to SIMILAR at the instance level and you issue these SELECT statements in the order shown below:

SELECT * FROM employees WHERE department _id=50;

SELECT * FROM employees WHERE department_id=70;

There is an index on the DEPARTMENT ID column of the 1,000,000 row EMPLOYEES table. Ninety percent of the employees are part of DEPARTMENT_ID 50.

In which two scenarios will Oracle9i share the corresponding cursors for the two statements? (Choose two.)

A. You are using the rule-based optimizer.

B. You are using the cost-based optimizer with up-to-date statistics but with no histograms.

C. You are using the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column.

D. You are using the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column and system statistics gathered.

Answer: A, B

Explanation:

Oracle9i will share the corresponding cursors for the two statements if you are using the rule-based optimizer or the cost-based optimizer with up-to-date statistics but with no histograms.

Incorrect Answers

C: Oracle9i will not share the corresponding cursors if you are using the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column.

D: The corresponded cursors will not be shared by Oracle if you use the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column and system statistics gathered.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 57-59

Chapter 2: Oracle9i Architecture Changes