I'm developing a project management system based on workspaces.
The aim is to create and develop projects inside the workspaces, parallel to the stream LIVE, and after approvall, do the merge.
I have two requirements:
Req1 - Workspaces should be continually refreshed, during their "livetime".
Req2 - After the merge, it is necessary to save the final information in the workspace, just for future read.
I thought the following approach:
Use workspaces in CR mode and after merge, switch to NCR. The scenario would be stable and unchanging, maintaining the necessary historical data.
I found out that it's not possible to change a CR workspace to a NCR.
Besides, the raising of workspaces (tried with 30000), cause general database degradation. Queries on version-enabled and merge/refresh operations were getting slower and slower.
Three questions about this:
1 - Is there any way to "freeze" CR workspaces? Like... avoid the auto-refresh process?
2 - Is it natural that queries and merge / refresh operations become successively slower with increasing workspaces? (i noticed that many wmsys.wm$ tables/views are used in these processes and it seems to me that their performance is getting worse. Is that possible? If so, is there any way to optimize?)
3 - Assuming that the use of thousands of workspaces may have some performance issues, what is the best way to save a final (total and unchangeable) "image" of the database after doing the merge of a project?
I thought using a system with just one CR workspace (history WS) and savepoints... just to keep the final views of the projects.
After merging to LIVE, the history workspace would be auto-refreshed, I would put a savepoint there and keep the final vision. But with a CR workspace, the savepoint doesn't work in the way i need... the vision is altered every time we change rows in LIVE, right?
So, i tried to use a NCR workspace. After merge operation, i force a refresh in the history workspace, put the savepoint and it's done. But there's still a little problem with this scenario: LIVE may have hundreds or thousands of changes to refresh... the process can be slow and block the LIVE records for a long time.
Am i jumping to conclusions or assuming something wrong? Is there any technical solution that I'm not seeing?
Any help or ideas are welcome!
Thanks in advance.
1. There is a way to freeze a workspace, using dbms_wm.FreezeWorkspace, but it would only prevent dmls / workspace operations from taking place within the child workspace. It would not remove the auto-refresh functionality of the workspace.
2. No, you should not being having a significant performance impact on queries by just adding workspace. Have the statistics of the WMSYS schema been gathered, and for the schemas that contain the version enabled tables? If not, I would recommend doing so. If you have done so already, I would need to see specific queries, etc.
3. If you need to get a snapshot of the data without the possibility of it being changed, you can't use a CR workspace, so your only option would be a NCR one. How much of the data that is being modified by a child workspace is being additionally modified by other children workspaces? Essentially, does each workspace work with its own distinct data or would there be alot of overlap(ie conflicts) between them? How many of these workspaces need to be merged and/or refreshed simultaneously?
Have you thought about creating a savepoint in the LIVE workspace at the time of the merge and use that as the snapshot? It would not work in all use cases, so would depend on the specifics of your setup. I'll continue thinking if there are existing ways to accomplish this.
Also, in regards to CR workspace savepoints, the savepoint data can be changed if the child workspace doesn't have its own version of the row. Any new row in LIVE would override the view of the data from each savepoint.
First of all, thank you for the explanations.
Answering your questions:
1 - My schema has all statistics gathered and WMSYS tables statistics are automatically gathered.
I checked the latest update of statistics in that schema and it was very recent. It's relevant to gather stats for all schema or it's enough just the tables?
2 - Don't know if i understood well your questions, but where it goes... my scenario has something like 5000 CR workspaces dealing, in most of the cases, with different data. In some cases (small amount) it's possible to work with the same rows inside more than one workspace.
About the merge process: it is based on a queue and there are no parallel executions. In this process we use a sequence of mergeTable executions with where clause to garantee that just the modified rows are locked in the process.
3 - The solution you proposed (savepoints in LIVE) seems very good to me. I tried it and works just like we need.
Just have a problem... the execution of dbms_wm.createSavepoint (with workspace LIVE) takes more than 430 seconds to finish.
Monitoring the queries that are runing in the session, seems that it's made just an update. Something like this:
UPDATE WMSYS.WM$VERSION_TABLE VT1
SET ANC_VERSION = :B3
WHERE (WORKSPACE = :B2 AND ANC_WORKSPACE = :B1) OR (ANC_WORKSPACE = :B1 AND EXISTS
FROM WMSYS.WM$VERSION_TABLE VT2
WHERE VT1.WORKSPACE = VT2.WORKSPACE AND VT2.ANC_WORKSPACE = :B2))
It's supposed to take so much time? If not, what may be the cause?
1. It should be sufficient as long as all of the tables/indexes involved in the query have recent statistics. Would need to see the actual sql plans and statistics to have a better understanding as to whether or not the optimizer chose the appropriate plan.
2. Using multiple MergeTable operations per workspace will add to the total time required to do the merge process. The locking is similar in the where clause vs no where clause case. What locks are you expecting to not be acquired?
3. Since all of your workspaces are CR workspaces that have LIVE as the parent workspace, CreateSavepoint would need to update the parent version of all the children workspaces. So, that update statement would need to update a row for each workspace. With ~5000 workspaces, 430 seconds to execute is far too long. Could it have been blocking while waiting for a lock on the parent workspace? The CreateSavepoint procedure would require an exclusive lock. Otherwise, as with above, would need to see the specifics of the query and exactly what the optimizer is doing.