Learn Dynamic Performance Table 12th-V $ db_object_cache

Learning Dynamic Performance Table

The12Article-- V $ db_object_cache

This view provides object statistics in the library cache (shared pool), provides more details than V $ librarycache, and is often used to find activity objects in the shared pool.

V $ db_object_cacheCommon columns in:

L owner: Object owner

L name: Object Name

L type: object type (for example, sequence, procedure, function, package, package body, trigger)

L kept: indicates whether the object is resident in the Shared Pool (Yes/No). It depends on whether the object has been "maintained" by the PL/SQL process dbms_shared_pool.keep (permanently fixed in the memory)

L sharable_mem: shared memory usage

L pins: number of sessions of the current execution object

L locks: number of sessions of the currently locked object

Instant Status column:

The following columns keep the statistical information of the object since the first loading:

L loads: Number of times objects are loaded.

Example:

1. Shared Pool execution and total memory usage

The following query shows the Shared Pool memory for objects of different categories.

It also shows whether an object is resident in the shared pool through the dbms_shared_pool.keep () process.

Selecttype, kept, count (*), sum (sharable_mem)

From v $ db_object_cache

Groupbytype, kept;

2. Find objects by loading times

Select owner, name sharable_mem, kept, Loads

From v $ db_object_cache

Where loads> 1 orderby loads DESC;

3. Find out the objects in which the memory used exceeds 10 MB and are not resident memory.

Select owner, name, sharable_mem, kept

From v $ db_object_cache

Where sharable_mem> 102400and kept = 'no'

Orderby sharable_mem DESC;