PRODUCT REVIEWS

By Robin Schumacher

Third-party tools pack a punch missing from OEM

Bringing Oracle Management Down to Earth


Desktop DBA 4.0 Platinum Technology 1815 South Meyers Rd. Oakbrook Terrace, IL 60181-5241 (800) 442-6861 or (630) 620-5000 Fax (630) 691-0718 www.platinum.com	DBArtisan 3.1 Embarcadero Technologies Inc. 400 Montgomery Street, Ste. 300 San Francisco, CA 94104 (415) 834-3131 Fax (415) 434-1721 www.embarcadero.com

As more Oracle databases take charge of server machines, be they Unix or NT, one thing is certain: The complexity of managing them is a challenge. Thanks to a range of tunable parameters and advanced SQL features and extensions, Oracle can bewilder even the seasoned DBA. To cope, administrators look for tools to help bear the burden of day-to-day management, as well as assist in the monitoring and troubleshooting of escalating database problems. Although Oracle offers the Oracle Enterprise Manager (OEM) suite of products, systems professionals still look to third-party vendors to provide functionality unavailable in OEM.

Two vendors in particular--Platinum Technology and Embarcadero Technologies--have solid reputations for providing powerful yet easy-to-use products that make the Oracle management process as smooth as possible. Platinum's Desktop DBA has already found its way onto many DBA desktops, and Embarcadero has recently forayed into the Oracle world with its DBArtisan tool for Oracle, which builds on the company's celebrated version for Sybase and Microsoft SQL Server. A short while ago, both companies released updated versions of their products, and I was able to get a preview of what they had kept under wraps for so long.

Desktop DBA 4.0

Platinum Technology
Many DBAs use Desktop DBA to manage their databases across the enterprise, and, without question, those using earlier versions will be quite pleased with the tool's new touches. In contrast to the OEM approach, which supports Oracle databases only, Platinum realizes that today's DBAs must tackle an array of database products. Thus the company has given Desktop DBA the ability to manage not only databases from Oracle, but others from Sybase, Microsoft, and Informix as well.

Platinum has always impressed me with the quality of its products, education, and support, so I was eager to see the latest version of its tool. Desktop DBA was originally developed by Datura Corp. (which was acquired by Platinum in 1994); at press time, the most current release was 4.0.3. To test the product, I used a Compaq Pentium 120 MHz machine loaded with 64MB of RAM running Windows NT 4.0, as well as a 133 MHz Pentium machine running Windows 95 with 32MB of RAM. The installation of the product went very smoothly; Desktop DBA uses about 14MB of disk space. In addition to installing the product, a setup routine must be performed for each database instance against which you'll use Desktop DBA. This routine primarily consists of running a setup program that creates an Oracle user and various owned views and synonyms.

I was pleased with the new face Platinum has put on 4.0. Gone is the gray, "rainy-day-in-Seattle" interface; in its place is a much improved, more colorful DBMS explorer (see Figure 1). Within the explorer, DBAs can register the various database servers with which they work. When a database is registered (by supplying a server name, ID, password, and connect string), you can use Platinum's native drivers to connect to your servers and get to work.

Taking Care of Business

For handling the everyday physical DBA duties, Desktop DBA packs a good punch. Administrators can connect to multiple Oracle instances and easily handle things like object creation, space management, and security. While I found the initial interface to be much improved over 3.0, I encountered the same old-fashioned dialog boxes loaded with buttons for managing various object properties. Although it's not the most elegant approach to GUI design (I prefer the tab-style interface), there is nothing wrong with this style at all; it's very functional and easy to use. The one thing that isn't so functional is the tiny, hard-to-read windows in some of the object dialogs that present object names (see Figure 2). Platinum has fixed this problem in some parts of Desktop DBA, but other areas remain untouched.

One unique feature of Desktop DBA is the ability to drag and drop objects among Oracle instances. This function works nicely when the same tablespaces and users exist in the two databases; it will aid DBAs who move objects from test to production databases that are similar in nature. Unfortunately, I found that problems occur when the databases differ. Platinum now prompts you to specify information during the drag operation--such as the new owner of the object in question--but doesn't provide the same dialog for a new tablespace. I found that if a tablespace in a target database doesn't have the same name as in the source, Desktop DBA produces one error message stating that it can't find the tablespace and another message saying that the operation completed successfully when it actually didn't. This problem can be easily fixed by allowing the specification of a new tablespace. The drag-and-drop operations worked flawlessly, however, when I dragged Oracle tables to a Microsoft SQL Server database--a benefit that anyone involved in those types of database migrations will appreciate.

Another reason Desktop DBA is nice to have around is for the help it provides with extended table alters. Many DBAs get requests to insert columns in the middle of a table or perform other difficult operations that affect a table and its dependencies. I've used the tool many times to perform these extended operations, and, without a doubt, it's saved me a lot of time. However, caution is in order here: You must log in as the owner of the objects being altered and not as SYSTEM or another user, or the alter will fail. Platinum may eventually want to take the stance that Embarcadero has taken with DBArtisan and disable extended table alterations when the logged-on user doesn't match the owner of the objects being altered.

In addition to providing object management, Platinum offers a number of other useful features. Recently, my shop installed a new version of PeopleSoft's financial package, so we had to compare the database structure with the new one to see what changed. Fortunately, we were able to use Desktop DBA's Compare Database function to examine the two databases for differences. Other utilities provided in Desktop DBA include effective graphical methods of loading and unloading data, and a facility for extracting database DDL that is difficult to use but works fine when you get the hang of it.

What's Missing?

Platinum has put together an excellent tool, but there's always room for improvement. I found a couple of the newer Oracle features missing from the product such as the ability to create bitmap indexes. In addition, other functionality--such as the capacity to shrink rollback segments, alter a tablespace's underlying data files to autoextend, and resize an existing tablespace's data files--was absent. For data loads, the ability to specify parallel and direct path loads would be welcome because these two options can drastically reduce load times. Fortunately, Platinum has included some of the new Oracle functions, such as the ability to rebuild indexes and deallocate unused space for tables and indexes, both of which are useful. Furthermore, sources at Platinum tell me that other 7.3 features not included in the 4.0 release will be present in 4.1 (now in beta).

Other functions that would be helpful include an EXPLAIN function for the SQL editor and more statistical information regarding the health of a database. Platinum may be holding off in this area because they have other products that specialize in database and application tuning (SQL-Spy and Plan Analyzer, respectively), but it has put a few useful monitoring functions into Desktop DBA. For example, DBAs now have the ability to view a user's connection for details such as I/O statistics and personal hit ratios.

I found the stability of Desktop DBA to be quite good, although I encountered a few general failures under NT 4.0 when exporting tables. These experiences were few and far between, however, and I found that, overall, the tool operated smoothly.

In terms of support, I contacted Platinum on one occasion to report a problem I found in an extended table alter experiment and was impressed with the attention I received. Support is a sore issue with me on many of the products our company owns, but Platinum has always been great. Using their normal support channels, I was quickly put in touch with an engineer who was able to duplicate my problem and log it as a bug. Then I received something I didn't expect: a call from a Desktop DBA developer to thank me for finding the problem. He also reported the problem had been corrected and the version in which the fix would appear. This level of support is rare--and impressive.

Support for Oracle8

With Oracle8 now a reality, when will Desktop DBA support for Oracle8 be available? Sources at Platinum tell me that version 4.1 of the tool will have much more support for many 7.3 features and be will compatible with Oracle8; no support, however, will exist for new object/ relational aspects such as partitioning, objects, and so on. Platinum was vague about when Desktop DBA would support Oracle8 completely, telling me only that the fourth quarter is a possibility.

The Long and the Short

Platinum has brought a lot to the table with its latest offering of Desktop DBA, and I'm confident that users won't be disappointed. To stay ahead of Oracle's OEM products, Platinum is working hard to provide things that make your life easier, and, although I found a few flaws in the product, I have no doubt that the company will make the necessary adjustments to keep the tool top-notch. If you're looking for a single product to manage your heterogeneous database environment, check out the latest version of Desktop DBA. At only $995 per user for a single database vendor, you'll find it a bargain over the time and effort you'd invest without it.

DBArtisan 3.1

Embarcadero Technologies DBArtisan is a terrific tool that assists database professionals in the management of Sybase and Microsoft SQL Server databases, and it has garnered a strong following in most shops that use these products. My partner and I use Artisan every day to manage the many SQL Server databases that require our attention; we've found it to be indispensable for day-to-day DBA tasks.

The waning popularity of Sybase coupled with the strong market for Oracle has caused Embarcadero to expand its DBArtisan line beyond the SQL Server realm to support Oracle's RDBMS. If past success in the Sybase and Microsoft arenas is any indicator, Embarcadero is likely to have a hit on its hands with its new Oracle offering.

Getting Started

The most current offering of DBArtisan available at the time of review was 3.1. I tested the product on a Windows 95 Pentium 133 MHz PC with 32 MB of RAM, as well as a Windows NT 4.0 Pentium 120 MHz machine loaded with 64 MB of RAM. The installation of seven diskettes consumed about 20MB on my machine and completed without a hitch. Unlike Platinum's Desktop DBA, no database installation is required for each Oracle instance the DBA will be managing; the DBA simply registers each instance by using Artisan's wizard-driven registration facility. When connected (the tool uses native drivers, not ODBC for Oracle connections), you're ready for action.

Using the very popular hierarchical navigator approach to object management, Artisan lists all the Oracle objects that a DBA will need to manage or create (see Figure 3). Embarcadero has done an excellent job of making Artisan's workspace an intuitive method for working with Oracle databases. The environment sports a polished look with multiple toolbars located throughout for quick access to object and tool facilities. Gone are the large, graphical toolbars in previous versions of Artisan (my favorite was always the Kill Process toolbar button, which showed the Grim Reaper, complete with sickle), but what hasn't changed is the product's ease of use and power for managing databases.

Wizards Everywhere

Nearly every object-creation process or other job has an associated wizard that guides the user through the task at hand. Some DBAs like wizard-driven tools; others prefer to bang the necessary syntax into one or more dialog boxes (as with Desktop DBA). I've seen good and bad implementations of wizards, and while I used to lean toward wizardless tasks, DBArtisan has me rethinking that approach. The product's wizards are succinct and supply good default values that allow DBAs to speed past some of the more mundane components that make up tables, indexes, and other objects. At the end of each wizard, Embarcadero allows the user to preview the SQL that will be executed (an excellent idea); other options allow the SQL to be saved to a file, printed, or emailed to another party. Embarcadero is currently working on a scheduling agent that will also allow the SQL to be executed at a later, predetermined time.

After you create an object with a wizard, you can create subsequent objects just like them with a clone feature. I recently used this highlight to build numerous rollback segments in an Oracle database I was reorganizing and it was a real time saver. The clone function is available for every object type that exists in Oracle, and you'll probably use it often.

Embarcadero may be fairly new to the Oracle scene, but it certainly isn't naive about the RDBMS. I was hard pressed to find an Oracle feature that Embarcadero hadn't built in to Artisan--including bitmap indexes, index rebuilds, tablespace data file resizing, rollback shrinks, and more. Artisan is also smart enough to know which version of Oracle a DBA is using, so mistakes when building objects are rare. For example, a choice to build bitmap indexes didn't show up when I ran Artisan against Oracle 7.2, but the option did appear when I executed the tool against version 7.3.

I was also impressed with the quality of information Embarcadero provides for each object category. For example, when tablespaces are clicked-on in the navigator, Artisan displays a nice listing that shows total space, available free space, and fragmentation information (see Figure 3). When rollback segments are selected, the DBA is presented with a rollback report showing the status of each segment along with the current number of gets and waits. Clicking on the report header columns allows the listing to be sorted by the column selected. Features like this one that go the extra mile give DBAs a nice overview of their objects and assist in making informed decisions.

For hardcopy listings of objects, Embarcadero provides easy-to-read reports that are built and shown in the user's favorite Web browser. The only problem I had with this approach is the time it takes to invoke the browser on the desktop. But when it's up and running, the reports were produced quickly.

Tool Box

Embarcadero has packed Artisan with useful tools that provide real value. As you'd expect, there are easy-to-use facilities that load and unload data. Keeping in step with the latest Oracle options, Artisan offers full support for parallel and direct path loads that can drastically reduce the time needed for these types of operations.

Another facility I found useful is the process monitor. Artisan shows demographic data concerning all the threads connecting to an Oracle instance, as well as locking information that can pinpoint contention problems. Embarcadero also offers the DBA drill-down capability from the monitor and allows the SQL a user is currently issuing, as well as statistical data regarding a user's session (such as CPU usage, sort activities, table scans, and more) to be viewed. Sessions may also be killed quickly for connections that are causing problems.

A slew of other tools are also provided for the administrator. DBAs can easily ferret out tables that contain chained rows, check redo log and archive activity, back up their control files, examine tablespaces for objects that won't be able to extend into the next extent, and examine instance parameters.

One tool that will no doubt see much action is the schema extraction facility. Taking an easy-to-use, wizard-driven approach, a DBA can selectively choose various Oracle schemas to copy or build DDL for an entire database. DBAs who frequently copy objects or schemas from test to production instances will truly appreciate this feature. If an administrator must move schemas or objects among databases frequently, Artisan provides a way to save the structure and style of the schema extraction so it may be quickly used again to repeat an extraction procedure.

On the Fly SQL

Naturally, Artisan offers a feature-rich facility for issuing SQL requests; I found the ISQL portion of Artisan to be one of the better query tools I've seen in a while. Embarcadero offers a variety of ways to execute SQL as well as PL/SQL requests. If only single statements are entered, requests may be sent as normal. If multiple SQL procedures are entered, the user has the option of issuing them one batch at a time. To help understand what the Oracle optimizer will do with a set of SQL, the ISQL utility also provides a complete EXPLAIN analysis so developers can examine their code for efficiency. Finally, to help with error processing, Artisan provides an error window that displays the line, column, and error message so programmers will have a good head start when their PL/SQL code isn't right the first time.

What About Oracle8?

DBArtisan offers support for nearly every (if not all) of the features found in Oracle 7.3, so the question arises as to how long we'll wait for Oracle8 support. Sources at Embarcadero tell me that version 4.0--due this fall--will offer multidatabase support for Oracle 7.3, Sybase, and Microsoft SQL Server. The company is also full steam ahead in designing Oracle8 functionality into the tool, and, if all goes well, support for Oracle8 could arrive by year's end.

Final Impressions

It's hard to find fault with Embarcadero's DBArtisan, but there are a few things that would make it even better. First, unlike Desktop DBA--which allows a DBA to manage different databases like SQL Server, Informix, and DB2 from a single tool--the 3.1 release of Artisan supports Oracle only. Sources at Embarcadero, however, tell me that the popular Sybase/SQL Server version of Artisan will soon be combined with the Oracle offering into one product.

In terms of bugs, they were few in number. The main bug I found involved the schema extraction facility and DELETE CASCADE constraints; the constraints were not included with the table DDL that Artisan produced.

I found the stability of the tool to be quite good and experienced only two general failures during large table unloads. In terms of speed, the tool seemed to run a bit faster under Windows 95 than NT 4.0, and it took somewhat longer to initialize under NT as well. Finally, the help system was quite thorough and provided good assistance when it was needed.

Embarcadero has priced DBArtisan 3.1 at $899 per user, which is a real bargain when you consider what the tool offers. If you're an Oracle DBA looking for some relief from day-to-day database management tasks, do yourself a favor and point your Web browser to www.embarcadero.com to download a trial copy.

Robin Schumacher is a senior DBA and developer for Louisville Gas & Electric in Kentucky and a principal at Advanced Computer Designs. He's also the coauthor of The PowerBuilder 5.0 Developer's Resource (Prentice Hall, 1997). You can reach Robin via email at robins@ka.net.