If there's one thing we hear people asking about it's "How can I use Toad for Data Analysts to automatically update my Excel report?" This blog entry is dedicated to just that. I'll show you how you can get your Excel reports to update from the database in just a few steps - and automate the process for next time!

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. This blog discusses eliminating the effect of network traffic when you are comparing SQL run times.

I am often asked about the naming conventions and coding standards that I use. My answer is usually a combination of muttering and vague statements and hand-waving.

 

That's because I have a confession: I don't have a single naming conventions and coding standards document that I use. Why not? Because I am a software developer! That is, I feel (well, I am) very busy, overwhelmed by deadlines. I don't feel like...

In this series of blog topics, I will attempt to translate certain features in Toad for Data Analysts for those of you who are more familiar with Toad for Oracle. As you may (or may not) know, Quest Software allows licensed owners of Toad for Oracle 9.7 to use Toad for Data Analysts free of charge! Toad for Data Analysts is Quest Software’s cross-database platform Query and Reporting tool. For Developers and DBAs who work predominately in Oracle, but have the need to query and/or report against other database platforms (SQL Server, DB2, and Sybase, just to name a few), then the pairing of Toad for Oracle and Toad for Data Analysts is for you. 

...

In humble tribute to the great southern comedian, Jeff Foxworthy, I thought I’d try a send up on this popular theme as it pertains to Toad. So here goes. If you have your own suggestions, just leave them as comments at the end of the blog!

The ‘ribbet’ sound announces to your cube-mates what time you’ve made it into work. Did you know you can disable the ‘ribbet’ and customize Toad to play any .WAV file you want?...

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.

...

Written by Finn Ellebaek Nielsen. Reasons for testing Oracle code in the database.

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. This blog discusses minimizing the effect of other activities on the CPU when you are testing very fast running SQL statements.

I recently performed 3 separate Toad for Oracle web demonstrations for a large company in the payroll and human resources industry.  Rather than a general overview of all Toad features, these 3 demos were customized for 3 different types of Toad users: DBA's, Analysts, and Developers.

This group of DBA's had used Toad for years, but had not upgraded recently.  As I have mentioned in previous posts, there has been a substantial amount of functionality for DBA's added to Toad in recent releases - just download the latest version.  Johannes' Toadworld blog has an excellent 3 part series on the Toad DBA Suite.

...

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

When you ask Oracle for an execution plan with Toad, we insert the plan into a plan table (configurable by the user.) Wow, that’s 36 pieces of information that could be included in every single plan step. Now, when Toad shows you a plan for a query, it chooses to show only the most common pieces of information.

Written by Steven Feuerstein. Introduction to the "Real Automated Code Testing for Oracle" blog.

While we are still quite a few months away from officially releasing the next version of Toad for Oracle, there are a few exciting developments to share with you today. Of course, you can follow the development of the next version of Toad yourself via the beta program, but we realize not everyone has the bandwidth to do so. Most years will see at least 2 releases of Toad. However,...

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

Like most applications, Preferences or Options enable the user to make changes to the default configuration to suit the way they work. The same is true for Toad but there are so many, it can be a bit of a challenge finding what you are looking for.

So the objective of this blog is to help you get a handle on what the main options are that you probably need to know about.

Some Toad Options are set during the Toad...

One question that has been coming up lately is how I can randomize my SQL so that not every user is submitting the same statements.  This is very important in creating a realistic workload to correctly perform a database performance test.  Without this step the database will either pull data from cache or there will be a larger than normal amount of constraints/conflicts, such as updating the same row in a table.

Fortunately,...

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. These factors include caching the data, caching the indexes, caching the SQL statement, other activities on the CPU, and network traffic.

This blog is the second in a series about common misconceptions surrounding SQL tuning. It covers the misconception that the goal of SQL tuning is to write a better SQL statement. The real goal of SQL tuning is not to create a better SQL statement, but it is to help the database optimizer to make the right decision when it is choosing the execution plan.

If you browse the internet with the key words “SQL Tuning”, you...

Every month, a Toad World newsletter goes out to thousands of Toad users and it includes a monthly puzzle that I write. Last month's puzzle went like this:

Which of the following blocks does not contain an infinite loop? A. DECLARE    l_line VARCHAR2(32767);    l_file UTL_FILE.file_type :=       UTL_FILE.fopen ('C:\temp', 'my_file.txt', 'R'); BEGIN    LOOP       UTL_FILE.get_line (l_file, l_line);    END LOOP;...

Written by Rene Woody This blog is the first of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers the options for terminating the SQL alternatives when they are executed in a batch.

 

The SQL optimization process in the Tuning Lab generates multiple alternative SQL statements that...

It's truly one of the oddities of the PL/SQL language that it does not offer a delimited string parsing program. The closest we can get is DBMS_UTILITY.COMMA_TO_TABLE, and that is sadly deficient (it only parses comma-delimited strings and each item between the commas must be a valid PL/SQL identifier).

 

So I built one myself (the parse package) and put it in the demo.zip file...

Written by Rene Woody   This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle.  It explains how to generate SQL alternatives that do not have Oracle optimization hints.

Oracle provides optimization hints that can be added to the syntax of a SQL statements to attempt to influence the execution plan that the database optimizer will use to execute the SQL statement. Quest SQL Optimizer for Oracle uses this technique in its optimization process to generate more unique execution plans for your original SQL statement. For more information on how the SQL alternatives are generated, see this previous blog: Optimizing SQL Part 1 – The Optimization Process.

...

In this week’s blog, I’d like to introduce you to Toad’s Formatter. For those older “Toadies”, you’ll be familiar with its predecessor Formatter Plus.

Recently there have been a lot of questions on some of the features of Toad Data Modeler which are part of the basic components of the tool but for whatever reason people seem to have a bit of trouble figuring them out.

In the past I have published some papers on a couple of these features on Toad World and I will provide links to them at the bottom of this blog.

 

One of the best and easiest ways to find...

This was a question I got this morning from a customer. I showed them how to do this with the ‘Alter’ dialog in Toad’s Schema Browser. However, they wanted to do it for more than one table at a time, and apparently some OTHER tool could do so. Why couldn’t Toad?

Great question. It turns out that IT CAN INDEED do so. Here’s how:



Step 0: Open the ‘Rebuild Multiple Objects’ window



...

If you’re like many of the analysts I speak with on a regular basis, then you use Microsoft Excel. It’s a staple of the Information Age, right!? It seems like you can do everything but wash your car with Excel. But what if you want to get that data out of your database and into a series of worksheets… on a regular basis…? Unless you have lots of free time, this is probably something that you don’t look forward to. Let’s take a look at how Toad for Data Analysts can make your Excel-life even easier!

Developers are hard people to satisfy. Oracle gives us this incredibly robust, powerful and easy to use database programming language – and all we can do is complain about what it doesn't do for us.

Well, that's reality for you: PL/SQL is powerful and robust and easy to use, but also very narrowly focused. So if you want it to do something outside of its area of expertise, sometimes you have to jump through a few more hoops than you'd like.

...

I had a very productive visit with an information management company, demonstrating the complete range of Toad solutions, including the Toad Development and DBA Suite for Oracle, Toad for SQL Server, as well as Toad for Data Analysts. The presentation took place in Boston, and dialed in through a webcast were attendees in Massachusetts, Pennsylvania, Belfast, and Bangalore. This visit was scheduled well in advance, and we...

I think we can all agree that of ALL the features in Toad that impact the most number of users is the data grid. Whether investigating the contents of a table, running queries, or monitoring connected sessions in the Session Browser, the data grids deliver information to the user. How well you know the data grid and all of its embedded features will impact how useful and productive you are for your employer.

We often...

I am an SC for Quest Software, and very fortunate to be able to demonstrate the solutions that make up the Toad product family. My initial goals for this blog will be to share some of the interesting conversations and discussions that arise from Toad demonstrations. Rather than screen shots of a certain feature, I will try and relate Toad functionality to real life scenarios and examples.

To start and end my demonstrations,...

While co-presenting with Bert at the Rocky Mountain Oracle User group, Bert yells out “Hey, show that cool FK lookup thingie in the data grids.” I was like, ‘huh?’ Surely Bert just didn’t call me out in front of 100 or so people on a Toad feature that I did not fully understand?

Well, I got around to figuring out just what Bert was talking about, and NOW I remember what all the fuss was about. This feature was added...

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

By Johannes Ahrends and John Pocknell

In the previous blog, we compared and contrasted how database Performance Management operations are performed in Oracle’s Enterprise Manager (OEM) and Quest’s Toad DBA Suite for Oracle and how Toad is an excellent complement to OEM because it simplifies and automates many tasks which would...

This blog is the first in a series about misconceptions surrounding SQL tuning that are quite common. The first one covers the misconception that you can use the estimated cost from the database SQL optimizer to accurately judge the performance of a SQL statement in comparison to its rewrites.

 

SQL tuning is a very interesting topic and most DBA or developers have at least some experience with tuning SQL. They...

Welcome to my first blog! I am very excited about this opportunity to relay information about benchmarking/performance testing and, in particular, Benchmark Factory for Databases (BMF), but where to begin? For my first blog, I’ll start on the topic of latency.

One key thing to understand when doing testing is the characteristics of the workload, and a big piece of the workload is how fast the transactions are submitted...

In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. In part 2, I checked out PL/SQL Developer's refactoring features. Now, it's time for Toad.

 

Differently from both PL/SQL Developer and SQL Developer, Toad does not have...

By Johannes Ahrends and John Pocknell

In the previous blog, we compared and contrasted how Database Maintenance operations are performed in Oracle’s Enterprise Manager (OEM) and Quest’s Toad DBA Suite for Oracle and how Toad is an excellent complement to OEM because it simplifies and automates many tasks which would take longer in OEM.

...

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 3rd, through Thursday, May 7th, at the Orlando convention center. I’m taking my family along and fully intend...

I recently got an email from a user, and after I replied figured that it might be helpful for others to see as well.

 

The Question:

“I was at the ROUMG conference and have a question.   You guys showed how to setup a short cut in the options.  Like to do a select statement – you can type in a short cut word and it will do the put the script in.  Like autofinish.

 

I know I need to build...

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

Written by Rene Woody

This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle.  It sheds some insight on why the SQL optimization process can take hours to run and what you can do to shorten the process.



SQL Statement...

In a previous blog http://www.toadworld.com/Community/Blogs/tabid/67/EntryID/324/Default.aspx I discussed a real life example of “How to use Quest SQL Optimizer to tune complex SQL”. I spent two days installing the database and another two days tuning a complicated SQL statement, which originally the SQL statement executed in 8 hours...

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.

...

By Johannes Ahrends and John Pocknell This 3-part blog will help you differentiate between what Oracle Enterprise Manager (OEM) offers the DBA compared to Toad DBA Suite for Oracle and how the same tasks are approached using each.

 

Toad DBA Suite for Oracle provides a complete solution for DBAs using Oracle Standard Edition, or in smaller shops where OEM is not being used or as a complementary solution for...

Written by Rene Woody

This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It explains the order in which the SQL statements are displayed.

After the optimization process is finished, the SQL alternatives are displayed in the order of their Oracle cost from the smallest cost to the largest. The Oracle cost is found in the execution plan and is generated by Oracle. The Oracle cost provides an estimate of the system resources that will be used by this execution plan to process the SQL statement. The theory is that the SQL statement with the lowest cost should be the best alternative. When actually testing the performance of alternative SQL statements in your database environment, you will find that frequently the SQL statements with the lowest cost are not the best performing SQL, since if Oracle cost estimation was correct for this SQL, you probably would not need to tune this SQL, so the best practice is to execute all the SQL alternatives to find the best one and not just assume that the SQL statement with the lowest estimate is the best.

...

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

Written by Rene Woody This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers the importance of finding the best “driving path” for retrieving rows from the database.

 

We’ll use a simple illustration of a Nested Loop...

US Airways flight 1549 teaches us that improbable events actually do occur sometimes. When the NTSB goes to investigate, the airplane’s black boxes will prove vital in the determination of cause. We can leverage this concept to fortify our chances of surviving a serious investigation. In this article I introduce design considerations for what I call the Black Box Data Store, the important data you need to prove your innocence in an investigation.

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