Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘Statistics’ Category

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

with 11 comments

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.

Written by Carlos Sierra

October 23, 2012 at 6:49 am

Posted in Statistics

Should I delete my column Histograms to improve plan stability?

with 4 comments

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

Written by Carlos Sierra

August 13, 2012 at 8:33 am

About DBA_TAB_HISTOGRAMS.ENDPOINT_VALUE

leave a comment »

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

Written by Carlos Sierra

August 9, 2012 at 6:46 am

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

with 5 comments

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.

Written by Carlos Sierra

July 20, 2012 at 4:52 am