Previous | Table of Contents | Next

Page 646

By using bind variables, both the previous selects in the first case can share the same select, which is stored in the library cache, thus reducing unnecessary parsing and reducing load on the latch. Using bind variables prevents multiple copies of the same select from being formed in the shared pool.

Parsing can be reduced in the shared pool or by pinning frequently used objects such as procedures and packages. The advantage of pinning these objects is that these will never be flushed out of the shared pool and subsequently the parse time is reduced. Objects that are used very frequently can be identified by the following query:

Select name ,executions

From v$db_object_cache

Where executions > <threshold limit>;


order by 2 desc;

These high-execution objects can be pinned in the shared pool using dbms_share_pool.keep(`object_name','P');.

To check the objects in the shared pool that are not pinned, execute the following query:

Select name,type,kept,sharable_mem
From v$db_object_cache
Where kept = `NO'
Order by sharable_mem desc;

Fragmentation of the shared pool can also cause high demand for these latches. A fragmented shared pool means the net free memory available in the shared pool may be large, but the total contiguous memory available may not be the same. Therefore, when an object like a large
PL/SQL object with a very high memory requirement is to be located in the shared pool, it will cause a lot of smaller chunks of allocated memory areas to be flushed. These would then have to be parsed again when a user makes the request for the same SQL again. The primary cause of fragmentation is large PL/SQL objects. Fragmentation can be avoided by increasing sharing among SQL statements by use of bind variables and pinning of large PL/SQL objects using the techniques mentioned previously. l

Page 647

PART VII

Parallel and Distributed Environments

  1. Parallel Query Management
  2. Parallel Server Management
  3. Distributed Database Management

Page 648

Previous | Table of Contents | Next