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

Most database vendors provide optimization hints which enable a user to influence the decision the database SQL optimizer will make when determining which execution plan it will choose. Oracle provides a full set of optimization hints to help users to rectify an individual SQL performance problem, thereby making it the most open of all the database platforms. This approach admits that the database's internal SQL optimizer cannot guarantee every SQL will perform well and therefore it sometimes necessary for the user to intervene for some SQL statements when the database SQL optimizer has failed to generate a good execution plan for them. ...

In this short blog, I want to cover a topic which quite a few customers have asked me about over the years, and that is where they need to debug a Package where the Package Specification and Package Body is stored on their file system as a combined file. Toad’s Editor is designed to make developing Packages, Types, Procedures and Functions easy. In Oracle, the Spec and Body of a Package or Type are two separate objects, although...

I am visiting this, well, very artificial paradise dropped into the middle of a desert, so as to participate in Collaborate 07. It's been an interesting and fun several days. I have a room on the 26th floor of the Mandalay Bay hotel, so I have a fantastic view looking out over the wave pool, lazy river and assorted pools between the hotel and the convention center. Better yet, I can see mountains right there on the horizon....

You’ll have to forgive me for spoofing the famous movie line “We don’t need no stinking badges” (Treasure of the Sierra Madre, 1948 and Blazing Saddles, 1974), it just seemed quite apropos J



I was discussing data modeling needs recently with a potential customer – and like many shops I visit, they saw no need for data modeling. They believed that their DBAs, data administrators and application architects knew...

In my last blog entry we looked at sizing tables in 10g, of course the other side of that coin is the sizing of indexes. As with tables the Oracle8 manuals had an explanation of how to size indexes, but manuals since then have been strangely silent on the subject. In this blog I will address the sizing of normal, b-tree indexes since they are still the in the majority as far as type of index used in most applications.

...

In Computability theory there is a famous decision problem called halting problem which can be informally stated as follows:



Given a description of a program and its initial input, this determines whether the program, when executing the input, will ever halt (complete). The alternative is that it runs forever without halting (stopping).



In 1936, Alan Turing proved that a general...

I rediscovered the joy of writing to myself a week ago, and I also was reminded of the danger of coding in isolation (that is, working by oneself).

I recently tangled with my implementation of column set management in Quest Code Tester.

"Column set management," you wonder, "what's that?"

In this amazing, automated code testing tool for PL/SQL, you can at a high level describe the tests you want Quest...

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

So why would I want to Profile my Code?

Well, as developers write their code, they’re principally focussed on ensuring the code enables the required application functionality and does so consistently for all test cases....

In this installment I thought I'd write about the exciting, and soon to debut, Toad Data Modeler 3.0 release rather than yet another boring academic or technical topic on data modeling. TDM 3.0 is quite exciting. While it has not yet been made available for public beta – it will be very soon (so please email the product manager John.Pocknell@Quest.com to get signed up). And once...

On one of my first Oracle consulting assignments I had to come up with the sizing estimates for tables for a data warehouse for a telecommunications company in California. At that time, (1994) there were few products that provided sizing algorithms for Oracle tables and indexes. In fact, all that was available were the somewhat cryptic sizing formula provided by Oracle (discontinued from the manuals in version 9). Not being...

Some musings about the life of a programmer.... Two weeks ago, we held the second annual Oracle PL/SQL Programming conference (I talked about it in last week's blog on the Test-A-Thon). I gave the keynote speech on the first day. It was titled "Wake up and smell the methodologies". Generally, I urged PL/SQL programmers to take a look at the various methodologies out there, such as Extreme Programming, Structured Programming...

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

·          Flexible Code Control Groups (CCGs), which...

A couple months ago as ODTUG was working out the final details for the Oracle PL/SQL Programming 2007 conference I came up with an idea for a Test-A-Thon: Contestants are handed a set of programs, and test cases for each of the programs. They must then (in the next hour) build test scripts for those programs that automatically verify whether or not the program works. Whoever gets furthest in building the test scripts...

I have been asked many times that why there are some dummy SQL transformations/rewrites that look meaningless, but it works in certain situations.  Let me give you some examples that may help to explain what theory on behind of Quest SQL Optimizer.

The following rules are implemented in the Quest SQL Optimizer for different platforms to deal with individual database SQL optimizer’s behavior. Some rules may look puzzling....