Archive for August 2012
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
Carlos Sierra's Tools and Tips 