Most recent blog entries

Automation - Exporting and Importing Data

Debbie Peabody — Thu, 11 Mar 2010 19:25:00 GMT

In this posting I show how to automate exporting and importing and highlight the new changes in this area for Toad for Data Analysts 2.6.

Data Grid Differences Between Toad® 9.7 and 10.x

John Pocknell — Thu, 11 Mar 2010 07:00:00 GMT

For those of you who have already upgraded from an older version of Toad to Toad version 10, you will no doubt notice some visual changes and behavioural differences in the data grids.

Our Toad development team wrote an article highlighting these differences which is posted in the Toad for Oracle Community, but in case you missed it, here is the content produced in this blog so that you are all aware.

Toad 10 grids have been enhanced with additional features and are now consistent throughout Toad.

Here are some basic cosmetic differences:

Toad 10

Toad 9.7

The record navigator has moved to the bottom of the grid:

Toad 10

Toad 9.7

Here is a breakdown of the new Navigation Bar buttons and their descriptions:

You can now bookmark one location in the grid and return to it later. Put your cursor in the row you want to bookmark and click the Save Bookmark button on Navigator toolbar.

To return to the bookmark, click the "Goto Bookmark" button.

SPECIAL NOTE: Although the new Navigator Bar has additional features, we will be including the original Toad 9.7 style navigation buttons at the top of the grid for the next release of Toad, by popular request.

In Toad 10, the grids are now compatible with Unicode. This allows you to see UTF8, UTF16 and UTF32 data where in Toad 9.7.2.5 this was not possible.

Unicode data in Toad 10

Data Grids share a single popup menu. Here is a comparison of the Data Grids from Toad 9.7 to Toad 10:

The following right-click options from 9.7 have moved:

Select Columns is now found at the top right corner of the grid:
Import from Clipboard - now located in import table data screen. Right-click SB-Tables-Import Table Data (or from main menu)
Excel style filtering- no need to toggle this option as its build right into the grid automatically:
Multi-select is no longer a right-click item - with few exceptions, grids are multi-select enabled. Row select enables you to select entire rows at the same time. If you turn it off, you can select blocks of cells within a grid.
Refresh options/ Grid Options - Gone. There is an options button on the toolbar:
Action Console - Gone. There is an action console button on the toolbar
Set Sequence Field - This feature was replaced with an auto-increment via sequence and trigger. In prior versions of Toad, when you set up a sequence column, it would only work for the user that set it up. Now anyone can utilize the feature from the Schema Browser | Tables page, right-click and select Add | Trigger/Sequence Pair

Creating a Sequence/Trigger Pair

New options added for Toad 10:

Single Record Viewer (right click) - Can be accessed here as well.
Filter data:
Toggle Navigator (Right Click)- allows the Navigator buttons viewed or hidden.
Grids can be grouped by a column header by dragging the header into the Group By area at the top of the grid. This feature can be toggled on the right-click menu.
In addition, some grids that had special functionality in the right-click menu now have an upper toolbar containing that functionality, as in the Rebuild Multiple Indexes grid.

FINAL NOTE: We have some other Data Grid enhancements coming in the next release of Toad (version 10.5). Keep checking Toad World for upcoming announcements!

BFScripts, Part 3

Kevin Dalton — Tue, 09 Mar 2010 15:59:00 GMT

In the final part of this series on BFScripts, we will look at the different ways you can use BFScripts to your benefit, including when performing user load tests and when connecting to a database.

The most common use of BFScripts is to randomize SQL statements when simulating large numbers of users. As I mentioned in the first part of this blog series, , you will yield unrealistic results by submitting the same SQL from each virtual user. Doing so will cause the database server to cache the SQL results, thus increasing performance, and the real world is never that predictable.

A better way to perform this test involves randomizing the SQL transactions using BFScripts for a bind parameter value, as seen above. Using the same SQL statement above, you can convert the BFScript to a bind parameter value, as seen below.

Another little known, but useful, way to use BFScripts is in the connection profile to change database logon credentials. For example, you can use BFScripts to find Oracle profile user names and passwords, as seen in the screenshot below.

You can find more details on how to do this at the Benchmark Factory community:

BFScripts can also be used in the command line for an external file you want to execute before or after an iteration. Using BF Scripts here allow you to gather information such as current userload, to external scripts. This will help you as you gather more information about performance, create data files, and so on. As you can see below, we are calling a batch file to create a data file that will be used to read real data inside executed SQL statements.

Yet another way to BF Scripts is when customizing your Benchmark Factory job by adding Execute File steps. These steps can be used to restore a database before a test run and when changing the database configuration. There is a video located on the BMF community showing how to add BFScripts to customize a BMF job.

Do you use BFScripts to control connecting to the database server or for executing external commands? Is there anywhere else you would like to see BFScripts available? I’d love to hear your comments!

Testing Object Types with Code Tester 1.9

QCTO Blog — Tue, 09 Mar 2010 08:00:00 GMT

by Steven Feuerstein

An object type is Oracle's version of an object-oriented class. It shares some of the features of a class, but falls a bit short. In Code Tester 1.9 and higher you can define and run tests for methods of an object type. You can also directly test the contents of an instance of an object type.

Click here to watch a video demonstrating the features described below.

Download Code Tester 1.9 here.

Set Value for Object Type IN Argument

Prior to Code Tester 1.9, the only way you could specify the value of an object type instance was to write initialization code to assign values to the attributes of that type.

Now, you can enter values for these attributes in the Attribute Grid of the Properties Window.

Here, for example, is the Properties Window of the "compare to" IN parameter for the ORDER function of the Quick Start object type, qctod#test_object_type:

If the attribute has a scalar datatype, you can enter the value directly in the grid. You can only specify single values (as literals, expressions or queries). Multiple values (random generation, from lists, etc.) are not allowed.

If an attribute of your object type is a complex type, then you will not be able to type a value in the grid. Instead, you will need to open the Properties Window for that type and then write code in the initialization section to populate this attribute.

How to Test Object Type Methods

To select an object type for testing, simply click on "New Test" on the toolbar or use the Dashboard menu. You will then see the "Choose Program to Test" list, which now includes types. Simply choose the type of interest and you will be taken to the Test Builder, at which time you then select the method that you want to test.

You can define several different types of methods in an object type: constructor, static and member methods. You can also test the contents of instances of an object type. You will find links below to details on how to test each of these types.

You can tell the type of your method in the combo list at the top of Test Builder:

Test Constructors

A constructor is a function whose name matches the type itself and is used to construct a new instance of that type. Oracle provides a default constructor for each type. You must, however, provide a value for each attribute when you call the constructor. As there are no private attributes in an Oracle object type, this means that a programmer will have to be aware of and provide sensible values for all attributes.

Often, this is very much not how an instance should be initialized, so you can define your own constructor in which you specify precisely the input values desired. A constructor must be of the form:

CONSTRUCTOR FUNCTION object_type_name (
   SELF IN OUT NOCOPY object_type_name (your_parameter_list)
   RETURN SELF AS RESULT

To test a constructor, you choose that method from the program list at the top of Test Builder. The Input Grid will contain a row for every parameter in your function's parameter list. The Outcome Grid will contain the following:

A row for the object type instance returned by the function. How to Test Object Type Instances explains how you can complete the definition of this outcome.
A row for each attribute of the object type. The outcome that you can defined for each attribute is determined by its datatype.

Generally, you will not want to set up an outcome for both the instance returned by the function and its attributes. Simply remove those outcomes that do not apply for your particular test.

Here, for example, is the default outcome grid for the constructor function of the Quick Start object type, qctod#test_object_type:

Test Static Methods

A static method is a function or method defined in an object type that is not connected to an instance of that object type. Every IN and IN OUT parameter will appear in the Input Grid. You can define one or more outcomes for the OUT and IN OUT parameters (and return value, if the static method is a function).

You will test static methods in exactly the same fashion as you would a procedure or function in a package.

Test Type Member Methods

A member method is a procedure or function that operates on a particular instance of an object type. It is executed in the form TYPE.METHOD, that is using dot notation.

A member method has associated with it a special parameter named SELF, which is the instance of the object type on which the method is invoked. For functions, SELF is by default IN only (the body of the function cannot change the values of an attribute of the instance). If a procedure, SELF is IN OUT by default, but that can be over-ridden as well (by explicitly specifying SELF as a parameter with the non-default parameter mode).

When you choose to test a member, each parameter of the member is displayed in the Input Grid, as occurs with a package-based subprogram.

If SELF is an IN or IN OUT parameter, each attribute of the object type is also displayed in the Input Grid, so that you can directly specify values for those attributes. Here, for example, is the Input Grid for the member procedure of the Quick Start object type, qctod#test_object_type:

Notice that each input's name is prefaced by "SELF" when it is the attribute of the instance.

If SELF is an OUT or IN OUT parameter, then Code Tester displays in the Outcome Grid a starting point of an outcome for each attribute of the object type. An outcome is also provided for the object type as a whole. You can remove any outcomes for which you do not need or want to provide a test. Here, for example, is the Input Grid for the member function, return_as_attr2_self_inout, of the Quick Start object type, qctod#test_object_type (it defines SELF as IN OUT):

Notice that each outcome's name is prefaced by "SELF" when it is the attribute of the instance.

Test MAP and ORDER Methods

MAP and ORDER methods are defined so that Oracle can perform native equality and inequality checks, as well as be able to compare two object types (as in, "Is object type 1 > object type 2?").

The MAP method returns a number. That number determines the relative "value" of the object type and is used in the comparisons.

The ORDER method returns one of three integer values: -1, 0 and 1. A 0 return value means that the two types are equal. Returning 1 means that the current (SELF) instance is greater than the instance passed as the ORDER method's only argument. Returning -1 indicates that the instance passed as the ORDER method's only argument is greater than the SELF instance.

To test a map method, you simply provide input values for each attribute of the type and then define the appropriate outcome on the number returned by the method.

To test an order method, Code Tester provides in the Input Grid one row for each of the attributes of the SELF instance, and one row for the ORDER's comparison object type.

Here, for example, is the default outcome grid for the ORDER member function of the Quick Start object type, qctod#test_object_type:

How to Test Object Type Instances

An instance of an object type is a variable (or, in the context of Code Tester, an expression) or a parameter whose datatype is the object type. You can test an object type instance by comparing one instance to another using an equality and inequality check. You can also provide values for each attribute and these will be compared individually.

Compare Object Type Instances

If the program you are testing returns an object type instance, or you define an Expression outcome and the type of the expression is an object type, then you will need to set up a test that compares the contents of an actual (changed by the program) object type instance and an expected instance.

Only two types of tests are defined for object type instances: equality and inequality, as you can see in this window from the Outcome Grid:

If the object type does not have a MAP or ORDER member defined in it, then Code Tester will test for equality by comparing each of the comparable attributes for equality. If the values of each of the attributes of the actual and expected instances are all the same (or both are NULL), then Code Tester records the test as a success. If the value of at least one attribute differs, the test has failed.

If the object type does have a MAP or ORDER member defined in it, then Code Tester will test for equality by calling that member function. In this case, success or failure is determined by the logic that the author of the instance has put into those functions.

Extending the Reach of Quest Code Tester for Oracle

The ultimate objective of Code Tester is to make it possible for PL/SQL developers to test their programs without having to write any code at all.

Version 1.9 takes Code Tester a big step closer to this objective, by making it possible to declaratively test the methods and attributes of an object type.

Say goodbye to hard-coding!

Steven Feuerstein — Mon, 01 Mar 2010 16:26:00 GMT

We all know that hard-coding is a bad thing in software. But most developers think of hard-coding simply as typing a literal value into your program whenever you need it.

BFScripts, Part 2

Kevin Dalton — Tue, 23 Feb 2010 14:28:00 GMT

In this blog, part 2 of a three part series, I will show you some ways BMF helps you in creating your BFScripts.

Code Coverage Analysis in Code Tester 1.9

QCTO Blog — Mon, 22 Feb 2010 07:00:00 GMT

Getting a happy green face after running your tests is, of course, very important. It is also important, however, to understand how much of the code in your program is being exercised by your tests. If, for example, you discovered that all your tests are successful, but only 10% of the lines of code in your program are actually run, you would have to conclude that either your tests are inadequate or you wrote a lot of code you don't need. Which of those two is more likely to be true?

The best way to gain a high level of confidence in your code is to complement your functional tests with code coverage analysis. Code Tester makes it easy for you to do that and even allows you to specify a target percentage of coverage against which your programs will be measured.

You can set your target or goal globally in the Preferences window:

You can also set different targets for specific program units. You might, for example, need a very high code coverage for certain key modules in your application.

Then in your test definition, you make sure that you have turned on the gathering of code coverage data:

Then when you run your test, Code Tester will automatically turn on the PL/SQL Profiler (DBMS_PROFILER) so that the raw data is generated by Oracle and then stored in Code Tester's repository.

After you have run your test, you can choose from a wide range of reports to view your code coverage information in detail:

It can't get much easier than this!

Watch a video demonstrating code coverage features in Code Tester 1.9.

Calling all Visual Studio users! (and those who want an Apple iPad!)

Daniel Norwood — Fri, 19 Feb 2010 22:54:00 GMT

I'm proud to announce that "ProjectFuze", the code name for Quest Software’s database schema provider (DSP) for Oracle in Visual Studio 2010, is now part of our Toad® solutions! Beginning with Toad for Oracle over 10 years ago, the Toad family of products is designed to help you do your job better, regardless of which platform you work on today - or tomorrow!

Code Tester Version 1.9 Now Available!

Steven Feuerstein — Thu, 18 Feb 2010 14:15:00 GMT

You can now download version 1.9 of Code Tester. This release features the following enhancements:

Object type support You can now test directly the methods of object types, test the contents of object type instances for equality and inequality, and specify the values of attributes through a grid.

Default value support You now request that the default of a parameter be used when testing your program. The generated test code will not pass a value for this argument.

Improved code coverage support You can now specify your code coverage target as a percentage, both as a preference (global target) and for specific program units. Actual code coverage % and target % values are now shown in Test Editor and code coverage reports. These reports have also been expanded.

Specifying order of execution of test suites You can now specify the order of execution of tests in a suite through the Dashboard (right-click on suite name, choose "Specify order of tests in suite").

Random test execution You can request through the Test Editor that unit tests and test cases within a unit test be executed in random order.

We have also cleaned up various parts of the user interface, most notably the Preferences Window, which has finally caught up with the rest of the tool (greatly expanded preferences, such as for export/import, as well as removal of old, unused preferences). All preferences are also now stored in their own preferences ini file, so that you can copy it to the workstations of other Code Tester users and easily apply a standard set of preferences to a development team.

If you have not yet checked out Code Tester, I urge you to do so. There is no better way to build and run regression tests that can finally break out of the vicious cycle of....

"I don't have enough time to do it right because I am too busy fixing bugs from the last release."

Download Quest Code Tester for Oracle v1.9

Automation - Date Ranges

Debbie Peabody — Wed, 17 Feb 2010 00:45:00 GMT

In this posting I'll show you what Toad for Data Analysts has to offer when using SQL Date Ranges

A Question from a user of SQL Optimizer 7.5

Richard To — Tue, 16 Feb 2010 19:25:00 GMT

Richard discusses a question from a user who tried our new SQL Optimizer 7.5.

How to add charts to your TOAD Report Manager reports

Gary Piper — Sat, 13 Feb 2010 19:42:00 GMT

Step by step instructions for creating TOAD Report Manager charts

It’s the Little Things, Re-Visited

Jeff Smith — Thu, 11 Feb 2010 08:00:00 GMT

Once again I was reminded how seemingly simple and common-sense features in Toad are frequently overlooked and yet also provide huge differentiation from other database tools out there. If this topic sounds familiar, it’s because I covered it back in 2008 for Toad for Data Analysts.

While doing a customer visit locally here in the Triangle at a local university, the audience I was presenting to was predominately using a competitor’s product. I won’t mention them by name, but let’s just say the audience was using the tool, but maybe saw room for improvement. The challenge thrown down to me was simple:

“Show us something compelling to think about moving to Toad”

I love the opportunity to wow people with Toad, and I had my entire favorite tricks lined-up and ready to go. Before starting though I asked a very important question – “What’s stressing you out lately in the database?” They thought for a minute, and then their manager talked about how recently they were frequently needing to take all of their foreign key constraints and triggers in several schemas and disabling them for some major data loads. Apparently this was a one item at a time task, e.g. select constraint, right-click > disable, go to next index, and repeat.

So with the table set, I knew I had them right where Toad wanted them!

The Toad Way

What you can do with one object in Toad, you can easily do for one or more. Let’s take care of the foreign keys first.

Open the Schema Browser to the Constraints tab
Set the filter appropriately
Multi-select and hit the ‘Disable’ button

This took me about 60 seconds to demo and honestly only took that long because I talk too much. I was not prepared for their reaction. You would have thought we had normalized their database to 4NF or something.

Apparently they had a hard time believing it should be this easy, because they insisted on seeing it done for the indexes as well. We had only used 1 out of our 90 minutes so I decided to humor them.

The full power of the Schema Browser is unleashed with multi-select and creative use of the filters.

Same basic process:

Go to the triggers tab
Set filter up appropriately
Multi-select, hit the button.

Same reaction, same surprised Jeff.

Apparently the tool they had been using didn’t allow you to perform actions against multiple objects in a single swoop. The filtering capability was also lacking so they weren’t able to get a list of objects from multiple schemas into a single list for performing their maintenance and data clean up tasks. These very two simple tasks that most Toad users take for granted were major items for this audience.

We ended up taking the full 90 minutes they gave us, but I think I could have stopped after the first 10 minutes and left with a completely satisfied and sold group of users.

Here’s a parting shot of operations you can launch against a list of selected tables in the browser:

Retrieving values from a record dynamically

Steven Feuerstein — Tue, 09 Feb 2010 19:19:00 GMT

Steven discusses generic retrieval/change challenges when it comes to PL/SQL records.

What's New In JProbe® 8.3?

John Pocknell — Sun, 07 Feb 2010 08:00:00 GMT

The next release of our popular enterprise-class Java profiler is about to hit the streets and we thought we'd give you a heads-up now to let you know what's coming!

The primary focus of this release is all about usability – right from installation and licensing to configuration ready for use. We've significantly revamped the whole user experience which should get you up-and-running with JProbe in a lot less time.

New JProbe Memory Analysis Welcome screen

Following a usability study conducted with JProbe users, a number of improvements were made particularly around the configuration of JProbe as well as the simplification of Run & Connect which are all accomplished from the main UI. In addition, if certain features are not available, we explain why and how to correct if applicable.

The new JProbe Configurations Welcome window

Simplifying configuration changes

There are a number of key pre-configured demos which are ready-to-run (3 for Java SE and 3 for Java EE) via a supplied installed version of Apache Geronimo 2.1.

The "Gamepack" Performance Analysis demo

Connecting the JProbe console to running sessions has been greatly simplified through the ability to scan for JProbe analysis engines running either on the local system or the local subnet.

Auto-attachment to running sessions

You may recall from release 8.2 that we introduced auto-instrumentation of the JDBC layer, allowing you to view SQL statements executed from the application which can then be tuned automatically using the SQL Optimizer for Oracle tool.

This is a significant addition to JProbe's capability because Java developers often have application performance problems which can be directly attributable to SQL execution performance on the Oracle database, yet are powerless to identify what they are, much less know how to deal with them.

For more information on SQL Optimizer for Oracle, please click this link: http://www.quest.com/sql-optimizer-for-oracle/

For information on a brand new package which combines JProbe and SQL Optimizer for Oracle, please click this link: http://www.quest.com/landing/?ID=4796. If you require a graphical interface into the Oracle database to let you write SQL statements automatically or work with Java in the Oracle database (Java Stored Procedures), look no further than Toad for Oracle - the world's leading Oracle development and database administration tool. http://www.quest.com/toad-for-oracle.

Information is available about another package which combines Toad for Oracle Xpert edition (inc SQL Optimizer for Oracle) and JProbe using the same link.

In 8.3, we've extended this capability to include individual tracking of prepared SQL statements and integration of SQL Optimizer for Oracle right in the UI (to save you having to copy and paste).

By default, the JDBC tab in the Summary view only shows Oracle DML statements (such as Select, Insert, Update and Delete). If you are running on the Windows OS and you have a copy of SQL Optimizer for Oracle installed, you will see a "Tune SQL Statement" menu option.

Executed SQL statements listed which can be tuned automatically using the SQL Optimizer for Oracle.

Licensing has been improved in 8.3, with auto-detection of the appropriate license key file – and you'll be pleased to know that no license key changes will be required with 8.3 being unlocked by any JProbe 8.x license key.

A new feature in the JProbe Performance profiler allows you to obtain basic memory information via an HPROF heap dump for later analysis without interruption of your performance analysis session. This feature requires a Sun 1.6 version JVM.

Creation of a JProbe Snapshot via HPROF

Several new command-line tools allow increased access to and control of JProbe sessions from a command-line environment. operating systems as well as

Finally, we made some essential platform changes such as support for Windows 7 and HP-UX (Note - 11i v3 for Itanium only) SpringSource dm Server 2.0.0 (on Windows), GlassFish 3.0 and Java Service Wrapper (often used with JBoss & Geronimo) application servers. You'll be pleased to know we've also restored support for BEA Weblogic Server 8.1.x .

The following IDEs are supported:

Eclipse 3.3, 3.4 and 3.5
MyEclipse 7.x

For customers preferring a physical shipment of the product, this is now available on DVD.

Well, I hope that's wetted you appetite and that you'll eagerly be waiting for the announcement that we've released JProbe 8.3.

On a final note, if you haven't used JProbe in a while and think it's the same tool you've used before; think again. This JProbe is a different user experience from any you've used before and is still the most complete Java profiling tool out there.

Just Bottom Line it for me, will ya?

Ben Boise — Fri, 05 Feb 2010 08:00:00 GMT

I have to giggle whenever I read the title of this entry. No, it’s not because I think I’m the King of Comedy (though, I do look pretty funny). The title came from a quote spoken by a former boss I had in a previous life. We were in a meeting with one of those co-workers who notoriously covers all the details of a topic, but rarely gets to the point. After about an hour of soaking in this increasingly irrelevant series of details, my boss finally said “just bottom line it for me .” The phrase became a favorite joke around the cafeteria table.

As I expand my understanding of DB2 LUW, I occasionally get stuck in the details. As users and creators of technology, it’s very easy to get so caught up in the details of what we’re doing that we lose sight of the proverbial “big picture.” As I was poking around in Toad for DB2, I noticed a screen I had previously ignored:

What you see, in the above screenshot, is the Database screen from the Database Explorer (requires the Database Administration configuration of Toad for DB2 LUW). I have spent a lot of time in the Database Explorer. How is it that I’ve overlooked this screen so frequently? Well, it’s because I’ve spent most of the time exploring other objects like schemas, tables, buffer pools; in other words, too much time mired in the details. What I enjoy about this screen is that I can use it to help me get back to the “big picture.” Each tab presents high-level information about the database you’ve selected. In the above screenshot, you can see the connection information about your current connection.

**The Applications Tab

The Applications tab shows me summary information about the currently connected applications in my database. When I select an application, the bottom portion of the screen will show more detailed information, such as Locks and current transactions the application is processing. In fact, you can right-mouse click on an application and Force it!

**The Space Usage tab

Now I can graphically view space allocation as well as use my right-mouse click to perform operations on the various tablespaces.

**The Monitor tab

Now I can view lock activity, buffer pool hit rates, connections, and SQL activity over time.

You can probably guess what the Utilities, Grants, and Script tabs are showing you. Don’t forget, you can right-mouse click on the database name, using the left-hand side of the Database Explorer, to perform additional operations on the database:

I feel really silly for having glossed over this jewel for so long.

Automation – Variables (Automation variables using datasets)

Debbie Peabody — Mon, 01 Feb 2010 06:07:00 GMT

In this post I’ll show you how to define and use automation variables to email a customized lettes to an email list.

Coming down the pipe, sneak peak at Toad for Oracle v10.5

Jeff Smith — Tue, 26 Jan 2010 15:20:00 GMT

A full preview is in the works, but sometimes a new feature is so compelling you just want to start telling strangers on the street about it right away. Rather than make you wait till tomorrow to hear all about Apple’s new tablet device, here’s some juicy Toad gossip right now!

DESCribe

Perhaps one of the most productivity-enhancing features in Toad, the DESC (F4) command allows the user to get access to all guts of any database object from the editor. Perhaps the best way to think of it is a miniature schema browser dedicated to a single object. You can have multiple DESC windows open at any given time, and again, Toad supports it for ANY object in the database – not just tables and views like SQL*Plus supports.

How it works now

From the editor, type in the name of the object, and hit F4.

So this works great now, how could it get any better?

You Ask, You Get

I frequently let customers know that one of the beauties of Toad is that it is a user-driven application. The bulk of the features developed and enhanced are directly influenced by our 2 million (and growing!) user community.

A few weeks ago someone in the Beta community suggested we make it possible to invoke the DESC functionality on demand while ANYWHERE in Toad. At the moment a user must open an editor and type the name of the object to get a DESC window. I’ve found myself several times doing just this. And it takes a few seconds and clutters up my Toad desktop. Surely there is a better way?

Now there is!

In Toad 10.5, you can invoke a DESC at anytime! Just hit CTRL+D (D for DESC) and you get this window:

In this example whilst looking at a list of objects my session has accessed, I wanted to know a bit more about DBMS_JOB. So I just hit CTRL+D and up pops the ‘Quick Describe’ window. From here I can just input the name of the object and let Toad do the rest.

This is admittedly NOT a new mind-blowing feature in terms of engineering or technical marvel. However, I’d argue this will fundamentally change the way I currently use the tool, and it will save me a few seconds of time every time I use it. When Toad can make my day easier or scream by just a bit quicker, then I’m a happy user.

Just a little more on v10.5

You can play with this version yourself, assuming you are current on your maintenance contract
It’s due early Spring 2010
We have LOTS of new toys, especially for our developer friends
Don’t worry DBAs, toys for you too including an Alert Log Viewer that’s bound to save you tons of time

Automation – Exception Reports (Using the Row Count variable)

Debbie Peabody — Wed, 20 Jan 2010 18:05:00 GMT

In this post I'll show how to produce an exception report using the row count variable and if..condition automation activity.

Automation - Getting Started with Toad for Data Analysts

Debbie Peabody — Fri, 15 Jan 2010 02:50:00 GMT

Scripting repetitive tasks can ease your work load. Here's how to do it in TDA.

How to create basic TOAD Report Manager templates

Gary Piper — Thu, 14 Jan 2010 20:23:00 GMT

A simple, step by step guide for creating TOAD Report Manager reports and templates (TRD -TOAD Report Definition format).

Continuous Integration

QCTO Blog — Wed, 13 Jan 2010 08:00:00 GMT

Written by Finn Ellebaek Nielsen. A short introduction to Continuous Integration (CI) and a description of how to integrate testing Oracle code with QCTO in a CI environment.

A New Toad® DBA Suite...for Oracle RAC

John Pocknell — Tue, 12 Jan 2010 08:00:00 GMT

Oracle Real Application Cluster (RAC) is a single database that is shared by multiple Oracle instances. It provides opportunities for improved database availability, performance and scalability. Oracle RAC lowers the TCO of a high-end database and IT assets, since a complex Oracle database can be set-up on low-cost, standard, modular parts. Oracle RAC has matured into a true enterprise, scalable solution, but the adoption curve and skill-set required to maintain RAC is relatively slow, as most organizations who have installed RAC only use it as a two-node cluster.

Introduced in 2008, Toad DBA Suite for Oracle provides a complete solution for DBAs using Oracle Standard Edition, in smaller shops where OEM is not being used or as a complementary solution for DBAs using Oracle Database Enterprise Edition and OEM.

With the Toad DBA Suite for Oracle, DBAs can improve operational efficiency to lower costs by formalizing database management through a systematic approach that assures maintenance, performance and change. The Toad DBA Suite for Oracle allows DBAs to simplify and automate common database administration tasks and become more proactive in solving database-related performance issues before end users are impacted.

Click here for a detailed assessment of Toad DBA Suite for Oracle.

Toad^® DBA Suite for Oracle - RAC Edition

The Toad DBA Suite for Oracle – RAC Edition is designed for DBAs and DBA managers to address user productivity and database performance, thus helping to reduce the total cost of ownership of a RAC implementation.

The Toad DBA Suite for Oracle - RAC Edition extends the current capabilities of Toad DBA Suite for Oracle, focuses on the database only and addresses three key areas of database administration:

Maintenance – managing users and user sessions, database health checks (including security as it relates to user Oracle accounts and permissions), task automation and scheduling, etc.
Performance Management – real-time performance diagnostics of RAC environments, database and SQL optimization
Change Management – configuration changes, schema changes, database workload replay testing and benchmark testing of RAC environments

Toad DBA Suite for Oracle - RAC Edition comprises the following components:

Toad for Oracle Xpert Edition (includes Quest SQL Optimizer for Oracle)
Benchmark Factory for Databases (Oracle Edition inc RAC)
Toad Data Modeler
Spotlight on RAC (seat-based license)
Spotlight on Windows
Spotlight on Unix/Linux

The main distinctions between this Suite and the regular Toad DBA Suite for Oracle are the upgraded Spotlight component which enables diagnostics of Oracle RAC and the leveraging of the RAC capabilities of Benchmark Factory for Databases.

There are additional enhancements planned for Toad for Oracle around management of a RAC environment, although there are many windows in Toad which already support RAC.

These include (but not limited to) the connections window, the window connection bars (which indicate connection to RAC), Oracle Parameters, Session Browser, Database Browser (which aggregates metrics across the cluster), Top Session Finder and Database Monitor.

Many of these windows look the same as they would if you were connected to a single instance, but collect data from the GV$ views and include the INST_ID column instead of the V$ views.

Let's start off with RAC performance diagnostics.

Spotlight on RAC's unique user interface provides you with an intuitive, visual representation of the activity on your cluster. Graphical flows illustrate the rate at which data is moving between database components. Icons display the value of key statistics and measurements (metrics).

The power of Spotlight on RAC lies in its ability to quickly isolate bottlenecks in the RAC cluster both at the instance or node level and the overall cluster level. Spotlight monitors at the cluster level because cluster performance will most directly affect the end user’s experience. However it is also important to monitor individual nodes to insure the cluster is operating at peak efficiency. Spotlight quickly tells the DBA:

How is each RAC node performing?
How is the cluster as a whole performing?
Is cluster-specific contention slowing down performance?

A range of drilldowns associated with the alerts provide detailed information to more easily diagnose the root cause of the problem. This information can be viewed on the screen , printed, or copied to a document.

Spotlight on RAC is delivered with best practice thresholds for performance metrics that provide advance warning as condition worsen. But these thresholds can easily be modified to suit your environment and your requirements.

Spotlight on RAC provides a complete view of your Oracle RAC architecture.

Spotlight on RAC showing a 4-node Oracle cluster

If a node on the cluster is experiencing a performance bottleneck (as node 4 is above), clicking on the node will launch Spotlight on Oracle to enable the DBA to quickly diagnose its root cause.

Now let's take a look at load testing your RAC environments.

Benchmark Factory addresses the two key goals of ensuring your RAC environment will perform well under production load - Scalability and Availability.

Scalability is a relative term, based on a simple rule that as the number of users accessing the system increases, the RAC configuration should be able to handle increased activity.
Availability is the ability of the system to provide continuous service when one or more of the components in the cluster fail.

Two important criteria used to achieve these goals focus on the cluster being able to load balance across all nodes, and when one or more nodes in the cluster fails, the users failover.

When a node in an Oracle RAC cluster fails, an Oracle database and the remaining nodes continue to run, providing uninterrupted service to users. Oracle RAC distributes database demands across several nodes, performing the function of load balancing. When more capacity is required, additional nodes can be added with minimal effort. Organizations spending time and money setting up a RAC cluster must ensure their systems have the stability to withstand user environment demands when adding nodes.

The Benchmark Factory for Databases component provides the means by which multi-user load (if necessary, thousands of concurrent users) can be applied to your RAC environment based on the well established industry-standard TPC-H and TPC-C benchmarks. Benchmark Factory validates Oracle RAC cluster scalability by using OLTP, database warehousing and mixed workloads. These tests are shipped with Benchmark Factory and a simple-to-use wizard walks you through the necessary steps to begin testing your RAC cluster.

Selecting Oracle RAC Scalability as the required test

The next step of the wizard establishes a connection to the RAC cluster running in a test environment and creates the database objects necessary for the scalability test to proceed. You are then asked to enter a scale factor (which will determine the amount of data to populate the tables) and the number of nodes you wish to test.

Selecting your scale factor

Defining the number of nodes to test

Benchmark Factory allows you to perform a "what-if" analysis to enable you to determine the optimum number of nodes to provide the best all-round performance in production, and, by using Spotlight on Oracle to monitor the test as it is running, the DBA is able to see at what point performance bottlenecks begin to appear.

Another method of testing your RAC environment is to capture and use actual application transactions from production. Benchmark Factory for Databases provides the means to import one or more Oracle Trace files to provide the means to load test the cluster. Oracle tracing can be turned on in either Toad for Oracle or Spotlight on Oracle and will include concurrency between sessions, session activity and latencies that include bind variables.

Benchmark Factory will generate a graphical report which clearly shows the optimal configuration to use.

Benchmark Factory report showing comparative cluster performance

Click here for more information on how to use the Benchmark Factory component to test your Oracle RAC environments, and in particular how to tune the interconnect.

Summary

Despite the advantages of implementing RAC in your environment, there are costs and risks associated with it and it's important to understand what the consequences of those risks are. Deploying RAC introduces additional complexities not present in a single instance environment, such as the interconnect, which requires high levels of DBA experience to effectively manage.

Due to these complexities, most organizations deploy a two-node RAC configuration because they don't know how to anticipate whether a different configuration might be better. For example, if you have 8 CPUs available across your server nodes, is it better to have 2 x 4 CPU nodes or 4 x 2 CPU nodes?

The Toad^® DBA Suite for Oracle - RAC Edition gives DBAs the confidence that they are deploying the optimum RAC configuration by enabling them to try out different CPU/node configurations in a test environment. By load testing different configurations in test and using Spotlight on RAC to assess performance in real-time, DBAs can make an informed decision on the best one to deploy in production.

Click here to view a White Paper on Load Testing and Monitoring Oracle Real Application Clusters.

Proactive Index Usage Analysis with Quest SQL Optimizer for DB2 LUW

Ben Boise — Wed, 06 Jan 2010 08:00:00 GMT

Using indexes can improve the performance of applications using the database. This is well known to many, many people. However, the application of too many indexes can have the reverse effect. There’s a balance that must be struck and you may find yourself needing to locate indexes that aren’t being used. If you’re already using the Quest SQL Optimizer for DB2 LUW, you can take advantage of the Index Usage Analyzer to help with this process.

Before we jump into this feature, I feel I should provide you with a little bit of background information. The Index Usage Analyzer identifies unused indexes by analyzing access plans and it is a wizard-driven process. While working through the wizard, you must specify which SQL statements you want to use for analysis. These SQL statements can be entered while working through the wizard or they can be selected from other features of the SQL Optimizer; specifically, the SQL Repository or the SQL Scanner. Let’s touch briefly on these other features.

The SQL Scanner extracts SQL statements embedded in database objects, captured from the DB2 Event Monitor, or stored in application source code and binary files. Once the SQL is extracted, access plans are retrieved and then classified according to a Problematic, Complex or Simple category, all without executing these statements. This is useful for locating potentially poor performing statements that may need to be optimized using the SQL Optimizer. You can access this feature using the Tools | SQL Scanner menu.

The SQL Repository stores statements that are used in the analysis of database performance. These statements could very well be ones you’ve deemed to be critically important. The repository is really a file that is located somewhere accessible to the workstation on which the SQL Optimizer is installed. You can manage the contents of the repository using the Tools | SQL Repository menu.

To start the Index Usage Analysis, select the Tools | Index Usage Analyzer menu. Now, select the Analysis | New Analysis menu. You’ll see the following screen:

** Please note you may have seen this screen when you first accessed the Index Usage Analyzer menu. Because I have previously run through this process, I chose the New Analysis menu option.

Click Next and you’ll see something similar to the following:

** I think it’s a good idea to provide a proper Name and Description for the analysis. “Analyzer1” with no description isn’t terribly, well, descriptive. Save you from yourself (maybe it’s just me that’s absent-minded).

Click Next:

** Use SQL captured in the Repository or SQL Scanner

Ok, now we’re coming to where we need to supply the SQL statements we want to use for this investigation. I’ve highlighted the dropdown list that is used to add SQL from the SQL Repository or SQL Scanner features I mentioned previously. You can also walk through another wizard-driven process and manually enter the SQL you want to investigate by clicking on the icon highlighted below:

** Use the above button to step through a wizard-driven process to manually add SQL

Make sure to check the box next to the SQL you want to use in this investigation and then click the Finish button. This starts the analysis process and when it’s complete, you’ll see a screen similar to the following:

We can use the tree to focus our analysis by looking at the tables used by the SQL we’re investigating as well as the SQL statements themselves. Selecting a node changes the information displayed in the right-hand pane.

** An example of a selected SQL node with the Index Used tab selected

** An example of a table node selected and examining the Unused Index

If I’m satisfied with the results, I can use a right-mouse click feature to actually drop an Unused Index. Naturally, I should use caution before dropping an index and take the appropriate steps to ensure this is the proper decision. If I’m examining the Unused Index tab, as shown below, I can right-mouse click on an index and select the Drop Index option:

I can examine my SQL statements, for unused indexes, by entering them or by selecting statements I’ve already captured in the SQL Scanner or SQL Repository. This is just another example of how many of the features in the Quest SQL Optimizer for DB2 LUW work together. Happy hunting!

Book Review: Toad Handbook, 2nd Edition

Jeff Smith — Thu, 31 Dec 2009 01:13:00 GMT

An exhaustive look at Toad, that doesn’t take that long to read and see immediate improvement in your day-to-day use of the tool. If you are new to Toad, this will shorten the learning curve. If you have been using Toad for 10 years, this will ensure you are not overlooking features or workflows that have snuck in while you weren’t looking.

Migrating your BMF Repository

Kuljit Sangha — Wed, 30 Dec 2009 23:39:00 GMT

This blog will give you the necessary steps to migrate your MySQL BMF repository to another database of your choice. The following databases are supported by BMF: Oracle, SQL Server, MySQL and Sybase. We will be using the Data Migration Wizard that is provided to us by BMF.

The first step is to decide which database platform you would like to do the migration for. Once that is decided, we can begin.

Click Next. On this next screen you will select the source of the repository. Most people will choose the BMF ODBC Connection since they are migrating from the default location.

Click Next. On the next screen, you will choose the database platform you would like to migrate the repository over to. However in order to select a database, you must create an ODBC connection to that database. If one does not already exist, click on the ODBC Administrator button. Once you select the ODBC connection, put in the necessary username/password to connect to the database. Beware that the BMF objects will be created into this account. If you prefer an independent BMF schema, then please create a new user ahead of time.

Once you have completed the above information and have clicked Next, you will come to a confirmation screen. This will indicate that everything has been setup correctly, and you are now ready to migrate the data.

Click Finish and the wizard will start the migration of your data.

After everything is completed, you can go into the target database to verify that the objects have been created successfully. They will be prefixed with ‘BF’.

TOAD Database Connections Problems

Bert Scalzo — Mon, 21 Dec 2009 20:07:00 GMT

One of the most common TOAD questions or problem areas is with connecting to remote and/or local databases. The process and requirements are actually quite simple – yet this remains a high frequency question that seems to cause remarkable confusion. And not just for new Oracle or new TOAD users, as sometimes even old pros can forget and ask. They just often do a Homer Simpson “Doh” once they hear the answer

Now there are literally dozens of scenarios that you might encounter, I am not going to show them all. Instead I’ll show you a common yet easy one to understand and troubleshoot. Then you simply need to apply your new “detective skills” to solve the other scenarios you might run into. Just remember, TOAD has two million+ users – and if we had a real connection problem we’d have heard about it and fixed it. Almost every time you get an unable to connect to database error message – it’s a local PC or possibly even a database server listener configuration problem. I’m not simply trying to pass the buck here. I’m just telling you that it’s the rare exception and not the rule – connection errors are most likely on your end, so investigate before just saying TOAD’s broken.

The most common scenario we run into is that people say “I can connect with Oracle SQL*Plus on my PC and TOAD gives an Oracle error – so TOAD does not work for me or my very special conditions. So here’s a screen snapshot of SQL*Plus working on my Windows XP PC.

OK – so SQL*Plus works on my PC. Now look at this screen snapshot – same PC yet TOAD won’t connect.

So let’s see what I did to get this wonderful Oracle error ORA-12154 (just one of the three to four error messages you’re likely to see while your SQL*Net environment or configuration is not right). Look at the first three solid arrows – I gave the correct user ID and password, plus the correct Oracle SID or instance ID. So why did I get this database connection error? Look at the crossed, fatter errors near the bottom right hand corner – these are you clues.

I have instructed TOAD to use the Oracle 11g client on my PC, and the red “X” next to the “TNSNames Editor” button indicates that there is no TNSNames.ora file. OK – but then why did SQL*Plus work? Look again back at the first screen snapshot. It appears that I have run SQL*Plus from Oracle 10g somehow? If I then do a right hand mouse on “MY Computer” on my desktop and choose properties, the Windows “System Properties” window will pop-up. Now if I next choose the “Advanced” tab and then press the “Environment Variables” button, the window in the next screen snapshot comes up. I finally double click the “PATH” so I can review and possibly edit my PATH environment variable – because here’s where the problem lies. Look at the screen a moment before reading on.

OK – my environment has the Oracle 10g home first in my PATH order. So when I run SQL*Plus it starts the binary from Oracle 10g and by default looks for the TNSNames.ora file under the same home – so it works because there is a TNSNames.ora file there. We know that for sure because as this next and final screen snapshot from TOAD shows, when I use the Oracle 10G home the red “X” turns to a green -> and the “TNSNames Editor” button indicates that there is a valid TNSNames.ora file. This attempt to connect using TOAD works – and works just fine.

So the key lesson or conclusion to take away from this blog is as follows: TOAD can connect to Oracle databases and it works 99.99% of the time. If you have a problem and get some Oracle connection error message, odds are that you have one of the following problems: a bad SQL*net install, are pointing to the wrong Oracle Home, have a Windows PATH environment ordering problem, or some other user setup and configuration problem related to your Windows and/or Oracle home.

So please apply this type of investigative process and thinking anytime that you cannot connect to your database with TOAD. TOAD could never have achieved such huge adoption and success if it could not connect to peoples’ databases. Thus the problem is most likely some minor little thing specific to your PC. Because in the last ten years I can count on one hand the real database connection problems that people have run into. It’s that rare.

How to reference IN argument values in outcomes

QCTO Blog — Mon, 21 Dec 2009 08:00:00 GMT

Sometimes you need to reference the value of an IN argument (an input, in Code Tester terminology) in your outcome. For example, under certain circumstances, the string returned by your function should be the same as the input value. Or perhaps the out value should be some part of the input value.

You could hard-code the same value you supplied in the Input Grid, but then if you change the input value, you have to change the outcome value as well. That's not a great solution.

Code Tester instead gives you two different ways to reference the input value indirectly:

Functions in the qu_result_xp package that accept the name of the argument and returns it value
Reference the name of the variable that Code Tester will declare and then assign the IN value to it before the program is run.

Let's take a look at both these techniques for a very simple example. Suppose I want to test this function:

FUNCTION first_three (string_in IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (string_in, 1, 3);
END;

I start up Test Builder and create a new test case. I enter "abcdefg" in the input grid for string. I will also open up the Properties Window for that input.

I see this:

Notice the variable name "i_STRING_IN." That is the name of the variable that will be used to store the input value and pass it to first_three. You can change this to anything you like, but the default is usually fine.

OK, now let's shift focus to the Outcome side of the test case. The function is supposed to return a string that is just a portion of the input value (first three characters). I could therefore create an outcome like this:

But this only proves that the correct value is returned for this one string ("abcdefg"). And, as noted above, if I ever want to change the input value, I must change the outcome value as well.

Instead of hard-coding the return value, I can open the Properties Window for the outcome's expected result:

and then in the "Expression of Query" field I can enter either of the following:

SUBSTR (i_String_in, 1, 3)

SUBSTR (qu_result_xp.argval_varchar2 ('STRING_IN'), 1, 3)

The first approach simply relies on the name of the input variable remaining "i_string_in." The second approach relies on the underlying API in the qu_result_xp package to look up the value of the IN argument with the specified name.

More generally, the API approach can be described as: when you want to get the value of an IN argument for a test case, you will call a program of this form:

qu_result_xp.argval_ < datatype > ('< argument_name >')

All of the following are currently supported:

FUNCTION argval_varchar2 (argument_name_in IN VARCHAR2) RETURN VARCHAR2;

FUNCTION argval_clob (argument_name_in IN VARCHAR2) RETURN CLOB;

FUNCTION argval_date (argument_name_in IN VARCHAR2) RETURN DATE;

FUNCTION argval_number (argument_name_in IN VARCHAR2) RETURN NUMBER;

FUNCTION argval_boolean (argument_name_in IN VARCHAR2) RETURN BOOLEAN;

FUNCTION argval_timestamp (argument_name_in IN VARCHAR2) RETURN TIMESTAMP;

FUNCTION argval_timestamp_tz (argument_name_in IN VARCHAR2)
   RETURN TIMESTAMP WITH TIME ZONE;

FUNCTION argval_timestamp_ltz (argument_name_in IN VARCHAR2)
   RETURN TIMESTAMP WITH LOCAL TIME ZONE;

FUNCTION argval_interval_ds (argument_name_in IN VARCHAR2)
   RETURN INTERVAL DAY TO SECOND;

When you pass the name of the argument, be sure to use a case that matches the actual argument name. In other words, unless the argument name is contained within double quotes, pass the argument name as UPPER CASE.

Another advantage of referencing the input value by name, rather than by the hard-coded value, is that you can use random values or lists of values to dynamically generate many different inputs (and therefore different test cases), all with the same/single outcome.

Consider the testing of first_three. So far I have built a single test case for a specific input value of "abcdefg". I am reassured that I get the right answer for that one value, but I'd feel lots more confident if I could "throw" lots of different strings at it and ensure that in all cases, the function returned the correct answer.

To do this, I can go back to the input for the test case and change the value from a single literal to a randomly generated list of, say, 100 strings with lengths varying from 10 to 32767:

And now every time I run my test, Code Tester will generate 100 new strings and run first_three for each. You see the partial results below:

I hope this gives you a clear understanding of how you can reference IN argument values by name, and make your test cases that much more flexible and useful.

Toad® for Oracle 10.1 Updates

John Pocknell — Thu, 17 Dec 2009 08:00:00 GMT

For those of you who haven’t yet upgraded to Toad 10, or were perhaps waiting until the first minor release, here’s a quick synopsis of what really characterizes Toad 10.

Toad for Oracle v10 offers new features and many enhancements to existing features, all of it designed to augment your workflow and to enable you to get your job done more productively, more accurately and with better visibility of the environment in which you work - whether you are a developer working in an unfamiliar database or a DBA trying to manage multiple databases.

Toad 10 is the beginning of a new generation of Toad releases which will offer significant improvements in productivity to developers, DBAs and analysts moving forward.

The following four statements characterize what this 10.0 release and subsequent releases will offer:

Usability – discover more of Toad’s features and ensure that the tasks you perform offer maximum productivity and follow a logical workflow
Automation – provide even more capabilities to maximize your productivity by creating and using automated workflow from regular tasks you perform in Toad
Education – discover a wide range of services related to Toad which will both extend your current knowledge of Toad and provide expert help when needed, thus helping to get maximum value from your investment in Toad
Flexibility – extend the use of Toad into regions where different character sets are used and provide a much simpler, coherent licensing mechanism

For more information about the Toad for Oracle 10.0 release, please read this Technical Brief published on Toad World.

So what’s been added in Toad 10.1?

The main purpose of this release was to provide official support for Oracle 11g R2. Oracle went GA with this release after the Toad 10 code was frozen. We’ve now completed our compatibility checks, and Oracle 11g R2 support is included in this release as well as all of the other products included in the Toad for Oracle suites such as SQL Optimizer and Spotlight on Oracle.

In addition, we wanted to make sure we supported Windows 7 (which is becoming a really popular platform) including 64-bit as well as Windows 2008 Server (inc 64-bit).

Well, do you want the good news or the REALLY good news?!

OK – how about both?

Firstly, for this 10.1 release, we are providing Toad Data Modeler, our cross-platform physical and logical data modeling tool for free across ALL editions of Toad for Oracle.

That means, whether you have Toad for Oracle Base Edition or Toad Xpert Edition with DB Admin, you can upgrade to version 10.1 and use Toad Data Modeler for FREE!! (requires a current maintenance contract).

Or, look at it another way. Toad for Oracle now takes you beyond Oracle and provides three tools for the price of one:

Toad for Oracle
Toad for Data Analysts (cross-platform querying and reporting tool)
Toad Data Modeler (cross-platform physical and logical modeling tool)

In addition, if you have a copy of Toad DBA Suite for Oracle (or you are considering upgrading from a lower edition), we now provide you with even greater value.

The Spotlight for Oracle component which provides DBAs with essential real-time performance diagnostics has been upgraded to include the following ADDITIONAL components and features:

Spotlight on Unix/Linux (formerly a chargeable product)
Spotlight on Windows (formerly a chargeable product)
Predictive Diagnostics (previously only available in a higher Spotlight edition)

For more information on Spotlight on Oracle, visit http://www.quest.com/spotlight-on-oracle/.

For more information on Toad for Data Analysts, visit www.quest.com/toad-for-data-analysts or visit the Toad for Data Analysts Community at http://tda.inside.quest.com .

For more information on Toad Data Modeler, visit www.quest.com/toad-data-modeler or visit the Toad for Oracle Community at http://modeling.inside.quest.com .

Monitoring your Unix/Linux server using Spotlight on Unix

Monitoring your Windows server using Spotlight on Windows

Predictive Diagnostics enables an assessment of what performance problems you might expect in the future based on a user-definable profile of what is happening today in order that DBAs can pro-actively plan an appropriate strategy.

This will enable DBAs to adopt and apply a PREDICT – DIAGNOSE – PREVENT methodology to performance management which will prove effective against future performance degradation.

Using Predictive Diagnostics to determine the future impact of IO growth

Oh, and did I mention the new online Knowledge Xperts?

Originally available as add-in components and then integral components of Toad for Oracle, Knowledge Xpert for PL/SQL Developers and Knowledge Xpert for Oracle Administration have been associated with Toad for many years. Today, both Knowledge Xperts are included with Toad for Oracle from Base Edition and higher.

Coincident with the Toad for Oracle 10.1 release, we are pleased to announce that both Knowledge Xperts are now available online via Toad World at no cost.

What makes these Knowledge Xperts unique is that, due to the nature of a community like Toad World, you can contribute by adding comments, rating your favorite topics and more.

Knowledge Xperts support Oracle versions 7.3.4 through to 11g and will continue to be updated with new Oracle releases to ensure you have access to all the very latest information about Oracle whether you are a developer or DBA.

And, don’t forget, this is all searchable from the new Search Bar in Toad 10.

Online Knowledge Xperts hosted by Toad World

It MUST be coming up to Christmas!!

Toad for Oracle Beta Program

The Toad for Oracle beta program enables Toad users who are on the full commercial version to perform a side-by-side install of the beta for testing, and providing the Toad development team with direct feedback. New beta programs follow shortly after a new release.

The Toad Beta community is hosted by the Toad development team themselves so they can provide instant dialogue with beta testers which means any tweaks can literally be performed in minutes.

The very latest Toad for Oracle 10.5 Beta has been posted with exciting new features and enhancements so be sure to give it a try and let us know what you think (you can install and use the Toad Beta side-by-side with your existing Toad provided you have a current commercial license)

If you are interested in participating, please click this link: http://www.toadfororacle.com/beta.jspa

Toad for DB2 LUW and Spotlight on DB2 LUW play well together

Ben Boise — Tue, 15 Dec 2009 08:00:00 GMT

Did you know that you can call Spotlight on DB2 LUW from Toad for DB2 LUW and vice versa? Well, ok maybe you did. If that’s the case, please move along, there’s nothing to see here. Still with me? Then read on!

Toad for DB2 LUW and Spotlight on DB2 LUW exist as separate executables on your workstation. You might now be thinking “no kidding, Ben.” That’s fine. After all, I have been called the Master of the Obvious. However, we may find ourselves working in one solution and then realize we have the need to work in the other solution. Naturally, we can just launch the solution of interest from our programs menu. However, let’s consider saving ourselves a little bit of time and launching them within some proper context. Let’s say I’m working in Toad for DB2 and have the need to do additional diagnostics using Spotlight. Rather than launching Spotlight, selecting the proper database connection and then connecting, I can simply use the Tools -> Quest Tools -> Spotlight on DB2… menu within Toad:

Spotlight on DB2 LUW then loads and creates a new Spotlight connection using the connection’s information from Toad for DB2!

Let’s consider the opposite approach. I may be diagnosing an issue in Spotlight on DB2 LUW and realize that I need to perform an operation within Toad. In this example, I’m working in the Tablespace Analysis drilldown in Spotlight. If I right-mouse click on a tablespace, a window loads that allows me to operate on the object using Toad:

If I choose the Alter… option, Toad for DB2 loads, connects to the database I was diagnosing in Spotlight, and then loads the appropriate menu for Altering this tablespace:

I encourage you to use the solutions in a manner that is most comfortable to you. With the integration that exists between Spotlight on DB2 LUW and Toad for DB2 LUW, I can save myself time and use these solutions within a relevant context. Just a little something else to think about!

Really Cool 'TOAD-Like' Freeware

Bert Scalzo — Mon, 14 Dec 2009 14:42:00 GMT

We all love TOAD – both the commercial and freeware versions. We love it because it’s simply the best Oracle productivity enhancing tool out there. A few weeks back, I mentioned the many other freeware offerings from Quest. So this week I thought I’d mention a few other freeware offerings that serve me nearly as much as TOAD – just in different functional areas. Here are my top five.

Importing and exporting TOAD Report Manager Reports

Gary Piper — Mon, 14 Dec 2009 00:10:00 GMT

Step by step instructions for importing and exporting TOAD Report Manager reports (TRD -TOAD Report Definition format).

Why Toad?

Jeff Smith — Wed, 09 Dec 2009 14:39:00 GMT

Toad for Oracle is a world-class enterprise query-develop-tune-administration tool for the Oracle database platform. This document attempts to differentiate Toad from the average Oracle IDE or query tool so that the reader can make an informed decision to invest in the Toad product family.

Following is a list of features and concepts that most commercial and even freeware offerings strive to deliver. Each feature will demonstrate clearly how Toad rises above the competition. If you can live with only the lowest-common-denominator features, then Toad may not be an easy sell. If, however, you need a tool to rescue from hairy situations, tune your systems, and automate those painful daily tasks, then Toad should be a clear winner.

Download Jeff's Toad Tech Brief: Why Toad? (PDF document, 1.2MB)

Execute TOAD Scripts on Multiple Databases

Bert Scalzo — Mon, 07 Dec 2009 02:33:00 GMT

Many TOAD users ask “How can I run my SQL script against a collection or even all my databases all at once (i.e. in a single step)?” The problem is they generally ask this question while in the Editor – which by definition works with but a single active database connection at a time. But if you simply save the editor contents to a SQL script file – you can then use the TOAD Script Manager to execute that script against any number of databases at the push of a button. The BERT.SQL script highlighted below will create a DBA privileged account. Since Script Manager’s connection portion (top half of screen) shows three connections, when I press the toolbar “Execute” button (i.e. lightning bolt), this script will be run against my local 10g database, a remote server’s 10g database, and a remote servers 11g database. It does not get any easier than that. So please look into adopting the TOAD Script Manager amongst the features you routinely use.

Quest Database Freeware

Bert Scalzo — Mon, 30 Nov 2009 13:26:00 GMT

Often database professionals need good freeware tools to get their job done. Sometimes it’s because there’s no funding for software. And in some cases a tool is needed to replace the purchased tool that ends up not being able to do the job. Finally in today’s world of open source, some people just hate to pay for tools – even if there is budget and/or the job clearly could benefit from commercial quality tools.

For all those people, Quest Software has a lot to offer – with all the following freeware tools that might suffice when free is a must or preferred.

The freeware tools are generally the same as our commercial products – but with a few minor features turned off or limited in their functionality. Here are a couple examples of our freeware differences:

Binding sparse arrays in a FORALL statement

Steven Feuerstein — Mon, 23 Nov 2009 15:44:00 GMT

FORALL was introduced into PL/SQL in Version 8i. It is a fantastic feature; you should use it in place of all loops that contain DML statements performing row-by-row processing. You will generally see performance improvements of at least an order of magnitude.

If you are not familiar with FORALL, make it a priority to get familiar. You can start with my brand new 5^th edition of Oracle PL/SQL Programming, complemented by the Oracle documentation. Follow up with a perusal of my 21^st Century PL/SQL course materials.

I assume a working knowledge of FORALL for the remainder of this post.

The typical (and, in Oracle8i and Oracle9i, the only) way to construct a FORALL statement is to use a header that is very similar to a numeric FOR loop. Here is an example:

DECLARE
   TYPE namelist_t IS TABLE OF employees.first_name%TYPE;

   l_enames   namelist_t := namelist_t ('ABC', 'DEF', 'SMITHIE');
BEGIN
   FORALL indx IN 1 .. l_enames.COUNT
      UPDATE employees
         SET first_name = l_enames (indx);
END;

More generally, the FORALL header looks like this:

FORALL integer_index IN low_value .. high_value
where integer_index is an implicitly declared integer iterator, low_value is the low end of the integer range and high_value is the high end. Each integer between low and high must reference a defined index value in any of the collections that are bound into the FORALL's DML statement (in the above case, there is just one: l_enames).

In other words, the binding array must be densely filled between the low and high values.

If the collection is sparse and the FORALL statement tries to read an element at an undefined element, it will raise an exception as shown below:

Notice that it does not raise NO_DATA_FOUND. I point this out because it seems like Oracle goes out of its way to raise this exception whenever the error bears even the remotest resemblance to "I tried to get something and it wasn't there" (SELECT INTO, read an element in a collection at an undefined index value, read past the end of a file).

Notice also that even if I include the SAVE EXCEPTIONS clause in my FORALL statement, this exception is not "saved" to the SQL%BULK_EXCEPTIONS pseudo-collection. That's because this is a "meta-error" for the FORALL statement. The exception is not raised by the SQL engine as it performs the DML statement. It is, instead, raised by the PL/SQL engine before the SQL statements are passed along to the SQL engine.

So what's a programmer to do if your binding array is not densely-filled?

In Oracle10g Release 2, Oracle added support for two new ways to construct the FORALL header: INDICES OF and VALUES OF. With either of these, your FORALL header no longer looks like a numeric FOR loop. Instead you specify that you want the FORALL statement to reference only those elements in the binding array whose index value is defined in the INDICES OF array.

The simplest form uses the same collection as the binding and INDICES OF arrays, as in:

   FORALL indx IN INDICES OF l_enames
      UPDATE employees SET first_name = l_enames (indx);

Now, an update statement will be sent to the PL/SQL engine only for this index values that are defined in the collection.

But the collection referenced in the INDICES OF clause need not be the same as the binding array. Here is an example of using INDICES OF with a distinct collection:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE boolean_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_employee_indices   boolean_aat;
BEGIN
   l_employees (1) := 137;
   l_employees (100) := 126;
   l_employees (500) := 147;
   
   l_employee_indices (1) := FALSE;
   l_employee_indices (500) := TRUE;
   l_employee_indices (799) := NULL;

   FORALL l_index IN INDICES OF l_employee_indices 
                  BETWEEN 1 AND 500
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;

Updates will be run only for employee IDs 137 and 147.

The VALUES_OF clause offers an additional layer of "indirection." With VALUES_OF, the FORALL statement executes a DML statement for each index value in the binding array that is an element in (not an index value in) the VALUES OF collection.

Here is an example:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE indices_aat IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   l_employee_indices   indices_aat;
BEGIN
   l_employees (-77) := 134;
   l_employees (13067) := 123;
   l_employees (99999999) := 147;
   l_employees (1070) := 429;
   
   l_employee_indices (100) := -77;
   l_employee_indices (200) := 13067;
   l_employee_indices (300) := 1070;
   
   FORALL l_index IN VALUES OF l_employee_indices
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;

In this block of code, an UPDATE is executed for employee IDs134, 123 and 429.

So if you are using FORALL but running into situations where your binding array may not be sequentially filled, give INDICES OF or VALUES_OF a try. It might simplify your life and code dramatically.

Investigating and Managing Differences with SQL Navigator

Ben Boise — Mon, 23 Nov 2009 08:00:00 GMT

Quest Software has two of the most powerful tools for PL/SQL development: Toad for Oracle and SQL Navigator for Oracle. Personally, I spend more time working with Toad for Oracle and I truly enjoy the solution. Before I proceed any further, I want to clarify a point. I am not advocating one solution over the other for PL/SQL development. That’s a conversation I refuse to have, because it borders on arguing which religion is better. I’m of the opinion that you should use the solution you’re most comfortable using and that helps you get your job done well.

Recently, I went to visit some customers who are using SQL Navigator for Oracle. Well, because I spend most of my time with Toad for Oracle, I found my SQL Navigator skills to be a bit rusty. In preparation for our meetings, I spent some time refamiliarizing myself with this solution. During this preparation, I discovered a feature that I wanted to share with you. Granted, I’m running the risk of exposing a feature you’re already aware of, but that’s the chance I take .

SQL Navigator for Oracle has a utility that allows you to compare both script differences and object differences. You can launch the comparison process in a few ways (imagine that). We’ll focus on using the menus within SQL Navigator for Oracle. All you have to do is select Tools | View Difference:

Selecting this menu loads the following dialog:

As you can see, we have the option of selecting Script or Object. The Script option allows us to compare a variety of file types. If you click the ellipses, you can activate the Files of Type dropdown to see the file formats that are supported for comparison purposes. I’m going to take us through an example of using the Object comparison.

The interface is very straight-forward. Simply select the Object radio button and fill out the proper objects you wish to compare from the various dropdowns. You’ll see something similar to the below screenshot:

**Please note that I am comparing the OW_CUSTOMERS table in my QUEST_DEV schema to the OW_CUSTOMERS table in my QUEST_PROD schema. My connection provides me access to both of these schemas; hence, the Session information is identical in both dropdowns.

You guess it. Now all we do is click the OK button and we have the following results:

You can see that we have a column size difference between these two schemas. This is indicated by the blue syntax in the above screenshot (line #3).

In my opinion, this is where it gets good. If I select the line that is different (line #3), you’ll notice a change in the toolbar:

The eraser icons give us the ability to Delete left text or to Delete right text. The arrow icons allow us to Copy to right or Copy to left. What does this mean to us? Using these icons we can now react to these differences.

Let’s say that we need to make our QUEST_PROD table look like our QUEST_DEV table. Our QUEST_PROD table’s NAME column is 64 bytes and we need it to be 70 bytes. Using the Copy to right arrow (the arrow pointing to the right), we can start to make this change:

Now, line #3 is identical on both the left-hand and right-hand sides of the screen. If you click on the floppy disk icon, on the toolbar, you’ll be prompted to save the changes as a script.

Icon to click:

Dialog to save changes:

Now that we’ve saved our changes, we can very easily load the script into the editor and execute it to apply our changes. All we have to do is click on the SQL icon in the toolbar:

This loads our script into SQL Navigator for Oracle’s editor:

We can now execute this script to apply our desired changes!

Investigating differences is very easy in SQL Navigator for Oracle. Perhaps equally important is the fact that we can react to these differences in a safe and logical manner. I find that I’m sometimes my worst enemy when it comes to managing changes. SQL Navigator for Oracle helps save me from myself.

New JProbe command-line tools for memory dump analysis and for JProbe analysis engine control

Stuart Hodgins — Thu, 19 Nov 2009 22:01:00 GMT

We are pleased to provide two new command-line utilities that were developed for the next version of JProbe. The first can be used standalone to obtain basic memory usage reports from JVM memory dumps. The second can be used for controlling a JProbe Analysis Engine in a headless environment or for scripting JProbe actions.

SQL Best Practice is SQL Tuning? -10 Common Misconceptions in SQL Tuning #5

Richard To — Wed, 18 Nov 2009 17:55:00 GMT

This blog is the fifth in a series about misconceptions surrounding SQL tuning that are quite common.

Around 15 years ago, when I was a DBA both for Oracle and Sybase, a lot of SQL came from my colleagues which I needed to tune. Initially, I enjoyed the satisfaction of tuning a SQL statement that ran in a few minutes down to a few seconds. But once SQL tuning became my daily job, it wasn’t fun anymore. I started to search for any tool in the market which could help, but I found that most of the SQL tuning tools only displayed the execution plan. Some provided best practices or recommendations, but these kind of tools actually did not help me to solve numerous SQL tuning problems, since most of the time the problematic SQL syntax was okay and all the indexes were properly built. The major problem for SQL tuning is how to help the database optimizer choose a better execution plan. To be frank, Oracle and Sybase do a good job of picking a good execution plan most of time. I think there will not be more than 5% of the SQL that needs our attention, normally most human mistakes normally can be eliminated during unit test or stress test. For example, a Cartesian join SQL which is missing the basic relationship between two tables in a production database is a very odd case which I have never seen in my life in last 20 years working experience with hundreds of programmers. But it is interesting that this case is always used as a demo case in many SQL tuning products. I don’t know what company would let this kind of SQL statement be executed in a production database without even one unit test on their programs, and then think that it needs to pay hundreds of dollars for a SQL tuning tool to solve this problem. I think anyone in the company who knows SQL coding could fix this problem within a minute!

It is common that a lot of best practices in today’s SQL tuning tools available in the market are built for demonstration only. There are not many cases where these so-called best practices can be applied in a real life situation.

Some Best Practices Discussion

I will discuss some common best practices that I learn from SQL tuning products, market experts, or Oracle manuals.

Best Practices Fallacy - Use UNION ALL instead of UNION

It is quite common to see tuning tools keep recommending to users to use UNION ALL instead of UNION as a best practice. Once those tools detect a UNION key word in a SQL statement, it will give you advice to change the UNION to the UNION ALL operation. I have often wondered if those tool designers really understand the risk of giving this recommendation. Do they really understand their target users’ knowledge? They are two different, although similar, operations and naturally these two operations have different meanings. The result will be equal only in a certain data set. UNION eliminates all duplicated records from a SELECT list. But UNION ALL returns all records without the elimination process, so it runs faster than UNION. If their users are knowledgeable enough and know without a doubt that UNION ALL is the same as UNION in their environment, they may not need these kinds of tools to help them to change UNION to UNION ALL. But the problem is that those users who don’t have enough knowledge to distinguish the difference between these two operations and trust that the recommendation is good in all circumstances, the result may be disastrous, since the problem sometimes will not be discovered in a development database with a small data set during unit test, but it will definitely create problems in their production database. You can imagine what a nightmare it could cause for an online trading system with a million transactions per day.

Best Practices Fallacy - Rewriting the SQL in PL/SQL code

Rewriting the PL/SQL code instead of tuning a SQL statement is one of the best practices that I am always against, unless you really understand what you are doing and know that the database SQL optimizer cannot achieve your performance expectation. In no other case do I think that a query can only be improved by rewriting the PL/SQL, and that it cannot be improved by normal SQL tuning techniques such as Oracle optimizer hints application, new indexes, a materialized view or SQL syntax rewrite. It is a misconception with people who have some sort of experience with rewriting their problematic SQL with PL/SQL code and have gained some performance improvement. The point is that what they are doing is just creating a new “data process flow” or “execution plan” to only process their SQL, when actually, most of time, they can achieve the performance improvement by using normal SQL tuning techniques. Furthermore, the PL/SQL code will introduce extra PL/SQL process and looping overheads to the database engine. Let’s use the following simple join SQL statement for discussion:

Original SQL

SELECT  *
  FROM employee,department
 WHERE emp_dept=dpt_id
   AND dpt_id<‘ACC’

If you place the above SQL into PL/SQL, probably you will start with an Open Cursor for Department with a filter dpt_id<‘ACC’ and then loop the following SQL until the end of the Department cursor.

SQL used in PL/SQL

SELECT *
 FROM employee
 WHERE emp_dept= var_dpt_id

Basically, this PL/SQL code is a typical Nested Loop operation with a driving path from Department to Employee. If Oracle failed to use a Nested Loop for the original SQL and a Nested Loop is a better plan in your environment, you will gain performance improvement for this PL/SQL, but actually, the same improvement can be more easily achieved by adding the USE_NL hint to the original SQL. I wonder how many PL/SQL programmers can use PL/SQL code to achieve Hash Join or Sort Merge Join; if these Hash or Sort Merge Join is better for the SQL. Why not let Oracle to do that for you; it is the reason we pay for the RDBMS.

Factoring Out Subqueries with Temporary Tables or WITH Clause

It is quite common that people will tell you to factor out subqueries with a Temp table or a WITH clause like the following SQL:

Original SQL

SELECT emp_id 
 FROM employee, 
       department 
 WHERE emp_id IN (SELECT emp_id 
                    FROM emp_small 
                   WHERE emp_dept < 'D') 
   AND dpt_manager IN (SELECT emp_id 
                         FROM emp_small 
                        WHERE emp_dept < 'D') 
   AND dpt_id = emp_dept

We can extract these two subqueries and transform them to a WITH clause like the following:

Best Practice SQL

WITH temp_emp AS (SELECT emp_id empid 
                    FROM emp_small 
                   WHERE emp_dept < 'D') 
SELECT emp_id 
 FROM employee, 
       department 
 WHERE emp_id IN (SELECT empid 
                    FROM temp_emp) 
   AND dpt_manager IN (SELECT empid 
                         FROM temp_emp) 
   AND dpt_id = emp_dept

Most people may think that this SQL looks very nice and should be encouraged as a best practice to any programmer and should not use the Original SQL syntax anymore, based on Original SQL syntax, you may interpret that the subquery (SELECT emp_id empid FROM emp_small WHERE emp_dept < 'D') should not be processed twice, so the second “Best Practice SQL” should run faster than the “Original SQL”. But the fact is that the “Original SQL” is almost 50 times faster than the “Best Practice SQL” since using a Temp table or WITH clause may save you time by not executing the factored subquery multiple times. But in reality, the Temp table or WITH clause introduces a temp table or an internal temp table (please refer to the plan in the following screen shot) into the execution plan, and this prevents the database SQL optimizer from merging those subqueries into the main query and limits the number of plans the SQL optimizer can generate during optimization. I am not saying you should or should not use this best practice, all that I am saying is that this example is used to show you that every environment is unique, sometimes this Best Practice is good, but sometimes it is bad. There is no common best practice that always works to improve performance.

Actually, SQL tuning is not using “SQL best practices” that follow generalized rules. It is the techniques that improve the SQL speed in a given hardware environment and a unique database configuration with a specific data distribution. Sometimes the best performance results may violate all the best practices that you have heard about, since your environment is always unique from everyone else’s.

The true best practice is “Don’t adapt those so-called best practices blindly!”

The Subtleties of Programming

Steven Feuerstein — Mon, 16 Nov 2009 15:54:00 GMT

I recently published the following puzzle in the ToadWorld newsletter:

Which of the following queries return the names of programs (without duplication) defined in the currently connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?

A.   SELECT name FROM USER_DEPENDENCIES
WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE'

B.   SELECT name from ALL_SOURCE
WHERE owner = USER
AND name = 'DBMS_OUTPUT.PUT_LINE'

C.   SELECT DISTINCT name from ALL_SOURCE
    WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0

D.   SELECT DISTINCT name from USER_SOURCE
WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0

I believed that (D) was correct. Certainly, A, B and C do not do the trick (click here to see my explanation). But I recently received the following email from Wim de Lange of the Netherlands:

"None of the answers below is correct. (D) could be the solution, but DBMS_OUTPUT.PUT_LINE could be spelled as DBMS_OUTPUT . PUT_LINE (spaces around the dot) and that code will still compile and run. So the spaces are destroying the solution here."

Wim is absolutely correct. You could put spaces between the package name, dot and subprogram name, and the PL/SQL compiler will have no trouble with that code.

Thanks, Wim, for your very close reading of my puzzle, and for reminding me that programming can be a very subtle and nuanced craft. Oh, and also that it is so difficult to come up with all the test cases for a program.

And just to show you that I am not the least bit upset about someone finding an error in my code and writing, Wim is hereby declared another winner of that contest and will receive a Toad World t-shirt in addition to the randomly selected four winners.

Data Warehousing Book Review

Bert Scalzo — Mon, 16 Nov 2009 13:46:00 GMT

It’s been a while since I wrote my data warehousing book, so I wanted to read something hip and new on the topic. Lucky for me someone suggested that I examine “Oracle Warehouse Builder 11g: Getting Started” by Bob Griesemer from PACKT Publishing. While I’ve in the past worked mostly with custom Extract, Transform and Load (ETL) applications, for many people the better alternative might be to use tools written specifically for that purpose – such as Oracle Warehouse Builder (OWB). And as with most pre-canned software, people often need an instruction manual and reference guide. Although Oracle does a bang up job on documentation these days, I and many others still like to have a third party book on the tools we use most. That’s why for TOAD users I just updated the” TOAD Handbook (2^nd Edition)”.

Since OWB has evolved into a mature and robust ETL offering and since I work mostly with the Oracle database, OWB and this book seemed like a natural pairing for Oracle Professionals working on data warehousing projects. As I began reading this book, I considered myself a data warehousing literate person with very weak Oracle Warehouse Builder experience. So the book’s “Getting Started” aspect appealed to me (because I hate when books I read are miles above my head and make me feel dumb).

“Oracle Warehouse Builder 11g: Getting Started” has nine chapters. The first four provide a superb foundation explaining everything one needs to know about data warehouses while introducing the fundamental concepts of the tool. As an OWB neophyte, I found these chapters as being critical to comprehend and appreciate the true scope and power of this ETL tool. Yet as a data warehousing literate person I did not find the foundational knowledge as detracting from the book as it’s so well woven into the material. So I believe anyone from data warehouse novice to Oracle ACE can benefit immensely from this book while feeling neither over nor underwhelmed. The second four chapters then coalesce these fundamental data warehousing concepts and OWB skills into true workflows necessary to design, build and populate a data warehouse. The ninth chapter then ties up any loose ends.

Throughout the book the screen snapshots are extremely well done – being very easy to read (i.e. not too small) and with the correct ratio of snapshots to text. The author has also done an outstanding job of placing the right mix of warnings or tips and tricks all through the book. Plus I found the writing style easy to read. Thus the book was a pleasure to read. If you’re working on an Oracle data warehousing project this book could well be a contributor to your success. And it makes a fine reference guide for OWB for your bookshelf. Check it out.

De-cluttering Your Toad Desktop

Jeff Smith — Wed, 11 Nov 2009 15:42:00 GMT

Quick, what’s the number one complaint about Toad for Oracle?

“The User Interface is waaaaaaaaay too complicated!”

Out of the box, Toad’s setup to pretty much have everything enabled. If we hid stuff, there’s a good chance users would assume the functionality just wasn’t there to be taken advantage of. And, as John so elegantly put it in his latest whitepaper on QuestDotCom, it’s easy for a tool to be elegant when the tool only does one or two things.

Toad does just about everything, so it’s likely the features you do not need or want may get in your way. Fortunately there’s a very easy solution to this: turn off or disable the features you do not use!

In about 5 minutes I setup this Toad desktop:

I disabled most of the toolbars
I removed buttons from the remaining toolbars I don’t ever use
I converted most of the buttons from esoteric graphics to just plain text buttons like so

vs

I closed the desktop panels I never use
- SQL Recall (I use the keyboard shortcuts instead, faster plus less real estate)
- Object Palette (I’m faster on the keyboard with F4 and Ctr+Period)
In the Editor, I turned off the output panels I use infrequently and limited it to the Data Grid, Explain Plan, Script Output, and DBMS_Output

Moving Into the Schema Browser

There is a tremendous amount of information Oracle has to share with the Toad user. We can turn most of it off very quickly!

Schema List – use the Session > Oracle User List dialog to hide schemas you don’t support or interact with, makes the dropdowns a LOT more navigable
Use the DropDown view for the objects – most amount of real estate.
Turn off support of the Object types you don’t work with
Turn off the right-hand-side tabs for each object you don’t work with
Filter, filter, filter!

Remember, if there’s something in Toad you do not like, you can turn it off!

If you decide you made a mistake, and you want to get it back, you can always reset your Toad to look like it did on day one.

Data Warehousing Tools

Bert Scalzo — Mon, 09 Nov 2009 14:05:00 GMT

Many people these days have to work on both traditional “online transactional processing” (i.e. OLTP) systems as well as data warehouses. But just what is a data warehouse? Generally speaking a data warehouse is nothing more than a specialized business reporting system used by mid to senior level management to research and develop a tactical plan and/or true long term strategy. Thus in many cases while these systems may be considered mission critical, they are not really true 24 X 7 systems. Often data warehouses are utilized during normal to extended business hours, with some batch activities for data loading and aggregation/summary calculations.

So what tools does one need for such an environment? In short, you really don’t need many new or specialized tools because a data warehouse is essentially just another database. It’s true that the end users are a little different, as are their reporting needs. But simply think of data warehouses as just a specialized reporting database. And you’ll see that many tools are really the same. Let’s examine the data warehouse development life cycle and what tools one might use.

First as with any database, you’ll need to gather the business requirements and design the physical database. For that, a data modeling tool like Toad Data Modeler (TDM for short) can suffice. TDM offers both logical and physical data modeling, as well as support for just about any database platform you might use. It offers both forward and reverse engineering – plus both model to “model and model” to “model to database” compare and sync capabilities.

Second you’ll need to populate or load the data warehouse. Some people will use specialized Extract Translate and Load (i.e. ETL) tools such as Oracle’s Warehouse Builder. A promising book that I’m reviewing currently and will soon blog about is “Oracle Warehouse Builder 11G: Getting Started”. But others may well decide to write custom applications to load the data warehouse. For that, a robust tool like Toad for Oracle can be invaluable. No single tool offers DBA’s and developers so much from one simple yet robust graphical user interface. Toad for Oracle is the single best database professionals’ Integrated Development Environment (i.e. IDE) or Rapid Application Development (i.e. RAD) tool.

Finally you’ll need a tool for your end users to query and report upon the data warehouse which helps them to transform the massive amounts of raw data into true business information. Some shops have actually used Toad for Oracle. But the business people tend to favor less technically oriented tools that offer better business workflows and capabilities. For that, a tool such as Toad for Data Analysts (TDA for short) is a fantastic choice. TDA offers a very friendly user interface and streamlined workflow, with task flow automation as well as integration with typical end user tools such as Microsoft Office.

So while you might think of your data warehouse as a special beast and thus be looking for DW vendors per se – remember that Quest offers a complete line of tools to support your DW needs. And with some Toad bundles, you can get all the products mentioned above at one low price J

Handling Complicated Tasks More Easily

QCTO Blog — Mon, 09 Nov 2009 08:00:00 GMT

Did you know that in Code Tester 1.8.5 you can now perform in-line validation of your customization code? For more complex tests, this will save you lots of time and make it easier to build your regression tests.

The ultimate objective of Code Tester is easy to state: allow developers to test arbitrarily complex PL/SQL programs without writing any test code. That turns out to be really hard to do, so as we get Code Tester closer and closer to that goal, we also offer the ability to fully customize every aspect of your tests.

One of the nicest new features in Code Tester 1.8.5 is the ability to perform "in-line" validation of customization code. Prior to 1.8.5, the only way to see if your custom code had compile errors was to generate the test package, which meant that you had to close the window in which you entered the code. If that test package had errors, you had to then circle back to that window - a cumbersome process that frustrated users.

There are two ways to determine if the custom code you entered has compilation errors:

Generate the test code (which will automatically happen the next time you try to run a test), which also attempts to compile the test package that will contain your test code.
Ask Code Tester to perform in-place validation (while the customization window is still open). The steps to request in-place validation vary depending on if you are in Test Builder or Test Editor.

Test Builder In-Place Validation

In Test Builder, when you have the customization window open for the test case, you can ask Code Tester to check for errors in three ways:

Press Ctl-Alt-V.
Select "Check for Errors" from the right click menu
Press the "Check for Errors" button.

All three options are shown below:

Test Editor In-Place Validation

When you press on the Customization tab at any level within the test definition, you can then edit custom code. Within this window, you can ask Code Tester to check for errors in three ways:

Press Ctl-Alt-V.
Select "Check for Errors" from the right click menu

Both these options are shown below:

When No Errors Are Found

If the custom code has no errors, then you will see this window:

When Errors Are Found

If your custom code contains one or more errors, Code Tester will open a window that contains all the customization code for the current element and also...

Custom code for elements above it in the test definition hierarchy.
If you ask to validate input or outcome custom code, custom code for all inputs.

If, for example, you request validation of test case custom code, you will also see in this window unit test custom code (if present). Code Tester does this because the test case custom code might reference custom code at that higher level.

All inputs are included because an outcome's customization code may also reference a local variable holding an input value.

You will see at the bottom of the window a grid of all errors reported from Oracle's USER_ERRORS data dictionary view. You can click on a line number in that grid and that line of code will be shown.

You cannot change any of the code in this window. Instead, you must close the window, change your code, and validate again.

Here is an example of the window:

Free TOAD Book Contest Results

Bert Scalzo — Mon, 02 Nov 2009 20:32:00 GMT

I want to start by thanking everyone who participated in the free TOAD book contest by submitting new feature ideas or enhancement requests. I received numerous good ideas. I also received about a third that were already in TOAD – and have been for a long time. I actually can benefit from those emails too, since Quest can probably better expose and/or offer training on those issues. Remember too this was an entirely subjective contest – i.e. I collected and judged the winners. That does not mean that they are necessarily the best ideas or that they will get onto the product roadmap. I’m pretty sure that all the ideas will be reviewed by the TOAD dev team and judged differently. However for just the purpose of simply allocating some free books – here are the entries with the winners in bold. I will be contacting the winners for shipping addresses so I can send them their TOAD book.

Note: Five of the winners are simply minor but useful enhancements to existing features (i.e. productivity enhancers), only one totally new feature in the winners list.

TOAD Options to offer support for DROP TABLE options (cascade and purge) in addition to the pop-up.
FIND NEXT DIFFERENCE function in any grid.
FIND NEXT DIFFERENCE function in the COMPARE DATA window.
A way to export all blobs in a table to discrete files in a directory where each blob file would get a unique filename based upon attributes in the record.
It would be nice if Toad could handle setting up and managing/administering a Data Guard environment.
All of Quest’s insight into optimizing Oracle for virtualized environments in the Health Check and the Create Database wizards.
TOAD as an application in Oracle Application Express (APEX), so that we could run TOAD from anywhere.
Ability to natively install and run the latest & greatest TOAD commercial (e.g. 10.1) on Mac OS and/or Linux.
Offer 64-bit Windows OS support such that one can use the 64-bit Oracle home/client install (currently requires using 32-bt client even on a 64-bit OS).
Code generators for languages such as PHP or JAVA to create web-pages or forms in the language taken from a table. It should include master-detail support.
For the Script Manager to come with more pre-built libraries of useful scripts, such as one for EBS – and a mechanism for the community support and/or make additions to them (e.g. ala open source type movement).
Session Browser add an icon or some other visual indication for which sessions have trace enabled (those enabled via TOAD and if possible any/all others).
For CONTROL-Space keystroke in the editor to perform an auto-complete when the prefix matches just a single value from the list.
For the replace string feature to permit more complex substitution patters like the code snippets such that something like BEGIN would result in BEGIN followed by a blank line and END, with the cursor in the blank line.
TOAD to offer APEX support – such as browser, editor with language support and ability to run and/or debug APEX applications.
Support for XML-DB – also with feature to generate a table or XST from the other.
TOAD to offer plug-ins or optional modules to mirror the Oracle Database options, such as Active Data Guard, Advanced Compression, etc.
TOAD Xpert edition to support complete and robust code and/or object impact analysis – with both normal and exception type reports.
For database long operations (LONG_OPS) a complete and all-in-one dashboard for what’s occurring in that long running process – rather than the spread out info in the session browser.
Compare and Sync should offer the ability to select multiple schemas for a single connection – so one compare connection can handle multiple schemas as one run.
Making all TOAD functions use the same wildcards, instead of ‘%’ in some places and ‘*’ in others – so both should work in all cases.
Print in the editor to offer an option to include matching line connectors as shown live in the editor within TOAD. Currently have to manually draw the lines after the print.
Editor to include bottom sub-tab like DBMS_OUTPUT but for HTF, HTP, OWA calls in the editor’s code.
On the connection screen, the ability to indicate “Save Password” at the individual connection level so that can save for development and test – but not for production.
In the editor, more advanced split screen support – such as being able to scroll and edit both portions of the split code (i.e. treat the two splits as two separate and live windows into a single instance of the code).
Augment the schema browser’s quick navigate/jump capability to work backwards and forwards in terms of offering Shift-F4 on both source and target, to add IE like back and forth toolbar icons, and maybe to replace the sundial icon with something more meaningful or related to what it does – navigation history.
Add an audible single (e.g. ribbit) for when a query or query thread completes.
Restrict Object Palette included Functions/Procedure/Packages from the selected schema to only those that the user has permission to execute.
Editor’s ability to automatically recognize user defined variable prefix as indicating a substitution variable (e.g. so prefix_variable treated same as :variable and &variable).
Editor’s ability to perform a “save” query/script with the variable substitution characters replaced by their last execution value – so you can ship a completed query/script to someone.
Editor ability to provide feedback (e.g. balloon help and/or status bar message) so that user selected identifier displays its length – very useful for keeping under 30 or 32 character length limits.
Editor double-click enhancements:
- Double clicking on white space selects all the white space between non white space characters. Consider end of line as not white space.
- Double clicking on the characters ( [ { selects all the characters from the opening ( [ { forward to the matching closing ) ] }, but not the parens, brackets, or braces.
- Double clicking on the characters ( [ { selects all the characters from the opening ( [ { forward to the matching closing ) ] }, but not the parens, brackets, or braces.
- Double clicking on either character in the /* pair selects forward to the matching */, but don't select the /* */ pairs.
- Double clicking on either character in the */ pair selects back to a matching /*.
- Double clicking on a non word character which is proceeded by and/or followed by that character, should select from the first to last character. I.e. in the line, '***** is a place holder.', double clicking on an asterisk should select all 5 asterisks.
- Double clicking on a non word character should select that character, not just move the cursor. Single click should move the cursor.

Finally note that one request that about a dozen people asked for was the ability to have some TOAD screens or the product in general to offer an auto-refresh capability such that TOAD can be left “running for extended periods of time” without a database connection timeout error occurring. We cannot do that. In today’s world of security and compliance, no tool should circumvent protections placed by systems and/or database administrators.

The Most Important Language to Learn

Steven Feuerstein — Mon, 02 Nov 2009 13:54:00 GMT

I was recently in Germany, and did an interview with Erik Franz of database|pro. One of the questions he asked me was: "Which programming language would you recommend a college student learn to give them the strongest chance at a good job upon graduation?" I would have loved to be able to answer: "Learn PL/SQL!

Toad for Oracle & XML

Jeff Smith — Thu, 29 Oct 2009 19:00:00 GMT

Oracle introduced support for XML in the relational database going back at least to about the 8i release. Toad for Oracle introduced formal support for XML in the 8.0 release.

XML and Toad v8.0 – v9.7

As you browse or query an object, if it contains an XML

via XML data type
via VARCHAR2/CLOB with XML header to start the string

Toad offers to display and edit the data with the XML editor as shown below.

You invoke the editor by double-clicking on the cell containing the XML string.

XML and Toad v10+

One of the ‘victims’ of Toad’s overhaul to support Unicode for the v10 release was the XML editor. The component was not able to support our requirement for Unicode. Rather than risk data corruption, it was decided to deprecate the feature (for more information, please consult the v10 Release Notes found under the Help menu.) Does this mean you can no longer use Toad to interact with and manage your XML data in the database? The answer is ‘No, no, no, no, no, no, no!’

Out of the box, when you attempt to edit XML data in Toad, it will be displayed in a standard text pop-up editor.

But that’s an icky way to edit and maintain my XML, Jeff!

No arguments here, but Toad gives you an excellent option going forward. I think we can all agree that Toad will never be the best word processor or FTP client available (although our FTP is pretty darn sweet!). The same can probably be said for our older XML editor. It pales in comparison to some of the free and pay editors out there. Check out Altova’s XMLSpy for starters. Toad now allows you to define an external editor for working with XML.

View – Options – Executables – XML Editor

Now when I invoke the editor by double-clicking on an XML cell, Toad sends the XML text directly to my editor of choice. When I’m done, I can send the data back to Toad to COMMIT my work.

I’ve chosen a tool called XML Marker. I don’t endorse it, and please read the ULA before choosing it or any other product for your organization. Microsoft also has something that takes a Notepad approach to XML. Check them out here.

I must admit I was a bit taken aback when this change occurred. But now that I’ve had a chance to digest it, and investigate some of the XML editors available out there, I’m pretty happy with how it’s turned out. I’m pretty sure the existing XML editor that Toad was using hadn’t been enhanced since it made its debut in v8. Now letting Toad tackle the database stuff and having a best-of-breed XML editor handle the XML stuff that Toad can talk to, I have the perfect combination!

Generate Schema Documentation in Toad for DB2

Ben Boise — Tue, 27 Oct 2009 07:00:00 GMT

In one of my previous lives, I worked at a startup company testing software. Our application used Oracle as the back-end and the nature of my role had me working closely with our Oracle DBA. As part of our processes, our DBA had to build documentation describing the schemas our application needed. The reports included things like table names, column names and their data types, indexes, etc. This used to take him hours until he discovered the HTML Schema Doc Generator feature in Toad for Oracle. With a few clicks, he was able to build a report containing all relevant information needed for our documentation. Well, Toad for DB2 offers the same capability.

To generate a Schema Report, in Toad for DB2, select one or more schemas in the Database Explorer. Next, simply right-mouse click and select the Send To | Schema Report menu. You should see something similar to the below screenshot:

There are two sides of this screen that allow you to customize your report. The left-hand side shows you the list of objects that will be included in the report. If you wish to remove an object from the report, you can select it and hit the Delete key to remove it. The right-hand side contains the settings for the report. You can use these settings to refine the information you wish to include in the report. Once you’ve refined the object list as well as the report settings, you can select Schema Report | Generate from the menu, or click the button on the toolbar highlighted in red below:

A new tab will display the report:

The toolbar in the Report Preview tab provides additional functionality, such as adding a watermark, changing the report background and saving the report and emailing it as other types of files like PDF and Excel. If I make changes and want to refresh the report, I’ll need to click the icon shown in red below:

Anyone having to produce this kind of documentation may find this feature of Toad for DB2 to be a huge time saver. Feel free to tell the boss it’ll still take 4 hours. What you do with your free time is your business ;-)

More Toad Tips and Tricks

Jeff Smith — Mon, 26 Oct 2009 20:48:00 GMT

So with Toad v10 being released, my Tips & Tricks guide became a bit out of date. You can find an updated copy, as well as our v9 copy, here. All of your favorite keyboard shortcuts and workflows remain largely unchanged, so this is more of an update for making sure screenshots and naming conventions are correct for our newer users.

For our Xpert edition users, you will see significant changes in the SQL Optimization interface and workflow. Be sure to investigate the 4 different SQL tuning methods, in particular ‘Optimize’ and ‘Rewrite.’ The nature of your query (how long it takes to run, how complex is it?) will determine the method that’s best for you.

Feel free to host your own internal Toad seminars using any of our content. If you need assistance or want some custom content developed, just let us know!

An Amazing Visit to Mexico City

Steven Feuerstein — Fri, 23 Oct 2009 12:38:00 GMT

As I write this, I am flying back to Chicago now, 9 PM Oct 21, from Mexico City, after a very nice two day visit.

Quest Mexico asked me to come down to help celebrate the release of Toad Version 10. I said "OK."

And, wow, they did a really fantastic job of making me feel appreciated. Besides putting me up in a fabulous, old but very excellent hotel, El Camino Real Polenco, and making sure I enjoyed outstanding meals of (surprise) Mexican cuisine, they pulled together an enormous gathering of over 250 Oracle technologists for an evening presentation at the beautiful http://www.haciendadelosmorales.com/index.php by Claudia Fernandez, Quest product manager supreme, to talk about Toad 10, and me to talk about, well, pretty much whatever I felt like talking about, which turned out to be the function result cache feature of Oracle11g.

Quest Mexico also ordered 100 of my Oracle PL/SQL Best Practices books to give away to the first 100 people who arrived on the 20th. That turned out to be a fine motivator. Developers started arriving at 2 PM or so for a 5 PM start time. Unfortunately, due to problems with Customs in Mexico, we didn't have those orange books. Fortunately, we had a nice big pile of a book that Claudia co-authored, Database Benchmarking. So attendees received those. And then they lined up for autographs. As I signed books, I got to meet a lot of very enthusiastic PL/SQL developers - several of which said that they benefited greatly from my writing. How satisfying!

On the wall behind our signing table was a banner with a reproduction of my head that must have been five feet high. I kid you not - in fact, you can see it here. Fortunately, I am a humble fellow, so I only posed in front of the banner for a few photos.

Many attendees also asked to have their photo taken with me. And Ricardo of Quest was kind enough to use my camera to also capture those moments. You can view all those photos here.

I must tell you that years ago, when people first asked for autographs, asked to have their picture taken with me, I was a little bit embarrassed by the whole situation. C'mon, I just wrote a few (well, 10) books about this one language, PL/SQL. Why are they getting so excited? They are being weird, ridiculous. But I eventually came to realize that it really doesn't matter what I think. What matters is that for whatever reason (I have some idea of why this happened, but it is still a bit of a mystery to me) my books, my writings, my trainings have had a substantial impact on the lives of these developers. My ideas and my ways of expressing them have improved their lives.

Now, that realization is/was a continually humbling experience. Now, I am honored that anyone would care to have me sign their book or share a photo with them. And I have decided to start celebrating this honor and, at least this is my intention, honor my readers by posting on Flickr the photos we took together (and others from the event in La Hacienda Morales in Mexico City).

If anyone sees their photo on this page and does not want it to be available publicly, please let me know immediately and I will remove it. My apologies in advance for any liberties taken.

Many thanks to Diana, Belen, Daniel, Pablo, Ricardo and others at Quest Mexico for organizing a fantastic set of events (beyond just the public program), media interviews and overall stay. I look forward to a triumphant return, or perhaps a visit to Monterey.

Once again, I find myself marveling at my good fortune and the fine life I have. I only wish I was able to spend time with all my PL/SQL friends around the world and not be away from wife, Veva, and my boys. That is a substantial price to pay. Sigh....

Finally, I very much enjoyed having an opportunity to resurrect my rusty (not) command of the Spanish language. I am not sure whether it is a blessing or a curse, but they tell me that I am able to pronounce words in Spanish very well (roll those r's, put the accent on the correct syllable, etc.). This leads native speakers to believe that I can speak and understand much more than I actually can. So I start out strong, but immediately stumble over a serious lack of vocabulary, and very poor comprehension (I can talk better than I can understand - big surprise, I spend so much of my life talking at others).