12c: PERFORMANCE ISSUE WITH IN-DATABASE ARCHIVING

In my earlier article on in-database archiving, I had demonstrated the functionality of this new feature introduced in Oracle 12c. But this feature you could also give problems with performance as pointed out by John Watson (http://www.orafaq.com/node/2824).

Let’s create a table of employees wherein we would like to archive the records of the employees who have retired.

SQL>create table hr.emp (id  number primary key,
name  varchar2(30),
retired  char(1)) row archival;

insert into hr.emp values (1, 'ONE', 'N');
insert into hr.emp values (2, 'TWO', 'N');
insert into hr.emp values (3, 'THREE', 'Y');

SQL> col ora_archive_state for a3
select id, name, retired, ora_archive_state from hr.emp;

ID NAME                           R ORA
---------- ------------------------------ - ---
1 ONE                            N 0
2 TWO                            N 0
3 THREE                          Y 0

– Archive the records of retired employees

SQL> update hr.emp set ora_archive_state=1 where id = 3;

– Check that archived records are not visible now

SQL>  select id, name, retired, ora_archive_state from hr.emp;

ID NAME                           R ORA
---------- ------------------------------ - ---
1 ONE                            N 0
2 TWO                            N 0

– Let’s check that primary key constraint on the table won’t allow me to add an employee with same id as a retired employee

SQL> insert into hr.emp values (3, 'ANOTHER THREE', 'N');

insert into hr.emp values (3, 'ANOTHER THREE', 'N')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C009857) violated

- Let’s create another table which is copy of hr.emp but without row archival clause

SQL> create table hr.emp_copy as select * from hr.emp;
alter table hr.emp_copy add primary key(id);

– Verify that emp_copy does not contain column ora_archive_state

SQL> select id, name, retired, ora_archive_state from hr.emp_copy;
select id, name, retired, ora_archive_state from hr.emp_copy
*
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

– Let’s issue the same query against the two tables and check the execution plan
— In case of table without row archival, the query is executed by accessing the index only

SQL> set autot trace exp
     select count(*) from hr.emp_copy where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2028575012

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     3 |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C009854 |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

– In case of table with row archival, Table Access by RoWID is used in addition to Index Unique Scan , the reason being the filter
“ORA_ARCHIVE_STATE”=’0′ used while executing the query

SQL> select count(*) from hr.emp where id=1
  2  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1746399475

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |  2015 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |  2015 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     1 |  2015 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C009859 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP"."ORA_ARCHIVE_STATE"='0')
   3 - access("ID"=1)

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

– We can try to resolve this problem by appending column ORA_ARCHIVE_STATE to the primary key 

SQL> alter table hr.emp drop primary key;

alter table hr.emp add primary key(id, ora_archive_state);

– Let’s check that now the query is executed using index access only

SQL> select count(*) from hr.emp ;

Execution Plan
----------------------------------------------------------
Plan hash value: 907615972

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |  2002 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |             |     1 |  2002 |            |          |
|*  2 |   INDEX FULL SCAN| SYS_C009860 |     2 |  4004 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."ORA_ARCHIVE_STATE"='0')
       filter("EMP"."ORA_ARCHIVE_STATE"='0')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

— But this could give rise to another problem related to constraint violation
– Let’s try to add an employee with id same as retired employee i.e. id = 3
— Oops.. It lets me do it!

SQL> insert into hr.emp values (3, 'ANOTHER THREE', 'N');

1 row created.

— Simply because primary key is composed of id and ora_archive_state and
ora_archive_state column is set to 0 (active/unarchived) by default

SQL> alter session set row archival visibility = all;

select id, ora_archive_state, name, retired from hr.emp;

ID ORA NAME                           R
---------- --- ------------------------------ -
1 0   ONE                            N
2 0   TWO                            N
3 1   THREE                          Y
3 0   ANOTHER THREE                  N

I still have to find the solution to this problem.

Thanks for your time!

References:

http://www.orafaq.com/node/2824

——————————————————————–

Related Links:

Home

Database 12c Index

12c: In-Database Archiving
12c: Improve Backup Performance Using In-database Archiving
12c: Solution To Performance Issue With In-database Archiving

 

 

—————————————————————————————-

2 thoughts on “12c: PERFORMANCE ISSUE WITH IN-DATABASE ARCHIVING

  1. Correct, you should not add the column to the constraint.You can add the column to the index used to enforce the constraint.

  2. Did you find the solution. As per Mark’s suggestion, appending the orac_archive_state column to the index can be a very expensive process as the index needs to be dropped and recreated.

Your comments and suggestions are welcome!