I recently gave a webcast about using indexes to boost your application performance, of course at the end of the webcast we took questions, unfortunately I wasn’t able to anser all of the questions so I pulled the ones I felt were most important and decided to answer them here in my blog. For the full webcast see : index webcast

...

How often are you offered less work or fewer responsibilities? Rarely does that ever happen, and if it does, it could be a bad sign! Well, as a database administrator, your job gets more and more complex every day even as RDBMS vendors keep adding ‘automated management’ features. On top of all your day-to-day tasks and preventive maintenance you perform, your most pressing tasks are playing firefighter with the proverbial fires in the database.

...

Last month, Jeff Smith wrote an excellent blog about Toad’s new Stats Pack capabilities.  And as Jeff pointed out, Stats Pack is an older – but free technology. While Oracle’s AWR and ADDM reports are clearly the next generation of performance diagnostics tools – and arguably preferable, you must purchase the OEM Tuning and Diagnostics packs to utilize these facilities. So even though DBA’s can access AWR and ADDM reports either through PL/SQL calls or the Toad DBA GUI – which simplifies access further, many people who use AWR and ADDM may not be properly licensed. Thus Stats Pack may well remain relevant for some time into the future due to its cost.

...

Well, here I sit at 30,000 feet over the Atlantic heading for London and then on to Abu Dhabi. It seems like just yesterday I was doing a webcast for the Performance Allstars series on Instant Replay and the use of history to find problems, oh wait, that was yesterday!

 

During the webcast many excellent questions where asked concerning historical data capture and usage so I thought I would answer some of the...

One might be tempted to think that with such a young tool like Toad for Data Analysis (TDA), that it would be a bit early to start trumpeting its advanced features and ‘hidden’ productivity boosters. In this case, one would be very wrong! Although Toad for Data Analysis is only up to release 1.1, it is built on top of Toad for SQL Server and Toad for DB2’s award winning platform. The development team has also included a couple...

You will find below an excerpt from my latest publication: the 2nd edition of Oracle PL/SQL Best Practices.  This edition is a complete rewrite of the 1st edition. I decided that since software is still, for the most part, written by humans, I would create a cast of characters who write the software I reference and critique in this book.

 

They...

There are many different types of Toad® users, and since job titles and descriptions vary so widely, it’s tough to say a typical Toad user looks like this and thus needs this. Thus Toad has evolved over time to meet the ever growing needs of our customers. Quest has two key Toad Yahoo discussion groups to facilitate the evolution and revolution of Toad The first group, groups.yahoo.com/group/toad, provides a useful and...

I usually write about best practice principles and general issues.

Today I thought I would share a bit of my pain with you on a very specific topic: single quotes and CLOBs, and specifically on Oracle 10.2.0.3.

I figure that I lost an hour or two of my life to this one, and would like to pass on the warning to you all.

In Quest Code Tester, we store in put and expected values that you provide in your test...

One of the nicer features within Toad® for DB2 is the wide array of reporting capabilities. There is a constant requirement for managerial level reports that need to be run on a regular basis. Many of these reports are generating using reporting tools like QMF, Crystal Reporting, etc. Toad’s data reporting facility provides an easy method for generating professional report templates which can be saved and reused. I will take...

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

I’ve been promising to deliver a write-up on Toad’s new support for Statspack, and I’ve finally gotten around to doing it!  Statspack has been around since the 8i release of Oracle.  If you are unfamiliar with the technology, please read this.  If you’re already a Statspack user and would like to see how Toad can help you take advantage of...

Winter is descending on Chicago; we had our first snow (flurries, really) on Thanksgiving Day, and the sun is weak. For many, this is a depressing time – and I mean that the lack of that and cold actually does make people feel depressed. Perhaps that is why I found myself thinking negatively – that is, about exceptions in PL/SQL. So I thought I would share with you some of the features and functions available in PL/SQL, especially...

When we create a super-and-sub type (a.k.a. Inheritance) entity relationship in a logical data model, it’s very clear what the business requirements being communicated are – that different subsets of attributes comprise the “cumulative entity” being modeled depending upon the business context.

So the Toad Data Modeler 3.0 logical diagram shown below relates that Entity1 always has the parent attributes of E1_Attribute1-4,...

I was initially planning on writing up a review of the new Statspack support in Toad version 9.5 for my next blog, but I wanted to make sure all of our Toad users were aware of the new installation process first!



There are a few things you need to know before you go to upgrade your copy of Toad.  Let’s go over them very quickly so you can start using the latest and greatest copy of your favorite software...

Would you like to increase productivity, improve code quality, and ensure that standards are being followed? Of course you would! And one of the best ways to do that is to rely heavily on Toad's Code Templates (similar functionality exists in SQL Navigator as well).

Code Templates are chunks of code that you can assign a name and description. You then use the name to quickly copy the associated code into your editor....

In many cases there is a need to create an object or to execute an SQL statement in multiple locations. The group execute option in TOAD for DB2 allows you to execute a script against multiple environments with one easy step.  Let’s step through a few use cases.

Example 1:  A new table needs to be added to multiple databases.  The first step is to generate the necessary DDL and send it to the editor.



...

I have worn many different hats here at Quest, and recently put on another in the Sales organization. Regardless of what my official title has been, talking to our Toad customers has always been one of the most rewarding aspects of my career. I decided a long time ago that I would do all I could to master Toad and share my findings with others. I hope to never stop learning or sharing!   One of the more intense experiences...

This blog posting is not meant to demean or slander the Oracle database in anyway. I’ve staked almost my entire career on Oracle technologies – and I intend to finish these last 7-10 years doing more Oracle stuff. To quote a funny Saturday Night Live skit: “Oracle has been very, very good to me.” So my comments are more about how “uninformed” installation of the latest Oracle release on a notebook could lead to negative surprises. 

...

Sorry about the late posting, but if you are interested in attending my presentations, here's the info: Session ID: S290723 Session Title: Break Your Addiction to SQL! Track: Database Room: Grand Ballroom A Date: 2007-11-12 Start Time: 12:30

Session ID: S290725 Session Title: Collect Yourself: Optimize PL/SQL Code with Collections Track: Database Room: Yosemite Room B Date: 2007-11-12 Start Time:...

Probably one of the most useful and frequented tasks performed with Toad® for Oracle is saving data to a file, such as comma delimited text (or any of the other numerous formats supported). It’s a relatively easy feature to find – you simply depress the “right hand mouse” (RHM) while anywhere within the displayed data grid and choose the “save as” option as shown below, but what if you could make that save operation run 10X...

Oracle’s Parallel Query Option (PQO) is a fantastic tool, but much like any good tool, it can very easily be used in the wrong situation or simply abused. In those cases, PQO can actually make database performance much worse. Let’s examine some common misuses or misperceptions regarding optimal and efficient PQO usage.

1.      PQO make sense universally on any SMP or multi-processer database server 

Not really....

I just got back from the IBM IOD conference in Las Vegas; it was a great show. Every year there is usually some buzz about what is “hot” with DB2. This year’s buzz was heard loud and clear – XML was being discussed just about everywhere. This is definitely the driving focus for the latest release of DB2 running on both LUW and z/OS. Toad® for DB2 3.0 has many XML features already enabled. You can create tables with XML data...

If you frequent Toad® World, then you are probably aware of the fact that Quest has a technology that offers to automatically tune your poorly performing SQL statements.  When I speak to customer or prospective clients, it is generally received extremely well.  Why is that?  Of ALL the problems that database professionals have in common, the biggest pain...

OK – now that I grabbed your full attention with that snappy title, let’s see just how to run the Toad® on your Linux desktop or notebook. Let’s say that you’re running Redhat, CentOS, SuSE or Ubuntu Linux on your PC, let’s further assume that you would like to run Toad either commercial or freeware on that PC against your local or remote Oracle database. But shoot, Toad is a Windows only program – so what’s a body to do?

...

All files referenced in document available from: www.oracleplsqlprogramming.com/downloads/demo.zip.

Introduction

Very, very few of us write perfect programs the first time, or the second time, or…. You get the idea. Our code is never perfect and can always be improved. Martin Fowler developed a technique he calls "refactoring," and it has become quite popular in the world of Java. Here is Mr. Fowler's description of refactoring:

...

They say time flies when you’re having fun, and it seems like 2007 has gone by at a record pace. 2007 has seen quite a few exciting developments in the ‘Toad World’ arena. In addition to Toad World itself, we have seen a new unit testing tool for PL/SQL and a version of Toad made just for analysts released. In addition we’ve seen the industry leading Toad for Oracle, Toad for SQL Server, and Toad for DB2 products enhanced with new versions and new capabilities in the last few months. ...

Hey DB2’ers,

I had a busy week last week speaking at the Central Canadian DB2 Users Group in Toronto and visiting with customers. The best part of this job is that I have the opportunity to speak to many people in the DB2 community and get to hear what they are doing with DB2 and what their needs are from a tool perspective.

It’s interesting to hear that although companies are using DB2 for a variety of applications...

1. I’m using a tool like Quest’s Benchmark Factory®, so that’s all I need.



Wrong. I highly recommend that anyone doing benchmarking read the specs for whatever industry standard tests they are going to perform. Because software to automate these tests will ask questions or present options that you cannot really define unless you understand their context – which is defined in the spec.



For example,...

Problem Resolution

For those of you who read my previous blog from last week, or read the recent White Paper entitled “Simplifying Oracle Database Administration” written by Mike Ault, Bert Scalzo and myself, you’ll have seen from the survey carried out by James F Koopmann that about 20% of a typical DBAs...

There are at least two database vendors are trying to build self-learning SQL optimizers. The idea is to use actual statistics from executed SQL statements to rectify the future cost estimation of the same or similar SQL statements. It seems like a good idea, but, you will find that their existing self-learning SQL optimizer is either turned off by default or built as an individual tuning advisor. Of course, we cannot say that they will not provide a better and fully automatic solution in future. But the fact is that this technology is not mature enough to be turned on automatically today. Furthermore, database SQL optimizers have a lot of problems pending that still need to be solved. They should not just focus on the error of cost estimation without taking care of the small plan space problem.  

...

Every software product has a feature or widget that is extremely powerful, but finds a way to confuse more people than it enlightens. That is not to say that the feature is poorly designed or implemented, but who can really say they understand and use 100% of an item’s potential?



A few of my personal...

Often people will write or call to relate that Toad® seems sluggish when working against Oracle 10g databases – and that creating new connections in particular seems to take an unreasonably long time. They are often especially frustrated since this problem did not occur with Oracle versions prior to 10g. So what is Toad doing that’s wrong (i.e. a bug)?

The short answer is that this is an Oracle 10g DBA best practices...

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

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



...

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