MERCADOS FINANCIEROS

Track all markets on TradingView

miércoles, 17 de febrero de 2010

DBMS_SHARED_POOL package Objetos en Memoria

DBMS_SHARED_POOL

desc v$db_object_cache;

package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored. DBMS_SHARED_POOL 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 procedures provided here may be useful when loading large PL/SQL objects. When large PL/SQL objects are loaded, users response time is affected because of the large number of smaller objects that need to be aged out from the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects.

DBMS_SHARED_POOL is also useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool.

Additionally, DBMS_SHARED_POOL supports sequences. Sequence numbers are lost when a sequence is aged out of the shared pool. DBMS_SHARED_POOL is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.



KEEP Procedure
Keeps an object in the shared pool

SIZES Procedure
Shows objects in the shared pool that are larger than the specified size

UNKEEP Procedure
Unkeeps the named object


DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (
threshold_size NUMBER);
Parameters

Table 97-2 ABORTED_REQUEST_THRESHOLD Procedure Parameters

Parameter Description
threshold_size

Size, in bytes, of a request which does not try to free unpinned (not "unkeep-ed") memory within the shared pool. The range of threshold_size is 5000 to ~2 GB inclusive.



Usually, if a request cannot be satisfied on the free list, then the RDBMS tries to reclaim memory by freeing objects from the LRU list and checking periodically to see if the request can be fulfilled. After finishing this step, the RDBMS has performed a near equivalent of an 'ALTER SYSTEM FLUSH SHARED_POOL'.

Because this impacts all users on the system, this procedure "localizes" the impact to the process failing to find a piece of shared pool memory of size greater than thresh_hold size. This user gets the 'out of memory' error without attempting to search the LRU list.


KEEP Procedure
This procedure keeps an object in the shared pool. Once an object has been kept in the shared pool, it is not subject to aging out of the pool. This may be useful for frequently used large objects. When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area large enough.

Syntax

DBMS_SHARED_POOL.KEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P');
Parameters


The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.

Currently, TABLE and VIEW objects may not be kept.

flag
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.

Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.

Set to 'T' or 't' to specify that the input is the name of a type.

Set to 'R' or 'r' to specify that the input is the name of a trigger.

Set to 'Q' or 'q' to specify that the input is the name of a sequence.

In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.




For example:

DBMS_SHARED_POOL.KEEP('scott.hispackage')
This keeps package HISPACKAGE, owned by SCOTT. The names for PL/SQL objects follow SQL rules for naming objects (for example, delimited identifiers and multibyte names are allowed). A cursor can be kept by DBMS_SHARED_POOL.KEEP('0034CDFF, 20348871'). The complete hexadecimal address must be in the first 8 characters.


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

SIZES Procedure
This procedure shows objects in the shared_pool that are larger than the specified size. The name of the object is also given, which can be used as an argument to either the KEEP or UNKEEP calls.

Syntax

DBMS_SHARED_POOL.SIZES (
minsize NUMBER);
Parameters

Table 97-4 SIZES Procedure Parameters

Parameter Description
minsize
Size, in kilobytes, over which an object must be occupying in the shared pool, in order for it to be displayed.



Usage Notes

Issue the SQLDBA or SQLPLUS 'SET SERVEROUTPUT ON SIZE XXXXX' command prior to using this procedure so that the results are displayed.


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

UNKEEP Procedure
This procedure unkeeps the named object.

Syntax

DBMS_SHARED_POOL.UNKEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P');
Caution:

This procedure may not be supported in the future if automatic mechanisms are implemented to make this unnecessary.

Parameters


Parameter Description
name
Name of the object to unkeep. See description of the name object for the KEEP procedure.

flag
See description of the flag parameter for the KEEP procedure.

Exceptions

An exception is raised if the named object cannot be found.
Publicado por en No hay comentarios:
Etiquetas:

jueves, 11 de febrero de 2010

Oracle Application Server 10G R3

omnctl status
opmnctl start
opmnctl startall
opmnctl stopall
opmnctl startproc ias-component=HTTP_Server

opmnctl status -app

Applications in Instance: as5.soaprod.us.oracle.com

application type: OC4J
------+-----------+---------+---------+----------------+----------+--------
pid | name | state | rtid | classification | routable | parent
------+-----------+---------+---------+----------------+----------+--------
11653 | system | started | g_rt_id | external | true |
11653 | default | started | g_rt_id | external | true | system
11653 | WSIL-App | started | g_rt_id | internal | true | default
11653 | ascontrol | started | g_rt_id | external | true | system
11653 | datatags | started | g_rt_id | internal | true | default
11653 | javasso | stopped | g_rt_id | internal | false |

./opmnctl status oc4j-group=default_group

Processes in Instance: as5.soaprod.us.oracle.com
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group | OC4J:home | 11653 | Alive

cat /etc/oratab

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/u01/app/oracle/infra:N
infra:/u01/app/oracle/infra:N
*:/u01/app/oracle/soa:N
soa:/u01/app/oracle/soa:N
*:/u01/app/oracle/as5:N

Para modificar el archivo con signo de admiracion antes

!clave

bajar la Consola de Enterprise Manager

/u01/app/oracle/as5/j2ee/home/config

vi system/jazn/data.xml

Para poder utilizar R2 para conectar con R3 debe tener en R2 Oracle AS Infrastrucutre Release 3 10.1.4
Publicado por en No hay comentarios:
Etiquetas:

VI Sustituir y Reemplazar

Sustituir y Reemplazar

g/2010/s//2020/g
Publicado por en No hay comentarios:
Etiquetas:

martes, 2 de febrero de 2010

Laboratorio Oracle Redologs

LABORATORIO UNIX

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.group#
/

1 52428800 INACTIVE /u01/letodb/redo01.log
2 52428800 CURRENT /u01/letodb/redo02.log
3 52428800 ACTIVE /u01/letodb/redo03.log

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo01a.log ') SIZE 300M;

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo02a.log ') SIZE 300M;

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo03a.log ') SIZE 300M;


SQL> ALTER DATABASE DROP LOGFILE GROUP 1;



LABORATORIO WINDOWS

SQL> /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> ed
Escrito file afiedt.buf

1* select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.group#
SQL> /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

6 filas seleccionadas.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR en línea 1:
ORA-01624: log 1 necesario para recuperación de fallo de la instancia power (thread 1)
ORA-00312: log online 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG'


SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG
Publicado por en No hay comentarios:
Etiquetas:
Suscribirse a: Entradas (Atom)