Execution Plans

Creating Optimizer Trace Files

August 18, 2011
By

Many Oracle DBA’s are probably familiar with what Optimizer trace files are and likely know how to create them. When I say “Optimizer trace” more than likely you think of event 10053, right? SQL code like this probably is familiar then: In 11g, a new diagnostic events infrastructure was implemented and there are various...

Read more »

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

Managing Optimizer Statistics Paper

February 16, 2009
By
Managing Optimizer Statistics Paper

Over the past couple days I’ve been reading through the recent paper by Karen Morton entitled “Managing Statistics for Optimal Query Performance“. In this paper Karen goes over many of the topics I have discussed as well (and a few that I have not) in the following blog posts: Troubleshooting Bad Execution Plans There...

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 »

Oracle 11g: Incremental Global Statistics On Partitioned Tables

July 16, 2008
By
Oracle 11g: Incremental Global Statistics On Partitioned Tables

Previously I blogged about the new and improved DBMS_STATS.AUTO_SAMPLE_SIZE used to calculate NDV in Oracle 11g and now I wanted to touch on another new feature of DBMS_STATS in 11g: Incremental Global Statistics On Partitioned Tables. Before Incremental Global Stats (Two-Pass Method) When DBMS_STATS.GATHER_TABLE_STATS collects statistics on a partitioned table, generally it does so...

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 »

Null-Aware Anti-Join

May 22, 2008
By

Recently someone showed me a query execution plan with an operation of HASH JOIN ANTI NA. At first, it was thought maybe it was a bug and the operation had a type-o in it, but after further research it was discovered it was a valid operation and a new cost-based query transformation for subquery...

Read more »

Choosing An Optimal Stats Gathering Strategy

March 26, 2008
By

Recently the Oracle Optimizer Development Team put out a White Paper entitled Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer. This paper discusses the main differences between 9i and 10g in the subject area of the Optimizer and Statistics. As G.I. Joe said, “Now we know! And knowing is...

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 »