Yesterday it was an almost nostalgic day. For several years – primarily in the late 1990’s – I was heavily engaged in everything to do with Oracle Designer, its Repository and API, and Version Control of Configuration Management. I spent many weeks at the UK Development Center for Designer 6i, discussed features and functionality with the architects and developers of the Oracle Repository, developed and taught courses on the new stuff and was one of the first to understand some maybe even most of it. Then I almost suddenly changed directions and spent most of my time on Java/J2EE and XML/XSLT as well as some Data Warehousing and BI. All exciting, all new. Yesterday however, I was back to Oracle Designer. One of our customers has a very interesting layout of Oracle Designer 6i. They have several dozens of Designer users. And they make heavy use of versioning, configurations for release management and workareas to set up different environments for development, test, production and maintenance. They even make use of branches for patch development. It was a feast for the eye, so to speak, especially since they also used CDM RuleFrame – a framework I had some involvement in as well. However, they were experiencing performance problems and had invited me to come and have a look – hoping I would present to them the silver bullet. The switch they could manipulate to turn good performance on. Well, as it turned out, the bullet wasn’t silver but it seems to be bronze! In this post I will tell some more about the analysis I performed and the steps for improvement I have recommended.
The complaints focused on the Design Editor. Not even so much the Forms generation process, as you would probably expect. It was just opening the Design Editor, opening nodes, refreshing nodes, switching between the tabs – simple database read operations really – that was agonizingly slow. Opening the node for a particular application system would take in the order of three minutes! At the same time, the RON performed pretty well and the queries I ran from TOAD directly against the repository were quite fast. This quickly helped me focus: database performance was fine, network traffic was not the issue and the client wasn’t all bad, considering the reasonable performance of the RON. Note: at the end of this post I have collected a few of the steps you would normally take when investigating performance issues with Oracle Designer and Oracle Repository/Oracle SCM (Software Configuration Manager). Just to be sure, I tested networkspeed (with tnsping) and I ran a couple of heavy duty queries on the repository. These performed rather well, as did the RON. This does not necessarily mean that no improvement can be made in database setup and repository maintenance. What it does mean is that nothing is horribly wrong with the way things have been setup and no huge gains in Design Editor performance are to be expected from within the database. Or at least, that is what I intially thought.
Focusing on the Design Editor
The Design Editor and the RON, while seemingly similar tools, each have a very different strategy for accessing the Repository. The RON is meta-model-driven (that is why for example it allows you to edit User Extended definitions) while the Design Editor has hard-coded repository access mechanisms. More importantly, the RON queries on demand and retrieves what you asked for, not much more. That means that with each node you open, it will access the database, but each time with a simple query. The Design Editor by contrast makes use of a client cache, internally called the DOM. This client cache stores huge chunks of repository data. This means that for example the Server Generator and the Forms Generator do not have to access the Repository all the time. They only need to access the DOM – the Client Cache and database interaction is taken care of for them. If the Design Editor was described in terms of the Model-View-Controller pattern, the DOM would be the Model. The issue we run into here is the manner in which the DOM is populated. Obviously, at certain points during a Design Editor session, the DOM must retrieve data from the Repository into its cache. It tries to do so in an efficient way: large chunks of data at the time. This means that once the data is in the client cache, you can comfortably work your way around the Design Editor, the diagrams etc. The problem lies in the way these large chunks are defined. Or at least, that is the best explanation I can offer for the behavior I find. When first started, the DOM reads in the names of all Folders and Application Systems in your workarea. So far so good. However, when you open just one application system node, it starts to either load data for all (top-level) folder and application system nodes or it uses queries that are heavily affected by the number of folders (or objects) in the workarea, much more so than the RON. To give you more insight in what I mean, let me explain to you what I investigated. To find out exactly when the performance problem started to appear, I created a workarea that only contained the application system for one project as well as the SYSTEM_FOLDER and the folder for Headstart. When I opened the Design Editor for this workarea en performed the same operations that I had earlier tried with the WA_DEVELOPMENT workarea used by all developers, the performance suddenly was much, much better:
Open DE from RON Open Application System Node Open PL/SQL Definitions Node Total WA_DEVELOPMENT 3 58 to 201 10 to 50 71 to 258 WA_TEST 3 4 to 11 6 to 10 13 to 24
Apparently, a larger number of Application Systems and/or a larger number of Objects in the Workarea has a huge impact on the performance of the Design Editor (500 to 1100 %). If somehow you can lower the size of your workarea, you may see a huge increase in performance. I have not yet concluded whether it is the number for folders, the number of top-folders, the number of objects in the top-folders or the overall number of objects in the workarea that causes the huge difference in performance. I can imagine that it primarly is the sheer number of objects loaded into the cache rather than the complexity of the queries, but I don’t know the DE well enough to know for sure. You could try and see what happens if you ensure that all top-level folders and application systems are empty: do not contains objects, only sub-folders and application systems. That may do the trick as well. The workarea WA_DEVELOPMENT, which was the primary workarea for all development work, contained all Application Systems and Folders from all projects. That meant that the workarea included 23 application systems. However, at least three mutually independent projects could be identified which in turn meant that we could split up the workarea in three WA_PROJECT_X_DEVELOPMENT workareas for each of three projects, with no problem at all! I think that when performance of opening individual element type nodes in the DE – for example the PL/SQL Definitions node – is still taking too long, you may see a substantial improvement by creating several sub-folders or child-application systems and distributing the PL/SQL Definitions and other objects across the sub-containers. The DE will load all objects in the node, so if you make sure that there are fewer objects (by having the node effectively distributed across several nodes) to load, opening the load will probably be much faster.
Repository Maintenance
We had already concluded that nothing was inherently wrong with the way the database was setup and the repository configured. However, there is always a number of things I investigate: Wastebasket, Lost+Found, Version Volume (and Insignificant versions) etc.. In more detail: Wastebasket The Repository employs logical delete when you delete an object. It is not physically removed from the repository tables when deleted; all that happens is that a flag is set that causes the record to be ignored by subsequent queries. However, it still occupies space and it also burdens your queries – in relatively light way, but still. You should make it a habit to periodically empty the wastebasket, for example weekly. You can empty the wastebasket manually from the RON or from a simple PL/SQL script that you could schedule using for example dbms_job. Emptying the wastebasket can take some time and resources- especially when it is full and has many structured elements in it, so I would recommend performing the operation outside working hours. Lost and Found When you create a new object, it is linked to the folder you create it in. More specifically, it is linked to the folder version that you are currently working on, and all subsequent folder versions until you remove the object from the folder. If you remove all folder versions that have the link to the object, the object ends up in the lost and found: it is orphaned and has no owning container. This basically means you can no longer make use of the object: you can not see it, include in workareas etc. You can only do to things: purge it from the repository (either manually or by script) or adopt it in another folder (through a script). The most simple scenario to get an object into the lost and found is the following: check out a folder; create an object; undo the check out of the folder. Objects in the lost and found can have references to other objects. They can be more damaging that objects in the wastebasket, as the Repository needs to take them more seriously. Getting rid of Lost and Found objects should be part of regular repository maintenance. Insignificant Versions When a repository is version-enabled, it will start to build up a collection of versions. Some of these versions are important in the long run: the root-version (1.0) of each object, the root of each branch, a merge-point between two versions and every version that is included in a configuration. Other versions are called insignificant: versions not used in any Workarea or Configuration, nor as root or branch junctions or merge point; in short a version that we can remove without affecting the version history for real. These versions take up space, slow down operations such as opening the Version History and basic query operations and in general clutter the repository. Removing insignificant versions is another periodic action in proper repository maintenance. These versions can be removed using the Purge Version dialog that is started from the Version menu in the RON – on a per object basis. Alternatively, you can use a Repository API script to purge these versions. An example of such a script is found at the end of this post.
Useful repository queries
(between parentheses you see the numbers for my investigation yesterday): Number of object versions in the Repository (44000):
select count(ivid) from i$sdd_object_versions
Number of distinct objects in the Repository (22000):
select count(distinct irid) from i$sdd_object_versions
Average number of versions per object in the Repository (2):
select count(ivid)/count(distinct irid) from i$sdd_object_versions
Top 10 most versioned objects in the Repository (from 152 to 61 versions; the 180 most versioned objects all had 10 or more versions):
select * from ( select count(ivid), irid from i$sdd_object_versions group by irid order by 1 desc ) where rownum <10
Number of objects in the Workarea WA_DEVELOPMENT (12000)
select count(*) from i$sdd_wa_context wac , i$sdd_workareas wa where wac.WORKAREA_IRID = wa.irid and wa.NAME='WA_Development'
Number of objects per object type in the Workarea WA_DEVELOPMENT (23 application systems, 503 folders, 2300 files, 1050 tables, 650 views, 2400 PL/SQL Modules)
select e.name , count(*) from i$rm_element_types e , ( select distinct ov.irid , ov.logical_type_id from i$sdd_object_versions ov where ov.WASTEBASKET = 'Y' ) ov where e.ID = ov.LOGICAL_TYPE_ID group by e.name order by e.name
Number of object versions in the Wastebasket (1400)
select count(*) from i$sdd_object_versions ov where ov.WASTEBASKET = 'Y'
Number of object versions in the Wastebasket, per object type (primarily files)
select e.name , count(*) from i$rm_element_types e , ( select distinct ov.irid , ov.logical_type_id from i$sdd_object_versions ov where ov.WASTEBASKET = 'Y' ) ov where e.ID = ov.LOGICAL_TYPE_ID group by e.name order by e.name
Number of Objects in the “Lost and Found” (objects with no owning folder) (1750)
select count(distinct ov.irid) from i$sdd_object_versions ov , i$sdd_folder_members fm where fm.MEMBER_OBJECT(+) = ov.irid and fm.FOLDER_REFERENCE is null
Number of Objects in the “Lost and Found” ordered by object type
select e.name , count(*) from i$rm_element_types e , ( select distinct ov.irid , ov.logical_type_id from i$sdd_object_versions ov , i$sdd_folder_members fm where fm.MEMBER_OBJECT(+) = ov.irid and fm.FOLDER_REFERENCE is null ) ov where e.ID = ov.LOGICAL_TYPE_ID group by e.name order by e.name
Number of Insignificant versions (versions not used in any Workarea or Configuration, nor as root or branch junctions or merge point; in short a version that we can remove without affecting the version history for real) (4750)
SELECT count(va1.to_ivid) num FROM SDD_VERSION_ASSOCIATIONS va1--Use view to avoid deleted versions , i$sdd_object_versions ov WHERE va1.edge_kind = 'N' AND va1.to_ivid = ov.ivid AND NOT EXISTS (SELECT NULL FROM SDD_VERSION_ASSOCIATIONS va2 WHERE va2.to_ivid = va1.to_ivid AND va2.edge_kind = 'M') --Not target for merge AND NOT EXISTS (SELECT NULL FROM SDD_VERSION_ASSOCIATIONS va3 WHERE va3.from_ivid = va1.to_ivid --Not tip,source for checkout --or merge AND va3.edge_kind IN ('T','C','L','M','B'))
Insignificant versions per folder:
select count(*) , jr_name.GET_PATH(folder_reference, 'NAME', '/',0) from ( select distinct ov.irid ov_irid , fm.folder_reference from i$sdd_folder_members fm , ( SELECT ov.ivid , ov.irid FROM SDD_VERSION_ASSOCIATIONS va1--Use view to avoid deleted versions , i$sdd_object_versions ov WHERE va1.edge_kind = 'N' AND va1.to_ivid = ov.ivid AND NOT EXISTS ( SELECT NULL FROM SDD_VERSION_ASSOCIATIONS va2 WHERE va2.to_ivid = va1.to_ivid AND va2.edge_kind = 'M' ) --Not target for merge AND NOT EXISTS ( SELECT NULL FROM SDD_VERSION_ASSOCIATIONS va3 WHERE va3.from_ivid = va1.to_ivid --Not tip,source for checkout --or merge AND va3.edge_kind IN ('T','C','L','M','B') ) ) ov where fm.MEMBER_OBJECT = ov.irid ) group by jr_name.GET_PATH(folder_reference, 'NAME', '/',0)
General recommendations for when performance is lousy
Most of these recommendations are very general and obvious. They come from Oracle support bulletins on MetaLink (see for example Note 187556.1 and 108822.1), discussion threads on OTN etc. The issue with performance is either database, network or client. To find the bottleneck, try the following: – run some of the queries listed in this post on the database (either from a SQL*Plus session started on the server machine or within a PL/SQL package to ensure server side execution); if these queries perform well, the server itself is not the issue. Better yet: if your server is an NT server, install the Designer Client tools on that server and try the same operation that is giving you a headache on the client. If there is a substantial difference, it is the network or the client. If performance is still crap, it must be the database. – try tnsping from the client to the database. Response times should be in the 0-20 ms range, otherwise the network is part of the problem 1) Ensure the PC’s have the minimum RAM required of 256 mb. 2) Set the PC’s Virtual Memory, Minimum and Maximum settings to be the same. 3) Use the Guides for navigating around the Design Editor and selecting PLSQL modules (Available from the Tools menu). 4) In the Repository Administration Utility, set ‘Compute Statistics’ to 20-50%. 5) Regularly (e.g. Weekly) carry out the standard Repository Administration tasks e.g. From the [Recreate Objects] button : Truncate Tempory tables, Reload missing objects, recompile objects, full reconcile. 6) Ensure all tablespaces, including Temporary and System tablespaces have plenty of space and are not ‘chaining’. (Discuss with local DBA for more information). 7) Increase the Shared Pool Size in the Init.ora file to as high as possible (this will be machine dependant, but the mimimum required is 32mb). (Discuss with local DBA to find the maximum possible for the machine). 8) Ensure tablespaces that the Indexes are created in are on a different disk to the tablespaces that the Data is created in. (Discuss with local DBA to confirm). 9) If using Versioning abilities, reorganise the repository to have more workareas with fewer objects in each. => I had not seen that one before my investigation, but this one was the bronze bullet alright! Specifically on Design Editor performance (where the performance is NT Account specific): Either delete the NT-account from the NT-domain and create it again, or replace the NTUSER.DAT file in the user’s account profile directory with a new (default) one. This must be done as NT-Domain-Admin and may result in additional activities, such as adding to groups and recreating resources. The problem may be caused by a large NTUSER.DAT file (for example, 4MB). By deleting the NTUSER.DAT file and copying the default one into the user’s account, the problem is resolved. NTUSER.DAT is a registry-editor compliant datafile, that stores user-specific Windows NT settings. Oracle states in its Statement of Direction: Significant work to improve performance has also been done. This work will be rolled into the 9.0.4.4 release as well as the parallel 9.0.2.8 and 6iP4.10. Ongoing work for performance improvement is suggested for future releases.
Purge Insignificant Versions from the Repository
declare c_workarea_name varchar2(50):= 'MY_WORKAREA'; c_uds_name varchar2(50):= 'REMOVE_INSIGNIFICANT_VERSIONS'; l_stack varchar2(16000); cursor c_smi ( b_uds_name in varchar2 ) is select smi.ELEMENT_REFERENCE irid , jr_name.get_CRN_from_IRID( smi.ELEMENT_REFERENCE , 'NAME' , 0 ) name from ci_set_member_items smi , ci_user_defined_sets uds where uds.NAME = b_uds_name and uds.IRID = smi.USER_DEFINED_SET_REFERENCE ; cursor c_wac ( b_wa_name in varchar2 ) is select ov.irid , jr_name.get_CRN_from_IRID( ov.irid , 'NAME' , 0 ) name from i$sdd_wa_context wac , i$sdd_workareas wa , i$sdd_object_versions ov where wa.NAME = b_wa_name and wa.IRID = wac.workarea_irid and wac.object_ivid = ov.ivid ; -- this function handles the current execption; -- it will analyze the error stack and return a string with all found exceptions,warnings and error messages function handle_exception return varchar2 is l_oracle_error_message varchar2(4000); l_message varchar2(100); l_stack varchar2(16000); arg0 varchar2(5000); arg1 varchar2(5000); arg2 varchar2(5000); arg3 varchar2(5000); arg4 varchar2(5000); arg5 varchar2(5000); arg6 varchar2(5000); arg7 varchar2(5000); arg8 varchar2(5000); msg_fac varchar2(100); msg_code varchar2(100); procedure write( p_text in varchar2) is begin l_stack:= l_stack ||chr(10)||chr(13)||p_text ; end; -- write begin if cdapi.stacksize > 0 then while cdapi.stacksize > 0 loop cdapi.get_message ( cdapi.stacksize , msg_fac , msg_code ,arg1 ,arg2,arg3,arg4,arg5,arg6,arg7,arg8 ); if instr( arg8, 'UDM') > 0 -- User Defined Message then -- now pop the message to remove it from the message stack! cdapi.pop_message ( msg_fac , msg_code ,arg1 ,arg2,arg3,arg4,arg5,arg6,arg7,arg8 ); write( msg_fac||'-'||msg_code||':'||arg7||arg6||arg5 ||cdwpbase.ifThenElse( arg6 <> '', ', '||arg6) ||cdwpbase.ifThenElse( arg5 <> '', ', '||arg5) ||cdwpbase.ifThenElse( arg4 <> '', ', '||arg6) ||cdwpbase.ifThenElse( arg3 <> '', ', '||arg7) ); else write(cdapi.pop_instantiated_message); end if; end loop; if cdapi.activity is not null then cdapi.abort_activity; write(Rob_msg.GetMsg(Rob_msg.MSG161_ODWACHCK_APIERR,'','','','')); else write(Rob_msg.GetMsg(Rob_msg.MSG162_ODWACHCK_SESSERR,'','','','')); end if; else l_oracle_error_message := sqlcode||' '||sqlerrm; if cdapi.activity is not null then cdapi.abort_activity; l_message := Rob_msg.GetMsg(Rob_msg.MSG161_ODWACHCK_APIERR,'','','',''); else l_message := Rob_msg.GetMsg(Rob_msg.MSG162_ODWACHCK_SESSERR,'','','',''); end if; if l_oracle_error_message = Rob_msg.GetMsg(Rob_msg.CAP079_ODWACHCK_UDEXCEP,'','','','') then write(l_message||'.'); else write(l_message||Rob_msg.GetMsg(Rob_msg.DSP216_ODWACHCK_ORAERR,'','','','')); write(l_oracle_error_message ); end if; end if; return l_stack; end handle_exception; begin jr_context.SET_WORKAREA(c_workarea_name); if nvl(c_uds_name,'') ='' then for r_wac in c_wac(b_wa_name => c_workarea_name) loop dbms_output.put_line('Remove insignificant versions for '||r_wac.name); jr_wastebasket.purge_insignificant_versions ( i_irid => r_wac.irid , i_branch_id => NULL ); end loop r_wac; else for r_smi in c_smi(b_uds_name => c_uds_name) loop dbms_output.put_line('Remove insignificant versions for '||r_smi.name); jr_wastebasket.purge_insignificant_versions ( i_irid => r_smi.irid , i_branch_id => NULL ); end loop r_smi; end if; dbms_output.put_line('You must perform a Commit or Rollback to either undo the changes are make them effective!'); exception when others then dbms_output.put_line('Error occurred:'); l_stack:= handle_exception; for i in 1..round((length(l_stack)/255))+1 loop dbms_output.put_line(substr(l_stack,1+255*(i-1) ,255*i)); end loop; end;
Oops!
I am sorry, i had a momentary memory lapse. You have to disregard my two previous comments.
I mixed up the performance problems in Design Editor with another performance problem with that I was working on.
BTW, Design Editor is NOT using RULE hint in general. Only in very few cases.
This time I have checked the document i wrote!
This is the findings:
1. When opening a folder in DE, a select is issued which check if there exists objects under the nodes (eg. Modules, Relational Tables and so on). I guess it is to determine if the nodes should be expandable.
2. When opening a node (eg. Modules)
2a. DE issues a select to find all the modules in that folder. On our system this select took about 10 seconds. This select will profit from that there is fewer rows in the folder_member table.
2b. Then for each module, it issues one select to get more info about each module. Its like Forms issuing post-query triggers to get lookup values for each row. Each of these selects took from 0.05 to 0.1 seconds. Now, if there is 700 modules as we have and the average for each select is 0.075 seconds. The sum of these selects is about 52.5 seconds. If you include the first select that finds all the modules, it takes 1 minute and 2.5 seconds to open the module node in my example. So you are obviously right about reducing the number of objects in each folder. RON is not issuing these selects, thats the reason why it is that much quicker.
A few clarifications.
1. The folder_member table is a link between a version of a folder and a version of a object.
2. Why it helps to purge the insignificant versions of the folder? The folder_members of the insignificant folder versions will be deleted as well. It will reduce the size of the folder_member table dramatically.
I have investigated similar performance problems in a fairly large repository.
This is what i found.
If you use a sql sniffer (like Quest SQL Monitor) you can see that DE uses the RULE hint for queries against the database. RON does not use hints; it uses CBO.
The problem is that if your application system (or folder) has a great number of objects, and the folder exists in MANY versions.
For every version of the folder, all the objects in it is stored as a record in the FOLDER_MEMBER table.
Say that you have 1000 objects in the folder, and 100 versions of the folder. Only this folder will have 1 million rows in the FOLDER_MEMBER table. Considering the RULE hint, the database has to do full table scan. The result is terribly bad performance.
So what is the cure? Purge all insignificant versions of the folder.