SELECT
COUNT(1)
FROM APPS.OE_ORDER_LINES_ALL OOLA,
APPS.OE_ORDER_HEADERS_ALL OOHA,
APPS.WSH_DELIVERABLES_V WDV,
APPS.WSH_DLVB_DLVY_V WDLVB,
APPS.OE_TRANSACTION_TYPES_Tl OTT1,
APPS.OE_TRANSACTION_TYPES_Tl OTT2,
APPS.PA_EXPENDITURE_ITEMS_ALL PEIA,
APPS.PA_COST_DISTRIBUTION_LINES_ALL PCDLA,
APPS.HR_ALL_ORGANIZATION_UNITS HAOU1,
APPS.HR_ALL_ORGANIZATION_UNITS HAOU,
APPS.MTL_PARAMETERS MTP,
APPS.QP_LIST_HEADERS QLH,
APPS.HR_LOCATIONS HRL,
APPS.PA_PROJECTS PPA,
APPS.PA_TASKS PT,
APPS.MTL_ITEM_LOCATIONS MIL,
APPS.PA_EXPENDITURE_TYPES PET,
APPS.PA_EXPENDITURE_COMMENTS PEC,
APPS.FND_LOOKUP_VALUES FLV3,
APPS.FND_LOOKUP_VALUES FLV4,
APPS.FND_LOOKUP_VALUES FLV1,
APPS.GEPS_CS_PA_EXPENDITURE_LINES GPELEI
WHERE OOLA.LINE_ID = WDV.SOURCE_LINE_ID(+)
AND OOHA.HEADER_ID= OOLA.HEADER_ID
AND OOLA.LINE_ID = PEIA.ATTRIBUTE3
AND OOLA.PRICE_LIST_ID=QLH.LIST_HEADER_ID
AND OTT1.TRANSACTION_TYPE_ID=OOLA.LINE_TYPE_ID
AND OTT2.TRANSACTION_TYPE_ID=OOHA.ORDER_TYPE_ID
AND OTT1.LANGUAGE = USERENV('lang')
AND OTT2.LANGUAGE = USERENV('lang')
AND OTT2.NAME LIKE 'Contractual Services%'
AND PCDLA.EXPENDITURE_ITEM_ID=PEIA.EXPENDITURE_ITEM_ID
AND PEIA.EXPENDITURE_ITEM_ID =GPELEI.EXPENDITURE_ITEM_ID---add by kiran on 13/12/2006 for May/07 Release
AND PEIA.EXPENDITURE_ITEM_ID=PEC.EXPENDITURE_ITEM_ID(+)
AND PEIA.EXPENDITURE_TYPE=PET.EXPENDITURE_TYPE
AND PPA.CARRYING_OUT_ORGANIZATION_ID=HAOU1.ORGANIZATION_ID
AND PT.TASK_ID=PEIA.TASK_ID
AND PPA.PROJECT_ID=PT.PROJECT_ID
AND PEIA.COST_DISTRIBUTED_FLAG='Y'
AND PEIA.ATTRIBUTE10 IS NULL
AND PCDLA.LINE_TYPE='R'
AND PEIA.TRANSACTION_SOURCE='Parts'
AND OOLA.FLOW_STATUS_CODE='CLOSED'
AND ((WDV.SOURCE_CODE='OE' AND WDV.RELEASED_STATUS_NAME='Shipped')
OR(WDV.SOURCE_CODE='WSH' AND WDV.RELEASED_STATUS_NAME='Not Applicable')
OR(WDV.SOURCE_CODE IS NULL AND WDV.RELEASED_STATUS_NAME IS NULL))
AND FLV3.LANGUAGE = USERENV('lang')
AND FLV3.LOOKUP_TYPE = 'GECS PARTS LINE TYPES'
AND FLV3.MEANING = upper(ott1.name)
AND FLV1.LANGUAGE = USERENV('lang')
AND FLV1.LOOKUP_TYPE ='SHIPMENT_PRIORITY'
AND FLV1.ATTRIBUTE_CATEGORY = 'SHIPMENT_PRIORITY'
AND FLV4.DESCRIPTION = HAOU1.ATTRIBUTE1(+)
AND FLV4.LOOKUP_TYPE ='GECS PROJECT LEGAL ENTITIES'
AND PEIA.ATTRIBUTE4 IS NOT NULL
AND PEIA.ATTRIBUTE4 = WDV.DELIVERY_DETAIL_ID
AND FLV4.LANGUAGE = USERENV('lang')
AND FLV1.LOOKUP_CODE = DECODE(WDV.SHIPMENT_PRIORITY_CODE,NULL,
OOLA.SHIPMENT_PRIORITY_CODE,WDV.SHIPMENT_PRIORITY_CODE)
AND MIL.INVENTORY_LOCATION_ID(+) = NVL(WDV.LOCATOR_ID,0)
AND MTP.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND MTP.ORGANIZATION_ID(+)=WDV.ORGANIZATION_ID
AND HRL.LOCATION_ID(+) = WDV.SHIP_FROM_LOCATION_ID
AND WDV.DELIVERY_DETAIL_ID = WDLVB.DELIVERY_DETAIL_ID
AND (PT.TASK_NUMBER IN ('IP','IX','IU') or ppa.project_type in ('SUSPENSE','PURGE') or peia.attribute2=0 or GPELEI.tp_exception_status in ('C','F'))
AND GPELEI.tp_exception_status in ('A','B','C','F')
I have some queries, trying to do some performance improvement.
Here I observed that,
its not using the index for pa_projects_all, pa_expenditure_items_all.
Even I tried some of the hints explicity. But, still not considered in the plan.
Highly appreciated if you could identify and explaining the issues.
Thanks,
Srinivasan.
repeat to thyself:
a) full scans are NOT evil
b) indexes are not all GOODNESS
c) read
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154 d) goto a) until you firmly believe a) and b) are true and have understood c
Than - realize that if you give someone a huge query
And that person doesn't know the schema AT ALL
And you give them no information about it.
And the query doesn't even reference the tables mentioned as being a problem (which are likely not problems at all)...
You will never get any useful feedback. There is no one that could answer your question unless they themselves know YOUR data and schema intimately.