Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 12c, Unix)
Query Tuning [message #657919] Sun, 27 November 2016 23:14 Go to next message
pstanand
Messages: 120
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;

/forum/fa/13334/0/


[mod-edit: image inserted into message body by bb]

[Updated on: Mon, 28 November 2016 15:15] by Moderator

Report message to a moderator

Re: Query Tuning [message #657921 is a reply to message #657919] Mon, 28 November 2016 00:28 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Previous Topic: DATABASE TUNING PRINCIPLE AND DATABASE TROUBLESHOOTING.
Next Topic: java.sql.SQLTimeoutException: ORA-01013
Goto Forum:
  


Current Time: Fri Jan 19 18:26:23 CST 2018

Total time taken to generate the page: 0.05739 seconds