## Archive for the ‘**Statistics**’ Category

## CBO Statistics on 11g: How to reduce poorly performing Execution Plans

Today, October 23 at 4:00 pm GMT (London time), Mauro Pagano will be presenting an interesting topic in CBO Statistics from Support perspective: what works and what almost works. This presentation is not that much about explaining features, but more about sharing Mauro’s experience regarding CBO Statistics. As many of you know, Mauro is one of the top Oracle resources when it comes to SQL Tuning hands-on within the Query Tuning team at Oracle Support.

This Webinar has registered over 600 participants, and I would not be surprise many more will join today. If you are interested please try to pre-register since the Webinar closes when it reaches 1000 participants.

Webinar will start at 4:00 pm GMT (London), 8:00 am San Francisco time, 11:00 am eastern time (Orlando!).

I (Carlos Sierra) will be there answering posted questions. Abel Macias and Steve Dixon will also join to help Mauro answering questions, which we expect to come in large numbers during the Webinar. This is a hot topic, since poor CBO Statistics causes more SQL to perform poorly than any other factor.

Link to register: WebEx

I hope you can attend live. If you cannot make it, you can always review the recording after a few days.

## Should I delete my column Histograms to improve plan stability?

When we combine the use of bind variables in a SQL statement with column Histograms we usually end-up having multiple execution plans over time. This plan instability is due to the lack of control we have over the selectivity of the predicates at the time the SQL is hard parsed while using bind peeking. On 11g this behavior is mitigated with the use of Adaptive Cursor Sharing (ACS), and it can be further improved when using ACS together with SQL Plan Management (SPM).

As we generate multiple execution plans for one SQL statement where the reason is the combination of binds and Histograms, some of these plans may perform better than others over a large number of executions. Thus it is natural to consider some of these execution plans as “bad plans”. If the use of ACS and SPM is not an option, then we may need to consider fixing the plan instability issue with the suppression of either bind peeking or Histograms. If we are in such case, it is better to consider the removal of Histograms first, and leave bind peeking active. The reason is that bind peeking even with no Histograms still uses CBO column statistics and among other things it uses column low and high values when computing selectivity.

Back to the question: Should I delete my column Histograms to improve plan stability?

If you are on 10g, or if on 11g but ACS 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.

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';

## About DBA_TAB_HISTOGRAMS.ENDPOINT_VALUE

If you have looked at the values on DBA_TAB_HISTOGRAMS.ENDPOINT_VALUE you may have seen some like 281422806648623000000000000000000000 when you were actually expecting something like 63736. I have used in SQLT the two functions below for many years. I got the foundations of these two out of a public source which unfortunately I did not record. Since you may or not may have SQLT, I thought a good idea to share these two functions in case you need them.

CREATE OR REPLACE FUNCTION get_internal_value (p_value IN VARCHAR2) RETURN VARCHAR2 IS temp_n NUMBER := 0; BEGIN FOR i IN 1..15 LOOP temp_n := temp_n + POWER(256, 15 - i) * ASCII(SUBSTR(RPAD(p_value, 15, CHR(0)), i, 1)); END LOOP; RETURN TO_CHAR(ROUND(temp_n, -21)); EXCEPTION WHEN OTHERS THEN RETURN p_value; END get_internal_value; / CREATE OR REPLACE FUNCTION get_external_value (p_value IN VARCHAR2) RETURN VARCHAR2 IS temp_n NUMBER; temp_i INTEGER; my_result VARCHAR2(32767) := NULL; BEGIN IF LENGTH(p_value) != 36 OR SUBSTR(p_value, 16) != '000000000000000000000' OR p_value > POWER(256, 15) OR p_value < POWER(256, 14) THEN RETURN p_value; -- cannot get external value END IF; temp_n := p_value / POWER(256, 14); -- get most significant digits -- decoding most significant digits then shift multiplying by 256 FOR i IN 1..14 LOOP temp_i := TRUNC(temp_n); temp_n := (temp_n - temp_i) * 256; IF temp_i NOT BETWEEN 32 AND 126 OR temp_n NOT BETWEEN 32 AND 126 THEN EXIT; -- reached the tail END IF; my_result := my_result||CHR(temp_i); -- all but last byte END LOOP; IF temp_i NOT BETWEEN 32 AND 126 THEN RETURN my_result||'?'; -- cannot decode END IF; -- scan to the right starting at temp_i FOR i IN temp_i..126 LOOP IF get_internal_value(my_result||CHR(i)) = p_value THEN RETURN my_result||CHR(i); -- approximate value END IF; END LOOP; -- scan to the left starting at temp_i FOR i IN 32..temp_i LOOP IF get_internal_value(my_result||CHR(temp_i + 32 - i)) = p_value THEN RETURN my_result||CHR(temp_i + 32 - i); -- approximate value END IF; END LOOP; RETURN my_result||CHR(temp_i); -- this is the best we could do EXCEPTION WHEN OTHERS THEN RETURN p_value; END get_external_value; / SELECT get_external_value('281422806648623000000000000000000000') FROM DUAL; GET_EXTERNAL_VALUE('281422806648623000000000000000000000') ---------------------------------------------------------- 63736 SELECT get_internal_value('63736') FROM DUAL; GET_INTERNAL_VALUE('63736') ---------------------------------------------------------- 281422806648623000000000000000000000

## A healthy approach to optimizer statistics: 11g improvements and common concerns you should know about

Have you ever wondered what is the “right way” to gather statistics for the Optimizer? Well, actually there is no “right way”. When someone asks me “how should we gathered statistics?”, my answer always is: “it all depends”.

Mauro Pagano is presenting at the Central Florida Oracle Users Group (CFOUG) meeting this coming Monday, July 23 at 6:00 PM, in Orlando, Florida. His topic is: “A healthy approach to optimizer statistics: 11g improvements and common concerns you should know about”. This presentation covers interesting features in 11g, but most important: what works well and what works almost well when it comes to statistics gathering. Mauro is one of the best engineers supporting query tuning (QTUNE) at Oracle. At his present role, he has seen many issues concerning statistics. Thus Mauro has a pretty good idea of what works best and in which conditions. He will also talk about common misconceptions and mistakes.

If you are in the Central Florida area, please contact the CFOUG to attend this valuable session. Power point will be available through the CFOUG web page after the presentation.