John Pocknell's Blog

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!

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.

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.

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

What’s New in Toad® for Oracle 10 – Part 2

John Pocknell — Tue, 06 Oct 2009 07:00:00 GMT

The very latest generation of Toad for Oracle is about to hit the streets (early October) so we thought we’d give you a quick preview now so you’ll be up to speed when it’s released. This blog is part 2 and covers more of the new features and updates.

What’s New in Toad® for Oracle 10 – Part 1

John Pocknell — Wed, 30 Sep 2009 07:00:00 GMT

The very latest generation of Toad for Oracle is about to hit the streets (early October) so we thought we’d give you a quick preview now so you’ll be up to speed when it’s released.

Introducing a New Free Utility Download – Toad Group Policy Manager

John Pocknell — Tue, 07 Jul 2009 18:13:00 GMT

You may already be familiar with the Toad World Exclusive Freeware area from where you can download and use really useful utilities such as the PL/Vision Code Library and the Freeware version of Quest Code Tester for Oracle.

Well here is another utility you’re going to find really interesting and it’s called the Toad Group Policy Manager.

As you probably figured out from its name, it has something to do with Toad and something to do with managing policiesJ. I wrote a blog about Toad Group Policy Manager (TGPM) two years ago when we released Toad version 9.1.

What is Toad Group Policy Manager?

The Toad Group Policy Manager provides a facility by which multiple copies of Toad for Oracle within an organization can share the same set of options or feature restrictions. It consists of a Windows Service which runs on a Windows server and publishes subsets of option data to defined groups of Toad users via TCP/IP, and an Editor which is used to define option sets and user groups. Users can be restricted from changing published Toad options or permitted to alter them.

Using the Toad Group Policy Editor, policies and standards can be distributed throughout a group environment.

The Group Policy functionality consists of three parts:

Group Policy Server
Group Policy Editor
Toad Policy files

PLEASE NOTE: This utility will ONLY work with Toad for Oracle 9.1 and higher. Policy Editor options added since 9.1 will ONLY work with the Toad for Oracle version in which they were introduced or higher. Please read the TGPM Compatibility Matrix in the TGPM Help for more information.

How do I install Toad Group Policy Manager as part of my Toad installation?

The Toad Group Policy Manager is included in the Toad for Oracle installer and provides an option for installing separately from the Toad for Oracle installer.

There are 3 installation options:

Full (Server and Editor)
Server Only (Recommended)
Editor Only (Recommended)

The generally recommended methods are Server Only and Editor Only so that the actual Policy Manager can be installed on a central server leaving the option to manage policies from a separate desktop using the Policy Editor.

TGPM listed in the Toad for Oracle installer

The Toad World version is exactly the same and comes with its own MSI installer.

How does Toad Group Policy Manager work?

The following diagram will help explain the concept of how Toad Group Policy Manager works.

When Toad for Oracle starts, if it recognizes it is in a managed environment it calls in to the Policy Server, giving it its computer name or IP address.
The Policy Server looks up the name, and if it is in a predefined group of managed users, the Policy Server creates a packet of information and sends it back to the client via TCP/IP.
Whatever options were applied in the Policy Editor are applied to the users Toad for Oracle application.

Once Toad Group Policy Manager is installed, the Group Policy Editor can be used to nominate the server on which you would like to have Toad Group Policy Manager to run and then you can begin defining Groups and Users following the same concept as Windows Active Directory.

Specify the Windows Server name or IP address

In order to use Toad Group Policy Management, a file called toad.pdl must be created and installed with each user's instance of Toad for Oracle. This .pdl file specifies the server and port information where Toad for Oracle will look for the Group Policy restrictions.

Specify the server location for the PDL file

Groups could be made up of development teams, DBA teams, analysts, etc, all of whom have different ways in which they use Toad for Oracle and different ways in which they share common assets like formatting templates, version control system access as well as certain Toad options, etc.

Group Policy Editor being used to add new Groups and Users

The user lookup will allow you to browse the Windows environment for different users even if they are in a different Domain. Alternatively, you can specify a computer name if more than one person uses the same desktop.

The next task is to decide what things you would like to share across your group or team by reviewing the list of Managed Items. From here you could make sure the same people have the same Toad Options, are using the same templates or, if you are a DBA, ensuring that Toad users have to enter an Oracle password when they log on.

Managed Items showing Action setting and Value for Toad Options and Settings – in this case PL/SQL Template files being shared across a development team

The Action settings enable flexibility in how policy settings are deployed to different user groups:

Don't Restrict - the default. This option leaves the managed item completely unrestricted.
Publish - Select this to publish a set of settings to the machines. The user can change the settings on this item, but they will be renewed each time Toad is restarted.
Restrict - Completely restrict the user from changing the settings made.

The Group Policy file now needs to be saved in to preserve its settings. This is saved as an xml file.

The final stage is to publish the Group Policy file to the server since the Policy Editor may be installed on a desktop machine remote from the server.

The files which are sent to the server when you click the publish button are:

GroupPolicy.xml
Zip archives of any files that the group policy server needs to publish to a user group (for example, formatting files, template files, and so on). One zip file is created for every restriction type requiring files that is to be published.

When you launch Toad for Oracle, the contents of the policy will be posted out from the server and refresh any current Options and Settings.

Toad Options window for a Managed Toad user showing the Team Coding options pre-set and deactivated

How do I know whether Toad Group Policy Manager is helpful to me?

Here are two scenarios to help explain how Toad Group Policy Manager will be helpful to you in your organization and enable much improved manageability especially where you have multiple Toad for Oracle users:

Scenario 1

A Development Manager wants to ensure each Project Team member has Toad’s Team Coding feature correctly configured and they have the correct access to the corporate Version Control System. There is also a standard Formatting Template which everyone must use. These options and settings would normally have to be configured separately for each Toad user.

The Development Manager simply uses the Group Policy Editor to select the Windows users in the project team then sets the appropriate Team Coding options, VCS configuration details and location of the Formatting Template through the Toad Policy Editor then publishes the policy out to the Toad Group Policy server.

Each Toad for Oracle user has their Options and Settings automatically updated by the Toad Group Policy Manager when they next launch Toad.

Scenario 2

A DBA wants to set up Toad to have Read-Only data grids for certain users and they also want to be able to prevent all users from saving their passwords.

The DBA simply uses the Group Policy Editor to define two Groups, each with the appropriate list of Windows users. To the first Group, they set the “Use Read Only Queries” Action to Restrict and the Value to True. To both Groups, they set the “Save Oracle Passwords” Action to Restrict and the Value to False (which de-activates the check-box). The changes are then published to the Toad Group Policy server.

Each Toad for Oracle user in their corresponding Group then has their Options and Settings automatically updated by the Toad Group Policy Manager when they next launch Toad.

Additional information is provided in the Toad Policy Editor Help.

To download the Toad Group Policy Manager installer, simply click this link or go to the Exclusive Toad World Downloads page from the Downloads tab on the home page.

We would be more than happy for you to tell us what you think about Toad Group Policy Manager should you go ahead and try it out, and also how you decided to deploy it in your organization. That way, we can look at improving it further.

Please post a comment at the end of this blog.

Introducing a New Free Utility Download – SQL Monitor

John Pocknell — Tue, 07 Jul 2009 17:43:00 GMT

Now there are two more which I’d like to introduce you to – one which has been around for many years and the other a relative newcomer.

The first one is called SQL Monitor. Some of you may have already come across it since it’s included with Toad for Oracle and can be launched from Toad’s UI via the External Tools button.

Launching SQL Monitor from Toad for Oracle

For those of you less familiar with the SQL Monitor, let me explain what it does.

SQL Monitor is a utility that monitors Oracle, MySQL, and DB2 Client DLL calls, allowing you to capture all SQL calls a program sends to the database via the client DLLs.

In Oracle, for example, this would include Toad for Oracle, SQL*Plus, SQL Developer, etc.

Originally built by the Toad development team to help with troubleshooting, many users have already found this to be an extremely useful utility.

SQL Monitor monitoring Toad for Oracle session SQL

The main window of the program consists of two parts:

Left pane

Applications

Click this tab to see a list of Windows applications that are connected to Oracle, DB2 or MySQL. These may include sqlplusw.exe, Toad, etc.

Services

Click the Services tab to see a list of the Windows NT services that are connected to Oracle. For example, these may include Oracle itself, third-party application servers that run as NT services, etc.

Right pane

The right pane contains a tab for each process being monitored. Click the appropriate tab to see the process.

Monitoring Processes

In the left pane, select the process you want to monitor by clicking the checkbox.

If you want to monitor an application that is not running, you can click the Launch button on the toolbar, or press . The new application will start and you can monitor it from the time it starts running.

Major features of SQL Monitor include:

Supports Windows NT/2000/XP
Supports Oracle clients starting with version 7.3
Displays SQL statements that come from processes in real-time, eliminating the necessity to turn on tracing and look into poorly formatted trace file
Allows monitoring NT services
Displays logon attempts, both successful and unsuccessful (username, password, TNS alias, success)
Performs syntax highlighting
Displays values of bind variables for SQL statements
Shows even statements that fail to execute. Displays Oracle error code and message for those statements.
Allows you to copy and paste statements from the output to one of your SQL analyzing tools (Toad, SQL Navigator, or even SQL*Plus)
Monitors applications like Import/Export, SQL*Loader, Server Manager (svrmgrl.exe), as well as applications written with Oracle Forms or Pro*C

To download SQL Monitor, simply click this link or go to the Exclusive Toad World Downloads page from the Downloads tab on the home page.

We would be more than happy for you to tell us what you think about SQL Monitor should you go ahead and try it out. That way, we can look at improving it further.

Please post a comment at the end of this blog.

Always like to be on the latest version?

John Pocknell — Tue, 07 Jul 2009 17:29:00 GMT

As many of you may be aware, Toad for Oracle is available in a number of different Editions and Suites. For those of you who have Toad Development Suite for Oracle or Toad DBA Suite for Oracle, there are a number of recent product updates which you can take advantage of now (GA Release Date July 8^th) rather than waiting for the next full scheduled release of Toad (due in October).

The below listed products are, of course, also available for download if you have them standalone, for which you should have received a separate notification:

Benchmark Factory for Databases 5.8.1 – Part of the Toad Development Suite and Toad DBA Suite
- Support for F.D.C.C. requirements (U.S.A. only)
- Database Workload Replay enhancements - Trace File imports for both Oracle and SQL Server with less than 1000 total SQL transactions will now be imported with the SQL transactions being shown in the BMF user interface. (released with BMF 5.8 on 14^th May 2009)
- Various fixes
Quest Code Tester for Oracle 1.8.3 - Part of the Toad Development Suite
- Improved Test Import/Export architecture
- Reporting extended to include Test Suites
- Improved usability and flexibility
- Various fixes
Spotlight on Oracle 6.1 - Part of the Toad DBA Suite
- Improved licensing so that Spotlight will accept either Standard, Professional or RAC keys in the same dialog.
- Oracle Home selector so that users can chose which Oracle Home to use (like Toad).
- Unix monitoring now uses Perl for improved performance.
- New diagnostics
  - Result Set Cache monitoring (Oracle 11.1 and above). Displayed on the SoO Home Page, these display low find-to-create ratio and high result cache sleep ratio. There is also a new Result Cache drilldown.
  - OEM monitoring – Oracle 10g and later. Basic monitoring of the OEM agent. There is a new component in the Server Processes part of the Home Page.
  - Hard Parsing activity (Oracle 10.2 and above). Based on the Oracle 10g Time Model, there is new alarm called “Total Parse Wait Time Ratio” alarm, and an associated drilldown in the “SQL and Application Workload” drilldown set. Hard parsing occurs when no SQL is found in the Shared Pool which may indicate that bind variables are not being used effectively.
  - Sorting diagnostics (Oracle 10.2 and above). Covers a range of PGA activities such as sorting, aggregations and hash. There is a new Temporary I/O Wait Alarm which may indicate where the allocated work area for memory sorts is insufficient. There is also a Sorting drilldown which identifies offending SQL statements and sessions with possible recommendation for allocating more PGA memory.

If you have the above products standalone or as part of the Toad Development/DBA Suite, you can update them by visiting https://support.quest.com/SUPPORT/index?page=downloads and searching for the product name (provided you are current on support).

Please read the Release Notes associated with each product for a detailed list of changes.

Extending Toad for Oracle’s Code Xpert with the SonarSource dashboard

John Pocknell — Mon, 06 Jul 2009 13:59:00 GMT

Written by Olivier Gaudin

Toad for Oracle’s Code Xpert utility (part of the Toad Professional Edition) is one of the best PL/SQL code analyzers available on the market, embedding the know-how of leading PL/SQL experts such as Steven Feuerstein. However, although there is the option to store data in the Code Xpert repository for point-in-time reporting, it is still primarily a static reporting tool and therefore cannot report dynamically on code quality trends as part of an overall quality strategy in a company, without manually querying the repository tables.

SonarSource is a company whose main objective is democratization of source code quality management. To reach this objective, the development of Sonar, an open source platform to manage source code quality, started two years ago.

Sonar enables the collection, analysis and reporting of quality metrics on source code. Its functionality is articulated around 7 strengths:

a flexible and powerful data collection engine
a synthetic project dashboard, showing every axis of quality analysis (coding rules, comments density, potential bugs’ detection, code duplication, unit tests, standards metrics and code complexity)
a centralized configuration using quality profiles with associated alerts
a TimeMachine to follow trends and compare versions
several tools to chase defects
a consolidated dashboard showing all projects at a glance
extensibility through a powerful API and a plugins forge

Originally built for Java, Sonar has been extended to include the PL/SQL language through a commercial plugin. In this blog, I am going to explain how Sonar leverages Toad’s Code Xpert unlocking its full potential in order to provide a full code quality management product.

Running code analysis with Toad’s Code Xpert covers several axes of code quality management However, this is not sufficient if you are required to have a global approach. What do I mean by that? If you want to manage your projects portfolio globally, follow the evolution throughout time, get alerts and then maybe delegate some remediation work, you are left with a lot of manual work to consolidate data, compare projects or versions. That is where Sonar comes into play with the following features:

1. Quality Profile management

A quality profile is the set of rules that you choose to apply when you run code analysis on projects. In an enterprise environment, you would certainly expect that several quality profiles are defined and get associated depending on the nature of the project.

Every rule defined in a quality profile is managed in Sonar. Priority, activation and configuration are handled centrally in the tool.

In Sonar, the set of rules is coupled to thresholds on metrics in order to trigger alerts. Therefore it becomes possible to mission Sonar to proactively notify you if a project reaches more than 30% of rules violation for example.

2. Central reporting

Sonar centralizes reporting at two levels.

Firstly at the project level where it enables its user to get a comprehensive dashboard (that extends to any directory) enabling to view all metrics at a glance to evaluate axes that need to be worked on. On top of the metrics covered by Code Xpert, Sonar also reports on duplicated code and comments.

Secondly, at the projects portfolio level to be able to be able to get the big picture projects portfolio. The configurable dashboard enables to compare projects and quickly understand where risk lies. This dashboard is cross language (Java and PL/SQL in this case).

3. Metric evolution

At every analysis, measures are recorded into the Sonar database and add to historical data. Sonar has a TimeMachine that enables to go back in time, compare versions and monitor evolution.

4. Chasing tools

Sonar embarks a collection of chasing tools to track down the defects. Amongst them: files cloud, hotspots, drill downs

The treemap	The files cloud
The hotspots

The drill downs

5. The resource viewer

The resource viewer enables to display the source code “tainted” with a specific type of defect. All defects of the selected type are highlighted within the code.

Moreover, the plugin embarks a PL/SQL code extractor that enables the analysis of any Oracle Forms application.

The Sonar PL/SQL plugin is now at version 1.5 and currently covers 4 quality axes out of 6 possible. Here is the list of planned short-term evolutions:

Integration of SQLScan
Integration of File complexity (McCabe)
Integration of Unit tests

Here are a few links to help you find out more about Sonar and the PL/SQL plugin:

Sonar web site: http://sonar.codehaus.org
Sonar in action: http://nemo.sonar.codehaus.org
Sample PL/SQL project : http://nemo.sonarsource.org/project/index/nl.oracledeveloper:utplsql
To download a limited version of the plugin : http://www.sonarsource.com/plugins

About the author

Olivier Gaudin is co-founder and director of SonarSource S.A.; an IT company created in November 2008 and based in Plan-les-Ouates, Switzerland.

He has 12 years experience in IT, mainly in the banking industry and experience heading Development and Application support departments.

He has a strong background in SQL and has been involved in CMMI and ITIL implementations and has an acute awareness of quality and reliability of processes.

What Is Unicode? (Part 2)

John Pocknell — Mon, 08 Jun 2009 13:13:00 GMT

In this blog, I hope to be able to help you understand how Unicode is handled on your Windows PC/notebook and how to configure your PC/notebook in order to be able to effectively use Unicode in Toad.

What Is Unicode? (Part 1)

John Pocknell — Mon, 01 Jun 2009 13:18:00 GMT

As some of you may already be aware, we are releasing our next version of Toad (version 10.0) later this year and it will have one or two “under the cover” differences. Unicode support is one of those differences and is probably one of the most significant technology upgrades in Toad’s history.

Options, Options and More Options

John Pocknell — Mon, 04 May 2009 16:28:00 GMT

Like most applications, Preferences or Options enable the user to make changes to the default configuration to suit the way they work. The same is true for Toad but there are so many, it can be a bit of a challenge finding what you are looking for.

So the objective of this blog is to help you get a handle on what the main options are that you probably need to know about.

Some Toad Options are set during the Toad installation. Remember the Initial Setup Wizard which ran as you were going through the final stages of installing Toad?

The responses you gave here reflect what you see in the Options window. The problem is, if you were not completely sure what you were being asked and guessed and you decide that you wish to change it afterwards, or you just want to change something in particular, how do you go about it?

The first thing to do is to access the Toad Options window. You can either do this from the Toad toolbar button or select View | Toad Options from the menu.

The Toad Options window looks like this (incidentally, it will open at the last page it was left at):

Toad Options Window

The left side shows an alphabetical list of categories and sub-categories which, when clicked on, will open a different page. There is a search field (bottom left) where you can search for something (using partial strings) that you can’t find.

So, here are my recommendations for those options that may be of the most interest:

Toad Windows settings – one of the options in the Initial Setup Wizard (above) asked you which window you would like to open when making a new database connection. The default is the Editor. However, if you wish to change this, select the Windows category in the Options window (last in the list):

Toad Windows Behaviour Settings

You will see a table with 7 columns and an alphabetical list of every window in Toad. Let’s change the window to open on a new connection to the Schema Browser. Scroll down the list until you find Schema Browser, then click the check box in the 5th column (the one called Auto open). Now scroll back up to find the Editor window and remove the check mark in the Auto open column.

Selecting Schema Browser as the “Auto open” Window

Notice that, in this option, you can save the size and location you open in Toad. The first column (Available) enables you to de-activate windows in Toad. For example, if you are using the DB Admin Module and you want to ensure that you don’t inadvertently access any windows which may use an Oracle OEM Management Pack (e.g. Diagnostics), you could use this to de-activate the ADDM/AWR and AWR Browser windows.

You can also limit some windows to one per database connection or one per Toad session. Examples of those might be AppDesigner and Database Browser respectively.
Visual Style – there a few places where you can change the visual style of Toad and its windows. If you want to change the overall style of Toad to suit other Windows applications you might be using such as Word and Excel, you can select from a number of choices including Office 2003, XP or Enhanced. This time, let’s search for which options might apply. Enter the word “style” into the Search field and push the Search button. You should get 5 entries returned. Now click on the last entry (Toolbars/Menus). Toad will open the correct Option page for you and highlight/flash the required option. How cool is that! Now make your choice and push the Apply button. Now select the last but one entry in your Search window (Schema Browser – Visual). This is another option you were presented with when you installed Toad. You might decide that you want to change your original selection.

Changing the Schema Browser to Display a Tree-View Instead of Tabs
Executables – the reason this option is important is because it might affect your ability to run Oracle utilities on your server. The purpose of this page is to tell Toad where to run utilities such as Import, Export, TNS Ping and RMAN. You can also select the location of the Windows Ping program and your preferred Windows editor.

Specifying the Location of Your Oracle Utilities
Stored Procedure Templates – I included this one since most of us (developers) are familiar with the concept of coding standards. If you’ve read Steven Feuerstein’s blogs on Toad World, you’ll know that he is a big advocate of using correct standards. This option allows you to take the PL/SQL templates supplied out-of-the-box in Toad and change them to suit the standards you work to. Select the Proc Templates category and you’ll see the table below. In it you will see a list of standard templates for all types of PL/SQL programs.

Not only can you use these every time you create a new program, you can edit them based on your own standards and even include Toad variables (such as username and date) and substitution variables for something more specific.

PL/SQL Code Template Options Showing How Toad and Substitution Variables Can Be Added

To add a substitution variable, click in the blank field in the panel and just type. I’ve added one for “Project” because I want to add the name of the project to the comments section of my code. I’ve also added the name I want to appear in the Author section of the header.

So now we are going to create a new template for a Function based on the standard Toad one and add a couple of things. First go the Templates panel and push the Add button.

Select the object type from the drop down list, the name by which you want the template to appear when you create a new Function in the Editor and browse to the default location and select the appropriate generic template file upon which your modified template will be based. (in this case, newfunc.sql). We have just created a duplicate Function template with a new name.

Now scroll to your new template listed in the Templates panel, select it and push the Edit File button.

Modified Function Template

Make the necessary edits. I’ve added a new line under PURPOSE: called PROJECT: and assigned a value of &Project (my substitution variable defined earlier). Click the Save button. All templates are stored in: C:\Documents and Settings\”username”\Application Data\Quest Software\Toad for Oracle\9.7\User Files\.

Now let’s test it. Go to the Editor and press the “Create New PL/SQL Object” button on the Editor toolbar . In the popup window:

Select the object type, type the object name (don’t use spaces) and, instead of selecting the usual “Default” template, select your new one. Your new template will appear in the Editor showing the new edits you made previously.

New “Tailored” Function Ready to Use

PL/SQL Templates can be shared across a development team or project team so that everybody creates their code to the same standard.
Oracle General settings – in order for Toad to interact with Oracle correctly, there are some settings here that you should check such as the appropriate version of your Oracle Client (if you are using one), where your Oracle Execution Plan table and your Chained Rows table (if using one) are located and what your DBMS_OUTPUT and OCI Array Buffer sizes are.

Oracle General Settings

From version 9.7 and higher, Toad will check to see if you are using the most appropriate Oracle Client version. If not, a warning message will appear when you try to connect.
Data Display – the presentation of data in Toad features a lot with many windows showing data grids, so you may want to change the default behavior, display settings and visual appearance to that which you are most comfortable with.

There are two sub-categories: Data and Visual.

Data settings include Behavior and Display and control how you work with data and how data like numbers and dates are presented. Visual settings include Behavior, Column Sizing and Display which control how the data grids can be manipulated and the style of the data presented.

Data Grid Visual Options
Debugger options – with Toad, there are different types of debuggers available depending whether you’re debugging PL/SQL or SQL*Plus scripts or whether you’re calling into Java Stored Procedures.

Click the Debugger in the options navigator and look at the “Debugger” panel. Here is where you can switch between DBMS, JDWP or Script debugging depending on your preference. Remember to click the “Allow stepping into Java source” if you want to continue your debugging session within your JSP. You’ll also find a panel called “Transactional Control”. This determines how, when debugging, you handle DML such as Insert, Update and Deletes.
Editor options – this is probably going to be one of the main options you’ll use as these influence the experience you will have within the Editor.

The Editor Options Have 5 Sub-Categories

Above, you will see the first called Behavior. The three main things I would check here are:
1. Languages panel: Code templates button – this is where you can add your own code templates which are activated in the Editor using .
2. Editor font button – this controls the appearance of the text in the Editor windows.
3. Auto replace button – this is a great feature that can save you a lot of time by using shortcuts to speed up text entry (just like Microsoft Word does). It’s really easy to add your own shortcuts.
The second sub-category (Code Assist) controls Toad’s Code Insight features (things like table/column popups, predictive text, etc). This also includes various languages for the Make Code/Strip Code feature. This is a real time saver if you have to embed SQL into an application language like Java (or remove it).

Display covers things like showing line numbers in your code and whether you want table, view or stored procedure highlighting (color coding) turned on.

Open/Save controls how Toad deals with opening and closing of files like Packages.

Printing allows you to set options like word wrap and how you want your headers and footers defined when printing the Editor contents.
Formatter options – this is a blue link at the bottom of the Toad Options window and enables you to control how your code is formatted to ensure it conforms to company or project requirements or just good best practices.

When you click this link, a formatting template will open allowing you to set things like tabs, indents, how columns and parentheses are displayed and how PL/SQL blocks are composed.

Formatter Options Showing the Preview Panel

For more detailed information on how the Formatter works, please read my blog entitled “Powerful Code Formatting in Toad”.

After making your changes in this generic template, you can save it under a different name using Save As and distribute to your other developers so that all code is formatted to the same standard.

There are, clearly, many options which I didn’t have time to cover, but hopefully, the few minutes it’s taken you to read this blog should make your experience of using Toad that bit better.

I hope you found this information useful and if you have any questions or queries, please feel free to post a Comment at the end of this blog.

Powerful Code Formatting in Toad

John Pocknell — Wed, 01 Apr 2009 13:05:00 GMT

In this week’s blog, I’d like to introduce you to Toad’s Formatter. For those older “Toadies”, you’ll be familiar with its predecessor Formatter Plus.

Configuring Toad’s Team Coding with Subversion

John Pocknell — Tue, 23 Sep 2008 01:03:00 GMT

With Toad for Oracle 9.7 now available, I thought I’d cover how to configure and use Subversion with Toad’s code sharing utility – Team Coding. We had a lot of requests for this integration and here it is !

The Subversion implementation in Toad allows you to use the conventional file-based check-out and check-in via the Editor’s File Open button. All you have to do is browse to the working folder, select your file and open.

However, Team Coding, a standard feature in Toad, makes using Subversion much simpler where you have a team of developers and you require visibility of the project’s code, who has access to it, what it’s status is, what the version history is and maybe compare versions. And this is the subject of this blog.

First things first, though – so what exactly is Team Coding?

This link directs you to a blog written by Jeff Smith last year on what Team Coding is, so if you’re not familiar with it, have a read of it before you read this blog.

http://toadworld.com/Community/Blogs/tabid/67/EntryID/136/Default.aspx

OK... back already?

Step 1 – Installing a Subversion client - TortoiseSVN

To use Subversion on Windows, you will need to use TortoiseSVN.

TortoiseSVN is a Subversion client, implemented as a Microsoft Windows shell extension. It is free software released under the GNU General Public License.

First download and install Tortoise SVN (http://tortoisesvn.net/downloads ). Please follow the instructions in this document (“Using Toad with TortoiseSVN.doc” ) on AskToad which can be found here:

http://asktoad.com/DWiki/doku.php?id=howtos&DokuWiki=ffb154ea8e0221a691f1805e1516f49f

Step 2 – Installing an SCC plug-in

In order to use Subversion with Team Coding, you will need to install an SCC plug-in.

Although there are a number of SCC plug-in programs you could use, the Toad Development team have tested a program called PushOK which can be downloaded from http://www.pushok.com/soft_download.php?idprogram=3 . Use the download which is most compatible with the version of Subversion you are using.

The PushOK SVN SCC proxy is an SCC API plug-in for any Microsoft SCC enabled IDE, including Toad for Oracle.

Please note: PushOK provide a free trial, but there is a charge for their software after the trial period. There are other plug-ins you could use (e.g. Unified SCC (30-day trial) and TamTam (unlimited eval period)) which will also require a purchased license. We may develop a direct interface to the SVN API for a future version of Toad to obviate the need to install this software.

Follow all the steps described in the “Using Toad with TortoiseSVN” document. If you have it set up correctly, then when you open the Repo-browser from your repository directory, you should see your Work folder registered and any files added displayed.

Step 3 – Configuring Team Coding

In your Team Coding settings windows, you can select the PushOK SCC from the list of available vendors. A login to PushOK should already have been established.

Step 4 – Create a Code Control Group

The next task is to create a Code control Group (CCG) in order to assign your code and other objects to a specific working group. Again, ensure you specify the correct working directory:

Now you need to define a CCG Mask to specify which schema and which code and other files you want associated with this CCG. Open the CCG and define the object types, schema and exclusion mask (or filter) you wish to apply.

Step 5 – Synchronizing code between database and Subversion

In order to synchronize the code you have in the database with Subversion, you need to perform an Export to VCS using thebutton.

This will create a copy of the code and other objects you specified in your CCG into your Subversion working folder. A list like this, which shows, based on how you defined the CCG, the code and other objects from which you can select or deselect what you would like to export.

Specify which export options you want, including comments and click OK

Before anything is exported to the Subversion working folder, TortoiseSVN will ask you to confirm the URL, Module and local path to its repository.

If everything is setup correctly, the export should proceed as follows:

Step 6 – Viewing your code

You can confirm that your files have been exported by right-clicking the repository folder and selecting Repo-browser. You should now see all the files located under the Work folder:

You will also open this window if you select “VCS Browser” from the Team Coding menu.

You should also see these files listed in the Team Coding Viewer window:

Select a program from the Toad Editor using the “Open from Database” button will initiate a pull from the VCS. The first time you do this, you will see a Question window (lower left screenshot).

The other window (upper left screenshot) you don’t normally see because it’s kept hidden, but shows how PushOK is accessing the Subversion working folder.

Clicking the Yes button will open the Team Coding Check-Out window (right screenshot) confirming the project from which the code will be accessed and (depending on which Team Coding options you have set) allow you to enter comments for the check-out operation.

Once the code is displayed in the Editor, you should have a “Checked Out” message displayed at the bottom of the window. This code is now write-locked until it is checked back in by the original user (or someone with Team Coding administrator rights).

To view the version history for an object, right-click the object in the Repository Browser and select “Show Log”.

This screenshot shows the revision history for the PL/SQL Function OW_CALC_ORDER_COST. By default, the Repository Browser will show the “Head” or highest version of each object. This can be changed to show specific versions.

The beauty of using Toad’s Team Coding feature with your VCS, is its seamlessness and visibility into your project code. It also enables much better protection of your code’s integrity since it checks the version of the checked-out code against the compiled version in the database, and offers a warning message to the user if they are out-of-sync. The user then has the choice of accepting the VCS version, the database version, or performing a file compare on-the-fly to determine which version to use.

Feel free to post comments on this blog, through ToadWorld, about your experiences of using Toad with your VCS and suggestions on how we can make improvements.

Thank you for reading !

Additional References:

There is also a paper available from ToadWorld written by Norman Dunbar about a year ago on how to configure Toad with Subversion (before we officially supported it in 9.7) if you prefer to use standard file check-out/in which contains some additional information about Subversion which can be found here:

http://www.toadworld.com/Education/ToadWorldPapersandPodcasts/tabid/82/Default.aspx (bottom of page)

What’s Coming in Toad 9.7 - Part 2

John Pocknell — Fri, 29 Aug 2008 13:56:00 GMT

It’s almost that time again as another exciting new release of Toad draws closer! We’ve decided that, rather than let you wait until Toad 9.7 is released before you see what’s new, we’ll give you a little fore-taste now! In this final blog (there are 2 parts), I want to cover what’s new for developers.

Data Generator (Toad Professional Edition)

One thing developers and testers often need and find it hard to obtain (apart from a decent pay rise!) is good data. It may sometimes be possible to obtain data from production, but this is problematic, since production data often contains sensitive or personal information which could render the company in breach of data protection legislation.

Clearly, creating data manually is too time consuming, so what is needed is a good data generator.

Toad 9.7’s Data Generator is available as part of the Professional edition and enables random data to be generated very quickly for single or multiple tables.

The Data Generator will recognise referential integrity, if it exists, and will also disable primary/foreign key constrains before population and re-enable them afterwards.

Data Generator is implemented in 2 places:

Schema Browser – table(s) right-click
ER Diagrammer – table right-click

Simply select a table (or group of tables) in Schema Browser and select Generate Data.

The default row count will be set to 1000 per table, but this can be changed, and the total estimated size on the database will be displayed.

The Advanced Options tab enables selection of things like table truncation before population, disable/enable Constraints and Triggers as well as allowing generation of Reproducible Sequences.

The minimum and maximum field values can be adjusted for character, number and date values in order to define an effective range.

The Show SQL button reveals how Toad generates the data using the Oracle DBMS_RANDOM package and the entire operation can be scheduled using the Windows Task Scheduler.

After generation, where you may have populated referential tables, you can use the Master-Detail Browser (Database menu | Report) to verify to correctness of the data.

In a future release of Toad, we will be enhancing Data Generator further with things like sample data and inter-field dependencies, etc.

Code Xpert (Toad Professional Edition)

Code Xpert is Toad’s PL/SQL code quality assessment and reporting tool which can be used by individual developers or by team leaders and managers alike to set up and maintain consistent code review rules and coding standards across their projects.

This is absolutely essential in order to reduce the number of development cycles due to poor quality and poor performing code, which then has to be re-worked by development. This can be hugely disruptive and expensive.

In this release, we have made a couple of enhancements:

Top 20 Rules
E-mailing of Code Xpert reports

Top 20 Rules was created in order to deal with occasions where a developer simply wants to be able to make a quick assessment of their code to see if there are any silly mistakes before a full-blown code review.

These rules have been chosen from the existing Code Xpert rules universe by Steven Feuerstein and Bert Scalzo as being those rules which are of most importance to most developers.

You can see straight away where a problem has been identified and also what the overall Toad Code Rating score is (Green, Yellow, Orange or Red) according to a combination of factors including Halstead, McCabes Cyclomatic and Maintainability Index.

Emailing of code review reports is very useful where a team (or project) review may have been carried out by a team leader or manager using the Code Xpert dashboard.

A completed review can now be emailed directly to someone in the organization for review or follow-up. This is sent as a Microsoft Excel attachment.

And appears like this:

Code Xpert data gets stored in a repository so it may be useful to create a difference report to identify trends, etc.

Upgraded Parser (Toad Base Edition)

Toad 9.7 gets an upgraded parser and formatter which fully support Oracle11g syntax in the Editor.

The formatter also enables a preview of any changes to the template so you can see the effect of a change before saving the template.

Subversion support (Toad Base Edition)

Although several customers reported that earlier versions of Toad work fine with the Subversion VCS, Toad 9.7 is now fully certified on Subversion, generally considered to be the successor to CVS (also supported by Toad). This is achieved through native support for the TortoiseSVN interface.

http://tortoisesvnscc.tigris.org/servlets/Search?scope=project&resultsPerPage=40&query=download&Button=Go. Toad already supports the Microsoft SCC API interface.

Once TortoiseSVN is installed, you can use it directly by opening a file from the Windows file system into the Toad Editor and then performing a check-out from the Editor, or by using Team Coding; Toad’s own code check-out/check-in utility (which links to any VCS).

In order to use TortoiseSVN with Toad’s Team Coding, you will need to install bridging software such as PushOK. http://www.pushok.com/soft_download.php?idprogram=3

Here, you can see the TortoiseSVN repository together with some code in the Toad Editor being checked-out via Team Coding using the PushOK bridging software.

There’s a new document available on the AskToad Wiki which fully documents how to install TortoiseSVNSCC and how it all works with Toad.

http://asktoad.com/DWiki/doku.php/howto_use_tortoise_svn_with_toad_s_source_control

Also watch out for a forthcoming blog on configuring Toad to work with Subversion.

Also note that Toad’s Team Coding now supports the ability to check in multiple files at the same time.

Code Refactoring (Toad Base Edition)

Toad 9.7 adds basic code refactoring. According to our own PL/SQL expert, Steven Feuerstein, refactoring is a highly structured methodology for going through clearly defined steps (each ideally verified by a pre-defined regression test) to make your code more maintainable.

In this release, we have added Extract Procedure. This involves selection of a correctly parsed block of code and extracting it into either a stored or local procedure.

Thus, this:

Becomes this:

Where Sub_Proc1 is:

Other (very basic) forms of refactoring, such as renaming of local variables and changing case were already available in Toad before 9.7.

There are many more exciting things in release which I don’t have space to cover in these blogs, but you can try Toad for Oracle 9.7 Beta NOW simply by clicking this link. http://www.toadsoft.com/beta.html

Download the Full Beta installer first into a separate directory from your current version, then install the Beta Update by downloading the zip and extracting into your new Beta installation folder.

The Toad Beta will use your current commercial license key. So enjoy and please let us know what you think!

For a comprehensive list of new features and enhancements in Toad 9.7, please refer to the Release Notes.

What’s Coming in Toad 9.7 - Part 1

John Pocknell — Fri, 22 Aug 2008 15:56:00 GMT

It’s almost that time again as another exciting new release of Toad draws closer !

We’ve decided that, rather than let you wait until Toad 9.7 is released before you see what’s new, we’ll give you a little preview now !

The main objectives of this release are to provide improved knowledge and education on the wealth of Toad features for users, so that you can really see Toad’s full value and realise you just can’t live without it. Additionally, we further strengthen support for Oracle database administration, and offer really cool new features for developers and DBAs, including data generation and workflow automation.

By the way, if you are still on a Toad for Oracle version prior to 9.6, then you may be missing out on valuable functionality. In Toad for Oracle 9.6, we moved the debugger and both Knowledge Xpert tools into the Base (Standard) Edition. So now is a really good time to upgrade if you haven’t already done so.

In this first blog posting (there will be two parts), I cover the new features that will most appeal to Oracle database administrators.

Database Browser (Toad DB Admin Module and Toad DBA Suite)

We’ve made some enhancements to the Database Browser so that it becomes your essential database administration console. Not only can you simultaneously navigate to every database you manage and drill-down into any one of them to administer database and schema objects, but you can now right-click on a database node and perform a variety of additional operations. These include Top Session Browser, Session Browser, Health Check, AWR Browser and many more, including a brand new one...Generate RMAN Scripts.

RMAN Support (Toad DB Admin Module and Toad DBA Suite)

After speaking to many DBAs and asking them about how they use RMAN, we saw a clear need to provide some essential backup script templates to simplify the task of creating RMAN scripts, particularly for less experienced DBAs.

There are a number of RMAN templates available in Toad for Oracle 9.7, from which you can start building your library. You can add to, edit and reference these scripts from a library accessible through the “Toad Options” window. Toad Variable names, such as current database and current user, etc. can be built-in and will be resolved when the scripts run.

Once an RMAN script is selected for execution, it executes on the server and closes itself once completed.

Also note that the Toad Editor now supports RMAN language. See screenshot above.

A log file is also generated, which serves as a record of the exact backup.

StatsPack and AWR Advisories (Toad DB Admin Module and Toad DBA Suite)

DBAs love the StatsPack and AWR Browsers from previous Toad releases, but thought that it would add tremendous value if we offered some basic advice, once a potential issue has been identified.

In Toad for Oracle 9.7, we’ve added advisories, which are based on information collected between two adjacent AWR snapshots. Note, to use this feature you must be licensed for OEM Diagnostics Pack (AWR).

The advisories are surfaced in two places:

StatsPack or AWR Browser
Session Browser (Waits tab – By Session and By Wait). Double-click blue hyperlinks.

Trace File Browser (Toad DB Admin Module and Toad DBA Suite)

Toad has always provided the means to view Oracle trace files (which can be generated using Toad’s Session Browser) using the TKProf Interface screen (Database menu | Diagnose), but once opened, you see the trace file contents displayed in “raw” format making it difficult to read and interpret.

Now with the new Trace File Browser, interpreting trace files is a breeze! In Toad for Oracle 9.7, the Trace File Browser is located immediately under the TKProf Interface menu item. Simply select and open your trace file (either directly from Windows or via FTP if on a UNIX server) and you will see the trace file information presented in a MUCH easier format with tabs showing statement details, wait summary and query summary, together with the execution plan, executions, fetches, waits and much more.

You can even send your reviewed trace file for workload replay testing using Quest’s Benchmark Factory for Databases (part of the Toad Development Suite and the Toad DBA Suite). I will discuss this more later in this blog posting.

Schema Compare and Sync (Toad DB Admin Module and Toad DBA Suite)

Perhaps one of the most popular Toad features for developers and DBAs is Schema Compare and Sync. I’ve included it in the DBA-focused blog because the “Sync” functionality is included in the DB Admin Module add-on (formerly DBA Module). Whereas, Schema Compare is available in every Toad for Oracle Edition.

Before I elaborate, there are two ways to perform a Schema Compare using Toad:

Using information stored in the Oracle Data Dictionary
Using native Toad Schema Definition files.

The first method uses “brute force” to obtain the DDL information pertaining to every object in each compared schema.

The second method uses what are called Schema Definition (or schema snapshot) files. These files are encrypted, compressed files which contain all the information about every object in a schema at a particular point-in-time. The real beauty of using these files is that, not only are they an accurate record of what a schema looks like at a particular point-in-time, but when used to perform a schema compare using Toad, they take a fraction of the amount of time to process. What would take minutes to process using a data dictionary compare only takes seconds using this method.

Schema Definition files have been available in Toad for many years, but in this release, each Schema Definition file created is unique – with the file name, including a date/time stamp, so you can see exactly when it was created.

Since Schema Compare is bi-directional, it’s absolutely possible to undo (rollback) a previously applied change. This means that Toad for Oracle provides some capabilities of a basic change management tool. I must point out however, before you get too carried away, that it’s advisable to only use this feature to synchronize changes between schemas in a NON-production environment!

The Schema Compare window also looks different in this release. It’s now much easier to view all of the target schemas (live connection and schema definition files) against those with which you want to compare – in a single window. The synchronization script (see below figure) can be used to sync the entire schema, or even a single object.

Furthermore, this entire operation can be replayed over again using our new automation console: Toad AppDesigner, which will be discussed in a subsequent section of this blog.

To clarify, with all Toad for Oracle Editions, you can perform a standard Schema Compare using the Oracle Data Dictionary. With the DB Admin Module add-on, you have the ability to use Schema Definition files (for performance and accuracy) and perform synchronizations.

Database Health Check (Toad DB Admin Module and Toad DBA Suite)

We’ve enhanced the database health check and security reporting tool for greater usability. The Report Output tab is now easier to navigate. By collapsing the main headers, you can view as much detail as you wish. There is also a new tab called “Diffs Since Last Run” which enables DBAs to quickly see if something has changed since the Health Check was last run – it will be flagged.

This is much more useful than the full report because there is so much information. Spotting a specific change – such as a database security breach – is now easier. As you can see in the figure below, two users have been granted DBA privileges as well as “Unlimited Tablespace” since the report was last run. This may have been overlooked within the main body of a full report.

Workload Replay Testing (Toad DB Admin Module + BMF and Toad DBA Suite)

Above, I mentioned the possibility of performing a workload replay test on an Oracle trace file. This is a popular method by which testers and DBAs can anticipate the impact that user load has on their production databases by simulating realistic load levels in a test environment.

Using Quest’s Benchmark Factory for Oracle – either standalone or with the Toad Development Suite and the Toad DBA Suite, an Oracle trace file can now be identified, interpreted and sent to Benchmark Factory for workload replay testing.

This integration is implemented in two places: (Database menu | Diagnose)

TKProf Interface
Trace File Browser (described above)

Once the workload replay script is generated, you have the option of immediately launching Benchmark Factory to run the test. The script can be opened directly from Benchmark Factory later if you wish.

Once Benchmark Factory launches, the workload replay script can simply be executed (which replays the trace file transactions with one connected user), or it can be copied to a scalability test – where additional “virtual users” can be added to simulate multiple database connections. Or, you can use a goal-based test – where you can specify a maximum TPS (transactions per second) or maximum user load based on a pre-defined response time.

More blogs and Technical Briefs on Benchmark Factory can be found here in Toad World.

Toad AppDesigner (Toad Base)

If you have tried Toad’s Action Recall (highlighted in the last blog on Toad 9.6), then you’ve already experienced productivity gains as a result of automating the steps you take in Toad to perform specific, repeatable tasks – either as a DBA or a developer.

In order to fully capitalize on this concept and create automated workflows, the Action Palette (which is now called AppDesigner) is now a full window to enable you to better visualize workflows, enable more tasks to be automated and combine different tasks using conditional logic.

As the name implies, AppDesigner enables Toad users to easily create mini-Toad applications based on tasks that would normally require multiple, repeatable steps in Toad. Toad AppDesigner can be used by any type of Toad user, but I’ve detailed a DBA specific use case below just to give you an idea.

Let’s imagine, as a DBA, you have to regularly run a database health check and generate an HTML schema script on all your databases, say, once-a-month. Even using Toad, this can become quite laborious! So imagine that, all you could simply select a Toad App (macro) which not only performs this procedure, but also drops you an e-mail to let you know it’s done.

OK, check this out!
First, open the Toad AppDesigner window (main toolbar )

In the left panel, you’ll notice an area, called Apps, where completed Toad Apps can be viewed and run. There is also an Execution Log area to keep a record of all Toad App executions and a Scheduled Items area to specifically itemize all those Toad Apps you have scheduled.

In the right panel, a series of tabs which lets you select App “components” which can be placed sequentially to form a continuous series of operations. These include:

Import/Export – export data from a table to external format, import from external format to Oracle, etc
DB Misc – schema compare, health check, script execute, etc
Utilities – FTP, TNS Ping, Windows services, etc
File Management – Create directories and files and manage them
Control – conditional logic operators such as If..Then..Else, etc

If I expand my “Toad App 1” example, you can see it is made up of a selection from the above.

I’ve used conditional logic to first TNS Ping a database instance (I could have compiled a list of database instances from which to read) and perform the steps as listed if the database is up. I send an e-mail if the server is down. If not, complete the steps and send an e-mail once complete.

The properties of each step can be defined by right-clicking an item in the list and selecting Properties or they can be defined from the corresponding window in Toad (e.g. DB Health Check).

To define the step’s properties from the task window, open the Actionable Window, complete all the necessary information, then press the in the lower left corner of the window to store as an Action (or Window Snapshot).

To define the step’s properties from within the Toad AppDesigner window, right-click the step and select “Properties.” For example, here are my properties for the DB Health Check and e-mail steps:

Once you have all of the steps complete (you can change the running order if it’s wrong), you can test your Toad App to make sure all the steps run correctly using the Run Selected App button on the main Toad AppDesigner toolbar.

After confirming that it all works, you can even schedule the Toad App to run at a time and frequency of your choosing, using the Toad App Schedule button . This uses the Windows Task Scheduler to launch Toad on your desktop and run your chosen Toad App.

Toad Apps can be shared with others very simply, and the recipient can import a Toad App into their Toad AppDesigner window and use or modify it. This is done using the Import from file and Export to file buttons.

If you get the chance to try out Toad AppDesigner, please be sure to let us know what ideas or experiences you have. We hope it will be tremendous fun and help save you hours of work.

For a comprehensive list of new features and enhancements in Toad 9.7, please refer to the Release Notes.

Look out for Part 2 of this Blog coming to Toad World very soon. I’ll cover new enhancements and features for developers.

Toad...and Java??!!

John Pocknell — Tue, 22 Jul 2008 17:09:00 GMT

I thought that’d grab your attention!

We know there are a lot of PL/SQL developers out there who are looking to make (or have already made) a break into application development using a more popular programming language like Java as well as many Java developers who use Toad to make access to Oracle easier.

According to a 2006 IOUG survey of Oracle sites, the 3^rd most widely used language after SQL and PL/SQL (77%) was Java (38%). http://www.ioug.org/Research.pdf

So – what does Toad do for Java?

Well actually, quite a bit, and mostly it’s focussed around performance. Let’s face it, if you are a Java developer and you haven’t come from a SQL or PL/SQL background, accessing the Oracle database efficiently is going to be a struggle where you depend on writing SQL statements to query data.

In this blog, I want to highlight one of the main areas where Toad can assist you in the Java world to give you the confidence you may be lacking in Oracle – which is:

Can Toad help me write better performing SQL in my Java ?

The answer is a definite yes. This is perhaps where Toad will probably be of most help to Java developers. The Toad Xpert edition includes our SQL tuning technology called SQL Optimizer.

Let’s assume I have a SQL statement which I’ve either written in the Editor or built using Toad’s Query Builder. You could add a Java wrapper and embed the SQL straight into your Java source, but first, you want to make a quick check that the SQL will perform well first.

Here’s a typical SQL statement, viewed in the Editor.

I can quickly get an impression of whether there’s a problem by performing an Explain Plan – which shows the expected execution path and steps which Oracle will take in order to execute the SQL as efficiently as possible. Press the button on the Editor’s toolbar. The TABLE ACCESS FULL on the EMPLOYEE table indicates that Oracle has to search across every record in the table instead of using an Index. This is very inefficient. Let’s see if we can make this statement perform faster.

Press the SQL Optimizer button on the Editor’s toolbar . A pop-up window will ask you into which window you wish to open the SQL Optimizer. Click OK to accept the default of “With the Tuning Lab”. To save time, you can also check the “Set as default” check box.

The Tuning Lab is typically used to tune ad-hoc SQL whereas the Batch Optimizer is typically used to bulk-tune multiple SQL statements, scripts, PL/SQL programs, etc. You could also use this if you had, say, hundreds of Java script files which contained embedded SQL statements.

Now, simply press the Optimize button () and let the SQL Optimizer do its thing!

Quest SQL Optimizer

You can see that the SQL Optimizer has found a number of possible re-write alternatives which may offer an improvement over the original. Although the Oracle Plan Cost, an indicator of the expected performance when the statement is executed, shows some statement re-writes with lower values than others, the only true way to perform a comparison is to go ahead and execute the statements. To save time, you can select which you want to execute or execute all of them.

In this case, I selected the top 10 and pressed the Execute Selected button. On the left of this screenshot, you’ll notice some Layout buttons. Press the Execution Statistics layout button to see the execution results compared graphically.

In the Scenario Explorer, the light blue bar shows the execution of the original statement. Light green bars indicate those re-writes where execution performance was improved. Dark blue indicates no change. Press the Resolution layout button and you can see that within a few seconds, I managed to achieve a performance improvement (elapsed time) of 14.83 times better than the original using alternative #7. Pretty impressive!

If you press the Compare Scenarios layout button, you can compare alternative #7 with the original SQL. This is what the SQL Optimizer did:

So now what I need to do is to lift the re-written SQL statement back into my Editor. Press the SQL Details layout button and select Alt #7 so it’s visible in the SQL Text window. Now press the button to return this re-written SQL statement to the Editor.

Alt #7 returned from the Quest SQL Optimizer

Now use the “Make a non-SQL code statement” button (Editor toolbar) to add the Java wrapper and presto!, you are ready to run your Java code with a far more efficient SQL statement.

Alt #7 with Java wrapper ready for insertion into Java source

Finally, I mentioned earlier that you may have Java files with embedded SQL statements and you want to assess them to see if there are any potentially poor performing SQL statements amongst them. You may, for example have Java code where the SQL has been generated using Hibernate (used for mapping Classes to database tables).

This is where the Quest SQL Optimizer’s SQL Scanner comes in useful. Press the SQL Scanner tab in the SQL Optimizer main screen and create a new Scanner Job. Click the Source Code link on the left and add all your Java files.

SQL Scanner

Now click Finish. Your Java code will be listed in the SQL Scanner window ready for scanning.

Push the button to begin scanning the embedded SQL. The results will be displayed as below and any SQL statements ranked according to Problematic, Complex, Simple or Invalid. This ranking system quickly enables you to assess which SQL statements are the worst, so that you can proceed with the optimization.

Selecting the appropriate Optimization button will go ahead and begin optimizing the SQL as described above and allow you to return the optimized SQL into the Java source code.

The Quest SQL Optimizer is designed to be both effective and simple to use, without any prior knowledge of how to tune SQL and should be an extremely valuable tool for Java developers concerned about poor SQL performance in their application.

Quest Software also provides a full suite of Java tools too and one, in particular, which is the perfect complement to Toad Xpert with SQL Optimizer for SQL tuning, is JProbe.

JProbe is an enterprise-class Java profiler providing intelligent diagnostics on memory usage, performance and test coverage, allowing developers to quickly pinpoint and repair the root cause of application code performance and stability problems that obstruct component and integration integrity.

JProbe also offers an Eclipse plug-in that provides intelligent code performance analysis and problem resolution directly within the Eclipse Java IDE.

For more information on Toad for Oracle Xpert (with Quest SQL Optimizer) with a free 30-day trial, please click this link: http://www.quest.com/toad-for-oracle/features-benefits.aspx#3

And for more information on JProbe, visit the JProbe user community. Additionally, there are two download options for you to get your feet wet. JProbe offers either a free 10-day trial that is a fully functional product and the JProbe Freeware which is an Eclipse-based memory analysis tool that leverage’s JProbe’s superior data visualization and investigative tools to analyze and diagnose heap dump memory issues.

Here are the links to download each product:

Toad and Oracle Licensing – What’s the Story ?

John Pocknell — Thu, 26 Jun 2008 14:28:00 GMT

Toad for Oracle may use, but is not dependent upon, Oracle database options such as RAC, Partitioning and Spatial and Enterprise Management (OEM) Packs such as Tuning and Diagnostics depending on various factors. Such Toad features include (but not limited to) ADDM/AWR Reports and AWR Browser which are part of the optional DB Admin module add-on.

Some features in Toad which appear to do what Oracle does, DO NOT actually use any Oracle features such as Schema Compare & Sync and Quest SQL Optimizer (included with Toad Xpert, Development Suite and DBA Suite).

This Blog sets out to clarify what Toad does and does not use, for those of you who are unclear and concerned about Oracle database licensing, especially given the (16 June 2008) announcement by Oracle of price increases between 15% and 20% across the board in their infrastructure and applications lines.

http://www.oracle.com/corporate/pricing/technology-price-list.pdf

Background

Before I start, let’s first understand what Oracle provides according to what database Edition you are connected to.

Some features in Oracle are more generally available such as Partitioning and Spatial and are options chosen by a DBA when they install the Oracle Enterprise Edition database.

Real Application Clusters (RAC) is available in both Standard (default) and Enterprise Editions (optional).

If you are using Oracle database Enterprise Edition, you may have licensed additional Enterprise Management Packs such as Diagnostics, Tuning, Change Management, Configuration Management, etc.

Please bear in mind that, by default, upon installation, the Oracle Management Agent enables these packs without any regard to what you may have licensed. Your DBA needs to de-select unlicensed packs as necessary after installing the agent on a target database.

If you have Oracle database Express or Standard (inc Standard One) Editions, options like those mentioned above are not available.

If you are not sure what you have available and want to check for your Oracle database, use this link : http://www.oracle.com/database/product_editions.html

Toad for Oracle Editions

OK, so now that’s clear, let’s look at how Toad utilizes this technology.

As you may already know, there are different editions of Toad for Oracle designed to meet the needs of specific individuals or groups:

Toad Base – a.k.a. Standard (inc Knowledge Xperts + Debugging) - essential Toad features
Toad Professional (Toad Base + extra features) - focussed on coding best practices
Toad Xpert (Toad Professional + SQL Optimizer) - focussed on application performance
Toad Development Suite (Toad Xpert + Quest Code Tester + Benchmark Factory for Oracle) – end-to-end Oracle development solution
Toad DBA Suite (Toad Xpert + DB Admin Module+ Toad Data Modeler + Benchmark Factory for Oracle + Spotlight on Oracle) – complete DBAs toolkit for administration, diagnostics and tuning

There is also an optional add-on called the DB Admin Module (formerly DBA Module), which contains most of Toad’s Oracle database administration features.

Does Toad need OEM?

No it does not.

In the optional DB Admin Module, Toad (8.5 and higher) offers 4 features which access the Oracle OEM Diagnostics Pack. These are:

ADDM/AWR Reports – enables snapshot management and shows both AWR and ASH reports
AWR Browser – graphical representation of data collected from AWR
Segment Advisor – determines space that can be reclaimed
Undo Advisor - Provides advice and helps to automate the establishment of the database undo environment.

You do not have to use these features if you don’t want to and we make it easy for you to disable them in Toad. Toad or the DB Admin module will not stop working if you disable them.

Toad does offer alternate ways to determine performance bottlenecks without the use of ADDM/AWR such as the StatsPack Browser, TK Prof interface, Database Monitor, Session Browser and the Quest SQL Optimizer’s SQL Inspector to name a few. However, if you are already licensed to use the OEM Diagnostics Pack and you wish to get additional value from it, you can use the functionality we offer in Toad.

If you are on Oracle database 10g and above, you can also use the Oracle Tuning Advisor feature in Toad Base Edition (9.6 and higher) which will access both the Oracle OEM Tuning Pack and Diagnostics Pack.

Alternatively, you could use Quest SQL Optimizer for Oracle (included with Toad Xpert, Development Suite or DBA Suite Editions). SQL Optimizer does not access any of the Oracle OEM Packs at all. In addition, Quest SQL Optimizer offers a unique way to rewrite your SQL code for maximum performance. Click this link to access our SQL Optimizer Community site which has lots of resources available for learning how to use this powerful tool. http://sqloptimizeroracle.inside.quest.com/index.jspa

How does Toad use Oracle functionality?

The degree to which Toad will use specific Oracle features depends upon a number of factors:

Which version of Toad you have (e.g. Oracle 10g features only appeared in Toad version 8.0 (July 2004)
Which Edition of Toad you have (see above)
Which version of Oracle database you are connected to
Which options were selected/deselected when/after Oracle database was installed
What Oracle Role/System Privileges you have granted

If you are using RAC (Standard or Enterprise Editions), there are a number of windows in Toad which will use this technology: Database Browser, Session Browser, Oracle Parameters, Top Sessions and Database Probe (to name a few).

Toad’s Oracle Parameters window for RAC

Likewise with Partitions; Toad has a number of windows which will use the Oracle Partitioning option in Oracle database Enterprise Edition, such as Create Table and Alter Table.

Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository were both introduced in Oracle 10g.

The Toad Help states which Editions support which Oracle technology: (as a tip, type in “10g” or “11g” into the Toad Help Search tab to reveal what Toad features are available)

Does Toad use the OEM Change Management Pack?

Toad has a Compare Schemas feature (available in any Toad Edition), which is very popular with Developers and DBAs alike, which lets you compare 2 schemas either using the Oracle Data Dictionary or a pre-generated Toad Schema Definition file (DB Admin module only) and produce a report. A synchronization script can be generated (read-only except DB Admin module).

This feature does not use the Oracle OEM Change Management Pack.

Does Toad use OEM Real Application Testing (RAT)?

Toad DBA Suite includes a component called Benchmark Factory for Databases which is a client-side database server performance testing tool. Together with Toad, this enables a DBA to generate an Oracle Trace File which contains a recording of database activity which Benchmark Factory can replay in a different environment such as a test database. Benchmark Factory replays the sessions maintaining concurrencies and transactional latencies. It basically replays exactly what happened in the original database environment including SQL and bind variables.

This technology does not use Oracle 11g’s RAT technology.

How can I disable the features in Toad that use OEM functionality?

In Toad for Oracle 9.7, you can disable any window in Toad via the Options window, to prevent inadvertent access to Oracle’s Options and Management Packs.

In Toad 9.6, you can use the Customize feature to remove items from the menu and toolbars. To do this, simply right-click on the menu (just like other Windows applications). A pop-up will appear and when you then click on the toolbar or a menu stack, you will be able to drag and drop items from there into the pop-up to remove them. Customize is also available in older versions of Toad but the process is a little different.

There is a section in the Toad Help which explains how to disable Toad’s ADDM/AWR Reports, AWR Browser, Segment Advisor, Undo Advisor and Oracle Tuning Advisor windows if you are concerned about access to OEM.

In the next release of Toad (version 10.0), disabling of windows which use OEM Management Packs will be a process which happens during installation or upgrade and will enable you to specify which Toad windows you wish to enable (since they will be disabled by default).

Does Toad provide any warnings about the use of OEM functionality?

Yes. Toad’s license agreement and Help (Toad 9.6) already provide warnings.

In Toad for Oracle 9.7, a pop-up window appears when Toad runs for the first time which advises you about the possibility of accessing Oracle features. This warning is also visible in the Toad Help:

In the next release of Toad, users will be notified during installation/upgrade, which specific windows that use OEM Management Packs are affected, and how to enable after installation/upgrade. These windows will be DISABLED BY DEFAULT.

If you are still not sure about how Toad uses features in Oracle, please send me an email and I will be happy to clarify.

Additional reading:

Mike Ault’s Toadworld Blog – “Landmines” http://toadworld.com/Community/QuestExpertsBlogs/tabid/67/EntryID/207/Default.aspx

John Pocknell – john.pocknell@quest.com
Product Manager – Toad for Oracle Solutions

What’s Coming in Toad for Oracle 9.6? (Part 3)

John Pocknell — Mon, 17 Mar 2008 14:52:00 GMT

It’s almost that time again as another exciting new release of Toad draws closer !

We’ve decided that, rather than let you wait until this release of Toad before you see what’s new, we’ll give you a little fore-taste now !

The emphasis on this release is to simultaneously continue to add value through enhanced individual and team-oriented productivity and automation, increased support for improving code quality, robustness and performance as well as continuing to improve product performance and stability.

In this blog, I want to first introduce some changes to the Editor; specifically what we call Toad Insight or what you may refer to as “dot lookup”.

For those who have no idea what I’m talking about, if you type a SQL statement in the Editor and you’re not sure (for example) which table columns you want to specify, after you type the table name followed by a period (or dot), Toad will open a pop-up panel which lists all the columns for that table, allowing you to select which one(s) you require.

You can also use Toad Insight to help when you’re using unfamiliar Packages and you’re not sure what the arguments are, or the correct order.

Previously, this operation was limited to the schema to which you were connected. In 9.6, we (optionally) allow you to cache objects from other schemas allowing you to perform lookups from other schemas. In this example, you can see that, because I have connections to both the QUEST_PROD and QUEST_OPTI schemas, I can lookup objects from both within the Editor window.

There are some new options which allow you to control how this works. For example which object types you wish to have cached. Go to the Editor options and look for Code Assist and you’ll see a new panel called Toad Insight Objects. You can uncheck object types you are not working with and thereby reduce the cache size.

For some time now, you’ve been able to launch other Quest and Windows applications from the Toad toolbar using the Configure/Execute External Tools feature.

In this release, however, we’ve added more workflow integration like we have right now with the SQL Optimizer and Benchmark Factory for Databases.

We’ve integrated two more Quest products – Quest Code Tester for Oracle and Toad Data Modeler (must be Toad Data Modeler version 3.1 or higher).

Quest Code Tester for Oracle is the first and only automated PL/SQL code testing tool available. Created by one of the world’s most prominent Oracle PL/SQL experts, Steven Feuerstein, Quest Code Tester for Oracle delivers practical and thorough code testing.

For more information on Quest Code Tester for Oracle, please click this link :

http://www.quest.com/code-tester-for-oracle/

Quest Software's Toad® Data Modeler is a cost-effective, yet powerful database modeling and design tool that is built for the individualdeveloper, DBA and data architect.

Toad Data Modeler makes it easier for you to build complex entity relationship models (both logical and physical), synchronize models, generate complex SQL/DDL, create “ALTER” scripts (Oracle only), and reverse engineer legacy databases.

For more information on Toad® Data Modeler, click this link: http://www.quest.com/toad_data_modeler/

If you have these products already installed on your desktop, Toad 9.6 will auto-detect them so there is nothing you have to do. Let’s look at a couple of use cases where this integration will be extremely useful.

Let’s say you’ve used Quest Code Tester for Oracle and created some test definitions for some of your PL/SQL code and run those tests. Your test definitions and test results are now stored in Code Tester’s repository for safe-keeping.

Now, you’re in a Toad session and you’ve made some alterations to one of those PL/SQL programs that you’d previously tested, and now you’d really like to be able to quickly run your test again without having to launch Code Tester. Well now you can !

Simply right click the code in Schema Browser and select Run Code Tester tests

Please note that Toad will determine, by looking in the Code Tester repository, whether a test definition exists for your program. If it does not, the menu option is not visible.

If a definition does exist, Code Tester will execute (without launching the Code Tester GUI) and your test results will be displayed in the Toad GUI.

A Code Tester button on the results pop-up will enable you to launch Code Tester at this point should you want to alter the test definition, create more test definitions, etc.

If you can think of any other use cases which make sense which enhance workflow between code development and unit testing, please let me know by adding a comment to this blog.

The other scenario I want to discuss is where there are developers, DBAs or their managers who need access to the project data model.

Having access to the current project data model is absolutely essential if development is to build an accurate application schema which respects the needs of the business and its end-users. Normally, this process is problematic since the company usually uses an “enterprise” modeling solution which is beyond the ease-of-use of most developers and DBAs so that you are then reliant on a database designer or architect to provide you with a current diagram of the model.

The requirement may be just to view the model, or to make changes to it, depending on the user’s specific responsibilities. If it’s just to view the model, this can be achieved very simply by using Toad Data Modeler’s HTML report generator. This operation would have to be carried out by somebody authorized to access the data model. Once the report is saved (to a Windows network drive, for example), this HTML report can be linked to all Toad users working on the project using Project Manager (for information on what Project Manager is and how to benefit from it, please click this link : http://www.toadsoft.com/haveumet.html ). All Toad users on this project now have access to the data model through Project Manager.

However, let’s say you need to directly Reverse Engineer objects from Toad into a Physical data model.

In Toad 9.6, this is now possible using the ER Diagram window. The ER Diagram window has been part of Toad for a number of years, and often, users mistook it as a modeling tool. Well now, you can have the next best thing !

I’m going to open the ER Diagram window (Database | Report | ER Diagram) and drag and drop some objects from the Object Palette (View | Object Palette) into the drawing view.

Next, I press the Save button and save the diagram as a .erd file in the C:\Program Files\Quest Software\Toad for Oracle\User Files\ERDiagrams folder. Once the diagram is saved, the Toad Data Modeler button becomes active on the ER Diagram toolbar .

Press this button and Toad Data Modeler launches (remember this must be version 3.1 or higher). The Toad database connection is passed across so you only have to enter the password and Reverse Engineering of the objects in your ER Diagram initiates automatically.

Once completed, you will have a brand new Physical data model ready for you to work on in Toad Data Modeler.

In addition to this integration, you also have integration inside Toad Data Modeler 3.1 itself.

One example is the ability to perform Reverse Engineering “on-the-fly” by dragging and dropping objects from a Project Manager panel in Toad Data Modeler (which looks exactly like Project Manager in Toad).

First, you open the required Toad Project Manager file from the file system.

Toad Data Modeler opens the default Toad User Files folder where the Project Manager files are normally stored (they can also be stored on a network share).

Select the Project Manager (.tpr) file you want and it will open inside Toad Data Modeler.

You can even set the icon styles for the objects to be exactly how you would see them in Toad. Now, the REALLY COOL thing is, you can drag and drop objects from the Project Manager panel into an existing Physical data model (provided the diagram window is open) and Reverse Engineering will be initiated automatically and entities created within the model.

So, if you really want to combine the benefits of using Toad and Project Manager to group all the project assets in one place, and share it across your team together with the ability to maintain your project’s data model, you can do it. Project Manager maintains a current list of “links” to the objects in Oracle’s Data Dictionary, so as you request object information via Project Manager, you’re looking at the latest snapshot of that object.

If you can think of any other use cases which make sense which enhance workflow between code development and data modeling, please let me know by adding a comment to this blog.

There are many other features coming in Toad for Oracle 9.6 which I haven’t had the space to include such as more support for Oracle 11g, update to the Import Table Data Wizard, more support for the Windows Task Scheduler but if you want to find out more, go to the Toadworld Home Page and look under the What’s New. There’s a Get To Know Toad 9.6 tutorial which walks you through a lot of the other new features.

Enjoy !

What’s Coming in Toad for Oracle 9.6? (Part 2)

John Pocknell — Fri, 07 Mar 2008 19:10:00 GMT

It’s almost that time again as another exciting new release of Toad draws closer !

We’ve decided that, rather than let you wait until this release of Toad before you see what’s new, we’ll give you a little fore-taste now!

In this blog, I want to walk you through two enhancements to some existing functionality. The first is the Health Check.

First introduced in 2002 with version 7.3, the Health Check is a reporting tool which reports on the health of your database instance and schema. Health Check is found under the Database | Diagnose menu.

For this release, we have added a new category called Vulnerability Assessment which looks specifically at potential security breaches in your Oracle database through the user accounts (things like - which users have the DBA role granted, where you have database links which use passwords and where you have Oracle or 3^rd party user accounts which are still using the default password)

Once the window is opened, you can select which database(s) you want to check in the database list in the left “Databases:” panel. You then select which checks you want to make. For the Schema checks, click the Schemas tab and select which schemas you wish to check.

If you want to preserve the Health Check results (for baselining and comparison later), Check 5 lets you store the results in a table called TOAD_HEALTH_CHECK in the TOAD schema.

Some of the checks allow you to adjust the parameter settings. For example, Check 84 lists any insecure Oracle Initialization Parameters. You can choose which ones you wish to check by clicking on the word “adjust” and pressing the button to open the appropriate dialog.

OK, so let’s assume you’re a DBA and you want to assess your database/schema health and run the VA checks once a month. Well, you can schedule the whole thing. You can specify what format you want the report in (HTML, RTF or Text), then click the Schedule button and open the Windows Task Scheduler dialog. Simply follow the steps to create the Scheduler task and you’ll automatically have your Health Check reports created for you.

The next enhancement I want to show you is to the PL/SQL Profiler. This feature has been in Toad for many years and is available in the Base edition (we also added the Hierarchical Profiler in the 9.5 release for those of you on Oracle 11g).

The PL/SQL Profiler lets you see where you might have a performance bottleneck in your PL/SQL code by timing how long each line of code takes to execute and how many times loops iterate.

For the 9.6 release, we’ve provided better interaction between the analysis window and the Editor window and improved the flexibility of the reports.

There’s a new “Details” button in the Profiler tab which gives you much easier access to the Profiler Analysis window. Selecting a line item in the expanded list in the Profiler tab highlights the line of code in the Editor.

There are also more display options for the graphs. These include being able to rotate the graph (pie or bar chart) by 90 degrees.

Look for Part 3 of this Blog coming to Toad World very soon, where we’ll examine some exiting changes to the way the Editor Insight works, new integration points with other Quest products and lots more !

What’s Coming in Toad for Oracle 9.6 ? (Part 1)

John Pocknell — Thu, 28 Feb 2008 23:23:00 GMT

It’s almost that time again as another exciting new release of Toad draws closer !

We’ve decided that, rather than let you wait until this release of Toad before you see what’s new, we’ll give you a little fore-taste now !

Before we get into the features, there are a couple of significant things just to get you warmed up ! The first is that the Toad Debugging functionality is moving from the Professional configuration to the Base (or Standard) configuration. This not only includes the standard (DBMS) debugging, but also the JDWP and External (formerly Just-In-Time) debugging. We’re also moving the Knowledge Xpert for PL/SQL and Knowledge Xpert for DBA to Base (Standard) from the Professional and DBA Module respectively.

Also, the Oracle Import and Export Utilities, currently only available in the DBA Module, will be added to Professional. We will continue to add more functionality to the Professional configuration this year.

By the way, a name change. The DBA Module (first introduced in December 2000 with v6.5) is to be called the DB Admin Module. Why ? Because we found that many people thought that the DBA Module was for DBAs whereas anyone who needs to administer an Oracle database can use it. It has a ton of stuff to simplify the administration of an Oracle database. So by making the name fit the function rather than the job title, we’re hoping more people will benefit from it.

Customization

The first feature I want to introduce is customization. Customization is not new in Toad, as some of you may already be aware. There are, in fact, a number of places in Toad where customization is possible ranging from the toolbar visual style, the Schema Browser through selection of tabs, tree-view, etc as well as the main toolbar and menu system.

In 9.6, we’ve made all of this really easy, right from when you upgrade (or install) your Toad.

There is an enhanced Initial Setup Wizard which includes a number of new options which enable you to decide how you want Toad to look and work for you.

These also include which window you wish Toad to open first (and for every connection thereafter) such as Editor, Schema Browser, etc.

Once you get into Toad, you can easily customize the toolbar or menu by simply right-mouse-clicking the toolbar or menu and selecting Customize.

The Customize dialog enables you to make a number of changes to both the toolbar and menu.

I’ll start with a very simple example based on something a customer said to me a few years ago. He said that he sometimes wished the Commit and Rollback buttons on the toolbar were separated so there was less chance he would inadvertently press the wrong one (which he had done on more than one occasion!)

OK, so right-mouse-click the toolbar the open the Customize dialog.

Now simply select the Commit button with a left-mouse-click and drag it to somewhere else on the toolbar. It’s that easy !


BEFORE		AFTER

You can even include text on the button if you so wish. With the Customize dialog still open, right-click the Commit button on the toolbar. Select Image and Text and watch what happens to the button. becomes

Now let’s take a look at the menus. Let’s say you want to just show the things that you, alone, use.

I’m going to pick the Utilities menu as an example and let’s say my Oracle database is on a Windows server and I never use Java.

I can use Customize to “clean-up” my Utilities menu. Again, with the Customize dialog open, click the Utilities menu. It will expand, but you won’t be able to run anything. Drag and drop the following into the Customize dialog box to remove: FTP, Java Manager, Network Utilities and Unix Job Scheduler


BEFORE		AFTER

Looks a bit simpler doesn’t it ! I can also move items within the menu up or down the list according to how often I use them. (drag and drop again)

You may be a Development Manager and want to standardize on a specific Toad toolbar/menu configuration for your team. The good news is that all these settings changes are preserved into a file called toolbars.ini which is in the User Files sub-folder of your Toad installation directory.

You can save this file into your team’s network share folder and register it with Project Manager (along with all your other project assets) so that they can copy over their own toolbar.ini files.

By the way, if you’re not sure what Project Manager is, open it ( on the main toolbar) then press F1. This is a very powerful asset sharing system for sharing database objects, files, folders, web pages and a lot more.

OK, so let’s say I’ve customized the hell out of my Toad toolbar and menu and something changes and I need to get everything back I got rid of or moved. For example, the DBA has now moved the Oracle database onto a UNIX box and so I need all those fancy UNIX features back !

Simply right-click the toolbar again and select Restore Defaults and, voila, everything reverts back !

Database Browser

The Database Browser has been significantly improved for 9.6 and combines the benefits of the original Database Browser, with its ability to get information about different database in one screen, with the Schema Browser, with its ability to dive into the database and schema objects.

Back in January, Bert Scalzo wrote a blog detailing the new Database Browser – here’s the link..

http://toadworld.com/Community/ExpertsBlog/tabid/67/EntryID/165/Default.aspx

AWR Browser

Another new feature in the release is the AWR Browser. This window is similar to the StatsPack Browser launched in 9.5 but collects data from the AWR (Automatic Workload Repository) instead.

Once you’ve set up your Snapshot management in the ADDM/AWR window and defined your snapshot intervals (Oracle default is every hour), you’re good to go.

Please note : Toad’s AWR Browser uses Oracle’s AWR, which is part of the Diagnostics Pack, so please check you are licensed to use this first !

Watch for Parts 2 and 3 of this Blog coming to Toad World very soon, where we’ll examine some exiting changes to the way the Editor Insight works, enhancements to the PL/SQL Profiler, new Vulnerability Assessment checks, new integration points with other Quest products and lots more !

Simplifying Database Administration – Part 1

John Pocknell — Wed, 10 Oct 2007 22:57:00 GMT

Problem Resolution

For those of you who read my previous blog from last week, or read the recent White Paper entitled “Simplifying Oracle Database Administration” written by Mike Ault, Bert Scalzo and myself, you’ll have seen from the survey carried out by James F Koopmann that about 20% of a typical DBAs time is spent fire fighting and responding to user related problems. Some production DBAs I have spoken to put it even higher.

So, here is a process which, I’m sure many of you follow; essentially detect (or discover) that a problem exists, diagnose the root cause of the problem to understand what needs to be done and finally resolve the problem using appropriate methods, or the classic “top down” approach.

I’m going to briefly walk you through the minimum steps necessary using Toad’s DBA Module to achieve this simply, rapidly and effectively.

1. Detect

If your telephone is already ringing off the hook when you get to the office, it is essential that you have a systematic, repeatable approach to determining the root cause of a stoppage or performance slow-down which any of your team can follow.

1.1 Monitor the OS to see if something is running external to Oracle
(batch jobs, etc) – it is essential that this is ruled out first before
looking at the database.

Here’s an example of the Unix Monitor showing activity on a Linux operating system while executing an industry standard TPC-C Benchmark (see Figure 1)

Figure 1 – Monitoring the Linux operating system

The graph on the right shows both IO devices have heavy activity and the process list shows both the Oracle DBWR (database writer) and LGWR (log writer) processes are top resource consumers. Both CPU Usage and Process Queue are quite reasonable – and thus not an issue.

Toad’s Unix Monitor works for Linux, Solaris, AIX, HP-UX and Tru64.

1.2. Monitor the Oracle instances on the server to determine which is
the one with the problem

Toad’s Database Browser lets you report on multiple instances to assess which one has the problem. (see Figure 2)

Figure 2 – Monitoring multiple Oracle instances

Focus on one instance and:

1.3. Check the server, database and TNS Listener are running (see
Figure 3)

Figure 3 – Monitoring the Oracle Server, Listener and Database

Toad pings each server, TNS pings the selected databases and checks to see if the TNS Listener process is running and provides simple up/down indicators.

1.4. Monitor that instance for problems (see Figure 4)

Figure 4 – Monitoring key Oracle Database metrics

            1.5. Check Alert Logs – Toad has an FTP interface if your alert logs are
                   stored on a UNIX or Linux server.
            1.6. Verify Rollback Segments are online – Toad’s Schema Browser will
                   give you a quick indication of this.
            1.7. Check ADDM and AWR for instance performance problems –
                   check-out Toad’s Reports menu for a list of these and other
                   reports.
            1.8. Examine Top Sessions for resource usage (see Figure 5)

Figure 5 – Determining Top User Sessions by CPU consumption

1.9. Monitor specific sessions (Long operations, SQL Statement
                   execution, etc) – Toad’s Session Browser will relate the SID
                   to a user to show full session data including the SQL statement
                   the user is executing.
            1.10. Monitor Tablespace usage for fragmentation, etc (see Figure
                    6)

Figure 6 – Monitoring Tablespace consumptio

2. Diagnose

Having determined where the issue is, you now need to quickly and effectively diagnose the exact cause of the problem and get user(s) up and running again.

In this screenshot of Toad for Oracle’s Database Probe window (see Figure 7)), you get real-time display of instance activity with alert markers indicating potential problems. Clicking on a marker displays a description of the problem – in this case excessive Redo Log Buffer size.

Figure 7 – Diagnosing an instance configuration problem

Another example might be slow database performance due to a user executing a very inefficient SQL statement (exposed by looking at top sessions by CPU for example). Having a view of all Oracle sessions with the ability to obtain information about each session (in this case the SQL statement the user is executing) enables a rapid way to resolve a problem.

Where the statement has already completed execution, you will need to look in the SGA or access the Oracle Trace files. (Database – Monitor – SGA Trace/Optimization or Database – Diagnose – TKProf)

In all cases, access to Toad’s SQL Optimizer directly from the SQL statement is essential in order to simplify the process of improving execution time. (see Figure 8)

Figure 8 – Diagnosing a users SQL execution problem

3. Resolve

Having diagnosed the root cause of the problem, whether it be database configuration, tablespace or rollback segment problems, or SQL execution performance, it is important to resolve the problem quickly and effectively.

Integration points in Toad for Oracle help enormously here (as you see in the screenshot above where SQL Optimizer can be launched directly from the diagnosis window.

In the case of a resolution which requires an improvement to the execution time for a SQL statement, this in itself may be time consuming and problematic.

Quest’s SQL Optimizer is a semi-automatic SQL and PL/SQL optimization tool which can be configured according to the business process being used. It is self-levelling in terms of the skill level of the person using it but, at all times, keeps you informed what it will do the database. (see Figure 9)

It is also batch oriented giving DBAs the freedom to let the tool optimize SQL according to rules they set without being involved, and also the manner in which the solution is deployed back into the application.

Figure 9 – Resolving a users SQL execution performance problem

Another essential characteristic which you will see in Toad’s DBA Module is workflow navigation. Tasks are presented in a clear, logical way – following a functional paradigm which makes sense and is easily repeatable without having to make notes !

Here is a sample of some of the menu features offered by Toad showing how they are offered in the functional paradigms of Administer, Compare, Create, Diagnose, etc (see Figure 10)

The order in which the menu items are presented can also be customized to suit a particular team – indeed, items could be removed or even an entirely new menu sub-system created to suit the workflow and range of tasks required.

Figure 10 : Administration Task presentation is clear and function based

It’s A Dirty Job – But Someone’s Got To Do It !

John Pocknell — Mon, 01 Oct 2007 18:32:00 GMT

There’s no doubt about it - being a DBA is not easy.

Yes, you might work very hard to try to keep things under control through scrupulous planning, dedication and long hours, but some business manager or application developer somewhere is always going to be unhappy with you when something breaks, data is lost or their app goes slow, and you’ll just have to take the blame for it when it happens.

This is a common occurrence in environments where everyone is under pressure to deliver to impossible deadlines, and even though the root cause of a problem is not always the database, the finger-pointing begins and you are the one who usually has to face the wrath!

Of course, the job of a DBA can also be very rewarding too with excellent salaries and lots of opportunities where you can make yourself look real good, praise is lavished upon you and that’s what keeps you going.

It’s also easy to fall into the trap that working hard is just what you need to do to get this done – but how about working smarter?

Have you often thought, what the heck does “working smarter” actually mean?

I found this really interesting article about what you can do to help “work smarter”, which can be applied to just about any job including that of a DBA. You see this sort of thing a lot with DBAs; they try to do everything, can’t say “NO”, afraid to delegate because they feel others can’t do the job as well as they can and then find their working time is dictated by the pace of other people’s demands.

One essential component to being able to work smarter as a DBA is having the right tools.

Let’s face it, your number one challenge as a DBA is trying to reduce the amount of “fire-fighting” you inevitably have to do when managing a production system.

Look at the chart below which shows how production DBAs spend their time. Look familiar?

It must be very frustrating when you have a gazillion other things to do (such as improving your backup/recovery strategy, working closer with the development DBAs, improving your knowledge of the latest and greatest from Oracle, etc) to have to continually react the other “stuff” simply because you are the “go-to” person and therefore expected to fix things that break.

If you had good tools, that your less experienced production DBAs and your development DBAs could use effectively to fix problems and encourage the formulation of consistent methodologies and processes, you’d me more likely to delegate more duties to them, therefore freeing up more of your time to plan your strategies, learn stuff, etc.

I recently wrote a White Paper entitled “Simplify Database Administration” which examines just this problem. Administering a production Oracle database is hard, especially when the telephone is ringing off the hook and people are queuing at your desk to ask questions.

This next series of blogs looks at simplifying database administration using the DBA Module in Toad^® for Oracle and shows how much more is achievable than perhaps you first thought.

Toad for Oracle DBA Module helps maintain database health and stability. It ensures the best possible efficiency and performance while minimizing the impact of changes to your database environment.

The DBA Module not only automates administration and reporting tasks, but it also provides a workflow that allows you to easily transition from one task to another. The Toad for Oracle DBA Module facilitates database administration tasks with a powerful browser-based view of performance information across multiple database instances, right down to the user session level. This module also includes a built-in Oracle database administration knowledge base – Knowledge Xpert for Oracle Administration. With the Toad or Oracle DBA Module, administrators can quickly:

Generate synchronization scripts, and compare and synchronize database schemas
Perform tablespace management and roll back segment maintenance
Schedule batch jobs and mine Redo Logs
Import or export Oracle operations and manage user sessions
Execute a 54-point health check to ensure the viability of your database instances

1. http://blogs.ittoolbox.com/database/solutions/archives/a-dbas-new-year-resolutions-6043

What’s Coming in Toad 9.1 - Part 2: Toad Group Policy Manager

John Pocknell — Mon, 18 Jun 2007 15:30:00 GMT

In Part 2 of this Blog, I want to introduce you to perhaps the most exciting feature of this release, the Toad Group Policy Manager – and it gives me particular pleasure to introduce this to you now as Toad for Oracle 9.1 is released.

The Toad Group Policy Manager provides a facility by which multiple copies of Toad for Oracle within an organization can share the same set of options. It consists of a Windows Service which runs on a Windows server and publishes subsets of option data to defined groups of Toad users via TCP/IP, and an Editor which is used to define option sets and user groups. Users can be restricted from changing published Toad options or permitted to alter them.

Using the Toad Group Policy Editor, policies and standards can be distributed throughout a group environment.

The Group Policy functionality consists of three parts:

· Group Policy Server

· Group Policy Editor

· Toad Policy files

The Toad Group Policy Manager is included in the new Toad MSI installer and provides an option for installing separately from the Toad for Oracle 9.1 installation.

The new Toad for Oracle 9.1 MSI installer

There is also a “What’s This” button which provides a summary of what Toad Group Policy Manager is. For a complete document which fully explains the architecture and how to set up and configure Toad Group Policy Manager, there is User Guide which is available both on the Toad for Oracle 9.1 installation CD and also from the Quest web site.

The following diagram will help explain the concept of how Toad Group Policy Manager works.

· When Toad for Oracle 9.1 starts, if it recognizes it is in a managed environment it calls in to the Policy Server, giving it its computer name or IP address.

· The Policy Server looks up the name, and if it is in a predefined group of managed users, the Policy Server creates a packet of information and sends it back to the client via TCP/IP.

Specify the Windows Server name or IP address

Specify the server location for the PDL file

Groups could be made up of development teams, DBA teams, analysts, etc, all of whom have differing ways in which they use Toad for Oracle and different ways in which they share common assets like formatting templates, version control system access as well as certain Toad options, etc.

Group Policy Editor being used to add new Groups and Users

The user lookup will allow you to browse the Windows enterprise for different users even if they are in a different Domain. Alternatively, you can specify a computer name if more than one person uses the same desktop.

Managed Items showing Action setting and Value for Toad Options and Settings – in this case PL/SQL Template files being shared across a development team

The Action settings enable flexibility in how policy settings are deployed to different groups:

· Don't Restrict - the default. This option leaves the managed item completely unrestricted

· Publish - Select this to publish a set of settings to the machines. The user can change the settings on this item, but they will be renewed each time Toad is restarted.

· Restrict - Completely restrict the user from changing the settings made.

The Group Policy file now needs to be saved in to preserve its settings. This is saved as an xml file.

The final stage is to publish the Group Policy file to the server since the Policy Editor may be installed on a desktop machine remote from the server.

The files which are sent to the server when you click the publish button are:

· GroupPolicy.xml

· Zip archives of any files that the group policy server needs to publish to a user group (for example, formatting files, template files, and so on). One zip file is created for every restriction type requiring files that is to be published.

When you launch Toad for Oracle 9.1, the contents of the policy will be posted out from the server and refresh any current Options and Settings.

Toad Options window for a Managed Toad user showing the Team Coding options pre-set and deactivated

Finally, here are two scenarios to help explain how Toad Group Policy Manager will be helpful to you in your organization and enable much improved manageability especially where you have multiple Toad for Oracle users:

Scenario 1

Each Toad for Oracle 9.1 user has their Options and Settings automatically updated by the Toad Group Policy Manager.

Scenario 2

A DBA wants to set up Toad to have Read-Only data grids for certain users and they also want to be able to prevent all users from saving their passwords.

Each Toad for Oracle 9.1 user in their corresponding Group then has their Options and Settings automatically updated by the Toad Group Policy Manager.

Additional information is provided in the Toad Policy Editor help file, also available as the GroupPolicyEditor.pdf file.

What’s Coming in Toad 9.1 - Part 1: Action Recall

John Pocknell — Fri, 25 May 2007 18:42:00 GMT

We decided that, rather than let you wait until the next release of Toad before you see what’s new, we’ll give you a little fore-taste now!

For those of you who already participate in the Toad Beta Testing program (www.toadsoft.com ), you will no doubt, have already played with this. I would encourage as many of you as possible to join, if you can make the time, since you have the power to help influence future product direction.

How many of you have heard of or used SQL Recall – a feature that’s been in Toad for many years now?

Well, this is a similar concept.

I want to paint a scenario for you. Let’s imagine, as part of your job, you have to export data from a data grid in Toad to an Excel spreadsheet and distribute it to someone in your organization. You first connect to the right database, navigate to the right schema and then select the table in Schema Browser. You click the data tab in Schema Browser, then right-click in the data grid to open the Save As dialog box. You choose “XLS File” as the format and make any necessary changes to the Options. You then specify the path and filename on your local or network drive. Then you go into Outlook, compose an email and attach the spreadsheet to the email and send.

How laborious is that if you have to do it on a regular basis ! (same table data, same recipient)

So, what if you could encapsulate those ‘actions’ into something (a bit like like an object) ? That thing would then be able to be re-used, shared, and distributed.

Well, that thing is called an Action.

An ‘Action’ is defined as an operation or series of operations in Toad which have met the user criteria necessary to justify the persistence of the operation to disk for later recall and use. Every Action is defined in plain text, can be scheduled, can be executed via the command-line, can be performed as part of a collection of Actions, can be copied and pasted via the clipboard and can be used to load the GUI from which they were created.

Toad windows can also be ‘loaded’ with an Action, meaning all controls and settings are changed to reflect the Action. Windows can also be ‘saved’ to Actions or the clipboard, to preserve their current settings. This mechanism replaces the wide variety of ways windows are currently loading and saving their settings.

Therefore, recalling an Action is simply the ability to re-perform a distinct operation or sequence of operations in Toad such as dropping a table, transferring a file via FTP, exporting DDL, sending an email and a myriad of other operations which currently exist or will exist in the future.

An Action Set can be created which is a collection of Actions for the purpose of organization. Any number of Action Sets can be created via the Action Palette. An Action Set itself can be executed, which is simply the execution of all the Actions contained within it. As with Actions, Action Sets will be able to be scheduled, and they can currently be executed via command-line.

How do I know whether something can be made into an Action?

Every Toad window which supports Actions has a lightning bolt in its window caption, in the upper right. The Lightning Bolt opens the Action Prompt Window and is the means by which Toad windows can create and load Actions.

Scheduled Actions can also be viewed via the Utilities > Task Scheduler window.

This is great! I can think of some really cool ideas right now!

Well, the good news for you is that the Toad development team is currently looking for a lot of user feedback on which Toad operations to “wrap” into an Action. In order to assist them and not waste valuable development time wrapping Actions which may not be used as frequently as others, please tell us what you would like. We see potential candidates as the various Export and Import windows as well as reports and so forth.

Keep in mind that we can also create new Actions which do not have any corresponding Toad window or even existing operation. For instance we will be designing an Action to convert script files to ANSI join syntax or vice-versa. This has been a common use of the Query Builder (formerly SQL Modeler) window and seems an excellent candidate for an Action. Keep an open mind about the possibilities here. Would a “Create Table” Action be useful as perhaps a template, when many tables are being created, to assist in pre-loading the GUI? How about the various operations when right-clicking a Table in the Schema Browser? What about creating an Action which is a series of operations to assist in workflow?

Please let us know – voice your opinion by responding to this Blog or by going to the Community tab on Toad World. Thanks!

So, where can I store my completed Actions?

The answer is – in the Action Palette.

This is a GUI View window (View > Action Palette – ensure you aren’t using custom menus which don’t have it yet !) akin to the Project Manager and is the means by which Actions and Action Sets can be organized, scheduled, named and so forth. It is also a powerful way to create new Actions instead of using Toad interactively.

Could I execute Actions without having to launch Toad ?

Sure, you can run Actions from the Command Line. (Note: for those of you who may be unaware, Toad has always had the capability of being launched from the Command Line with a view to performing a specific task. For more information on Command Line execution, go to Toad Help | Index tab and search for the keywords “command line”)

To execute Actions via the Command Line, use the –a parameter and specify the Action, Action Set or series of both. If specifying only the Action, the Action name must be unique across all Action Sets or else an entry will be made in the Action Log about more than one Action found. To fully qualify an Action, use ->. Some examples follow.

The following runs an Action Set called “MondayQueries.” It will execute all Actions within the Action Set:

Toad.exe –a “MondayQueries”

The following runs an Action Set called “Email Mom”. Only one Action by that name in the entire datafile can exist:

Toad.exe – a “Email Mom”

The following runs a fully qualified Action, since there may be more than one Action by the name “EmpQuery”:

Toad.exe –a “CommonQueries->EmpQuery”

The following runs a series of Actions and Action Sets:

Toad.exe –a “CommonQueries” “EmailSet->Email Mom” “SalesReports->MondayReport”

Separate more than one Action or Action Set with a space and surround each item with double-quotes.

How can I schedule an Action?

To Schedule an Action, find it in the Action Palette. A Search mechanism exists if needed. The interface is the common Windows Task Scheduler interface. Notice that the Task Scheduler window is preloaded with the necessary command-line and parameter information. Compare the parameter and you will see it matches the necessary syntax as described in the ‘Command Line Usage’ section. If an Action already has a Schedule, clicking the Schedule button will open the Edit Task Schedule window so the Schedule can be changed or removed.

Toad for Oracle 9.0 - How do I Debug PL/SQL Combined into a Single File?

John Pocknell — Tue, 24 Apr 2007 20:24:00 GMT

In this short blog, I want to cover a topic which quite a few customers have asked me about over the years, and that is where they need to debug a Package where the Package Specification and Package Body is stored on their file system as a combined file.

Toad’s Editor is designed to make developing Packages, Types, Procedures and Functions easy. In Oracle, the Spec and Body of a Package or Type are two separate objects, although they work together. Since the Editor is not a script editor or processor, multiple objects are maintained in separate tabs: one tab per object.

Maintaining one object per tab allows Toad to provide advanced features such as interactive debugging, Team Coding, and advanced parsing capabilities.

Keeping objects on individual tabs makes it easy to jump from the Spec to the Body, simply by pressing and clicking over an object name.

However, some developers prefer to save both the Spec and the Body of a Package in one file. Toad can accommodate this using its file splitting options.

File splitting in Toad for Oracle was specifically designed to accommodate users with multiple PL/SQL objects in a single file. This feature lets you load multiple objects from one file and save Package Specs and Bodies to one file.

When a file is loaded, the Editor checks to see whether more than one PL/SQL object is created in the file, if so, the Editor takes action depending upon which of the options listed below is set.

Go to Options | Editor | Open/Save.

Automatically split files when multiple objects separated by "/"

This option will automatically split files when Toad comes across a "/". Toad assumes you want to split different objects onto different tabs.

Each section divided by a forward slash will be placed in a separate tab.

Prompt to split files

When this option is checked, Toad will ask if you want to split a file into separate tabs. Splitting the file is recommended, but you can choose at the prompt to keep the file as one.

Note: If you do not split the file, compiling will be disabled.

Never split files

When this option is checked, Toad will always load files into one tab and never split a file into body and spec. Note: This option disables compiling.

In addition, you can decide how you want the Package to be displayed in the Editor. For example, if you choose to load the Package into a single tab in the Editor, you will be able to compile and debug the Package the same as you would if you loaded them into separate tabs. But, in any case, you have to begin your debugging session in the Body otherwise the debugging buttons will be greyed out…..

If you are using (and want to watch) Package variables, you must tell the Debugger what type of variable it is

After you have added a Watch to the variable, double-click in the Watches tab to open its properties. Click the Package Variable check box and select the Package name and Owner from the drop downs. You can also define the format of the values expressed (Floating Point, Scientific, etc). As you debug, you will be able to track the values of the Package variable as it changes. Note the Break on value change.

Also, make sure you have the below options set and that you have the DBMS Debugger option set (not JDWP). Options | Debugging.

The other option you should set is the “Step through package initialization”.

When you have a Package that includes Package variables, the first time you execute a Procedure in the Package the variables are initialized. If this option is checked, the Debugger will step to the lines of code where the variables are declared and initialized. If the option is unchecked, the Debugger will never step into the Package initialization.

Debugging Packages in Toad is a breeze, but if you are new to this, there is a very simple example Package available in the Toad Help called Trigonometry which you can install and use as a test (Help | Index | keyword Package).

Once you’ve completed your debugging session, the code will have to be re-combined back into a file. Again, refer to the Options under “Saving files” to decide how you want this done.

When a Package Spec and Body are both loaded from database into the Editor, and you choose to save to file, Toad’s behavior depends on these options.

If only the Spec or only the Body is loaded, then only the object that is loaded will be saved to file.

The default is Never combine spec/body.

Automatically combine spec/body when saving object to file

Puts Spec and Body into the same file.

Prompt to combine spec/body

Prompts you every time you close the files.

Never combine spec/body

Saves only the object on the current tab.

Toad for Oracle 9.0 - How do I Detect Performance Bottlenecks in my PL/SQL Code?

John Pocknell — Tue, 27 Mar 2007 16:54:00 GMT

In this blog, I want to introduce you to a little known feature in Toad for Oracle called the PL/SQL Profiler. Believe it or not, the profiler has been in Toad since September 1999 (Version 6)!

So why would I want to Profile my Code?

Well, as developers write their code, they’re principally focussed on ensuring the code enables the required application functionality and does so consistently for all test cases. They also need to make sure any errors are properly handled. So they’re focussed on testing and debugging.

What they don’t often have an opportunity to validate, though, and this isn’t generally discovered until after the application goes into production is: does the code unit perform well when it runs and do I have any way of reporting on it? Also, is it likely to scale well in Production?

Imagine a large PL/SQL Package running in a Production database with many Stored Procedures and Functions. If the Package performance is poor, how do you determine (to the line of code) where the bottleneck is? You could probably make an educated guess and look for anything which makes a call to the database (another Package call, SQL statement execution, etc) but you don’t really know. And this problem will be greatly amplified in a Production environment.

This is where Toad’s PL/SQL Profiler can help…

The Oracle Database (8i and above) provides a Probe Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. The collected profiler (performance) data can be used for performance improvement efforts or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.

The profiler API is implemented as a PL/SQL package, DBMS_PROFILER, that provides services for collecting and persistently storing PL/SQL profiler data.

What do I need to do to set up the PL/SQL Profiler?

You first need to run Toad’s Server Side Objects Wizard which is located under the Database – Administer menu group. This will install 3 tables into a schema of your choice then verify that you have access to Oracle’s SYS.DBMS_PROFILER Package. If this Package is not installed, you need to do the following:

1. Login to Oracle through Toad as SYS.
2. Load the Oracle home>\RDBMS\ADMIN\PROFLOAD.SQL script into the Editor.
3. From the SQL Editor menu, select Execute as Script (or press F5).
4. Make sure that GRANT EXECUTE on the DBMS_PROFILE package has been granted to PUBLIC or to the users that will use the profiling feature.

Once this is complete, you’ll find Toad’s PL/SQL Profiler toggle button becomes active.

How do I use the PL/SQL Profiler?

Improving application performance is an iterative process. Every iteration involves the following:

Exercising the application with one or more benchmark tests, with profiler data collection enabled.
Analyzing the profiler data, and identifying performance problems.
Fixing the problems.

To support this process, the PL/SQL Profiler supports the notion of a run. A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the end of the run by clicking the Toggle PL/SQL Profiling button in the main Toad toolbar.

A typical session involves:

Starting profiler data collection in session.
Executing PL/SQL code for which profiler/code coverage data is required.
Stopping profiler data collection.

Some PL/SQL operations, such as the very first execution of a PL/SQL unit, may involve I/O to catalogue tables to load the byte code for the PL/SQL unit being executed. Also, it may take some time executing package initialization code the first time a package procedure or function is called. To avoid timing this overhead, you should warm up the database before collecting profile data. Warming up involves running the application once without gathering profiler data.

The profiling information is stored in database tables (installed earlier by the Server Side Objects Wizard). This enables the ad-hoc querying on the data. It lets you build customizable reports (summary reports, hottest lines, code coverage data, and so on) and analysis capabilities.

How do I view the collected data and what do I do with it?

There are three levels to this collected data.

Level 1

The top level are the individual "Runs" of each Procedure executed while profiling was enabled.

The bottom half of the window lists the runs, including Run Number, Procedure, Timestamp, Comment, and Total Time to execute. You can sort on the columns by clicking on the column headers.

Double-click one item for the execution stats for that individual run.

Level 2

The top half of the window is a graph showing the percentage of time required to run each component of the Procedure.

In addition, labels on the chart show actual execution time for the Fastest, Slowest, and Average Unit or line of code.

The bottom half shows the breakdown of all the code used during the program unit execution.

Level 3

If you double-click a code unit, you will expose the lines of PL/SQL code showing the Line #, Passes (0 indicates the line did not execute, positive numbers indicate whether the line executed or, in the case of a Loop, the number of iterations made), Total, Min and Max Times (in milliseconds).

Between the top half of this window and the bottom half is the analysis toolbar which lets you alter the graph characteristics, navigate between the levels or open the Editor.

If you are lucky enough to have either Toad for Oracle Xpert or Toad Development Suite for Oracle, you will be able to not only open the PL/SQL code unit in the Editor, but actually copy a bottlenecking SQL statement into the SQL Optimizer and tune it. Then have the re-written statement returned back into the code. The SQL Optimizer will also test the SQL for Production Scalability. Great workflow!!

What about Code Coverage?

If you want to understand code coverage better, in Toad for Oracle 8.6 and above, you can have gutter indicators show you whether a line of code executed or not (Green indicates it did – Red indicates it did not). As you mouse-over a Green indicator, Toad will tell you the execution time and number of executions made.

If you are experiencing PL/SQL code degradation and not sure where to look or what to do, the chances are Toad’s PL/SQL Profiler will give you visibility of the root cause quickly and efficiently to enable you make an informed decision on your next course of action.

Have fun!! (And remember, PL/SQL Profiler comes with Toad Standard Edition)

Toad for Oracle 9.0 - How Do I Setup Team Coding and VCS Integration?

John Pocknell — Mon, 12 Mar 2007 22:12:00 GMT

1. What is Team Coding

Team Coding is a cooperative source control feature. You can use Team Coding alone, or in conjunction with a third party version control system. Team Coding works with the Editor to control access and development of functions, procedures, packages, triggers and types. It also supports scripts in the Editor. Some of its key features include:

· Flexible Code Control Groups (CCGs), which help you control objects from one or more schemas, using customizable groupings.

· You can organize objects into CCGs by using object masks representing both specific database objects and collections of similar objects (by using the Oracle % wildcard). You can also set up exclusion masks to exclude specific objects from a CCG.

· Even without a third party version control product, you can use CCGs, to track referenced objects through Team Coding (though without revision history).

· All configuration data is stored in the database under Team Coding control. Individual workstations do not need to be configured.

Even better – it’s a Standard feature which I bet you hadn’t even heard of !

2. What is Server Side Objects Wizard?

The Server-Side Objects Wizard is designed to simplify the task of checking whether Team Coding is already configured on the user’s desktop, and if not, to run a script which installed a small repository (called the Team Coding Repository) into a database schema of the user’s choice (usually the Toad schema).

If you wish to install for the first time, select the first option as illustrated below.

Log on to the database in which you want the Team Coding Repository to be built. The Toad schema is recommended to help you manage where any Toad Server Objects are installed.

In the next step, choose the Team Coding option. This will create the required roles, tables, indexes, triggers, constraints and sequences in the Toad schema. Access to these objects is also granted to the Public role – this allows Team Coding “rules” to apply to every user of Toad.

Follow all the steps for creating the Server Side objects and also for creating the 3 Team Coding roles: TC_ADMIN_ROLE, TC_LDR_ROLE and TC_MGR_ROLE which can then be assigned to users as appropriate.

This is an order where you can control the access users have to the administration features in Team Coding such as the Team Coding Viewer and the ability to freeze, lock, etc. code in the database.

The rights which each role has are as follows:

Administrator (TC_ADMIN_ROLE) - Can configure the instance to define how Team Coding operates, which VCS (if any) is used, and so on. This role is automatically assigned to the Toad user.

Project Manager (TC_MGR_ROLE) - Can create and delete code control groups (CCGs) and relate them to a VCS project.

Team Leader (TC_LDR_ROLE) - Can modify CCGs, define the objects or scripts are included in the group, and freeze objects. Can also delete rows from the Team Coding Viewer.

Users that are not granted one of these three roles hold the assumed role of developer. They can view the status of objects with the Team Coding Viewer and check items in and out of source control.

3. Setting the Usability Options

The usability options allow you to control a Team’s coding behavior in the environment with features such as Automatic Check-Out. Note, you can also specify the VCS provider’s name and default working directory.

4. Checking Team Coding Status

Finally, check the Team Coding Status (using a login with the TC_ADMIN_ROLE) to ensure everything is correctly set up.

Click the Settings button (below) to display the configuration options.

The Configuration tab allows you to select the VCS Provider (if you want to use one) and whether to use Code Control Groups (see 5 below) to determine how Developers can access the code.

Note: Team Coding will facilitate code access control even without a VCS if you do not have one. No versioning will take place, but code can be checked-out, locked and then checked-in to the Team Coding Repository.

The File Extension tab lists the default file types associated with using Team Coding.

The General tab enables you to set certain defaults like automatic generation of a new version number when you Check-In.

5. Code Control Groups

Code Control Groups are like filters which allow you to determine if code is controlled or not. If code is controlled using a Code Control Group, this means when a developer accesses the code through the Editor (or Schema Browser or Project Manager), Team Coding will automatically handle the request and perform a Check-Out from the VCS (if it is configured)

Here is an example of a Code Control Group called “Production Group” where all code in the “QUEST_PROD” schema will be controlled by Team Coding with the exception of the “OW_ORDER_TRIGGER” Trigger which will not.

6. Using Team Coding

Toad is now working under the Team Coding configuration.

When the user next launches Toad, they will see a window pop-up which prompts for the VCS login (if Team Coding is configured with a VCS). This option can be changed so that a VCS login would have to performed separately if the user so wishes.

As the code is Checked Out, Team Coding will automatically compare the code with the code in the database to see if they are the same. If not, a warning message will appear informing you that the code is out-of-sync and giving you the option of running a Compare, using the VCS version or using the Database version.

Once you are finished with the code, Team Coding will jump in again and control the Check In.

Finally, once Team Coding is in use by your Development team, and as a user with TC_ADMIN_TOLE, you will be able to monitor and manage access to your code both within the Team Coding Repository, but also within the VCS itself because Toad can access the VCS management console.

Team Coding Viewer

VCS Browser (Microsoft Visual Sourcesafe)

Correctly set up and implemented across your Development team, Team Coding will simplify and automate code management in a way which is much more secure and workflow oriented, leaving you time to concentrate on other things.