Tuning Update query
From: Lok P <loknath.73_at_gmail.com>
Date: Mon, 3 May 2021 00:23:12 +0530
Message-ID: <CAKna9VZ4d+xx1BANgmqgB1V4CihN_NAWbODZb-B9JeofYp3VqA_at_mail.gmail.com>
Hello, We have a plsql procedure in which it updates multiple columns of the global temporary table after loading it in different steps. It's updated for different filter criteria and each of these UPDATES are running for hours even if they are simple ones. I am trying to see if it's possible to do it in an efficient fashion by combining all different updates into one UPDATE statement or any other possible method? One initial thought was to do it as part of the INSERT itself rather than updating it through multiple statements at the later stage , but then I see the data load/INSERT is happening for multiple if/else conditions which may not be straight enough.
Date: Mon, 3 May 2021 00:23:12 +0530
Message-ID: <CAKna9VZ4d+xx1BANgmqgB1V4CihN_NAWbODZb-B9JeofYp3VqA_at_mail.gmail.com>
Hello, We have a plsql procedure in which it updates multiple columns of the global temporary table after loading it in different steps. It's updated for different filter criteria and each of these UPDATES are running for hours even if they are simple ones. I am trying to see if it's possible to do it in an efficient fashion by combining all different updates into one UPDATE statement or any other possible method? One initial thought was to do it as part of the INSERT itself rather than updating it through multiple statements at the later stage , but then I see the data load/INSERT is happening for multiple if/else conditions which may not be straight enough.
Attached is the sql monitor for few of the long running UPDATES. And i am seeing this is not really spending time for updating rows but rather in finding the eligible rows for update i.e. the SELECT part of it is actually taking time. So wanted to understand how these UPDATES can be done faster?
Its version 11.2.0.4 of Oracle Exadata-X5.
Regards
Lok
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 02 2021 - 20:53:12 CEST
- text/plain attachment: Sql_Monitor_36.txt