| |
 |
|
Oracle Concepts - Fixed
Size Areas and Pinned Objects
Oracle Tips by Burleson Consulting |
What to Pin
In all of the guidelines stated so far I
mention that the memory is usually allocated above and beyond that
needed for fixed size areas and pinned objects. How do you determine
what to pin? Generally speaking any package, procedure, function or
cursor that is frequently used by your application should be pinned
into the shared pool when the database is started. I suggest adding a
“null” startup function to every in house generated package it
essentially looks like Source 17.
FUNCTION
start_up RETURN number IS
Ret NUMBER:=1;
BEGIN
Ret:=0
RETURN ret;
END start_up;
Source 17: Example Null Startup Function
The purpose of the null startup function is to
provide a touch point to pull the entire package into the shared pool.
This allows you to create a startup SQL procedure that pulls all of
the application packages into the pool and pins them using the
DBMS_SHARED_POOL package. The DBMS_SHARED_POOL package may have to be
built in earlier releases of Oracle. The DBMS_SHARED_POOL package is
built using the DBMSPOOL.SQL and PRVTPOOL.PLB scripts located in
(UNIX) $ORACLE_HOME/rdbms/admin or (NT) x:\orant\rdbms\admin
(where x: is the home drive for your install).
How do you determine what packages, procedures
of functions to pin? Actually, Oracle has made this easy by providing
the V$DB_OBJECT_CACHE view that shows all objects in the pool, and,
more importantly, how they are being utilized. The script in figure
15.32 provides a list of objects that have been loaded more than once
and have executions greater than one. Some example output from this
script is shown in Source 18. A rule of thumb is that if an object is
being frequently executed and frequently reloaded it should be pinned
into the shared pool.
rem
rem FUNCTION: Report On Objects Which Should Be Kept
rem
COLUMN owner FORMAT A11 HEADING Schema
COLUMN name FORMAT A22 HEADING 'Should Keep|Object Name'
COLUMN HEADING Name|Space
COLUMN type FORMAT A20 HEADING Object|Type
COLUMN kept FORMAT A4 HEADING Kept
COLUMN sharable_mem FORMAT 999,999 HEADING Shared|Memory
COLUMN executions FORMAT 9,999,999 HEADING Executes
SET LINES 132 PAGES 47 FEEDBACK OFF
@title132 'Oracle Should Keep Report'
BREAK ON owner ON namespace ON type
SPOOL rep_out/&db/should_keeps
SELECT
owner,
namespace,
type,
name,
sharable_mem,
loads,
executions,
locks,
pins,
kept
FROM
v$db_object_cache
WHERE
type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE',
'SEQUENCE','INVALID
TYPE')
AND loads>1 AND executions>loads AND
executions>100 AND kept='NO'
ORDER BY owner,namespace,type,executions desc;
SPOOL OFF
SET LINES 80 PAGES 22 FEEDBACK ON
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
Source 18: Script to Show Objects Which Should
Be Kept
The output from the script in Source 18 is
shown in Listing 21. Notice the objects with high executions.
Date:
12/16/99
Page: 1
Time: 12:54 PM
Oracle Should Keep Report
SYSTEM
SBSFAPRD database
Name
Object Should Keep
Shared
Schema Space
Type
Object Name
Memory LOADS Executes
LOCKS PINS Kept
----------- --------------- --------------
------------------------------
SIEBEL TABLE/PROCEDURE PROCEDURE
NGS_EXPORT_PROC
63,310 51
5,471 72
0 NO
TRIGGER
TRIGGER S_OPTY_ESCL_T1
6,733 130 ########
92 0 NO
S_OPTY_POSTN_ESCL_T2
7,035 635 94,678
41 0 NO
S_OPTY_PROD_ESCL_T1
6,466 110 78,297
151 0 NO
S_ACCNT_POSTN_ESCL_T1
6,308 674 40,756
10 0 NO
S_OPTY_POSTN_ESCL_T3
2,971 60
38,236 1
0 NO
S_ACCNT_POSTN_ESCL_T2
6,308 674 36,428
12 0 NO
S_ACCNT_POSTN_ESCL_T3
6,372 220 31,664
4 0 NO
S_OPTY_PROD_ESCL_T2
6,618 167 24,587
14 0 NO
S_OPTY_CON_ESCL_T2
6,273 664 17,926
14 0 NO
S_OPTY_ESCL_T2
6,981 156 16,783
6 0 NO
S_OPTY_POSTN_ESCL_T1
6,707 705 16,306
25 0 NO
S_CONTACT_ESCL_T2
6,064 183 11,077
10 0 NO
S_ORG_EXT_T_ESCL_T2
6,882 467
7,022 8
0 NO
S_ORG_EXT_ESCL_T2
7,368 448
7,021 8
0 NO
S_OPTY_CON_ESCL_T3
6,481 792
3,669 5
0 NO
S_SRV_REQ_ESCL_T1
6,984 678
3,429 6
0 NO
S_OPTY_PROD_ESCL_T3
6,602 420
2,393 2
0 NO
S_ADDR_ORG_ESCL_T2
7,473 203
1,987 9
0 NO
NGS_U_TR
5,743 39
1,515 28
0 NO
S_ORG_EXT_ESCL_T3
6,016 140
443 2
0 NO
S_ORG_EXT_T_ESCL_T3
6,162 81
194 2
0 NO
S_ADDR_ORG_ESCL_T3
6,409 38
114 3
0 NO
S_ORG_INDUST_ESCL_T3
6,331 33
102 2
0 NO
SYS BODY
PACKAGE BODY STANDARD
28,700 11
397,486 170
0 NO
DBMS_APPLICATION_INFO
4,209 40 164,523
2 0 NO
DBMS_OUTPUT
9,399 30 134,819
1 0 NO
UTL_FILE
9,380 53
7,210 73
0 NO
TABLE/PROCEDURE PACKAGE
STANDARD
120,844 107 397,486
170 0 NO
DBMS_APPLICATION_INFO
12,161 278 173,486
2 0 NO
DBMS_STANDARD
15,097 109 161,973
234 0 NO
DBMS_OUTPUT
14,615 148 100,434
1 0 NO
UTL_FILE
3,596 82
7,210 73
0 NO
Listing 21. Example Output From the Script In
Source 18.
Note that you only have to pin the package,
not the package and package body.
Another criteria for determining if an object
should be pinned into the shared pool is its size. The
DBMS_SHARED_POOL.SIZES procedure searches the shared pool for any
objects larger than the size in kilobytes of the argument it is
passed. Generally, the larger the size the more likelihood that the
object is a package and you will want to keep it in the pool, smaller
objects tend to be individual queries and can be aged out of the pool.
Remember that the DBMS_SHARED_POOL procedure is not generally
automatically loaded when an instance is built, the DBMSPOOL.SQL and
PRVTPOOL.PLB scripts must be run from INTERNAL or SYS users for it to
be created. The use of DBMS_SHARED_POOL.SIZES is shown in Listing 22.
SQL> set
serveroutput on size 4000;
SQL> execute sys.dbms_shared_pool.sizes(10);
SIZE(K) KEPT NAME
------- ------
---------------------------------------------------------------
139 SYS.STANDARD
(PACKAGE)
56 SYS.DBMS_SHARED_POOL
(PACKAGE BODY)
31 SELECT
TO_CHAR(SHARABLE_MEM / 1000 ,'999999') SZ,DECODE(KEPT_VE
RSIONS,0,'
',RPAD('YES(' || TO_CHAR(KEPT_VERSIONS) |
| ')' ,6))
KEEPED,RAWTOHEX(ADDRESS) || ',' || TO_CHAR(HASH
_VALUE)
NAME,SUBSTR(SQL_TEXT,1,354) EXTRA FROM V$SQLAREA
WHERE
SHARABLE_MEM > :b1 * 1000 UNION SELECT TO_CHAR(SH
ARABLE_MEM / 1000 ,'999999') SZ,DECODE(KEPT,'YES','YES
(004D7F84,2008220828) (CURSOR)
30 SYS.STANDARD
(PACKAGE BODY)
27 SYS.DBMS_SHARED_POOL
(PACKAGE)
17 SYS.V$SQLAREA
(VIEW)
16 SYS.V$DB_OBJECT_CACHE
(VIEW)
15 insert into
idl_ub2$(obj#,part,version,piece#,length,piece) val
ues(:1,:2,:3,:4,:5,:6)
(0027BA44,-512326869) (CURSOR)
PL/SQL procedure successfully completed.
Listing 22. Example of the use of the
DBMS_SHARED_POOL.SIZES procedure
The ‘set serveroutput on size 4000’ command in
Listing 22 limits the size of the output buffer to 4000 bytes. The
‘set serveroutput’ command is required. Perhaps in the future if we
all bug Oracle for an enhancement they will incorporate the use of
UTIL_FILE and just generate us a report listing 21 we can review as we
desire. As you can see from Listing 22, there is one large package in
shared memory. Let’s issue a keep against this package to retain it.
Listing 23 shows the results from this action.
SQL> execute
dbms_shared_pool.keep('sys.standard');
PL/SQL procedure successfully completed.
SQL> execute dbms_shared_pool.sizes(130);
SIZE(K) KEPT NAME
------- ------ -----------------------------------------------------
139 YES SYS.STANDARD
(PACKAGE)
PL/SQL procedure successfully completed.
Listing 23: Example of the use of
DBMS_SHARED_POOL.KEEP packaged procedure.
By issuing keeps against large packages to
hold them in memory you can mitigate shared pool fragmentation that
results in the ORA-04031 error. By pinning the packages so they don’t
age out this prevents smaller queries, cursors and procedures from
taking their areas and then when the packages are reloaded, viola! an
ORA-04031 as the package seeks a large enough group of areas in which
to install itself. Under ORACLE8 this is supposed to be eliminated due
to the way the shared memory area is now used, however I have had some
reports on as late a version as 8.0.5.
Guideline 4: Determine usage patterns of
packages, procedures, functions and cursors and pin those that are
frequently used.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher
here.
|
