Hi General to increase performance and to decrease query execution time. In query we use HINTS . Using HINTS is one option and at programming level using PS is other. Regards Praveen
"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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------- Yahoo! Autos. Looking for a sweet ride? Get pricing, reviews, & more on new and used cars.
