Author: SCASTLED
Subject: DBMSPOOL.SQL Script for pinning by hex address
Modified: 19 Jul 95 03:46:56
Revised DBMSPOOL.SQL for pinning by hex address
-----------------------------------------------
rem
rem $Header: dbmspool.sql,v 1.1.312.1 93/11/05 17:39:50 ajasuja: Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem dbmspool.sql - dbms_shared_pool utility package.
Rem DESCRIPTION
Rem This package allows you to display the sizes of objects in the
Rem shared pool, and mark them for keeping or unkeeping in order to
Rem reduce memory fragmentation.
Rem RETURNS
Rem
Rem NOTES
Rem MODIFIED (MM/DD/YY)
Rem ajasuja 11/05/93 - handle UNIX addresses
Rem rkooi 12/08/92 - Creation
create or replace package dbms_shared_pool is
------------
-- OVERVIEW
--
-- This package provides access to the shared pool. This is the
-- shared memory area where cursors and PL/SQL objects are stored.
----------------------------
-- PROCEDURES AND FUNCTIONS
--
procedure sizes(minsize number);
-- Show objects in the shared_pool that are larger than the specified
-- size. The name of the object is also given which can be used as
-- an argument to either the 'keep' or 'unkeep' calls below. You should
-- issue the SQLDBA or SQLPLUS 'set serveroutput on size xxxxx'
-- command prior to using this procedure so that the results will
-- be displayed.
-- Input arguments:
-- minsize
-- Size, in kilobytes, over which an object must be occupying in the
-- shared pool, in order for it to be displayed.
procedure keep(name varchar2, flag char DEFAULT 'P');
-- Keep an object in the shared pool. Once an object has been keeped in
-- the shared pool, it is not subject to aging out of the pool. This
-- may be useful for certain semi-frequently used large objects since
-- when large objects are brought into the shared pool, a larger
-- number of other objects (much more than the size of the object
-- being brought in, may need to be aged out in order to create a
-- contiguous area large enough.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
-- Input arguments:
-- name
-- The name of the object to keep. There are two types of objects:
-- PL/SQL objects which are specified by name, and SQL cursor
-- objects which are specified by a two-part number (indicating
-- a location in the shared pool). For example:
-- dbms_shared_pool.keep('scott.hispackage')
-- will keep package HISPACKAGE, owned by SCOTT. The names for
-- PL/SQL objects follows SQL rules for naming objects (i.e.,
-- delimited identifiers, multi-byte names, etc. are allowed).
-- A cursor can be keeped by
-- dbms_shared_pool.keep('0034CDFF, 20348871')
-- The complete hexadecimal address must be in the first 8 characters.
-- The value for this identifier is the concatonation of the
-- 'address' and 'hash_value' columns from the v$sqlarea view. This
-- is displayed by the 'sizes' call above.
-- Currently 'TABLE' and 'VIEW' objects may not be keeped.
-- flag
-- This is an optional parameter. If the parameter is not specified,
-- the package assumes that the first parameter is the name of a
-- package and will resolve the name. It can also be set to 'P' or
-- 'p' to fully specify that the input is the name of a package.
-- In case the first argument is a cursor address and hash-value, the
-- parameter should be set to any character except 'P' or 'p'.
-- Exceptions:
-- An exception will raised if the named object cannot be found.
procedure unkeep(name varchar2, flag char DEFAULT 'P');
-- Unkeep the named object.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
-- Input arguments:
-- name
-- The name of the object to unkeep. See description of the name
-- object for the 'keep' procedure.
-- Exceptions:
-- An exception will raised if the named object cannot be found.
end;
/
Rem DBA_KEEPSIZES - size that will be keeped for a pl/sql object
Rem For instance, do:
Rem select * from dba_keepsizes where totsize>30 and owner='SCOTT'
Rem order by totsize desc;
Rem to see all PL/SQL objects owned by SCOTT which are larger than 30Kbytes
create or replace view dba_keepsizes (totsize, owner, name) as
select trunc((sum(parsed_size)+sum(code_size))/1000),
owner, name
from dba_object_size
where type in ('PACKAGE', 'PROCEDURE','FUNCTION','PACKAGE BODY')
group by owner, name;
create or replace package body dbms_shared_pool is
procedure psdkeep(schema varchar2, name varchar2, cursor_addr raw,
cursor_hash binary_integer, keep boolean);
pragma interface(C, psdkeep);
procedure doit(name varchar2, keep boolean, flag char) is
owner varchar2(30);
part1 varchar2(30);
part2 varchar2(30);
dblink varchar2(30);
part1_type number;
objno number;
begin
if flag <> 'P' and flag <> 'p' then
psdkeep('', '', hextoraw(substr(name,1,8)),
to_number(substr(name,10)), keep);
else
dbms_utility.name_resolve(name, 1, owner, part1, part2, dblink,
part1_type, objno);
psdkeep(owner, part1, null, null, keep);
end if;
end;
procedure keep(name varchar2, flag char) is
begin
doit(name, TRUE, flag);
end;
procedure unkeep(name varchar2, flag char) is
begin
doit(name, FALSE, flag);
end;
procedure sizes(minsize number) is
cursor c1(msize number) is
select to_char(sharable_mem/1000, '999999') sz,
decode(kept_versions, 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 > msize*1000
union
select to_char(sharable_mem/1000, '999999') sz,
decode(kept, 'YES', 'YES ', ' ') keeped,
owner || '.' || name || lpad(' ', 29 - (length(owner) +
length(name))) || '(' || type || ')' name,
null extra
from v$db_object_cache v
where sharable_mem > msize*1000
order by 1 desc;
l number;
i number;
begin
dbms_output.put_line('SIZE(K) KEPT NAME');
dbms_output.put_line('------- ------ ---------------------------------------
------------------------');
for rec in c1(minsize) loop
if substr(rec.name,1,1) in ('0','1','2','3','4','5','6','7','8','9') then
l := length(rec.extra);
i := 0;
while i < l loop
if i = 0 then
dbms_output.put_line(rec.sz || ' ' || rec.keeped || ' ' ||
substr(rec.extra,i,63));
i := i + 63;
else
dbms_output.put_line(' ' ||
substr(rec.extra,i,59));
i := i + 59;
end if;
end loop;
dbms_output.put_line(rpad(' (' || rec.name ||
')', 45) || '(CURSOR)');
else
dbms_output.put_line(rec.sz || ' ' || rec.keeped || ' ' || rec.name);
end if;
end loop;
end;
end;
/