Remote DBA

Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2)

Oracle Tips by Mike Ault

What to Pin

The output from the script in Figure 2 is shown in Figure 3. Notice the objects with high executions.

Date: 11/20/98 Page: 1
Time: 09:59 AM Oracle Objects Report AULTM

AGCD database

Name Object Object Shared

Schema Space Type Name Memory LOADS Executes LOCKS PINS Kept

------ --------------- -------------- ---------------------------- -------- --------- -------- --------- --------- ----

SYS BODY PACKAGE BODY DBMS_EXPORT_EXTENSION 6,957 1 1,338 1 0 NO

DBMS_SQL 11,016 1 50 1 0 NO

DBMS_SYS_SQL 21,428 1 50 1 0 NO

DBMS_DEFER_IMPORT_INTERNAL 4,070 1 50 1 0 NO

STANDARD 26,796 1 50 1 0 NO

DBMS_APPLICATION_INFO 4,585 1 8 1 0 NO

DBMS_OUTPUT 8,799 1 1 1 0 NO

TABLE/PROCEDURE PACKAGE DBMS_EXPORT_EXTENSION 12,269 1 1,355 1 0 NO

DBMS_DEFER_IMPORT_INTERNAL 10,662 1 51 1 0 NO

DBMS_SQL 6,960 1 50 1 0 NO

STANDARD 118,556 1 50 1 0 NO

DBMS_SYS_SQL 7,472 1 50 1 0 NO

DBMS_APPLICATION_INFO 11,569 1 9 1 0 NO

DBMS_OUTPUT 13,391 1 1 1 0 NO

Figure 3: Example Output From the Script In Figure 2.

Unfortunately in my active instance I already have the objects pinned that are required, but the example report in Figure 3 taken from one of my less active instances still shows the concept. Note that you only have to pin the package, not the package and package body.

Guideline 4: Determine usage patterns of packages, procedures, functions and cursors and pin those that are frequently used.

SEE CODE DEPOT FOR FULL SCRIPTS