Update is running long with temp transformation in execution plan

The temp table transformation would be in the plan if you are using a WITH clause and the optimizer has decided it should be materialized. Comparing your attached plan (please just paste as others are not so carefree with what they download from untrusted sources), COMSYS.REP_OSJ_SWITCH_VW_BRANCH_BONUS is really a view.

Just looking at the plan is not enough to know where the time is going, I recommend including the row source statistics to get an idea of where the time is mainly being spent. Have a read of https://ctandrewsayer.wordpress.com/2017/03/21/4-easy-lessons-to-enhance-your-performance-diagnostics/ I go over getting row source statistics in section 4.

The general advise would be to look at the definition of your view and see what you can simplify it to within your statement. If you can take out all the parts where it is trying to sort large tables then you will probably see some easy wins.

-

Please ignore the random bold words in my reply, the formatting just applied itself halfway through typing my response and I can't seem to remove it.

-edit-

Seems like the words are only appearing bold when editing the comment, weird

-edit

Ive just checked the file again and spotted the SQL live report, I’ll have a read of that shortly!

-edit

How long does it take to completely execute? Is it not possible to run on your development/testing server and rollback? (I trust you are running this on something other than your production environment first)

Alternatively you can get a good enough representation of the work required by rewriting it to a select query, selecting that scalar subquery instead of updating a column to it.

If there’s no way it completes in a reasonable time to get this information, then you could check out the real time live sql monitor report (if you’re licenced for the diagnostic+tuning Packs) or if you just have diagnostic pack licenced you can look at which events get hit on which lines of your plan and how often by querying v$active_session_history.

Otherwise we will have to resort to guessing a little, potentially missing out the easy low hanging fruit. At a minimum I would suggest obtaining the session events summary for a session running your statement (include a few minutes if it takes too long to wait for the entire thing to complete.

As @AndrewSayer pointed out, most of your time comes from the SET clause of your update where you query the view REP_OSJ_SWITCH_VW_BRANCH_BONUS by rep_id - and in the SQL Monitor report you posted this was done 10,000 times (possibly less than the maximum needed thanks to some scalar subquery caching).

Inside that view you seem to have a CTE ("with" subquery) that is materialized to a table with 274K rows which is then scanned twice (operaitons 79 and 84) to pick (I think) one row each time you scan. And then you have a hash join (operations 90 - 93) which includes another scan and then join of the same data. Since this set of events happens 10,000 times, that's a massive CPU workload.

One strategy to consider is to extract just the SET subquery and think about optimising that for one REP_ID. In other words, optimise:

               SELECT
                       'Y',
                       A.REASON
                               ||'5 - CHANGED OSJ ON '
                               ||REP_LAST_OSJ_SWTCH_DT
                               ||'|',
                       REP_LAST_OSJ_SWTCH_DT
               FROM
                       COMSYS.REP_OSJ_SWITCH_VW_BRANCH_BONUS
               WHERE
                       REP_ID = {some suitable constant}
;

My first thought was that you might simply disable (for the session) the option to materialize CTEs and see what happens. RUn the SQL Monitor against a single select and then multiply by 10,000 to see if that saves you enough time. I believe that you can do this test by setting a hidden parameter

alter session set "_with_subquery" = 'inline';

Regards

Jonathan Lewis

@OracleUser_WVSC7

I'm thinking of using your before and after plans as an example of trouble-shooting non-trivial execution plans. It may take some to time to, and I think I'm going to see if I can use Prezi to create a presentation and video of it.

In the meantime, a fews the table generic pointers:

1) update tableX set columns = {subquery1}, columns = {subquery2} where {subquery} and {subquery} and ... will produce an execution plan where the first child to the update is the part that identifies rows to be updated. and the second and subsequent child operations correspond to the subqueries in the set clause.

2) Inline scalar subqueries in the select list appear in the plan before the plan for their driving query block but at the same indentation (though there is a least one bug with the indentation in complex cases).

3) If you have a materialized CTE (temp table transformation) then the plans to populate the resulting temporary table appear before the plan for the main query block that uses them, and at the same indentation: in other words the "Load as select"s and main query block are all children of the Temp Table Transformation and the mainquery block is the last child.

4) When you find operations in a query plan that appear to take a long time you have to be careful when you work out which one is the cause and which one is the effect. For example if you have a hash join and (probe/2nd) table tablescan that both take a long time is the hash join taking a long time because it's waiting for the tablescan, or is the tablescan taking a long time because the hash join is very slow calling for more rows because it's using a lot of CPU to do some complicated calculations with each batch of rows it gets from the tablescan.

Now you have to combine these guildelines to untangle your execution plan - and that's not easy (and it's got a little extra twist because you always have to watch out for IN/EXISTS subqueries in the WHERE clause being transformed through things like unnesting and complex view merging.

Pausing at that point to look at the original plan and to give you a break before I make a few specific comments in the next post.

Regards

Jonathan Lewis