OraFAQ Forum: Performance Tuning » optimatize query??

Home » RDBMS Server » Performance Tuning » optimatize query??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

optimatize query?? [message #250537]

Tue, 10 July 2007 06:52

steve_kc
Messages: 5
Registered: July 2007
Location: Malaysia

Junior Member

Hi All,

My query takes about 1 hour to excute, is there anyway to reduce the excution time??

SELECT
CIQ.CIQ_STOCK_CODE,
QT.QT_CS_CODE AS Country,
QT.QT_REFERENCE_NUMBER AS "Order#",
CBD.CBD_NAME AS "Cust Name",
SP.SP_NAME AS "Sales Rep Name",
CC.CC_CHANNEL,
PIQ.PIQ_QUANTITY AS Qty,
QT.QT_CONFIRM_DATE,
QT.QT_REQUESTED_DELIVERY_DATE,
PIQ.PIQ_DESCRIPTION,
PRD.PRD_DESCRIPTION,
QT.QT_CUSTOMER_CODE,
QT.QT_STATUS,
QA.QA_S_WDP_1 AS "Work/Daytime Phone",
QA.QA_S_MBL_1 AS "Mobile Phone",
QA.QA_S_HP_1 AS "Home Phone",
QA.QA_PHONE_1 AS "Cust Phone 1",
QA.QA_PHONE_2 AS "Cust Phone 2",
QT.QT_DELIVERY_CHANGE AS FDD,
PIQ.PIQ_IS_CUSTOMER_KIT,
FROM
DM_QUOTATIONS@ISMANZ QT,
DM_PRODUCTS_IN_QUOTATION@ISMANZ PIQ,
DM_COMPONENTS_IN_QUOTATION@ISMANZ CIQ,
DM_SALES_PEOPLE@ISMANZ SP,
DM_QUOTATION_OTHER_DETAILS@ISMANZ QOD,
DM_CUSTOMER_BASIC_DETAILS@ISMANZ CBD,
SO_COST_CENTRES@ISMANZ CC,
DM_QUOTATION_ADDRESSES@ISMANZ QA,
DM_PRODUCT_SUB_GROUPS@ISMANZ PSG,
DM_PRODUCTS@ISMANZ PRD
WHERE
QT.QT_NUMBER =PIQ.PIQ_QT_NUMBER AND
PIQ.PIQ_SERIAL_NO = CIQ.CIQ_PIQ_SERIAL_NO AND
PIQ.PIQ_QT_NUMBER = CIQ.CIQ_PIQ_QT_NUMBER AND
CIQ.CIQ_PRD_CODE = PRD.PRD_CODE AND
CIQ.CIQ_PSG_CODE = PSG.PSG_CODE AND
QT.QT_NUMBER = QOD.QOD_QT_NUMBER AND
QOD.QOD_CS_CODE = CC.CC_CS_CODE AND
QOD.QOD_CC_CODE = CC.CC_CODE AND
QOD.QOD_CS_CODE = SP.SP_CS_CODE AND
QOD.QOD_SP_CODE_INTERNAL = SP.SP_CODE AND
QT.QT_CBD_NUMBER = CBD.CBD_NUMBER AND
QT.QT_NUMBER = QA.QA_QT_NUMBER AND
CIQ.CIQ_STOCK_CODE IN (Select CIQ_STOCK_CODE FROM VMIBackOrder ) AND
(QT.QT_CS_CODE='361'OR QT.QT_CS_CODE='391')AND
QT.QT_STATUS<=500 AND
QT.QT_TYPE='3' AND
QT.QT_OUT_OF_WAREHOUSE_DATE Is Null;

Thanks in advance.

Report message to a moderator

Re: optimatize query?? [message #250542 is a reply to message #250537]

Tue, 10 July 2007 06:56

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

Can you format your code according to the sticky in the forum.

Also to even attempt at trying to tune a query please post the results from explain plan and tkprof.

Report message to a moderator

Re: optimatize query?? [message #251332 is a reply to message #250537]

Fri, 13 July 2007 00:33

orafan23
Messages: 13
Registered: December 2005

Junior Member

Hi,It`s really tough to tune a query without execution plan or information on the objects being used.However,by a glance on the query I see the following joins
(QT.QT_CS_CODE='361'OR QT.QT_CS_CODE='391')AND
QT.QT_STATUS<=500 AND
QT.QT_TYPE='3' AND
QT.QT_OUT_OF_WAREHOUSE_DATE Is Null;

Try adding this as the from clause and make it an inline query,
say in your from clause something like this
************************************
From
(select QT.QT_CS_CODE Country,
QT.QT_REFERENCE_NUMBER "Order#",QT.QT_CONFIRM_DATE,
QT.QT_REQUESTED_DELIVERY_DATE , QT.QT_CUSTOMER_CODE,
QT.QT_STATUS,QT.QT_DELIVERY_CHANGE FDD,QT.QT_NUMBER,QT.QT_CBD_NUMBER,QT.QT_NUMBER
from DM_QUOTATIONS@ISMANZ A where
A.QT_STATUS<=500 AND
A.QT_TYPE='3' AND
A.QT_OUT_OF_WAREHOUSE_DATE Is Null) QT,
DM_PRODUCTS_IN_QUOTATION@ISMANZ PIQ,
DM_COMPONENTS_IN_QUOTATION@ISMANZ CIQ,
DM_SALES_PEOPLE@ISMANZ SP,
DM_QUOTATION_OTHER_DETAILS@ISMANZ QOD,
DM_CUSTOMER_BASIC_DETAILS@ISMANZ CBD,
SO_COST_CENTRES@ISMANZ CC,
DM_QUOTATION_ADDRESSES@ISMANZ QA,
DM_PRODUCT_SUB_GROUPS@ISMANZ PSG,
DM_PRODUCTS@ISMANZ PRD

WHERE
-------------------------------

Let know the execution plan of this query.

Thanks,
Raghava

Report message to a moderator

Previous Topic:	statspack table fetch continued row
Next Topic:	slow update on large table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 08:46:09 CST 2024