Shulou(Shulou.com)06/01 Report--

PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared1 what is DBMS_SHARED_POOL

Mos explains

The DBMSSHAREDPOOL package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored. DBMSSHAREDPOOL enables you to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.

The DBMS_SHARED_POOL package provides stored procedures to pin PL/SQL objects or SQL cursors to Oracle shared pools. Once these objects are fixed, they will no longer participate in paged out, but will reside in memory. Even if you use alter system flush shared_pool, the objects will not be removed from the shared pool (of course, the database related objects will still be removed after restarting). This has the advantage of avoiding ORA-04031 errors or system performance problems caused by loading large objects into the shared pool.

2 Install DBMS_SHARED_POOL

Note: DBMS_SHARED_POOL is not installed with the system by default and must be installed before it can be used. The corresponding sql script is dbmspool.sql

SQL> show user;USER is "SYS"SQL> @?/ rdbms/admin/dbmspool.sql3 How non-sys users use the package: DBMS_SHARED_POOL Users first need sys to grant execute permissions to use the package: grant EXECUTE DBMS_SHARED_POOL to ;4 DBMS_SHARED_POOL package Common stored procedures

1) DBMSSHAREDPOOL.KEEP stored procedure

Role: Used to pin objects to shared pools

Parameter explanation:

PROCEDURE DBMS_SHARED_POOL.KEEP (name IN VARCHAR2 ,flag IN CHAR DEFAULT 'P');Flag Description---------- -------------- C cursor JC java class JD java shared data JR java resource JS java source P Package, procedure, or function nameQ sequence R trigger T type Any other character Cursor specified by address and hash value

Examples:

exec sys.dbms_shared_pool.keep('TRADE.TRG_KEEP_SEQ'); exec sys.dbms_shared_pool.keep('scott.city','T')

2).DBMSSHAREDPOOL.UNKEEP stored procedure

Role: Used to purge objects from the retention pool

Parameter explanation: Reference 1)

Examples:

exec sys.dbms_shared_pool.unkeep('TRADE.TRG_KEEP_SEQ','P')

3) DBMSSHAREDPOOL.SIZES stored procedure

Role: This procedure displays objects in the shared pool that exceed a specified size, including cursors and anonymous PL/SQL blocks. (The size of the specified value is in kbytes)

Parameter explanation:

PROCEDURE DBMS_SHARED_POOL.SIZES (minsize IN NUMBER);

Example: execute sys.dbmssharedpool.sizes(70);

4）ABORTED_REQUEST_THRESHOLD

Function: Sets a threshold size when an object larger than this threshold is loaded into the shared pool. Avoid unpinned memory objects being cleaned up when there is not enough space in the shared pool. Possible problems: ORA-4031

Value range: The value is between 5000 - 2147483647,

Examples:

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size IN NUMBER); execute SYS.DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD(50000);5 sql statements associated with this

1) Find objects that need to reside in shared pools

SELECT *FROM v$db_object_cacheWHERE sharable_mem > 10000 * AND (TYPE='PACKAGE' OR TYPE='PACKAGE BODY' OR TYPE ='FUNCTION' OR TYPE ='PROCEDURE')AND ept ='NO';

2) SQL statement resident memory

SQL> select count(*) from all_objects; COUNT(1) -------- 40793SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select count(*) from all_objects';ADDRESS HASH_VALUE SQL_TEXT-------- --------------- ----------------------------------------2D33FF58 789896629 select count(*) from all_objects SQL> exec sys.dbms_shared_pool.keep ('2D 33FF58, 789896629','C');PL/SQL procedure successfully completed. If we want to unpin into memory, we call DBMS_SHARED_POOL.UNKEEP with the same parameters as KEEP.

3) Command to clear shared pool (if there is no free space when using package keep object, flush shared_pool)

ALTER SYSTEM FLUSH SHARED_POOL; --This operation does not clear memory-resident objects

4)View objects currently resident in memory

select * from v$db_object_cache where kept='YES';

5)Find large anonymous PL/SQL blocks and split them into smaller PL/SQL blocks to improve utilization of shared pools

SELECT sql_textFROM v$sqlareaWHERE command_type=47AND LENGTH(sql_text)>500;

7. Solve the opening problem

SQL> show user;USER is "SYS"SQL> @?/ rdbms/admin/dbmspool.sqlSQL> exec sys.dbms_shared_pool.sizes(0)

8. Other errors encountered while solving the problem

Error: ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes Resolution: PL/SQL: DBMS_OUTPUT.ENABLE (buffer_size => NULL);SQL*Plus: set serveroutput on size unlimited

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.