Oracle Schema schema: standalone I / O overhead schema?

Question

We are developing a database schema for a new system based on Oracle 11gR1. We have defined the main scheme, which will contain about 100 tables, it will be accessed from the Java application on the front panel.

We have a requirement for checking values ​​that have been changed in 50 tables, this needs to be done every row.

This means that for one row in MYSYS.T1 can be 50 (or more or even less, but at least 1) rows in the MYSYS_AUDIT.T1_AUD table. We could have the old values ​​of each column entry and the new values ​​available from T1 .

The DBA made a comment advising against this method because it said that a separate circuit means additional I / O for each operation. In principle, the AUDIT schema will be used only for analysis and input of values ​​(thus SELECT and INSERT ).

Is it true that “a separate circuit means additional I / O”? I could not find excuses.

It seems logical to me, since the AUDIT data should not be faked, so a separate scheme.

In addition, we developed a separate scheme for archiving some tables from MYSYS . From MYSYS_ARC table can be copied to cassettes or deleted after enough time.

Some statistics:
Several tables (about 20, 30) in the MYSYS can grow to about 50 million rows.
We asked for a total disk space of 4 TB.
MYSYS_AUDIT scheme may have 10 times more than the MYSYS , but we will not store them for more than 3 months.
Private tables in MYSYS will have the following transactions / minutes.

• 100 INSERT in MYSYS means the same number of inserts in the MYSYS_AUDIT tables.
• 1000 UPDATE in the MYSYS tables, which means the same number of inserts in the MYSYS_ADIT tables.

Questions:
Given all this, can you suggest me any improvements?

• Does a separate circuit affect drive on / off? (one additional input-output for each circuit?)
• Any general suggestions?

Picture:

 +-------------------+ +-------------------+ | MYSYS | | MYSYS_AUDIT | | | | | | 1. T1 | | 1. T1_AUD | | 2. T2 | | 2. T2_AUD | | 3. T3 |--------->| 3. T3_AUD | | 4. T4 |(SELECT, | 4. T4_AUD | | . | INSERT) | . | | . | | . | | . | | . | | 100. T100 | | 50. T50_AUD | +-------------------+ +-------------------+ | | | | |(INSERT) | | | * +-------------------+ | MYSYS_ARC | | | | 1. T1_ARC | | 2. T2_ARC | | 3. T3_ARC | | 4. T4_ARC | | . | | . | | . | | 100. T100_ARC | +-------------------+ 

In addition, we have two more schemes with read-only permissions, but they are mainly intended for adhoc, and we are not against performance on them.

Suggestions:
There are several suggestions. We agree on the following.

• Scheme for logical partitions.
• TRIGGER to insert data into AUDIT tables.
• There will be no _AUD suffix in _AUD . :)
• The order of filling the tables of the ARCHIVE schema.
• Sections based on intervals.

We are analyzing ...

• Workspace manager.

The question remains open for further suggestions before deciding whether APC or dpbradely.

Answer 1

Having a separate scheme is definitely the way to go. Among other things, this means that you can use the same table names - MYSYS.T1 and MYSYS_AUDIT.T1 - this is useful if you have tables with long names (> 25 characters).

But the main advantage of a separate scheme is that audit tables can be protected from accidental or mischievous interference.

The impact of entering audit lines, e, g, from a trigger, remains independent of who owns the tables.

general table design advice

It is a good idea to provide audit tables with the same structure as the main table. Therefore, if you have the metadata columns that you need for auditing, such as the version number, include them in the main table. In addition, populate the audit tables with new values, not old ones. That is, when a new record is inserted into MYSYS.T1 , insert the corresponding record in MYSYS_AUDIT.T1 ; when an existing record is updated in MYSYS.T1 , insert the new record in MYSYS_AUDIT.T1 . It is much easier to check and report in the audit tables if their last entries are the same as the current ones in the main tables.

use of triggers

An audit does not have to be complicated. All we need is the insert statement in before insert or update or delete trigger . They can easily be generated from the USER_TAB_COLUMNS data dictionary window.

Answer 2

The presence of objects in a separate schema will not cause additional I / O operations - perhaps there was a misunderstanding and that your database administrator wanted to say that the presence of an audit will lead to additional I / O, which, of course, will be true no matter how you implement it .

Answer 3

Look at the breakdown of ranges into audit data. You might want to send audit data to a cheaper repository. Separation makes it easy to send, for example, a month of data, to another server. Or delete accounts older than a month.

http://www.orafaq.com/wiki/Interval_partitioning

Answer 4

In addition to the APC answer.

You can take a look at Workspace Manager , it may be better than triggers, because they can fail or be disabled, etc.

Answer 5

Also, if you have money, take a look at Oracle Total Recall.