John Pocknell

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

Introduction

Toad for Oracle may use Oracle database Options (RAC, Partitioning, Spatial, etc) and Enterprise Management Packs (Tuning, Diagnostics, etc) depending on various factors (see full article). Such Toad features include (but not limited to) ADDM/AWR Reports, AWR Browser and ASM Manager which are part of the DB Admin Module.

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 Tuning.

This Blog sets out to clarify what Toad uses and what it does not for those of you who are unclear and concerned about Oracle database licensing, especially given the recent (June 16) 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.

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 (includes 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 utilises this technology.

As you may already know, there are different editions of Toad for Oracle:

Toad Base – maybe known as Standard (includes 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 Add-On Module called the DB Admin Module (formerly DBA Module), which contains most of Toad’s database administration features.

Does Toad need OEM ?

Toad offers optional features in the DB Admin Module (Toad 8.5 and higher) which WILL ACCESS the Oracle OEM Diagnostics Pack such as :

ADDM/AWR Report Generator – enables snapshot management and shows both AWR and ASH reports
AWR Browser – graphical representation of data collected from AWR
ASM Manager – enables management of ASM disk groups and clients

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 use the Oracle Tuning Advisor feature in Toad Base (9.6 and higher) which WILL access both the Oracle OEM Tuning Pack and Diagnostics Pack.

Alternatively, you could use Toad Xpert, Development Suite or DBA Suite Editions and use the Quest SQL Optimizer which 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 the following link tol take you to 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.

With the introduction of Oracle 10g came Automatic Database Diagnostic Monitor (ADDM), Automatic Workload Repository (AWR) and Automatic Storage Management (ASM).

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 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.

In the new Toad for Oracle 9.7 release (currenty in Beta; Blogs to follow shortly), you will be able to disable any windows in Toad, via the Options window, to prevent inadvertent access to Oracle’s Options and Management Packs.

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

Yes. Toad’s license agreement and Help (Toad 9.6) already provide a warning about this.

In the new Toad for Oracle 9.7 release, we have added a pop-up window (appears once when Toad 9.7 runs for the first time) which will contain wording similar to what is currently available in the Toad Help :

NOTE: There are features installed by default in the Oracle database which are ready to use, even if the license for such features has not been purchased (e.g. Diagnostics Pack, Tuning Pack). It is, therefore, the end user's responsibility to make sure that these features, which may be used by Toad, are covered by the user's Oracle license. Such features include, but are not limited to, Partitioning, RAC, Advanced Workload Repository (AWR) and Compression.

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.

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 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.

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 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

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 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.

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 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.