Performance Tuning in Oracle Apps

Performance Tuning

Gather Schema and Table Statistics
Goto Sysadminstrator -> Run -> Gather Schema Statistics
                                                -> Gather Table statistics

Tracing the Form
Go to -> Diagnostics -> Trace -> Trace with Binds and Waits
Once trace is completed go to Trace -> No Trace
Generate the trace File with tkprof
tkprof filename.trc filename.prf explain=apps/<apps_pwd> sort=prsela,exeela,fchela

Concurrent Program Tracing
1. Enable the trace button in Concurrent -> Programs -> Define
2. Execute the Conc Program and note the Request Id
3. Get the Session Details (SID and Serial)
4. Select the trace from udump location and investigate the issue

Tuning Query Tips
1. Run the Execute Plan for the Query find the cost of it.
2. Check whether the indexes are being used by Query, if not create the Indexes for custom tables.
3. Also check for the Full Outer Joins for the tables
4. Try Using Hints to the query to Improve Performance, mostly avoid the use of hints
5. Rewriting Complex Queries using Temporary Tables/Global Temp Tables
6. Use minus instead of NOT EXISTS /NOT In subqueries
7. Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins
8. Use Case instead of Decode
9. Use UNION_ALL instead of UNION
10. Use the only required columns from tables instead of all(*)
11. Avoid the use of DISTINCT and use the GROUP BY to get unique records
12. Use Exists instead of IN Operator
13. Avoid using functions such as RTRIM, TO_CHAR, UPPER, TRUNC on indexed columns.
14 There shouldn't be any Cartesian Product.
15. Avoid Using Order by for the large data