News, views, tips and tricks on Oracle and other fun stuff

SQL Plan Stability and CBO Statistics Myths Busted

Carlos Sierra:

Since lack of Histograms or freezing CBO Statistics do not guarantee Plan Stability, do not rely on these two myths. If what you are looking for is Plan Stability use then SQL Plan Management available since 11g or SQL Profiles available from 10g.
Comments Off on SQL Plan Stability and CBO Statistics Myths Busted | Filed in Oracle | Tags:

Delete Column Histograms to Improve SQL Plan Stability

Carlos Sierra:

If you are on 10g, or if on 11g but ACS (Adaptive Cursor Sharing) is not an option, then deleting column Histograms may be your best option to improve your plan stability. Like any other change, you rather test first on a non-production environment. Even in such test environment, you may want to restrict your test to only those tables accessed by your SQL. On 11g, DBMS_STATS.DELETE_COLUMN_STATS provides an option to delete just the Histograms while preserving all other column statistics. This is by using parameter COL_STAT_TYPE with value HISTOGRAM. If you want to do the same on 10g, you may want to use the SQLT HGRM module. In any case you can always restore column statistics using DBMS_STATS.RESTORE_TABLE_STATS.

Carlos gives us a tip on how to disable the use of Histograms with a hidden parameter:

If you are considering deleting Histograms to test the effect on an execution plan and the performance of your SQL, you may want to test first asking the CBO to simply ignore them. If patch for bug 9550277 has been applied in your system then you can command below.

Worth noting that Jonathan Lewis was very successful in tuning a customer’s query by simply deleting histograms:

The most critical piece of advice I had given them was to get rid of ALL the histograms they had on their system, and then watch very carefully for any signs that they might need to re-introduce a handful of histograms over the next few weeks. One of their critical queries completed in less that 2 seconds when histograms were removed, but took 33 seconds to complete when histograms were in place.
Comments Off on Delete Column Histograms to Improve SQL Plan Stability | Filed in Oracle | Tags:

Bind, don’t concatenate, to optimize performance and simplify dynamic string construction

Steven Feuerstein gives us a great and simple example of how to use bind variables to avoid concatenation of variable values into dynamic SQL strings and, at the same time, improve performance by order of magnitude:

When you use bind variables, you greatly simplify the task of writing the dynamic SQL string. You don’t have to write all that concatenating code and you don’t have to perform datatype conversions. The USING clause automatically performs native binding of the appropriate types.

He also notes:

You can bind only variable values. You can’t bind in the names of tables or columns, nor can you bind in parts of a SQL statement structure, such as the entire WHERE clause. In these cases, you must use concatenation.
Comments Off on Bind, don’t concatenate, to optimize performance and simplify dynamic string construction | Filed in Oracle | Tags: ,

Best Practices for Oracle E-Business Suite Performance Tuning

Steven Chan:

Isam Alyousfi and Lester Gutierrez are key members of our Applications Performance Group. They recently presented their popular session covering performance tuning tips for all layers of the E-Business Suite at OAUG/Collaborate earlier this year. […] This presentation is chock full of tips, pointers, and hard-won knowledge. It represents the distillation of countless performance-related Service Requests and customer escalations. If you’re grappling with performance issues in your environment, or simply trying to squeeze more performance out of existing hardware, I’d strongly recommend downloading this presentation.

Tuning All Layers Of E-Business Suite – Performance Topics

Comments Off on Best Practices for Oracle E-Business Suite Performance Tuning | Filed in Oracle | Tags: , ,

Join factorization, a new Oracle DB 11gR2 feature

Hong Su from Inside the Oracle Optimizer:

The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries. Join factorization is a cost-based transformation. It can factorize common computations from branches in a UNION ALL query which can lead to huge performance improvement.

Yet another reason to upgrade to 11gR2.

Comments Off on Join factorization, a new Oracle DB 11gR2 feature | Filed in Links, Oracle | Tags: ,

Oracle Performance Monitoring in Less than 12 Minutes (Video)

In this two part video, Stephane Faroult talks about “the basics of Oracle Performance Monitoring, and how you can get fancy (and even useful) reports without spending a fortune”. Stephane also demonstrates “how to install the (free) statspack utility and how some web resources can help you generate the reports you need for proactive monitoring”.

2 Comments | Filed in Oracle | Tags: ,

Rewriting SQL Queries for Performance in 9 Minutes (Video)

Here is another video from Stephane Faroult about how you can improve query performance:

3 Comments | Filed in Oracle | Tags: , , ,

Northwest Oracle Users Group (NWOUG) 2006 Fall Conference

When was the last time you did not use a computer for one full day? My last time was yesterday and it was not because I was on vacation. Yesterday, I was in the Northwest Oracle Users Group (NWOUG) 2006 Fall Conference.

I attended six 45-minute presentations. The sessions that were interesting and that I liked most were the one presented by Dan Morgan: The Hidden Jewels in 10g, and the one presented by Cary Millsap: Accountability for System Performance. Dan did not use PowerPoint (which is good because I do not like watching slides while at the same time listening to the presenter reading the slides, I like the “real” thing), he actually fired up his SQLPlus and demonstrated a few really cool, useful and some undocumented Oracle 10g SQL features, which I will be blogging about soon. I also liked Cary Millsap’s presentation mainly because the subject that he talked about was really interesting.

In the conference, I also bumped into Jared Still. Jared and I used to work for the same company before we both moved on to different employers, but still in the same neighborhood. Even though we worked for the same employer, we were not in the same department. After all, Jared is a DBA and I am a developer :).

2 Comments | Filed in Oracle | Tags: , ,

How do you use tkprof to solve a performance problem?

In his latest article, Jonathan Lewis uses tkprof to analyse the performance of a query. He concluded that “When you examine the output from tkprof, it often takes a simultaneous cross-check of the block level statistics, the Row Source Operation counts, and some sensible guesswork about the purpose of the query, to be able to understand where the problem is, and how it may be addressed.” Read Jonathan’s interesting article: An example of making sensible guesses from a tkprof output.

2 Comments | Filed in Oracle | Tags: , ,