Oracle 19c Sql Performance Analyzer (SPA) by example

Dear Readers,

SPA by example:

Step 1)
Get the STS name

select count(*), sqlset_name from dba_sqlset_statements group by sqlset_name order by 2;

Step 2)
Create an analysis task

Command:

VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');

Actual Execution:

SQL> VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');SQL>
PL/SQL procedure successfully completed.
SQL>
You have options to configure the analyze task.

Step 3)

Create pre change sql trail task

Command:

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', - 
       execution_name => 'my_exec_BEFORE_change');

Actual output:

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', -
       execution_name => 'my_exec_BEFORE_change');> >
PL/SQL procedure successfully completed.
SQL>

I wanted to update my init param filesystemio_options from none to setall, to see the effect. I am using cooked filesystem to store my datafile.

12:40:16 SQL> sho parameter filesyste
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

12:47:33 SQL> sho parameter filesys
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL
12:47:46 SQL>

Param changed and db bounced.

Step 4)

Create post change sql trail task

Command:

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', - 
       execution_name => 'my_exec_AFTER_change');

Actual output:

12:47:46 SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', -
       execution_name => 'my_exec_AFTER_change');12:48:29 > 12:48:29 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.00

12:48:34 SQL>

Step 5)

Compare the pre/post analysis task result

Command:

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', - 
       execution_type => 'COMPARE PERFORMANCE', -
       execution_name => 'my_exec_compare', -
       execution_params => dbms_advisor.arglist(-
                             'comparison_metric', 'elapsed_time'));
overall arglist supported:
elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, optimizer_cost, and io_interconnect_bytes

Actual output:

12:55:37 SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'COMPARE PERFORMANCE', -
       execution_name => 'my_exec_compare', -
12:58:21 > 12:58:21 > 12:58:21 >        execution_params => dbms_advisor.arglist(-
                             'comparison_metric', 'elapsed_time'));12:58:21 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
12:58:22 SQL>

Step 6)
Report the result:
Active html format:

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool spa_active.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'my_spa_task',
          type => 'active', section => 'all') FROM dual;
spool off
Text form:
VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
                'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep

Actual output:

12:59:55 SQL> 12:59:55 SQL> VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
                'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep13:00:32 SQL> 13:00:32 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08

13:00:32 SQL> 13:00:32 SQL>
General Information
---------------------------------------------------------------------------------------------
 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : my_spa_task                     SQL Tuning Set Name        : SQLT_WKLD_STS
  Task Owner   : SYS                             SQL Tuning Set Owner       : SYS
  Description  :                                 Total SQL Statement Count  : 27
Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name             : my_exec_compare        Started             : 08/15/2022 12:58:22
  Execution Type             : COMPARE PERFORMANCE    Last Updated        : 08/15/2022 12:58:22
  Description                :                        Global Time Limit   : UNLIMITED
  Scope                      : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status                     : COMPLETED              Number of Errors    : 0
  Number of Unsupported SQL  : 14
Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : my_exec_BEFORE_change    Execution Name      : my_exec_AFTER_change
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 08/15/2022 12:40:04      Started             : 08/15/2022 12:48:30
  Last Updated        : 08/15/2022 12:40:07      Last Updated        : 08/15/2022 12:48:34
  Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                   Per-SQL Time Limit  : UNUSED
  Number of Errors    : 0                        Number of Errors    : 0
 ---------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  -53.36%
 Improvement Impact  :  2.95%
 Regression Impact   :  -56.31%
SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall              27                  0
 Improved              2                  0
 Regressed             3                  0
 Unchanged             8                  0
 Unsupported          14                  0
Top 13 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric | Metric | Impact    | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before | After  | on SQL    | Change |
--------------------------------------------------------------------------------------------
|        65 | 3rnkw340dt3mj |   -51.07% |     12819 |      7 |    107 | -1428.57% | n      |
|        59 | 06852xf36pjwr |    -3.41% |         1 | 170068 | 255758 |   -50.39% | n      |
|        68 | 4krsqvh5cgrus |     1.94% |         1 | 482556 | 433917 |    10.08% | n      |
|        82 | fcyayutm5frcc |    -1.83% |      6576 |      9 |     16 |   -77.78% | n      |
|        60 | 0cwuxyv314wcg |     1.02% |      8508 |      9 |      6 |    33.33% | n      |
|        70 | 5tq075cva3dga |     -.62% |      7725 |     14 |     16 |   -14.29% | n      |
|        66 | 4g2g8zv8tr8vv |      .51% |     12819 |     13 |     12 |     7.69% | n      |
|        74 | a8ntu3081hfgw |      .51% |      1277 |     20 |     10 |       50% | n      |
|        69 | 5mz8u3b34u9gw |       .5% |     12579 |     17 |     16 |     5.88% | n      |
|        81 | f90zn75aphu4w |      .35% |      1261 |    722 |    715 |      .97% | n      |
|        67 | 4hbzjyh4p336s |      .15% |      1277 |     18 |     15 |    16.67% | n      |
|        63 | 29rsy84cajnjd |        0% |      5094 |     14 |     14 |        0% | n      |
|        77 | bswc46zum45tj |        0% |     12579 |      9 |      9 |        0% | n      |
--------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

13:00:33 SQL>

SPA has lot of limitation like it cant do DDL, PDML limitation etc... this made in my test nearly 14/27 sql unsupported. Which means nearly 50% of the sql execution arent supported.
I was hoping the filesystemio option brings in postive benefit, but it wasnt.
As such SPA and STS are very stright forward. Can use it as part of performance engineering.

Thanks

Sql Tuning Set (STS) creation in 19c

 Dear Readers,

In this blog, we will see how we can create a Sql Tuning Set in 19c Oracle database.

Procedure to create Sql Tuning Set

Step 1)
Ensure you have Tuning Pack licesnse.

Step 2)
We will use Pl/Sql API to create and load the sql tuning set. To create the STS...

Command:

BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS',description  => 'STS to store SQL from the private SQL area');
END;

Actual execution:

SQL> BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS',description  => 'STS to store SQL from the private SQL area');
END;  2    3    4
  5  /
PL/SQL procedure successfully completed.
SQL>

Step 3)
Select the sqls to be loaded into the STS.

Command:

DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE( 
            DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(
            begin_snap =>17
            ,end_snap=>18
            ,basic_filter => 
            ' module = ''wish8.6@10.0.2.15 (TNS V1-V3)'' ')
          ) p;
-- load the tuning set
  DBMS_SQLSET.LOAD_SQLSET (  
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor 
);
END;
/

Actual execution:

SQL> DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE(
            DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(
            begin_snap =>17
            ,end_snap=>18
            ,basic_filter =>
            ' module = ''wish8.6@10.0.2.15 (TNS V1-V3)'' ')
          ) p;
-- load the tuning set
  DBMS_SQLSET.LOAD_SQLSET (
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor
);
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19
PL/SQL procedure successfully completed.
SQL>

--> less than 1 minute for the above step.

Step 4)
Verify the loaded sqls.

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999
SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
       ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) )
order by 4;

>> by default it sorts in asc.

Now the result:

SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
ap249zmtsh0hc TPC INSERT INTO new_order (no_o_id      75898        1775
              C   , no_d_id, no_w_id) values (:o
                  _id, :o_d_id, :o_w_i
70khh6rkrtwgj TPC INSERT INTO history (h_c_id, h     184359        5337
              C   _c_d_id, h_c_w_id, h_w_id, h_d
                  _id, h_date, h_amoun
...
aw9ttz9acxbc3 TPC BEGIN payment(:p_w_id,:p_d_id,    7997748      158122
              C   :p_c_w_id,:p_c_d_id,:p_c_id,:b
                  yname,:p_h_amount,:p
16dhat4ta7xs9 TPC begin neword(:no_w_id,:no_max_   12292007     1170785
              C   w_id,:no_d_id,:no_c_id,:no_o_o
                  l_cnt,:no_c_discount

27 rows selected.

If you look at the last row... comparing this aginsts AWR report....

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
            12.3         12,579          0.00    9.3   95.2     .3 16dhat4ta7xs9
Module: wish8.6@10.0.2.15 (TNS V1-V3)
begin neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:no_o_ol_cnt,:no_c_discount
,:no_c_last,:no_c_credit,:no_d_tax,:no_w_tax,:no_d_next_o_id,TO_DATE(:timestamp,
'YYYYMMDDHH24MISS')); END;
             8.0         12,819          0.00    6.0   94.0     .0 aw9ttz9acxbc3
Module: wish8.6@10.0.2.15 (TNS V1-V3)
BEGIN payment(:p_w_id,:p_d_id,:p_c_w_id,:p_c_d_id,:p_c_id,:byname,:p_h_amount,:p
_c_last,:p_w_street_1,:p_w_street_2,:p_w_city,:p_w_state,:p_w_zip,:p_d_street_1,
:p_d_street_2,:p_d_city,:p_d_state,:p_d_zip,:p_c_first,:p_c_middle,:p_c_street_1
,:p_c_street_2,:p_c_city,:p_c_state,:p_c_zip,:p_c_phone,:p_c_since,:p_c_credit,:

You can notice the top 2 sqls by elapsed time shown in AWR are matching with our STS.

Thanks

Breakup of Oracle Foreground and Background wait class metrics history from AWR views

 Dear Readers,

In this blog, we are going to look at the possibilities of reporting the foreground and background wait class metrics. Oracle in the AWR doesnt report the foreground and background wait class metrics separately, it only reports the foreground metrics by wait class; but not the background.

So I wrote a sql analytic query based on dba_hist_system_event view to report the metrics...

Query:

with t as
(
select /*+ materialize */ snap_id
,wait_class
,sum(total_waits) totwts
,sum(time_waited_micro) tmwt
,sum(total_waits_fg) totwtsfg
,sum(time_Waited_micro_fg) tmwtfg
,(sum(total_waits)-sum(total_waits_fg)) totwtsbg
,(sum(time_waited_micro)-sum(time_waited_micro_fg)) tmwtbg
from dba_hist_system_event
group by snap_id
,wait_class
)
select t.wait_class
,(t.totwts-a.totwts) total_waits
,(t.tmwt-a.tmwt) total_time_waited_us
,(t.totwtsfg-a.totwtsfg) total_waits_fg
,(t.tmwtfg-a.tmwtfg) time_waited_fg_us
,(t.totwtsbg-a.totwtsbg) total_waits_bg
,(t.tmwtbg-a.tmwtbg) time_waited_bg_us
from
t ,t a
where t.snap_id=18
and a.snap_id=(t.snap_id-1)
and a.wait_Class=t.wait_class
order by 1,2;

Result:

WAIT_CLASS                    ,TOTAL_WAITS,TOTAL_TIME_WAITED_US,TOTAL_WAITS_FG,TIME_WAITED_FG_US,TOTAL_WAITS_BG,TIME_WAITED_BG_US

------------------------------,-----------,--------------------,--------------,-----------------,--------------,-----------------

Administrative                ,         32,                 189,            32,              189,             0,               0

Application                   ,         17,               24645,            17,            24645,             0,               0

Commit                        ,      26770,            84755489,         26759,         84639135,            11,          116354

Concurrency                   ,          2,               25996,             0,                0,             2,           25996

Configuration                 ,         13,              922471,            13,           922471,             0,               0

Idle                          ,     168764,          1.1192E+11,        105007,       5855698235,         63757,       1.0606E+11

Network                       ,     120130,             1099890,        120024,          1099821,           106,              69

Other                         ,       6035,            76421612,          4793,           696732,          1242,        75724880

Scheduler                     ,          0,                   0,             0,                0,             0,               0

System I/O                    ,      40147,           119983483,          1009,            93039,         39138,       119890444

User I/O                      ,       5086,             5884585,          4479,          2866115,           607,         3018470

11 rows selected.

So you can see above the breakup of the wait class information by foreground and background processes.

AWR report sample (Showing the total waits and time waited information in total - not classified by fg or bg process):

Wait Classes by Total Wait Time

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                          Avg             Avg

                                        Total Wait       Wait   % DB   Active

Wait Class                  Waits       Time (sec)       Time   time Sessions

---------------- ---------------- ---------------- ---------- ------ --------

System I/O                 40,147              120     2.99ms   90.7      0.1

Commit                     26,770               85     3.17ms   64.1      0.0

Other                       6,035               76    12.66ms   57.8      0.0

DB CPU                                          42              31.7      0.0

User I/O                    5,086                6     1.16ms    4.4      0.0

Network                   120,130                1     9.16us     .8      0.0

Configuration                  13                1    70.96ms     .7      0.0

Concurrency                     2                0    13.00ms     .0      0.0

Application                    17                0     1.45ms     .0      0.0

Administrative                 32                0     5.91us     .0      0.0

AWR report sample (Showing the total waits and time waited information in total - only for fg):

Foreground Wait Class                 DB/Inst: ORA19C01/ORA19C01  Snaps: 17-18

-> s  - second, ms - millisecond, us - microsecond, ns - nanosecond

-> ordered by wait time desc, waits desc

-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0

-> Captured Time accounts for        100.0%  of Total DB time         132.30 (s)

-> Total FG Wait Time:                90.34 (s)  DB CPU time:          42.00 (s)

                                     %Time      Total Wait

Wait Class                     Waits -outs        Time (s)   Avg wait  %DB time

-------------------- --------------- ----- --------------- ---------- ---------

Commit                        26,759     0              85     3.16ms      64.0

DB CPU                                                  42                 31.7

User I/O                       4,479     0               3   639.90us       2.2

Network                      120,024     0               1     9.16us       0.8

Configuration                     13     0               1    70.96ms       0.7

Other                          4,793     0               1   145.36us       0.5

System I/O                     1,009     0               0    92.21us       0.1

Application                       17     0               0     1.45ms       0.0

Administrative                    32     0               0     5.91us       0.0

Concurrency                        0                     0                  0.0

                          ------------------------------------------------------

If you read the line "-> Captured Time accounts for        100.0%  of Total DB time         132.30 (s)". Oracle AWR doesnt account for bg process times in the db time metrics, the same applies for DB CPU metric as well. Background process spends are measured separately.

Then why would some one be interested in the background process wait_class metric information.

In my DB during a hammerDB load test, the system was chocked with system I/O class of wait events aka "log file parallel write & Redo Transport Attach", since my machine had archivelog & standby enabled. Also @ the os end I saw a lot of system and IO waits, I didnt store vmstat result, but you can see below the sar report (averaged out!!!) over 10 mins time showing a significant increase in system cpu usage.

                                                Total

                                       %Time     Wait              Waits   % bg

Event                            Waits -outs Time (s)  Avg wait     /txn   time

-------------------------- ----------- ----- -------- --------- -------- ------

log file parallel write         28,790     0       90    3.13ms      1.1   39.1

Redo Transport Attach                6     0       72   12.03 s      0.0   31.3

02:20:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle

02:30:01 AM     all      0.27      0.00      4.53      0.17      0.00     95.04  <<< this is the window the test ran

02:40:01 AM     all      0.04      0.00      0.66      0.13      0.00     99.17

Hope this helps!

Thank you!

User managed backup and redo size growth

 Dear Readers,

As per oracle document, when you perform user managed backup using 
BEGIN BACKUP at database or tablespace level, your expected to generate more redo (since we will include the image of the full block undergoing change, since the user managed backup tool doesnt understand the dbfile architecture - might create fractured block in backup) compared to using RMAN to perform the same backup.

So I wanted to test that...

Test platform details:

Oracle Version: 19.3.0
DB Type: Standalone x86_64
Os: Linux

In session 1:

  1  create table t1 as
  2  select level lvl,mod(level,3) mdlvl3,mod(level,4) mdlvl4
  3  from dual
  4* connect by level<10000
SQL> /
Table created.

In session 2:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

In session 1 back:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=20;

9999 rows updated.

Statistics

----------------------------------------------------------

         95  recursive calls

        156  db block gets

        141  consistent gets

         38  physical reads

     949056  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

So we see it is only 949K or so redo generated.

In session 2 again:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

SQL> alter database begin backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

ACTIVE

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=30;

9999 rows updated.

Statistics

----------------------------------------------------------

         55  recursive calls

        158  db block gets

        139  consistent gets

         37  physical reads

    1134532  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

This time 1.1M redo bytes generated, this ideally should be the full image of the block which are undergoing change.

In session 2 again:

SQL> alter database end backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=40;

9999 rows updated.

Statistics

----------------------------------------------------------

         89  recursive calls

        158  db block gets

        141  consistent gets

         37  physical reads

     949164  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

SQL>

In session 2 again:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter database begin backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

ACTIVE

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=30;

9999 rows updated.

Statistics

----------------------------------------------------------

         55  recursive calls

        156  db block gets

        139  consistent gets

         37  physical reads

    1134400  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

SQL>

So we repeated the same update 4 times, 2 times in "BACKUP" mode and other 2 times normally.

In all the scenario when we had "BACKUP" mode active, the amount of redo size is 1.1M vs "NO ACTIVE BACKUP" mode, the size is 949K.

So this confirms oracle's document and expected behaviour.

Thanks