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 :-

Friday, November 9, 2012

My First APEX Application For A Swedish Logistics Company Is In Production Now !!

After a long time went back writing SQL's and PL/SQL blocks, triggers, procedures & functions again.....140 Hrs of learning apex and working after my regular work has paid, Memories of my initial developer days, My first Oracle APEX 4.1 application is out in production for a Swedish logistics company.

Anybody interested in developing quick applications for yourself, Please start learning APEX, Its quick and smart and easy...

Below are the few screen shots of the application.

Oracle APEX 4.1
Oracle 11g R1 database
Oracle BI Publisher For Reports

Happy Learning!

Monday, October 15, 2012

My Oracle Support- Create Quick Bookmarks In Firefox

“Use Firefox Quick Searches to quickly access Oracle related information: DOCs, SRs, NOTEs, BUGs etc…”

lets add Firefox bookmark with search capability:
1) inside Firefox press Ctrl+Shift+B
2) at the left side select All Bookmarks -> Bookmarks Menu
3) press right button of mouse and select “New Folder”, type something like MyOracle
4) right click on newly created folder(MyOracle) and select “New Bookmark” and fill next fields:
Keyword: mn
press “Add” button to add bookmark.
5) now close Bookmarks Library window and go to Firefox address bar
6) type “mn 1269139.1″ and press “enter” – You will move directly to specified MOS NOTE!!!
So now we have quite useful shortcut to go directly to specific NOTE.
Lets add rest of shortcuts to Oracle resources:
Bookmark Name Keyword Location
MOS Note mn
MOS BUG Description bd
MOS Patch mp
MOS Patch Flash mpf,(page=PatchSearchResultsHome&id=gj46pr1y(search=%0A&incFamilyProds=false&flag=search))
MOS Service Request sr,(page=SRView(sr_number=%s))
MOS Service Request Html srh
MOS Search KB s
MOS Search BUG sb
MOS Search ALL sa
Search 11.2 Docs sd
Search Tahiti st


Sunday, October 14, 2012

Find The Details Of a Troublesome SQL That Affects Performance

When performace issues are reported most of the DBA's remain in confused state and the following questions keep running in their minds.

1) From where do I start, investigating?
2) What are the details that can help me isolate the troublesome part in the database?
3) How do I prove that my findings are correct and accurate.

Below are few technique's which can help a DBA to isolate a performance issue and pin point the culprit behind the database performance issue.

(a) You can use ADDM report, to find out the most expensive SQL's that are found (The recent SQLwith performance issue should be in the list) 


Below is the snippet of the O/P, which displays the expensive SQL's seen so far.
15 Most expensive SQL in the cursor cache

------------ ---------- -------------------------------------------------------
0z318y6g3uagc 2,733.88 declare l_retstat varchar2 ( 3 ) ; l_msg_data varchar2
8az1s9vjf7zk4 2,135.59 call$_propagation_procedure ( :0,
gqdfvwwknvrgp 1,309.21 select apps.xmlpr_transform.wf_event2MGW(source.user_da
dfc81r953uw0x 1,082.00 BEGIN xxg_oci_progress_wf_pkg.process_deferred_wf(:errb
6vgrdp67bc6cq 766.20 SELECT "A5"."ITEM_NUM" "SORT_ORDER","A5"."ATTRIBUTE1" "
g8102j8yynbwz 761.17 begin WF_STANDARD.CONTINUEFLOW (:v1, :v2, :v3, :v4, :v5

The above SQL's may be of your insterest now, find more details on their execution plan etc

Further enter the SQL_ID on to the prompts that can help you identify the the execution plan for the SQL_ID, You can directly check for any problem in the execution plan, Also find if the SQL is using dynamic execution plan.

Also, the report gives the rational's and other task_id created for doing a SQL profiling, If it finds a better execution plan.

Now that we talked about SQL profiling , that does not give a permanent fix to the troublesome SQL, So, we need more information to find if its really a bad SQL with a poor code.

Below information's can help us identify, If the SQL keeps changing the SQL plan very frequently, and there by creating too many cursors in library cache and leading to performance issue.


---------- ------------- --------------- ------------ ----------
10 8jc25wc5fvuyq 664152792 0 12459
14 8jc25wc5fvuyq 664152792 0 11476
11 8jc25wc5fvuyq 664152792 0 18313


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

10 8jc25wc5fvuyq BEDA0B2003005077C34D000202F41707D010CBD86FD5A4FE3F24E0434D0EBF0AA66F OMEGAP_OCI jdbc.OCI.OMARDataSource UPDATE XXG.XXG_ORD_STATUS_IN_JMS_QT tab set tab.user_data.text_lob = :1 where tab.msgid = :2  
(b) Check if the SQL is bind sensitive or Bind aware? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select is_bind_sensitive, is_bind_aware, sql_id, inst_id, child_number from gv$sql where sql_text like '%UPDATE XXG.XXG_ORD_STATUS_IN_JMS_QT tab set tab.user_data.text_lob%';
- - ------------- ---------- ------------
N N 5qcyw4ku1ujfb 9           0
N N 1r2pbduccrp3s 9           0
N N 5qcyw4ku1ujfb 16          0
N N 1r2pbduccrp3s 16          0
N N 5qcyw4ku1ujfb 10          0
N N 1r2pbduccrp3s 10          0
Y N 8jc25wc5fvuyq 10          0
N N 5qcyw4ku1ujfb 15          0
N N 1r2pbduccrp3s 15          0
N N 5qcyw4ku1ujfb 14          0
N N 1r2pbduccrp3s 14          0
Y N 8jc25wc5fvuyq 14          0
N N 5qcyw4ku1ujfb 8           0
N N 1r2pbduccrp3s 8           0
N N 5qcyw4ku1ujfb 2           0
N N 1r2pbduccrp3s 2           0
N N 5qcyw4ku1ujfb 7           0
N N 1r2pbduccrp3s 7           0
N N 5qcyw4ku1ujfb 3           0
N N 1r2pbduccrp3s 3           0
N N 5qcyw4ku1ujfb 1           0
N N 1r2pbduccrp3s 1           0
N N 5qcyw4ku1ujfb 11          0
N N 1r2pbduccrp3s 11          0
Y N 8jc25wc5fvuyq 11          0
N N 5qcyw4ku1ujfb 5           0
N N 1r2pbduccrp3s 5           0
N N 5qcyw4ku1ujfb 13          0
N N 1r2pbduccrp3s 13          0
N N 5qcyw4ku1ujfb 12          0
N N 1r2pbduccrp3s 12          0
N N 5qcyw4ku1ujfb 4           0
N N 1r2pbduccrp3s 4           0
N N 5qcyw4ku1ujfb 6           0
N N 1r2pbduccrp3s 6           0

Let's see what the columns mean. Oracle observes the cursors for a while and sees how the values differ.

If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y".

After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values. If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y".

In summary: Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change.

SQL> select * from gv$sql_cs_histogram where sql_id = '8jc25wc5fvuyq';

---------- ---------------- ---------- ------------- ------------ ---------- ----------
10 000000056C898240 183364566 8jc25wc5fvuyq 0 0 0
10 000000056C898240 183364566 8jc25wc5fvuyq 0 1 0
10 000000056C898240 183364566 8jc25wc5fvuyq 0 2 0
14 0000000553834DD0 183364566 8jc25wc5fvuyq 0 0 0
14 0000000553834DD0 183364566 8jc25wc5fvuyq 0 1 0
14 0000000553834DD0 183364566 8jc25wc5fvuyq 0 2 0
11 00000005039AE3D0 183364566 8jc25wc5fvuyq 0 0 0
11 00000005039AE3D0 183364566 8jc25wc5fvuyq 0 1 0
11 00000005039AE3D0 183364566 8jc25wc5fvuyq 0 2 0

As the adaptive cursor sharing feature uses the correct plan based on the value of the bind variable,

the database must be holding that information somewhere. It exposes that information through another new view V$SQL_CS_SELECTIVITY that shows the selectivity of the different values passed to the bind variable.
SQL> select * from gv$sql_cs_selectivity where sql_id = '8jc25wc5fvuyq';

no rows selected

This view shows a wealth of information. The column PREDICATE shows the various predicates (the WHERE condition) users have used. The LOW and HIGH values show the range of values passed.

SQL> select child_number,
from gv$sql_cs_statistics
where sql_id = '8jc25wc5fvuyq';

------------ ------------------- - ---------- -------------- ----------- ----------
0 1416895947 Y 1 2 50 0
0 186061784 Y 1 2 78 0
0 3949253548 Y 1 2 108 0

This view shows the statistics about the execution as recorded by the database.

The column EXECUTIONS shows how many times the query was seen to be executed with different values in the bind variable.

The column PEEKED (shown as "P") in the output shows if the optimizer peeked into the bind variable to arrive at a good plan.

These views show additional information that is not necessary for you to understand how this feature works.
Adaptive Cursors are activated and used automatically.


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".



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







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_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


• Environment Variable at Concurrent Manager Tier.


• 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.