I am working on some new transformation rules for Quest SQL Optimizer to maximize SQL performance for Oracle Exadata.  It is interesting to investigate how subtle changes to SQL syntax can enable more offload operations to the storage server with corresponding improvements in execution time.

The following is one type of transformation that you might find useful.



SQL transformation to enable deeper...

Since version 8.7, our Quest SQL Optimizer has been enhanced to support Exadata. These enhancements focus on displaying smart scan and bloom filtering on both the original query plan and alternative plans.

I have been researching new rules exclusively for Exadata and have found that there are not many discussions on SQL tuning on an Exadata machine. We also have not spent a lot of effort on SQL transformation. With...

We are excited to announce the upcoming release of Toad for Oracle version 11.6 which will be released tomorrow, Tuesday October 2nd, 2012. This blog serves as a early look at the updates and enhancements to Toad for Oracle v11.6 including its corresponding bundled components. Toad for Oracle is the most widely used database management solution on the market today with over 2 million users. Toad brings operational efficiency...

Quest SQL Optimizer for Oracle automates the SQL optimization process and maximizes the performance of your SQL statements. SQL Optimizer analyzes, rewrites, and evaluates SQL statements located within database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA) or Quest Software Foglight Performance Analysis for Oracle. Once SQL Optimizer identifies problematic SQL statements, it optimizes...

Introduction

Quest SQL Optimizer for Oracle automates the SQL optimization process and maximizes the performance of your SQL statements. SQL Optimizer analyzes, rewrites, and evaluates SQL statements located within database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA) or Quest Software Foglight Performance Analysis for Oracle. Once SQL Optimizer identifies problematic SQL...

原文：“EXECUTING SQL IN TUNING LAB – PART 10 – PERFORMANCE IMPROVEMENT EXPECTATIONS”  原文投稿日期：2009年8月21日

原文：“EXECUTING SQL IN TUNING LAB – PART 9 – WHY RUN TIMES VARY”  原文投稿日期：2009年8月7日

原文：“EXECUTING SQL IN TUNING LAB – PART 8 – CRITERIA FOR BEST SQL”  原文投稿日期：2009年7月24日

原文：“EXECUTING SQL IN TUNING LAB – PART 7 – TESTING SQL WITH VARIABLES”  原文投稿日期：2009年7月10日

原文：“EXECUTING SQL IN TUNING LAB – PART 6 – TESTING IN A DEVELOPMENT ENVIRONMENT”  原文投稿日期：2009年6月26日

原文：“EXECUTING SQL IN TUNING LAB – PART 5 – SELECTING ALTERNATIVES TO TEST”   原文投稿日期：2009年6月12日

原文：“EXECUTING SQL IN TUNING LAB – PART 4 – EQUAL COMPARISON FOR RUN TIMES: ELIMINATING THE EFFECT OF NETWORK TRAFFIC”  原文投稿日期：2009年5月29日

原文：“EXECUTING SQL IN TUNING LAB – PART 3 – EQUAL COMPARISON FOR RUN TIMES: MINIMIZING THE EFFECT OF OTHER ACTIVITIES ON THE CPU”  原文设稿日期：2009年5月15日

原文：“EXECUTING SQL IN TUNING LAB – PART 2 – EQUAL COMPARISON FOR RUN TIMES: MINIMIZING THE EFFECT OF CACHING” 原文投稿日期：2009年5月1日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“EXECUTING SQL IN TUNING LAB – PART 1 – CRITERIA FOR TERMINATING SQL ”    原文投稿日期：2009年4月17日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To） 

I did not use to pay much attention to the update table in an UPDATE statement. I figured that there was not a lot I could do with an update table unless there were complex search conditions. I preferred to focus on the sub-queries that appear on the update table's filtering and the update SET command.  

Today, I want to introduce a new transformation rule that is not implemented in our engine yet, but it is quite...

Richard discusses why SQL tuning by SQL Plan Baselines is a safe and effective method to improve your SQL performance.

What is a time-slot based SQL workload?

A SQL workload can come from different sources.  For example, an executing program, a database SQL repository, or a monitoring tool that captures SQL from the database, memory, network or client programs. The way a representative SQL workload is formed will ultimately affect the result of a workload based index advisor or an impact analyzer.  If you are using a monitoring tool...

 

What is a workload based index advisor?

A workload based index advisor uses a specified SQL workload to recommend an optimal indexing configuration. The recommended indexes should be optimal for a specific workload that you are encountering every day. I believe most people know the meaning of workload based index optimization, but few of us really use it as a practice since it is too difficult for a human to...

I have been performing research in the area of index optimization for more than 10 years and I'm pleased to announce that a new workload based index advisor is now available in Quest SQL Optimizer 8.5 for Oracle. The first index advisor algorithm I developed is still available in our SQL Rewrite module.  This advisor was designed to work on a single SQL statement or a small set of SQL and does not consider workload.  It embeds a large amount of human knowledge/rules to narrow down the index candidates in order speed up the search process. Rule/knowledge based index advisors can work well for small numbers of SQL where the index candidate space is relatively small but become ineffective when used on large SQL populations incorporating composite indexes and where index recommendations are based on actual application workload.

I have a dream to build the world's best index advisor that solves for any number of SQL statements, from one to thousands, and auto balances its recommendation based on actual workload. My research in this area was carried out almost 10 years ago and the research paper completed around 6 years ago but due to some reasons we have been unable to implement it - until now. I have not been so excited since I created my first...

This blog is the ninth in a series about misconceptions surrounding SQL tuning that are quite common.

 

What are Hints?

Most database vendors provide a plan hints feature for users to influence their database SQL optimizer to generate a specific plan for a given SQL statement. Basically, every database vendor provides their own hints feature to correct their database SQL optimizer decisions in special...

For Part I, Part II & Part III please visit:

How to Use Quest SQL Optimizer to Tune Data Warehouse SQL – Part I

How to Use Quest SQL Optimizer to Tune Data Warehouse SQL – Part II

...

For Part I, please visit:  How to Use Quest SQL Optimizer to Tune Data Warehouse SQL – Part I

 

A Data Warehouse (DW) is a database system used in an organization to collect data from one or more data sources and is typically used to generate reports or...

A Data Warehouse (DW) is a database system used in an organization to collect data from one or more data sources and is typically used to generate reports or run ad-hoc queries that aggregate data for business analysis purposes. A DW generally consists of an ETL (Extract, Transform, and Load) tool, a database, a reporting tool and other facilitating tools such as a data modeling tool. The nature of a DW is to store large...

In SQL Optimizer for Oracle 8.0, we have selected two simpler rules from our rules library and incorporated them. For a discussion of the first rule, please visit Toad World Blogs - Two New Transformation Rules in SQL Optimizer Version 8 – Part I for details. I...

 

I have been asked many times how to use one source SQL statement and tune it for different sized databases. The problem is that when you have only one program source and you want to deploy it to various sized databases in different companies, it is quite common that the SQL performs well in company A but performs poorly in company B. If you tune the SQL for company B, the new SQL syntax and plan may not be good for...

I haven’t written anything in my blog in the last two months since I’ve been busy developing and testing the new version 8 of Quest SQL Optimizer for Oracle. It has been a long time since I’ve been as deeply involved in the development as I’ve been in last few months. In Chinese, “8” is a lucky number - it means prosperous and good luck.  So we have put a lot of effort to make it happen and hope this new version will bring you prosperity and solve more SQL performance problems than ever before.

I was recently demonstrating our SQL Optimizer for DB2 LUW solution when a question came up regarding comparing Access Plans in the Index Expert portion of the Optimizer.  Excellent question indeed!  However, before I go any further, perhaps a little background on the Index Expert is warranted.

The Index Expert allows you to simulate indexes for a SQL statement.  We use our technology to generate virtual indexes (i.e....

Republished from John Pocknell's Blog

I thought that’d grab your attention!  



We know there are a lot of PL/SQL developers out there who are looking to make (or have already made) a break into application development using a more popular programming language like Java as well as many Java developers who use Toad to make access to Oracle easier.  

According to a 2006 IOUG survey of Oracle sites,...

Richard discusses a question from a user who tried our new SQL Optimizer 7.5.

Using indexes can improve the performance of applications using the database. This is well known to many, many people. However, the application of too many indexes can have the reverse effect. There’s a balance that must be struck and you may find yourself needing to locate indexes that aren’t being used. If you’re already using the Quest SQL Optimizer for DB2 LUW, you can take advantage of the Index Usage Analyzer to help...

Version 7.5 of Quest SQL Optimizer for Oracle will be released in October. The major effort for this new release was in the optimizer engine and the new UI for the optimization, index generation and execution functions. The following is a screen shot of the new UI. It looks tidy and easier to use compared to the old version. All the statistics names now use the Oracle standard naming, so, users can easy understand each statistics...

Written by Rene Woody

This blog is a continuation 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 expectation that Quest SQL Optimizer will always be able to improve the performance of your SQL statements.

...

This blog is a continuation 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 why the run time of a SQL statement may vary from one execution to the next.

When you execute a SQL statement several times in Quest SQL Optimizer, you may notice that the run time will vary from one execution to the next. This adds an additional challenge to picking out the best SQL statement from a group of alternative statements.

...

I sometimes worry that people are confused about how to use the Quest SQL Optimizer for Oracle to help them tune their SQL statements. The solution is extremely powerful and provides you with the options to control numerous aspects of the optimization process. In fact, I suggest you read Richard To’s articles on Toad World to get an in depth understanding of the optimization process and the various settings within the SQL...

When selecting the best performing SQL statement for your application, how should disk I/O be weighed in comparison to the run time when you are selecting the best SQL alternative to replace your original SQL statement?

A SQL statement with a variable that is provided at run time poses an extra challenge when trying to determine which alternative SQL statement is going to give the best overall performance. Each time the SQL statement is executed, a different variable could be supplied.

This blog covers testing the SQL alternatives in a development environment when it is not possible to do extensive testing in the production environment.

In Quest SQL Optimizer when you generate alternative SQL statements for your original SQL statement you may be presented with hundreds of SQL alternatives. The only way to be completely sure that you have the fastest one is to run all the SQL alternatives. But unless the SQL statements run relatively quickly, this is not always practical. So then you are left to determine which SQL statements you should select to test.

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.

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.

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.

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

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.

...

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.

...

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

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.

...

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

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 the Oracle optimization hints are applied to the SQL statements to generate more execution plans.

 

One technique that you can...

Recently, I received an email from the presales support team. They asked me to help them tune three SQL statements for a benchmark database provided by a customer. Our presales support said that our SQL Optimizer was not providing any alternatives for two of these three complex SQL statements. I found two of these SQL statements were very complicated and I knew that there should be a lot of alternative SQL generated. Finally,...

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 optimizing SQL statements that use VIEWs.



In early versions of the Oracle database, a VIEW was handled like a temporary table that...

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 how to generate more SQL alternatives.



When you optimize a simple SQL statement,...

Written by Rene Woody

This blog is a continuation of a series (view last blog on the Optimization Process) about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers how to limit the number...

Written by Rene Woody This blog is the beginning of a series that will cover the optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle.

When writing a SQL statement, it is easy to be satisfied when you have figured out how to get the correct results. This is especially true when you are writing a complicated...