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
Page 648