Oracle Certification News

Oracle Certification Emagazine

Oracle Enqueues

A Latch as used in Oracle is a low-level mechanism for serializing concurrent access to memory structures such as cache buffer headers. A latch is a simple memory structure that averages 100 to 200 bytes in size (actual size is dependent upong the version of Oracle, Operating System and hardware platform). Latches are contained in a state object structure and reside in either fixed SGA (parent latches) or the shared pool. They are set via the hardware-atomic compare-and-swap (CAS) instructions – LOCK CMPXCHG for Intel. Latches can be shared and are used for some AQ operations.

Latch Contention occurs when a process attempts to get a latch but fails because another process already has control of it. If the attempt (get) was issue with in no-wait mode, then return to the calling process with a failure result. If the get was issued in a willing-to-wait mode then continue attempting to obtain the latch until x number of tries is reached.

The term to describe a get that is executed in a willing-to-wait mode that tries repeatedly without acquiring the latch is spinning – the process issues the get, if the latch is already held then the process will sleep, wake up after x amount of time expires and attempts to acquire the latch again.

The process attempting to acquire a latch may be waken by the process that releases the latch but this method isn’t used for all latches.

The following views are used to troubleshoot contention issues:

• v$session_event
• v$session_wait
• v$latchholder

Troubleshooting latch contention consists of first identifying the session(s) that are currently experiencing problems and then identify the latch the session is waiting for and determine if the wait time is significant enough to warrant attention. Identifying the child latch that is involved will help determine if the contention is concentrated on a particular child latch or are many child latches involved.

As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and consume less memory space. They can also be embedded inside other structures, they have flexible spin/yield/wait strategies defined by the client process and they do not factor into the accounting for GETS,SPINS or YIELDS, only WAITS.

Keep in mind that KGX mutexes are not OS mutexes.

In 11g+ mutexes are used instead of library cache latches. The default for pinning library cache cursors and parent examination for 10.2.0.2+ is mutexes – _kks_use_mutex_pin=true.

Some known mutex types in 11g:

• Cursor Parent
• Cursor Pin
• Cursor Stat
• Library Cache
• hash table

Mutex Troubleshooting – involves querying the views: v$session_wait, v$mutex_sleep and v$mutex_sleep_history among others. The mutex sleeps are well instrumented in the wait interface and the P1, P2, P3 values contain the hash value of the library cache objects experiencing contention, the session holding the mutex, etc. The views v$event_name and v$session_wait “text” columns document the meaning of P1, P2 and P3. The v$mutex_sleep view shows the wait time and the number of sleeps for each combination of mutex type and location while the v$mutex_sleep_history view shows the last individual occurences of mutex sleeps based on a circular buffer and this view contains the highest level of detail.

Enqueues – Terminology and Views

Locks are called enqueues because the locking mechanism implements an ordered queuing structure for lock waiters. Latches and mutexes do not have sophisticated queuing mechanisms.

Enqueue Resources – v$resource(X$KSQRS)

Enqueue Locks – v$lock(X$KSQEQ)

Enqueue resource types – v$lock_type has two meanings – ID1 and ID2

Slots in a hash table identify a resource. Resources are not locks, they are placeholders that reference the object that can be locked. The unique identifier (primarykey) for a resource will consists of the following:

• Resource type (TM, TX, MR, …)
• Resource identifier 1 (ID1)
• Resource identified 2 (ID2)
• Example:
• TM-XXXXXXXX-XXXXXXXX

The resource identifiers are used to break down a resource class into individual objects. An example of this would be two tables that both have a TM resource allocated to them in memory. The ID1 specifies the object_id of the table (v$lock_type which contains the meaning of ID1/2 for each resource type).

Locks – provide the link between the resource locked and the holder of that resource. If there are many lock holders on a single resource (assuming compatible mode) then it follows that there will be multiple lock structures pointing to that one resource.

PL/SQL Execution Diagnosis

There are new colummns from 10.2.0.3 on in V$SESSION:

• PLSQL_ENTRY_OBJECT_ID
• PLSQL_ENTRY_SUBPROGRAM_ID
• PLSQL_OBUECT_ID
• PLSQL_SUBPROGRAM_ID

Troubleshooting Tools – Session Level

Profile session wait / CPU timing with V$SESSION_WAIT, V$SESSION_EVENT, V$SESS_TIME_MODEL (10g+) and the tools Snapper, Sesspack, Statspack session mode.

Profile session performance counters – V$SESSTAT

Profile a session’s process stack – statisically – with pstack, procstack, ostackprof, dstackprof, procmon.exe, procexp.exe, oradebug short_stack, oradebug errorstack.

Solarix, Linux and HP-UX use pstack. AIX use procstack, see Metalink for procwatcher. If you have gdb or mdb you can use them to exaxmine stack traces.

Windows – windbg and procexp (sysinternals site) – but you will not be able to see symbolic function names in oracle.exe.

Oracle Internals – oradebug short_stack, oradebug dump errorstack 1 and alter session set events ‘942 trace name errorstack’.

Explain Plan Evolution from 9.2 to 11g

Starting with Oracle 9.2 the typical method for generating explain plans were:

1. explain plan for <statement>

2. select * from table(dbms_xplan.display)

Oracle 10g:

1. autotrace uses dbms_xplan

2. set autotrace on

3. select * from table(dbms_xplan.display_cursor())

Oracle 11g:

Introduced DBMS_SQLTUNE.REPORT_SQL_MONITOR

Inherent issues when using explain plan for – all bind variables are treated as VARCHAR2 and you may not see the actual (real) execution plan Oracle used.

An alternative method is to query the view V$SQL_PLAN_STATISTICS or use DBMS_XPLAN.DISPLAY_CURSOR.

Common Terminology Definitions

Access Path – physical data storage access to read data from tables, indexes, external tables and database links. Only access paths can access physical data and return the row sources.

Row Source – virtual stream of data via access paths or from child row sources

Filter Predicate – row source property – rows can be discarded based on defined conditions. Row sources can be cascading, non-cascading or semi-cascading.

Join – rows are filtered and merged based on rows that match from child row sources. Matching is determined by join predicates. Join operators can only operate on two inputs while other operations can combine the result of more than two row sources.

Oracle Kernel functions are responsible for execution plan steps.

Oracle Cloud Computing

Discovering as much as I can about Oracle Cloud Computing. I haven’t been able to determine if it is possible to run PeopleSoft using this architecture but from just reading through the Oracle documentation i see that you can run an Oracle Database and a Web Server (Web Logic) so that leaves the Application Server and Process Scheduler.

It would be great if you could set up your initial PeopleSoft delivered environments in this type of infrastructure to jump start projects while you wait for hardware to be delivered or you just don’t want to own hardware anymore.

Install Blat on Windows

blat -install localhost
blat -install <smtp host> <email address>
blat -install <smtp host> <email address> 3 25

number of times to send message (3)
TCP/IP port to use (26)

blat -install localhost <email adress>  3 25 FromStan1

store the settings in a Profile named: FromStan1

blat -install <smtp server> <email address> – - FromStan2

store the settings in a profile named: FromStan2, default number of tries and port

blat -to -from -subject -body -server -q

blat -profile (executed on ERAC2 lists)

SMTP: 10.160.225.30 “email address” 3 25

Updated HP-UX 11i v3 Kernel List

The following is a list of non-default kernel parameters for a Production PS HCM 9.0, PeopleTools 8.49 environment running on a rx7640 in a VPAR with 15.5 GB of memory. The list of kernel parameters is for the VPAR containing the Production Database Instance:

Tunable                   Value  Expression    Changes

dnlc_hash_locks            4096  4096

filecache_max         138412032  132MB         Imm (auto disabled)

filecache_min         138412032  132MB         Imm (auto disabled)

ftable_hash_locks          4096  4096

lcpu_attr                     0  0             Imm (auto disabled)

max_thread_proc            1200  1200          Immed

maxdsiz_64bit        2147483648  2147483648    Immed

maxssiz               134217728  134217728     Immed

maxssiz_64bit        1073741824  1073741824    Immed

maxuprc                    3780  3780          Immed

msgmni                     4200  nproc         Immed

msgtql                     4200  nproc         Immed

ncsize                    36672  36672

nflocks                    2048  2048          Imm (auto disabled)

ninode                    35648  8*nproc+2048

npty                        256  256

nstrpty                     256  256

nstrtel                     256  256

semmni                     4200  nproc

semmns                     8400  semmni*2

semmnu                     4196  nproc-4

shmmax               7516192768  7516192768    Immed

shmmni                      512  512           Immed

swchunk                    4096  4096

vnode_cd_hash_locks        4096  4096

vnode_hash_locks           4096  4096

vps_ceiling                  64  64            Immed

vx_ninode                100000  100000        Immed

Tunable                   Value  Expression    Changes
dnlc_hash_locks            4096  4096
filecache_max         138412032  132MB         Imm (auto disabled)
filecache_min         138412032  132MB         Imm (auto disabled)
ftable_hash_locks          4096  4096
lcpu_attr                     0  0             Imm (auto disabled)
max_thread_proc            1200  1200          Immed
maxdsiz_64bit        2147483648  2147483648    Immed
maxssiz               134217728  134217728     Immed
maxssiz_64bit        1073741824  1073741824    Immed
maxuprc                    3780  3780          Immed
msgmni                     4200  nproc         Immed
msgtql                     4200  nproc         Immed
ncsize                    36672  36672
nflocks                    2048  2048          Imm (auto disabled)
ninode                    35648  8*nproc+2048
npty                        256  256
nstrpty                     256  256
nstrtel                     256  256
semmni                     4200  nproc
semmns                     8400  semmni*2
semmnu                     4196  nproc-4
shmmax               7516192768  7516192768    Immed
shmmni                      512  512           Immed
swchunk                    4096  4096
vnode_cd_hash_locks        4096  4096
vnode_hash_locks           4096  4096
vps_ceiling                  64  64            Immed
vx_ninode                100000  100000        Immed

RMAN Backup RAC Example

RAC Backup and Recovery using RMAN

Objectives:
===========

1. Verify the database mode and archive destination.
2. Verify connectivity using sqlnet for target and catalog.
3. Determine your backup device
4. Understand how to create an RMAN persistent configuration for a RAC env.
5. Create backups to disk using
6. Backupset Maintenance using the configured retention policy
7. Restore and Recover
a. complete
b. incomplete
8. Review and understand the impact of resetlogs on the catalog.
9. RMAN Sample Commands

Configuration:
==============

This discussion is for a 2-node Oracle RAC Cluster.
The logs are being archived to their respective node.
We are allocating channels to each node to enable the autolocate feature
of RMAN in a RAC env.

1. Verify the databases are in archivelog mode and archive destination.

a. NODE 1: thread 1

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/product/10.2.0/dbs/arch
Oldest online log sequence 20
Next log sequence to archive 21
Current log sequence 21

b. NODE 2: thread 2

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/product/10.2.0/dbs/arch
Oldest online log sequence 8
Next log sequence to archive 9
Current log sequence 9

2. Verify connectivity to the target nodes and catalog if used.

a. % setenv TNS_ADMIN $ORACLE_HOME/network/admin
b. % sqlplus /nolog
c. SQL> connect sys/pwd@node1 as sysdba
d. SQL> connect sys/pwd@node2 as sysdba
e. SQL> connect rman/rman@rcat

3. Set your testing areas.

Testing HOME for logs: /opt/app/oracle/admin/usupport/rman

Backups HOME Location: /opt/app/oracle/admin/backup/rman/V1020

4. Connect using RMAN to verify and set the controlfile persistent configuration.
The controlfiles are shared between the instances so configuring the control-file on node 1 also sets it for all nodes in the RAC cluster.

a. Alway note the target DBID connected to target database: V1020 (DBID=228033884)

b. Default Configuration

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/product/10.2.0/dbs/snapcf_V1020.f’; # default

c. Make changes to the default that fit your business requirements.
Note the retention policy can be set “TO REDUNDANCY x” or it can
be set “TO RECOVERY WINDOW OF x DAYS”, this is new in Oracle9i.

In this example, using PARALLELISM 2 as 2 nodes are used.
The PARALLELISM will than automaticly start 2 channels and will
use the related CONFIGURE CHANNEL for additional clauses.

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/opt/app/oracle/admin/backup/rman/V1020/%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/admin/backup/rman/V1020/snapcf_V102009xx.f’;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK connect ‘SYS/rac@node1′;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK connect ‘SYS/rac@node2′;

d. Review/Verify your new configuration.

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/opt/app/oracle/admin/backup/rman/V1020/%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT ‘SYS/rac@node1′;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT ‘SYS/rac@node2′;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/admin/backup/rman/V1020/snapcf_V92321.f’;

5. Make a backup using the new persistent configuration parameters.

a. Backup database with differential incremental 0 and then archived logs
using the delete input option.

backup incremental level 0
format ‘/opt/app/oracle/admin/backup/rman/V1020/%d_LVL0_%T_%u_s%s_p%p’
database;
backup archivelog all format ‘/opt/app/oracle/admin/backup/rman/V1020/%d_AL_%T_%u_s%s_p%p’
delete input;

b. Backup again using differential inremental level 1

backup incremental level 1
format ‘/opt/app/oracle/admin/backup/rman/V1020/%d_LVL1_%T_%u_s%s_p%p’
database;
backup archivelog all format ‘/opt/app/oracle/admin/backup/rman/V1020/%d_AL_%T_%u_s%s_p%p’
delete input;

c. To simplify this in Oracle9i we can also use PLUS ARCHIVELOG
Note: This uses a different alorithm then backup database and
backup archivelog in separate commands.

BACKUP incremental level 0
format ‘/opt/app/oracle/admin/backup/rman/V1020/%d_LVL0_%T_%u_s%s_p%p’
database PLUS ARCHIVELOG format ‘/opt/app/oracle/admin/backup/rman/V1020/%d_AL_%T_%u_s%s_p%p’
delete input;

Algorithm for PLUS ARCHIVELOG:

1. Archive log current
2. Backup archived logs
3. Backup database level 0
4. Archive log current
5. Backup any remaining archived log created during backup

6. Backupset Maintenance using the configured retention policy

RMAN> list backup summary;
list backup by datafile;
list backup of database;
list backup of archivelog all;
list backup of controlfile;

Note: these above can be enhanced with the “until time” clause as well as
the archivelog backups using “not backed up x times” to cut down on
many copies of a log in several backupsets. Then continuing with SMR
Server Managed Recovery use the change archivelog from…until…delete
to remove old logs no longer needed on disk.

RMAN> report obsolete;

RMAN> delete obsolete;
or
delete noprompt obsolete;

RMAN> report schema;

7. Restore and Recover

Complete Recovery

a. With the database mounted on the node1 and nomount on node2 connect
to the target and catalog using RMAN.

rman target / catalog rman/rman@rcat

This script will restore and recover the database completely and open.
All previous backup will still be available for use because there was
not RESETLOGS command given.

run {
restore database;
recover database;
alter database open;
}

Incomplete Recovery

Note: If you are using instance registration the database must be mounted to
register with the listener. This means you must use the current control
file for restore and recovery or setup a dedicated listener if not
already done. RMAN requires a dedicated server connection and does not
work with using instance registration before mounting the controlfile.
Using the autobackup controlfile feature requires the DBID of the
TARGET database. It must be set when the database is not mounted and
only the controlfile and spfile (in 9.2>) can be restored this way.

a. shutdown node1 and node2

b. startup nomount node2 and node1

c. start rman

> rman trace reco1.log

RMAN> connect catalog rman/rman@rcat

RMAN> set dbid=228033884;

RMAN> connect target

d. Restore the controlfile from autobackup

% rman trace recocf.log

RMAN> SET DBID=228033884;

RMAN> CONNECT TARGET

RUN
{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO ‘/opt/app/oracle/admin/backup/rman/V1020/%F’;
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
RESTORE CONTROLFILE FROM AUTOBACKUP
MAXSEQ 5 # start at sequence 5 and count down (optional)
MAXDAYS 5; # start at UNTIL TIME and search back 5 days (optional)
MOUNT DATABASE;
}

e. Verify what is available for incomplete recovery. We will recover to the
highest scn log sequence and thread. We will use the log sequence in this
case. Your options are “until time”, “until scn”, or “until sequence”.

SQL> select max(sequence#) from v$archived_log
2 where thread#=1;

MAX(SEQUENCE#)
————–
25

SQL> select max(sequence#) from v$archived_log
2 where thread#=2;

MAX(SEQUENCE#)
————–
13

Note: In this case the scn is greater in thread 2 sequence# 13 then in
sequence 25 from thread 1. So we will set the seqeunce to 14 for
rman recovery because log recovery is always sequence+1 to end
at +1 after applying the prior sequence.

SQL> select sequence#, thread#, first_change#, next_change#
2 from v$archived_log
3 where sequence# in (13,25);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
———- ———- ————- ————
25 1 1744432 1744802
13 2 1744429 1744805

SQL> select sequence#, thread#, first_change#, next_change#
2 from v$backup_redolog
3 where sequence# in (13,25);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
———- ———- ————- ————
25 1 1744432 1744802
13 2 1744429 1744805

f. If using LMT Temporary tablespace the controlfile will have the syntax
to add the tempfile after recovery is complete.

SQL> alter database backup controlfile to trace;

Example:
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/dev/usupport_vg/rV92B_temp_01.dbf’
SIZE 41943040 REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#

g. Since log sequence 13 thread 2 next_change# is 3 changes ahead of thread 1
sequence 25 we are using dequence 14 to stop recovery. This will restore
the datafiles and recover them completely using the online logs.

run {
set until sequence 14 thread 2;
restore database;
recover database;
alter database open resetlogs;
}

8. Review and understand the impact of resetlogs on the catalog.

RMAN> list incarnation of database V1020;

Note: After resetlogs there are 2 incarnations in the recovery catalog. Only
one incarnation can be current at one time for a given dbid. The Inc Key
keeps track of the database incarnations.

List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
2656 2657 V1020 228033884 NO 1 29-MAY-03
2656 3132 V1020 228033884 YES 1744806 13-JUN-03

9. RMAN Sample Commands

a. With a dedicated listener (not using instance registration)
restoring the controlfile.

run {
allocate channel d1 type disk connect ’sys/rac@node1′;
allocate channel d2 type disk connect ’sys/rac@node2′;
set until sequence 14 thread 2;
restore controlfile;
alter database mount;
release channel d1;
release channel d2;
}

b. Backup Archivelog

backup archivelog all not backed up 3 times;
backup archivelog until time ’sysdate-2′ not backed up 2 times;

Automatic Memory Management (AMM) 11g

Autmatic Memory Management AMM

The basic memory structures associated with Oracle Database include:

System Global Area (SGA)

The SGA is a group of shared memory structures, known as SGA components that contain data and control information for one Oracle Database Instance. The SGA is shared by all server and background processes.

Program Global Area (PGA)

A PGA is a memory region that contains data and control information for a server process. It is a nonshared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to the server process that was instantiated. There is one PGA for each server process. Background processes also allocate their own PGAs and the total PGA memory allocated for all background and server processes attached to an Oracle Instance is referred to as the total Instance PGA memory and the collection of the individual PGAs is referred to as the total instance PGA or just Instance PGA.

This memory region is the one that contains the global variables, data structures and control information for a server process. An example of this type of data is the runtime area of a cursor. Each time that a cursor s executed a new runtime area is generated for that cursor in the PGA memory region of the server process mapped to that cursor.

Performance of complex long running queries typical of PeeopleSoft depend to a large extent on the memory available in the Program Global Area (PGA) which is known as the work area.

Below is a figure that attempts to illustrate the relationships among Oracle memory structures:

Evolution of Memory Management Features

Memory Management has evovled with each new Oracle Database version.

Oracle 9i

Beginning with Oracle 9i which introduced the dynamic SGA infrastructure that allowed for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown and restart the Oralce Database. Key features introduced in this version:

• Dynamic Memory resizing

• DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS

• DB_nK_CACHE_SIZE for multiple block sizes

• PGA_AGGREGATE_TARGET this is the initial version of Automatic PGA Memory Management

Oracle Database 10g

Automatic Shared Memory Management known as ASMM was introduced in Oracle version 10g. You have the option of enabling automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value. The degree of how much control you give Oracle for ASMM is control by supplying zero values for several key Oracle initialization parameters. You can permit Oracle to have full control or you can elect to manually control the minimimum size of several memory structures.

Oracle Database 11g

Automatic Memory Management (AMM) was introduced in Oracle verion 11g. Note the missing “S” in the acronym. The new Oracle initialization parameters were created for use with AMM – MEMORY_MAX_TARGET and MEMORY_TARGET.

The most important SGA components are described below:

Notes: RESULT_CACHE_MAX_SIZE is a new parameter that was introduced as part of the 11g Memory Architecture. The result cache is comprised of the SQL query result cache and PL/SQL function result cache, which share the same memory structures. Results of queries and query fragments can now be cached in memory in the SQL query result cache. The database can then use those cached results to answer future executions of those queries and query fragments. Similarly the PL/SQL Function Result can also be cached.

In order to these features you have to set the Oracle Initialization Parameter – RESULT_CACHE_MODE which determines the behavior of the SQL query result cache behavior. The possible values are MANUAL and FORCE.

Oracle Database 11g supports various memory management methods, which are set by choosing values for the Oracle initialization parameter settings. Oracle recommends that you use the Automatic Memory Management method and the following lists illustrates their recommendations:

1. Automatic Memory Management – for both the SGA and Instance PGA
2. Automatic Shared Memory Management – for the SGA
3. Manual Shared Memory Management – for the SGA
4. Automatic PGA Memory Management – for the Instance PGA
5. Manaual PGA Memory Management – for the Instance PGA

1. Automatic Memory Management – for both the SGA and Instance PGA

Starting with Oracle 11g, Oracle can now manage both the SGA memory and the instance PGA memory completely automatically. You set the value for the total memory size that is to be dedicated to the Oracle Instance and Oracle takes that value and dynamically sets the values for the various memory structures and manages the exchange of memory between the SGA and the Instance PGA as needed to meet processing demands. This capability is referred to as Automatic Memory Management. By using this method the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

Two new parameters were introduced in Oracle 11g named MEMORY_MAX_TARGET and MEMORY_TARGET. You set only a target memory size (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

If you create a database with the Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, the DBCA enables you to select memory management.

Switching to Automatic Memory Management

1. Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

In SQL*Plus connect as SYSDBA and display the following values for SGA_TARGET and PGA_AGGREGATE_TARGET:

SQL> show parameter target

archive_lag_target
db_flashback_retention_target
fast_start_io_target
fast_start_mttr_target
memory_max_target
memory_target
pga_aggregate_target
sga_target

Add the values together for pga_aggregate_target and sga_target.

2. Decide on a maximum amount of memory that you want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes.

3. Change the parameter in the Oracle initialization parameter file using spfile and for our example 1800M for MEMORY_MAX_TARGET and 400M for the PGA_AGGREGATE_TARGET.

SQL>
alter system set memory_max_target = 1800M scope=spfile;
alter system set memory_target = 1800M scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set pga_aggregate_target = 0 scope=spfile;

If you have decided to not implement the spfile parameter file and still use a pfile (which you should move away from) then edit the initORCLSID.ora and set the parameters manually:

MEMORY_MAX_TARGET=1800M
MEMORY_TARGET=1800M
SGA_TARGET=0
PGA_AGGREGATE_TARGET=0

In the event that you do not specify any value for MEMORY_MAX_TARGET and decide to only use the MEMORY_TARGET then Oracle automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you omit the MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter parameter defaults to zero. After startup you can then change the value for MEMORY_TARGET to a non-zero value and provide that the number does not exceed the value of MEMORY_MAX_TARGET this allows you to dynamically set the value for MEMORY_TARGET without having the restart Oracle.

MEMORY_MAX_TARGET however is not a dynamic parameter, it is a static one and if you desire to change its value then you will have to bounce the Oracle DB to affect the change.

4. Shutdown and startup the database:

SQL> shutdown immediate;
startup mount
show parameter

The above steps suggest setting the values for SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and Instance PGA are tuned up and down dynamically as Oracle determines they need to be without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. If you do this then these values act as minimum values for the sizes of the SGA or Instance PGA.

Note: In the case you set any parameter value to a value which is higher than MEMORY_TARGET, then you will get the following error when attempting to start up Oracle:

Monitoring and Tuning Automatic Memory Management

The dynamic performance view v$memory_dynamic_components displays the current sizes of all dynamically tuned memory components including the total sizes of the SGA and Instance PGA. The view v$memory_target_advice provices tuning advice for the MEMORY_TARGET initialization parameter.

sql> select * from v$memory_target_advice order by memory_size;

You can also query the v$memory_resize_ops which has a circular history buffer of the last 800 SGA resize requests.

3. Automatic Shared Memory Management – for the SGA

If you want to exercise more direct control over the size of the SGA, you can choose not to use AMM (Automatic Memory Management) 11g’s Memory Model and enable Automatic Shared Memory Management (ASMM) 10g’s Memory Model. This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of the current workload without requiring any additional or manual intervention and/or configuration.

If you enabled AMM and you decide you want to switch to ASMM you can follow the steps below:

SQL> alter system set memory_target = 0 scope=both;
SQL> alter system set sga_target = 500M scope=both;

4. Manual Shared Memory Management – for the SGA

To gain complete control over the individual SGA component sizes you can disable both auto automatic memory management and automatic shared memory management. In this mode you have to set the sizes of several individual SGA components, thereby determing the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.

In this case you set SGA_TARGET and MEMORY_TARGET to 0 and set the value for other SGA components up to the value of SGA_MAX_SIZE.

<4. Automatic PGA Memory Management – for the Instance PGA

With using Automatic Memory Management, PGA memory is allocated based upon the value of MEMORY_TARGET. In case you enable Automatic Shared Memory Management (ASMM) or manual Shared Memory Management, you also implicitly enable Automatic PGA Memory Management.

Automatic/Manual PGA Memory Management is decided by the inialization parameter WORKAREA_SIZE_POLICY which is a session- and system-level parameter that can take one of two values MANUAL or AUTO, the default is AUTO.

With Automatic PGA Memory Management, you set a target size for the Instance PGA by defining a value for PGA_AGGREGATE_TARGET and the sizing of the SQL work areas is determined automaitically and all *_AREA_SIZE Oracle Initialization parameters are ignored for these sessions. This feature is available from 9i onwards.

At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes like session memory. The resulting PGA memory is then alloted to individual active work areas based on their specific memory requirements.

5. Manual PGA Memory Management – for the Instance PGA

In the case where you want to manually specify the maximum work area size for each type of SQL operator such as sort or hash-join then you can enable Manual PGA Memory Management.

To accomplish this set the WORKAREA_SIZE_POLICY value to MANUAL and also specify values for *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE AND CREATE_BITMAP_AREA_SIZE, etc.

The Oracle 11g Database supports Manual PGA Memory Management but Oracle strongly recommends against using that model and leave Automatic PGA Memory Managemnet to Oracle itself.

The following table summarizes the various Memory Management Models that are available in Oracle 11g:

The Automatic Memory Management (AMM) Model feature uses background processes named Memory Managment (MMAN) which was introduced in Oracle 10g and which participated in Automatic Shared Memory Management (ASMM) using SGA_TARGET. MMAN serves as the SGA Memory Broker and is responsible for coordinating all of the memory activies including the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and maintains the pending memory resize operations and queues.

Where to find EMCA log files

Where to find the logs:

EMCA 10.1

In 10.1 the emca log files are located in the $ORACLE_HOME/cfgtoollogs directory and are in the format they are in is emca.log.X.

EMCA creates the log file if it does not exist (usually the first time it is launched) and then it appends to this each time it runs.

EMCA 10.2

In 10.2 the emca log files are located in the $ORACLE_HOME/cfgtoollogs/emca directory. The filename is in the format emca_timestamp.log where timestamp is in the format: YYYY-MM-DD_HH12-MI-AMP/PM.log.

A new file is created each time emca is run. Then for each database, a sub-directory is creatd under $ORACLE_HOME/cfgtoollogs/emca in the format: $ORACLE_HOME/cfgtoollogs/emca/$ORACLE_SID.

RAC:

If the database is a RAC database, for some specific operations like add and remove of an instance a new subdirectory is created for each instance under the $ORACLE_HOME/cfgtoollogs/emca/db_unique_name directory.

emca creates the log files only on the node of the cluster from where emca is running from

For example, for a RAC database with two instances there will be emca log files in the following locations:

1. $ORACLE_HOME/cfgtoologs/emca
2. $ORACLE_HOME/cfgtoollogs/emca/prod
3. $ORACLE_HOME/cfgtoollogs/emca/prod/prod1 (SID of the first RAC instance on the database prod)
4. $ORACLE_HOME/cfgtoollogs/emca/prod/prod2 (SID of the second RAC instance on the database prod)

EMCA 11.1

In 11.1 the emca log files are located in the $ORACLE_BASE/cfgtoollogs/emca directory. The filename is the format emca_timestamp.log where timestamp is in the format: YYYY-MM-DD_HH12-MI-AMP/PM.log. An example of a filename is: emca_2009-04-17_10-45-10-AM.log. A new fiile is created each time emca is executed. Then for each database a sub-directory is created under $ORACLE_BASE/cfgtoollogs/emca – $ORACLE_HOME/cfgtoollogs/emca/db_unique_name.

How to get additional data into the emca.log?

1. Backup the file emcalog.properties in $ORACLE_HOME/sysman/config
2. Edit the file emcalog.properties to change the value of the parameter shown below:

oracle.sysman.emcp.level = CONFIG to oracle.sysman.emcp.level = FINER

RAC Databases on Clustered Nodes (valid for emca 10.1, 10.2 and 11.1)

What is managed in Oracle 10g ASMM

What is Managed Automatically through 10g ASMM

The Automatic Shared Memory Management functionality is enabled when you set SGA_TARGET to a value greater than 0 in 10g+. ASMM will then attempt to manage what it thinks is the best size for the following components in the SGA:

Shared Pool
Large Pool
Java Pool
Buffer Cache (the default on managed by db_cache_size)
Streams Pool (10g R2+)

The other buffer caches (managed through parameters DB_nK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE), Log Buffer and Fixed SGA areas are not automatically tuned by MMAN. However these settings do affect the actual memory available to MMAN in the SGA_TARGET setting.

The SGA_TARGET can be changed dynamically up to the setting for SGA_MAX_SIZE. However, keep in mind there are limitations for some platforms on how LOCK_SGA works with memory at the OS level.

As an illustration how this works we’ll use an example where the SGA_TARGET is set to 4 GB.

In addition to specifying a value for SGA_TARGET you have also set values for DB_KEEP_CACHE_SIZE, LOG_BUFFER and DB_4K_CACHE_SIZE and for this example we’ll use the values 256M for db_keep_cache_size, 200M for the log_buffer size and 512M for the db_4k_cache_size.

We also elected to manually set the size for the shared pool to 1 GB (SHARED_POOL_SIZE=1G).

Now you want to know how does this affect MMAN. Well this means that MMAN can not manage or auto-tune the memory components will all 4 GB of memory. MMAN can only access 2,206,203,904 bytes.

The following breakdown serves to illustrate how the memory is allocated in the SGA:

Log Buffers                              209,715,200
Keep Buffer Cache                  268,435,456
4K Buffer Cache                      536,870,912
Shared Pool                         1,073,741,824
==============================
Total                                        2,088,763,393

Note: (by manually assigning a value to the shared pool you are telling Oracle that the size of the shared pool can not shrink below this value)

There are benefits to manually choosing a value for the auto-tuned components of the SGA. However it does have an impact on the amount of memory that MMAN has access to when it needs to grow and shrink the various components.

The following is a general rule of thumb for setting up ASMM:

SGA_TARGET = 256M* # of CPUs

The above rule of thumb makes a number of assumptions about your application’s memory usage. It is simply a starting point, you should identify a starting point and run queries to determine the appropriate size of the pools for your specific application. Some of the following assumptions are:

• Run queries and observe the memory trends to determine the minimum size for the various pools to meet the needs of your application
• Set the SGA_TARGET to 25% above the sum of the fixed and hard-coded settings
• Start with a set of hard-coded values for the pool parameter settings in your initial Oracle init file.
• Select a value for the SGA_MAX_SIZE equal to 25% above the setting for SGA_TARGET. This valuue won’t work for every environment. Memory for SGA_MAX_SIZE is allocated by Oracle at startup. By setting it to a value lower than SGA_MAX_SIZE it will provide you will the ability to bump up the size of the SGA_TARGET which may benefit your application performance.

After a reading a tremendous amount of articles, books and papers I’ve found that it is best to set a value as the minimum size for the above pools in your spfile rather than permitting Oracle to set them for you. You need to identify the base values as your starting point as you query, observe and determine what the values should be for your environment going forward.

As a starting point review the data returned by querying v$sga_dynamic_components and manually assign values to the pools at 70 to 75% of the current size. If there are indications of failed attempts to shrink the shared pool below that number over time, then decrease the default setting by another 10% and monitor the environment to determine if that value is more appropriate.