Speed Up execution of APEX packages – PINNING!

July 16, 2008  |  apex, dba

One of the most important parts of tuning a stressed Oracle database is to increase performance of business logic code. Lot’s applications in nowadays use RDBMS not only for data storage but also for business logic processing. For instance, at Neoface we always try to put every data manipulation code at the database side by coding triggers, packages, procedures and functions. Indeed we use database for almost every processes we can rely on it.

APEX uses Oracle’s procedural language – PL/SQL extensively… Version 3 comes with more than 180 packages each one with lots of procedures, functions, variables and SQL statements. When a procedure in a package is called entire package is loaded into the shared pool memory, though it happens to be expensive first time the response is faster for subsequent calls.

If the packages beeing loaded to memory are large this may be problematic. Loading a package into memory causes a delay and if the shared pool memory is full, several smaller packages and other objects may be get rid of shared pool.

One technique to prevent paged out packages is to set some packages as non-swappable, telling the database that after their first load they should remain in memory. This process is called PINNING. To pin a package we should use dbms_shared_pool.keep. We can also unpin packages by using dbms_shared_pool.unkeep.

What packages to PIN?

This depends on how good or bad your database performs. Memory is an important part of it and sizing SGA is an important part of it. A rule of thumb is that if an object is being frequently executed and reloaded, it should be pinned into the shared pool memory.

So, let’s run a script to show top 10 LOADED packages and top 10 EXECUTED packages. In this sample i’ll only check APEX packages, so this can be seen as APEX tuning 😉

-- TOP 10 APEX reloaded packages
SELECT *
FROM (SELECT   SUBSTR (owner, 1, 20) owner, SUBSTR (NAME, 1, 20) NAME, loads, executions, pins, kept
FROM v$db_object_cache
WHERE NAME IN (SELECT object_name
FROM all_objects
WHERE owner LIKE '%FLOW%' AND object_type = 'PACKAGE' )
ORDER BY loads DESC)
WHERE ROWNUM <= 10;

-- TOP 10 APEX executed packages
SELECT *
FROM (SELECT   SUBSTR (owner, 1, 20) owner, SUBSTR (NAME, 1, 20) NAME, loads, executions, pins, kept
FROM v$db_object_cache
WHERE NAME IN (SELECT object_name
FROM all_objects
WHERE owner LIKE '%FLOW%' AND object_type = 'PACKAGE' )
ORDER BY executions DESC)
WHERE ROWNUM <= 10;

If the result of this scripts shows packages that are at the same time in the TOP 10 executed and TOP 10 Loaded, then… my recommendation is to PIN those packages to memory!

For instance, in my non-stressed database, the result shows that WWV_FLOW_LANG appears at the same time in the top 10 executed and top 10 loaded packages. Of course, this isn’t a production database and results would vary a lot if it were a production database.

So, to keep a package in the shared pool you just need to do this:

BEGIN
DBMS_SHARED_POOL.KEEP ('WWV_FLOW_LANG');
END;

You can also create a trigger to PIN your most used packages at database startup. To do so, since 8i, you can do it with a database startup trigger. I usually PIN Oracle standard packages at database startup. In this script i also added the WWV_FLOW_LANG package.

CREATE OR REPLACE TRIGGER pin_packages
AFTER STARTUP ON DATABASE
BEGIN
DBMS_SHARED_POOL.KEEP ('STANDARD');
DBMS_SHARED_POOL.KEEP ('DBMS_ALERT');
DBMS_SHARED_POOL.KEEP ('DBMS_DDL');
DBMS_SHARED_POOL.KEEP ('DBMS_DESCRIBE');
DBMS_SHARED_POOL.KEEP ('DBMS_LOCK');
DBMS_SHARED_POOL.KEEP ('DBMS_OUTPUT');
DBMS_SHARED_POOL.KEEP ('DBMS_PIPE');
DBMS_SHARED_POOL.KEEP ('DBMS_SESSION');
DBMS_SHARED_POOL.KEEP ('DBMS_SHARED_POOL');
DBMS_SHARED_POOL.KEEP ('DBMS_UTILITY');
DBMS_SHARED_POOL.KEEP ('WWV_FLOW_LANG');
END;

So with this trigger, next time your database startup it will automatically PIN all packages that are in this trigger, and you don’t have to worry about pinning everytime database starts.

I hope it helps, despite some people advocates that Pinning objects into memory is not crucial for database performance… 🙂

Cheers,
João


Leave a Reply