29 December, 2006

ArchiveLogs and Transaction Volumes

Do you monitor ArchiveLog volumes ? I use ArchiveLog generation statistics as
a measure of Transaction Volume. With automated scripts comparing recent hourly
ArchiveLog rates against historical rates (eg hourly rate in the last 6 hours vis-a-vis
hourly rate in the preceding 36 hours ; hourly rate in the last 24 hours vis-a-vis
hourly rate in the preceding 72 hous), I hope to be able to capture spikes in
transaction volumes early.
Recently, we had a database ArchiveLog volumes go up by 20% in two weeks
and another 25% in the next two weeks -- thus by 50% in 1 month.
Systems and Storage were getting ready to provide more disk space for the
ArchiveLogs filesystem while I talked to the application team. There had
been some configuration changes requested by the users and, unbeknown to
the application administrators, Transaction Volumes surged by 50%.
When I asked them about the surges, they were very quick to identify the
cause of the surge.

Fortunately, there are a few {but not enough} application teams that are aware
of what archivelogs are and what they indicate -- a quick and ready measure
of transaction volumes.

28 December, 2006

ORA-1555 and UNDO_RETENTION

When you see ORA-1555s do you just go ahead and increase UNDO_RETENTION ?
Recently, I had a string of ORA-1555s occurring frequently through the day.
One quick suggestion was to increase UNDO_RETENTION. Some DBAs might do that.
However, that would have been jumping to a conclusion.
This was a database that had been running 9.2 for more than 2 years. I had one other
9.2 database with the same schema, usage and comparable size {for another business
unit} and wasn't seeing ORA-1555s there.
The trace files showed me that these ORA-1555s were always for the same SQL.
I knew that this SQL was a Refresh Query being executed to refresh a Materialized View
in another database.
Apparently, it was only this query that was reporting ORA-1555s at about 1 out of every 4
refreshes (the refresh being hourly) . However, later, a developer also reported a
similar query on the same tables taking a long time (but not yet erroring out on ORA-1555s}.
So, the fix was not to increase UNDO_RETENTION but to tune/optimize the tables
and specific queries. The rest of the database schema had no issues.

Why an Oracle DBA Blog ?

I have been an Oracle DBA for many years now.
About time I started posting some observations, I think.