Viewing Library Cache Statistics - Exercise

Objective: Interpret the output from a library cache listing by analyzing library cache statistics

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 your answer.

Background

You have been getting complaints of poor response times on your Oracle database. The external factors (CPU, memory, disk I/O) are low, and you data buffer hit ratio looks fine. You suspect that you may have a problem with contention in the shared pool. You first display the shared pool parameters in Server Manager, and then run the library cache script. You have obtained the following output:
Do not try to read too much into this output. The data is very straightforward.

SQL*Plus> connect internal;
Connected.
SQL*Plus> show parameters pool
NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
buffer_pool_keep                    string  buffers:50, lru_latches:1     
buffer_pool_recycle                 string  buffers:50, lru_latches:1     
large_pool_min_alloc                string  16K                           
large_pool_size                     string  0                             
parallel_min_message_pool           integer 48330                         
shared_pool_reserved_min_alloc      string  5K                            
shared_pool_reserved_size           string  300000                        
shared_pool_size                    string  6000000
SQL>@library
  executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------ ---------------------------- ------------------------
     127,630                       63,815                    .5000
=========================
Library Cache Section
=========================
NAMESPACE        Hit ratio pin hit ratio      reloads
--------------- ---------- ------------- ------------
SQL AREA                44            56         1309
TABLE/PROCEDURE         60            66          611
BODY                    97            97            0
TRIGGER                100           100            0
INDEX                    0             0            0
CLUSTER                 47            20            0
OBJECT                 100           100            0
PIPE                    99            99            0
8 rows selected.

Instructions

Perform a cursory analysis of this information and suggest a possible cause and remedy for the slow performance.

Submitting your exercise

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