SQL Tuning

Reading Parallel Execution Plans With Bloom Pruning And Composite Partitioning

October 12, 2010
By

You’ve probably heard sayings like “sometimes things aren’t always what they seem” and “people lie”. Well, sometimes execution plans lie. It’s not really by intent, but it is sometimes difficult (or impossible) to represent everything in a query execution tree in nice tabular format like dbms_xplan gives. One of the optimizations that was introduced...

Read more »

The Core Performance Fundamentals Of Oracle Data Warehousing – Set Processing vs Row Processing

July 20, 2010
By

In over six years of doing data warehouse POCs and benchmarks for clients there is one area that I frequently see as problematic: “batch jobs”.  Most of the time these “batch jobs” take the form of some PL/SQL procedures and packages that generally perform some data load, transformation, processing or something...

Read more »

The Impact Of Good Table And Query Design

March 19, 2009
By

There are many ways to design tables/schemas and many ways to write SQL queries that execute against those tables/schemas. Some designs are better than others for various reasons, however, I think that frequently people underestimate the power of SQL (for both “good” and “evil”). All too often in data warehouses, I see tables designed...

Read more »

DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS

October 14, 2008
By

I’ve written before on choosing an optimal stats gathering strategy but I recently came across a scenario that I didn’t directly blog about and think it deserves attention. As I mentioned in that previous post, one should only deviate from the defaults when they have a reason to, and fully understand that reason and...

Read more »

Automatic DB_FILE_MULTIBLOCK_READ_COUNT

August 14, 2008
By

Note: Originally this experiment was from a post I wrote on the Oracle Forum: Database – General. I recommend that you read Jonathan Lewis’ summarization of the thread instead of reading all 671 posts (as of today). You will spend much less time and get more out of the discussion. One of the new...

Read more »

Using Bitmap Indexes Effectively

May 29, 2008
By

Recently I was reading this thread, “Trying to make use of bitmap indexes” on the Oracle Forum. Before I had finished a working example, Jonathan Lewis had posted his response which was on par with my thoughts. Since this is a topic I see frequently, I thought I would finish my experiment and publish...

Read more »

There Is No Time Like '%NOW%' To Use Dynamic Sampling

March 5, 2008
By

I recently came across a query in which the Optimizer was making a poor cardinality estimate, which in turn caused inefficient join type, which in turn caused the query to run excessively long. This post is a reenactment of my troubleshooting. The Suspect SQL The original SQL was quite large and had a fairly...

Read more »

ANSI Outer Joins And Lateral Views

February 18, 2008
By

A few months ago the Oracle Optimizer Team did a blog post entitled Outerjoins in Oracle. In the Lateral View section of that post they go through some examples and discuss how a query is transformed with the ANSI outer join syntax. I thought it would be useful to go through an example that...

Read more »

Oracle 11g: Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR

January 6, 2008
By

Many times a DBA wants to know where a SQL statement is in its execution plan and where the time is being spent. There are a few ways to find out this information, but an 11g new feature makes gathering this information extremely easy. Oracle 11g Real-Time SQL Monitoring allows you to monitor the...

Read more »

What Are Your System Statistics?

January 2, 2008
By

I’ve been working on a few test cases and I’m in search of some real-world data. If your production Oracle database uses system statistics, either Workload Statistics or Noworkload Statistics, and you are willing to share them, please post a comment with the output from the following two queries: select version from v$instance; select...

Read more »

Troubleshooting Bad Execution Plans

November 21, 2007
By

One of the most common performance issues DBAs encounter are bad execution plans. Many try to resolve bad executions plans by setting optimizer related parameters or even hidden underscore parameters. Some even try to decipher a long and complex 10053 trace in hopes to find an answer. While changing parameters or analyzing a 10053...

Read more »

The Real-World Performance Group: Oracle OpenWorld 2007 Recap

November 19, 2007
By

Oracle OpenWorld 2007 has come and gone and from the Real-World Performance Group’s perspective we’d consider it a successful one. The content of this year’s presentations seems to have gone over quite well as shown by the number of hallway comments and emails we received. At least two Oracle bloggers have put up their...

Read more »