Structured Data: Oracle Database Performance And Scalability Blog

Oracle OpenWorld 2008: The Real-World Performance Group

June 18, 2008 – 1:00 am
With Oracle OpenWorld 2008 only a few months away, I thought I would take a moment to mention that the Real-World Performance Group will again be hosting three sessions. If you were at the sessions last year, then you probably know how good they were. If you didn't make it, you can check out the recap from last year's sessions. Hope to see you there! Session ID: S298785 Session Title: Real-World Database Performance Roundtable ...

Posted in Oracle | 3 Comments »

Using Bitmap Indexes Effectively

May 29, 2008 – 1:00 am
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 it here. What We Are Given The author of the original post had stated that the table in question contains about 16 million rows and states: "The table contains three IDEAL columns for bitmap indexes the first of which may have only two, the second three ...

Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Performance, SQL Tuning, Statistics, Troubleshooting, VLDB | 6 Comments »

Null-Aware Anti-Join

May 22, 2008 – 1:00 am
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 unnesting in Oracle Database 11g. The NA stands for Null-Aware. There is also a second type of Null-Aware Anti-Join, which is the Single Null-Aware Anti-Join which is displayed in the execution plan as ANTI SNA. The null-aware anti-join may be computed ...

Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Performance | 7 Comments »

Top Ways How Not To Scale Your Data Warehouse

April 28, 2008 – 1:00 am
Working in the Real-World Performance Group at Oracle has allowed me to see quite a few customers' data warehouses. Unfortunately some customers find their data warehouse suffering from performance problems, not because there is a platform issue, but often because the features are not used or are not used correctly. I thought I'd put together a list of the most common problems but present them in a facetious manner. The following is meant to be sarcastic and read with a bit of humor. Consider it the "Comedy of Errors" data warehouse edition. ...

Posted in Data Warehousing, Oracle, Performance, VLDB | 5 Comments »

Choosing An Optimal Stats Gathering Strategy

March 26, 2008 – 1:00 am
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 half the battle." The other half of the battle is successfully applying that knowledge to the databases that you manage. Statistics are input to the Oracle Optimizer and the foundation of good plans. If the statistics supplied to the Oracle Optimizer ...

Posted in 10gR2, Execution Plans, Optimizer, Oracle, Statistics, Troubleshooting | 12 Comments »

Awareness Test

March 21, 2008 – 2:30 pm
I came across this video the other day and I immediately thought of rule #3 from my Got Root Cause? post: "Be detail oriented, but do not become too obsessed with any one detail." Watch the video and see if you pass the Awareness Test. Most importantly, remember the punch line of the video the next time you are troubleshooting.

Posted in Oracle, Troubleshooting | 2 Comments »

There Is No Time Like ‘%NOW%’ To Use Dynamic Sampling

March 5, 2008 – 1:00 am
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 complex plan so I simplified it down to this test case for the purpose of this blog post: [sourcecode language='sql']select [...] from fact_table al1 where al1.region = '003' and al1.order_type = 'Z010' and al1.business_type ...

Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, VLDB | 11 Comments »

ANSI Outer Joins And Lateral Views

February 18, 2008 – 6:00 pm
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 recently came through the Real-World Performance Group. For simplicity purposes and so that you can play along at home, the test case has been recreated to use EMP and DEPT which have been created and populated via the $ORACLE_HOME/rdbms/admin/utlsampl.sql script. The Three Test Cases Consider ...

Posted in Execution Plans, Optimizer, Oracle, SQL Tuning, Troubleshooting | 14 Comments »