Using Histograms in Font

Generator Code 39 Full ASCII in Font Using Histograms

Normally, the CBO assumes that data is uniformly distributed in a table. There are times when data in a table isn t distributed in a uniform way. If you have an extremely skewed data distribution in a table, you re better off using histograms to store the column statistics. If the table data is heavily skewed toward some values, the presence of histograms provides more efficient access methods. Histograms use buckets to represent distribution of data in a column, and Oracle can use these buckets to see how skewed the data distribution is. You can use the following types of histograms in an Oracle database: Height-based histograms divide column values into bands, with each band containing a roughly equal number of rows. Thus, for a table with 100 rows, you d create a histogram with 10 buckets if you wanted each bucket to contain 10 rows. Frequency-based histograms determine the number of buckets based on the distinct values in the column. Each bucket contains all the data that has the same value.

You create histograms by using the METHOD_OPT attribute of the DBMS_STATS procedure such as GATHER_TABLE_STATS, GATHER_DATABASE_STATS, and so on. You can either specify your own histogram creation requirements by using the FOR COLUMNS clause, or use the AUTO or SKEWONLY values for the METHOD_OPT attribute. If you choose AUTO, Oracle will decide which columns it should collect histograms for, based on the data distribution and workload. If you choose SKEWONLY, Oracle will base the decision only on the data distribution of the columns. In the two examples that follow, I use the FOR COLUMNS clause to specify the creation of the histograms.

The following example shows how to create a height-based histogram while collecting the optimizer statistics: SQL> BEGIN DBMS_STATS.GATHER_table_STATS (OWNNAME => 'HR', TABNAME => 'BENEFITS', METHOD_OPT => 'FOR COLUMNS SIZE 10 Number_of_visits'); END; The following example shows how to create a frequency-based histogram: SQL> BEGIN DBMS_STATS.GATHER_table_STATS(OWNNAME => 'HR', TABNAME => 'PERSONS', METHOD_OPT => 'FOR COLUMNS SIZE 20 department_id'); END;

You can use the DBA_TAB_COL_STATISTICS view to view histogram information. Following are the two queries that show the number of buckets (num_buckets) and the number of distinct values (num_distinct), first for the height-balanced and then for the frequency-based histogram created in the previous section: SQL> SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = 'BENEFITS' AND column_name = 'NUMBER_OF_VISITS'; COLUMN_NAME ----------------NUMBER_OF_VISITS NUM_DISTINCT ------------320 NUM_BUCKETS -----------10 HISTOGRAM --------------HEIGHT BALANCED

SQL> SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = 'PERSONS' AND column_name = 'DEPARTMENT_ID'; COLUMN_NAME -------------DEPARTMENT_ID NUM_DISTINCT -----------8 NUM_BUCKETS -----------8 HISTOGRAM ---------FREQUENCY

Although using bind variables improves performance and scalability by reducing parse time and memory usage, literal values actually produce better execution plans than bind values for variables. When you force cursor sharing in the database by setting the CURSOR_SHARING parameter to EXACT or SIMILAR, some SQL statements end up with suboptimal plans for some bind variable values. The CostBased Optimizer may very well create a suboptimal plan if it happens to peek at the bind values and if the bind values used by the first SQL statements to go into the shared pool are unrepresentative of the true values of the variable. Developers and DBA s sometimes resort to setting the unofficial Oracle initialization parameter _OPTIM_PEEK_USER_BINDS (ALTER SESSION SET "_optim_peek_user_binds"=FALSE;) to prevent the database from peeking at the bind values. Adaptive cursor sharing provides a much more elegant way to prevent the optimizer from creating suboptimal execution plans caused by bind peeking. Oracle relies on its bind peeking technique when it first parses a SQL statement. The optimizer will always hard parse a new statement and peek at the bind variable values to get a sense of what the values look like. The initial bind values it sees during the bind peeking have an inordinate influence