11.1: ORA-600 [17147] AND [kghfrempty:ds] On Select Using Functional Indexes

(Doc ID 884882.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.

Symptoms

Following errors might occur in a 11.1.0.7 environment using E-Business Suite

ORA-00600: codice di errore interno, argomenti: [17147], [0x1118A79A8], [], [], [], [], [], []
ORA-00600: codice di errore interno, argomenti: [17147], [0x1118A79A8], [], [], [], [], [], []
ORA-00600: codice di errore interno, argomenti: [kghfrempty:ds]

When executing a query with functional indexes execution time is slow.

SELECT OW.SOURCE_NAME "owner", L.DESCRIPTION "oppDescription", 'DUMMY' "updateableFlag",
L.CURRENCY_CODE "currency", L.WIN_PROBABILITY "winProb", L.LEAD_NUMBER "leadNumber",
L.DECISION_DATE "decisionDate", L.LAST_UPDATE_DATE "updatedDate", L.SALES_STAGE_ID "salesStageID",
L.CUSTOMER_ID "partyID", PARTY.PARTY_NAME "customerName", L.TOTAL_AMOUNT "totalAmount",
P1.SOURCE_NAME "createdBy", L.STATUS "status", L.LEAD_ID "leadID", PARTY.PARTY_TYPE
"customerType", L.CLOSE_REASON "closeReasonCode", L.CHANNEL_CODE "channelCode", P2.SOURCE_NAME
"updatedBy", L.SALES_METHODOLOGY_ID "methodologyCode", L.CREATION_DATE "createdDate",
L.LAST_UPDATE_DATE "rupdatedDate" FROM AS_LEADS_ALL L, JTF_RS_RESOURCE_EXTNS OW, HZ_PARTIES PARTY,
AS_PERIOD_RATES R, AS_PERIOD_DAYS D, JTF_RS_RESOURCE_EXTNS P1, JTF_RS_RESOURCE_EXTNS P2 WHERE
(L.OWNER_SALESFORCE_ID = OW.RESOURCE_ID(+) AND L.CUSTOMER_ID = PARTY.PARTY_ID AND L.CURRENCY_CODE
= R.FROM_CURRENCY AND R.TO_CURRENCY = :1 AND R.PERIOD_NAME = D.PERIOD_NAME AND L.DECISION_DATE =
D.PERIOD_DAY AND D.PERIOD_TYPE = :2 AND R.CONVERSION_TYPE = :3 AND R.PERIOD_SET_NAME = :4 AND
D.PERIOD_SET_NAME = :5 AND L.CREATED_BY = P1.USER_ID AND L.LAST_UPDATED_BY = P2.USER_ID AND
(L.LEAD_ID IN (SELECT AAA.LEAD_ID FROM AS_ACCESSES_ALL AAA , AS_RPT_MANAGERS_V RM WHERE 1=1 AND
AAA.SALESFORCE_ID = RM.RESOURCE_ID AND AAA.SALESFORCE_ID = RM.RESOURCE_ID * 1 AND
RM.PARENT_RESOURCE_ID = :6 AND RM.PARENT_RESOURCE_ID*1 = :7 AND SYSDATE BETWEEN
RM.START_DATE_ACTIVE and NVL(RM.END_DATE_ACTIVE, SYSDATE) ) ) ) ORDER BY L.CREATION_DATE DESC,
UPPER(OW.SOURCE_NAME) ASC

 

Call stack:
kghfrmrg <- kghfre <- kghsfx kghunfhp <- kghundo <- kghfrh_internal kghfrh


Execution plan:
---------------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                         |                         |       |       |    20 |           |
| 1   |  SORT ORDER BY                           |                         |     1 |  1051 |    20 |  00:00:01 |
| 2   |   VIEW                                   | VM_NWVW_2               |     1 |  1051 |    19 |  00:00:01 |
| 3   |    HASH UNIQUE                           |                         |     1 |   499 |    19 |  00:00:01 |
| 4   |     NESTED LOOPS                         |                         |     1 |   499 |    18 |  00:00:01 |
| 5   |      NESTED LOOPS                        |                         |     1 |   452 |    18 |  00:00:01 |
| 6   |       NESTED LOOPS                       |                         |     1 |   401 |    16 |  00:00:01 |
| 7   |        NESTED LOOPS                      |                         |     1
|   365 |    15 |  00:00:01 |
| 8   |         NESTED LOOPS OUTER               |                         |     1 |   329 |    14 |  00:00:01 |
| 9   |          NESTED LOOPS                    |                         |     1 |   268 |    13 |  00:00:01 |
| 10  |           NESTED LOOPS                   |                         |     1 |   209 |    12 |  00:00:01 |
| 11  |            NESTED LOOPS                  |                         |     1 |    70 |    11 |  00:00:01 |
| 12  |             NESTED LOOPS                 |                         |     1 |    59 |     5 |  00:00:01 |
| 13  |              TABLE ACCESS BY INDEX ROWID | JTF_RS_REP_MANAGERS     |     1 |    44 |     4 |  00:00:01 |
| 14  |               INDEX RANGE SCAN           | JTF_RS_REP_MANAGERS_N9  |     2 |       |     2 |  00:00:01 |
| 15  |              INLIST ITERATOR             |                         |       |       |       |           |
| 16  |               INDEX UNIQUE SCAN          | JTF_RS_GROUP_USAGES_U2  |     1 |    15 |     1 |  00:00:01 |
| 17  |             TABLE ACCESS BY INDEX ROWID  | AS_ACCESSES_ALL_ALL     |     1 |    11 |     6 |  00:00:01 |
| 18  |              INDEX RANGE SCAN            | AS_ACCESSES_N12         |     1 |       |     5 |  00:00:01 |
| 19  |            TABLE ACCESS BY INDEX ROWID   | AS_LEADS_ALL            |     1 |   139 |     1 |  00:00:01 |
| 20  |             INDEX UNIQUE SCAN            | AS_LEADS_U1             |     1 |       |     0 |           |
| 21  |           TABLE ACCESS BY INDEX ROWID    | HZ_PARTIES              |     1 |    59 |     1 |  00:00:01 |
| 22  |            INDEX UNIQUE SCAN             | HZ_PARTIES_U1           |     1 |       |     0 |           |
| 23  |          TABLE ACCESS BY INDEX ROWID     | JTF_RS_RESOURCE_EXTNS   |     1 |    61 |     1 |  00:00:01 |
| 24  |           INDEX UNIQUE SCAN              | JTF_RS_RESOURCE_EXTNS_U1|     1 |       |     0 |           |
| 25  |         TABLE ACCESS BY INDEX ROWID      | JTF_RS_RESOURCE_EXTNS   |     1 |    36 |     1 |  00:00:01 |
| 26  |          INDEX UNIQUE SCAN               | JTF_RS_RESOURCE_EXTNS_U3|     1 |       |     0 |           |
| 27  |        TABLE ACCESS BY INDEX ROWID       | JTF_RS_RESOURCE_EXTNS   |     1 |    36 |     1 |  00:00:01 |
| 28  |         INDEX UNIQUE SCAN                | JTF_RS_RESOURCE_EXTNS_U3|     1 |       |     0 |           |
| 29  |       TABLE ACCESS BY INDEX ROWID        | AS_PERIOD_DAYS          |     1 |    51 |     2 |  00:00:01 |
| 30  |        INDEX RANGE SCAN                  | AS_PERIOD_DAYS_N1       |     1 |       |     1 |  00:00:01 |
| 31  |      INDEX UNIQUE SCAN                   | AS_PERIOD_RATES_U2      |     1 |    47 |     0 |           |
---------------------------------------------------------------------------+-----------------------------------+

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms