Re: Designing an application log for performance (NEWBIE)

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Thu, 29 Apr 1999 08:56:28 -0700
Message-ID: <3728812C.7684E0FD@us.oracle.com>

One small clarification (and this time one I've thought through, thanks for picking up my last error on OPS, Andrew!). The first release of the cache fusion architecture is in 8i as Andrew mentioned. What he didn't say is that this first release addresses the read-write pinging issue (reading from one node, writing on the other). The next release will cover write-write pinging.

HTH. Pete

Andrew Babb wrote:

> 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

--

Regards

Pete

---

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark   2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

---

Received on Thu Apr 29 1999 - 10:56:28 CDT