This discussion is archived
13 Replies Latest reply: Jul 22, 2011 11:26 AM by Ben Speckhard RSS

Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions

794598 Newbie
Currently Being Moderated
Hi All,

I need suggestions on the below usage of ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions procedure in OWM.

Requirement: Need to determine modified rows between two savepoints in a workspace and update modified rows.

Data:
Workspace name -'WPS1'.
Savepoint1 name -'WPS1_SVP1'.
Savepoint2 name -'LATEST'.

What I am doing?

1.Goto Workspace.

EXEC Dbms_wm.Goto_Workspace('WPS1');

2. Run below query on 'ALL_WM_MODIFIED_TABLES ' to identify modified tables and keep them in a collection say 'Modified_Tables'.

select distinct TABLE_NAME From ALL_WM_MODIFIED_TABLES where WORKSPACE='&WPS';

3.Set DiffVersions procedure between two Savepoints.

EXEC Dbms_wm.SetDiffVersions('WPS1','WPS1_SVP1','WPS1','LATEST');

4. Loop through the 'Modified_Tables' collection and run the below query on {table_name}_DIFF Views.

+select * from {table_name}_DIFF where WM_DIFFVER = 'WPS1,LATEST' and WM_CODE in ('I', 'U','NE');+

5. Update rows on modified tables.


Inputs needed:

Please provide suggestions/recommendations/feedback/limitations/best practises of the above used OWM elements (ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions.

Also provide me feedback on improving performance of any of the above queries.

My frontend application is based on .NET and it is running very slow.


Thanks in advance.
  • 1. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    The process that you are using is fine. The query will return all of the rows that were modified after the WPS1_SVP1 savepoint, although there shouldn't be any NE rows in the LATEST savepoint when the 2 chosen savepoints are ancestors of each other in the same workspace.

    As for the performance of the view, I would suggest to make sure that all of the tables have statistics collected. This would include the LT and AUX user tables, as well as the WMSYS schema. If that doesn't help, then I would need to see a trace of the query, which is usually best handled via an SR, as there is typically other information needed to gathered (database parameters, etc). Do you know if the performance issue you are seeing is occurring on all versioned tables, or just specific ones?

    Also, are you interested in the actual rows, or just the existence of any row for a given table? I wasn't clear what step #5 was updating. Are you updating the rows that are found in the _DIFF view another time, or just storing the existence of the diff information in some other table?

    Regards,
    Ben
  • 2. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    794598 Newbie
    Currently Being Moderated
    Hi Thank you for the response. It was good to get confirmation on the usage.

    Following are my updates on to your response.

    Para-1: If a row is deleted after WPS1_SVP1 will it be marked as NE in the LATEST savepoint?.

    Para-2: Performance issue that i am seeing occurs only on some specific tables with huge data. I will provide you with the DB parameters shortly as i don't have correct information right now.
    (possibly via a SR)
    Also is it possible to get few inputs for the below query-
    In case of DIFF view queries what are the key elements in query execution plan that needs to be considered for perfromance improvment.

    Para-3: In Step #5 i will update rows that are found in the _DIFF view.

    Thanks & Regards,
    Pv
  • 3. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    1. Deleted rows after WPS1_SVP1 will have a 'D' wm_code value, not 'NE'. 'NE' will only be used when the row never existed for a given branch.

    2. What is the performance of the DIFF view currently?  What are you expecting it to be?   The DIFF view will depend on many different factors: the number of versions in each of the 2 branches, the number of versions in the set of common versions used as the base, the number of actual rows in each of those versions, etc. Most likely, the optimizer is choosing a plan that is not optimal. Make sure that all of the histogram statistics are up to date, but beyond that, it might need a hint, different db parameters, etc. As mentioned, file a SR as that is the easiest way to handle performance questions.

    Regards,
    Ben
  • 4. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    aschilling Newbie
    Currently Being Moderated
    hello!

    we also need to use the ALL_WM_MODIFIED_TABLES view and at first I thought I understood how it works, but now I'm not that sure again.
    our application features a comfortable way of merging two workspaces. in order to just look at the tables that really need user interaction we'd like to find out which tables have been modified between two workspaces and since the last synchronization. this would be some kind of a directional check, is that possible?
    so, if a user would like to refresh, he's just interested in the parent workspace. if he likes to merge to the parent he would be interested what tables were changed in the workspace he currently is in (you might think that the user should know what he did, but in our case the user can also synchronize changes of his colleagues, so he does not necessarily know, whether it's already worth merging the workspace).
    I think that should be somehow possible with the modified tables view and the _diff views, but in some cases I don't understand the content.
    am I right, that in our case we should filter the ALL_WM_MODIFIED_TABLES view for the workspace and the savepoint LATEST? however, it looks like we also have to take into account other workspaces than LATEST, so if someone could shed some light on how the "savepoint"-column has to be interpreted exactly, that would be awesome :)

    regards,

    Andreas
  • 5. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Andreas,

    The ALL_WM_MODIFIED_TABLES view associates changes due to dmls/merge/refresh within each version enabled table to the savepoint in which the change was made. This includes both explicit savepoints, and implicit savepoints that are created for CreateWorkspace operations. There are cases when a version will have both an implicit and explicit savepoint associated with it. In those cases, the explicit savepoint would be listed in the view.

    To accomplish what you are trying to do you would need to use ALL_WM_MODIFIED_TABLES, and find all savepoints(not just LATEST) since the last refresh/merge operation that include a change to a particular table. We store information related to these activities, but it is not exposed currently in any view, and can change due to rollback/deleteSavepoint operations. However, you really should not need to worry about it, as OWM takes care of everything when you execute a merge or refresh operation. For example, if you call MergeTable on a particular table, and there is no data that needs to be merged, then the only work that would be done would be the check. None of the actual merge code would be executed.

    However, if you were interested in whether a table has data to merge/refrresh but do not want to actually perform the operation, then you would need to record the current savepoint in use when the operation is run. Later, you would need to check for new savepoints in the ALL_WM_MODIFIED_TABLES view since the previous one. The ALL_WORKSPACE_SAVEPOINTS view gives you an ordered list of savepoints within each workspace. However, rollback and delete operations could cause these stored versions to become stale, and so the stored savepoint information would need to be updated at that time to reflect the change. It is best to let OWM handle this, but could be done if needed.

    Regards,
    Ben
  • 6. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    aschilling Newbie
    Currently Being Moderated
    hi Ben,

    thanks alot for the extensive answer, I guess we understood everything now.
    we are aware that the actual merge/refresh does basically need no time when executed on a no-change-table (actually, our application also ignores these tables in the process), but we need the information beforehand. this is due to the way our process works. during setup we also need to load unchanged data in order to display the real diff data in the model hierarchy the user expects. for leaf-tables in the graph we can safely ignore everything that does not have diff entries associated, but for non-leaf-tables that may have children we can't optimize that way, as we might need some nodes to display subnodes with diff data in a tree. thus, the performance penalty is not actually from OWM but from loading unnecessary data. If we would know exactly which tables have changes, we can optimize that process and ignore more tables during the setup of our merge/refresh process.

    btw, is it to expect that the information about previous merge/refresh operations will be exposed via a view in upcoming releases of OWM? that would be quite handy, as it is a requirement of our customer for one of the upcoming versions of our application. else we'd have to implement it ourselves with some additional tables.

    regards,

    Andreas
  • 7. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Exposing that information is nothing that we have planned at this time. If this is something that you require, you could always file an SR and get an enhancement request filed. As I mentioned, there are a number of operations that can affect this, so I can't say for sure that it would even be possible. But filing a request is your best option for it to be considered.

    Regards,
    Ben
  • 8. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    875913 Newbie
    Currently Being Moderated
    Hello Ben and other OWM Experts !

    Our need is to identify the data changed during a process.

    I have a simple use case where I am using a table with half a million row update (around 250K rows deleted and 250k rows inserted). I call SetDiffVersion API. The query on _DIFF is very slow. Currently it takes almost an hour.

    What are the guidelines to make sure the query on DIFF works fast? I ran gathertable_statistics on LT and AUX tables. I don't have enough privilege to run statistics on WMSYS tables. Will get permission from from DBA and validate if the performance improves after running gather schema stats on WMSYS schema.

    Right now I am performing queries on LT table to retrieve the changed data based on the createtime. The performance seems to be good. Since its an internal table how risky is to use this table directly? Is there any document where I can understand the internals of LT table that provides details on delstatus, version and other OWM columns in this table?

    Thanks,
    OWM User
  • 9. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    875913 Newbie
    Currently Being Moderated
    Hello Ben and other OWM Experts !

    Your response to this topic is greatly appreciated.

    Thanks,
    OWM User
  • 10. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    875913 Newbie
    Currently Being Moderated
    Hello Ben and other OWM Experts !

    Your response to this topic is greatly appreciated.

    Thanks,
    OWM User
  • 11. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    What version of Workspace Manager are you using? If the performance does not improve after gathering statistics on WMSYS, let me know. However, debugging performance issues such as this one is often difficult within a forum without access to the appropriate traces,etc. Filing a SR might be beneficial.

    Also, there isn't anything dangerous about querying the LT table, but it really isn't supported either. The DIFF view handles all cases, which might be missed when querying LT/AUX directly. I would recommend determining why the _DIFF view is performing slowly, rather than trying to rewrite the view itself.

    Regards,
    Ben
  • 12. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    876879 Newbie
    Currently Being Moderated
    Thanks Ben in responding to this problem!

    I am using OWM 10.2.0.5.0

    The gather statistics on WMSYS schema did not help either. Following is the performance statistics that I observe

    •     _DIFF view selection (select *) for following number of row change
    o     for 50K row change - 11 minutes
    o     for 30K row change - 6.5 minutes
    o     for 20K row change - 5 minutes
    o     for 10K row change - 2.4 minutes

    •     _DIFF view selection (select (1)) for following number of row change
    o     for 50K row change - 12 minutes
    o     for 30K row change - 6.8 minutes
    o     for 20K row change - 4.8 minutes
    o     for 10K row change - 2.4 minutes

    While waiting for the OTN response, I created an SR. Following is what Oracle says.. They are asking not to gather statistics ! I tried the rule based optimizer suggested by Oracle. It did not help. I have responded back to Oracle with my observation.

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    I think you have run into following bug -

    Bug 7581604: BAD PERFORMANCE OF QUERY AGAINST _DIFF IF VERSION ENABLED TABLE IS ANALYZED
    This bug is fixed in11.2.0.1. There is no fix for this bug in 10.2. But you can use one of the following workarounds -

    1. Using a RULE hint in the query to force use of the Rule Based Optimizer, for example:
    select /*+ RULE */ * from <Table>_DIFF

    2. dont gather statistics !!!! when stats are gathered for a version enabled table, querying _DIFF view starts using a full table scan execution plan instead of index range scan.
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  • 13. Re: Using ALL_WMD_MODIFIED_TABLES,_DIFF view & SetDiffVersions
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    I saw the SR. I'd recommend to continue with that process, rather than here on the forums. It's much easier process in order to find a solution.

    Regards,
    Ben