Bert Scalzo's Blog

TOAD Database Connections Problems

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

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

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

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

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

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

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

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

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

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

Really Cool 'TOAD-Like' Freeware

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

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

Execute TOAD Scripts on Multiple Databases

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

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

Quest Database Freeware

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

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

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

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

Data Warehousing Book Review

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

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

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

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

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

Data Warehousing Tools

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

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

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

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

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

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

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

Free TOAD Book Contest Results

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

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

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

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

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

Good Database Design – It's Never an Accident

Bert Scalzo — Thu, 22 Oct 2009 11:48:00 GMT

When designing and constructing a successful (i.e. effective and efficient) relational database, there are two fundamental sets of rules or design principles that are universally accepted and generally practiced by database architects.

Toad for Oracle Database Connection Styles

Bert Scalzo — Wed, 14 Oct 2009 11:34:00 GMT

TOAD offers three distinct ways to connect to your database, as shown by the three tabs circled below on the connection screen. In this week’s blog we’ll examine the differences between the first two: TNS and Direct. No matter which of the three methods you choose, TOAD requires the Oracle “SQL*Net” client to be on your PC. TOAD needs to talk over the network to your database, and that requires certain Oracle network DLL files to exist on your PC. TOAD will communicate with Oracle via those DLL files over the network.

The first tab “TNS” stands for Transport Network Substrate – although everyone simply uses TNS abbreviation (and may not even know what it stands for). The “TNS” connect method requires a text file on your local PC in the $ORACLE_HOME/network directory. This tnsnames.ora file serves like a network hosts file, but for database name resolution. This tnsnames.ora file simply contains entries that define where and how a database can be reached, and the alias name assigned for each entry. So in the example below, the alias ORCL is defined in that file. Thus when TOAD initiates a “connection request” to Oracle using this method and passing the ORCL alias, Oracle knows to look that information up with the tnsnames.ora file, translate the specified alias into the corresponding complete connection string, and then attempts to establish a connection with the matching database server. Thus TOAD users’ failure to install the proper Oracle client and/or improper and/or missing alias entries from the tnsnames.ora file can result in numerous Oracle connection error messages. Finally regardless of which database connection tab/method, always make sure that your Oracle home points to where you think (i.e. where the correct tnsnames.ora file and “SQL*Net” DLL’s are located), as shown by the arrow below.

But there’s another TOAD method for establishing database connections, the “Direct” tab pointed to below, which eliminates one of the two requirements mentioned above. It does not require the tnsnames.ora file! Instead you provide sufficient database connection info on the connection screen for TOAD and “SQL*Net” to directly access the database – and without requiring an alias name or any translation. You’ll still need the correct or proper Oracle Home identified so that TOAD knows where the “SQL*Net” DLL files are found. But that’s it. Plus TOAD’s saved connection file will contain all that information. So you can utilize TOAD more portably – e.g. carrying and running your “all-inclusive” TOAD tool around on a Flash Drive. Plus it helps to eliminate potential incorrect connections, because with the TNS method you really don’t know where you think you’re pointing to unless you open and/or edit the tnsnames.ora file!

Optimize Oracle Virtual Machines on a Notebook PC – Part 3

Bert Scalzo — Wed, 30 Sep 2009 11:53:00 GMT

Last week’s blog was part 2 of a 3 part series about techniques for optimizing a notebook PC for running Oracle database within a virtual machine. In part 1 I detailed my “top ten” ideas for proper host (i.e. notebook) configuration. In part 2, I offered a comparable list for proper client or VM configuration. And finally, this week I’ll do the same for Oracle database instance setup and configuration within a VM.

Once again let's assume that you have the typical recent or last generation notebook PC (i.e. one with a dual core CPU and say a max 4 GB of RAM). Here’s what can we do to the database in order to obtain optimal performance (for this platform only that is).

Keep the Oracle SGA memory allocation to a reasonable minimum. Assuming that we’ve 4 GB of RAM and allocate 50% or 2 GB to the VM, then keep the SGA size between 600 MB (an acceptable minimum that I’ve mentioned in a prior blog) and 50% or 1 GB.
During database creation, if you’re not going to use Oracle Enterprise Manager then make sure to uncheck OEM in two places – the Database Creation Assistant (DBCA) screens for Configure OEM (uncheck the box), and Database Content (uncheck the OEM Repository box).
During database creation, eliminate overheads most likely unnecessary for a notebook posed by flashback and archive log mode – the Database Creation Assistant (DBCA) screen for Database Content (uncheck both the flashback and enable archive boxes).
Since we’re going to keep the SGA size to a minimum, make sure to use the Oracle version’s automatic memory allocation options (for 10g and 11g).
Consider using a database block size of 4K rather than the 8K default. Remember that a notebook PC generally has a fairly constrained IO system.
Manage Oracle disk IO efficiently through proper initialization parameter settings (i.e. DISK_ASYNC_IO=TRUE and FILESYSTEMIO_OPTIONS=SETALL).
Since many notebooks perform basic IO operations in 64K chunks, be sure to set the database IO sizing options to leverage this. So for a 4K block size (from # 5 above), set DB_FILE_MULTIBLOCK_READ_COUNT=64K / block size (i.e. 64 / 4=16).
Minimize certain Oracle internal IO overheads where possible. For example, set the AUDIT_TRIAL=NONE to eliminate auditing (the default in 11g). Also keep Oracle trace and dump files to a reasonable size (remember notebooks have a very limited IO bandwidth) by setting MAX_DUMP_FILE_SIZE=1M (or something likewise small).
Disable certain “optional must pay for” OEM tuning and diagnostic pack overheads built into every database (even when you don’t pay for or use them). I recommend Mike Ault’s blog about Avoiding Oracle Licensing Landmines. For example disable automatic workload Repository snapshots by calling DBMS_AWR.DISBALE_AWR.
Consider using “shared” server (i.e. MTS or multi-threaded) connection model. When you need to have the database VM handle significant multiple concurrent connections then lower the OS overhead per connection by sharing the server processes.

Optimize Oracle Virtual Machines on a Notebook PC – Part 2

Bert Scalzo — Wed, 23 Sep 2009 14:52:00 GMT

Last week’s blog was part 1 of a 3 part series about techniques for optimizing a notebook PC for running Oracle database within a virtual machine. In part 1 I detailed my “top ten” ideas for proper host (i.e. notebook) configuration. This week I’ll offer a comparable list for proper client or VM configuration. And finally, next week I’ll do the same for Oracle setup and configuration within a VM.

Once again let's assume that you have the typical recent or last generation notebook PC (i.e. one with a dual core CPU and say a max 4 GB of RAM). Here’s what can we do to the clients or VM’s in order to obtain optimal performance.

For the Virtual Machine Settings, don’t set the VM CPU count too aggressively. For example our test notebook has a dual core processor, thus we should not set the CPU count for any VM greater than one. When the host OS is managing “time slices” for concurrent process execution, it waits until the idle CPU count equals the client CPU setting. If you multi-task a lot or have host virus scanning real time protection, then you could see artificially induced delays.
For the Virtual Machine Settings, don’t set the VM memory size too aggressively. For example our test notebook has 4 GB of RAM, thus we should not set the VM memory size greater than half that. When the host OS is performing its memory management for concurrent process execution, the host (and the client too) could both be swapping/paging a VM’s memory at the same time. This also can introduce artificially induced delays.
Review and rethink issues #1 and #2 based upon how many concurrent VM’s you’re going to run. The more concurrent VM’s obviously the lower then the CPU and RAM allocations you should make. If you’re notebook PC is tight on memory, better to run fewer VM’s than to suffer the performance hits when over allocated.
For the Virtual Machine Settings, be careful about your network model decision. I see many people simply choosing NAT (Network Address Translation) even though there can sometimes be significant translations delays introduced because it’s so easy. Yet I’ve seen where an Oracle database connection from host or another concurrent VM application can take 10 seconds or longer. Some applications might even time out. I personally use “bridged” when on my home network and “host only” when traveling.
If you’re VM’s OS is going to use a graphical user interface (GUI) then make sure to install the VMware tools. In fact, I’d recommend simply always adding the tools – because you never know when circumstances might change. And when your host is running Windows with the necessary hardware, make sure to check the display option for accelerate 3D graphics.
If you won’t need to backup and restore VM images known as snapshots – then turn this option off. It adds overhead for something I’m never going to use. I simply zip up my VM files when I want to make a backup – so that I can move them between hosts.
Keep all your VM’s VMware Tools version up to date. It’s simple to do and will offer performance improvements worth the brief time required to update. You can even set it to auto upgrade as newer version become available.
If you’re not going to use hardware such as floppy disk, CDROM or USB – then turn them off. There is polling and other overheads that can be reduced or eliminated. And the cumulative effects when running more than one VM might be quite worthwhile.
If you have multiple hard disks (remember from part 1 that we don’t want to use slow USB disk drives for the VM’s), then place the host OS on one drive and the VM’s on the second drive. Spreading the disk IO between the host and the client will always be worthwhile. If you’re lucky enough to have an SSD drive it won’t matter.
Make sure to regularly defragment and shrink your VM disk drives. The shrink is performed via the client VMware tools and the defragment is done via the Virtual Machine Settings for the disks.

Optimize Oracle Virtual Machines on a Notebook PC

Bert Scalzo — Wed, 16 Sep 2009 19:01:00 GMT

People have often asked me “How do I optimize my Oracle virtual machines running on my meager notebook PC?” So I decided to put together this quick checklist to assist with just that question.

Let's assume that you have the typical recent or last generation of notebook PC, that is one with a dual core CPU and say a max 4 GB of RAM. Here's how to optimize your Oracle VM on that hardware. The advice below are my top ten advice for proper host configuration. In part two (next week) I'll focus on recommended client VM configuration, and in part three (two weeks out) I'll cover some basic Oracle database configuration recommendations for optimal virtualized performance.

Make sure that your notebook's firmware or BIOS has “virtualization support” turned on. This will enable your CPU and virtualization software to work more efficiently together.
Upgrade your notebook's memory to the maximum allowed – e.g. 4GB. It's a sub $100 upgrade that's quite easy to do and will yield the greatest improvement possible.
Upgrade your notebook's hard disk to a 7200 RPM model. This too is a sub-$100 upgrade that's very easy to do and will yield a fairly noticeable overall speed increase.
Run a 64-bit host operating system such as Windows XP 64-bit. When you instead run a 32-bit host operating system such as Windows XP 32-bit, your machine will only see and use between 3.0 and 3.5 GB of RAM (depending on your notebook's chip set). That means you're effectively wasting anywhere from 13% to 25% of your memory.
Consider eliminating the host operating system's swap file and instead live inside your memory available. You may only be able to run one or two VM's, but they will operate much faster than if they had to swap on a single notebook disk drive.
Eliminate all unnecessary host operating system services or daemons. Do a web search for your specific host operating system's excesses and turn them off. For example disable the “Window's Indexing” service. There are numerous such services and daemons that are all on by default.
Do not run your VM's off a USB disk drive – USB's bandwidth is too slow. If you must utilize an external drive, then use e-SATA. Otherwise just place the VM on your notebook's disk drive.
Exclude your virtual machine home directory and all subdirectories from your host's Anti Virus scanner. The Real Time Anti Virus protection doesn't really need to scan any of the VMs' files.
Set the host operating system's file system to not update files' attribute or property for the last access in order to minimize the actual underlying physical IO's that must occur to support an IO request
1. For Windows: HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\FileSystem\NtfsDisableLastAccessUpdate = 1
2. For Linux: Edit the /etc/fstab and ass the NOATIME attribute
  1. ```
  /dev/sda6 /      ext3 defaults,noatime 1 1
```
2. ```
/dev/sda1 /boot  ext2 defaults,noatime 1 2
```
Use VMware Workstation version 6.5.x (~$189) instead of the freeware VMware Server or even an older version of Workstation. The recent version of Workstation offers significantly better performance for both IO and network traffic. If you simply cannot spend the money for VMware Workstation, then my advice would be to instead try using the freeware Virtual Box. Note: I'm not recommending these virtualization products in general versus Oracle's VM. But for the basic notebook setup we're talking about – both these alternatives are dirt simple and good enough. For a real database server deployment, my advice would be quite different.

TOAD Master Detail Browser

Bert Scalzo — Wed, 09 Sep 2009 12:50:00 GMT

While TOAD offers numerous capabilities, many people often spend a majority of their time simply “browsing” existing table data. Even when writing SQL or PL/SQL code to perform some business function or action, developers need to see what the data looks like in order to write the code. And of course business analysts and managers often look at the data in order to spot patterns or drive report design. For these needs people might utilize the Schema Browser or Editor’s data grid. But often business data spans tables – and one needs to see the related data all together in order to comprehend its real meaning. For that TOAD offers the Master Detail Browser – one simple screen to see all related data. I’ve included an excerpt from my upcoming book TOAD Handbook 2^nd Edition, which debuts September 28^th 2009 – so before Oracle Open World.

Master-Detail Browser

You might well wonder why the Master-Detail Browser was not covered in Chapter 2, where we covered both the Database Browser and the Schema Browser. The reason is simple: Those browsers are more focused on learning about the structure (with some emphasis in browsing the data), whereas the Master-Detail Browser is clearly 100% about browsing the data. Furthermore, even though the Database browser and the Schema browser possess options that affect their appearance and basic function. both start out by default with something visible and usable. In contrast, with the Master-Detail Browser, you have to define what is to be shown—and for multiple tables, what their connection is (whether defined in the database or provided by you). Thus the Master-Detail Browser is a much more interactive screen, where you must provide input and direction as to its basic operation.

Just what does the Master-Detail Browser screen do? Ever wished that you could navigate a specific table’s data, and at the very same time see all the associated records from other related tables? For example, have you ever wanted to browse the ORDER table data and for each order also see the LINEITEMS for that specific order? That’s what the TOAD Master-Detail Browser offers—intelligent data browsing for related tables. It lets you “see” your data in a much less database-centric and much more human-readable format. Both business/data analysts and database application developers have found this screen highly useful, because it truly humanizes and increases the readability of their businesses’ data.

To launch the Master-Detail Browser, from the TOAD main menu select Database | Report | Master-Detail Browser. You will see the screen shown in Figure 9.1. Note that this screen is empty when it first launches. You must define what’s to be displayed—a process that is fully explained in the following paragraphs.

Figure 9.1
Master-Detail Browser

Before explaining just how we got the information displayed in Figure 9.1, let’s first look at what we see in such a completed screen. On the left-hand side (LHS), TOAD displays a panel showing a tree view of the relationships between parent and child tables. In Figure 9.1, we can see that the CUSTOMER table has a child table called MOVIERENTAL, which has a child table called RENTALITEM. Furthermore, all child tables are related to their parents by database foreign key constraints (i.e., the type for each child is shown as Table FK). But the best part is how this screen works once the information has been provided. When you select a row in the data grid for any table, the children of that table have their contents refreshed to correspond to the parent. Thus, when we selected the Troy Aikman CUSTOMER record, only the MOVIERENTALS for him are shown. The same is true with the MOVIERENTAL and RENTALITEM records: When you select the parent rows, the child rows match those of the parent.

But how did we get this specific information to display? Recall that when you first launch this screen, the LHS navigator and the central display area data grids are empty. Remember, that’s the key difference with this screen—you must configure it for your data and browsing needs. First you need to choose the base object upon which to build the screen, as shown in Figure 9.2. Here we’re selecting the CUSTOMER table as our base object.

Figure 9.2
Master-Detail Browser: Choose Base Object

Of course, that action merely results in the display of a single data grid with the selected table’s data, which is not too different from what happens when you use the Schema Browser or Editor. Now, however, we will add in the child tables with their automatically synchronized data. In Figure 9.3, note that the cursor shows we’ve clicked the “Add Detail Under This Dataset” toolbar icon. Because our database has foreign key constraints defined and enabled for the base object, the child was automatically added. Thus the MOVIERENTAL table was mechanically added as a type of “Table (FK).” We did not have to do a thing. If we repeat this process by clicking the “Add detail …” option for this new table, the third data grid will be added for the RENTALITEM table, as shown in Figure 9.1.

Figure 9.3
Master-Detail Browser: Add Child Object

There are three toolbar icons in the child data grid areas that you should use. Clicking the minus sign (–) will decrease the data grid size, whereas clicking the plus sign (+) will increase it. As you add more children, you’ll need to use these options to manage the limited real estate available on the screen. Clicking the X toolbar icon removes the child, its data grid, and all the children below it; this function allows you to remove items from the tree view displayed on the LHS navigator. Note that you can also remove items by selecting the tree node for that item, performing a right-hand mouse (RHM) click to access the RHM menu, and selecting “Delete Current Node.”

But what happens when our database does not have convenient foreign keys already defined for the automatic “Add detail …” option? How do we get the same end result? Many developers and analysts work with big third-party applications such as ERP and CRM packages, and we cannot modify the database design to get TOAD features to work with those applications. Don’t worry: TOAD can handle this scenario as well. When you choose “Add detail …” and there are no preexisting database foreign keys, TOAD requires you to use the “Define Master/Detail Relationship” toolbar icon to add them, as shown in Figure 9.4. Until you perform this step of manually defining how the parent and the child relate, the data grid remains empty (i.e., TOAD has no idea of how to initially populate the data grid or how to keep it in a synchronous state).

Figure 9.4
Master-Detail Browser: Empty Child Added

When you click the “Define Master/Detail Relationship” toolbar icon, a pop-up window for manually defining the parent–child link is displayed, as shown in Figure 9.5. Of course, the top portion of this pop-up is prefilled with your base object. In our case, DEPT did not have a primary key, so only the left quadrant was prefilled with data. We then chose to have TOAD use the DEPTNO column as the parent’s key, chose the EMP tables to serve as the child, and selected the DEPTNO column to serve as the child’s key. TOAD is not creating any database keys nor constraints here; rather, we are simply telling TOAD how to auto-sync these tables (i.e., instructing TOAD what the join conditions are for the related data grids). Also note that the “Type” for the child will be “Other” and not “Table (FK)”; this notation is used for any object with user-selected criteria that explain how to link parent and child.

Figure 9.5
Master-Detail Browser: Manually Defining Links

No matter which method (auto versus manual) you used to construct your Master-Detail Browser screen, you will want to save your work so that you can later reopen the screen and have it appear exactly as you have defined it. To do so, click the floppy disk toolbar icon, which saves the complete current screen definition as an “.MD” file. When you restart the Master-Detail Browser, you can choose to open that prebuilt screen. Also, be aware that your screens where you define manual master/detail relationships (i.e., links) between tables are stored in this file.

Flashback to the Rescue

Bert Scalzo — Wed, 02 Sep 2009 11:11:00 GMT

Face it, we’ve all been there – that moment when we’ve done something a wee bit too fast and then realize we probably shouldn’t have done it. Then we often generally say something like “stupid database” or “stupid TOAD’ or “stupid whatever. But there are ways to recover without having to go hat in hand to the DBA and beg forgiveness and obtain a recovery. More recent versions of Oracle offer several wonderful “Flashback” options that can often save the day – most of which are fully supported by TOAD. I’ve included a summary of all those options with an excerpt from my newly released book “Advanced Oracle Utilities: The Definitive Reference”. You should be able to order it starting the first or second week of September 2009.

DBMS_FLASHBACK

At the beginning of this chapter when discussing various use case scenarios for the data pump export and import were mentioned. Namely – that a DBA might need the ability to restore collections of tables to some pristine state on a regular interval. In the prior section we just examined doing restructures where a backup or before image might be handy in case of problems mid-restructuring. The truth is that there are many cases where the DBA would like the ability to restore a logical portion of the database back in time or to a prior SCN. But the problem is that physical backups and restores are very complex – and operate more at the physical database level. And few applications are generally worthy of their own backup and recovery strategy using the physical tools to build logical or application based restore points.

But a long time ago Oracle introduced a great concept in the SQL and PL/SQL languages called the SAVEPOINT. This was the ability to issue a database state bookmark within you application code such that you could rollback to an application based logical point in time. This was a useful technique, but never really saw extensive usage. But it was a good concept nonetheless – if it only it would have extended to database objects and/or even the database level itself. Well now it does – we have Oracle’s flashback technology.

Oracle flashback technology essentially lets you create SAVEPOINT like bookmarks to restore to for either objects or the entire database. In some respects it’s a great short term point-in-time recover technique – rather than going to a full blown backup and restore. Plus its usage has been made so integrated, seamless and easy – that it’s sure to see heavy usage as time goes on. It’s truly a key new must-have tool for the DBA’s tool belt.

There are six flashback technologies (in chronological order of their appearance) whose topics we’ll examine in more detail:

Flashback Queries (9i)
Flashback Drop (10g)
Flashback Tables
Flashback Database
Flashback Transaction (11g)
Flashback Archives (i.e. Oracle Total Recall)

Furthermore, you’ll see that unlike other features discussed in this chapter – you’ll want to learn how to utilize these various flashback technology capabilities via OEM, SQL commands and the PL/SQL API.

Flashback Queries

Oracle 9i introduced the concept of the flashback query. I like to call this the “Back to the Future” or time machine type query – where Oracle lets us make some queries in the present as if from a database state in the not too distant past.

The black magic that makes this possible are UNDO tablespaces and automatic UNDO management – and that Oracle now treats those UNDO blocks as first rate data based upon the UNDO_RETENTION parameter. Using these, Oracle does its best to retain UNDO data (not guaranteed). You can even force that availability via the UNDO tablespace RETENTION GUARANTEE option.

In its simplest form, we merely add an AS OF clause to the SELECT statement to request the current execution be performed as if it were run at some prior specified time or system change number (i.e. thus looking backwards in time).

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  353026

SQL> update movies.customer set zip='99999';

62 rows updated.

SQL> commit;

Commit complete.

SQL> select firstname, lastname, zip from movies.customer as of scn 353026 where rownum < 5;

FIRSTNAME            LASTNAME                       ZIP
-------------------- ------------------------------ -----
Flozell              Adams                          75063
Troy                 Aikman                         75063
Larry                Allen                          75063
Eric                 Bjornson                       75063

We also can enable the entire Oracle session to enter a “time tunnel” or “time warp” so that we don’t have to add anything at all to the SELECT command to see such historical data. In that case we simply enable and disable the flashback effect as shown here via the PL/SQL packages found in DBMS_FLASHBACK.

SQL> select firstname, lastname, zip from movies.customer where rownum < 5;

FIRSTNAME            LASTNAME                       ZIP
-------------------- ------------------------------ -----
Flozell              Adams                          99999
Troy                 Aikman                         99999
Larry                Allen                          99999
Eric                 Bjornson                       99999

SQL>
SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(353026)

PL/SQL procedure successfully completed.

SQL>
SQL> select firstname, lastname, zip from movies.customer where rownum < 5;

FIRSTNAME            LASTNAME                       ZIP
-------------------- ------------------------------ -----
Flozell              Adams                          75063
Troy                 Aikman                         75063
Larry                Allen                          75063
Eric                 Bjornson                       75063

SQL> execute DBMS_FLASHBACK.DISABLE

PL/SQL procedure successfully completed.

Flashback Drop

Oracle 10g introduced the flashback drop concept – which is built off the new recycle bin in the database (think of it as much like the Windows recycle bin). Now when you drop an object, it’s moved first to the recycle bin (assuming that the recycle bin has been enabled via the RECYCLEBIN=ON init.ora parameter). The recycle bin simply retains the dropped objects under a new unique name.

SQL> drop table junk;

Table dropped.

SQL> select object_name, original_name, type from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- ---------------
BIN$oP2i2G1STvita2AHhmFdVw==$0 JUNK                             TABLE

Then when we want to un-drop a table, the process is very easy – we simply use the FLASHBACK TABLE command to restore the table as it was before the drop. When the table flashback is invoked, not only are the table and its data brought back – but so are any dependent objects and grants. It’s that simple.

SQL> select * from junk;

        C1         C2
---------- ----------
         1          2

SQL> drop table junk;

Table dropped.

SQL> select * from junk;
select * from junk
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table junk to before drop;

Flashback complete.

SQL> select * from junk;

        C1         C2
---------- ----------
         1          2

For those who prefer a graphical interface, OEM has a rather easy screen for doing object level complete recoveries (i.e. un-drop).

Figure 1: OEM Object Level Recovery Screen

Prior to the flashback drop capability, the best method for doing object level recoveries was the logical backup method discussed earlier in this chapter (i.e. export/import). But handling referential integrity and dependencies was a problematic manual effort requiring significant investment to get 100% right.

Flashback Tables

The next logical step in the flashback progression is to do more than to undo a simple table drop – but rather to permit the table to return to its prior state as if making a flashback query permanent. Once again we have yet another key new requirement for this latest flashback feature to work – tables must have row movement enabled (feature that permits Oracle to change the ROWID of a row, otherwise they are usually immutable). Now we can use the prior section’s flashback syntax to return a table to some prior specified time or system change number – as shown here.

SQL> create table junk (c1 int, c2 int) enable row movement;

Table created.

SQL> insert into junk values (1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  362096

SQL> insert into junk values (3,4);

1 row created.

SQL> commit;

Commit complete.

SQL> flashback table junk to scn 362096;

Flashback complete.

SQL> select * from junk;

        C1         C2
---------- ----------
         1          2

Once again for or those who prefer a graphical interface, OEM has a rather easy screen for doing object level point in time table recoveries (i.e. flashbacks).

Figure 2: OEM Table Screen

Figure 3: OEM Table Flashback Screen

Flashback Database

Continuing with our flashback technology progression, the next big step is to permit one to flashback an entire database. In essence we now want to perform a point in time recovery at the database level – and all very simply and without actually doing anything in RMAN. Once again we have yet another key new requirement for this latest flashback feature to work – the database must be running in ARCHIVELOG mode. Plus, we can only flashback to whatever online redo logs are immediately available via disk – any further back in time requires a traditional point in time recovery via RMAN. But for many cases even that short time span may suffice as the first-level basic point-in-time recovery strategy. The next example may look quite a bit like the last for the flashback table, we now simply have to take the database to a mounted status and issue the FLASHBACK DATABSE command as shown. All that’s changed is the scope of the operation.

SQL> create table junk (c1 int, c2 int) enable row movement;

Table created.

SQL> insert into junk values (1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  365991

SQL> insert into junk values (3,4);

1 row created.

SQL> commit;

Commit complete.

SQL> connect sys/mgr as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1334996 bytes
Variable Size             398459180 bytes
Database Buffers          222298112 bytes
Redo Buffers                5640192 bytes
Database mounted.
SQL> flashback database to scn 365991;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect bert/bert
Connected.
SQL> select * from junk;

        C1         C2
---------- ----------
         1          2

We’ve now wrapped up the flashback technology historical progression as of Oracle 10g release 2 – so now it’s onto even bigger and better flashback capabilities with Oracle 11g. However whereas the progression and granularities up until this point were probably fairly obvious, we’re now moving into less obvious but critically useful flashback techniques – hopefully with a decent background and understanding now to appreciate just how we got here.

Flashback Transaction

The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure rolls back a transaction and all its dependent transactions. As with all the other flashback technologies discussed so far, the transaction back out operation uses UNDO to create and execute the compensating or opposite transactions that return the affected data to its original state. So in some respects the granularity of this flashback operation is somewhere between our last two cases: table and database. We’re now flashing back a subset of the database that represents some logical collection of tables and queries. It essentially implements the prior mentioned SAVEPOINT concept in the database now rather than the application code. In fact, flashback transactions mostly eliminate the need for the next section on redo log file mining – as it’s now transparently and more easily done as shown here.

First, we query the FLASHBACK_TRANSACTION_QUERY view to see what transactions exist for whatever objects and/or users we think may have done something that need undone. This view can return a lot of information in even a mildly busy database, so filtering is highly recommended. Here I’ve asked to see what transactions have occurred in the past day by logon user BERT and on tables owned by BERT. Note that this view offers us the UNDO SQL code.

SQL> select xid, start_scn, operation, table_name, undo_sql from flashback_transaction_query where start_timestamp>=sysdate-1 and username='BERT' and table_owner='BERT';
XID               START_SCN OPERATION    TABLE_NAME
---------------- ---------- ------------ ------------
UNDO_SQL
--------------------------------------------------------------
0200030052030000     475697 DELETE       JUNK
insert into "BERT"."JUNK"("C1","C2") values ('5','6');

0200030052030000     475697 DELETE       JUNK
insert into "BERT"."JUNK"("C1","C2") values ('3','4');

0200030052030000     475697 INSERT       JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAD';

0200030052030000     475697 INSERT       JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAC';

0200030052030000     475697 INSERT       JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAB';

0200030052030000     475697 INSERT       JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAA';

If I now want to simply undo the two delete commands (whose undo action to re-insert the data that was deleted), here’s the PL/SQL code for doing that.

SQL> select * from bert.junk;

        C1         C2
---------- ----------
         1          2
         7          8

SQL> declare
   trans_arr XID_ARRAY;
begin
   trans_arr := xid_array('0200030052030000','0200030052030000');
   dbms_flashback.transaction_backout (
        numtxns         => 1,
        xids            => trans_arr,
        options         => dbms_flashback.cascade
   );
end;
/

SQL> select * from bert.junk;

        C1         C2
---------- ----------
         1          2
         3          4
         5          6
         7          8

Once again for or those who prefer a graphical interface, OEM has a rather easy screen for doing object level point in time transaction recoveries (i.e. flashbacks).

Figure 4: OEM Table Screen

Figure 5: OEM Transaction Flashback Screen

Flashback Archives

The final piece of the puzzle in the flashback progression is the new Oracle 11g flashback archive. You define an area that provides the ability to automatically track and archive transactional data changes to specified database objects. These flashback archives become user named and somewhat then managed persistence of UNDO at the specified object level. So when you need to do a SELCT with an AS OF, you can rely on the object being in your chosen container for the specified duration and competing for space only with the objects you so choose. Thus it’s merely a named are to support all the prior flashback features we’ve just examined.

SQL> create tablespace flash_archive
datafile 'c:\oracle\oradata\ordb1\flash_archive.dbf' size 50M;


Tablespace created

SQL> create flashback archive default flash_archive tablespace flash_archive retention 30 day;

Flashback archive created.

SQL> create table bert.junk (c1 int, c2 int) flashback archive flash_archive;

Table created.

Databases will be Virtualized

Bert Scalzo — Thu, 20 Aug 2009 14:52:00 GMT

I have written a book about Optimizing Oracle on VMware, plus have both a paper and presentation regarding the same. Furthermore I have also written blogs about Virtual Paranoia and a four-part series Oracle + VMware = Success. These basically all espouse essentially the same fundamental belief – that database virtualization is here to stay, and is rapidly moving towards mainstream.

But a couple legitimate questions or concerns are always brought up (and they are good questions). I’m going to address the three that I hear most often, because I believe having the answers to just these will knock down 80+% of all the roadblocks to virtualizing your databases.

Oracle does not support VMware. Not true. Roughly speaking – a little over a year ago their position was not to support any VM technology. Then about a year ago, once they got their own VM solution (OVM) - they supported that, but really not others. Then shortly after that, they opened up and now support non-OVM solutions such as VMware.

But there is one very simple and reasonable caveat- Oracle support retains the right to ask the customer during troubleshooting efforts to at some point re-host to non-VM in order to eliminate that possible point of contention. That’s more than fair. Plus with the Oracle acquisitions of Sun (who already had several VM technology offerings, including Virtual Box and Solaris Containers) and Virtual Iron – Oracle has truly become a virtual powerhouse in the VM arena (pun intended). So it’s in there best interest to support and propagate virtualization of everything – including databases.
VM overhead is too debilitating for databases. Not true. Servers and memory are dirt cheap these days. And while storage costs have come down, the larger sizes of databases these days has grown to often eliminate savings. So today’s database servers have plenty of bandwidth to host more than one thing. And for those DBA’s overly concerned about the maybe roughly 10-15% overhead, CPU’s and memory are so cheap that factoring this cost into your cumulative or aggregate server needs is really not that big an issue – really. Often the direct power savings as well as the indirect power savings (e.g. lower cooling needs) can more than compensate for the minor increase in sizing calculations.
Databases are too special or critical to virtualize. OK – BUT, DBA’s freely accepted virtualized storage for databases almost a decade ago. Gone are the days of going into the server room and pointing to your database’s disks. And since IO is the “Achilles Heal” or weakest link in terms of performance for any database, thus we’ve already virtualized the most important performance aspect. Thus it seems a little odd to debate or argue about virtualizing the remaining aspects, which generally impact database performance much less than the IO. So we’re just taking the next logical step in a progression we started a long time ago.

With these two issues put to rest, hopefully you and your management will more quickly embrace what’s inevitable – virtualization of database servers. Because none of us like swimming upstream J

TOAD and Database Versioning

Bert Scalzo — Wed, 12 Aug 2009 13:01:00 GMT

One critical task for DBA's is maintaining their database versioning history. For example the database objects were in some specified state (in terms of their construction) on such and such a date. The key reason this is critical is that application updates and bug fixes often require database modifications. So the DBA must maintain a database versioning history that matches such changes. There are two chief reasons for this: database application documentation and being able to rollback application changes in case they are unstable. It's often the latter case that is the key reason for most DBA's :)

Most DBA's accomplish their database structural versioning using one of three common methods:

Using data modeling tools as their database structural “source code” and initiating changes there. Then using the data modeling tools compare and sync capabilities to propagate all modifications in either direction (model to database or database to model, and often both).
Using a collection of textual SQL scripts containing the data definition language (i.e. DDL) statements. Some DBA's only version the differences from some defined baseline, in which case new objects use CREATE and exiting objects use ALTER. Other DBA's create complete DDL scripts for each version. Neither approach is universally optimal – you'll have to decide.
Using advanced database versioning tools, often referred to as schema management or change management tools. The problem here is that these tools are generally singular in purpose and thus only used occasionally – plus it's just one more software package to purchase and learn.

But guess what – as usual TOAD offers a solution. It's just that many people overlook or don't find the TOAD feature, hence this blog.

Of course TOAD offers an interface to source code control systems from the editor and thus supports the second option. However there is a much better solution that handles the third option, and does so using just TOAD (with the optional DBA module/bundle) – which you already have, know and love.

I'll walk through an example for doing this at the schema level. But note that TOAD offers this for both the schema and database level objects. So it can do it all.

Let's say that I want to record today's status or version history for my MOVIES database schema and all of its database objects. I would choose TOAD's “Generate Schema Script” screen as shown here in Figure 1.

Figure 1: Generate Schema Script

However instead of generating a textual SQL file containing the DDL statements to CREATE, DROP and ALTER objects – we simply tell TOAD to make an offline snapshot of the Oracle data dictionary information or meta data. TOAD refers to this binary file as a DEF file. This DEF file will be created on your local PC's disk drive and truly represents an offline snapshot of the data dictionary. Figure 2 below shows an example of saving my MOVIES schema meta data and placing that DEF file in my C:\Temp directory.

Figure 2: Generate Schema as a DEF File

So now we have a database version history snapshot point or baseline. We can thus use this as if we still had access to the database dictionary's structural information as it was when the DEF file was originally created. So let's say that I make modifications to my MOVIES schema and then want to see the differences between the way it was and the way it is now. I would choose TOAD's “Compare Schemas” screen as shown here in Figure 3.

Figure 3: Compare Schemas

However there is now a subtle difference versus the way you might have previously used this screen. Here instead of comparing a live database schema to another live database schema, we instead instruct TOAD to compare the DEF file to a live database schema. Thus we specify the DEF file as our source with a target of the MOVIES database schema live in the data dictionary as shown below in Figure 4.

Figure 4: Compare Schemas using a DEF File

Therefore we can now capture and save (in our version control system if we want) the TOAD DEF files. That way we have an actual mini offline copy of the data dictionary. And we can use TOAD to compare and sync against those historical definitions. Furthermore, TOAD offers several other screens that can work off these DEF files – including the HTML Schema Doc screen. So look again at those screens that you find that can work off this mysterious DEF file thing. You may find it to be quite useful and worth consideration.

Toad for Oracle - Log Switch Frequency Map

Bert Scalzo — Wed, 05 Aug 2009 13:06:00 GMT

This is the 3rd of 3 blogs on Toad for Oracle DBA features

Toad for Oracle - SGA / Trace Optimization

Bert Scalzo — Thu, 30 Jul 2009 14:03:00 GMT

This is the 2nd of 3 blogs on Toad for Oracle DBA features

Toad for Oracle - Database Health Check

Bert Scalzo — Thu, 23 Jul 2009 12:02:00 GMT

This is the first of 3 blogs on DBA features in Toad for Oracle

Data is an Asset

Bert Scalzo — Wed, 15 Jul 2009 13:02:00 GMT

Sometimes information systems professionals can get “caught up” in our own worlds. For example, project managers and their developers might focus on latest and greatest application development methodologies such as Extreme Programming (XP) or Agile Development. Likewise enterprise architects might be adopting some new or updated modeling conventions. While database administrators (DBA’s) are often engaged and engrossed in the latest database versions and their new features. Now also include the new versions of the software we use to build systems, hardware’s exponential growth, new programming languages and everything else in a state of flux in our worlds – it’s easy to see why we’re often so overwhelmed that we forget some very basic facts.

The “business” is our customer. We build computerized systems to support the business. Information systems people and costs are by their very definition what the business, executives and MBA’s consider “overhead”. That means the business could theoretically run without these people and costs. It might be less efficient, but it could work. Thus we need to always remember that and maintain a deep sense of humility.
The “data” is a business asset. The business people should define it, own it and be the stewards for it. We need to learn from them enough business to build systems to support their needs (i.e. the business requirements). Thus we need to learn the meta-data from them – which is the data about the data. Then we need to provide two key facilities for handling their data:
1. Data storage containers, whether records in files or tables in databases, to house the business data or assets at rest. And when using databases, we may be able to add certain “quality checks” (e.g. constraints and relationships) to help keep that data pristine (e.g. effective). But databases are essentially just containers for the data at rest – and nothing more.
2. Data processing mechanisms (i.e. application code when executing) to affect the business data – which equates to data or assets in motion. The data assets should remain true to the business requirements during any such movement. As with the database, the application might include mechanisms or facilities for keeping data pristine – such as edit checks and data value transformations. But applications are simply computerized mechanisms for data in motion – and nothing more.

So why am I up on a “soap box” preaching this information? Because I’m seeing more and more systems that fail to deliver or miss business expectations. In fact I’ve been an expert witness for the plaintiff in several cases for seven figure application development cost recoveries. I highly recommend reading Yourdon’s “Decline & Fall of the American Programmer" for more information along these lines. It’s an older book, but the concepts generally remain the same – just substitute today’s hot terms for some of yesteryears’.

Information systems people are very intelligent, highly motivated and well compensated. We just need to maintain some humility and focus. If web hosting providers can market 99.9% uptime, the software we build should be at least 99.9% correct when handling the companies most valuable asset – the business data, whether in motion or at rest.

New TOAD Book Coming Soon!!!

Bert Scalzo — Wed, 08 Jul 2009 14:57:00 GMT

Dan Hotka and I wrote the TOAD Handbook back in 2003. Of course since then TOAD has changed quite a bit – both in terms of the graphical user interface and functionality. Thus we have updated the content and created the TOAD Handbook 2^nd Edition, which debuts September 28^th – before Oracle Open World.

This new book is “current” as of TOAD 9.7.2.5, which is the current version. You can pre-order it at online book stores such as Amazon.

I’m hoping the Open World book store will be selling it. Plus Quest may be giving away some free copies at our Open World booth – so stop by and see.

Are Database GUI Tools Evil?

Bert Scalzo — Wed, 01 Jul 2009 21:01:00 GMT

There are always those DBA’s who refuse to use GUI tools because they prefer scripts. And that’s OK – it’s a personal preference. But recently I’ve seen some stuff posted on the web and sent in email blasts suggesting that people who do use DBA GUI tools like TOAD and OEM are somehow suspect as to their skill levels. And yes, I am sure there are some people who might not know Oracle very well but get along just well enough because of such tools. I’ve seen these people sometimes referred to as “posers”. I don’t fully agree. If the person using such DBA tools understands the foundational concepts and simply utilizes them to augment their productivity, then who cares if they can recite perfect syntax from memory. Managers hire people to get work done. Sometimes the how it’s done is not as important as just getting it done (and done right of course). Are DBA tools like TOAD and OEM like loaded shotguns? Yes, but in the hands of competent and knowledgeable people shotguns put food on the table. We’re not all vice presidents who might accidentally shoot our best friend in the face. J

TOAD Database Setup Wizard

Bert Scalzo — Wed, 17 Jun 2009 14:07:00 GMT

Sometimes people will have a problem getting a TOAD feature to work – such as the PL/SQL debugger or profiler. For example a user might run TOAD and not be able to select or highlight the debugger icon on the main toolbar. So when they’re in the editor they cannot seemingly invoke or use the debugger. The initial conclusion is that TOAD must have a bug. But 90% of the time, it’s actually a database setup issue. Your DBA’s must grant TOAD users certain Oracle rights for some TOAD features to function. For example in both Oracle 10g and 11g, a TOAD user must have been granted the privilege DEBUG CONNECT SESSION for the debugger to function. We even have this specific information on the ask toad wiki. Since this privilege is not part of any of the pre-canned roles for Oracle 7 backwards compatibility (i.e. CONNECT, RESOURCE, DBA), we get this very question all the time.

Furthermore, certain TOAD features might also require some Oracle supplied database objects within your database. For example, the debugger requires the DBMS_DEBUG package is installed and granted to TOAD users. Another good example is when asking TOAD to display an explain plan – there generally has to be a PLAN_TABLE table that is available. Finally some TOAD features require some TOAD specific database objects within your database. Note that there are relatively few of these, and you must choose to turn on or use those features before the objects must be within your database. But when either the Oracle supplied or TOAD specific database objects are not there, those TOAD features that require them are missing – thus again many might initially consider it to be a TOAD bug. It is not.

To help alleviate such issues as discussed above, TOAD offers a utility to make TOAD deployment and usage automatic and easy. But you have to manually run it – TOAD does not automatically do so for you (primarily because it just needs done once, so why check every time you run TOAD). This handy utility is called the Database Server Side Objects Wizard, shown below. Somebody with proper database rights should run this once before TOAD users start using the database – it helps you to configure your database to support whatever TOAD features you think you’ll be using.

Below are going to be 8 screen snapshots of running this wizard with a brief comment about each screen. It’s not very complicated, so I won’t belabor the obvious points

The first screen is merely a welcome and identification screen where you pick what task you’re interested in performing – and which thus dictates what screen you’ll see next. In this example I’ll simply run through database object creation necessary for some TOAD features to work. Remember, this entire process is optional and you can optionally select only those items that your users will need. But when using a TOAD feature that has such a requirement, failure to run this wizard will generally prevent those TOAD features from working.

Second you simply need to provide a privileged account that can run the commands or script generated by this utility. Since you may need to create a TOAD user/schema to hold those TOAD specific database objects for some features (again, these are very few and optional), you’ll need to connect as someone who can create a user. That generally requires DBA type privileges, so here I’ve used the user SYSTEM (which I’d generally not do in a true production environment, instead I’d create my own DBA user accounts).

Third you simply choose to add, update or remove feature support for four key TOAD features: TOAD security, the profiler, team coding and Code Xpert. Since none of those database objects already exists, check boxes are disabled for remove, update, recompile, and administer. I simply chose to add support for the PL/SQL profiler – this is an Oracle specific set of database objects required for the TOAD PL/SQL profiler to work.

Fourth you choose to add or remove and grant the privileges necessary to use the TOAD space manager – which can track table space and data file growth and IO workloads over time, plus provide predictions about future growth. This feature is available on the Table Spaces screen via the last two tabs (Space History and IO History).

Fifth you decide on which method by which you want the wizard to create the TOAD schema or user. Specifically, by which collection of grants do you want it to obtain the required special privileges. Note that is only required when adding profiler support.

Sixth you simply provide the basic user creation information required to create the new TOAD user or schema.

Seventh you simply choose either to run the generated script as the privileged user that you provided on the second screen or to save it to a file. You can preview the script in the text window. There is a progress bar that updates during execution.

Eighth and finally, you simply review the object status. Basically you’re looking to see “Feature X is currently installed correctly” across the board. That’s it.

Benchmarking Myths Continued

Bert Scalzo — Wed, 20 May 2009 01:33:00 GMT

The last two posts have covered popular misconceptions or “urban myths” myths when measuring and performing benchmarks. That might seem like the “whole enchilada”, but there's yet one more critical aspect to successful benchmarking – preparation. I encounter numerous people who believe that they can simply assemble the necessary hardware, install the OS, install the database software, create the database, and then have at it. And usually they allocate just two to four weeks for all of this work. I'm reminded from my youth of the Boy Scout's motto – “Be prepared”. That's the single most important aspect to benchmarking – and yet often one of the most overlooked.

For example, many people will choose to run an industry standard benchmark such as the TPC-C for OLTP or the TPC-H for data warehousing. Usually they are relying on tools such as Quest Software's Benchmark Factory (BMF) to handle the bulk of the work – and that too is fine. But they don't read the TPC spec for the test they're about to perform, so when BMF asks them a question such as how big to scale the WAREHOUSE table – they're not really and truly prepared to answer intelligibly. The point is that the benchmarking tools assume a certain minimal familiarity with the benchmark and its spec. So if you're going to run an industry standard benchmark such as the TPC-C or TPC-H, you really need to read the spec. They can be found at www.tpc.org.

OK – so now you've read the spec. But remember last week's post about tools not being able to auto-magically build fully optimized database structures (i.e. already clustered or partitioned) since it does not know about your hardware. Well there's preparation for that too. After you've read the benchmark spec and now have some appreciation for what you're about to do, then read the full disclosure reports posted by people who've already performed and optimized these very same tests. You will find those too at www.tpc.org. For example IBM, Dell and HP post lots of great benchmarking results. You'll find that Appendix A of any disclosure reports contains the complete SQL DDL (Data Definition Language) commands they used to achieve their stellar results. Therefore simply find a disclosure report where the person was using similar database servers and disk storage hardware as you have, and then look to their DDL to see how you might best create your database objects.

You also will see some common themes across the DDL regardless of the hardware. For example all the TPC-C disclosure reports generally CLUSTER the DISTRICT table. The reason is simple. All the thousands of concurrent OLTP transactions must fight each other to update the very few rows in this table. Remember how back before sequence number generators we used to use counters in sequence table – and how that was a huge bottleneck? Well that's essentially what the TPC-C benchmark does. The DISTRICT table contains a “counter variable” column that all the transactions for the same district must update. Hence you'll see huge row contention within this table. By merely forcing each district row or record to go into its own block, the majority of the bottleneck is eliminated. And this is but a technique most Oracle DBA's and developers know about. But you need to understand the test in order to apply your wisdom and insights.

Hence why I always tell people – look before you leap. Benchmarking requires lots of preparation and understanding to best meet your expectations. But when you're prepared, benchmarking is really not all that tough – and getting stellar results is not as hard as people might think.

Push Button Benchmarking

Bert Scalzo — Thu, 14 May 2009 13:56:00 GMT

The title says it all – there is no such thing. This is the second benchmarking myth or “urban legend” that needs exposed. However this is the most prevalent frustration or exasperation that people attempting database benchmarks experience – and experience the hard way (i.e. their efforts majorly or totally fail to meet their initial expectations).

There are many reasons for this benchmarking failure – first and foremost is that people fail to allocate proper time for benchmarking. You cannot expect to complete the task in two weeks if you’re new to benchmarking, over-rely on tools to do all the “heavy lifting”, and when it might take days to even weeks to load the required workload test data.

It’s this second issue – over reliance on tools to do it all – that sinks most benchmarking efforts. Tools are a great thing. As I sit here typing in Microsoft Word, this task is much easier than in the past using manual type-writers. But I still have to think of the content. Yes MS Word offers a spell checker, but it can miss an obvious mistake such as “The dog eight my homework.” So MS Word makes the task easier – but the bulk of the work still falls on the writer’s shoulders. And to reiterate last week’s myth using this example, I do not ask MS Word for the document properties and then focus on word count (i.e. TPS) to measure a document’s success. It’s how it reads that matters

Benchmarking and benchmarking tools work exactly the same way. Let’s examine what steps a successful database benchmark effort must perform – and must perform entirely and correctly in order to potentially meet expected results:

Configure and optimize the server hardware
Install and optimize the server operating system
Install and optimize the server RDBMS (e.g. Oracle, SQL Server, etc)
Create and optimize the “TEST” database
Create database user/schema for benchmark objects (e.g. tables, indexes, etc)
Create optimized benchmark database objects for desired benchmark scale
Load the benchmark database objects to their initial or pre-workload state
Run the benchmark workload as per the spec that defines transaction nature
Monitor database performance under workload, diagnose and then optimize
Repeat steps 6, 7, 8 and 9 until satisfied (i.e. results approximate expectations)

There are probably more steps, but I tried to keep it very simple. Plus the math is easy for ten steps. You should ONLY rely on database benchmarking tools for Step 8 – “running” the workload as per spec. That means that pre-execution and post-execution steps are for you to decide and optimize.

Many people eventually “get” the fact that benchmarking tools cannot monitor and then optimize their database. So they’ll eventually utilize tools like Quest Software’s Spotlight for Oracle and Spotlight for RAC to address this need. And while they’re not happy, they begrudgingly accept the fact that running a workload does not also mean optimizing it.

But most people just seem to feel that the benchmarking tool should automatically create fully optimized database objects to contain the data the workload operates upon. On first thought that seems fairly reasonable. But after a little reflection, how can a benchmarking tool “magically” know how many “spindles” that you have and how they are allocated or grouped. In other words, how can a benchmarking tool “guess” as how to partition your database objects? Benchmarking tools can simply issue stock and/or generic “CREATE” commands. The DBA must manually optimize all benchmarking database objects based upon their particular hardware configuration.

So in Benchmark Factory terms, I always run a benchmark in two steps (i.e. two separate BMF projects). Step one simply creates stock or generic database objects which are to be loaded and then used to load optimized database objects upon which the actual workload is performed. You can consider these initial database objects and their data as a “staging” area. Yes – that require extra storage, but disk space is cheap and your time is not.

The second step is a BMF project that does the following:

Drop the BMF user/schema (not the staging area!!!)
Create the BMF user/schema
Create and load the optimized database objects
1. Copy all the data from staging to BMF schema/user
2. Uses CREATE AS SELECT – very efficient and fast
3. Uses hints for APPEND and PARALLEL – even faster
Collect database statistics for data dictionary and BMF schema
Take a database performance metric snapshot (e.g. STATS PACK)
Perform the standard workload
Take a database performance metric snapshot (e.g. STATS PACK)

So that’s how I recommend successfully handling steps 6 and 7 for creating and loading database objects that make effective and efficient usage of your disk storage subsystem, which should lead to generally achieving or exceeding your benchmarking performance expectations. Failure to work this way will almost always lead to quite unhappy results.

The Myth of Transactions per Second (TPS)

Bert Scalzo — Wed, 06 May 2009 13:27:00 GMT

As with many facets of life, database benchmarking has several myths or “urban legends” that need summarily dispelled. So I’m going to write a few short blogs focusing one by one on some of these misunderstood database benchmarking issues. Note that I am not preaching that database benchmarking is a worthwhile task, because there are many who feel it’s not. In fact I recently read an excellent Forrester paper by Noel Yuhanna on the decreased value of standardized database benchmarks. But for those who do decide to experiment with or perform some standardized database benchmarks, you will want to avoid some common misconceptions and traps.

Let’s start by first examining the concepts and measurements of a standardized database benchmark. In its simplest form, a standardize database benchmark simply requires that a well defined workload is submitted to a database server and that measureable results shall be observed. These results are then to be applied to some complex mathematical formulas which yield comparable numbers (i.e. can be used for “apples to apples” comparisons).

So in the case of the TPC-C (www.tpc.org/tpcc), an older on-line transaction processing (OLTP) benchmark, the metric used to report Maximum Qualified Throughput (MQTh) is the tpm-C – which is the number of “new orders” processed per minute. There’s other concurrent database activity and an order is more than just a single database operation, thus tpm-C represents a true "business throughput" rather than just a discrete transaction execution rate. Thus all TPC-C test results should be reported in tpm-C. Yet most people seem to focus on TPS instead. And many are just looking at the entire database workload in calculating TPS rather than successfully completed “new orders”, so they are twice as wrong (i.e. wrong metric and calculating transaction throughput wrong). Yet most people seem intent on merely getting high TPS rates as the true measure of success.

I liken measuring standardized database benchmarks to something we all know fairly well – driving an automobile. We measure driving success and failures along several driving “business” metrics:

Did we arrive okay
How long did it take
How much fuel did we use
Did we keep up with traffic

In short, we generally care about the user perceptions of the experience rather than the internal workings of the automobile engine. So things such as RPM and MPH are less important. That may seem odd – that engine work effort and velocity are not critical. But they are just internal mechanics or side effects of what we really care about (i.e. results).

TPS rates are much like RPM or MPH depending upon your viewpoint. Regardless, they are not the true measure. Either express in terms of tpm-C or something more useful from the real business perspective – such as average response time, which SLA’s often require.

Can Toad Do That?

Bert Scalzo — Wed, 18 Mar 2009 12:28:00 GMT

A lot of times people will ask the Toad Yahoo discussion group or live at Toad Tips & Tricks events whether Toad can do something. Specifically, the question might be more like can Toad version X support this very cool new Oracle feature I’d like to leverage.

For example, a user might ask does Toad 8.0 offer a screen for Oracle AWR. If you look back at my earlier Toad World blog about Toad versus Oracle versions, you’ll often find your exact answer spelled out specifically – i.e. the Toad version new feature list might say exactly what you’re looking for. So in the case of this particular question, we clearly see that Toad 9.0 adds ADDM and AWR support.

But what if what you’re looking for is not spelled out so nicely? Again, this chart can generally answer any such question – with a little effort and thought on your part. Let’s assume that Toad 9.0 did not list those two specific items and yet the question remains.

If we look at just the earliest Oracle version that offers AWR (10.2.0.1), then the first version of Toad after that was Toad 8.6. But you cannot safely assume that a Toad version supports every new Oracle feature the very first Toad release after that Oracle version releases. The better route is to look for the first Toad release after the current terminal release for that Oracle version (i.e. 10.2.0.4 – which is what Oracle tech support recommends and supports), and then choose the Toad version that came out just after that. So in this case, Toad 9.6 would be the safe answer.

In fact, here’s a summarization of that complex chart to make life easy:

If you’re using Oracle 9i, then your Toad should be >= 9.0
If you’re using Oracle 10g, then your Toad should be >= 9.6
If you’re using Oracle 11g, then your Toad should be >= 9.7 (9.8 is in beta)

The point is that software versions like Toad 8.0 were written years before the newer database versions releases. If you like using newer Oracle features – then that’s reason enough all by itself to remain current on your Toad maintenance. Because all the Toad updates and upgrades are free when you’re on maintenance.

Collaborate Coming – with Special Toad Tips & Tricks

Bert Scalzo — Wed, 25 Feb 2009 14:11:00 GMT

The joint IOUG and OAUG Collaborate 2009 conference is a just over two months away. Not only is Collaborate a premier event for Oracle database technology and applications technical sessions, it’s a good format for hobnobbing with fellow database professionals.

This year’s event is being held Sunday, May 3^rd, through Thursday, May 7^th, at the Orlando convention center. I’m taking my family along and fully intend to spend an extra day or two at Disney World.

As usual the technical sessions look very good – and note that there is going to be an all day Toad Tips & Tricks session on May 3^rd. You can read about it on their web site.

http://www.ioug.org/collaborate09/attending/university.cfm#u7

I promise that this session will be time well spent. No matter what your job title or your experience using Toad – you will leave this event saying “Wow – I did not know Toad could do that!”

Oracle Configuration for Toad Usage

Bert Scalzo — Wed, 18 Feb 2009 16:04:00 GMT

Like most things in life, there are prerequisites and preparations for Oracle databases. Regardless of version, this is especially true for application access and usage. Oracle is a highly scalable and configurable database, therefore DBAs must configure their database for how it will be used to guarantee meeting their users’ expectations – including database developers. Failure to do so can lead to perceptions of “slow performance” and sometimes even seemingly odd Oracle error messages that may unfortunately be misconstrued as application bugs. Toad is no exception to this.

Now it would be nice if I could identify the 20 or so Oracle spfile or init.ora parameters that would yield ideal Oracle database behavior for the optimal Toad experience. But, as Toad provides so many capabilities and people use it so differently, it’s hard to identify the ideal settings that universally apply. But I’ll try to identify a couple – and hope that these examples lead readers to see a pattern and address all those that make sense within their environment.

Let’s assume that you’re going to use Toad for just doing schema browsing, data browsing (whether in the schema browser and/or editor data grid), data filtering, data manipulation, data exports (i.e. data grid “Save As”) and basic SQL execution to obtain such data. Then the database simply needs to be setup to best handle typical OLTP type database access. Thus the DBA might set the following parameters:

CLIENT_RESULT_CACHE_SIZE = BETWEEN 10 and 100 MB
CURSOR_SHARING = SIMILAR
CURSOR_SPACE_FOR_TIME = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = BETWEEN 4 and 128
DISK_ASYNCH_IO = TRUE
FILESYSTEMIO_OPTIONS = SETALL
OPTIMIZER_INDEX_CACHING = BETWEEN 20 and 80 (not default of 0)
OPTIMIZER_INDEX_COST_ADJ = BETWEEN 20 and 80 (not default of 100)
QUERY_REWRITE_ENABLED = TRUE
RESULT_CACHE_MODE = FORCE
RESULT_CACHE_MAX_SIZE = DERIVED, but maybe override to taste/needs
SESSION_CACHED_CURSORS = BETWEEN 10 and 50
STAR_TRANSFORMATION_ENABLED = TRUE

Again, I’m not trying to identify every possible database configuration setting that you might need to consider – but rather to establish a pattern. Look at how your users will use Toad and then configure your database accordingly.

Now what if you’re going to be doing lots of PL/SQL compiles, executions, debugging and profiling? Not properly configuring your database for these special needs can result in many different and seemingly odd Oracle messages – including ORA-4031: unable to allocate %s bytes of shared memory. What does this mean and why did Toad seemingly cause Oracle to return this error? That’s easy – you’re doing extensive PL/SQL work that requires space within the shared pool according to the Oracle architecture and how things internally work. If you just use the default settings when creating your database, then you will generally be at risk when doing lots of PL/SQL work – regardless of the tool being used. So here are some example parameters that I’d set for this use case:

SHARED_POOL_SIZE = BETWEEN 100 and 500 MB (or more depending)
SHARED_POOL_RESERVED_SIZE = BETWEEN 5% (default) and 20%
LARGE_POOL_SIZE = BETWEEN 10 and 50 MB (or more depending)

Now some people might say “We’re using Oracle’s 10g’s automatic SGA_TARGET or Oracle 11g’s automatic MEMORY_TARGET allocation, so we don’t need to set these!” But if you know what your needs will be – then set these as floors that the automatic memory management cannot go below. In other words add your wisdom and insight to Oracle's automatic attempts at a generic solution.

Here are a few other PL/SQL specific items to review:

PLSQL_CCFLAGS = depends
PLSQL_CODE_TYPE = depends, consider NATIVE for TEST and PROD
PLSQL_OPTIMIZE_LEVEL = consider 3 for TEST and PROD

There are far more setting that we could discuss, but hopefully you can see the pattern. When setting up your Oracle database for whatever application and usage it will have to support, then properly configuring your Oracle database will help you to avoid painful and unnecessary headaches.

Happy Toading ….

Creating a minimalist Oracle 11g database

Bert Scalzo — Wed, 04 Feb 2009 16:09:00 GMT

The Oracle 11g database is nothing short of a modern technical marvel. From the features it offers to the many platforms it runs upon – it’s amazing how far they’ve come. But as a “true enterprise” database, Oracle 11g requires some DBA oversight to be applied when creating databases. Although Oracle supplies a very simple and yet fully capable database creation assistant (DBCA) utility, one needs to use it intelligently. AS I tried to point out in a prior blog, Oracle 11g on a notebook: Tread Lightly, creating an Oracle 11g database on a notebook is not ideally done with DBCA defaults. So, I thought I’d walk you through my basic recommendations for creating a light weight Oracle 11g database.

Following my recommendations, you’ll end up with a 1GB database that requires just 600 MB of SGA memory – and that on my modest IBM X61 ultra-portable notebook created in just under 10 minutes. That’s far less resources than the defaults would create.

DOWNLOAD PDF: Creating a Minimalist Oracle 11g Database (1.7 MB)

What Version of Toad Should We Use?

Bert Scalzo — Wed, 21 Jan 2009 14:39:00 GMT

We don’t get this particular question per se as often as one would expect, but we do see enough user problems where this fundamental issue becomes the underlying question to answer for problem resolution. Below is a chart for the release dates for both Toad and Oracle. The “blue highlighted” dates are when Oracle first releases a major new version, such as Oracle 10g in January of 2004. But it’s the “red highlighted” dates that are more significant – as these are what Oracle calls the “terminal release” for a database version, such as 10.2.0.4. That’s the version Oracle treats as the mainstream one that it expects its users to standardize upon – especially for support purposes. In fact an Oracle tech support call where you’re not on the terminal release probably has an above average likelihood of ending with the advice to upgrade to see if that fixes it. Thus look again below, there are four key Oracle versions of which one should be the version that your database server is on. Also note that both Oracle 8i and 9i are essentially unsupported products at this time for most people (unless you have a specific and special extended support agreement with Oracle – and these are neither cheap nor common, so don’t just say yeah we have that).

So what version of Toad should you be using? That’s easy – one that came out around the time (and preferably after) your database terminal release version. So that’s why I’ve highlighted four Toad versions in red boxes below. If you’re trying to use Toad 7.6 with and Oracle 9i, 10g or 11g database – then you’re just asking for problems. Stay current on your maintenance and Toad upgrades are free. Don’t live dangerously – Toad 9.6 or 9.7 should be your standard for most cases these days. Happy Toad’ing to you all …..

Toad “Explains” It Best

Bert Scalzo — Wed, 14 Jan 2009 15:31:00 GMT

Oracle explain plans – they are considered by many as critical when optimizing or tuning SQL statements (I however prefer to look at trace data in conjunction with explain plans). For those who prefer primarily to rely on explain plan interpretation, comprehension and improvement – Toad offers numerous capabilities to make the entire process simpler and more productive. Because let’s face it, explain plans are like much proofs in mathematics. Some people are naturally good at proofs, while the rest of us struggle with them. That’s where Toad comes in – Toad makes explain plan work trivial. In this blog I’ll just focus on how the base edition of Toad can help. For even more, Toad offers the SQL Optimizer add-on product that’s the key enabling component of the Toad for Oracle Xpert Edition.

Let’s start with a fairly simple query that’s based off the SCOTT/TIGER demo tables, specifically the EMP table. So, we have a single table query – that should not be too tough, right? Look at the code below – it contains both “AND” and “OR” operations, two similar non-correlated sub-queries, and one correlated sub-query. The explain plan should not therefore be trivial. But Toad can make working with it just that. J

select *
from emp aaa
where
( active = 'Y'
 and job <> 'PRESIDENT'
)
and

(
 (
 sal+nvl(comm,0) > ( select avg(sal+nvl(comm,0))
                      from emp 
                      where active = 'Y'
                        and job <> 'PRESIDENT'
                        and hiredate < sysdate-90)
 and
 sysdate-hiredate < ( select avg(sysdate-hiredate)
                       from emp
                      where active = 'Y'
                        and job <> 'PRESIDENT'
                        and hiredate < sysdate-90)
 )
or
 (
  sal+nvl(comm,0) > ( select sal+nvl(comm,0)
                      from emp bbb
                      where bbb.active = 'Y'
                        and bbb.job <> 'PRESIDENT'
                        and bbb.empno = aaa.mgr)
 )
);

So let’s look at the query in the Toad Editor – and what we see by default when we press the ambulance toolbar icon to generate the explain plan for the editor content. Most users know about or have done this numerous times – but please read on, because there’s more!

OK – the cost of just 14 looks good (for those who focus on cost – which may not always be the best metric in isolation). But look at that tree-view of the explain plan statements. So has Toad offered anything unique or special (so far)? No (look at the same function performed in Oracle’s (SQL Developer below). Yes, Toad also displays the explain plan’s step numbers in the order to read them – but that’s still not too easy to read in this “tree-view” format.

So just how should one read the explain plan – and is there an easier/better way? Of course the answer is an emphatic “yes” – and Toad makes explain plan usage both the easiest and best possible experience, if you know where to look. So let’s dig deeper…

First, let’s examine how one should read the explain plan for either of the prior product’s screen snapshots. Look for the most indented operations. They are performed first. Then for those that are at the same level. They execute in their hierarchical order. Another way to say that is to read the explain plan backwards using a “rightmost uppermost” order. Of course that was somewhat easier to read in Toad, because it displayed the line numbers.

But that’s just too difficult and too much work for this lazy guy. What I really want is a graphical display that communicates all that information without the line numbers even being really needed – i.e. my brain “just sees it correctly”. There has to be an easier way. Of course there is – it’s called Toad. Simply “right-mouse” click anywhere on your Toad explain plan area to display the following options:

The “Adjust Content” choice permits you to pick what output Toad will display in your explain plan – such as which “explain plan table” columns to display or not. But it’s the “Display Mode” options that are going to make your day – because both the “Graphic” and “MS Graphic” options make reading explain plans a snap. And for those of you who also do Microsoft SQL Server development, the “MS Graphic” option means the exact same display style of execution plans as Microsoft SQL Server does! Here they are:

I personally find both these styles much easier to read, and thus I can focus on making the query better – rather than spending far too much energy just reading them. Thanks Toad!

Run Toad Freeware on Linux!

Bert Scalzo — Wed, 07 Jan 2009 15:48:00 GMT

I’ve got Toad Freeware installed and running on my Ubuntu 8.10 Linux machine – and so far, not a single problem has yet occurred (knock on wood).

Here are the basic steps that I had to perform in order to “Make it so” (as Star Trek Next Generation’s Captain Piccard would say):

Install Ubuntu with Wine – make sure that the Wine version is latest and greatest
Install WineTricks to improve Wine reliability ( http://wiki.winehq.org/winetricks )
1. wget http://www.kegel.com/wine/winetricks
2. sh winetricks corefonts vcrun6
Install Toad for Oracle freeware (http://www.toadsoft.com/lic_agree.html )
1. Download both freeware files: toad_free.zip and freeware_update.zip
2. Unzip toadfree.zip file into a temporary directory (e.g. /home/user/temp)
3. Open a Linux command window and cd to that temporary directory
4. Wine toad freeware install.exe (remember to use \ before spaces)
5. Unzip freeware_update.zip file into /home/user/.wine/c_drive/Program Files/Quest Software/Toad for Oracle FREEWARE
Install Oracle 10g (not 11g) Client 32-bit for Windows
1. Download Oracle 10g 32-bit client file: 10201_cleint_win32.zip
2. unzip 10201_cleint_win32.zip file into a temporary directory
3. open a command window and cd to that temporary directory
4. cd again into the client directory and wine setup.exe
5. Make sure to set Oracle Installer’s Oracle Home directory to a Wine subdirectory such as /home/user/.wine/c_drive/oracle/product/10g/client
6. Optional Step: copy and/or create a tnsnames.ora file in that directory
Run Toad via the Linux Application Main Menu -> Wine -> Programs -> Quest Software -> Toad for Oracle Freeware

Toad + Excel = Easy Data Access

Bert Scalzo — Wed, 17 Dec 2008 14:27:00 GMT

Many business analysts rely heavily upon Microsoft Excel as one of their primary tools for manipulating and reporting upon their business data. Sometimes they will send data extraction requests to their information systems (IS) people to perform and then deliver, sometimes they’ll instead access it themselves in place via the external data interface within Excel, and sometimes they’ll extract the data themselves from the Oracle database and then perform their magic upon that data in Excel. No matter what the scenario, the facts are that business people often want to work with Oracle database information in their tool of choice – MS Excel – and thus there is an overriding need for making that data readily accessible. That’s where Toad can be of great assistance. In this week’s blog I’ll review several very key and common scenarios whereby Toad facilitates working on Oracle data within Excel.

Look at this screen snapshot below – I performed a query within the Toad SQL Editor and merely want to copy the selected/highlighted rows from the Toad data grid into an Excel workbook. I simply perform a mulit-row selection, perform a copy (i.e. control-c) and a paste within Excel (i.e. control-v) – resulting in the Excel workbook PASTE_#1. That’s it – simply the steps one might expect in a Windows environment. But what if I don’t want the column headers pasted in with the data – can Toad handle that too?

The answer (as it almost always is with Toad) is YES – Toad can do that. You simply need to investigate to find the mechanism provided for whatever task you’re looking to perform. In his case, you simply select the rows and right-hand-mouse click to see the data-grid menu, and then choose “Save-As” – which displays the dialog shown in the upper center of the following screen snapshot, and choose Excel Instance as the export format. Note that the check-box for “Include column headers” is not checked, this turns off the column headers being taken along for the ride. Now when the OK button is then pressed, Toad will now create a new workbook in an already running instance of Excel, as shown by Sheet5 below. Again that’s all there is too it – pretty simple and easy.

But what if Excel was not already running at the same time? Now what can Toad do? As before the answer is YES –Toad can once again accommodate almost any of your needs. As in the prior screen snapshot simply choose the export type of Excel file as shown below with the “Launch after creation” check-box checked – then Toad will now instead create the new Excel .XLS file with the selected Toad data and launch Excel to display it.

So what if the reason you’re sending the data over to Excel is simply so you can create a fancy PDF report for some business manager, YES – Toad could do that too. I won’t go into great detail since I’m already a couple pages into this – but search Toad’s online help for “Fast Reports” for a complete explanation. But here’s a simple example of what one might do within Toad – and possibly eliminate the need for using Excel or any other tool.

Steps to create the PDF file for the report shown below:

Multi-select your rows of data in a data grid as before
Right-hand-mouse and now choose the “Report” option
When Report Designer launches, design & save your report
Preview your report to inspect the expected output format
Save the report for later execution (they can even be scheduled)

Here’s what the report designer looks like:

And here’s an example report – which can be exported in all the file formats shown in the drop-down box below:

Virtual Paranoia?

Bert Scalzo — Wed, 10 Dec 2008 15:24:00 GMT

Lately I’ve been seeing what I think might be an interesting yet disturbing new trend: whenever there’s a problem that initially seems unusual and/or unexplainable, it’s not uncommon to quickly question virtualization as a potential source of the issue. I fully understand that virtualization adds a new variable to the basic equation – and represents greater complexity due to more moving parts. But it seems to be somewhat rushed to the head of the list. In some respects it reminds me of the days when many tech support calls started off with how much memory do you have, oh just that much, that’s your problem.

Now that’s not to say that virtualization is without some additional concerns that must be added to the mix – some of which can radically affect the results. But as far as true issues that virtualization breaks – I’ve run into just two so far. Some client operating system and database monitoring tools are very sensitive to the real time clock, and any skew between the host and client VM may yield slight variations that might possibly affect some results. And second, some older Windows versions have poorer memory management that seems not to work 100% reliably on VM’s. Shy of these, I’ve yet to encounter any problems that I can point the finger to the virtual machine software (e.g. VMware).

So when someone says “I can’t explain problem X and it doesn’t occur on non virtualized machine that’s setup exactly the same”, please make sure only to accept such arguments with a fair and reasonable amount of verification. Let’s not waste any precious resources and cycles chasing such an easy scapegoat. There may be issues where virtualization is legitimately the culprit – just not most of the time. So let’s not promote it from obscurity to the head of the class.

Oracle + VMware = Success Part IV

Bert Scalzo — Wed, 03 Dec 2008 18:47:00 GMT

A few weeks ago I wrote Part III of this series – and fortunately (or unfortunately for my poor ego) an astute reader wrote to ask for some clarification on my Excel chart. Ends up my Excel formula had a mistake – so many thanks to Johannes V. Djernæs from Miracle A/S in Denmark. So I’ll be sending him a few free books and other goodies. Thus note that it can pay dividends to read and reply to these blogs. Now I’m off to double check my checkbook’s latest math as well – I’m probably already broke and don’t know it.

So here’s the corrected text and chart. As Homer Simpson would say – doh.

Let’s assume we have a Linux host running a Linux based Oracle database client. Let’s examine Scenario #2 from the chart below. Start by assuming that we create the host file system using the default block size: 2K. Let’s further assume that we do the same thing on the client – but that we size the database blocks at 4K. Thus each Oracle physical IO requests asks the client OS for two IO’s, and each client IO asks the host for one IO. That’s a total of four logical IO requests (although only two physical IO’s in reality – but note that there is overhead for each logical IO request, so larger numbers are worse). Now if the database block size had instead been 8K while the other factors had remained at 2K (i.e. Scenario #3), then the logical IO’ would instead now be DB -> Client = 4 and Client -> Host = 4 for a grand total of eight logical IO requests. Here’s a basic chart for some common combinations and their sometimes surprising overheads:

Obviously there are some very bad choices in the chart above. So a seemingly good 16K database block size might actually result in a total of 12 logical IO requests across the virtualization infrastructure under the worst case scenario. While it’s still effectively just 16 K being read, the extra overheads added by this mismatch only serve to multiply the bad effects. So choose wisely.

Speeding-Up Oracle on Your Notebook PC – Part 2

Bert Scalzo — Wed, 26 Nov 2008 14:50:00 GMT

Just a very brief blog this Thanksgiving week (my favorite holiday of the year) – part 2 of last week’s relatively easy suggestions on how to make Oracle run as fast as possible on minimal computer setup. As before, my goal is simply to squeeze as much performance blood from the Oracle turnip as possible when deployed on limited capacity equipment. The one additional suggestion below only makes sense for environments where instance recovery is not a key issue – e.g. a demo notebook PC for traveling consultant types. So make sure not to use this unless you understand what the drawbacks are (discussed below) and can live with them.

Place the Oracle REDO LOG files on a RAM Disk. For extremely transaction heavy environments this can make a huge difference. But for general purpose usage, it may provide just a small improvement. Thus apply and use the technique with forethought.

While Oracle is running it is writing transactional history to the REDO LOG files such that if the instance is brought down unexpectedly, then the outstanding transactions can be played forward to restore to the state of the instance failure, and then rolled back for the un-committed transactions. If you’re running in NOARCHIVELOG mode, then the contents of the REDO LOG files get overwritten once the database transactions’ round-robin consume the allocated space – so database transactions can get lost (i.e. aged out) anyhow in this setup. So placing the REDO LOG files on the RAM Disk just adds one more scenario where data is aged out (and rather abruptly) – at computer shutdown.

So for purely demonstration or development environments, where raw speed is the most critical factor – this technique has merit. If it makes sense for your database computing needs, then give it a try …

Run Toad off a USB Flash Drive

Bert Scalzo — Wed, 19 Nov 2008 21:32:00 GMT

Lot’s of people have asked “How can I setup Toad to run off a flash drive?” The reason for this question varies in nature and intent by requestor – but the general concept is that some users need the ability to carry Toad with them. Some are contractors who must use whatever computer and/or desk is open that day. Some are database developers who must travel around their company to perform or assist with various tasks. And finally, some are DBA’s who need to travel around while diagnosing and troubleshooting problems. In all cases, the request makes total sense (i.e. making portable a legal license). Now we have a way to do it!

Note – Toad is normally licensed per seat, therefore this is not a mechanism or technique to circumvent or violate the spirit of that license agreement. The basic idea is that per seat means for a single USB Flash Drive being used by a single user on a single computer at a time – and thus no concurrency.

Here are the steps:

Format the USB Flash Drive as an NTFS file system.
1. File explorer, right hand mouse, properties, hardware tab
2. Select the USB Flash Drive and press the properties button
3. Policies tab, choose Optimize for Performance and press OK
4. Format the USB Flash Drive – NTFS should now be available
Create TOAD_FLASH directory on your USB Flash Disk (at topmost level)
Download and then unzip the TOAD_FLASH.zip file onto your USB Flash Disk
Unzip the contained Oracle Instant Client installation file onto your USB Flash Disk
Install Toad for Oracle and copy the Toad home (install directory) to your flash drive

Example: xcopy /s /q "C:\Program Files\Quest Software\Toad 9.7" "X:\Toad 9.7\"
Run TOAD_FLASH_SETUP.bat file to install (and save pre-existing Toad setups)
Run TOAD_FLASH_RUN.bat file to launch Toad (must enter license first time)
Run TOAD_FLASH_RESTORE.bat file to restore PC back to prior Toad state

You can download the file at http://www.bertscalzo.com/Files/TOAD_FLASH.zip

Speeding-Up Oracle on Your Notebook PC

Bert Scalzo — Wed, 19 Nov 2008 15:29:00 GMT

Just a quick blog this week – some relatively easy suggestions on how to make Oracle run as fast as possible on minimal computer setups such as a notebook PC. My goal is simply to squeeze as much performance blood from the Oracle turnip as possible when deployed on limited capacity equipment. The suggestions below should work for most operating systems, including Windows, Mac-OS and Linux – simply interpret and apply the suggestions in a contextually appropriate manner.

Exclude Oracle data files directory(s) from all anti-virus and anti-spyware automatic and manual scans. Myself, I exclude the entire “C:\Oracle” directory – where I place all my Oracle files.
Eliminate all unnecessary background processes (demons) or services. Here are some basic examples (note that your computing environment needs and thus extraneous list will surely vary – so check with your local administrators to be sure and thus safe):
1. Windows XP
  1. ATI Hot Key Poller
  2. Computer Browser
  3. Distributed Link Tracking Client
  4. Error Reporting Service
  5. Indexing Service
  6. Upload Manager
2. Redhat Linux(s)
  1. anacron / cron
  2. httpd
  3. ISDN
  4. Net FS
  5. NFS Lock
  6. Send Mail
Clean up (i.e. purge) the Oracle logging directory tree. For 9i and 10g, this was subdirectories such as BDUMP and UDUMP under your “admin/SID” directory. Starting with 11g, you need to look at the entire “DIAG” directory. Myself, I use a freeware called CCCleaner to purge all these files such that they don’t accumulate.
Place the Oracle “DIAG” directory on a RAM Disk. This also has the side effect of automatically purging the files (step #3 above) when the laptop is shutdown.
Exclude Oracle data files directory(s) from excessive or unnecessary last update timestamp maintenance.
1. Windows
  1. ```
  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate = 1
```
2. Redhat Linux(s)
  1. chattr +A file_name per Oracle control, data and log file
  2. chattr –R +A directory_name per Oracle SID data directory
  3. Edit /etc/fstab for the Oracle Home and/or data file systems
    Example: /dev/sda6 / home ext3 defaults,noatime 1 1

Just Make Oracle Go Faster

Bert Scalzo — Wed, 12 Nov 2008 16:25:00 GMT

Catchy title – huh? But don’t laugh, in today’s hectic world many database professionals (whether DBA’s or developers – and sometimes even end users) just wish that the Oracle database would go faster. That may seem self evident – but let’s just accept that premise for now. So what does this mean for Toad users?

Toad offers numerous database monitoring, diagnostic and tuning screens or utilities. But not all of us have sufficient spare time to dig through the database haystack to find such issues. Moreover we all don’t know the database internals anymore – because Oracle 10g and 11g have become such technical behemoths. For example, just how many of us now know all the various wait events and what they really, really mean anymore? There are just too many to stay an expert on every little internal detail.

So what’s an Oracle professional to do? How should we attack a performance problem where we know that the SLA is being missed – but there are a lot of things going on that we may or may not know all the details about. And like the database, applications have become very complex too – often with numerous technology layers beyond the database. So how can we quickly and easily locate the needle in the haystack? Because we all want to concentrate on fixing items that quickly yield measurable results.

For those who have licensed Oracle’s diagnostic and tuning packs – Toad offers screens to support more productive work with those facilities. Because not everyone wants or is permitted to run OEM – as some shops limit OEM access to production DBA’s. That’s OK as Toad with the DB Administrators module (or bundle) can help you out here.

My experience has been that the fastest way to locate and concentrate on the most likely database performance issues is via Oracle’s AWR and ADDM reports. For those who do not have those Oracle options (they are optional OEM add-on licenses) – you can very often use the Stats Pack report in a similar fashion. Simply take a manual AWR and/or Stats Pack snapshot before and after some time period where database performance is generally not acceptable. Then run the reports using Toad’s screens (rather than OEM).

Here are examples of the AWR and ADDM screen’s that I’m talking about – and the tabs (i.e. reports) of key interest. Often just these two reports alone can point me into the right direction for where to spend my tuning time and effort.

Figure 1: Toad Screen for AWR Report

Figure 2: Toad Screen for ADDM Report

By starting with an analysis of just these two reports, I can often locate the most critical areas of concern and thus promptly address the most major performance issues – and all within just a few minutes time. What I’m saying is that for 20% of my diagnostic time and effort, I can locate and often correct 80% of my biggest issues.

Then from here, I can now dive into the other more complex or detailed Toad screens or utilities for performance optimization – such as the Database Health Check (part of Toad Database Admin Module) and/or the SQL Optimizer (part of Toad Xpert Edition).

Thus I can use Toad to quickly solve some major issues (so as to quiet things down a bit so I can look further). Then I can use the many other Toad features to find the remaining and more complex performance issues that need attention. So Toad permits me to do the heavy lifting very fast, and then to follow-up with the more intricate work to its logical conclusion. In other words, Toad can do for database performance what it has always done for SQL and PL/SQL development – improve your productivity by letting you concentrate on the task at hand rather than the Oracle mechanisms to do it.

You gotta love Toad J

Toad Makes DB Waits Easy

Bert Scalzo — Wed, 05 Nov 2008 15:19:00 GMT

I’ve always been a huge fan of Cary Millsap’s “Method-R” technique for Optimizing Oracle Performance via detailed analysis of all of the business process wait states. It is fundamentally sound since it’s based on genuine business concerns and detailed analysis of a mission critical business processes’ overall execution time (and not simply just the database waits or ratios). But while many people like this approach, few have found the tools to support this technique. But Toad offers many screens for DB Wait analysis. Did you really expect me to say otherwise?

Let’s examine a few (most assume that you own the DB Admin module or bundle).

If we want to begin an end trace collection for a particular database session in order to create database trace files, then we simply use Toad’s Session Browser as shown below. Remember, all of the trace files will be on the database server – and we can retrieve them using Toad’s FTP Screen.

Figure 1: Toad's Session Browser

Once you have the trace file, you can then explore it using Toad’s Trace File Browser as shown below. Note that this screen offers a link to the FTP screen for convenience.

Figure 2: Toad's Trace File Browser

I could go on as there are numerous other screens to support this database wait centric optimization technique. But then the real question arises – “What does this all mean?” We’re all not wait event experts like Cary Millsap, Tom Kyte, Mike Ault and many other Oracle experts out there. That’s where Toad makes life easy once again – as all the most common wait events have pop-up windows to explain why they generally occur and how to possibly fix them. These pop-up help screens are very easy to locate as they always shows up as a blue underlined hyperlink on wait event screens. So when you double-click on them, you get help such as this.

Figure 3: Wait Event Explanation Screen

Below are screen snapshots of just some of the many other places where you’ll find this wait event explanation feature in Toad. Remember to simply look for database wait event information (i.e. tabs) on a particular screen, and then double click on those marked with a blue underlined hyperlink. Now we all can look like wait event know it alls J

Figure 4: Wait's on Trace File Browser

Figure 5: Wait's on the Session Browser, Sessions

Figure 6: Wait's on the Session Browser, Waits #1

Figure 7: Wait's on the Session Browser, Waits #2

Figure 8: Wait's on the Session Browser, Waits #3

Oracle + VMware = Success Part III

Bert Scalzo — Thu, 30 Oct 2008 14:25:00 GMT

A few weeks ago I wrote about how simply adjusting various virtualized infrastructure default settings that a database could perform up to 440% better. Of course “your mileage” will vary, but the key point is that taking the time to properly layer your virtual infrastructure settings can have substantial impacts – which are only magnified by the net or cumulative effect of a virtual server hosting multiple database servers. And while my example was specific to Oracle, the same principles generally apply for any database being deployed on a virtual server.

I presented this information since there are still many who question deploying database servers on virtualized platforms. My key contention is that as long as there are sufficient resources and/or bandwidth to handle the net requests, that requiring database servers to be islands onto themselves is now passé’. Even if we say that there is a 10-20% overhead (which I’m inclined to say is closer to ten than twenty), with cheap hardware these days the benefits just far outweigh the negatives – just spend 10% more and get a bigger server to handle multiple databases. But it may be a few years before that opinion is generally or more widely accepted.

This week I’m going to elaborate on just one of the techniques I used to get such stellar results. If you refer back to the first blog, I’m going to explain choice #10. As a reminder:

#10 = Adjust VM client file system block size to more closely match Oracle block size

The default block size for both the host and client OS file systems’ is generally not the same as your Oracle block size (although hopefully the Oracle block size is a multiple of the OS file block size). Since the host may be servicing multiple Oracle databases with different block sizes, different database platforms (e.g. MySQL), or being used as to host other applications (e.g. web server) – we cannot always make this adjustment at the host level. But generally we can make it for each of the Oracle database clients.

Let’s assume we have a Linux host running a Linux based Oracle database client. Let’s assume that we create the host file system using the default block size: 2K. Let’s further assume that we do the same thing on the client – but that we size the database blocks at 4K. Thus each Oracle physical IO requests asks the client OS for two IO’s, and the client asks the host for two IO’s. That’s a total of four IO requests (although only two physical IO’s in reality – but there is overhead for each request, so larger numbers are worse). Now if the client had instead created its file system at 4K, then the IO numbers would instead be client = 1 and host = 2 for a total of 3 (which is obviously less than 4). Here’s a chart to show some common combinations and their sometimes surprising overheads:

Host OS block size	2	2	2	2	2	2	2	2	4	4	4	4	4	4	4	4
Client OS block size	2	2	2	2	4	4	4	4	2	2	2	2	4	4	4	4
DB block size	2	4	8	16	2	4	8	16	2	4	8	16	2	4	8	16
Total IO requests	4	7	13	25	5	7	12	22	4	7	13	25	4	6	11	21

Obviously there are some very bad choices in the chart above. So a seemingly good 16K database block size might actually result in total 25 IO requests across the virtualization infrastructure. While it’s still effectively just 16K being read, the extra overheads added by this mismatch only serve to multiply the bad effects. So choose wisely.

Oracle + VMware = Success Part II

Bert Scalzo — Wed, 22 Oct 2008 10:40:00 GMT

Last week I wrote about how simply adjusting various virtualized infrastructure default settings that a database could perform up to 440% better. Of course “your mileage” will vary, but the key point is that taking the time to properly layer your virtual infrastructure settings can have substantial impacts – which are only magnified by the net or cumulative effect of a virtual server hosting multiple database servers. And while my example was specific to Oracle, the same principles generally apply for any database being deployed on a virtual server.

This week I’m going to elaborate on just one of the techniques I used to get such stellar results. If you refer back to the prior blog, I’m going to explain what choices #5 and #9 are. As a reminder:

#5 = Change VM host registry settings to improve file system IO for databases
#9 = Change VM client file system settings to improve IO performance for databases

In reality, this is one and the same improvement simply being applied to two different virtualization levels: the host and each client. Thus I’m simply going to present how to accomplish this technique for both Windows and Linux, and then leave it to the reader to make sure to apply it properly across all their various virtualization layers.

Both the Windows NTFS and Linux ext2/3 file systems maintain multiple “meta-data” information related to file access – such as date created, last time updated, etc. So an IO request might actually generate multiple physical IO’s – one for the data file and one or more for updating the related meta-data. In the case of the VMware server, we really don’t care to keep OS detailed file system information about the hosted clients’ data file access – it’s simply neither useful nor critical (unless doing snapshots). And at the Oracle database level, we know Oracle accesses its files every so many seconds – so why pay to update the meta-data with that information. Oracle keeps its own timestamps (i.e. SCN) in the file headers.

For Windows, we simply adjust the following registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\
Control\FileSystem\NtfsDisableLastAccessUpdate = 1

By the way, Toad offers a screen to do this for Windows:

For Linux, there are several ways to accomplish the same result. We can set the attribute for the individual Oracle files as follows:

chattr +A file_name

Or we can do it for an entire directory:

chattr –R +A directory_name

However the best method (because it automatically handles any file additions) is to edit the /etc/fstab and add the NOATIME attribute:

/dev/sda6    /              ext3    defaults,noatime    1 1
/dev/sda1    /boot          ext2    defaults,noatime    1 2
/dev/cdrom   /mnt/cdrom     iso9660 noauto,owner,ro     0 0
/dev/sda5    swap           swap    defaults            0 0

Many people ask if I know similar settings for other operating systems such as AIX, HPUX and Solaris – I don’t. But please reply and share that information.

Oracle + VMware = Success

Bert Scalzo — Tue, 14 Oct 2008 22:59:00 GMT

Many people swore that database on a virtual machine would not fly – or at best, would be accepted very, very slowly. I was not one of those people – so I wrote a new book on just that subject: Oracle on VMware: Expert tips for Database Virtualization. However I don’t expect people in these tough economic times to just go out and buy every new book, so I thought I’d share some other papers I’ve seen worth reading on the subject – plus a brief synopsis on the Oracle + VMware presentations I did this year at Oracle Open World.

One of the first and most robust papers I’ve seen on the subject is from VMware itself: Deployment of Oracle Databases on VMware Infrastructure. This fine paper is 119 pages of highly useful DBA information for both effectively and efficiently deploying Oracle databases on a VMware infrastructure. For those who don’t have the funds for my book, this is a darn good choice. And even if you get my book, the paper makes an excellent companion.

Now at first Oracle was a little slow to fully embrace databases on VM’s. But then they débuted their own virtual machine solution – and things changed. There are now papers that show that benchmarks of VM’s versus bare metal are reaching acceptable results. There are even papers now for successfully deploying RAC on VM’s. So the trend winds have changed. Virtualization is no longer verboten for databases – and I for one think the world is better for it.

So at Oracle Open World, I presented some materials to show that deploying Oracle on VMware with “defaults across the board” could cost you as much as 440% in terms of achievable performance. However, we must approach configuration, optimization and tuning in a slightly different manner – because we now must fully consider the four core shared resources shown here:

So by using Quest tools

Benchmark Factory to generate a database load
Toad for Oracle with DB Admin, Spotlight for Oracle and Performance Analysis for Oracle to monitor and diagnose the database performance
Foglight for VMware to monitor and diagnose virtual machine performance

I was able to easily modify all the default settings across the board to obtain that 440% improvement for the TPC-C OLTP benchmark (your mileage will of course vary). See below how the response time for 300 concurrent users decreased from 1080 ms to 200. And while 1080 ms (i.e. 1 second) may have been acceptable, what would happen if the user load increased to 1000 or more users. Obviously the tuned scenario would scale much better to handle a much higher concurrent user load.

So what black magic was applied to get these results? The ten simple steps listed below (note that the VM server was running Windows 2003 Server, but similar issues would also apply had it been Linux or some other OS):

Obtain a baseline test for relative comparisons
On VM host, exclude VM clients from active, online virus scans
Remove Windows Indexing Service – because don’t need fast file searches
Remove other extraneous Windows services
Change VM host registry settings to improve file system IO for databases
Optimize the VM host configuration and options
Optimize the VM client OS configuration and options for Oracle database
Remove other extraneous VM client OS services or daemons
Change VM client file system settings to improve IO performance for databases
Adjust VM client file system block size to more closely match Oracle block size

If you’re curious about #’s 5, 9 and 10 – I’ll write about those in an upcoming blog.

RAC the Casba

Bert Scalzo — Wed, 17 Sep 2008 13:44:00 GMT

On many occasions, people will email or call me to get help with perceived RAC connectivity issues for various client applications – Toad and other Quest products included. Now some times they may have setups that we’ve not pre-tested, so they have a potential bug – or at least another regression testing scenario for us. But many times, it’s simply a SQL*Net configuration issue. While it’s easy to draw a simple diagram of what RAC is, it’s almost an art form to write RAC “tnsnames.ora” files that work as intended. So I figured a quick refresher on RAC and SQL*Net configuration files might be useful.

Let’s start by drawing a picture of the basic concept of an Oracle Real Application Cluster (a.k.a. RAC). RAC is essentially a group of database servers and Oracle instances running as a collective group so as to appear as a single server and database to the outside world. Thus to all the end users, web servers and application servers out there – there is really just the RAC server and database. The fact that it’s a group performing as a cluster is really transparent and unimportant – except the correct tnsnsmes.ora files J

So what should the tnsnames.ora file look like? Let’s show an example where the RAC database has just two servers and instances: RAC1 and RAC2.

RAC =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac1-vip)(PORT=1521))
    (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac2-vip)(PORT=1521))
    (LOAD_BALANCE = YES)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
 )

RAC1 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac1)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = RAC)
            (INSTANCE_NAME = RAC1)
        )
    )

RAC2 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac2)(PORT=1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = RAC)
            (INSTANCE_NAME = RAC2)
        )
    )

So let’s use Toad as an example – how should we connect to the database? Generally speaking, probably 98% of the time we should simply specify the RAC instance/service name and let Oracle load balance us to whichever node has capacity. Only when we need to debug PL/SQL should Toad users (because of an Oracle limitation/requirement) specify connecting to a specific node – such as RAC1. That’s because the Oracle debugger API needs to create two sessions on the same node so one can watch the other. If we tried to debug using RAC, it might load balance the two sessions onto different nodes – and then we’d get a debugger scoping error. But for all other times we should simply use RAC to connect – period.

Finally, look at the RAC entries for the TCP host addresses. Why do they say node-vip instead of just node as with the RAC1 and RAC2 corresponding entries? What is the VIP portion? Let’s look at my PC’s Windows hosts file:

192.168.100.104   linux_rac1
192.168.100.105   linux_rac1-vip
192.168.200.104   linux_rac1-priv
192.168.100.106   linux_rac2
192.168.100.107   linux_rac2-vip
192.168.200.106   linux_rac2-priv

Why are there three IP addresses per database server? Look back to our RAC architecture diagram above. The RAC instances require two distinct TCP/IP addresses, one for the public network (e.g. linux_rac1) and one for the private network (linux-rac1-priv). So what exactly are the seemingly extra entries for linux_rac1-vip and linux_rac2-vip, and why do we use them as our addresses for the RAC entry in our tnsnames.ora file?

When the DBA sets up the cluster nodes, one of the steps involved creating virtual IP addresses. That’s what these entries point to. A virtual IP address is the path by which users should access the node, and whenever a node crashes – Oracle can dynamically reassign the virtual IP address to point to one of the other nodes in the cluster. That way we have transparent application failover (TAF) – because all the network access is virtualized and dynamic. Hence nodes can come and go, or be physically relocated – and to the database nothing has changed. Only the Oracle listeners on the database servers need be aware of this dynamic addressing scheme.

Speed Up Toad - Updated

Bert Scalzo — Wed, 10 Sep 2008 11:38:00 GMT

It’s not uncommon for software to increase in install size and memory footprint over the years – and Toad is no exception. But, that does not mean that these newer versions have to run more slowly. We take great pride in keeping Toad expedient as it matures. Thus do not simply assume that if your Toad is running slower these days – that it’s just the “cost of progress”. Most often there are simple steps to keeping Toad humming along.

Below are ten easy ways to make sure you’re getting the best productivity from Toad:

Run the Toad Advisor – and see what it turns up. Often the “low hanging fruits” it points out can make a significant performance improvement for the few seconds it takes to run and examine.
Make sure your Oracle data dictionary (i.e. catalog) statistics are appropriate for your database version. So if your Oracle database version is 9i or less, there should be NO statistics collected for SYS or SYSTEM. And if your Oracle database version is 10g or greater, then there must be statistics gathered for SYS and SYSTEM. And note that Oracle 10g does NOT do this automatically for you. Unfortunately, the automatic 10g “stats job” does everything except these schemas. See the following for more info:

http://asktoad.com/DWiki/doku.php/faq/answers/database_versions#why_can_toad_sometimes_seem_to_run_slower_on_10g

http://www.toadworld.com/Community/ExpertsBlog/tabid/67/EntryID/135/Default.aspx
Make sure the option for “Highlight Table Names” is off – as this setting requires Toad to load all database table names into memory. This could lead to excessive memory consumption and slow connection times. This is probably the single most often reason people seem to experience slowness – especially on larger databases.
Verify that your option for “SQL Recall: Statements to Save” is set to a reasonable size. Toad has to read and parse the XML file /user files/SAVEDSQL.XML every time it starts up. XML by nature is not too brief, so having a large saved statement size can easily result in an overly big XML file. You may even need to truncate or replace that XML file if your initial setting is too high and results in too big a file. NOTE – Toad has reworked the way is does saved SQL, so this basically only applies to Toad 9.5 and before.
Watch out for “network drives”!!! If you have network drives in your Windows path, SQL path, or any of the Toad options that write to files – and especially the ones that do so automatically at timed intervals (e.g. save settings every, backup editor every, etc) – then you’re just asking for things to run slowly.
Review the editor option for auto-save timing (i.e. backup every N minutes) and think about how many editor windows and/or tabs that you routinely work with. I’ve seen where power users (those who have dozens of editor windows and/or tabs open) will see a noticeable hiccup every so many minutes – and wonder why.
Review your general options setting for OCI Array Buffer Size. It defaults to 500. If you’ve set it lower or higher, you be sending excessive network packets (where the # of network packets = amount data returned from database server to Toad / OCI Array Buffer Size). If you’ve set it higher, you may run into PC limitations depending upon your machine’s memory – and the number of applications you run concurrently.
Review your compile option for Compile Body and Spec as a Pair. Remember that for many versions of Oracle, when you compile the Spec you also cause an Oracle internal dependency recompilation flag to be set. Thus in Toad if you’re compiling the dependencies, you may be creating more work for yourself. Plus, you might be affecting others’ work. Be a good compilation citizen.
Review your Schema Browser Data & Grid option setting for Enable FK Lookup. If you have numerous foreign keys, if the foreign keys involve many columns, or if the lookup tables are really big – these can slow down the performance of the data grid.
Review your Schema Browser Data & Grid option setting for Use No Parallel Hint. When tables and/or databases have a default parallel degree setting, then the Schema Browser Data Grid browsing will use parallel queries. That means you’ll spawn lots of server processes to do a simple task – plus Oracle persists those processes so as to save on the creation/destruction costs. Checking this box can often reduce database server process resource consumption – and like chicken soup, it won’t hurt J

Toad Executing Scripts against Multiple Databases (Part 4)

Bert Scalzo — Mon, 18 Aug 2008 17:46:00 GMT

Last week’s blog was the third in a series about how to make Toad run scripts and/or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I will highlight the fourth and final (???) method of running scripts against multiple databases using the new Toad “App Designer” (the modern and very robust replacement for the old command line interface for running Toad actions). The new “App Designer” makes almost anything doable in Toad able to be fully automated, scripted, scheduled, etc. I cannot in one short blog elaborate on the near limitless capabilities this new feature makes possible, so for now I’ll just focus on the very simple task of running a script against several databases at once – the mantra of this particular blog series.

Here’s a table space report I like to run regularly. While I might want to schedule this report to run against all my databases and email me the output (which the App Designer can do), for now I’ll just show the basic steps required to run the script against my collection of databases manually. Here’s a screen snapshot of the App Designer.

The steps to perform are as follows:

Press the Create New App toolbar icon – give the application a name
Press the Execute Script toolbar icon – fill in the screen presented
Name the script
Define the script file name for output criteria

Did you note the right-hand-mouse menu option for variables? That presents a pop-up window with a collection of useful predefined substitution variables. So in this example case, an execution will create a file names TableSpace_Report_SID_DATE.txt – where SID = the database connection ID and DATE = the system date with special characters stripped out so it can be used in a file name. That’s pretty much it. The Application Designer will create a text file with the application name – and it contains code that looks like the following. But don’t sweat – you don’t need to worry about this content, it’s merely the meta-data information necessary to drive Toad.

object TarActionSet
 Enabled = True
 ID = 1
 ParentID = -1
 UserName = 'Tablespace_Report'
 Categories.Strings = (
    'Default')
 Opened = False
 RunData = {
    545046300A54617252756E44617461025F310B416374696F6E536574494402FF
    02494402FF084661696C4D6F6465070A666D436F6E74696E7565065374617475
    7307096173537563636573730000}
 ExecutableItems = {
    545046301254617245786563757461626C654974656D7300054974656D730ADF
    0300005450463010546172457865637574655363726970740007456E61626C65
    6409024944020208506172656E744944020108557365724E616D65060F457865
    6375746520536372697074310B416374696F6E536574494402000B4C61737452
    756E44617465050068C2F78359FE9A0E400C536372697074536F757263650707
    737346696C657306546172676574070F7474446973636172644F757470757407
    52756E446174610A45010000545046300A54617252756E44617461000A416374
    696F6E4E616D65060F4578656375746520536372697074310B416374696F6E53
    6574494402010D416374696F6E5365744E616D6506115461626C657370616365
    5F5265706F72740249440202084661696C4D6F6465070A666D436F6E74696E75
    650752756E44617465050068C2F78359FE9A0E400752756E5573657206074253
    43414C5A4F104D657373616765732E537472696E6773010640382F31382F3230
    303820383A32333A333120414D3A202045786563757465205363726970743120
    2845786563757465205363726970742920737461727465642E0641382F31382F
    3230303820383A32333A333120414D3A20204578656375746520536372697074
    3120284578656375746520536372697074292066696E69736865642E00065374
    6174757307096173537563636573730000064C6F67696E730A67010000545046
    300A544C6F67696E5265637300054974656D730A4A0100005450463009544C6F
    67696E5265630011456E6372797074656450617373776F7264067731372C302C
    302C302C3232322C34352C3233342C3233322C36342C3230312C3132312C3738
    2C35352C32312C392C3234332C33392C3234342C35392C34392C3131312C352C
    3137362C37352C3231382C3133332C36372C3137392C3232372C3133372C3135
    302C35352C3235322C35332C32342C3231340455736572060442455254065365
    7276657206054F52444231064E756D62657202000B4C617374436F6E6E656374
    05006007804D58FE9A0E4009436F6E6E656374417306064E4F524D414C05436F
    6C6F7204008000000A4F7261636C65486F6D65061D433A5C4F7261636C655C70
    726F647563745C31312E312E305C64625F310B4175746F436F6E6E656374080C
    5361766550617373776F726408084661766F7269746508064D6574686F640200
    000000001145786563757461626C65536372697074730A65000000545046300C
    5461724469736B4974656D7300054974656D730A46000000545046300B546172
    4469736B4974656D00044E616D650616433A5C42535C426572745C53514C5C74
    73702E73716C0A45786563757461626C6509084973466F6C6465720800000000
    00000000}
end

So here’s my Windows Tablespace_Report.bat file which I can double click on to run or schedule:

call "c:\program files (x86)\quest software\toad 9.7\toad.exe" bert/bert@ordb1 -a tablespace_report
call "c:\program files (x86)\quest software\toad 9.7\toad.exe" bert/bert@ordb2 -a tablespace_report
call "c:\program files (x86)\quest software\toad 9.7\toad.exe" bert/bert@ordb3 -a tablespace_report

That’s all there is to it. But please remember, the new App Designer can permit one to do nearly limitless cool things – so experiment away and enjoy

Toad Executing Scripts against Multiple Databases (Part 3)

Bert Scalzo — Wed, 13 Aug 2008 15:18:00 GMT

Last week’s blog was the second in a series about how to make Toad run scripts or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I’ll highlight the third method of running scripts against multiple databases using the “Toad Editor” and Dynamic SQL Scripting. This week’s method is neither the easiest nor the best, it’s just yet another way to accomplish this task. It also has the benefit of building on one of my earlier blogs about Dynamic SQL Scripting via Toad. Furthermore, it starts us thinking about the problem in terms of its atomic operations – which will nicely lead us into next week’s fourth method (i.e. Toad Actions and Application Designer – the replacement for the older Toad Command Line facility). So please read on.

I have the Dynamic SQL script (shown here) which I use to coalesce all the tablespaces in the current database. If I load this script into the Toad Editor and press F5 (Execute Script), it will coalesce all the tablespaces in that session’s database – and only that database. There’s nothing too fancy here.

set term off
set pagesize 0
set verify off
set feedback off
spool coalesce.tmp
select 'alter tablespace '||tablespace_name||' coalesce;'
 from dba_tablespaces
 where status = 'ONLINE'
    and contents not in ('TEMPORARY','UNDO');
spool off
set term on
set echo on
@coalesce.tmp

Now what if I want to run this script against multiple databases? Well, I can simply use the exact same technique (Dynamic SQL) that now calls another Dynamic SQL script to get Toad to run the commands against multiple databases. Look at the script below in Toad’s Editor and the resulting output – mission accomplished! So just what did I do here? I merely wrote a Dynamic SQL script that calls the coalesce.sql script for the hard-coded databases. And yes, I know using SYSTEM is a bad practice, as is hard coding the user id and password. But I wanted to show this first. A slightly better method will be shown later.

The slightly better (i.e. more secure) solution is slightly more complex – but also happens to be much easier to dynamically make work for any number of databases without making any code modifications.

set term off
set pagesize 0
set verify off
set feedback off
spool c:\temp\coalesce_all.tmp
select 'spool c:\temp\coalesce_all.log' from dual;
select 'connect ‘||SYS_UID||’/’||SYS_PWD||'@’||DB_SID||’
'||'@coalesce.sql'
From berts_system_user_id_table;
select 'spool off' from dual;
spool off
@c:\temp\coalesce_all.tmp

Remember – in the weeks to come I’m going to demonstrate other ways to execute SQL scripts against multiple databases at once, so please continue to tune in

Toad Executing Scripts against Multiple Databases (Part 2)

Bert Scalzo — Wed, 06 Aug 2008 13:11:00 GMT

Last week’s blog was the first in a series about how to make Toad run scripts or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I’ll highlight the second method of running scripts against databases using the “Toad Project Manager”.

You simply switch to Project Manager’s Connection tab as shown below, “multi-select” the connections you want to execute something against, and then via the right-hand-mouse context menu choose either a “Quick Script” from the Script Manager, or a Named SQL. That’s all there is to it.

You’ll then see output from both databases as shown below (i.e. two script editor windows with the script executed against each database). For those not yet familiar with the Toad Script Manager, review last week’s blog. Also look at the second screen snapshot below of the Script Manager’s screen and Toad’s main menu toolbar icon. The items on the Script Manager screen checked as “On File Menu” are then shown both under the toolbar icon and Project Manager’s right-hand-context menu (both shown). So remember – in the weeks to come I’m going to demonstrate other ways to execute SQL scripts against multiple databases at once, so please tune in

Project Manager Screen – Connections Tab:

Script Manager Screen – Set Quick Script:

Toad Executing Scripts against Multiple Databases (Part 1)

Bert Scalzo — Tue, 29 Jul 2008 23:15:00 GMT

One of the more frequent questions people ask me is why can’t Toad run scripts or commands against multiple databases at once? Of course as usual the answer is Toad already can – so I’m going to dedicate the next couple weeks’ blogs to this topic.

This week I’ll highlight the first method of running scripts for multiple databases using the “Toad Script Manager”. It has been in Toad for a long time now – and it’s available off the Main Menu as pointed out below. You simply create a grouping of scripts (in this case I told Toad to point to my “C:\Bert\SQL” directory – where I keep all my SQL scripts from over 20 years of Oracle work. Then I simply indicate to the Script Manager that I want multiple connections active when I execute the selected item. That’s all there is to it. You’ll then see output from both databases as shown below. Remember – in the weeks to come I’m going to demonstrate other ways to execute SQL scripts against multiple databases at once, so please tune in