One of the questions I get quite a bit from customers is; do we have the ability to compare data and/or have the ability to compare DDL files to what’s currently in the DB2 catalog?  As of V3.0 of TOAD for DB2 the answer is YES to both of these questions. A new feature called “Difference Viewer” allows you to compare table data, results sets, and various script files either file to file, or file to catalog. This feature is...

Well, I decided that Linuxtown wasn’t going anywhere very fast and with the all the SRs I was leaving in my wake I soon would not be welcome by the locals so I decided I would take it on the lamb over to Windowsville for a while. Besides it was looking like the local disk array was going to pin a power supply failure on me. Let me tell you, they don’t have it much better. If you are expected to be surrounded by happy smiling...

A US-based fast food chain apparently has a new advertising campaign running. I’m not exactly sure what it is supposed to be for, and most of their commercials are pretty confusing. They do have one that I like. Apparently their food is not only JUST fast, but also made to order, and tastes good. What does this have to do with Toad®? Well, Toad allows you to compose...

Use templates to accelerate high quality coding. When you open a new edit window in Toad, it looks like this:



Well, of course it does....it's an empty, new edit widow!

That's fine, but suppose I now need to create a new package for my application. Sadly, what many of us do is start typing:



...

A common task for DBAs is to create a test or development environment that has a subset of production data for testing and development purposes. As usual, Toad® offers a feature for doing just that – the Data Subset Wizard, found under main menu Database-> Export->Data Subset Wizard.



There are two common scenarios where...

What's the point? This utility will make it easy for you to read in the contents of DDL statements (like CREATE OR REPLACE PACKAGE) and execute them within Oracle.

Show me the code! It's not a terribly long program, so I will include it right in this posting:

CREATE OR REPLACE PROCEDURE exec_ddl_from_file (

   dir_in IN VARCHAR2

 , file_in IN VARCHAR2

)

AUTHID CURRENT_USER

...

This week I am going to switch gears over to the big iron and talk about DB2 z/OS. IBM has been making great strides in the area of schema management. As any mainframe DBA knows, the process of having to make a seemingly simple schema change can result in a very complex combination of DDL, Utilities and batch processes. These changes can result in applications having to be unavailable for significant amounts of time. Starting...

I get this question a lot. I am teaching a class and a student asks: "When did Oracle add autonomous transactions?" Or the INDICES of clause. Or – who knows what? Seeing as I am totally obsessed with PL/SQL, I usually actually remember the answer to such questions.

Occasionally, however, I do not. And of course I cannot always be there for you to answer such a question when you have it.

So I thought I would pass along a tip – a way to quickly determine the version in which a feature was introduced to Oracle.

...

I have been working on SQL Tuning research for more than 10 years, but my focus has changed to time-series forecast technology in the last two years. Recently, Oracle’s AWR and other database vendor’s statistics/metrics repository have drawn my attention. The relatively low cost of keeping performance statistics in databases, combined with today’s powerful CPUs and cheap storage has resulted in more database vendors keeping...

Toad® has been a popular Oracle development and administration tool for the past decade.  In fact, the original trademark for our favorite little guy was ‘T.O.A.D’ – an acronym that stood for “the Tool for Oracle Application Developers.”

A couple of treats for our history buffs: 1. T.O.A.D. from 1998 2. Oracle Magazine story from 1998

         

...

Last week, Jeff Smith’s blog on the FTP utility in Toad® was very good. He even says at the end that “If you like Toad’s FTP, then I recommend you check out the REXEC and TELNET features as well.” That made me think – it probably would be nice just to see a list of all the UNIX and Linux capabilities within Toad in one article....

Well, the 32 bit 11g beta is officially over with the GA of the 11g 32 bit release on the Oracle download site. I replaced my beta copy with the production release and started playing about 2 weeks ago. I thought you all might like to know some of the “gotchas” I have run into so far.

Granted, some of these may be my fault and yes, I have filed SRs on them and Oracle has been responsive in troubleshooting them, however on the off chance they are bona fide bugs I would like to give you all a heads up. 

...

Starting with Oracle 9i, Oracle recommends that SQL developers use the ANSI join syntax instead of the Oracle proprietary (+) syntax. There are several reasons for this recommendation, including:

Easier to segregate and read (without mixing up join versus restriction code) Easier to construct join code correctly (especially in the case of “outer” joins) Portable syntax will work on all other ANSI compliant...

It’s no secret that I am a die hard Toad® fanatic. There are many reasons for this, but the one I want to talk about today is one of Toad’s best kept secrets: a commercial-grade FTP client built right into Toad!

  Older versions of Toad can find ‘FTP’ under the ‘File’ menu.

Yes, Toad DOES have its very own fully-functional...

As we all know, there are many factors that influence the performance of your application. Proper memory allocations, physical design, how the SQL is written and workload all affect the way your queries perform. One of most overlooked parts of testing an application is testing queries under production workload conditions.  A seemingly simple query runs fine in your test environment, but how will it perform when 10, 25 or 50...

I don't know about you, but I sometimes write code that (inadvertently, not on purpose) contains an infinite loop. So I run my program and Toad goes off into never-never land, with Oracle chewing up CPU cycles so intently that it is hard to connect as SYS and kill the session.

I hate that, don't you?

Now, there are two ways to address this problem:

1.       Don't write code that contains infinite loops....

As I wrote last week, Oracle 11g has a plethora of really great new features. One that should really hit a home run with data warehousing DBAs is “Invisible Indexes.” The name is not a joke – they are exactly what they say. An invisible index is ignored by the query optimizer when forming an explain plan for a SELECT statement, but they are fully maintained during DML such as INSERT, UPDATE, DELETE and MERGE.

Let’s...

For more than 10 years now, Toad has been THE tool for Oracle developers, DBAs, and analysts.  A few years ago, Quest began introducing versions of Toad that brought native support to popular platforms such as DB2, SQL Server, and MySQL.  Oracle enthusiasts were able to jump over to a new RDBMS technology without having to leave their favorite productivity tool behind.

While most features were implemented...

Kudos to Alex Nuijten for bringing this gem to light, he wrote in his latest AMIS blog entry that:

"If you are a regular user of the FORALL statement, you are probably also familiar with this message:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

While I was playing around on...

Oracle 11g has a plethora of great new features. One area that has improved with each of the last four releases has been partitioning, i.e. the method to break larger tables into smaller, more manageable and efficient sub-tables. While it was originally touted as a boon for just the data warehousing world, partitioning nonetheless works well in many situations. With Oracle continuing to improve upon them – partitioning is now a powerful asset in any DBA’s tool belt. So let’s examine some of the new table partitioning techniques introduced with Oracle 11g....

You will find in this Quseful a package that will generate/return collections of random values of strings, numbers and dates. It also contains a "self-test" random_verifier procedure that you can run to verify "at a glance" that the values being generated seem random. As a bonus, I include the pick_winners_randomly procedure, which I use in my seminars to pick the winners in raffles for my books and other goodies. I wrote...

Welcome to my inaugural blog for Toad® for DB2.  Many of you may not be aware that Toad is now a multiplatform solution. I plan to discuss subjects across both DB2 running on the LUW platforms as well as z/OS.  I will address day-to-day issues and show you how Toad can make your job easier whether you are developing new applications or administering legacy databases. Toad for DB2 has been available for about two years now. Quest...

Oracle 11g is out for Linux – and like every new release, there’s tons of cool new stuff. So over the next few weeks, I will write about some of Oracle 11g’s new features. This week, I’ll look at Virtual Columns and Virtual Indexes. 

When designing a relational database, analysts often look at prior systems’ screens and reports for insight. So the database analyst designing the business entity of a “product” might see...

Today database vendors are more willing to provide a means for the end user to influence the decision of which execution plan to use for a SQL statement. They provide this because database optimizers cannot guarantee that they the will generate the best execution plan for a given SQL statement. However, when you do apply Optimization Hints you might find that sometimes the specified Hints may not affect the database SQL optimizer’s...

In my last blog I attempted to cover all of the cool filters in Toad in a single posting.  How silly of me!  Let’s see if we can’t further down the road.

As a quick re-cap, we went over the first 3 filters available in the Schema Browser.

Schema Browser List Filters





...

We all know that “a picture is worth a thousand words,” so I provided a few pictures (shown below) to try and briefly yet succinctly explain what ASM is and how it’s different than the previous ways of managing disk space within Oracle.

Traditional Methods To begin, let’s review how DBAs have historically allocated disk space to Oracle – so that we have a baseline to compare against, and thus see how much simpler ASM makes the overall picture, which generally translates to easier setup and management. Figure 1 shows the traditional options.

...

I offer in this Quseful (Quick and Useful) a package that you can use to determine if a string contains a valid integer, number, binary_float or binary_double (note: if you are not running Oracle 10g, you will need to comment out the binary_* versions in this package). It is based on code I wrote about back in 1997 (available here, along with the article...

Ever hear people say they miss the good ole days – when times and things were much simpler than now? Well the same is true for Oracle DBAs, the early 90’s (circa 1993) were much simpler in terms of hardware selection for an Oracle server as summarized below.

CPU

Architecture

CPU

Family

...

Having the world’s information at our fingertips via databases is an extremely powerful resource.  It can also be quite daunting. Any Oracle user who has had to manage an APPS database knows this intuitively as those schemas consists of hundreds of thousands of objects.  Or you could have only a solitary table, but the table has...

I am participating in the Oracle11g beta. The next time I tell you I am going to do a beta please lock me up until the fit passes. I have subjected myself to this abuse since Oracle8, usually tied with writing or updating a book. Maybe I am getting smarter as I get older as I am not (currently) involved in a book project although there may be one or two more in the pipeline in the future.

To begin with you need to...

Be sure to read part one and part two to learn more about Optimization Hints.

Will Hints limit the SQL’s future optimization flexibility? Yes, it is a general concern of using Hints on your SQL statements; actually, it is a...

Your Mission: Go fix ALL of the slow SQL in your application ASAP.

SQL is an extremely powerful language for us database geeks. Without completely understanding, or even caring how the database is going to resolve my query, SQL allows me to ask for data by describing it.  For example, “Give me the employees by department, sorted by salary” equates to:

...

I use Toad® for Oracle almost everyday to tune and optimize problematic databases for customers, partners and friends. And I noticed that I followed a pattern – which seemed consistent across all such attempts. And although I wrote the whitepaper “Maximize Database Performance via Toad”, in hindsight it...

To read part one in this series on Optimization Hints you can visit my previous blog.



Use Hints for mission critical systems For mission critical system, you may not want to take the risk of changing the database's physical structure just to fix a small number of SQL statements’ performance problems.  The use of...

Some are no doubt asking why I would blog in an Oracle forum about maintaining infrastructure. Others are thinking “about time!” This dichotomy of experience in infrastructure points out the two types of DBAs (very generally speaking), first we have the hands on, maintains servers, helps build systems, load OS and maintain databases type and second, the only touches the DB type

In my work I have to be the first type...

One of the most widely recognized and often quoted database benchmarks is the TPC-C. For over 14 years, the TPC-C has been the industry standard OLTP test; however, it’s very clearly showing its advanced age. The TPC-C does not adequately mimic today’s real-world database workloads, nor does it properly stress the capabilities of today’s hardware and database engines. As such, the TPC-C is quickly loosing favor!

...

Should you use Optimization Hints? The use of Hints in your SQL statements will fix the execution plan so that Oracle Optimizer will not switch to a better (or a worse) execution plan when the database environment changes. Therefore, many people say, we should use Hints only as the last resort and use them carefully. This argument is certainly a safe declaration issued by most of the database vendors. They don’t want people...

I offer in this Quseful (Quick and Useful) a package that you can use to dynamically retrieve the value of almost any column from any table. I created this package as a "helper" utility for Quest® Code Tester users. Here's the problem that I was solving with this package:

We added support for automated testing of XML documents in Quest Code Tester 1.6, which will be released in a month or so (a very solid beta is...

What the heck is that?



This is what my friend and co-worker, Dennis, supports on a daily basis!  And yes, it is that confusing.



Dennis is an Applications Developer for our IS group here at Quest Software.  I asked Dennis if he’d spend some time talking about what he thinks about Toad®.

...

I’ve been working with Toad® for several years now, and even now that I’m NOT directly associated with the Toad development team, my Windows taskbar usually has at least 1 if not 2 Toad programs hanging around.  To be honest, if someone were to take Toad away from me today, my productivity would definitely suffer.

While most users are introduced to Toad as the market’s leading Integrated Development Environment (IDE)...

Probably the single most painful task in data modeling (or any modeling for that matter) is switching tools. Forget the high direct costs like purchasing licenses and the obvious indirect costs of retraining your staff, because it’s the migration of all your meta-data from one tool to the other that looms large on the horizon of pain. I’ve even witnessed people who will remain on an unsupported tool by a defunct vendor rather...

That's me. A hypocritical programmer. And I am here to admit it, to make a confession. Hypocrisy has got to be one of the most infuriating traits of human beings.

Definition:

"A pretense of having a virtuous character, moral or religious beliefs or principles, etc., that one does not really possess." http://dictionary.reference.com/browse/hypocrisy

...

Quest Recursive SQL Transformation technology is an innovative AI technology that simulates human SQL transformation technique. It incorporates a set of transformation rules to transform SQL statements on a section-by-section basis. This replaces the trial and error method used by human to rewrite the syntax of a SQL statement. Each transformation rule in the optimization engine is independent from one another, like a capsule;...

In Part 2 of this Blog, I want to introduce you to perhaps the most exciting feature of this release, the Toad Group Policy Manager – and it gives me particular pleasure to introduce this to you now as Toad for Oracle 9.1 is released. The Toad Group Policy Manager provides a facility by which multiple copies of Toad for Oracle within an organization can share the same set of options.  It consists of a Windows Service which...

Hints in Oracle have been around since version 8. Hints are like compiler directives that tell Oracle what path to take when optimizing a SQL statement (generally speaking.) However, Oracle will ignore the hint if it can’t do it or it is formatted poorly.

Most tuning products for SQL will make use of hints if statement re-arrangement doesn’t solve the problem. In later versions of Oracle, outlines became available....

Qusefuls #1 and #2 were published on Steven Feuerstein's personal blog at feuerthoughts.blogspot.com.

About Qusefuls A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I originally published Qusefuls on my blog (feuerthoughts.blogspot.com), but am now switching the "home" for Qusefuls to my ToadWorld blog. Each Quseful contains...

I’ve been searching for a definitive answer to the question: “What is the cost of poor database design?” No doubt you have all seen the cost/benefit graphs for fixing application problems and the pyramid showing how up to 90 percent of performance issues in a running database are SQL and index related, but what about the cost of building an application on a poorly designed database that forces poor SQL usage?

No doubt...

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

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

It is Wednesday, May 09, 2007 and I am sitting in the Admiral's Club at the Buenos Aires airport. I just finished two days in this lovely city. My first day was spent enjoying the chilly, but very sunny afternoon, walking for four hours around the city. It is a busy, busy place with many buses throwing way too much gritty exhaust into the air (hey, but they are Mercedes Benz buses! I think that Americans would be generally...

With the use of RAC on the rise it is time to talk about the use of a feature very under-utilized in Oracle. This feature I refer to is cross-instance parallel query. Many times on site visits I see people using single-instance parallel query, but no one whose system I reviewed has used cross-instance parallel. Some weren’t aware it was available while others assumed Oracle did it automatically.

In order to enable...