Re: Designing an application log for performance (NEWBIE)

Hi David,

If this is truly an Application Audit table, then in theory it does not require any indexes, and therefore don't create them since it will only slow you down. An old measure, and I am not sure if it is still valid under Oracle8, is that if an insert into a non index table takes 1 unit of time, then for each index on the table, you require between 2 and 3 extra units of time to maintain each index. To manage the Audit table, I would suggest creating Oracle Triggers on the controlling tables, so that no one can bypass the auditing, which is possible if the auditing is implemented at tool level (Oracle Forms).

With regards to your Digital Cluster, you will need to run Oracle Parallel Server (OPS) in order to utilize both machines against the same Oracle database. One big problem with Oracle8 v8.0 is that you still have 'pinging via disk' when a user connected to one of the servers (server1) requires data currently being accessed by a user on the other server (server2). When the user requests the data block from server1, server1 discovers that it does not own the data, and asks server2 if it owns the data. If server2 has the data in cache and it is still dirty (modified), then server2 has to write the data block(s) to disk and when the write is complete, sends a message back to server1 saying that the data is now on disk, and that control of the data block is now handed over to server1. Server1 then accesses the database to get the data into the buffer cache, and then finally sends the data back to the client. Now if a client of server2 wants this data back, then the whole thing has to be done in reverse. This is one of the reasons that partitioning the application users on OPS is of importance. Under Oracle8i (aka v8.1), Oracle have introduced a concept called 'Cache Fusion' and this eliminates then need to 'ping via disk'.

As far as the original table is concerned with the indexes, try not to index updatable columns unless absolutely required since this results in even more overheads on managing the index structure (a delete and an insert = 4 -> 6 units of time). Having said this, if the application requires the indexes, then you need to create them. It sounds like you have a reasonable system so they should be able to cope. Just spend the time working on the OPS configuration and read the OPS manuals.

Andrew

David Thom wrote:

> We're jumping into a project with Oracle8 (our first with Oracle), and need
> some guidance on how to design an application "log" table for
> high-performance/high-volumes (not the Oracle internal transaction log, but
> an application table which is receive rows of application-level data).
>
> We're estimating as many as a million rows/day, initially running on two
> clustered 4-way Alphas each with 2GB RAM under Digital UNIX 4.0e.
>
> Are there any Oracle-unique considerations when adding huge numbers of rows
> to an existing table? What if this same table also needs multiple indexes?
> Are there design considerations which would help minimize the performance
> hit of updating the indexes?
>
> Any guidance appreciated!
>
> David Thom
> NPSi Houston
Received on Wed Apr 28 1999 - 19:08:22 CDT