Tags

, , ,

We recently had bad performance due to the full table scan on the gl_code_combinations table. Metalink Note: [ID 858725.1]

Indexes for GL_CODE_COMBINATIONS

To optimize the GL_CODE_COMBINATIONS performance add a non-unique concatenated index on just the active segments of the GL_CODE_COMBINATIONS table. This is a required index, per development, no matter where the performance problem is, as it improves performance for many other GL functionalities as well.
If one such index already exists make sure that the segments with the highest number of values still come first as the spread of values may change over time.
The order of the columns, in the CREATE INDEX command, is decisive – the more sensitive or selective an index is the better the performance. To achieve that, the segments with more occurrences in the table must be placed first.

To find what indexes already exist, use this sql:

Select aic.table_name, aic.index_name,
ai.uniqueness, aic.column_name, aic.column_position
From all_ind_columns aic,
all_indexes ai
where ai.index_name = aic.index_name
and ai.table_name = ‘GL_CODE_COMBINATIONS’
order by aic.index_name, aic.column_position;

To determine the order of the segments in the concatenated index execute the following select statement, which counts the number of distinct values for each segment used in the GL_CODE_COMBINATIONS table (as the index is for all Charts Of Accounts we do not distinguish between COA’s):

set serveroutput on

DECLARE
v_SegNum VARCHAR2(9);
v_FreqCount NUMBER;
BEGIN
FOR i IN 1..30
LOOP
EXECUTE IMMEDIATE
‘SELECT COUNT(DISTINCT SEGMENT’||TO_CHAR(i)||’)
FROM gl_code_combinations’
INTO v_FreqCount ;
IF v_freqCount <> 0 THEN
DBMS_OUTPUT.PUT_LINE(
‘SEGMENT’||TO_CHAR(i)||’ Frequency = ‘||TO_CHAR(v_FreqCount));
END IF;
END LOOP;
END;

For example, consider that this select statement has returned the values:

SEGMENT1 Frequency = 4
SEGMENT2 Frequency = 37
SEGMENT3 Frequency = 76
SEGMENT4 Frequency = 3221
SEGMENT5 Frequency = 21
SEGMENT6 Frequency = 22

The indexes for individual segments with a small number of distinct values should be disabled in the Segments Definition form (deselect the index column – please see the paragraph C.1 above).
In general the best concatenated index would include ALL segments, ordered by decreasing number of distinct values. From data above, the best order to make the most selective index is:

  1. SEGMENT4,
  2. SEGMENT3,
  3. SEGMENT2,
  4. SEGMENT6,
  5. SEGMENT5,
  6. SEGMENT1

In this example we recommend to deselect the index flag on SEGMENT1, as there is no benefit to index a segment with a small number of different values used in the code combinations.

As it is recommended not to have 2 indexes starting with the same column, you would need to uncheck also the index flag on SEGMENT4 in the Key Segments form, because the concatenated index will be used instead (in this example segment4 will be the first column in the concatenated index). If you do not, then the index on segment4 will be recreated  the next time the GL Optimizer is executed and you would end up with 2 indexes starting with segment4.

Sign on to SQL*Plus as APPS to create manually the new concatenated index GL_CODE_COMBINATIONS_CAT. In our example you should  execute the following:

CREATE INDEX GL_CODE_COMBINATIONS_CAT ON gl_code_combinations
(segment4,
segment3,
segment2,
segment6,
segment5,
segment1)
PCTFREE 0
INITRANS 2
MAXTRANS 255
TABLESPACE user_index
STORAGE (INITIAL 1048576
NEXT 16384
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 20);

When this index is created you must run the GL Optimizer with Yes for the options to Maintain Indexes, to create/drop the required indexes for the segments.
Be aware that if the concatenated index uses the name of the standard segment index, it may be replaced/deleted by the GL Optimizer. When this happens the concatenated index must be recreated manually.

Advertisements