Speeding up Oracle Applications/Payroll processing

% sqlplus apps/apps

To retrieve the current value of ‘THREADS’ parameter:

SQL> select parameter_value
from pay_action_parameters
where parameter_name = 'THREADS';
To set the value for the number of payroll threads:

SQL> update pay_action_parameters
set parameter_value = <number_of_threads>
where parameter_name = ‘THREADS’;

SQL> commit;

if you don't have thread value use the below sql :
SQL> insert into PAY_ACTION_PARAMETERS values ('THREADS', 'XXXX');

thank you
Osama mustafa 

Gather Schema Statistics fails with Ora-20001

Cause: FDPSTP failed due to ORA-06550: line 1, column 7:
PLS-00307: too many declarations of ‘ GATHER_ALL_COLUMN_STATS’ match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Solution :
To Check which objects or tables are locked

TO Unlock all the tables in a schema at once :
sql> exec dbms_stats.unlock_schema_stats(‘schema_owner’);
e.g : sql> exec dbms_stats.unlock_schema_stats(‘apps’);
TO Unlock all Individual tables in a schema at once
sql> exec dbms_stats.unlock_schema_stats(‘table_owner’,’table_name’);
e.g : sql > exec dbms_stats.unlock_schema_stats(‘AR’,’AR_REV_REC_QT’); 
There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.
Following SQL should have returned one row , not two.
SQL> select a.column_name, nvl(a.hsize,254) hsize
where table_name = ‘JE_BE_LINE_TYPE_MAP’
order by column_name;
—————————— ———-
2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
— identify duplicate rows
select table_name, column_name, count(*)
group by table_name, column_name
having count(*) > 1;
— Use above results on the following SQL to delete duplicates
where table_name = ‘&TABLE_NAME’
and column_name = ‘&COLUMN_NAME’
and rownum=1;
— Use following SQL to delete obsoleted rows
where (table_name, column_name) in
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null

Conncurrent manger Problems

I Post this after suffering with Concurrent manager  in customer side

Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase

Below are several different possible solutions to the problem where concurrent
requests are stuck in pending status:

1. When shutting down the concurrent manager are there any FNDLIBR processes still running at the OS level? If so, do a kill -9 on them. Then restart the concurrent manager.

2. Try Relinking $FND_TOP.
3. Rebuild the concurrent manager views. As applmgr run the following from
the OS:

This is non-destructive. Concurrent Manager views can be rebuild by running the following command at the command line:

Ensure that concurrent manager is shutdown.


restart the concurrent mgr.

4. Another possibility is that the Profile Option “Concurrent: OPS Request Partitioning” is set to OFF (by default). Unless Concurrent OPS is being used, the setting for this profile option should be NULL.

5. Yet another possibility is that System Profile Option: Concurrent Active
Requests is set to 0.

1. Log into Oracle Applications as SYSADMIN.
2. Select System Administrator responsibility.
3. Navigate to PROFILE –> SYSTEM.
4. Query for %CONC%ACTIVE%.
5. Change the profile option for Concurrent: Active Request Limit to Null (blank).
6. Exit Oracle Applications and log in again for the change to take affect.
7. Run a new concurrent request.

6. Another possibility is that Concurrent managers were brought down, while an
outstanding request was still running in the background. In which case, Update
the FND_CONCURRENT_REQUESTS table as follows:

sql> update fnd_concurrent_requests
set status_code=’X’, phase_code=’C’
where status_code=’T’;
sql> commit;

7. The control_code for concurrent_queue_name = ‘FNDCRM’ is ‘N’ in the FND_CONCURRENT_QUEUES table, which means ‘Target node/queue unavailable’. This value should be NULL (CRM is running; target and actual process amount are the same), or ‘A’ (‘Activate concurrent manager’ control status).

Set the control_code to ‘A’ in fnd_concurrent_queues for the Conflict Resolution Manager:

1. Logon to Oracle Applications database server as ‘applmgr’.
2. Verify the Applications environment is setup correctly ($ORACLE_HOME and $ORACLE_SID).

3. Logon to SQL*Plus as ‘APPS’ and run the following SQL statement:

update fnd_concurrent_queues
set control_code = ‘A’
where concurrent_queue_name = ‘FNDCRM’;


4. Verify the status of the concurrent managers through the
Concurrent -> Manager -> Administer form.

If the CRM is still not active, bounce (deactivate, activate) the Internal Concurrent Manager. This is done through the Concurrent -> Manager ->

Administer form from the ‘System Administrator’ responsibility. It can also be done through the CONCSUB command at the command level.

Setting the control_code to ‘A’ in the fnd_concurrent_queues table for the Conflict Resolution Manager indicates that this concurrent manager is to be activated with the parameter values specified through this table for this manager (MAX_PROCESSES, CACHE_SIZE, etc).

8. What is the cache size? Try increasing it then bounce the concurrent manager.

If concurrent requests are rarely prioritized and there are managers that service short-running requests, consider setting the cache size to equal at least twice the number of target processes. This increases the throughput of the concurrent manaagers by attempting to avoid any sleep time. For example, if more than one manager or worker processes the same type of requests with only a small cache size, it may be unable to process any jobs in a single processing cycle, because other processes have already run the cached requests. When this happens, it is important to note that the manager will sleep before refreshign its cache. To increase manager throughput where there are sufficient requests of the required type in the queue, increase the cache size to improve the chance of the manager finding work to process and thus avoid having to enter a sleep phase.

TIP: Ensure that the system is not resource-constrained before attempting to increase the rate of concurrent processing in this way; otherwise, these changes may actually reduce concurrent processing throughput because jobs take longer to run..

Enter the number of requests your manager remembers each time it reads which requests to run. For example, if a manager’s workshift has 1 target process and a cache value of 3, it will read three requests,, and will wait until these three requests have been run before reading new requests.

In reading requests, the manager will only put requests it is allowed to run into its cache. For example, if you have defined your manager to run only Order Entry reports then the manager will put only Order Entry requests into its cache.

If you enter 1, the concurrent manager must look at its requests list each time it is ready to process another request. By setting the cache size at a higher number, the concurrent manager does not have to read its requests list each time it runs a request. However, the manager does not recognizea nay priority changes you make for a particular request if it has already read that request into its cache. Further, even if you give a higher priority to a new request, that new request must wait until the buffer isempty and the manager returns to look at the requests list. That request may have to wait a long time if you set the buffer size to a high number.

You should use cache size to tune your concurrent managers to work most efficiently for you site’s needs. If your organization tends to reprioritize jobs going to a certain manager, that manager should have its buffer size set fairly low.

*Suggestion: *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.

9. Please check the sleep seconds set for the concurrent manager which runs the report that is taking long time for completion. Reducing the sleep time will improve performance.

1. Check for the value set for the sleep seconds for the concurrent manager using the following path: System Administrator Responsibilty -> Concurrent -> Manager -> Define.

2. Check the processes running for the concurent manager using the following command:

ps -ef | grep ‘INVLIBR’

As all concurrent programs are dealt by INVLIBR, the number of processes returned here must tally with the value specified for the processes defined for the concurrent manager.

3. Also, check for the duration for which the processes are sleeping. Reducing the value assigned for it would improve performance.

AWR Reports Types

list from the home ($ORACLE_HOME/rdbms/admin) with (mostly) the one-line description from the start of file. I’ve put the most useful ones in the first list:

Basic AWR reports
Standrad SQL Statement Report
Period Diff on current Instance
Workload Repository Report Instance (RAC)
Script to Output General AWR Information

 For most people the awrrpt.sql and awrsqrpt.sql are likely to be sufficient, but the “difference between two periods” can be very useful – especially if you do things like regularly forcing an extra snapshot at the start and end of the overnight batch so that you can (when necessary) find the most significant differences in behaviour between the batch runs on two different nights.

If you get into the ‘RAC difference report’ you’ll need a very wide page – and very good eyesight !

There are also a lot of “infrastructure and support” bits – some of the “input” files give you some nice ideas about how you can write your own code to do little jobs like: “run the most recent AWR report automatically”:


AWR Baseline Migrate
AWR LOAD: load awr from dump file
AWR Extract
Get inputs for diff report
Workload Repository Compare Periods Report
Get inputs for global diff reports
Workload Repository Global Compare Periods Report
AWR Global Input
Workload Repository RAC (Global) Report
AWR Input Name
Get inputs for AWR report
Workload Repository SQL Report Instance

There are also a couple of deceptively named files that you might miss in 11.2:

Server Performance AWR RAC report
AWR IO Intensity Report
Streams Performance Advisor report


 Thank you
Osama Mustafa


Understand AWR Report

I mention earlier how to generate AWR , But after you did this how can you read it .

1) The first thing to be checked in AWR report is the following:-

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 112 11-Jun-09 00:00:57 191 6.7
End Snap: 113 11-Jun-09 01:00:11 173 7.4
Elapsed: 59.23 (mins)
DB Time: 710.73 (mins)

Check the “DB Time” metric. If it is much higher than the elapsed time, then it indicates that the sessions are waiting for something.

Here in this example, the Elapsed Time is around 60 minutes while the DB Time is around 700 minutes. This means that 700 minutes of time is spent by the sessions on waiting.

2) Next thing to be looked is the following:-

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.67 In-memory Sort %: 100.00
Library Hit %: 98.60 Soft Parse %: 99.69
Execute to Parse %: 5.26 Latch Hit %: 99.31
Parse CPU to Parse Elapsd %: 12.78 %Non-Parse CPU: 99.10 
As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
3) Then comes the Shared Pool Statistics.
Shared Pool Statistics
Begin End
Memory Usage %: 85.49 80.93
% SQL with executions>1: 42.46 82.96
% Memory for SQL w/exec>1: 47.77 81.03 

The memory usage statistics of shared pool is shown.
Idealy this should be lesser. If it is very high like beyond 90, this shows the contention in the shared pool.

4) Next thing to be looked after is the Top 5 Timed Events table.

This shows the most significant waits contributing to the DB Time.

Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 4,076,086 28,532 7 66.9 User I/O
CPU time 11,214 26.3
Backup: sbtbackup 4 4,398 1,099,452 10.3 Administrative
log file sync 37,365 2,421 65 5.7 Commit
log file parallel write 37,928 1,371 36 3.2 System I/O 

Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.

5) Then , SQL Statistics can be checked.

SQL Statistics
SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads 

SQL Statistics section would have commonly the above four sections.

Each section shows the list of SQLs based on the order of the respective metric.
For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order
of the Elapsed Time. High resource consuming SQLs can be spotted out and meant for

Note: All the above four sections of SQL Statistics show the list of SQLs in descending order.
i.e, For ex: Highest elapsed time is shown as first.

6) Then comes the IO Stats section.

This shows the IO Statistics for each tablespaces in the database.

As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30, add myself(not greater that 30)
which is considered to be IO bottleneck.

Tablespace IO Stats
ordered by IOs (Reads + Writes) desc 
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
TEMP 3,316,082 933 4.91 1.00 28,840 8 0 0.00
DAT1 520,120 146 16.06 1.21 185,846 52 902 13.00
DAT3 93,411 26 42.82 2.98 13,442 4 16 23.13
DAT2 98,171 28 91.97 7.97 5,333 2 325 34.89 

In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.

7) Then , Advisory Statistics can be checked.

This section shows the following:-

Buffer Pool Advisory
PGA Aggr Summary
PGA Aggr Target Stats
PGA Aggr Target Histogram
PGA Memory Advisory
Shared Pool Advisory
SGA Target Advisory
Streams Pool Advisory
Java Pool Advisory 

It is very commonly used to check the advisories for the most important SGA structures like shared pool, buffer cache etc and PGA.

8) Then finally, init.ora Parameters is shown which shows the list of parameters set at instance level.

init.ora Parameters

All the above said sections except the DB Time can be checked from Statspack report also.

The statspack snapshots are not generated automatically as in AWR.It has to be generated during the problem period as follows:-

Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report

exec statspack.snap
wait for 60 minutes
exec statspack.snap

Please run $ORACLE_HOME/rdbms/admin/spreport.sql
and specify BEGIN and END ID’s of the snapshots taken during the problem.

Some Other Links Will Be Useful :

1-statspack examples.
2-Analyaz statspack.
3-Active Session History.
4-Statspack Article.
5-About Statspack.
6-Using Statspack.
7-AWR Reports

Thank you
Osama mustafa

Steps to Generate AWR Reports

Automatic Workload Repository

The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.

Step 1: Go to $ORACLE_HOME.

Step 2: Once in $ORACLE_HOME go to cd rdbms and then cd admin.

Step 3: Run command ‘ls –ltr awr*’.

Step 4: Go to ‘sqlplus / as sysdba

Step 5: In sqlplus the following is run ‘SQL> @awrrpt.sql. Select the format for the report as either ‘HTML’ or ‘TEXT’.

Step 6: Select number of days you want to go back or just hit enter for listing all
completed snapshots, if you press ENTER it will give you all snapshot .

Step 7: Then specify Begin and End snapshot Ids.

Step 8: Here you specify the name of the report or select the default name assigned.

Exit Sql , And Search for your report_name in the Same Directory .

 Just As Note :

@?/rdbms/admin/awrrpti.sql – Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.

@?/rdbms/admin/awrddrpt.sql – Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.

@?/rdbms/admin/awrinfo.sql – Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.

@?/rdbms/admin/awrrpt.sql – It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to choose the start and end snapshot it.

The Next Post will be How to read AWR Tips .


Osama Mustafa


Oracle has a very useful built-in function called SYS_CONTEXT. The syntax of this function goes like this:

SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session. Go ahead and run the following query:

from dual

Amazing , you can Use this Function in Oracle Security .


Thank you
Osama mustafa

Migrate the database From File System to ASM

I posted before Migration Database from ASM to File System,But today i will talk about How to migration File System to ASM and this is the common case I hope i will describe this topics so well,Please if you have any In-query Contact me,Comment i will answer you,Helping you is my pleasure:


1.configure flash recovery area.
2.Migrate datafiles to ASM.
3.Control file to ASM.
4.Create Temporary tablespace.
5.Migrate Redo logfiles
6.Migrate spfile to ASM.

step 1:Configure flash recovery area.

SQL> connect sys/sys@orcl as sysdba
SQL> alter database disable block change tracking;

Database altered.

SQL> alter system set db_recovery_file_dest_size=500m;

System altered.

SQL> alter system set db_recovery_file_dest=’+FRAG’;

System altered

step 2 and 3: Migrate data files and control file to ASM

Use RMAN to migrate the data files to ASM disk groups,All data files will be migrated to the newly
 created disk group, DATA
SQL> alter system set db_create_file_dest='+DATA';

System altered.

SQL> alter system set control_files='+DATA/control_new.ctl' scope=spfile;

System altered.

SQL> shutdown immediate

[oracle@rac1 bin]$ ./rman target /

RMAN> startup nomount
RMAN> restore controlfile from '/u01/new/oracle/oradata/mydb/control01.ctl';
RMAN> alter database mount;
RMAN> backup as copy database format '+DATA';
RMAN> switch database to copy;
RMAN> alter database open;

RMAN>Exit ;

SQL> conn sys/oracle as sysdba


SQL> select tablespace_name,file_name from dba_data_files;


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

USERS +DATA/mydb/datafile/users.261.705064915

UNDOTBS1 +DATA/mydb/datafile/undotbs1.259.705064821

SYSAUX +DATA/mydb/datafile/sysaux.258.705064283

SYSTEM +DATA/mydb/datafile/system.257.705063763

SQL> select name from v$controlfile;



step 4:Migrate temp tablespace to ASM.
SQL> alter tablespace temp add tempfile size 100m;

Tablespace altered.

SQL> select file_name from dba_temp_files;



otherwise,Create temporary tablespace in ASM disk group.


SQL> alter database default temporary tablespace temp1;

Database altered.

step 5:Migrate redo logs to ASM.
SQL> select member,group# from v$logfile;

you will find i above query the redo-log in file system start adding new one to ASM after this you can delete .

SQL> alter database add logfile group 5 size 5m;

Database altered.

SQL> alter database add logfile group 6 size 5m;

Database altered.

For example to drop , you can do the following :

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

Now we can say we finish But we have to do Two More Steps 

Add additional control file

SQL> connect sys/sys@ORCL as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

SQL> alter database backup controlfile to '+DATA/Control_new02.ctl';

Database altered.

SQL> alter system set control_files='+DATA/
Control_new.ctl '
Control_new02.ctl' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Step 6:Migrate spfile to ASM:

Create a copy of the SPFILE in the ASM disk group.
If the database is using an SPFILE already, then run these commands:

run {

But if you are not using One :

SQL> create spfile='+DISK/spfile' from pfile='$ORACLE_HOME/dbs/initORCL.ora';

 Thank you
Osama mustafa


Check memory On Linux

Sometime you as DBA you need to check your memory On Operating System Level, i will give you some command will useful to you :

1-free -m / free -g

m : megabytes
g : gigabytes

              total    used     free   shared  buffers   cached 
Mem: 503 451 52 0 14 293
-/+ buffers/cache: 143 360
Swap: 1027 0 1027

 another example :

free -t -m
       total       used       free     shared    buffers     cached
Mem: 750 625 125 0 35 335
-/+ buffers/cache: 253 496
Swap: 956 0 956
Total: 1707 625 1082

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 0 131620 35432 341496 0 0 42 82 737 1364 15 3 81 1

The below Command for clear cache memory in linux 

echo 3 > /proc/sys/vm/drop_caches
 you can make it as job in linux save in it crontab 
0 * * * * /root/clearcache.sh
Create a file in '/root' called 'clearcache.sh' with the following content:

sync; echo 3 > /proc/sys/vm/drop_caches
Thank you 
osama mustafa 

Duplicate Database Using RMAN / Clone Database

Step By Step : Cloning Database On the Same Server / Using Rman Duplicate From Active Database

The Below Link Contain Document Describe Step by Step how to Duplicate Database Using RMAN ,

Cloning Database / Duplicate Database Using RMAN


Thank you
Osama mustafa