Turning your Designer/2000 environment will help you optimize your efforts and gain maximum benefit from your application development projects. Use these ten tips to make sure your development engine is delivering peak performance.
By Mahesh Vallampati
Oracle's Designer/2000 is a rapid application modeling and development toolset based on the client/server paradigm. The front end-typically a Microsoft Windows client-accesses data in a repository stored in an Oracle database server. The Designer/2000 toolset consists of the following components:
The following tips and techniques can help you provide a framework for running a well-tuned Designer/2000 environment in these three component areas.
Tip 1: Bring your system up to speed.
Be sure to use a certified combination of Designer/2000 and the Oracle7 database to make sure the releases are compatible. In order to install Designer/2000, set up the database with the Procedural option installed and all DBMS packages valid. Make sure the database is installed with the Distributed option linked in order to exploit Designer/ 2000's reverse-engineering features. The open cursor parameter should be set to at least 200 in the database initialization file. The repository owner should be granted execute privileges on the DBMS_Shared _Pool, DBMS_Lock, Standard, DBMS_Pipe, and DBMS_SQL packages prior to installation. Each repository requires at least 70 megabytes of system tablespace to store the APIs and at least 18 megabytes of Shared_ Pool_Size in the System Global Area (SGA).
Tip 2: Re-create indexes and hash tables frequently.
The Designer/2000 repository consists of a set of tables, views, indexes, procedures, packages, and triggers. Oracle added hash tables in Release 1.2 to improve access performance. With Designer/2000, you start with an empty repository and fill it with application data. The indexes and hash tables originally created with an empty repository may not continue to be efficient as the repository is filled with data. Therefore, you can improve performance by periodically re-creating the indexes and hash tables. Doing this every two months is usually sufficient, but it can be done more often depending on the rate at which the data in your repository grows.
Designer/2000's Repository Administration Utility provides a method for re-creating indexes and tables. Make sure there are no clients logged on to the repository by shutting down the database and restarting it in exclusive mode, granting access only to the repository owner. Re-creating the hash tables is basically a process of truncating the RM$HASHES table and re-populating it with the data in the repository's two main driving tables-SDD_Elements and SDD_Structure_Elements-using the Repository Administration Utility. These tables are owned by the repository owner.
This process requires full updates on the hash tables used to access repository tables, which consumes a lot of rollback segment space and time. Do the updates about every two months.
Tip 3: Size the SGA Shared_Pool_Size and DB_Block_Buffers.
You need a Shared_Pool_Size of at least 18 megabytes to load the minimum required portion of the API, but 30 megabytes is optimal for the Designer/2000 environment. A higher Shared_Pool_Size value boosts performance, because fewer SQL statements are swapped out of the shared pool.
The Designer/2000 installation requires that the minimum required DB_Block_Buffers setting be 1,000. Setting this parameter in the range of 1,500 to 2,000 improves performance significantly. The choice of database block size is operating-system-specific and is typically listed in the Oracle installation guide for your platform.
Tip 4: Pin procedures to the SGA.
Designer/2000 is procedure- or package-driven. You can improve performance by pinning the packages in the SGA; once pinned, packages don't have to be loaded from the system tablespace into the SGA.
Pinning the procedures to the SGA means that the Shared_Pool_Size should be at least 18 megabytes, which loads only the minimum required portion of the SGA. A Shared_Pool_Size of at least 30 megabytes provides good performance by preventing swapping of the API packages.
One way to pin is to press the Pin button in the Repository Administration Utility. This procedure is manual, however, which means the pinning is lost if the database is shut down and restarted. Alternatively, the repository owner can run the SQL script in Listing 1 when the command Alter System Flush Shared_Pool is executed, to keep the packages and procedures persistently pinned. The best method is to use this script as a part of the repository database start-up procedure.
Tip 5: Size the tablespaces.
The system tablespace should be set to at least 100 megabytes, Repository Tables tablespace to at least 200 megabytes, indexes to at least 100 megabytes, and temporary tablespace to 50 megabytes. The Sort_Area_Size should be set to 64K, because Designer/2000 does a significant amount of sorting of present data. This 64K value is site-specific and can be tuned by observing your site's sysstat tables.
Tip 6: Re-create the repository through import/export.
The SDD_Elements and SDD_Structure_Elements tables can become fragmented from frequent insertion, updating, and deletion of rows. If there are not enough extents for the table to extend, back up the database and perform the following steps: 1) Export the repository owner, using the Compress Extents option; 2) Drop the repository owner, including contents; 3) Reimport the repository owner, using the Compress Extents option; 4) Run the Recompile Objects option in the Recreate Menu to recompile and validate the repository API packages; 5) Re-create the hash tables and indexes, using the Repository Administration Utility.
Exporting and then reimporting the data reduces row chaining to its lowest theoretical limit. Exporting and importing by using the Compress Extent option imports table data in a single contiguous extent if such an extent can be created, which speeds up table access and dramatically improves performance. However, during this process the repository API packages, hash tables, and indexes must be recompiled and revalidated (as in steps 4 and 5).
Tip 7: Be aware of the impact of sharing objects across multiple application systems.
A Designer/2000 repository can consist of many applications, and it is possible to share objects between applications in the same repository. You cannot share objects across repositories, but you can move objects across repositories by using Designer/2000 utilities.
It is good practice to have at least two repositories-one each for testing and actual development. Designer/2000 is based on the Rapid Application Development (RAD) paradigm. Developers can work in the test repository and then move objects to the development repository after testing is complete and functional requirements have been met. Doing RAD tasks in the development repository is risky because-in the process of experimentation and testing-some objects may be changed and then may not be able to be reset. Moreover, when developing templates, the developer needs administration privilege on applications, which is risky in the actual development repository. In a test repository, developers are free to experiment, validate results against the requirements in the development repository, and initiate change-control procedures to make the changes official.
Designer/2000's paradigm of sharing objects among applications can be an administrative problem when used indiscriminately, because it can have an impact on versioning and backup of application systems. Consider the following scenario: There are three applications: A, B, and C. A is the main application and contains shared objects from B and C. When A is versioned, a new version is created, A2. Copies of B and C are also made and called 01B and 01C. These are frozen along with the original version of application A.
Creating a new version of an application, in this case A, also creates multiple copies of the shared applications, which puts too many applications in the repository. To avoid this, restrict the use of the share privilege in Designer/2000.
Tip 8: Use suggested settings for PC clients.
The optimal settings for the Designer/2000 client on a PC are:
Hardware: 25MHz or better 486DX2 processor, 32 megabytes of RAM, minimum 500K conventional memory (before starting Windows), Microsoft Windows 3.1 and DOS 5.0 or higher, and SQL*Net-supported networking software.
PATH settings: Designer/2000 executables are executed using two types of Windows files: executables (.exe) and dynamic link libraries (DLL). Using the Designer/2000 executables involves significant DLL access in Oracle_Home/ bin, so put this directory in the PATH variable first. For example, set PATH as:
PATH J:\ORAWIN\BIN; C:\NETWORK;C:\WINDOWS;C:\DOS
In this example, J:\ORAWIN-the client's Oracle_Home-is actually on a network drive.
Designer/Developer 2000 DLLs: One way to tune the clients is to preload Designer/2000 and Developer/2000 DLLs by executing the programs Oracle_Home/bin/cdeinit.exe and r25srv.exe in the Windows startup group. This is particularly useful if Designer/2000 is stored on a network drive.
Tip 9: Install Designer/2000 client tools on a file server.
Designer/2000's client tools need as much as 300 megabytes of disk space, so it's not feasible to install Designer/2000 clients on every PC. Instead, install Designer/2000 on a file server and have the clients load tools from the server and then execute them. Loading executables from the file server can decrease performance; follow the installation guidelines closely to achieve the best performance.
Tip 10: Configure SQL*Net.
You can configure SQL*Net two ways for maximum results: prespawned server processes and dead-connection detection.
A common complaint about client/server tools such as Designer/2000 is that they take too long to connect to the server. The delay is caused by the overhead involved in authentication and verification and the resources needed to spawn a process on the server. SQL*Net 2.x provides a feature that reduces the time needed to accept client connections and speeds connect time by allowing the network administrator to prespawn processes. A good rule is to prespawn three more processes than there are users. The extra processes run utilities such as Reverse Engineering and Database Design Wizard. To set up prespawned processes, configure the PRE_SPAWN_MAX, PRE_SPAWN_LIST, and PRE_SPAWN_DESC parameters in listener.ora on the server.
Another common problem of client/server tools is client-side tools crashing while the server process is still running-and consuming resources and holding locks-which degrades performance. SQL*Net's EXPIRE_TIME parameter detects and cleans up dead processes. This parameter is set in the sqlnet.ora file on the server side and specifies the time interval (in minutes) for when a probe is sent to detect dead connections. If the probe finds dead connections, it forces the server process to exit cleanly, thereby clearing locks and releasing resources. This detection facility comes with overhead, including increased network traffic due to the dead connection probes being sent out at intervals-typically every 10 minutes-and additional processing required to differentiate between dead and regular processes.
An alternative is to have the system administrator use the Alter System Kill Session command to release locks and resources held by dead processes.
Designer/2000 is a valuable modeling and development tool. You can optimize your productivity by carefully organizing and managing its use. Implementing the above techniques should go a long way toward providing a well-tuned and powerful Designer/2000 environment.
Mahesh Vallampati ([email protected]) is a staff consultant with the Oracle National Telecom Practice. He has four years of experience installing, configuring, and deploying Oracle databases and Oracle Financials.