Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Friday, September 28, 2012

Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite [ID 1057802.1]



This Document contains 5 topics.



1. Generic Tips

2. Transaction Manager (TM).

3. Parallel Concurrent Processing (PCP) Environment.

4. Tuning Output Post Processor (OPP).

5. Concurrent Processing Server Tuning



Generic Tips

1) Sleep Seconds - is the number of seconds your Concurrent manager waits between checking the list of pending concurrent requests (concurrent requests waiting to be started). A manager only sleeps if there are no runnable jobs in the queue.



Tip: During peak time, when the number of requests submitted is expected to be high, Set the sleep time to a reasonable wait time(e.g. 30 seconds) dependent on the average run time and to prevent backlog. Otherwise set the sleep time to a high number (e.g. 2 minutes) . This avoids constant polls to check for new requests.



2) Increase the cache size (number of requests cached) to at least twice the number of target processes.



For example, if a manager's work shift has 1 target process and a cache value of 3, it will read three requests, and try to run those three requests before reading any new requests.



Tip: Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.

This is only guidance and a balance needs to struck in tuning the cache, so with fast jobs you need to cache to get enough work for a few minutes. With slow jobs, a small queue helps should you need to reprioritize requests.



3) Create specialized concurrent managers to dedicate certain process either short or long running programs to avoid queue length.



4) To maximize throughput consider reducing the sleep time of the Conflict Resolution Manager (CRM). The default value is 60 seconds. You can consider setting to 5 or 10 seconds.



5) Avoid enabling an excessive number of standard or specialized managers. It can degrade the performance due polling on queue tables (FND_CONCURRENT_REQUESTS...). You need to create specialized managers only if there is a real need.



6) Set the system profile option "Concurrent: Force Local Output File Mode" to "Yes" if required . You need to apply patch 7530490 for R12 (or) 7834670 for 11i to get this profile.



Refer Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance



Note:- The profile option "Concurrent: Force Local Output File Mode" is set to "No" by default. After applying the patch, set the profile option to YES will cause FNDCPPUR to always access files on the local file system, hence FNDCPPUR will remove the OS files faster.To enable this feature, All Concurrent Manager nodes must be able to access the output file location via the local filesystem



7) Truncate the reports.log file in log directory. Refer Note.844976.1 for more details



Truncation of file "reports.log" is a regular maintenance work of Application DBA. Make sure that reports log file size should not increase to its maximum limit of 2 GB. There is no purge program to truncate file "reports.log". This maintenance needs to be done manually and regularly depending on number of concurrent program which uses "reports.log". You can safely truncate "reports.log".



The "reports.log" file can be located under $APPLCSF/$APPLLOG.



8) Ensure "Purge Concurrent Request and/or Manager Data, FNDCPPUR," is run at regular intervals with "Entity" parameter as "ALL". A high number of records in FND_CONCURRENT tables can degrade the performance.



Additionally, the following are very good methods to follow for optimizing the process:

• Run the job in hours with low workload. Doing this after hours will lessen the contention on the tables from running against your daily processing.

• To get the requests under control, run the FNDCPPUR program with Age=20 or Age=18 would be a good method. That means, all requests older than 18 or 20 days will be purged.

• Once the requests are under control, run the FNDCPPUR program with Age=7 to maintain an efficient process. This would solely depend on the level of processing that is performed at your site

9) Ensure that the log/out files are removed from the locations shown below as you run "Purge Concurrent Request and/or Manager Data program".



$APPLCSF/$APPLLOG

$APPLCSF/$APPLOUT



In the event that it does not remove the log/out files, over a period of time it will slow down the performance. Please refer to the following note which suggests the patch which fixes it.



Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance





10) Defragment the tables periodically to reclaim unused space / improve performance



FND_CONCURRENT_REQUESTS

FND_CONCURRENT_PROCESSES

FND_CRM_HISTORY

FND_ENV_CONTEXT

FND_TEMP_FILES





HOW TO DEFRAGMENT



10.1) alter table . move;



10.2) Note that, some indexes might become unusable after table is moved, check the index status from dba_indexes for the table moved and rebuild them too as explained in next bullet.



select owner, index_name, status from dba_indexes

where table_owner = upper('&OWNER') and

table_name = upper('&SEGMENT_NAME');



10.3) alter index . rebuild online;



Note: Ensure the tablespace in which the object currently exists has got sufficient space before you move/defragment . Always take backup of the tables before moving the data. It is recommended to perform ths action on Test instance initially then testing thoroughly before performing it on Production instance.



10.4) You will need to collect the statistics for the tables.



For example:

exec fnd_stats.gather_table_stats ('APPLSYS','FND_CONCURRENT_REQUESTS',PERCENT=>99);

Transaction Manager (TM)

11 ) Profile Concurrent:Wait for Available TM - Total time to wait for a TM before switchover to next available TM. Consider setting this to 1 (second).



12) Ensure enough TMs exist to service the incoming request load.



13) When the load is high, set the following profile to optimum values to achieve better results.



PO: Approval Timeout Value - Total time for workflow call (When initiated from Forms) to time out.



14) Set the sleep time on the Transaction Manager to a high number (e.g. 10 minutes), this avoids constant polls to check for shutdown requests.

Parallel Concurrent Processing (PCP) Environment

15) If the failover of managers is taking too long refer to Note:551895.1: Failover Of Concurrent Manager Processes Takes More than 30 Minutes



16) Refer NOTE:1389261.1 when you are in the process of implementing PCP.



17) Set profile option 'Concurrent: PCP Instance Check' to 'OFF' if instance-sensitive failover is not required. Setting it to 'ON' means that concurrent managers will fail over to a secondary application tier node if the database instance to which it is connected goes down.



18)Transaction Manager uses DBMS_PIPE to communicate with application session prior to 11i.ATG_PF.H RUP3. DBMS_PIPE in turn uses OS Pipe.We might use Advance Queue(AQ) with 11i.ATG_PF.H RUP3 by setting System Profile Concurrent: TM Transport Type to QUEUE.



Note Pipes are more efficient but require a Transaction Manager to be running on each DB Instance (RAC). So you might want to use "Queue" for easy maintenance.



19) Add these parameters depends on your Database version



+ _lm_global_posts=TRUE

+ _immediate_commit_propagation=TRUE (11g RAC)

+ max_commit_propagation_delay=0 (9i RAC)



20) To speed up the PCP Failover ,Tune the below parameters.

• Kernel parameters (Find the analogous parameter for your platform)

tcp_keepalive_intvl

tcp_keepalive_probes

tcp_keepalive_time ( Do not set this value to low; since it will then use up your network resources with unnecessary traffic)

• DCD (Dead connection detection) setup; sqlnet.ora from the Database Tier

sqlnet.expire_time

• Environment Variable at Concurrent Manager Tier.

FDCPTRW

• PMON Cycle & Sleep Intervals for ICM (internal Concurrent Manager) setup.

Navigation OAM -> SiteMap -> Monitoring -> Internal Concurrent Manager Link(Under Availability) -> "View Status" -> "Edit ICM Runtime Parameters"

• Enable Reviver.

What is FNDREVIVER and How Is It Set? (Document : 466752.1)

Tuning Output Post Processor (OPP)

In order to tune the OPP to improve performance refer the below Note.

NOTE:1399454.1 Tuning Output Post Processor (OPP) to Improve Performance

Concurrent Processing Server Tuning

1. Any Concurrent Processing (CP) server tuning or load balancing needs are to be addressed by Oracle Consulting. There are way too many site specific factors that needs to be considered for optimum CP throughput: from machine hardware, to user request volume, to required Work Shifts, to programs run time characteristics (long / short running)--not to mention also testing and benchmarking. Such a tasks, is beyond the scope of ATG Support.



ATG support would be glad to investigate a failing manager or program issue; however, CP performance issues due to increased concurrent request volume or due to a new installation needs to be addressed by Oracle Consulting.



2. The "Tuning Concurrent Processing" chapter of the white paper "A Holistic Approach To Performance Tuning Oracle Applications Systems Release 11 and 11i" Note 69565.1 may provide some basic insight. Also reference the "Defining Concurrent Managers" and the "Setting Up and Starting Concurrent Managers" chapters of the "Oracle Applications System Administrator's Guide - Configuration".



3. As per Note 69565.1 "A Holistic Approach to Performance Tuning Oracle Applications Systems", "50% of concurrent processing performance tuning is in the business!"



4. Visit the Concurrent Processing Product Information Center (PIC) Note 1304305.1 for additional performance and setup documentation.

HAPPY LEARNING!

Friday, September 21, 2012

Steps for Creating ACFS Mount For A New Oracle_Home

Use below steps for creating ACFS mount for new DB binary home.


Create ACFS Volume - OH
=======================
How To Resize An ACFS Filesystem/ASM Volume (ADVM) [ID 1173978.1]


Diskgroup should have attribute setup

'compatible.asm' = '11.2', 'compatible.advm' = '11.2';

sqlplus "/as sysasm"

SQL> alter diskgroup DATA_1 set attribute 'compatible.asm' = '11.2';
SQL> alter diskgroup DATA_1 set attribute 'compatible.advm' = '11.2';

Create the next volume (10GB) in the ACFSDG diskgroup

SQL> ALTER DISKGROUP DATA_1 ADD VOLUME DB11203_3 SIZE 10G ;

Query the new volume name

ASMCMD> volinfo -a
Diskgroup Name: DATA_1

         Volume Name: DB11203_3
         Volume Device: /dev/asm/DB11203_3
         State: ENABLED
         Size (MB): 10240
         Resize Unit (MB): 32
         Redundancy: UNPROT
         Stripe Columns: 4
         Stripe Width (K): 128
         Usage:
         Mountpath:



Create the new ACFS filesystem on the volume above (only on one node):

[grid@dbaasm ~]$ /sbin/mkfs -t acfs -b 4k /dev/asm/DB11203_3-15 -n "DB11203_3" --(as grid user - oracle)

mkfs.acfs: version                   = 11.2.0.3.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/DB11203_2-15
mkfs.acfs: volume size               = 10737418240
mkfs.acfs: Format complete.


File system should not be added to the Oracle ACFS mount registry - skip it

Setup ORACLE_BASE - root, oracle sessions
export ORACLE_BASE=/u01/app/oracle # this is the option we have at the moment as standard - use this option
 
Setup GRID_HOME to match the current GI installation - root, oracle sessions

export GRID_HOME=/u01/app/grid/product/11.2.0.3/grid_1

Create mount directory to link to OH - as root or oracle if have enough grants

for i in `$GRID_HOME/bin/olsnodes`
do
echo "Node: $i.."
ssh $i "mkdir -p ${ORACLE_BASE}/product/11.2.0.3/db_3;chown oracle:oinstall ${ORACLE_BASE}/product/11.2.0.3/db_3"
done

Enable a file system to be automounted in GI - root
In an Oracle Grid Infrastructure clusterware configuration, run srvctl add filesystem to enable a file system to be automounted when an Oracle Database home is installed on the Oracle ACFS file system.

cd ${GRID_HOME}/bin
./srvctl add filesystem -d /dev/asm/DB11203_3-15 -v DB11203_3 -g DATA_1 -m ${ORACLE_BASE}/product/11.2.0.3/db_3 -u oracle




Maintenance commands

srvctl status filesystem -d /dev/asm/DB11203_3-15
srvctl start filesystem -d /dev/asm/DB11203_3-15
srvctl stop filesystem -d /dev/asm/DB11203_3-15

/sbin/acfsutil size ${ORACLE_BASE}/product/11.2.0.3/db_3

$ /sbin/acfsutil info fs /u01/app/oracle/product/11.2.0.3/db_3
$ /sbin/acfsutil info fs
/u01/app/oracle/product/11.2.0.3/db_3
    ACFS Version: 11.2.0.3.0
   flags:        MountPoint,Available
    mount time:   Mon Mar 12 11:17:41 2012
    volumes:      1
    total size:   10737418240
    total free:   10573426688
    primary volume: /dev/asm/DB11203_3-15
        label:                 DB11203_3
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 7681
        size:                  10737418240
        free:                  10573426688
        ADVM diskgroup         DATA_1
        ADVM resize increment: 33554432
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0
    replication status: DISABLED


sqlplus "/as sysasm"

SQL> column VOL_DEVICE format A30
SQL> column FS_NAME format A40
SQL> set linesize 100
SQL> select FS_NAME, VOL_DEVICE, TOTAL_MB , FREE_MB from V$ASM_ACFSVOLUMES;

FS_NAME                                  VOL_DEVICE                       TOTAL_MB    FREE_MB
---------------------------------------- ------------------------------ ---------- ----------
/u01/app/oracle/product/11.2.0.3/db_3    /dev/asm/DB11203_3-15               10240 10083.6055

$ asmcmd

ASMCMD> volinfo -a
Diskgroup Name: DATA_1

         Volume Name: DB11203_3
         Volume Device: /dev/asm/DB11203_3-15
         State: ENABLED
         Size (MB): 10240
         Resize Unit (MB): 32
         Redundancy: UNPROT
         Stripe Columns: 4
         Stripe Width (K): 128
         Usage: ACFS
         Mountpath: /u01/app/oracle/product/11.2.0.3/db_3

crsctl stat res -w "TYPE = ora.acfs.type"
crsctl stop res -n
crsctl start res -n

HAPPY LEARNING!

Wednesday, September 12, 2012

Scared of shutdown abort?

Identifying Which Instance Owns Which Shared memory and Semaphore Segments

Sometimes it happens that after a crash or after an abnormal shutdown the oracle database instance does not release the shared memory or semaphores.

And if you do not know how to find out the unreleased shared memory or semaphores, this how you do it.

How to find which instance owns which shared memory and semaphores

On a Unix machine give the following commands

table1



$ ipcs -b

IPC status from /dev/mem as of Mon Dec 23 11:47:25 IST 2002
T ID KEY MODE OWNER GROUP QBYTES
Message Queues:
q 0 0x4107001c --rw-rw---- root printq 4194304
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 11 0x00001515 --rw-rw-rw- root system 16
m 12 0x435dce60 --rw-rw-rw- root system 8024
m 13 0x0d01c353 --rw-rw-rw- root system 1440
m 179306510 0xffffffff D-rw------- ias9 dba 1052672
m 20709394 0xec8bc6b4 --rw-r----- ora8i732 dba 328286208
m 39452692 0x62a7d5a4 --rw-r----- oracle9i dba 520896512
m 64487446 0x076d8fb0 --rw-r----- oracle9i dba 437010432
m 56492055 0x02fa8d7c --rw-r----- ora8i732 dba 293888000
m 28442648 0xbe0bab6c --rw-r----- oracle9i dba 520896512
m 4325402 0xc4d72a50 --rw-r----- oracle9i dba 470073344
m 262171 0xffffffff D-rw-r----- oracle9i dba 470073344
m 19005482 0x493f947c --rw-r----- oracle9i dba 486850560
T ID KEY MODE OWNER GROUP NSEMS
Semaphores:
s 393216 00000000 --ra-ra-ra- imnadm imnadm 4
s 1 0x6201c1b8 --ra-r--r-- root system 1
s 11 0x580038a1 --ra-ra-ra- root system 1


The above table shows the output of the ipcs –b command. This displays the shared memory and the semaphores.

Now how to know which one to kill?

Say the instance ORCL has not released the memory or semaphores.

Do the following



$ export ORACLE_SID=ORCL
$ svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> oradebug ipc
Information written to trace file.
SVRMGR> exit
Server Manager complete.
$


In case you get the error



SVRMGR> oradebug ipc
ORA-00074: no process has been specified
SVRMGR>


Then find pid of a background process.



$ ps –ef|grep smon [or pmon]

oracle9i 278798 1 0 11:57:26 - 0:00 ora_smon_FFSYSHCM
ora8i732 288438 1 0 Dec 21 - 0:02 ora_smon_CSSREP1
oracle9i 315834 1 0 11:49:58 - 0:00 ora_smon_FF4HCM
oracle8i 367370 1 0 11:47:41 - 0:00 ora_smon_PGUATOMR
ora8i732 25398 1 0 Dec 21 - 0:32 ora_smon_ORCL


This will give the pid of the instance ORCL which is 25398



SVRMGR> oradebug setospid 25398
SVRMGR> oradebug ipc
Information written to trace file.
SVRMGR> exit
Server Manager complete.
$


This will write down a trace file in your $ORACLE_BASE/admin/ORCL/udump

Extract from the trace file.

Table2


"ora_48170_orcl.trc" 78 lines, 3673 characters
/ora8i732/app/product/admin/ORCL/udump/ora_48170_orcl.trc
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
ORACLE_HOME = /ora8i732/app/product/8.1.7
System name: AIX
Node name: tcs6m1
Release: 3
Version: 4
Machine: 000F2F7F4C00
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 48170, image: oracle@tcs6m1 (TNS V1-V3)

*** 2002-12-23 11:46:31.286
*** SESSION ID:(7.1) 2002-12-23 11:46:31.207
Dump of unix-generic skgm context
areaflags 00000037
realmflags 0000000f
mapsize 00001000
protectsize 00001000
lcmsize 00001000
seglen 00001000
largestsize 00000000c0000000
smallestsize 0000000000400000
stacklimit 0
stackdir -1
mode 640
magic acc01ade
Handle: 2011d3d8 `/ora8i732/app/product/8.1.7ORCL'
Dump of unix-generic realm handle `/ora8i732/app/product/8.1.7ORCL', flags = 00000000
Area #0 `Fixed Size' containing Subareas 0-0
Total size 0000000000011f94 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
0 0 56492055 0000000030000000 0000000030000000
Subarea size Segment size
0000000000012000 0000000011846000
Area #1 `Variable Size' containing Subareas 2-2
Total size 0000000006a99000 Minimum Subarea size 00100000
Area Subarea Shmid Stable Addr Actual Addr
1 2 56492055 000000003ad10000 000000003ad10000
Subarea size Segment size
0000000006b00000 0000000011846000
Area #2 `Database Buffers' containing Subareas 1-1
Total size 000000000acfe000 Minimum Subarea size 00002000
Area Subarea Shmid Stable Addr Actual Addr
2 1 56492055 0000000030012000 0000000030012000
Subarea size Segment size
000000000acfe000 0000000011846000
Area #3 `Redo Buffers' containing Subareas 3-3
Total size 000000000002a000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
3 3 56492055 0000000041810000 0000000041810000
Subarea size Segment size
000000000002a000 0000000011846000
Area #4 `Lock Manager' containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
4 5 56492055 0000000041842000 0000000041842000
Subarea size Segment size
0000000000002000 0000000011846000
Area #5 `Java' containing Subareas 4-4
Total size 0000000000008000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
5 4 56492055 000000004183a000 000000004183a000
Subarea size Segment size
0000000000008000 0000000011846000
Area #6 `skgm overhead' containing Subareas 6-6
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
6 6 56492055 0000000041844000 0000000041844000
Subarea size Segment size
0000000000002000 0000000011846000
Dump of AIX-specific shared memory info
Number of internal AIX shared memory segments 1
Segment Shmid Size Attach Addr
1 56492055 293888000 30000000
Maximum processes: = 315

Semaphore identifiers:
393216


From table 1 and table 2 you can see that 56492055 is the shared memory that is no longer required.

Use the ipcrm –m to remove the shared memory segment.


$ ipcrm –m 56492055


Similarly you can remove the semaphores


$ ipcrm –s 393216.

Or

To find the Shared Memory id and Semaphore id you can use:


$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "ORCL" :
Shared Memory:
ID KEY
56492055 0x8a85a74c
Semaphores:
ID KEY
393216 0x4bd4814c
Oracle Instance alive for sid "ORCL"


This will save lot of your valuable time when an instance crashes and the shared memory and semaphore is not released, preventing you from starting up the database again.

HTH
Regards

PS:
or simply do


ipcrm -m `ipcs -map|awk '$9==0 {print $2}'`


$9 gives NATTACH (The no of processes attached to shared memory segment).
For the killed instance this value is 0

$2 gives the shared memory key

HAPPY LEARNING!