SQL> DESC SYS.DBMS_SHARED_POOL
Procedure Aborted_Request_threshold
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Threshold_size Number in
Procedure Keep
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Name varchar2 in
Flag char in default
Procedure Sizes
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Minsize Number in
Procedure unkeep
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Name varchar2 in
Flag char in default
SQL>
In other words, if we want KeeP a SQL
We can query V $ SQLAREA
Check 'Address' and 'Hash_Value'
Then execute dbms_shared_pool.keep ('0034CDFF, 20348871', 'C')
This will put the CURSOR Keep in memory
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 - Contiuous Area Large Enough.
- Warning: This Procedure May Not Be Supported in The Future When
- And if Automatic Mechanisms Are IMplement to make this
- Unnecessary.
- Input Arguments:
- Name
- The name of the object to keep. There Are Two Kinds of Objects:
- PL / SQL Objects, Triggers, Sequences, Types and Java 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', 'C')
- The Complete Hexadecimal Address Must Be in The First 8 Characters.
- The value for this identifier is the connection of the
- 'address' and 'hash_value' column from the V $ SQLAREA View. This
- Is Displayed by the 'Sizes' Call Above.
- Currently 'Table' and 'View' Objects May Not Beeped.
- Flag
- this is an optional parameter. If the parameter is not specific,
- The package assumes That The First Parameter Is The Name of A
- package / procedure / function and will resolve the name. Otherwise,
- The parameter is a character string indicating what kind of objecting. - THATER STRING INDICA
- To Keep the name Identifies. The string is copy insensitive .-- The Possible Values and the Kinds of Objects They Indicate Are
- Given In The Following Table:
- Value Kind of Object To Keep
- - --------------------------
- p package / procedure / function
- q sequence
- r Trigger
- t type
- JS Java Source
- JC Java Class
- JR Java Resource
- JD Java Shared Data
- cursor
- IF and only if the first argument is a cursor address and hash-value,
- The flag parameter should be set to 'c' (or 'c').
- EXCEPTIONS:
- An Exception Will Raised if The named Object Cannot Be Found.