SQL & PL/SQL

Hi,
This may not be a question with complete details, but I am looking only for some thoughts.
Below is a query expected to return records around 100K and none of the three tables used in this query has more than 100K records.
Still this query on 11g database taking almost 3 Hrs to return the data, Very high level, anything which need very first attention?
(database is well tuned already and table/index stat is good, no full table scan in this query-Explain plan at end)

Thanks


SELECT EQXDL.LIST_HEADER_ID,
QP.LIST_LINE_ID,
EQXDL.LIST_NAME,
EQXDL.DESCRIPTION,
EQXDL.INVENTORY_ITEM_ID,
EQXDL.ITEM,
EQXDL.ITEM_DESC,
QL.OPERAND,
NULL LIST_PRICE,
EQXDL.CURRENCY_CODE,
EQXDL.PRIMARY_UOM_FLAG,
QP.PRODUCT_UOM_CODE UOM_CODE,
QP.PRICING_ATTRIBUTE CCC_PRICING_ATTRIBUTE,
QP.PRICING_ATTR_VALUE_FROM CCC_PRICING_ATTRIBUTE_VALUE,
(SELECT USER_SEGMENT_NAME
FROM QP_SEGMENTS_V
WHERE SEGMENT_MAPPING_COLUMN = QP.PRICING_ATTRIBUTE
AND PRC_CONTEXT_ID IN
(SELECT PRC_CONTEXT_ID
FROM QP_PRC_CONTEXTS_V
WHERE PRC_CONTEXT_CODE = 'PRICING ATTRIBUTE'))
CCC_PRICING_ATT_DISP,
NULL LOCATION_ID,
'NEW' RECORD_STATUS,
'CCCAttr' RECORD_COMMENTS,
SYSDATE CREATION_DATE,
SYSDATE LAST_UPDATE_DATE,
:B3 CREATED_BY,
:B3 LAST_UPDATED_BY,
TEST_PRIC_ATT_DISP (PRICING_ATTR_VALUE_FROM)
CCC_PRICING_ATT_DISP_VAL,
:B1 REQUEST_ID
FROM QP_LIST_LINES QL,
QP_PRICING_ATTRIBUTES QP,
EQIX.EQXQP_PRICELIST_DOWNLOAD EQXDL
WHERE 1 = 1
AND QL.LIST_HEADER_ID = QP.LIST_HEADER_ID
AND QL.LIST_LINE_ID = QP.LIST_LINE_ID
AND QL.LIST_HEADER_ID = EQXDL.LIST_HEADER_ID
AND QL.LIST_LINE_ID = EQXDL.LIST_LINE_ID
AND QP.LIST_HEADER_ID = EQXDL.LIST_HEADER_ID
AND QP.LIST_LINE_ID = EQXDL.LIST_LINE_ID
AND QP.PRICING_ATTRIBUTE_CONTEXT = 'PRICING ATTRIBUTE'
AND (SELECT USER_SEGMENT_NAME
FROM QP_SEGMENTS_V
WHERE SEGMENT_MAPPING_COLUMN = QP.PRICING_ATTRIBUTE
AND PRC_CONTEXT_ID IN
(SELECT PRC_CONTEXT_ID
FROM QP_PRC_CONTEXTS_V
WHERE PRC_CONTEXT_CODE =
'PRICING ATTRIBUTE')) = 'CCC'
AND CASE
WHEN ( (SELECT USER_SEGMENT_NAME
FROM QP_SEGMENTS_V
WHERE SEGMENT_MAPPING_COLUMN = QP.PRICING_ATTRIBUTE
AND PRC_CONTEXT_ID IN
(SELECT PRC_CONTEXT_ID
FROM QP_PRC_CONTEXTS_V
WHERE PRC_CONTEXT_CODE =
'PRICING ATTRIBUTE')) =
'CCC')
THEN
(TEST_PRIC_ATT_DISP (QP.PRICING_ATTR_VALUE_FROM))
END IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE IN :B4)
AND QP.LIST_HEADER_ID = :B2
AND EQXDL.REQUEST_ID = :B1

Explain Plan:

Plain Explain Plan: -

1 Rows were retrieved using the unique index QP.QP_PRC_CONTEXTS_TL_U1 .
2 Rows were retrieved using the unique index QP.QP_PRC_CONTEXTS_B_U1 .
3 Rows from table QP.QP_PRC_CONTEXTS_B were accessed using rowid got from an index.
4 For each row retrieved by step 1, the operation in step 3 was performed to find a matching row.
5 One or more rows were retrieved using index QP.QP_SEGMENTS_B_U3 . The index was scanned in ascending order..
6 Rows from table QP.QP_SEGMENTS_B were accessed using rowid got from an index.
7 Rows were retrieved using the unique index QP.QP_SEGMENTS_TL_U1 .
8 For each row retrieved by step 6, the operation in step 7 was performed to find a matching row.
9 Rows from table QP.QP_SEGMENTS_TL were accessed using rowid got from an index.
10 For each row retrieved by step 8, the operation in step 9 was performed to find a matching row.
11 Rows were retrieved using the unique index QP.QP_PRC_CONTEXTS_TL_U1 .
12 Rows were retrieved using the unique index QP.QP_PRC_CONTEXTS_B_U1 .
13 Rows from table QP.QP_PRC_CONTEXTS_B were accessed using rowid got from an index.
14 For each row retrieved by step 11, the operation in step 13 was performed to find a matching row.
15 One or more rows were retrieved using index QP.QP_SEGMENTS_B_U3 . The index was scanned in ascending order..
16 Rows from table QP.QP_SEGMENTS_B were accessed using rowid got from an index.
17 Rows were retrieved using the unique index QP.QP_SEGMENTS_TL_U1 .
18 For each row retrieved by step 16, the operation in step 17 was performed to find a matching row.
19 Rows from table QP.QP_SEGMENTS_TL were accessed using rowid got from an index.
20 For each row retrieved by step 18, the operation in step 19 was performed to find a matching row.
21 One or more rows were retrieved using index QP.QP_PRICING_ATTRIBUTES_N8 . The index was scanned in ascending order..
22 Rows from table QP.QP_PRICING_ATTRIBUTES were accessed using rowid got from an index.
23 One or more rows were retrieved using index EQIX.EQXQP_PRICELIST_DOWNLOAD_N4 . The index was scanned in ascending order..
24 Rows from table EQIX.EQXQP_PRICELIST_DOWNLOAD were accessed using rowid got from an index.
25 For each row retrieved by step 22, the operation in step 24 was performed to find a matching row.
26 Rows were retrieved using the unique index QP.QP_PRC_CONTEXTS_TL_U1 .
27 Rows were retrieved using the unique index QP.QP_PRC_CONTEXTS_B_U1 .
28 Rows from table QP.QP_PRC_CONTEXTS_B were accessed using rowid got from an index.
29 For each row retrieved by step 26, the operation in step 28 was performed to find a matching row.
30 One or more rows were retrieved using index QP.QP_SEGMENTS_B_U3 . The index was scanned in ascending order..
31 Rows from table QP.QP_SEGMENTS_B were accessed using rowid got from an index.
32 Rows were retrieved using the unique index QP.QP_SEGMENTS_TL_U1 .
33 For each row retrieved by step 31, the operation in step 32 was performed to find a matching row.
34 Rows from table QP.QP_SEGMENTS_TL were accessed using rowid got from an index.
35 For each row retrieved by step 33, the operation in step 34 was performed to find a matching row.
36 One or more rows were retrieved using index APPLSYS.FND_LOOKUP_VALUES_U1 . The index was scanned in ascending order..
37 For each row retrieved by step 25, the operation in step 36 was performed to find a matching row.
38 Rows were retrieved using the unique index QP.QP_LIST_LINES_PK .
39 For each row retrieved by step 37, the operation in step 38 was performed to find a matching row.
40 Rows from table QP.QP_LIST_LINES were accessed using rowid got from an index.
41 For each row retrieved by step 39, the operation in step 40 was performed to find a matching row.
42 HASH UNIQUE
43 A view definition was processed, either from a stored view VM_NWVW_2 or as defined by steps 42.
44 Rows were returned by the SELECT statement.

Edited by: Bhushan on Feb 12, 2011 11:42 PM