This content has been marked as final. Show 4 replies
Hi Andreas,1 person found this helpful
What version of Workspace Manager are you currently using? There is a bug fix for the _DIFF view that rewrites the views, which typically leads to much better performance. If you do not have that, it is worthwhile to file an SR to look into it.
The PKD* views generate the potential diffs, and then filter out any rows that have been synced, and then determines the rowid for the base row. The DIFF view essentially takes the results of these views and creates 3 separate rows for each diff. Those are the BASE, PARENT, CHILD rows used within resolveconflicts.
CompressWorkspace will work on both implicit and explicit savepoints. If you only wanted to remove implicit savepoints, you would need to call DeleteSavepoint on each of the savepoints, or iteratively define the range of savepoints specified to CompressWorkspace to only include impliciit savepoints. However, you can preserve history by setting compress_view_wo_overwrite=>FALSE, which is the default value. All of the rows will continue to exist, just within a single savepoint/version. This assumes the tables have the VIEW_WO_OVERWRITE setting, and not VIEW_W_OVERWRITE.
we are currently using 10.2.0.4.3 on the production system.
on the test system we did what you suggested to get rid of all the unnecessary savepoints and performance improved significantly. querying count(*) on the biggest _DIFF view took ~5s as opposed to several minutes before and the application also performs alot better again.
so, is it expected that a large number of savepoints can have such a drastic impact on the performance? or should the performance penalty be not that heavy? the documentation of CompressWorkspace clearly states that performance can be improved with purging the savepoints, so I assume that (no matter whether we have an OWM version affected by the bug you mentioned or not) at least to some degree there is a correlation between the number of savepoints and the performance.
would 1500 savepoints mean an unusual amount or should OWM in general be able to easily cope with that? not that we expect our users to ever generate this amount of savepoints with their normal usage, but just to get some feeling for what might become a problem in the future and what not.
Andreas,1 person found this helpful
It's the not number of savepoints that should impact performance, it's the data stored within the _LT table. The compress operation may be removing a large chunk of the table. If the history is being retained, and performance is still improving significantly, then that would be unexpected. Be sure that statistics are gathered on the WMSYS schema.
thanks alot for the information.
yes, we retained the history. so the size of the _LT tables should not have changed, still performance improved drastically.
I'll check with our DBAs whether statistics are gathered for WMSYS.