How many rows were inserted/updated/deleted in my merge?

Before we get started, I need to make one thing very clear.

Do not attempt this unless you have paid for the tuning pack licence!

I’ve seen this asked a few times and have always thought it was just unachievable, but I discovered this by accident today so thought I’d share.

First, let’s have some test data. I’m super lazy so let’s get QuickSQL to do the work for us, we go to quickSQL, and enter the following as our data model

# pk: "identify"
employees_trg /insert 5
 name /nn vc50
 email /lower
 cost center num
 date hired
 job
employees_mrg /insert 10
 name /nn vc50
 email /lower
 cost center num
 date hired
 job

(Okay, I admit I copied one of the sample ones and made some tweaks and took out the triggers from the generated code, I’m that lazy)

I won’t include the full script it came out with, you can run it yourself and see. But here is the data:

select * from employees_trg;

  ID NAME               EMAIL                        COST_CENTER DATE_HIRED          JOB
---- ------------------ ---------------------------- ----------- ------------------- ------------------------
   1 Gricelda Luebbers  gricelda.luebbers@ab4z.com            17 13/10/2017 22:46:33 Executive Engineer
   2 Dean Bollich       dean.bollich@ab40.com                  7 14/07/2017 22:46:33 Data Architect
   3 Milo Manoni        milo.manoni@ab41.com                  26 19/08/2017 22:46:33 Marketing Associate
   4 Laurice Karl       laurice.karl@ab42.com                  3 13/08/2017 22:46:34 Customer Advocate
   5 August Rupel       august.rupel@ab43.com                 75 17/08/2017 22:46:34 Web Developer

select * from employees_mrg;

  ID NAME                EMAIL                        COST_CENTER DATE_HIRED          JOB
---- ------------------- ---------------------------- ----------- ------------------- ---------------------------
   1 Gricelda Luebbers   gricelda.luebbers@ab4z.com            62 05/08/2017 22:46:34 Solustions Specialist
   2 Dean Bollich        dean.bollich@ab40.com                 37 08/08/2017 22:46:34 Help Desk Specialist
   3 Milo Manoni         milo.manoni@ab41.com                  52 13/07/2017 22:46:34 Application Developer
   4 Laurice Karl        laurice.karl@ab42.com                 94 20/07/2017 22:46:34 Quality Control Specialist
   5 August Rupel        august.rupel@ab43.com                  5 20/08/2017 22:46:35 Analyst
   6 Salome Guisti       salome.guisti@ab44.com                18 28/07/2017 22:46:35 Network Architect
   7 Lovie Ritacco       lovie.ritacco@ab45.com                21 06/09/2017 22:46:35 Programmer Analyst
   8 Chaya Greczkowski   chaya.greczkowski@ab46.com            36 26/07/2017 22:46:35 Application Developer
   9 Twila Coolbeth      twila.coolbeth@ab47.com               81 24/08/2017 22:46:35 Sustaining Engineering
  10 Carlotta Achenbach  carlotta.achenbach@ab48.com           11 12/10/2017 22:46:35 Support Specialist

We have two employees tables, one target, one merge. Now let’s write out the merge, we want to include inserts/updates/deletes all with filters (to show off). I’ve also added some extra information so we can double check our numbers.

As you may have guessed by the bold statement at the top, we need to monitor the execution of this SQL. Easy enough if we add a /+monitor/ hint, otherwise it will need to take at least 5 seconds or run in parallel to be automatically monitored.

merge /*+monitor*/ into employees_trg t
using employees_mrg m
on (t.id = m.id)
when matched then update
set t.name = m.name
   ,t.job  = 'Updated'
where  length(t.email) <= length(m.email)
delete where t.date_hired = sysdate-50;

7 rows merged.

select * from EMPLOYEES_TRG;

   ID NAME                EMAIL                        COST_CENTER DATE_HIRED          JOB
----- ------------------- ---------------------------- ----------- ------------------- -----------
    1 Gricelda Luebbers   gricelda.luebbers@ab4z.com            17 13/10/2017 23:09:34 Updated
    3 Milo Manoni         milo.manoni@ab41.com                  26 19/08/2017 23:09:34 Updated
    4 Laurice Karl        laurice.karl@ab42.com                  3 13/08/2017 23:09:34 Updated
   27 Lovie Ritacco       lovie.ritacco@ab45.com                21 06/09/2017 23:09:35 Inserted
   30 Carlotta Achenbach  carlotta.achenbach@ab48.com           11 12/10/2017 23:09:36 Inserted

So we have 3 updated rows, 2 inserted rows, and we can see we’re missing id 2 and 5 so they must have been deleted.

And let’s check out the SQL Live Monitor report. First we get the SQL_ID for our statement, we can use the sql_find script that I shared in 4 EASY lessons to enhance your performance diagnostics

@sql_find "merge /*+monitor*/ into employees_trg"
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED
------------- ------------------- ---------- ----------- ---------- --------------
SQL_TEXT
-----------------------------------------------------------------------------------------------------
2qcnzh2cqfugz 19/10/2017 23:10:00          1           1          0              7
merge /*+monitor*/ into employees_trg t using employees_mrg m on (t.id = m.id) when matched then upd

Now we can run this sql_id through the dbms_sql_monitor report_sql_monitor function.

set lines 250
col report for a250
set long 2000000000
set longch 250
select dbms_sql_monitor.report_sql_monitor
(sql_id=> '2qcnzh2cqfugz'
,type=>'TEXT'
,report_level=>'all'
) report
from dual;
Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  ANDY (259:56483)
 SQL ID              :  2qcnzh2cqfugz
 SQL Execution ID    :  16777216
 Execution Started   :  10/19/2017 23:10:00
 First Refresh Time  :  10/19/2017 23:10:00
 Last Refresh Time   :  10/19/2017 23:10:00
 Duration            :  .018252s
 Module/Action       :  SQL*Plus/-
 Service             :  pdb1.andrew
 Program             :  sqlplus.exe

Global Stats
========================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |
========================================================
|    0.02 |    0.02 |     0.00 |     89 |    1 |  8192 |
========================================================

SQL Plan Monitoring Details (Plan Hash Value=2280051147)
=================================================================================================================================================
| Id |         Operation          |     Name      |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                            |               | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
=================================================================================================================================================
|  0 | MERGE STATEMENT            |               |         |      |         1 |     +0 |     1 |        2 |     . |          |                 |
|  1 |   MERGE                    | EMPLOYEES_TRG |         |      |         1 |     +0 |     1 |        2 |     . |          |                 |
|  2 |    VIEW                    |               |         |      |         1 |     +0 |     1 |       10 |     . |          |                 |
|  3 |     SEQUENCE               | ISEQ$$_80273  |         |      |         1 |     +0 |     1 |       10 |     . |          |                 |
|  4 |      HASH JOIN RIGHT OUTER |               |      10 |    6 |         1 |     +0 |     1 |       10 | 723KB |          |                 |
|  5 |       TABLE ACCESS FULL    | EMPLOYEES_TRG |       5 |    3 |         1 |     +0 |     1 |        5 |     . |          |                 |
|  6 |       TABLE ACCESS FULL    | EMPLOYEES_MRG |      10 |    3 |         1 |     +0 |     1 |       10 |     . |          |                 |
=================================================================================================================================================

We can’t really tell much from this about what DML was really done, we can see the 2 rows on line 1, but it’s not obvious what that could refer to.

I wrote the following query when I discovered that hidden in the depths of v$sql_plan_monitor were some pivoted out columns that can be used to determine why DOP downgrade was experienced. This was something I knew was possible in 12c thanks to a few blog posts mentioning it in the Active/Flash version of the monitor report, but I hadn’t figured out how to get the information from the views until now.
This is the generic query:

var sql_id varchar2(100)
exec :sql_id := '2qcnzh2cqfugz'

col value_text for a53
prompt Reducing length of column as we won't be using it here.
col value_text for a10
col description for a100

select up.plan_line_id
      ,sms.name
      ,up.value
      ,dop_down.value_text
      ,sms.description
from   (
select * from (
select sql_id
      ,plan_line_id
      ,otherstat_1_id
      ,otherstat_1_type
      ,otherstat_1_value
      ,otherstat_2_id
      ,otherstat_2_type
      ,otherstat_2_value
      ,otherstat_3_id
      ,otherstat_3_type
      ,otherstat_3_value
      ,otherstat_4_id
      ,otherstat_4_type
      ,otherstat_4_value
      ,otherstat_5_id
      ,otherstat_5_type
      ,otherstat_5_value
      ,otherstat_6_id
      ,otherstat_6_type
      ,otherstat_6_value
      ,otherstat_7_id
      ,otherstat_7_type
      ,otherstat_7_value
      ,otherstat_8_id
      ,otherstat_8_type
      ,otherstat_8_value
      ,otherstat_9_id
      ,otherstat_9_type
      ,otherstat_9_value
      ,otherstat_10_id
      ,otherstat_10_type
      ,otherstat_10_value
from v$sql_plan_monitor spm
where spm.sql_id = :sql_id
and   spm.sql_exec_id = (select max(sql_exec_id) from v$sql_monitor sm where sm.sql_id = :sql_id) -- this will identify the same execution that the report was using
)
unpivot ((id,type,value)
for pivId in ((otherstat_1_id ,otherstat_1_type ,otherstat_1_value ) as 1
             ,(otherstat_2_id ,otherstat_2_type ,otherstat_2_value ) as 2
             ,(otherstat_3_id ,otherstat_3_type ,otherstat_3_value ) as 3
             ,(otherstat_4_id ,otherstat_4_type ,otherstat_4_value ) as 4
             ,(otherstat_5_id ,otherstat_5_type ,otherstat_5_value ) as 5
             ,(otherstat_6_id ,otherstat_6_type ,otherstat_6_value ) as 6
             ,(otherstat_7_id ,otherstat_7_type ,otherstat_7_value ) as 7
             ,(otherstat_8_id ,otherstat_8_type ,otherstat_8_value ) as 8
             ,(otherstat_9_id ,otherstat_9_type ,otherstat_9_value ) as 9
             ,(otherstat_10_id,otherstat_10_type,otherstat_10_value) as 10
             )
       )
) up
left join v$sql_monitor_statname sms
  on up.id = sms.id
left join (select 'downgrade reason' name, 350 value , 'DOP downgrade due to adaptive DOP'                     value_text from dual union all
           select 'downgrade reason' name, 351 value , 'DOP downgrade due to resource manager max DOP'         value_text from dual union all
           select 'downgrade reason' name, 352 value , 'DOP downgrade due to insufficient number of processes' value_text from dual union all
           select 'downgrade reason' name, 353 value , 'DOP downgrade because slaves failed to join'           value_text from dual
          ) dop_down
  on sms.name = dop_down.name
 and up.value = dop_down.value
order by 1,2
/

PLAN_LINE_ID NAME                                VALUE VALUE_TEXT DESCRIPTION
------------ ------------------------------ ---------- ---------- -----------------------------------------------------------------
           1 MERGE deleted row count                 2            count of rows deleted by MERGE
           1 MERGE inserted row count                2            count of rows inserted by MERGE
           1 MERGE updated row count                 3            count of rows updated by MERGE
           4 Build Row Count                         5            Number of rows for the build
           4 Build Size                         491520            Size of the build input in bytes
           4 Fan-out                                 8            Number of partitions used to split both inputs
           4 Slot Size                          122880            Size of an in-memory hash-join slot
           4 Total Build Partitions                  8            Total number of build partitions
           4 Total Cached Partitions                 8            Total number of build partitions left in-memory before probing

We can clearly see that we inserted 2 rows, updated 3 and deleted 2, exactly what our data suggested.

On top of this, we have some cool stats that might be interesting if we were looking into what was going on in line 4 (the hash join).

Be sure to take a look at the other available stats in v$sql_monitor_statname. I’d love to see some examples of these.

Advertisements