Structured Data: Oracle Database Performance And Scalability Blog

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

March 5, 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 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
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 | 16 Comments »

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

January 6, 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 allows you to monitor the performance of SQL statements while they are executing as well as see the breakdown of time and resources used for recently completed statements. It is on by default when STATISTICS_LEVEL is set to to ALL or TYPICAL (the default value) and monitors statements that ...

Posted in 11gR1, Execution Plans, Oracle, Performance, SQL Tuning, Troubleshooting | 5 Comments »

What Are Your System Statistics?

January 2, 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 queries: select version from v$instance; select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN'; For example, my noworkload system statistics look like this: SQL> select version from v$instance; VERSION ----------------- 11.1.0.6.0 SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN'; PNAME ...

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

Oracle Myth Busting: Show, Don’t Tell

December 16, 2007
Richard Foote has recently started blogging (as of December 11th) and one of his recent posts discusses Oracle Myths and Information Pollution. I find this topic very interesting as I'm always amazed at the number of people who make changes to their production database based on the results from their favorite Internet search engine, and don't even bother to test it themselves first! Like Richard, I'd encourage anyone who gets information from the Internet to do your diligence and understand the why, and not just the results someone else observed. I guess one could associate it to ...

Posted in Oracle, Troubleshooting | 1 Comment »

Oracle Optimizer Development Team Starts A Blog

December 5, 2007
Since the introduction of the Cost Based Optimizer (CBO) in Oracle 7.0, people have been both fascinated and terrified by it and the statistics that feed it. There has long been a belief that a degree in witchcraft or black magic is required to successfully work with the CBO. Some people feel this shroud of mystery is caused in part by the lack of documentation or detailed examples about the Optimizer. In order to help remove this shroud and the black magic that surround the Optimizer, the development team responsible for it have started a blog. The blog postings ...

Posted in Execution Plans, Optimizer, Oracle, Performance, Statistics, Troubleshooting | 8 Comments »

Oracle Database 11g Release 1 (11.1.0.6.0) for HP-UX PA-RISC

November 21, 2007
Oracle Database 11g Release 1 (11.1.0.6.0) was released for HP-UX PA-RISC today (11/21). Grab it from the Oracle Database Software Downloads on OTN.

Posted in 11gR1, Oracle | No Comments »

Troubleshooting Bad Execution Plans

November 21, 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. While changing parameters or analyzing a 10053 trace might be useful for debugging at some point, I feel there is a much more simple way to start to troubleshoot bad execution plans. Verify The Query Matches The Business Question This seems like an obvious thing to do, but I've seen numerous cases ...

Posted in Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, Troubleshooting | 21 Comments »