I haven't looked at the query in detail, too hard when it's not formatted.
Things to check though:
Without the schema (I missed the original email, appologies if the schema was
included there) and indexes not much can be deduced. Most likely though there
is a missing index or something is preventing an index being used.
Two things that did jump off the page at me:
first:
> > ORDER BY TO_NUMBER("CUSTNUM") ASC
Why 'to_number'? Is it not already a number in the schema? This is the kind of
thing that will prevent an index being used (functions on otherwise indexed
columns)
two:
> > AND (NVL(PC.PEND_LIST_STATUS, 'N') = 'N')
Again this doesn't look the most obvious way,
perhaps (pc.pend_list_status = 'N' or pc.pend_list_status is null)
Best thing, check the schema, then use explain plan and other optimizing tools,
I'm pretty sure the problem will become obvious.
Regards
Brett Connor
> "Jonnalagadda, Sumithra" <[EMAIL PROTECTED]> wrote:
> yes. its a prepared statement. I am not the authour of the query. In
> production we started to have performance issues so trying to investigate.
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf
> Of Larry Meadors
> Sent: Friday, February 10, 2006 1:03 PM
> To: Struts Users Mailing List
> Subject: Re: [OT off topic] Oracle Database Performace Issue
>
>
> Huh...and that runs slow?
>
> Imagine that.
>
> ;-)
>
> Sweet mother of mystery, that is one gnarly query. Seems to have a lot
> of repetition in it...could it be redone as a stored procedure?
>
> Are you running it as a prepared statement?
>
> Larry
>
>
> On 2/10/06, Jonnalagadda, Sumithra wrote:
> > Here is the query
> >
> >
> >
> > SELECT PTI.PARTY_ID AS "CUSTID",
> > PTI.CMA_CUSTOMER_NUMBER AS "CUSTNUM",
> > PTI.SOURCE_ID AS "CUSTSOURCE",
> > PTI.PARTY_CLASS_CD AS "CLASS",
> > (SELECT CMAPSV.STATUS_VALUE
> > FROM type3 CMAPS,
> > type4 CMAPSV
> > WHERE PTI.PARTY_TYPE_INSTANCE_ID = CMAPS.PARTY_TYPE_INSTANCE_ID
> > AND CMAPS.PARTY_STATUS_TYPE_ID = 1
> > AND CMAPS.PARTY_STATUS_VALUE_ID = CMAPSV.PARTY_STATUS_VALUE_ID
> > AND (NVL(CMAPS.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND (TRD.CURR_DT >= CMAPS.FROM_DT
> > AND TRD.CURR_DT <= CMAPS.TO_DT)
> > AND ROWNUM = 1) AS "CUSTSTATUS",
> > PTI.TYPE_NAME AS "CUSTTYPENAME",
> > PTI.MASTER_PARTY_TYPE_ID AS "CUST_SUPER_TYPE_ID",
> > PTI.PARTY_TYPE_ID AS "CUSTTYPE",
> > (SELECT AU3.LINE_1 || '~' || AU3.CITY || '~' || AU3.STATE || '~' ||
> > AU3.POSTAL_CODE
> > FROM MV_type4 AU3
> > WHERE AU3.PARTY_ID = PTI.PARTY_ID
> > AND (NVL(AU3.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND ((TRD.CURR_DT >= AU3.ADDRUSG_FROM_DT
> > AND TRD.CURR_DT <= AU3.ADDRUSG_TO_DT))
> > AND AU3.ADDR_USAGE_TYPE_ID = '83'
> > AND ((TRD.CURR_DT >= AU3.ADDR_FROM_DT
> > AND TRD.CURR_DT <= AU3.ADDR_TO_DT))
> > AND ROWNUM = 1) AS ADDRESS,
> > (SELECT FIRST_NAME || '~' || MIDDLE_NAME || '~' || LAST_NAME
> > FROM PERSON
> > WHERE PERSON.PARTY_ID = PTI.PARTY_ID) AS "NAME",
> > (SELECT CC.CLASSIF_CODE_NAME
> > FROM type5 PC,
> > type6 CC
> > WHERE PTI.PARTY_ID = PC.PARTY_ID
> > AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> > AND (TRD.CURR_DT >= PC.FROM_DT
> > AND TRD.CURR_DT <= PC.TO_DT)
> > AND (NVL(PC.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> > AND CC.CLASSIF_TYPE_ID = '7'
> > AND ROWNUM = 1) AS "Primary Specialty",
> > (SELECT CC.CLASSIF_CODE_NAME
> > FROM type5 PC,
> > type6 CC
> > WHERE PTI.PARTY_ID = PC.PARTY_ID
> > AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> > AND (TRD.CURR_DT >= PC.FROM_DT
> > AND TRD.CURR_DT <= PC.TO_DT)
> > AND (NVL(PC.PEND_LIST_STATUS, 'N') = 'N')
> > AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> > AND CC.CLASSIF_TYPE_ID = '132'
> > AND ROWNUM = 1) AS "CAM_FLAG",
> > (SELECT CC.CLASSIF_CODE_NAME
> > FROM type5 PC,
> > type6 CC
> > WHERE PTI.PARTY_ID = PC.PARTY_ID
> > AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> > AND (TRD.CURR_DT >= PC.FROM_DT
> > AND TRD.CURR_DT <= PC.TO_DT)
> > AND (NVL(PC.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> > AND CC.CLASSIF_TYPE_ID = '12'
> > AND ROWNUM = 1) AS "MODEL",
> > (SELECT CC.CLASSIF_CODE_NAME
> > FROM type5 PC,
> > type6 CC
> > WHERE PTI.PARTY_ID = PC.PARTY_ID
> > AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> > AND (TRD.CURR_DT >= PC.FROM_DT
> > AND TRD.CURR_DT <= PC.TO_DT)
> > AND (NVL(PC.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> > AND CC.CLASSIF_TYPE_ID = '12'
> > AND ROWNUM = 1) AS "PROF_CLASS",
> > (SELECT PR.REFERENCE_VALUE
> > FROM PARTY_REFERENCE PR
> > WHERE PTI.PARTY_ID = PR.PARTY_ID
> > AND PR.PARTY_TYPE_ID = PTI.PARTY_TYPE_ID
> > AND (TRD.CURR_DT >= PR.FROM_DT
> > AND TRD.CURR_DT <= PR.TO_DT)
> > AND (NVL(PR.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND PR.REF_TYPE_ID = '108'
> > AND ROWNUM = 1) AS "IMS ID"
> > FROM MV_TYPE1 PTI,
> > TAB__RUN_TYPE2 TRD
> > WHERE (TRD.CURR_DT >= PTI.FROM_DT
> > AND TRD.CURR_DT <= PTI.TO_DT)
> > AND (NVL(PTI.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND PTI.PARTY_TYPE_GROUP_ID IN ('1',
> > '2')
> > AND (PTI.CMA_CUSTOMER_NUMBER IS NULL
> > OR NOT PTI.CMA_CUSTOMER_NUMBER = 1)
> > AND PTI.PARTY_TYPE_ID = ('14')
> > AND EXISTS (SELECT 1
> > FROM PARTY_CLASSIF PC
> > WHERE PC.PARTY_ID = PTI.PARTY_ID
> > AND PC.PARTY_TYPE_ID = PTI.PARTY_TYPE_ID
> > AND (NVL(PC.PEND_LIST_STATUS,
> > 'N') = 'N')
> > AND NOT (PC.FROM_DT >= '31-DEC-4000'
> > OR PC.TO_DT <= '2-FEB-2006')
> > AND (PC.CLASSIF_CODE_ID = ('1951')))
> > ORDER BY TO_NUMBER("CUSTNUM") ASC
> >
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
