Oracle Designer – on Design Editor performance and Repository Maintenance

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;