Questo articolo è un copia e incolla dell’articolo ufficiale che trovate qui:

http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html

A Fresh Look at Auditing Row Changes

By Connor McDonald

Triggers can provide auditing information, but there’s a future in flashback.

Barbara is looking forward to a long-overdue vacation. She’s been working extremely hard to get a suite of new features for the company’s human resources (HR) application through construction, testing, and finally deployment into the production environment. Satisfaction ratings from the HR department are high, and no serious bugs have been reported.

But Barbara’s vacation plans are about to be stymied. As often happens in IT projects, some requirements get pushed to the side during the construction phase, with the intent of tackling them later. Now, an email titled “Urgent: Need to capture all row changes for audit compliance” arrives in Barbara’s inbox. Audit functions, although important from a security and compliance perspective, are typically invisible to application stakeholders and are hardly the exciting part of any functionality showcase. So the auditing requirements for Barbara’s application enhancements were pushed aside early in the construction phase, only to resurface as an emergency postimplementation requirement.

Fortunately for Barbara, auditing row changes is a common need throughout the IT industry. A quick web search reveals the most prevalent solution: for each table in her application, there should be a partnering audit table to hold the history of row changes, plus some additional metadata.

For example, for the EMPLOYEES table in Barbara’s application:

SQL> desc EMPLOYEES

Name                          Null?    Type
————————————————————————————— ———————— ————————————
EMPLOYEE_ID                            NUMBER(6)
FIRST_NAME                             VARCHAR2(20)
LAST_NAME                     NOT NULL VARCHAR2(25)
EMAIL                         NOT NULL VARCHAR2(25)
PHONE_NUMBER                           VARCHAR2(20)
HIRE_DATE                     NOT NULL DATE
JOB_ID                        NOT NULL VARCHAR2(10)
SALARY                                 NUMBER(8,2)
MANAGER_ID                             NUMBER(6)
DEPARTMENT_ID                          NUMBER(4)
VACATION_BALANCE                       NUMBER(6,2)

there will be an appropriately named equivalent table for capturing any row changes:

SQL> desc AUDIT_EMPLOYEES

Name                          Null?    Type
————————————————————————————— ———————— ————————————
AUD_WHO                                VARCHAR2(20)
AUD_WHEN                               DATE
AUD_OPERATION                          VARCHAR2(1)
AUD_MODULE                             VARCHAR2(30)
EMPLOYEE_ID                            NUMBER(6)
FIRST_NAME                             VARCHAR2(20)
LAST_NAME                     NOT NULL VARCHAR2(25)
EMAIL                         NOT NULL VARCHAR2(25)
PHONE_NUMBER                           VARCHAR2(20)
HIRE_DATE                     NOT NULL DATE
JOB_ID                        NOT NULL VARCHAR2(10)
SALARY                                 NUMBER(8,2)
MANAGER_ID                             NUMBER(6)
DEPARTMENT_ID                          NUMBER(4)
VACATION_BALANCE                       NUMBER(6,2)

Some additional metadata columns are present in the AUDIT_EMPLOYEES table for capturing who performed the change, when the change occurred, what operation was performed (INSERT, UPDATE, or DELETE), and from which program or module the change originated. Barbara’s application is already live in the production environment, so the function for populating the AUDIT_EMPLOYEES table must be transparent to the existing code. Thus, database triggers are the natural choice.

Listing 1 shows the trigger Barbara has built to capture all changes to the EMPLOYEES table.

Code Listing 1: Audit data capture trigger for EMPLOYEES

SQL> create or replace
  2  trigger TRG_AUDIT_EMPLOYEES
  3  after insert or update or delete on EMPLOYEES
  4  for each row
  5  declare
  6    l_operation varchar2(1) :=
  7        case when updating then 'U'
  8             when deleting then 'D'
  9             else 'I' end;
 10  begin
 11    if updating or inserting then
 12        insert into AUDIT_EMPLOYEES
 13          (aud_who
 14          ,aud_when
 15          ,aud_operation
 16          ,aud_module
 17          ,employee_id
 18          ,first_name
 19          ,last_name
 20          ,email
 21          ,phone_number
 22          ,hire_date
 23          ,job_id
 24          ,salary
 25          ,commission_pct
 26          ,manager_id
 27          ,department_id)
 28        values
 29          (user
 30          ,sysdate
 31          ,l_operation
 32          ,sys_context('USERENV','MODULE')
 33          ,:new.employee_id
 34          ,:new.first_name
 35          ,:new.last_name
 36          ,:new.email
 37          ,:new.phone_number
 38          ,:new.hire_date
 39          ,:new.job_id
 40          ,:new.salary
 41          ,:new.commission_pct
 42          ,:new.manager_id
 43          ,:new.department_id);
 44    else
 45        insert into AUDIT_EMPLOYEES
 46          (aud_who
 47          ,aud_when
 48          ,aud_operation
 49          ,aud_module
 50          ,employee_id
 51          ,first_name
 52          ,last_name
 53          ,email
 54          ,phone_number
 55          ,hire_date
 56          ,job_id
 57          ,salary
 58          ,commission_pct
 59          ,manager_id
 60          ,department_id)
 61        values
 62          (user
 63          ,sysdate
 64          ,l_operation
 65          ,sys_context('USERENV','MODULE')
 66          ,:old.employee_id
 67          ,:old.first_name
 68          ,:old.last_name
 69          ,:old.email
 70          ,:old.phone_number
 71          ,:old.hire_date
 72          ,:old.job_id
 73          ,:old.salary
 74          ,:old.commission_pct
 75          ,:old.manager_id
 76          ,:old.department_id);
 77    end if;
 78  end;
 79  /

Trigger created.

A quick series of tests confirms the correct operation of the trigger, so Barbara forges ahead and creates auditing tables and associated triggers for all the tables in the HR application.

Hidden Costs

Barbara’s vacation plans remind her that one of the tasks performed each night on the EMPLOYEES table is an update of the vacation balance for all employees in the organization. Before her auditing changes went live in the production environment, this task was efficient—it had to be, because it temporarily locks all rows in the EMPLOYEES table.

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;

219136 rows updated.

Elapsed: 00:00:03.01

A few seconds is an acceptable lock time. In the rare instance that anyone updates a single employee record outside of business hours, that person would experience only a slight pause—nothing that would be cause for alarm. But now that Barbara’s auditing changes have been implemented, a problem has surfaced. The vacation adjustment takes significantly longer:

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;

219136 rows updated.

Elapsed: 00:00:17.92

 

Barbara performs a trace on the vacation update to gauge the impact of auditing. Listing 2 shows the TKprof-formatted output of the trace file.

Code Listing 2: TKprof output from trace of vacation adjustment

update EMPLOYEES
set    VACATION_BALANCE = VACATION_BALANCE + 1

call     count       cpu   elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.00       0.00          0          1          0          0
Execute      1    15.61      17.47          0       2380     223618     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total        2    15.61      17.48          0       2381     223618     219136


INSERT INTO AUDIT_EMPLOYEES (AUD_WHO ,AUD_WHEN ,AUD_OPERATION ,AUD_MODULE ,
  EMPLOYEE_ID ,FIRST_NAME ,LAST_NAME ,EMAIL ,PHONE_NUMBER ,HIRE_DATE ,JOB_ID ,
  SALARY ,MANAGER_ID ,DEPARTMENT_ID ,VACATION_BALANCE) 
VALUES
 (USER ,SYSDATE ,:B12 ,SYS_CONTEXT('USERENV','MODULE') ,:B1 ,:B2 ,:B3 ,:B4 ,
  :B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 )


call     count      cpu    elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.00       0.00          0          0          0          0
Execute 219136     8.60       8.71          0       5865     245856     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total   219137     8.60       8.71          0       5865     245856     219136

From the trace file, Barbara observes that the INSERT statement for the audit table added 8.71 seconds of elapsed time to the process. It was called 219,136 times—once for every changed row, even though only a single UPDATE statement was issued. This also incurs the same number of context switches between the SQL and PL/SQL engines, further adding to the overall elapsed time. Barbara’s auditing changes are not as transparent to the application as they initially appeared to be. She needs a fix…fast.

To reduce the context switching, Barbara must refactor the audit trigger code to use bulk binding. This is not a trivial undertaking, because row changes need to be buffered as they occur and then applied in one pass after the issuing data manipulation language (DML) is complete. In earlier versions of Oracle Database, this would mandate a PACKAGE definition along with three separate triggers. But as of Oracle Database 11g, a single compound trigger can be used to provide the required functionality.

Listing 3 shows the compound trigger Barbara built to improve the efficiency of her audit capture function.

Code Listing 3: Compound trigger for audit capture

SQL> create or replace
  2  trigger TRG_AUDIT_EMPLOYEES
  3  for insert or update or delete
  4  on EMPLOYEES compound trigger
  5
  6  --
  7  -- an array structure to buffer all the row changes
  8  --
  9    type t_row_list is
 10      table of AUDIT_EMPLOYEES%rowtype index by pls_integer;
 11
 12    l_audit_rows      t_row_list;
 13
 14    l_operation varchar2(1) :=
 15        case when updating then 'U'
 16             when deleting then 'D'
 17             else 'I' end;
 18
 19  before statement is
 20  begin
 21    --
 22    -- initialize the array
 23    --
 24    l_audit_rows.delete;
 25  end before statement;
 26
 27  after each row is
 28  begin
 29    --
 30    -- at row level, capture all the changes into the array
 31    --
 32    l_audit_rows(l_audit_rows.count+1).aud_who     := sys_context('USERENV','SESSION_USER');
 33    l_audit_rows(l_audit_rows.count).aud_when      := sysdate;
 34    l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
 35    l_audit_rows(l_audit_rows.count).aud_module    := sys_context('USERENV','MODULE');
 36
 37    if updating or inserting then
 38        l_audit_rows(l_audit_rows.count).employee_id      := :new.employee_id;
 39        l_audit_rows(l_audit_rows.count).first_name       := :new.first_name;
 40        l_audit_rows(l_audit_rows.count).last_name        := :new.last_name;
 41        l_audit_rows(l_audit_rows.count).email            := :new.email;
 42        l_audit_rows(l_audit_rows.count).phone_number     := :new.phone_number;
 43        l_audit_rows(l_audit_rows.count).hire_date        := :new.hire_date;
 44        l_audit_rows(l_audit_rows.count).job_id           := :new.job_id;
 45        l_audit_rows(l_audit_rows.count).salary           := :new.salary;
 46        l_audit_rows(l_audit_rows.count).manager_id       := :new.manager_id;
 47        l_audit_rows(l_audit_rows.count).department_id    := :new.department_id;
 48        l_audit_rows(l_audit_rows.count).vacation_balance := :new.vacation_balance;
 49    else
 50        l_audit_rows(l_audit_rows.count).employee_id      := :old.employee_id;
 51        l_audit_rows(l_audit_rows.count).first_name       := :old.first_name;
 52        l_audit_rows(l_audit_rows.count).last_name        := :old.last_name;
 53        l_audit_rows(l_audit_rows.count).email            := :old.email;
 54        l_audit_rows(l_audit_rows.count).phone_number     := :old.phone_number;
 55        l_audit_rows(l_audit_rows.count).hire_date        := :old.hire_date;
 56        l_audit_rows(l_audit_rows.count).job_id           := :old.job_id;
 57        l_audit_rows(l_audit_rows.count).salary           := :old.salary;
 58        l_audit_rows(l_audit_rows.count).manager_id       := :old.manager_id;
 59        l_audit_rows(l_audit_rows.count).department_id    := :old.department_id;
 60        l_audit_rows(l_audit_rows.count).vacation_balance := :old.vacation_balance;
 61    end if;
 62  end after each row;
 63
 64  after statement is
 65  begin
 66    --
 67    -- then at completion, do a single insert of all the rows into our audit table
 68    --
 69
 70    forall i in 1 .. l_audit_rows.count
 71       insert into AUDIT_EMPLOYEES
 72       values l_audit_rows(i);
 73    l_audit_rows.delete;
 74  end after statement;
 75
 76  end;
 77  /

Trigger created.

Barbara re-examines the performance of the vacation adjustment with her new trigger in place.

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;

219136 rows updated.

Elapsed: 00:00:04.01

Her audit capture now imposes dramatically reduced overhead. A trace of the process confirms the benefit of bulk binding in this instance. Listing 4 shows that the number of calls to INSERT into the AUDIT_EMPLOYEES table has shrunk from more than 200,000 down to the optimal value, 1.

Code Listing 4: TKprof output from trace of vacation adjustment, with new compound trigger

update EMPLOYEES
set    VACATION_BALANCE = VACATION_BALANCE + 1

call     count      cpu    elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.01       0.00          0          1          0          0
Execute      1     3.41       3.47          0       2365     224004     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total        2     3.43       3.47          0       2366     224004     219136


INSERT INTO AUDIT_EMPLOYEES 
VALUES
 (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
  :B15 ) 


call     count      cpu    elapsed       disk      query    current       rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.43       0.51          0       3306      21864     219136
Fetch        0     0.00       0.00          0          0          0          0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total        2     0.43       0.51          0       3306      21864     219136

Barbara’s auditing enhancements to the HR application are back on track, as are her vacation plans. Still, Barbara has a nagging concern about the amount of potential code maintenance overhead she has introduced. In the future, every time a structural change is made to the HR application, the auditing triggers will need to be refactored to accommodate that change. And Barbara’s fix does not address another potential pitfall with the trigger-based solution: although bulk binding improves performance and resolves the context-switching overhead, it also introduces the risk of excessive memory consumption. Because all rows changed by the issuing DML are buffered before being processed at statement completion, a significant amount of session memory can be consumed. This could strain the database server—or, worse, it could crash the application session with “ORA-4030: Out of process memory” errors.

Listing 5 shows the memory consumption Barbara’s bulk binding incurs when the vacation adjustment runs. The session requires more than 160 MB of program global area (PGA) memory. This may be acceptable for a single session running a nightly process such as the vacation update, but it obviously would not scale to a scenario where hundreds or thousands of sessions have a similar requirement.

Code Listing 5: Session memory consumption for the vacation adjustment with bulk binding

--
-- First we check the current ceiling of PGA memory before 
-- the vacation adjustment
--
SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';

       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                                2508344

1 row selected.

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;

219136 rows updated.

--
-- Then we check the same statistics once the adjustment has completed
--

SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';

       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                              164430744

To obviate that risk, the trigger can be adjusted to perform bulk binding in batches throughout the DML processing, as shown in Listing 6. But with both code maintenance overhead and complexity increasing, Barbara is keen to find a simpler solution.

Code Listing 6: Trigger to keep PGA consumption in check, still using bulk binding

SQL> create or replace
  2  trigger TRG_AUDIT_EMPLOYEES
  3  for insert or update or delete
  4  on EMPLOYEES compound trigger
  5
  6  --
  7  -- an array structure to buffer all the row changes
  8  --
  9    type t_row_list is
 10      table of AUDIT_EMPLOYEES%rowtype index by pls_integer;
 11
 12    l_audit_rows      t_row_list;
 13
 14    l_operation varchar2(1) :=
 15        case when updating then 'U'
 16             when deleting then 'D'
 17             else 'I' end;
 18
 19    procedure insert_logged_so_far is
 20    begin
 21      forall i in 1 .. l_audit_rows.count
 22         insert into AUDIT_EMPLOYEES
 23         values l_audit_rows(i);
 24      l_audit_rows.delete;
 25    end;
 26
 27  before statement is
 28  begin
 29    --
 30    -- initialize the array
 31    --
 32    l_audit_rows.delete;
 33  end before statement;
 34
 35  after each row is
 36  begin
 37    --
 38    -- at row level, capture all the changes into the array
 39    --
 40    l_audit_rows(l_audit_rows.count+1).aud_who     := sys_context('USERENV','SESSION_USER');
 41    l_audit_rows(l_audit_rows.count).aud_when      := sysdate;
 42    l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
 43    l_audit_rows(l_audit_rows.count).aud_module    := sys_context('USERENV','MODULE');
 44
 45    if updating or inserting then
 46        l_audit_rows(l_audit_rows.count).employee_id      := :new.employee_id;
 47        l_audit_rows(l_audit_rows.count).first_name       := :new.first_name;
 48        l_audit_rows(l_audit_rows.count).last_name        := :new.last_name;
 49        l_audit_rows(l_audit_rows.count).email            := :new.email;
 50        l_audit_rows(l_audit_rows.count).phone_number     := :new.phone_number;
 51        l_audit_rows(l_audit_rows.count).hire_date        := :new.hire_date;
 52        l_audit_rows(l_audit_rows.count).job_id           := :new.job_id;
 53        l_audit_rows(l_audit_rows.count).salary           := :new.salary;
 54        l_audit_rows(l_audit_rows.count).manager_id       := :new.manager_id;
 55        l_audit_rows(l_audit_rows.count).department_id    := :new.department_id;
 56        l_audit_rows(l_audit_rows.count).vacation_balance := :new.vacation_balance;
 57    else
 58        l_audit_rows(l_audit_rows.count).employee_id      := :old.employee_id;
 59        l_audit_rows(l_audit_rows.count).first_name       := :old.first_name;
 60        l_audit_rows(l_audit_rows.count).last_name        := :old.last_name;
 61        l_audit_rows(l_audit_rows.count).email            := :old.email;
 62        l_audit_rows(l_audit_rows.count).phone_number     := :old.phone_number;
 63        l_audit_rows(l_audit_rows.count).hire_date        := :old.hire_date;
 64        l_audit_rows(l_audit_rows.count).job_id           := :old.job_id;
 65        l_audit_rows(l_audit_rows.count).salary           := :old.salary;
 66        l_audit_rows(l_audit_rows.count).manager_id       := :old.manager_id;
 67        l_audit_rows(l_audit_rows.count).department_id    := :old.department_id;
 68        l_audit_rows(l_audit_rows.count).vacation_balance := :old.vacation_balance;
 69    end if;
 70
 71    --
 72    -- bulk bind every 1000 rows to keep the memory down
 73    --
 74    if l_audit_rows.count > 1000 then
 75      insert_logged_so_far;
 76    end if;
 77  end after each row;
 78
 79  after statement is
 80  begin
 81    --
 82    -- then at completion, pick up the remaining rows
 83    --
 84    if l_audit_rows.count > 0 then
 85      insert_logged_so_far;
 86    end if;
 87  end after statement;
 88
 89  end;
 90  /

Trigger created.

SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';

       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                                2508344

1 row selected.

SQL> update EMPLOYEES
  2  set    VACATION_BALANCE = VACATION_BALANCE + 1;

219136 rows updated.

Elapsed: 00:00:05.31

SQL> select st.sid, s.name, st.value
  2  from v$statname s, v$sesstat st
  3  where st.statistic# = s.statistic#
  4  and st.sid = sys_context('USERENV','SID')
  5  and s.name = 'session pga memory max';

       SID NAME                                                    VALUE
—————————— ————————————————————————————————————————————————————— ———————
         6 session pga memory max                                2508344

1 row selected.

 

A Better Way

The use of triggers for capturing row changes is common throughout the Oracle application development community. But few developers display Barbara’s diligence in ensuring that bulk binding caters to multirow DML statements, and even fewer explore potential alternatives.

Barbara peruses the Oracle Database Advanced Application Developer’s Guide and comes across Oracle Flashback technology. The Flashback Query feature catches her eye: “Oracle Flashback Query retrieves data as it existed at some time in the past.” Since any time could be nominated, Barbara infers that it should be doable to see every possible state of a database table, which sounds very much like the auditing requirement she has just implemented. In the same section of the documentation, she also discovers Oracle Flashback’s Oracle Flashback Data Archive feature: “Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.” This sounds like a perfect match for Barbara’s audit needs, so she builds a Flashback Data Archive test case to examine its features.

First she allocates some storage to hold her archive data:

SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB12\ARCH_SPACE.DBF' 
  3  size 50G;

Tablespace created.

Next, she creates a new structure, called a flashback archive, which defines the retention duration of the row change history for any table placed into it.

SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;

Flashback archive created

And then she simply associates her HR tables with the flashback archive.

SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;

Table altered.

“So far, so good,” thinks Barbara. However, it is not immediately apparent where the audited row changes are located or how she is meant to access them. Listing 7 shows that some new objects have been created within Barbara’s schema. Their structure suggests they are related to Flashback Data Archive in some way, but she finds it unlikely that she’s supposed to query those tables directly.

Code Listing 7: Tables created to support Flashback Data Archive

SQL> select * from tab;

TNAME                                    TABTYPE CLUSTERID
———————————————————————————————————————— ——————— —————————
EMPLOYEES                                TABLE
SYS_FBA_DDL_COLMAP_102596                TABLE
SYS_FBA_HIST_102596                      TABLE
SYS_FBA_TCRV_102596                      TABLE

SQL> desc SYS_FBA_DDL_COLMAP_102596
 Name                          Null?    Type
 ————————————————————————————— ———————— ——————————————————
 STARTSCN                               NUMBER
 ENDSCN                                 NUMBER
 XID                                    RAW(8)
 OPERATION                              VARCHAR2(1)
 COLUMN_NAME                            VARCHAR2(255)
 TYPE                                   VARCHAR2(255)
 HISTORICAL_COLUMN_NAME                 VARCHAR2(255)

SQL> desc SYS_FBA_HIST_102596
 Name                          Null?    Type
 ————————————————————————————— ———————— ——————————————————
 RID                                    VARCHAR2(4000)
 STARTSCN                               NUMBER
 ENDSCN                                 NUMBER
 XID                                    RAW(8)
 OPERATION                              VARCHAR2(1)
 EMPLOYEE_ID                            NUMBER(6)
 FIRST_NAME                             VARCHAR2(20)
 LAST_NAME                              VARCHAR2(25)
 EMAIL                                  VARCHAR2(25)
 PHONE_NUMBER                           VARCHAR2(20)
 HIRE_DATE                              DATE
 JOB_ID                                 VARCHAR2(10)
 SALARY                                 NUMBER(8,2)
 MANAGER_ID                             NUMBER(6)
 DEPARTMENT_ID                          NUMBER(4)
 VACATION_BALANCE                       NUMBER(6,2)

SQL> desc SYS_FBA_TCRV_102596
 Name                          Null?    Type
 ————————————————————————————— ———————— ——————————————————
 RID                                    VARCHAR2(4000)
 STARTSCN                               NUMBER
 ENDSCN                                 NUMBER
 XID                                    RAW(8)
 OP                                     VARCHAR2(1)

To determine whether the flashback archive is working as expected, Barbara tries a test query examining the EMPLOYEES table at a past point in time. The execution plan in Listing 8 confirms her hypothesis that the new tables that were created are related to Flashback Data Archive and are accessing historical data.

Code Listing 8: Execution plan for tables associated with Flashback Data Archive

SQL> set autotrace traceonly explain
SQL> select * from EMPLOYEES AS OF TIMESTAMP SYSDATE-3;

Execution Plan
—————————————————————————————————————————————————————————————————
| Id  | Operation                 | Name                | Rows  |
—————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT          |                     |  9399 |
|   1 |  VIEW                     |                     |  9399 |
|   2 |   UNION-ALL               |                     |       |
|*  3 |    FILTER                 |                     |       |
|   4 |     PARTITION RANGE SINGLE|                     |  1054 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_102596 |  1054 |
|*  6 |    FILTER                 |                     |       |
|   7 |     MERGE JOIN OUTER      |                     |  8345 |
|   8 |      SORT JOIN            |                     |   203 |
|*  9 |       TABLE ACCESS FULL   | EMPLOYEES           |   203 |
|* 10 |      SORT JOIN            |                     |   230K|
|* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_102596 |   230K|
—————————————————————————————————————————————————————————————————

Seeing a point-in-time state of the EMPLOYEES table is useful, but Barbara needs more than that. She needs to be able to see every point-in-time state of a row in the table to build up a history of changes and satisfy her audit requirements. With each update to the table, a new “version” of a row comes into existence, and Barbara needs to list every one of those versions. Having reviewed Oracle Flashback technology features in the documentation, Barbara knows that she can use the Flashback Version Query feature of Oracle Flashback to achieve this. Listing 9 shows how the VERSIONS BETWEEN syntax lists all the historical versions of a single employee record (EMPLOYEE_ID = 100) and how the VERSIONS_STARTTIME and VERSIONS_OPERATION pseudocolumns can be used to track the transaction time and operation performed on each row.

Code Listing 9: Flashback Version Query

SQL> select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         nvl(VERSIONS_OPERATION,'I') OP
  4  from EMPLOYEES
  5  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  6  where EMPLOYEE_ID = 100
  7  order by EMPLOYEE_ID, ts;

EMPLOYEE_ID FIRST_NAME         JOB_ID     VACATION_BALANCE TS                                  O
——————————— —————————————————— —————————— ———————————————— ——————————————————————————————————— —
        100 Steven             AD_PRES               45.76 11-JAN-16 08.27.55.000000000 AM     U
        100 Steven             AD_PRES               46.76 12-JAN-16 08.28.07.000000000 AM     U
        100 Steven             AD_PRES               47.76 13-JAN-16 08.53.56.000000000 AM     U
        100 Steven             AD_PRES               48.76 14-JAN-16 08.53.56.000000000 AM     U
        100 Steven             AD_PRES               49.76 15-JAN-16 08.53.56.000000000 AM     U

Barbara is excited about her progress. She can now list a history of row changes for the EMPLOYEES table, and no triggers are required. The vacation update routine will run at the same swift speed as it did before her audit changes were implemented.

But there is still an outstanding issue. Barbara’s original trigger-based audit implementation picked up additional metadata—namely, who did the change and what program was being executed. That data is not present in the Flashback Data Archive tables. Barbara doesn’t want to add those metadata columns to each of the base tables in her application, because that would break a primary implementation requirement for auditing—that it should be transparent to the existing application.

Luckily for Barbara, her application is deployed on Oracle Database 12c. Flashback Data Archive has been extended to enable the capture of context-level information automatically and without changes to the base tables. Barbara contacts her administrator and requests context-level tracking to be enabled for Flashback Data Archive:

SQL> exec dbms_flashback_archive.set_context_level('ALL');

PL/SQL procedure successfully completed.

Now, when rows are inserted, updated, or deleted in the tables Barbara has associated with a flashback archive, any context information in the standard USERENV context (as well as any custom user-defined contexts) will be captured for each transaction. From her trigger-based audit implementation, Barbara knows that she needs the MODULE and SESSION_USER attributes from the USERENV context. She performs some sample DMLs against the EMPLOYEES table and then checks to ensure that the context-level metadata is being collected. Listing 10 shows that the capture of context information for each transaction is working and Barbara’s implementation of the audit requirement with Flashback Data Archive is complete.

Code Listing 10: Flashback Version Query with context information

SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
  4         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
  5  from EMPLOYEES
  6  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  7  where EMPLOYEE_ID = 100
  8  order by EMPLOYEE_ID, ts;

EMPLOYEE_ID FIRST_NAME   VACATION_BALANCE TS                     WHO          PROGRAM
——————————— ———————————— ———————————————— —————————————————————— ———————————— ————————
        100 Steven                  52.76 11-JAN-16 10.04.03     BARBARA      SQL*Plus
        100 Steven                  51.76 11-JAN-16 11.14.03     BARBARA      SQL*Plus
        100 Steven                  53.76 11-JAN-16 12.05.06     BARBARA      SQL*Plus
        100 Steven                  54.76 11-JAN-16 13.01.06     BARBARA      SQL*Plus
        100 Steven                  55.76 11-JAN-16 14.23.09     BARBARA      SQL*Plus

Although Barbara initially implemented the auditing requirements with a trigger-based approach, she does not need to abandon the audited row changes she has already collected. Using the DBMS_FLASHBACK_ARCHIVE package, she can import her captured audit changes into the new Flashback Data Archive tables and then drop her old auditing tables and triggers.

Conclusion

The use of triggers for auditing is common and is still often regarded as the only possible solution for capturing row changes on Oracle Database tables. However, in Oracle Database 11.2.0.4 and above, auditing requirements can now be met with Flashback Data Archive—a simple, more efficient, and more secure solution.

Questo articolo è un copia e incolla dell’articolo ufficiale che trovate qui;

http://www.oracle.com/technetwork/issue-archive/2016/16-may/o36dba-mcdonald-3001274.html

Improved Defaults in Oracle Database 12c

By Connor McDonald

The new DEFAULT clause provides better values for getting started and better performance.

Gordon has just completed some self-study Oracle Application Express training and is eager to start building applications to improve his skills and, more importantly, to rapidly deliver value to the business stakeholders in the company where he works.

He knows that Oracle Application Express is a database-centric development tool, so he wants to make sure he takes as much advantage as possible of the features available to him within the database so that the applications he builds will be more robust; will require less coding; and could even be ported to other front-end technologies, should the need arise. The applications will be simply a window into his data, but the data relationships, integrity, and controls will be within the database, alongside the data.

From his Oracle Application Express training, Gordon knows there are advantages to ensuring that all of the database tables are keyed with surrogate keys, either instead of, or in addition to, any natural keys. Using surrogate keys will also reduce the amount of coding required in Oracle Application Express, and Gordon aims to deliver applications rapidly, so he wants to minimize the amount of coding he has to do.

However, his data-centric vision for building applications dictates that even without Oracle Application Express as an interface to the data, the integrity and function of the data in the database should remain intact. So Gordon needs to ensure that his surrogate keys are populated even if Oracle Application Express is not the application interface. And that means he will need a database trigger for every surrogate key he wants to populate. For Gordon, this seems like a contradiction. To achieve less coding in Oracle Application Express, he will have to do more coding in the database. But not wanting to compromise his vision, Gordon builds triggers for each of his new database tables. Here’s one example trigger:

SQL> create or replace
  2  trigger TRG_SALES_SURROGATE_KEY
  3  before insert on SALES
  4  for each row
  5  begin
  6    :new.SALE_SEQ := SEQ_SALES.nextval;
  7  end;
  8  /

Trigger created.

After a few days of development and testing, Gordon’s first Oracle Application Express module, a sales analysis tool, is deployed into production and is a great success with the users at his company. So much so that in the weeks after the deployment, several of the company’s existing applications have been extended to interface with his new database tables. Gordon’s foresight in not assuming that his Oracle Application Express modules would be the only interface to his new tables has proven well justified.

Trigger Overhead

But Gordon’s success has also created some new challenges. The popularity of the application has led to a request to load all the company’s daily sales data into Gordon’s database tables—millions of records per day. Gordon does some preliminary testing in a nonproduction environment to gauge the impact of loading the data:

SQL> insert /*+ APPEND */ into SALES
  2  select rownum, ...
  3  from
  4   ( select 1 from dual connect by level <= 10000 ),
  5   ( select 1 from dual connect by level <= 1000 )
  6  /

10000000 rows created.

Elapsed: 00:06:37.75

Just under seven minutes seems quite reasonable for loading 10 million rows, but Gordon is curious to see if the sequence population trigger is adding to the overhead. So he repeats the loading test with the trigger disabled:

SQL> alter trigger SALES_SURROGATE_KEY disable;

Trigger altered.

SQL> insert /*+ APPEND */ into SALES
  2  select rownum, ...
  3  from
  4   ( select 1 from dual connect by level <= 10000 ),
  5   ( select 1 from dual connect by level <= 1000 )
  6  /

10000000 rows created.

Elapsed: 00:00:04.73

Gordon is astounded by the difference. From nearly seven minutes down to less than five seconds! Without the trigger, Gordon could easily load the historical data with almost no disruption to the running application. But conversely, the trigger cannot be disabled without the threat of a major disruption, namely that a surrogate key value wouldn’t be generated automatically—which could mean that the application would crash or that data would be corrupted. Although Gordon has maintenance control over his Oracle Application Express code, he has no control over, or even awareness of, the other company applications that are now all interfacing with his data. Those applications might need the trigger, they might not—but Gordon cannot assume that either is true. He unfortunately just has to accept the slow performance.

Other problems have also surfaced. One of the applications outside Gordon’s control implements record update logic via a delete-then-insert strategy, and the application is crashing whenever it attempts such an operation. When the application attempts to reinsert the record with the previous surrogate key value, Gordon’s trigger automatically overwrites it with the next sequence value. Here’s an example of the problem observed by the crashing application:

SQL> delete from sales where sale_seq = 456;

1 row deleted.

SQL> insert into sales (sale_seq, invoice_num, .... )
  2  values (456, 720001, ... );

1 row created.

SQL> select * from sales where sale_seq = 456;

no rows selected

When the application attempts to retrieve a row it just inserted, it can no longer find it. Gordon rushes an emergency fix into production to all the surrogate key generation triggers for his database tables, so that they assign a sequence value only when no primary key value is provided on insertion. Here’s an example:

SQL> create or replace
  2  trigger SALES_SURROGATE_KEY
  3  before insert on SALES
  4  for each row
  5  when (new.SALE_SEQ is null)
  6  begin
  7    :new.SALE_SEQ := SEQ_SALES.nextval;
  8  end;
  9  /

Trigger created.

But Gordon is becoming less confident that the triggers will not cause other issues as time goes on. He spends the weekend digging through the Oracle Database documentation, looking for a better solution.

New DEFAULT Clause Options

By Monday he has the solution he wants. In Oracle Database 12c, a suite of improvements has been implemented around the DEFAULT clause for table columns. Of particular interest to Gordon is that a sequence value can now be assigned as the default value for a column. With one simple command, Gordon has removed the need for his triggers:

SQL> alter table SALES modify SALE_SEQ default SEQ_SALES.nextval;

Table altered.

Gordon is also delighted to discover that the DEFAULT clause implementation is not simply equivalent to having a “hidden” trigger somewhere within the Oracle Database kernel, as evidenced by the fact that nightly loads of sales data are no longer slow. They are as now just as fast as his tests were when there was no trigger at all. He now has faster performance—and no sequence assignment triggers to maintain. Armed with his knowledge of the Oracle Database 12c new DEFAULT clause features, he looks for other opportunities to improve the application.

Looking through the SALES table, he discovers an unfortunate oversight. The INVOICE_DATE column, which should have been mandatory, was never constrained as such. Gordon hadn’t noticed the omission, because his Oracle Application Express code correctly provided a value for INVOICE_DATE in all cases. But the presence of some null values indicates that some of the external applications are not providing a value for INVOICE_DATE. Gordon adds a DEFAULT clause, thinking that this will resolve the issue:

SQL> alter table SALES modify INVOICE_DATE default sysdate;

Table altered.

However, a few days later, when Gordon examines the SALES table, he discovers that rows are still being created with a null INVOICE_DATE. He’s puzzled by how this could possibly be the case, given that there is now a default value assigned in the definition of the column. Unable to see the source code for the external applications, he performs some database tracing, using DBMS_MONITOR, to see what could be the cause. When Gordon examines the resulting trace file, the problem becomes apparent. Within the trace file is a typical INSERT statement from one of the external applications:

insert into SALES ( INVOICE_NUM, INVOICE_DATE, QUANTITY, ... )
values ( :invoice_num, null, :quantity, ... );

Even though a DEFAULT clause is present, the default value is not being respected, because the application code is explicitly assigning a null value to the INVOICE_DATE. But Gordon knows that the new DEFAULT features in Oracle Database 12c can also handle this scenario. The DEFAULT ON NULL clause will force a default value into a column even when a null value has been explicitly included in the INSERT statement. Gordon makes a slight adjustment to his column definition and makes an interesting discovery when he tries to apply the change:

SQL> alter table SALES modify INVOICE_DATE default on null sysdate;
ERROR at line 1:
ORA-02296: cannot enable - null values found

Adding a DEFAULT ON NULL clause means that the database will automatically convert the column to being also constrained as NOT NULL. So Gordon first must correct the missing values for INVOICE_DATE. But once he has completed the data correction, the DEFAULT ON NULL clause can be successfully added to permanently stop the problem:

SQL> alter table SALES modify INVOICE_DATE default on null sysdate;

Table altered.

 

Conclusion

Whether you have code in database triggers or in the application layer to handle default values that typically need to be assigned to row values as data is created in the database, take the opportunity to review this code when you upgrade to Oracle Database 12c. There is a good chance you might be able to remove much of it and replace it with the new declarative DEFAULT clause features. Your applications will end up with less code and better performance.