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.
select name
from hr_operating_units
where organization_id = 2003;
QUERY_PLAN
-----------------------------------------------------------------------------
SELECT STATEMENT Cost = 6
2.1 TABLE ACCESS BY INDEX ROWID HR_ORGANIZATION_INFORMATION
3.1 NESTED LOOPS
4.1 NESTED LOOPS
5.1 NESTED LOOPS
6.1 TABLE ACCESS BY INDEX ROWID HR_ALL_ORGANIZATION_UNITS_TL
7.1 INDEX UNIQUE SCAN HR_ALL_ORGANIZATION_UNTS_TL_PK UNIQUE
6.2 INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK UNIQUE
5.2 TABLE ACCESS BY INDEX ROWID HR_ORGANIZATION_INFORMATION
6.1 INDEX RANGE SCAN HR_ORGANIZATION_INFORMATIO_FK1 NON-UNIQUE
4.2 INDEX RANGE SCAN HR_ORGANIZATION_INFORMATIO_FK2 NON-UNIQUE
select name
from hr_all_organization_units
where organization_id = 2003;
QUERY_PLAN
------------------------------------------------------------
SELECT STATEMENT Cost = 2
2.1 TABLE ACCESS BY INDEX ROWID HR_ALL_ORGANIZATION_UNITS
3.1 INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK UNIQUE
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 description
from fnd_lookup_values
where lookup_type = 'XXERP'
and lookup_code = '10142';
QUERY_PLAN
---------------------------------------------------------------
SELECT STATEMENT Cost = 4
2.1 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
3.1 INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 UNIQUE
By utilizing all columns in the unique index, we get a lower cost.
FND_LOOKUP_VALUES FND_LOOKUP_VALUES_U1 UNIQUE LOOKUP_TYPE 1
FND_LOOKUP_VALUES UNIQUE VIEW_APPLICATION_ID 2
FND_LOOKUP_VALUES UNIQUE LOOKUP_CODE 3
FND_LOOKUP_VALUES UNIQUE SECURITY_GROUP_ID 4
FND_LOOKUP_VALUES UNIQUE LANGUAGE 5
select description
from fnd_lookup_values
where lookup_type = 'GEPS_SERVICE_SHOPS_LIST'
and lookup_code = '10142'
and view_application_id = 660
and language = userenv('LANG')
and security_group_id = 0;
QUERY_PLAN
--------------------------------------------------------
SELECT STATEMENT Cost = 3
2.1 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
3.1 INDEX UNIQUE SCAN FND_LOOKUP_VALUES_U1 UNIQUE
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 varchar2
is
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 varchar2
is
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.
