| Previous | Table of Contents | Next |
This means that a large procedure that initially fits into memory may not fit into contiguous memory when it’s reloaded after paging out. Consider a problem that occurs when the body of a package has been paged out of the instance’s SGA because of other more recent/frequent activity. Fragmentation occurs, and the server cannot find enough contiguous memory to reload the package body, resulting in an ORA-4031 error.
To prevent paging, packages can be marked as nonswappable, which tells the database that after their initial load they must always remain in memory. This is called pinning or memory fencing. Oracle provides a procedure called dbms_shared_pool.keep to pin a package. Packages can be unpinned with dbms_shared_pool.unkeep.
NOTE: Only packages can be pinned. Stored procedures cannot be pinned unless they are placed into a package.
The choice of whether to “pin” a package in memory is a function of the size of the object and the frequency of its use. Very large packages that are called frequently might benefit from pinning, but any difference may go unnoticed because the frequent calls to the procedure have kept it loaded into memory. Therefore, since the object never pages out, the pinning has no effect. Also, the way that procedures are grouped into packages may have some influence. Some Oracle DBAs identify high-impact procedures and group them into a single package, which is pinned in the library cache.
In an ideal world, the shared_pool parameter of the init.ora should be large enough to accept every package, stored procedure, and trigger that may be used by the applications. However, reality dictates that the shared pool cannot grow indefinitely, and wise choices must be made in terms of which packages are pinned.
Because of their frequent usage, Oracle recommends that the standard, dbms_standard, dbms_utility, dbms_describe, and dbms_output packages always be pinned in the shared pool. The following snippet demonstrates how a stored procedure called sys.standard can be pinned:
CONNECT INTERNAL;
@/usr/oracle/rdbms/admin/dbmspool.sql
EXECUTE dbms_shared_pool.keep('sys.standard');
A standard procedure can be written to pin all of the recommended Oracle packages into the shared pool. Here is the script:
EXECUTE dbms_shared_pool.keep('DBMS_ALERT');
EXECUTE dbms_shared_pool.keep('DBMS_DDL');
EXECUTE dbms_shared_pool.keep('DBMS_DESCRIBE');
EXECUTE dbms_shared_pool.keep('DBMS_LOCK');
EXECUTE dbms_shared_pool.keep('DBMS_OUTPUT');
EXECUTE dbms_shared_pool.keep('DBMS_PIPE');
EXECUTE dbms_shared_pool.keep('DBMS_SESSION');
EXECUTE dbms_shared_pool.keep('DBMS_SHARED_POOL');
EXECUTE dbms_shared_pool.keep('DBMS_STANDARD');
EXECUTE dbms_shared_pool.keep('DBMS_UTILITY');
EXECUTE dbms_shared_pool.keep('STANDARD');
Automatic Re-Pinning Of Packages
Unix users may want to add code to the /etc/rc file to ensure that the packages are re-pinned after each database startup, which guarantees that all packages are re-pinned with each bounce of the box. A script might look like this:
[root]: more pin ORACLE_SID=mydata export ORACLE_SID su oracle -c "/usr/oracle/bin/svrmgrl /<<! connect internal; select * from db; @/usr/local/dba/sql/pin.sql exit; !"
The Database Administrator also needs to remember to run pin.sql whenever he or she must restart a database. This is done by reissuing the pin command from inside SQL*DBA immediately after the database has been restarted.
How To Measure Pinned Packages
Listing 2.2 shows a handy script to look at pinned packages in the SGA.
Listing 2.2 Looking at pinned packages in the SGA.
memory.sql - Display used SGA memory for triggers, packages, & procedures
SET PAGESIZE 60;
COLUMN EXECUTIONS FORMAT 999,999,999;
COLUMN Mem_used FORMAT 999,999,999;
SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,20) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE TYPE IN ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY EXECUTIONS DESC;
Listing 2.3 shows the output of MEMORY.SQL.
Listing 2.3 The output of MEMORY.SQL.
SQL> @memory OWNER TYPE NAME EXECUTIONS MEM_USED KEPT ---- ------ ------ ---------- -------- ---- SYS PACKAGE STANDARD 867,600 151,963 YES SYS PACKAGE BODY STANDARD 867,275 30,739 YES SYS PACKAGE DBMS_ALERT 502,126 3,637 NO SYS PACKAGE BODY DBMS_ALERT 433,607 20,389 NO SYS PACKAGE DBMS_LOCK 432,137 3,140 YES SYS PACKAGE BODY DBMS_LOCK 432,137 10,780 YES SYS PACKAGE DBMS_PIPE 397,466 3,412 NO SYS PACKAGE BODY DBMS_PIPE 397,466 5,292 NO HRIS PACKAGE S125_PACKAGE 285,700 3,776 NO SYS PACKAGE DBMS_UTILITY 284,694 3,311 NO SYS PACKAGE BODY DBMS_UTILITY 284,694 6,159 NO HRIS PACKAGE HRS_COMMON_PACKAGE 258,657 3,382 NO HRIS PACKAGE BODY S125_PACKAGE 248,857 30,928 NO HRIS PACKAGE BODY HRS_COMMON_PACKAGE 242,155 8,638 NO HRIS PACKAGE GTS_SNAPSHOT_UTILITY 168,978 11,056 NO HRIS PACKAGE BODY GTS_SNAPSHOT_UTILITY 89,623 3,232 NO SYS PACKAGE DBMS_STANDARD 18,953 14,696 NO SYS PACKAGE BODY DBMS_STANDARD 18,872 3,432 NO KIS PROCEDURE RKA_INSERT 7,067 4,949 NO HRIS PACKAGE HRS_PACKAGE 5,175 3,831 NO HRIS PACKAGE BODY HRS_PACKAGE 5,157 36,455 NO SYS PACKAGE DBMS_DESCRIBE 718 12,800 NO HRIS PROCEDURE CHECK_APP_ALERT 683 3,763 NO SYS PACKAGE BODY DBMS_DESCRIBE 350 9,880 NO SYS PACKAGE DBMS_SESSION 234 3,351 NO SYS PACKAGE BODY DBMS_SESSION 65 4,543 NO GIANT PROCEDURE CREATE_SESSION_RECOR 62 7,147 NO HRIS PROCEDURE INIT_APP_ALERT 6 10,802 NO
| Previous | Table of Contents | Next |