Total Pageviews

March 30, 2016

3/30/2016 02:23:00 AM
Oracle Applications performance tuning issues take two forms:

A) Simple issues that developers can fix themselves with an intermediate knowledge of the applications and the database.B) Complex issues that require an advanced knowledge of tuning.

This whitepaper focuses on the simple issues and provides rules of thumb that developers should follow to optimize query performance in Oracle Applications.Rules

1) Explain plan every query.  Explaining plan only takes 30 seconds for one query and will give a developer a good idea as to whether a query is efficient.  Explain plans with full table scans and high query costs should generally be a red flag that require tuning.

2) Keep the table statistics up to date.  This is critical, so the optimizer will select the best execution plan and give the correct cost.  Either run the concurrent program “Gather Table Statistics” for individual tables or “Gather Schema Statistics” for all tables within a schema.


3) To tune:a) Rewrite the query.  This will solve 95% of tuning problems.b) Add custom indexes.  This will solve 5% of tuning problems.c) In extremely rare cases, adding hints will improve performance.


4) Avoid using unnecessary views.  This can lead to joins of tables that are not required.Example:

I want to get the org_id for an operating unit name.  I can use the HR_OPERATING_UNITS view, which includes several tables I do not need and leads to a higher cost.  Instead, I can use HR_ALL_ORGANIZATION_UNITS table, which will give me the same information at a lower cost.---Don't useselect  namefrom    hr_operating_unitswhere organization_id = 2003;----use this---------select  namefrom    hr_all_organization_unitswhere organization_id = 2003;


5) Use all columns in indexes when possible.


Example:We want to get information about a lookup, and we know the lookup type and lookup code.  The first query returns the correct results but does not take full advantage of a unique index on FND_LOOKUP_VALUES.
select descriptionfrom fnd_lookup_valueswhere lookup_type = 'XXLOOKUP'and lookup_code = '10142';


By utilizing all columns in the unique index, we get a lower cost.


select descriptionfrom fnd_lookup_valueswhere lookup_type = XXLOOKUP''and lookup_code = '10142'and view_application_id = 660and language = userenv('LANG')and security_group_id = 0;

6) Do not trust the list of indexes in the TRM – the list is not always accurate.  Instead, query ALL_INDEXES and ALL_IND_COLUMNS to get the correct indexes.


7) Custom tables at a bare minimum must have one unique index.  As the developer tunes queries that use custom tables, non-unique indexes may also be required.


8) Do not use explicit cursors (e.g., open-fetch-close) for queries that return only one row – this is inefficient.  Instead, use a standalone query in a PL/SQL block.
Example – incorrect use of cursor:
FUNCTION get_user_name (p_user_id IN fnd_user.user_id%TYPE)   RETURN VARCHAR2IS   CURSOR user_cur   IS      SELECT   user_name        FROM   fnd_user       WHERE   user_id = p_user_id;
   v_user_name   fnd_user.user_name%TYPE;BEGIN   OPEN user_cur;
   FETCH user_cur INTO   v_user_name;
   CLOSE user_cur;
   RETURN (v_user_name);END get_user_name;
Example – rewrite as standalone query using PL/SQL block:
FUNCTION get_user_name (p_user_id IN fnd_user.user_id%TYPE)   RETURN VARCHAR2IS   v_user_name   fnd_user.user_name%TYPE;BEGIN   BEGIN      SELECT   user_name        INTO   v_user_name        FROM   fnd_user       WHERE   user_id = p_user_id;   EXCEPTION      WHEN NO_DATA_FOUND      THEN         v_user_name := NULL;   END;
   RETURN (v_user_name);END get_user_name;


9) Do not use distinct, rownum=1 or group by when the query will only return one row.