OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 12c, Unix)

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

Query Tuning [message #657919]

Sun, 27 November 2016 23:14

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi Friends,
Thanks for your support so far.
I have the below query which is incorporated in the .NET code. When executing the application this query takes long time.
I have attached the explain plan for the same.
Request your help to fine tune this query. This has been already converted into MERGE.
Is there any other way this query can be fine tuned.
Please provide a suggestion for this.
Thanks.

MERGE INTO M_PRICES 
USING (SELECT M_PRICES.ROWID row_id, 
              oldprice.price AS oldpricecur 
         FROM M_PRICES, 
              OLDPRICE, 
              M_PRODUCTS 
        WHERE M_PRICES.PRODUCTID = M_PRODUCTS.PRODUCTID 
          AND lower(nvl(trim(both ' ' from M_products.topitemno),' ')) = lower(nvl(trim(both ' ' from OLDPRICE.TOPITEMNO),' ')) 
          AND lower(nvl(trim(both ' ' from M_products.subitemno),' ')) = lower(nvl(trim(both ' ' from OLDPRICE.SUBITEMNO),' '))  
          AND M_PRICES.CURRENCYCODE = OLDPRICE.CURRENCYCODE 
          AND M_PRICES.COUNTRYCODE = OLDPRICE.COUNTRYCODE 
          AND M_PRICES.INCOTERM = OLDPRICE.INCOTERM) src 
ON ( M_PRICES.ROWID = src.row_id ) 
WHEN MATCHED THEN 
UPDATE 
   SET oldpricecur = src.oldpricecur;