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.Leave a comment | Filed in Oracle | Tags: performance
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.
ALTER SESSION SET “_FIX_CONTROL”=’9550277:1′;
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 | Filed in Oracle | Tags: performance
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 | Filed in Oracle | Tags: performance, pl/sql
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.Comments Off | Filed in Oracle | Tags: EBS, performance, presentation
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: performance, video
Here is another video from Stephane Faroult about how you can improve query performance:3 Comments | Filed in Oracle | Tags: database, performance, sql, video
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: conference, performance, sql
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: performance, tkprof, tuning