ReUse SQL library cache - Exercise

Re-use of SQL in the Library Cache

Objective: Identify potential library cache problems and make tuning recommendations.

Exercise scoring

You will receive 10 points for this exercise. To receive full credit, you will need to respond to the scenario presented below using what you now know about Oracle locks. Once you have completed your answer, you will submit it.

Instructions

You have been told that your database is experiencing poor performance and you have determined that memory fragmentation within the library cache may be responsible. You have run memory.sql and seen the output below. Based upon this output, make the appropriate tuning recommendations.

Mon Aug 23                                                             page    1
                                dbname Database
                              Shared Pool Objects
OWNER      TYPE         NAME                   EXECUTIONS     MEM_USED Kept
---------- ------------ -------------------- ------------ ------------ ----
MASTER     TRIGGER      INSERT_LOCATION            14,783      215,447 NO
MASTER     TRIGGER      INSERT_TIME                14,592      215,615 NO
SYS        PACKAGE      DBMS_STANDARD               9,417       14,661 YES
MASTER     PACKAGE      INSERT_PKG                  9,154      108,875 YES
MASTER     PACKAGE BODY MANAGER                     9,123       55,159 NO
SYS        PACKAGE      DBMS_APPLICATION_INF        6,195       12,045 YES
SYS        PACKAGE BODY DBMS_APPLICATION_INF        4,310        4,389 YES
SYS        PACKAGE      DBMS_OUTPUT                 3,617       15,219 YES
SYS        PACKAGE      STANDARD                    3,549      119,300 NO
SYS        PACKAGE BODY STANDARD                    3,549       26,880 NO
SYS        PACKAGE      DBMS_SQL                    2,772        6,760 NO
SYS        PACKAGE      DBMS_SYS_SQL                2,772        7,460 NO
SYS        PACKAGE BODY DBMS_SYS_SQL                2,772       21,480 NO
SYS        PACKAGE BODY DBMS_SQL                    2,772       11,168 NO
SYS        PACKAGE BODY DBMS_OUTPUT                 1,824        9,043 YES
MASTER     TRIGGER      OVERT                         806      226,483 NO

Submitting your exercise

Type your query in the text box below. Click the Submit button to submit the exercise.