RE: Tuning Update query

AND (not but, JL’s note is an example of knowing a lot and thinking clearly; that’s the norm for JL but segregating the four key problems so succinctly is excellent even for JL),  

it appears you have four disjoint P.CODE values drawn from P. If those are a small fraction of P, you might do well pulling the required columns for P into P_XX, P_YY, …, etc. as GTTs or materialized inline views as well as starting with GTTs GTT_TAB_XX, _YY, …, etc. to begin with instead of mixing them up and then filtering through them.  

Don’t ignore the if regarding the part before the “as well as.” Since they are disjoint sets, breaking up the initial GTT makes sense regardless of the fraction of each with respect to P. I doesn’t make sense to mix them together and then filter through them. This, of course, needs to be done in place of how GTT_TAB is currently created. Don’t screw up where you use ‘AA’ twice, and you may want to pull ‘XX’ into two different GTTs, one where CIND is ‘Y’ and the other where CIND is something else.  

If you’re using the Tim Gorman “Scaling to Infinity” (the fastest update is an insert) approach, you may want to destroy/purge each GTT as soon as you are done with it. Materialized inline views will take care of themselves but you can index GTTs at a sufficient release.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Monday, May 03, 2021 4:55 AM
To: Lok P
Cc: Oracle L
Subject: Re: Tuning Update query  

If you're updating many columns of every row in a large (global temporary) table using lots of separate statements that use the "set .. subquery" strategy you are introducing 4 problems.  

1. Updating lots of columns means you will probably start doing lots of row migration - and you can't set pctfree on GTTs to avoid this, it WILL be 10%
2. You will be generating a huge amount of undo
3. If you aren't including a "where exists" predicate on the source table that matches the predicate on the set subquery you will be updating rows to null that don't need to be updated - the monitor report echoed by Mladen showed 17M rows updated, but only 8M executions of the subquery - which probably means 9M rows in the GTT updated to NULL.

In your specific case there's (probably) a logic flaw in the update statement. You have a "where tmp.cind='Y'" in the subquery. This means (effectively) set the values to NULL when tmp.cind ='Y', NOT 'don't update the rows where tmp.cind='Y'. If you want the update to be selective then you need the predicate as part of the where clause on the update.  

Finally -

If this is a table of roughly 17M rows (rather than the 8billion of the estimate) then the fastest strategy would probably be to

1. See if you can minimise the number of update ste[ps by updating from a JOIN of several source tables
2. Rewrite the code to step through a series of GTT doing

        insert /*+ append */ into next_gtt select from previous_gtt join {source tables}  

Using insert /*+ append */ with gtts will:

1. use bulk processing rather that the row by row processing for filter subqueries
2. miimise BOTH undo and redo.

Regards

Jonathan Lewis          

On Sun, 2 May 2021 at 19:53, Lok P <loknath.73_at_gmail.com> wrote:

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-l