Description:
Some customers have observed that a full table scan of the attributes table while running with Oracle.  Depending on the size of the attributes table this might cause a timeout on the BW side.  This has only been observed on the Class Query, API numbers 63010 through 63020.  The class query is know to be used in BW WF on user synchronization and sometimes when a processes is exported from the designer to the work flow engine.

The first action to do is to rebuild the indexes on attributes table as they currently exist.  If this does not work then there are two alternatives which have been shown to solve the issue.  

Do only one of the two solutions.  Both have been shown to fix the issue.

Initial tests have shown the
following indexes to improve performance and it will be added to a future
release once further tests by Quality Assurances verify the usefulness of
the indexes.

Solution 1:

Drop the index ATTRIBUTES_KEY

CREATE INDEX "ICDBOWN"."ATTRIBUTES_NAME"
    ON "ICDBOWN"."ATTRIBUTES"  ("ATTRIBUTE_NAME")
    COMPRESS 1


CREATE INDEX "ICDBOWN"."ATTRIBUTES_KEY"
    ON "ICDBOWN"."ATTRIBUTES"  ("OBJECT_ID")
    COMPRESS 1

or,

Solution 2:

CREATE INDEX "ICDBOWN"."ATTRIBUTES_STR_VALUE"
ON "BWWFCI"."ATTRIBUTES" ("STR_VALUE")
TABLESPACE "ICDBOWN" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
NOLOGGING

Note INTRANS values must be evaluated for each site.  A value of 2 may be too low for some sites.

Engineering says of the two solutions -
"The tradeoff in solution (1) is that the single column index on OBJECT_ID is
smaller than the out-of-the-box index on (OBJECT_ID, ATTRIBUTE_NAME) and so
may lead the optimizer to prefer the index over a table scan. The
disadvantage is the index is less selective and so a little more CPU is
expended to restrict on ATTRIBUTE_NAME; since CPU is heavily expended during
table scans (or disk access in general) the trade off may be a good one in
certain situations. A second trade off is that adding a column to an index
costs less than adding a whole new index during updates (some say 10% to add
a column, 100% to add an index), but again this may be minor compared to a
table scan of a large table."


Symptoms:

Cause: