Search My Oracle Blog

Custom Search

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.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016