What do you recommend for better utilization of the result cache?

The result cache is enabled for the database instance.
Examine the following code for a PL/SQL function:
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
RESULT_CACHE RELIES_ON (HR.EMPLOYEES)
IS
date_hired DATE;
BEGIN
SELECT hire_date INTO date_hired
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
RETURN TO_CHAR(date_hired);
END;
You notice that results for the functions are not used effectively. What do you recommend for
better utilization of the result cache? (Choose all that apply.)

The result cache is enabled for the database instance.
Examine the following code for a PL/SQL function:
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
RESULT_CACHE RELIES_ON (HR.EMPLOYEES)
IS
date_hired DATE;
BEGIN
SELECT hire_date INTO date_hired
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
RETURN TO_CHAR(date_hired);
END;
You notice that results for the functions are not used effectively. What do you recommend for
better utilization of the result cache? (Choose all that apply.)

A.
Set the RESULT_CACHE_MODE parameter to FORCE.

B.
Increase the value for the RESULT_CACHE_MAX_SIZE parameter.

C.
Add a format mask parameter, such as RETURN TO_CHAR(date_hired, fmt) to
GET_HIRE_DATE.

D.
Change the return type of GET_HIRE_DATE to DATE and have each session invoke the
TO_CHAR function.

Show Hint

Leave a Reply 5

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

gelete

gelete

C, D

The function get_hire_date does not specify a format mask, so the format mask defaults to the one that NLS_DATE_FORMAT specifies.

If sessions that call get_hire_date have different NLS_DATE_FORMAT settings, cached results can have different formats.

If a session gets a cached result whose format differs from its own format, that result will probably be incorrect.

Some possible solutions to this problem are:

– Change the return type of get_hire_date to DATE and have each session invoke the TO_CHAR function.

– If a common format is acceptable to all sessions, specify a format mask, removing the dependency on NLS_DATE_FORMAT.
For example: TO_CHAR(date_hired, ‘mm/dd/yy’);

– Add a format mask parameter to get_hire_date. For example:

CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER, fmt VARCHAR) RETURN VARCHAR
RESULT_CACHE RELIES_ON (HR.EMPLOYEES)
IS
…
RETURN TO_CHAR(date_hired, fmt);

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#g3336053

Reply