1 package dbms_shared_pool is
2 ------------
3 -- OVERVIEW
4 --
5 -- This package provides access to the shared pool. This is the
6 -- shared memory area where cursors and PL/SQL objects are stored.
7
8 ----------------------------
9 -- PROCEDURES AND FUNCTIONS
10 --
11 procedure sizes(minsize number);
12 -- Show objects in the shared_pool that are larger than the specified
13 -- size. The name of the object is also given which can be used as
14 -- an argument to either the 'keep' or 'unkeep' calls below. You should
15 -- issue the SQLDBA or SQLPLUS 'set serveroutput on size xxxxx'
16 -- command prior to using this procedure so that the results will
17 -- be displayed.
18 -- Input arguments:
19 -- minsize
20 -- Size, in kilobytes, over which an object must be occupying in the
21 -- shared pool, in order for it to be displayed.
22 procedure keep(name varchar2, flag char DEFAULT 'P');
23 -- Keep an object in the shared pool. Once an object has been keeped in
24 -- the shared pool, it is not subject to aging out of the pool. This
25 -- may be useful for certain semi-frequently used large objects since
26 -- when large objects are brought into the shared pool, a larger
27 -- number of other objects (much more than the size of the object
28 -- being brought in, may need to be aged out in order to create a
29 -- contiguous area large enough.
30 -- WARNING: This procedure may not be supported in the future when
31 -- and if automatic mechanisms are implemented to make this
32 -- unnecessary.
33 -- Input arguments:
34 -- name
35 -- The name of the object to keep. There are two kinds of objects:
36 -- PL/SQL objects, triggers, sequences, types and Java objects,
37 -- which are specified by name, and
38 -- SQL cursor objects which are specified by a two-part number
39 -- (indicating a location in the shared pool). For example:
40 -- dbms_shared_pool.keep('scott.hispackage')
41 -- will keep package HISPACKAGE, owned by SCOTT. The names for
42 -- PL/SQL objects follows SQL rules for naming objects (i.e.,
43 -- delimited identifiers, multi-byte names, etc. are allowed).
44 -- A cursor can be keeped by
45 -- dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
46 -- The complete hexadecimal address must be in the first 8 characters.
47 -- The value for this identifier is the concatenation of the
48 -- 'address' and 'hash_value' columns from the v$sqlarea view. This
49 -- is displayed by the 'sizes' call above.
50 -- Currently 'TABLE' and 'VIEW' objects may not be keeped.
51 -- flag
52 -- This is an optional parameter. If the parameter is not specified,
53 -- the package assumes that the first parameter is the name of a
54 -- package/procedure/function and will resolve the name. Otherwise,
55 -- the parameter is a character string indicating what kind of object
56 -- to keep the name identifies. The string is case insensitive.
57 -- The possible values and the kinds of objects they indicate are
58 -- given in the following table:
59 -- Value Kind of Object to keep
60 -- ----- ----------------------
61 -- P package/procedure/function
62 -- Q sequence
63 -- R trigger
64 -- T type
65 -- JS java source
66 -- JC java class
67 -- JR java resource
68 -- JD java shared data
69 -- C cursor
70 -- If and only if the first argument is a cursor address and hash-value,
71 -- the flag parameter should be set to 'C' (or 'c').
72 -- Exceptions:
73 -- an exception will be raised if the named object cannot be found.
74 procedure keep(schema varchar2, objname varchar2, namespace number,
75 heaps number);
76 -- Keep an object in the shared pool.
77 -- Input arguments:
78 -- schema
79 -- The user name or the schema to which the object belongs to
80 -- objname
81 -- The name of the object to keep.
82 -- namespace
83 -- This parameter is a number indicating the library cache namespace in
84 -- which the object has to be searched for.
85 -- heaps
86 -- heaps to keep. e.g if heap 0 and heap 6 are to be kept..
87 -- 1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
88 -- Exceptions:
89 -- an exception will be raised if the named object cannot be found.
90 procedure keep(hash varchar2, namespace number, heaps number);
91 -- Keep an object in the shared pool.
92 -- Input arguments:
93 -- hash
94 -- 16-byte hash value for the object.
95 -- namespace
96 -- This parameter is a number indicating the library cache namespace in
97 -- which the object has to be searched for.
98 -- heaps
99 -- heaps to keep. e.g if heap 0 and heap 6 are to be kept..
100 -- 1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
101 -- Exceptions:
102 -- an exception will be raised if the named object cannot be found.
103 procedure unkeep(name varchar2, flag char DEFAULT 'P');
104 -- Unkeep the named object.
105 -- WARNING: This procedure may not be supported in the future when
106 -- and if automatic mechanisms are implemented to make this
107 -- unnecessary.
108 -- Input arguments:
109 -- name
110 -- The name of the object to unkeep. See description of the name
111 -- object for the 'keep' procedure.
112 -- flag
113 -- See description of the flag parameter for the 'keep' procedure.
114 -- Exceptions:
115 -- an exception will be raised if the named object cannot be found.
116 procedure unkeep(schema varchar2, objname varchar2, namespace number);
117 -- Unkeep an object in the shared pool.
118 -- Input arguments:
119 -- schema
120 -- The user name or the schema to which the object belongs to
121 -- objname
122 -- The name of the object to unkeep.
123 -- namespace
124 -- This parameter is a number indicating the library cache namespace in
125 -- which the object has to be searched for.
126 -- Exceptions:
127 -- an exception will be raised if the named object cannot be found.
128 procedure unkeep(hash varchar2, namespace number);
129 -- Unkeep an object in the shared pool.
130 -- Input arguments:
131 -- hash
132 -- 16-byte hash value for the object.
133 -- namespace
134 -- This parameter is a number indicating the library cache namespace in
135 -- which the object has to be searched for.
136 -- Exceptions:
137 -- an exception will be raised if the named object cannot be found.
138 procedure purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
139 -- Purge the named object or particular heap(s) of the object.
140 -- Input arguments:
141 -- name
142 -- The name of the object to purge. See description of the name
143 -- object for the 'keep' procedure.
144 -- flag
145 -- See description of the flag parameter for the 'keep' procedure.
146 -- heaps
147 -- heaps to purge. e.g if heap 0 and heap 6 are to be purged.
148 -- 1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
149 -- Default is 1 i.e heap 0 which means the whole object will be purged.
150 -- Exceptions:
151 -- an exception will be raised if the named object cannot be found.
152 procedure purge(schema varchar2, objname varchar2,
153 namespace number, heaps number);
154 -- Purge the named object or particular heap(s) of the object.
155 -- Input arguments:
156 -- schema
157 -- The user name or the schema to which the object belongs to
158 -- objname
159 -- The name of the object to purge.
160 -- namespace
161 -- This parameter is a number indicating the library cache namespace in
162 -- which the object has to be searched for.
163 -- heaps
164 -- heaps to purge. e.g if heap 0 and heap 6 are to be purged.
165 -- 1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
166 -- Default is 1 i.e heap 0 which means the whole object will be purged.
167 -- Exceptions:
168 -- an exception will be raised if the named object cannot be found.
169 procedure purge(hash varchar2, namespace number, heaps number);
170 -- Purge the object or particular heap(s) of the object.
171 -- Input arguments:
172 -- hash
173 -- 16-byte hash value for the object.
174 -- namespace
175 -- This parameter is a number indicating the library cache namespace in
176 -- which the object has to be searched for.
177 -- heaps
178 -- heaps to purge. e.g if heap 0 and heap 6 are to be purged.
179 -- 1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
180 -- Default is 1 i.e heap 0 which means the whole object will be purged.
181 -- Exceptions:
182 -- an exception will be raised if the named object cannot be found.
183 procedure markhot(schema varchar2, objname varchar2,
184 namespace number DEFAULT 1,
185 global boolean DEFAULT true);
186 -- Mark a library cache object as a hot object.
187 -- Input arguments:
188 -- schema
189 -- The user name or the schema to which the object belongs to.
190 -- objname
191 -- The name of the object.
192 -- namespace
193 -- This parameter is a number indicating the library cache namespace in
194 -- which the object has to be searched for.
195 -- global
196 -- If TRUE (default), mark the object hot on all RAC instances.
197 -- Exceptions:
198 -- an exception will be raised if the namespace passed in is invalid.
199 procedure markhot(hash varchar2, namespace number DEFAULT 1,
200 global boolean DEFAULT true);
201 -- Mark the named object as a hot object.
202 -- Input arguments:
203 -- hash
204 -- 16-byte hash value for the object.
205 -- namespace
206 -- This parameter is a number indicating the library cache namespace in
207 -- which the object has to be searched for.
208 -- global
209 -- If TRUE (default), mark the object hot on all RAC instances.
210 -- Exceptions:
211 -- an exception will be raised if the namespace or hash value
212 -- passed in is invalid.
213 procedure unmarkhot(schema varchar2, objname varchar2,
214 namespace number DEFAULT 1,
215 global boolean DEFAULT true);
216 -- Unmark a library cache object as a hot object.
217 -- Input arguments:
218 -- schema
219 -- The user name or the schema to which the object belongs to.
220 -- objname
221 -- The name of the object.
222 -- namespace
223 -- This parameter is a number indicating the library cache namespace in
224 -- which the object has to be searched for.
225 -- global
226 -- If TRUE (default), unmark the object as hot on all RAC instances.
227 -- Exceptions:
228 -- an exception will be raised if the named object cannot be found.
229 procedure unmarkhot(hash varchar2, namespace number DEFAULT 1,
230 global boolean DEFAULT true);
231 -- Unmark a library cache object as a hot object.
232 -- Input arguments:
233 -- hash
234 -- 16-byte hash value for the object.
235 -- namespace
236 -- This parameter is a number indicating the library cache namespace in
237 -- which the object has to be searched for.
238 -- global
239 -- If TRUE (default), unmark the object as hot on all RAC instances.
240 -- Exceptions:
241 -- an exception will be raised if the named object cannot be found.
242 procedure aborted_request_threshold(threshold_size number);
243 -- Set aborted request threshold for the shared pool.
244 -- Input arguments:
245 -- threshold_size
246 -- The size in bytes of a request which will not try to free unpinned
247 -- (not "unkeep-ed") memory within the shared pool. The range of
248 -- threshold_size is 5000 to ~2 GB inclusive.
249 -- Description:
250 -- Usually, if a request cannot be satisfied on the free list,
251 -- the RDBMS will try to reclaim memory by freeing objects from the
252 -- LRU list and checking periodically to see if the request can be
253 -- fulfilled. After finishing this step, the RDBMS has performed a near
254 -- equivalent of an 'alter system flush shared_pool'. As this impacts
255 -- all users on the system, this procedure "localizes" the impact to the
256 -- process failing to find a piece of shared pool memory of size
257 -- greater than thresh_hold size. This user will get the out of
258 -- memory error without attempting to search the LRU list.
259 -- Exceptions:
260 -- An exception will be raised if threshold is not in the valid range.
261 --
262 end;
[Home] [Help]