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