Pin a query in shared pool

 

                                                 pin a query in shared pool

Pinning database objects in Oracle's shared pool can improve database performance. It is recommended that all ESRI stored procedures and sequences be pinned. Once objects reside in Oracle's shared pool, they do not need to be parsed, which saves considerable resources.

Pinning objects in the shared pool uses available memory that might otherwise be used for other processes that share the pool. Allocate the additional memory to the shared pool as needed. In general, do not pin objects that are rarely used; this could have the adverse affect on database performance.

Procedure

1.      Before pinning database objects, create the SYS package that pins the objects in the shared pool. As the SYS user in SQL*Plus, execute the dbmspool.sql script located under the $ORACLE_HOME/rdbms/admin directory.

Optionally, it is possible to grant execute privileges to the new DBMS_SHARED_POOL package to users who will need to pin objects. For example, to grant these privileges to the SDE user:

Code:

GRANT execute ON dbms_shared_pool TO sde; 

2.      Begin pinning objects in the shared pool.

Code:

EXECUTE dbms_shared_pool.keep ('STANDARD', 'P'); As a general rule, always pin the following 

packages owned by SYS:

   STANDARD 

   DBMS_STANDARD 

   DBMS_UTILITY 

   DBMS_DESCRIBE 

   DBMS_OUTPUT 

Pinning other SYS packages that are often used, such as DBMS_LOCK and DBMS_ALERT, 

may be desired.

3.      As the SDE user, pin the objects that the application frequently uses.
Code:
EXECUTE dbms_shared_pool.keep ('VERSION_UTIL', 'P');

Code:

 EXECUTE dbms_shared_pool.keep ('LOCK_UTIL', 'P'); 

Database objects are not limited to stored procedures. Database sequences, triggers, and cursors

 can all be pinned in the shared pool. ESRI recommends pinning frequently used sequences as well.

Code:

EXECUTE dbms_shared_pool.keep ('SDE.CONNECTION_ID_GENERATOR', 'Q'); 

SQL> SELECT 'Example TEST' FROM dual;

'EXAMPLETEST

------------

Example TEST

SQL> select substr(sql_text,1,15) Text,address,hash_value,KEPT_VERSIONS

from v$sql where sql_text like '%Example%';  2 

SQL> @?/rdbms/admin/dbmspool.sql

Session altered.

Package created.

Grant succeeded.

Session altered.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000006189AC28,2929179549','c');

PL/SQL procedure successfully completed.

SQL> select sid,serial#,prev_sql_id from v$session where audsid=userenv('sessionid');

       SID    SERIAL# PREV_SQL_ID

---------- ---------- -------------

            79        57117 5rsr6qt29hu5s

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='5rsr6qt29hu5s';

ADDRESS      HASH_VALUE

---------------- ----------

0000000061A2DE58 1150838968

Resize Logfile Group

View log file location:

 

SQL> select * from v$logfile;

---------- ------- -------

SQL> set linesize 1000;

SQL> set pagesize 1000;

    GROUP# STATUS  TYPE    MEMBER                                                                                                                                                                        IS_     CON_ID

             1            ONLINE  /u01/app/oracle/oradata/TESTDB/redo1.log                                                                                                                           NO   0

             2            ONLINE  /u01/app/oracle/oradata/TESTDB/redo2.log                                                                                                                           NO   0

             3            ONLINE  /u01/app/oracle/oradata/TESTDB/redo3.log                                                                                                                           NO   0

Adding new logfile group 4 and 2 members

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log','/u01/app/oracle/oradata/TESTDB/redo04b.log') size 100m;

Database altered.

SQL> col member format a50;

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             1             1            58   41943040          512               1 NO  INACTIVE                         2026743 26-JUL-21                2040694 26-JUL-21                   0

             2             1            59   41943040          512               1 NO  INACTIVE                         2040694 26-JUL-21                2053407 26-JUL-21                   0

             3             1            60   41943040          512               1 NO  CURRENT                         2053407 26-JUL-21            1.8447E+19                       0

             4             1             0  104857600          512               2 YES UNUSED                                     0                                 0                           0

Drop logfile group 1:

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

 

Adding new logfile group 5

 

SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/TESTDB/redo05a.log','/u01/app/oracle/oradata/TESTDB/redo05b.log') size 50m;

Database altered.

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             2             1            59   41943040          512               1 NO  INACTIVE                         2040694 26-JUL-21                2053407 26-JUL-21                   0

             3             1            60   41943040          512               1 NO  CURRENT                         2053407 26-JUL-21            1.8447E+19                       0

             4             1             0  104857600          512               2 YES UNUSED                                     0                                 0                           0

             5             1             0   52428800           512               2 YES UNUSED                                     0                                 0                           0

 

Drop logfile group 2

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

 

Database altered.

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            60   41943040          512               1 NO  CURRENT                         2053407 26-JUL-21            1.8447E+19                       0

             4             1             0  104857600          512               2 YES UNUSED                                     0                                 0                           0

             5             1             0   52428800           512               2 YES UNUSED                                     0                                 0                           0

 

SQL> alter system switch logfile;

 

System altered.

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            60   41943040          512               1 NO  ACTIVE                             2053407 26-JUL-21                2062712 26-JUL-21                   0

             4             1            61  104857600         512               2 NO  CURRENT                         2062712 26-JUL-21            1.8447E+19                       0

             5             1             0   52428800           512               2 YES UNUSED                                     0                                 0                           0

 

Adding log group 6:

SQL> alter database add logfile group 6 ('/u01/app/oracle/oradata/TESTDB/redo06a.log','/u01/app/oracle/oradata/TESTDB/redo06b.log') size 50m;

Database altered.

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            60   41943040          512               1 NO  ACTIVE                             2053407 26-JUL-21                2062712 26-JUL-21                   0

             4             1            61  104857600         512               2 NO  CURRENT                         2062712 26-JUL-21            1.8447E+19                       0

             5             1             0   52428800           512               2 YES UNUSED                                     0                                 0                           0

             6             1             0   52428800           512               2 YES UNUSED                                     0                                 0                           0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            60   41943040          512               1 NO  ACTIVE                             2053407 26-JUL-21                2062712 26-JUL-21                   0

             4             1            61  104857600         512               2 NO  ACTIVE                             2062712 26-JUL-21                2062768 26-JUL-21                   0

             5             1            62   52428800          512               2 NO  CURRENT                         2062768 26-JUL-21            1.8447E+19                       0

             6             1             0   52428800           512               2 YES UNUSED                                     0                                 0                           0

 

SQL> alter system drop logfileALTER DATABASE DROP LOGFILE GROUP 3;

alter system drop logfileALTER DATABASE DROP LOGFILE GROUP 3

             *

ERROR at line 1:

ORA-02065: illegal option for ALTER SYSTEM

 

 

SQL> alter system switch logfile;

 

System altered.

 

SQL>

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            60   41943040          512               1 NO  ACTIVE                             2053407 26-JUL-21                2062712 26-JUL-21                   0

             4             1            61  104857600         512               2 NO  ACTIVE                             2062712 26-JUL-21                2062768 26-JUL-21                   0

             5             1            62   52428800          512               2 NO  ACTIVE                             2062768 26-JUL-21                2062802 26-JUL-21                   0

             6             1            63   52428800          512               2 NO  CURRENT                         2062802 26-JUL-21            1.8447E+19                       0

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            64   41943040          512               1 NO  ACTIVE                             2062809 26-JUL-21                2062812 26-JUL-21                   0

             4             1            65  104857600         512               2 NO  CURRENT                         2062812 26-JUL-21            1.8447E+19                       0

             5             1            62   52428800          512               2 NO  ACTIVE                             2062768 26-JUL-21                2062802 26-JUL-21                   0

             6             1            63   52428800          512               2 NO  ACTIVE                             2062802 26-JUL-21                2062809 26-JUL-21                   0

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            64   41943040          512               1 NO  ACTIVE                             2062809 26-JUL-21                2062812 26-JUL-21                   0

             4             1            65  104857600         512               2 NO  ACTIVE                             2062812 26-JUL-21                2063502 26-JUL-21                   0

             5             1            66   52428800          512               2 NO  CURRENT                         2063502 26-JUL-21            1.8447E+19                       0

             6             1            63   52428800          512               2 NO  INACTIVE                         2062802 26-JUL-21                2062809 26-JUL-21                   0

 

SQL> alter system switch logfile;

 

System altered.

 

SQL>  select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            64   41943040          512               1 NO  ACTIVE                             2062809 26-JUL-21                2062812 26-JUL-21                   0

             4             1            65  104857600         512               2 NO  ACTIVE                             2062812 26-JUL-21                2063502 26-JUL-21                   0

             5             1            66   52428800          512               2 NO  ACTIVE                             2063502 26-JUL-21                2063511 26-JUL-21                   0

             6             1            67   52428800          512               2 NO  CURRENT                         2063511 26-JUL-21            1.8447E+19                       0

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3            1            68   41943040          512               1 NO  CURRENT                         2063522 26-JUL-21            1.8447E+19                       0

             4             1            65  104857600         512               2 NO  ACTIVE                             2062812 26-JUL-21                2063502 26-JUL-21                   0

             5             1            66   52428800          512               2 NO  ACTIVE                             2063502 26-JUL-21                2063511 26-JUL-21                   0

             6             1            67   52428800          512               2 NO  ACTIVE                             2063511 26-JUL-21                2063522 26-JUL-21                   0

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             3             1            68   41943040          512               1 NO  INACTIVE                         2063522 26-JUL-21                2063527 26-JUL-21                   0

             4             1            69  104857600         512               2 NO  CURRENT                         2063527 26-JUL-21            1.8447E+19                       0

             5             1            66   52428800          512               2 NO  INACTIVE                         2063502 26-JUL-21                2063511 26-JUL-21                   0

             6             1            67   52428800          512               2 NO  INACTIVE                         2063511 26-JUL-21                2063522 26-JUL-21                   0

 

Drop logfile group 3

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

 

Database altered.

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME        CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

             4             1            69  104857600         512               2 NO  CURRENT                         2063527 26-JUL-21            1.8447E+19                       0

             5             1            66   52428800          512               2 NO  INACTIVE                         2063502 26-JUL-21                2063511 26-JUL-21                   0

             6             1            67   52428800          512               2 NO  INACTIVE                         2063511 26-JUL-21                2063522 26-JUL-21                   0

 

SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

 

    GROUP#     BYTES            MEMBERS STATUS

---------- ---------- ---------- ----------------

             4  104857600        2 CURRENT

             5   52428800         2 INACTIVE

             6   52428800         2 INACTIVE

 

SQL>