ow to Automate Pinning Objects in Shared Pool at Database Startup

How to Automate Pinning Objects in the Shared Pool at Database Startup:
=======================================================================

********************************************************
1/ Create the procedures to be executed by the triggers
********************************************************

a. Create a table to store the names of packages and procedures that had to be
reloaded several times during the instance life.

SQL> create table sys.list_tab (owner varchar2(64),NAME VARCHAR2(100));
Table created.

b. The procedure proc_pkgs_list retrieves the names of the packages and
procedures that will be kept in the shared pool at startup and inserts
the names in the table.
create or replace PROCEDURE proc_pkgs_list AS pragma AUTONOMOUS_TRANSACTION;
own varchar2(64);
SQL> create or replace PROCEDURE proc_pkgs_list AS
2 pragma AUTONOMOUS_TRANSACTION;
3 own varchar2(64);
4 nam varchar2(100);
5 cursor pkgs is
6 select owner,name
7 from SYS.v_$db_object_cache
8 where type in (‘PACKAGE’,’PROCEDURE’)
9 and (loads > 1 or KEPT=’YES’);
10 BEGIN
11 delete from sys.list_tab;
12 commit;
13 open pkgs;
14 loop
15 fetch pkgs into own, nam;
16 exit when pkgs%notfound;
17 insert into sys.list_tab values (own , nam);
18 commit;
19 end loop;
20 end;
21 /

Procedure created.

c. The procedure proc_pkgs_keep retrieves the procedures and package names and
keeps the objects in the shared pool. In order to use the dbms_shared_pool
package procedures, execute the dbmspool.sql script first.

SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
2 own varchar2(64);
3 nam varchar2(100);
4 cursor pkgs is
5 select owner ,name
6 from sys.list_tab;
7 BEGIN
8 open pkgs;
9 loop
10 fetch pkgs into own, nam;
11 exit when pkgs%notfound;
12 SYS.dbms_shared_pool.keep(”|| own || ‘.’ || nam || ”);
13 end loop;
14 sys.dbms_shared_pool.keep(‘SYS.STANDARD’);
15 sys.dbms_shared_pool.keep(‘SYS.DIUTIL’);
16 END;
17 /

Procedure created.

********************************************************
2/ Test that the procedures execute properly
********************************************************

SQL> execute sys.proc_pkgs_list;
PL/SQL procedure successfully completed.

SQL> execute sys.proc_pkgs_keep;
PL/SQL procedure successfully completed.

********************************************************
3/ Create the event triggers
********************************************************

a. The procedure proc_pkgs_list is executed before the
database shuts down, since this is the only way to get
the list of the procedures and packages that need to be
kept in the shared pool. For this purpose, create a
trigger to be fired before the instance shuts down.

SQL> CREATE OR REPLACE TRIGGER db_shutdown_list
2 BEFORE SHUTDOWN ON DATABASE
3 BEGIN
4 sys.proc_pkgs_list;
5 END;
6 /

Trigger created.

b. The procedure proc_pkgs_keep is executed when the database
starts up. For this purpose, create a trigger to be fired
after the database starts up.

SQL> CREATE OR REPLACE TRIGGER db_startup_keep
2 AFTER STARTUP ON DATABASE
3 BEGIN
4 sys.proc_pkgs_keep;
5 END;
6 /

Trigger created.

Errors
——

Check in the alert.log to see if the database triggers executed successfully.

When shutting down or starting up a database, if a database trigger fails to
execute, the following messages may appear in the alert.log:

*** SHUTDOWN

Shutting down instance (immediate)
License high water mark = 2
Mon May 22 12:31:45 2000
ALTER DATABASE CLOSE NORMAL
Mon May 22 12:31:45 2000
SMON: disabling tx recovery
Mon May 22 12:31:46 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_12624.trc:
ORA-04098: trigger ‘DB_SHUTDOWN_LIST’ is invalid and failed re-validation
SMON: disabling cache recovery
Mon May 22 12:31:47 2000
Thread 1 closed at log sequence 16579
Mon May 22 12:31:47 2000
Completed: ALTER DATABASE CLOSE NORMAL
Mon May 22 12:31:47 2000
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT

*** STARTUP
Example 1:

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0

SMON: enabling tx recovery
Tue Apr 18 10:21:38 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_7291.trc:
ORA-04098: trigger ‘DB_STARTUP_KEEP’ is invalid and failed re-valid
ation
Tue Apr 18 10:21:38 2000
Completed: alter database open

Tue Apr 18 10:21:30 2000
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0

Example 2:

SMON: enabling tx recovery
Tue Apr 18 11:12:41 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_7562.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 68
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 43
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 51
ORA-06512: at “SYS.PROC_PKGS_KEEP”, line 13
ORA-06512: at line 2
Tue Apr 18 11:12:41 2000
Completed: alter database open

In the /8i/ora815/admin/hp11_815/udump/ora_7562.trc file:

Error in executing triggers on STARTUP
*** 2000.04.18.11.12.41.052
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 68
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 43
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 51
ORA-06512: at “SYS.PROC_PKGS_KEEP”, line 13
ORA-06512: at line 2

—————–

Procedures and triggers are created and executed under SYS. The table list_tab
must exist before the database is shutdown the first time.
Execute the procedures before shutting down the database to test the triggers.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
This script is provided for educational purposes only. It is NOT supported by
Oracle Support Services. The script has been tested and appears to work as
intended. However, you should always test any script before relying on it.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, email packages and operating systems handle text formatting
(i.e. spaces, tabs and carriage returns), this script may not be in an
executable state when you first receive it. Check over the script to ensure
that errors of this type are corrected.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

RELATED DOCUMENTS
Note 69925.1 PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL
Note 1012047.6 HOW TO PIN OBJECTS IN YOUR SHARED POOL
Note 61623.1 Tuning the Shared Pool and resolving ORA-4031 in Oracle7
Note 61760.1 Using the Oracle DBMS_SHARED_POOL Package
Note 69493.1 Oracle8i – Database Trigger Enhancements
Note 74173.1 Database Event Triggers in Oracle8i

.

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em BUFFERS, PLSQL TUNING, SGA. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s