You work for a company as a DBA. The company has an application to manage the details of its business and customer base. However, application users complain that updating the SALES record takes more time than it used to earlier. On investigation, you notice that the application performance degrades when a call is made to the UPDATE_INV procedure. Further, you execute the following query to investigate:
SQL> SELECT name, type, sharable_mem, kept
2 FROM v$db_object_cache
3 WHERE sharable_mem > 4000
4 AND EXECUTIONS > 5
5 AND (type=’FUNCTION’ OR type=’PROCEDURE’)
6 /
NAME TYPE SHARABLE_MEM KEPT
——————- ————– ————— ———–
GETEMKEY FUNCTION 13695 YES
UPDATE_INV PROCEDURE 14766 NO
SETEMUSERCONTEXT PROCEDURE 13703 YES
DECRYPT FUNCTION 17790 YES
UPDATE_DEPT PROCEDURE 18765 NO
What would you do to improve the performance?
A.
Enable the result cache if not already enabled.
B.
Flush the shared pool to make space for this procedure.
C.
Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.
D.
Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.
E.
Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.