-
Db : 8.0.6
I have a database that has just one application schema. All it contains is some 400 snapshots to a master database. Don't go by the number, all these snapshots get refreshed 'COMPLETE'ly every night and it takes about 7 minutes. Real small tables.
These are not updatable snapshots.
Now, given the fact that I have only this one application schema in the database, the snapshots are not updatable and they get refreshed once every night, I expect to see no rollback segment activity. At least pretty low, correct ?
On the contrary, I am seeing the rollback WRITES incrementing continuously. I can not see any transactions in the database. Just SELECTs. v$transaction, v$open_cursor are always empty. I don't even see archive logs getting generated frequently.
Someone is telling me the db might be suffering from WAITS on rollback segments. My question is WHY - when noone supposed to 'write' anything to this db ?
Is there a way to find out who is using the rollback segments in this scenario and for what ? Which processes should I expect to be using rollbacks ?
-
-
So you say you don't see any active transactions on the system, yet you observe high rollback writes activity. But are there any writes happening at the same time in other tablespaces/datafiles?
I guess you are encountering so-called "delayed block cleanout" mechanism in Oracle. Oracle does not necessary write to database files all the necessary informations in all changed blocks after each commit, particulary after masive block changes (like your complete refresh). Large number of blocks can remain on disks marked as "dirty" and are "cleaned out" only when they are read for the first time after the change. So in fact a pure read-only aplication can cause quite extensive write activity after a mass data changes.
If you wan't to avoid this to happen during normal working hours you can simply isue a full table scan on each of your changed tables/snapshots after your refresh job is finished. This will cause the delayed block cleanout to happen immediately after the refresh.
-
I'll give it a try. But the block clean-outs should result in READs of rollback segments isn't it ?
On the other front, the database doesn't have any other operations expect these SELECTs.
[Edited by svk on 09-26-2001 at 05:42 PM]
