Archive for the ‘SQL Tuning’ Category
Thursday, May 29th, 2008
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 ...
Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Performance, SQL Tuning, Statistics, Troubleshooting, VLDB | 6 Comments »
Wednesday, March 5th, 2008
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 ...
Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, VLDB | 11 Comments »
Monday, February 18th, 2008
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 ...
Posted in Execution Plans, Optimizer, Oracle, SQL Tuning, Troubleshooting | 16 Comments »
Sunday, January 6th, 2008
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 ...
Posted in 11gR1, Execution Plans, Oracle, Performance, SQL Tuning, Troubleshooting | 5 Comments »
Wednesday, January 2nd, 2008
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 ...
Posted in 10gR2, 11gR1, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics | 29 Comments »
Wednesday, November 21st, 2007
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. ...
Posted in Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, Troubleshooting | 21 Comments »
Monday, November 19th, 2007
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 ...
Posted in Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, VLDB | 3 Comments »
Wednesday, October 31st, 2007
In the Real-World Performance Roundtable, Part 2: The Optimizer, Schema Statistics, SQL Tuning at Oracle OpenWorld 2006, I worked an example of how the optimizer can have difficulty estimating the correct cardinality when there is data correlation. (The Zodiac example can be found on pages 46-49 of the presentation.) ...
Posted in 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, SQL Tuning, Statistics, VLDB | 4 Comments »