Oracle Schema Design: Seperate Schema with I/O Overhead?
We are designing database schema for a new system based on Oracle 11gR1. We have identified a main schema which would have close to 100 tables, these will be accessed from the front end Java application.
We have a requirement to audit the values which got changed in close to 50 tables, this has to be done every row.
Which means, it is possible that, for a single row in MYSYS.T1
there might be 50 (or more or even less, but minimum 1) rows in MYSYS_AUDIT.T1_AUD
table. We might be having old values of 开发者_Go百科every column entry and new values available from T1
.
DBA gave an observation, advising against this method, because he said, separate schema meant an extra I/O for every operation. Basically AUDIT schema would be used only to do some analyse and enter values (thus SELECT
and INSERT
).
Is it true that, "a separate schema means an extra I/O" ? I could not find justification.
It appears logical to me, as the AUDIT data should not be tampered with, so a separate schema.
Also, we designed a separate schema for archiving some tables from MYSYS
. From MYSYS_ARC
the table might be backed up into tapes or deleted after sufficient time.
Few stats:
Few tables (close to 20, 30) inMYSYS
schema could grow to around 50M rows.
We have asked for a total disk space of 4 TB.
MYSYS_AUDIT
schema might be having 10 times that of MYSYS
but we wont keep them more than 3 months.
Handful of tables in MYSYS will have the following transactions/mins.
- 100
INSERT
inMYSYS
meaning same number of inserts intoMYSYS_AUDIT
tables. - 1000
UPDATE
inMYSYS
tables meaning same number of inserts inMYSYS_ADIT
tables.
Questions:
Given all these, can you suggest me any improvements?- Separate schema affects disc I/O? (one extra I/O for every schema ?)
- Any general suggestions?
Figure:
+-------------------+ +-------------------+
| 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 |
+-------------------+
Apart from this, we have two more schemas with only read only rights, but mainly they are for adhoc purpose and we dont mind the performance on them.
Suggestions:
There are couple of suggestions. We are agreed to on the following.- Schema for logical separations.
TRIGGER
for inserting data into AUDIT tables.- Table names will not have
_AUD
suffix. :) - Procedure to populate
ARCHIVE
schema tables. - Partitions based on intervals.
We are analyzing ...
- Workspace manager option.
The question is still open for further suggestions before accepting APC's or dpbradely's solution.
Having a separate schema is definitely the way to go. Apart from anything else it means you can use the same table names - MYSYS.T1
and MYSYS_AUDIT.T1
- which is helpful if you have tables with long names (>25 chars).
But the main virtue of the separate schema is that the audit tables can be shielded from accidental or mischievous tampering.
The impact of inserting audit rows, e,g, from a trigger, remains regardless of who owns the tables.
general table design advice
It is a good idea to give the audit tables the same structure as the main table. So if you have metadata columns which you need for auditing, such as revision number, include them on the main table. Also, populate the audit tables with new values, not old. That is, when a new record is inserted into MYSYS.T1
insert a matching record into MYSYS_AUDIT.T1
; when an existing record is updated into MYSYS.T1
insert a new record into MYSYS_AUDIT.T1
. It is a lot easier to validate and report on the audit tables if their latest records are the same as the current ones in the main tables.
use of triggers
Auditing doesn't need to be complicated. All we need is an insert statement in a before insert or update or delete trigger
. These are easily be generated from the data dictionary view USER_TAB_COLUMNS.
Having the objects in a separate schema will not cause extra I/O - perhaps there was a misunderstanding and what your DBA meant to say was that the presence of auditing would result in extra I/O, which of course will be true no matter how you implement it.
Take a look at range partitioning for the audit data. You might want to ship auditing data to less expensive storage. Partitioning makes it easy to ship, for example a month of data, to another server. Or to delete autiting records older than a month.
http://www.orafaq.com/wiki/Interval_partitioning
In addition to APC's answer.
Could take a look at Workspace Manager, could be better than triggers as they can fail or be disabled etc.
Also, if you have the money, look at Oracle's Total Recall
精彩评论