Pickleball spielen

Implementing High Availabilty for an Oracle Database may impact the performance of the protected Database, depending on the method used. If Data Guard is used, the two protection levels Maximum Availability and Maximum Protection may impact performance of the Primary, as discussed for example in SMITH, MICHAEL T. (2007): Data Guard Redo Transport and Network Best Practices: Oracle Database 10g Release 2, available on OTN.

A special case is the use of a Logical Standby Database, because there is an additional possible Performance Impact, regardless of the protection level: Because the actualization of the Logical Standby is done with SQL Apply, more Redo is generated on the Primary to enable the retrievement of the changed rows on the Standby. With Physical Standby, that is not necessary, because the ROWID recorded on the Primary is the same on the Standby. But on Logical Standby Databases, rows can reside in completely different blocks, so the ROWID from the Primary is meaningless.

Therefore, with Logical Standby present, at least additionally the Primary Key resp. a Unique Column of rows, modified on the Primary Database is recorded. If there is no Primary Key on the table where rows where modified, we record all columns additionally. So depending on the number of columns and the absence of Primary Keys resp. Unique Columns on the production system, a Logical Standby may significantly impact the performance of the Primary Database. Fortunately, we can test that easily before we actually implement a Logical Standby – this posting is designed to show how to do that.

First, we prepare a table without Primary/Unique Columns and relatively many columns:

SQL> drop user adam cascade;
grant dba to adam identified by adam;
connect adam/adam@prima

create table sales as select
rownum as id,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id,
5000 as amount_sold,
sysdate as time_id,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col1,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col2,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col3,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col4,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col5,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col6,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col7,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col8,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col9,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col10,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col11,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col12,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col13,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col14,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col15,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col16,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col17,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col18,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col19,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as col20
from dual connect by level<=2e5;

That created a table with 200.000 rows and 176 MB in size. We will now record statistics under normal circumstances when no Logical Standby is present.

SQL> exec dbms_workload_repository.create_snapshot

update sales set amount_sold=amount_sold-1;

commit;

exec dbms_workload_repository.create_snapshot

@?/rdbms/admin/awrrpt

The update above took about 27 seconds to complete. We look now at two sections of the AWR-Report:

Elapsed Time was 0.86 minutes, DB Time 0.54 minutes, LGWR wrote 113 MB during that period. There is nothing special with these numbers, they only become meaningful by comparison. Now we setup supplemental log data as if there would be a Logical Standby Database present that would need it. If you are on 11g, the creation of a Logical Standby Database automatically turns on Supplemental Logging (You can spot this by looking at the alert logfile of the Primary during creation of the Logical Standby or by looking at v$database).

SQL> alter database add supplemental log data 
(primary key, unique index) columns;

exec dbms_workload_repository.create_snapshot

update sales set amount_sold=amount_sold-1;

commit;

exec dbms_workload_repository.create_snapshot

@?/rdbms/admin/awrrpt

alter database drop supplemental log data 
(primary key, unique index) columns;

The very same update as before now took about 45 seconds. If we look at the same AWR-sections as before, we can see why:

Elapsed Time increased to 1.18 minutes, DB Time increased to 0.78 minutes, LGWR had to write 249 MB now – more than doubled as before! Of course, the impact is artificially high in this example, but you can see how easy this can be tested before a Logical Standby was actually created. Although this demonstration here was with 11g Release 2, it can be done in the same way with lower versions also.