Java Security

Below is the Execution plan -
comreg_update_long.txt (50.98 KB)

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

hi , when i am attaching the execution plan , it is not taking because of so many characters
its weird problem -
you mentioned about row source stats , but also attached the sql monitor report when it was running -
since its update i can;t re run with statistics_level parameter

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

I've managed to look at the live report and it looks like the problem is that it's nested looping to your REP_OSJ_SWITCH_VW_BRANCH_BONUS view for the scalar subquery part of your update (the set = ) part. It is able to do this much more efficiently when it is executing it as part of the existence check as it can do it as a hash join and only do the full scans and merge joins once.
Your view has not been written with the performance of selecting with a REP_ID filter in mind.
A step forward, could be to materialize the entire view yourself first and using that in your statement:
with CTE (select * from COMSYS.REP_OSJ_SWITCH_VW_BRANCH_BONUS)
And then swapping out all mentions of the view with CTE. Just mentioning it twice (in the existence check and in your update clause) should be enough for it to work out if materializing is worth it (and I believe this will be a no-brainer for the CBO).
Another option is to help the CBO unnest the scalar subquery so it can execute the required join there using more batch friendly methods (like it's doing the exists check). A guess would be to change it so that you are selecting MAX(..) inside the scalar subquery instead of the non-aggregated columns, this is a requirement for the unnest scalar subquery transformation, but I'm not sure if it works with multiple columns or if it will get complicated due to the complexity of your view.

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

Hi Jonathan ,
Thank you - I used the alter session set "_with_subquery" = 'inline' and the update itself is finishing now in 24 sec 's - Before it was running between 30 and 45 mins -
How did you find out most of the time came from the SET clause of the update?- when i looked at plan i thought hash join semi at line 3 was for set clause and not for exists clause - how can i find out where this set clause is happening ? - there are 3 rep_id joins in the query with the main table - Also original SQL monitor plan was also showing wrong estimates at line number 10 and 11 - can this be an issue as well ?
I am not able to post the Execution with alter session set "_with_subquery" = 'inline' as the execution plan is too long and I am getting too many characters - i have to attach it as text pad -
Thanks
Bhavani .
disable_temp_transformation_exec_plan.txt (40.61 KB)

@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

@user-wvsc7
Looking at the SQL Monitor plan from the first file:
Operations 2 - 49 are the plan for identifying rows to be updated, operation 50 is the first line of the plan for the set subquery.
Operation 2 is a FILTER with two children, operation 3 and operation 46 - the is the second of your WHERE clause subqueries being operated at a filter subquery.
Operation 3 is a hash join SEMI which is your first where clause subquery being unnestead and being operated as a semi-join.
Operation 6 is the second child of the hash join, and it's a "view" of a TEMP TABLE TRANSFORMATION. Operation 7 is the LOAD AS SELECT which has three child rows - the first two are inline scalar subqueries in the select list of the third child. We can see that the LOAD AS SELECT is active for only one second. Its 3rd child (hash unique at operation 15) produces 274K rows.
Operation 21 apparently takes about 2,100 seconds active. But its chld operation is a WINDOW SORT PUSHED RANK which is sorting a view which produced 246K rows in one second. So we DO NOT BELIEVE that operation 21 is the cause of the 2,100 seconds, it's producing the rows we need to update very quickly but waiting for the "set subquery" to operate.

I have to stop at this point because my wife has just gone into a Zoom conference, but very quickly:
Operation 50 is the SET subquery, and we can see it executes 10,011 times. Then we can see from the Activity column that operations 78/79, 83/84, and 90-93 spend a huge amount of time on CPU - so that where we need to look for an explanation of how the time goes.
t.b.c.
Regards
Jonathan Lewis

I dont know how to inject this particular hint alter session set "_with_subquery" = 'inline'; in the app code and i can't create sql profile either because sql_id changes everytime this runs -
I tried hinting to see if it works and its not working -
UPDATE /*+ opt_param('_with_subquery','inline') */
bdhulipa.BRANCH_BONUS A
SET
(
EXCLUDE_FLAG,
REASON ,
LAST_OSJ_DATE
)
=
(
SELECT /*+ opt_param('_with_subquery','inline') */
'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 = A.REP_ID)
I also tried rewriting the code using with clause and its not working-
UPDATE comsys.branch_bonus_detail a a SET (exclude_flag,reason,last_osj_date ) =
select 'Y',a.reason|| '5 - CHANGED OSJ ON '|| rep_last_osj_swtch_dt|| '|',rep_last_osj_swtch_dt from
(
with cte as (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)u1
where u1.rep_id=a.rep_id)
WHERE
pay_period = 202018
AND EXISTS (
SELECT
'X'
FROM
comsys.rep_osj_switch_vw_branch_bonus
WHERE
rep_id = a.rep_id
AND rep_last_osj_swtch_dt >= TO_DATE('01/01/2013 00:00:00','mm/dd/yyyy hh24:mi:ss'))
)
AND EXISTS (
SELECT
'x'
FROM
comsys.rep
WHERE
rep_id = a.rep_id
AND master_bank_id IS NULL
AND nvl(master_id, rep_id) NOT IN (
SELECT DISTINCT
master_bank_id
FROM
comsys.rep
WHERE
master_bank_id IS NOT NULL
)
);
ERROR at line 3:
ORA-01767: UPDATE ... SET expression must be a subquery

Unfortunately the _with_subquery parameter isn't considered to be an optimizer parameter. And until 19.3 I can't seem to get the inline() hint to apply to a query block other than the one it's in, otherwise that would be something you could use to override the parameter effect.
About the online thing you could do, perhaps, is to run the query with the parameter set, copy the set of hints produced in the OUTLINE information, and then write that set of hints into the SQL. (Which would work if the only difference in the statements was some injected literal).
Regards
Jonathan Lewis

Hi,
When I need to perform an UPDATE whose SET clause is going to be using the same subquery as the one in the WHERE clause, I tend to go for a MERGE statement then. The rows of interest will be selected in the query within the USING clause, so the rows are read only once. Your statement could be rewritten this way (not tested, so there may be typos):

MERGE INTO COMSYS.BRANCH_BONUS_DETAIL A
USING (SELECT B.REP_ID,
             B.REP_LAST_OSJ_SWTCH_DT
       FROM COMSYS.REP_OSJ_SWITCH_VW_BRANCH_BONUS B
       WHERE REP_LAST_OSJ_SWTCH_DT >= :B2
        AND EXISTS ( SELECT
                       'x'
                     FROM
                          COMSYS.REP R1
                     WHERE
                          R1.REP_ID              = B.REP_ID
                     AND    R1.MASTER_BANK_ID IS NULL
                     AND    NVL(R1.MASTER_ID, R1.REP_ID) NOT IN
                             (
                                     SELECT R2.MASTER_BANK_ID
                                       FROM
                                               COMSYS.REP R2
                                       WHERE
                                               R2.MASTER_BANK_ID IS NOT NULL))                                               

     ) v
ON (V.REP_ID = A.REP_ID
   AND A.PAY_PERIOD = :B1)
WHEN MATCHED THEN UPDATE SET A.EXCLUDE_FLAG = 'Y',
                            A.REASON      = A.REASON || '5 - CHANGED OSJ ON ' || V.REP_LAST_OSJ_SWTCH_DT || '|', = 
                            A.LAST_OSJ_DATE = V.REP_LAST_OSJ_SWTCH_DT;