we are currently facing some performance issues with querying _DIFF views. to our knowledge, the problems from similar threads in this forum do dot apply.
doing a SELECT COUNT(*) on the DIFF view of a table with the most records takes a few minutes (5-8), although the result later on is just ~3000 rows. the explain plan says that most time is spent on doing merge-joins on the PKDC and PKDB views of that table (out of curiosity, what are these views good for btw?)
one thing we noticed is, that we're having quite a load of savepoints (~1600), nearly all of them implicit. do many implicit savepoints impact performance? (we assume so)
we found out that due to an application bug we are always opening a conflict resolution sesssion during workspace synchronization even if no conflicts are to be resolved which does not hurt directly, however the implicit savepoint comes from beginResolve(). thus, everytime somone synchronizes workspaces, an implicit savepoint is created. and I assume that these savepoints cannot be deleted afterwards by OWM as someone is in the respective workspace (which is btw. the normal case, because many users have an open application that has a session in that workspace).
any possibility to remove unneeded implicit savepoints in a reliable way? (or, for a first manual step: is it OK to simply remove all the implicit savepoints that have removable=YES?) we saw there is compressWorkspace[Tree], however if we interprete the documentation correctly, this procedure also removes explicit savepoints set by the user. also, what we would need is something that preserves the complete history including explicit savepoints set by the user and just removes anything else that is not needed for any function of OWM anymore. do any possibilities exist?
ok, quite a few questions again :-) thanks for some hints!
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.
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.