Database, SQL and PL/SQL

Enhancements to Optimizer Statistics

Test your knowledge of enhancements to statistics gathering and new types of histograms in Oracle Database 12c.

By Anita Mukundan

November/December 2015

This column contains sample questions, answers, and explanations about some of the enhancements that improve the cardinality estimates by the optimizer in Oracle Database 12c. The sample questions are of the type you may encounter when taking the “Upgrade Oracle9i/10g/11g OCA to Oracle Database 12c OCP” (1Z1-067), “Upgrade to Oracle Database 12c” (1Z0-060), or “Oracle Database 12c: Performance Management and Tuning” (1Z0-064) exams.

Automatic Generation of Column Group Statistics

Complex queries can contain multiple predicates on different columns from the same table. Extended statistics such as column groups help improve the statistical information available to the optimizer.

Question 1

You execute this PL/SQL block as the SYS user:


Next you successfully execute this SQL statement for the CUSTOMERS table in the SH schema:


What will be the outcome?

a. Statistics will be gathered for each column with skewed data.

b. Statistics will be gathered for column groups created automatically based on their data correlation in the table.

c. Statistics will be gathered for column groups created automatically based on a given workload.

d. Statistics and histograms will be gathered for all column groups and expressions used in the WHERE clause of queries on the CUSTOMERS table.

Concurrent Optimizer Statistics Gathering

Concurrent optimizer statistics gathering reduces the overall time required for gathering statistics for multiple tables in a schema or for multiple partitions or subpartitions in a table, by enabling the database to fully use multiple CPUs.

Question 2

You want to set the CONCURRENT preference for DBMS_STATS.SET_GLOBAL_PREF in the HR schema. You have all the required privileges, and the SYSAUX tablespace is online.

Which three configurations are required?

a. Set the JOB_QUEUE_PROCESSES parameter to at least twice the number of CPU cores.


c. Enable Oracle Database Resource Manager.

d. Disable Oracle Database Resource Manager.

e. Enable parallel statement queuing.

f. Disable parallel statement queuing.

Dynamic Statistics Enhancements

With dynamic statistics in Oracle Database 12c, the optimizer automatically decides whether dynamic statistics are useful and which dynamic statistics level to use for all SQL statements. (Dynamic statistics was previously called dynamic sampling.)

Question 3

What are the two implications of setting the parameter value OPTIMIZER_DYNAMIC_SAMPLING = 11?

a. Dynamic statistics will be gathered only if at least one table in a query statement has no statistics.

b. Dynamic sampling will be used for all queries, irrespective of the existing base statistics and the total execution time expected for the SQL statement.

c. The gathered statistics will persist in the cache and are available to other queries.

d. The gathered statistics will persist in the cache, available only for the query they were initially generated for.

e. The dynamic sampling level used for queries will vary, depending on the predicates used and the presence of stale, insufficient, or missing statistics.

New Types of Histograms

Oracle Database 12c introduces two new types of histograms: top-frequency and hybrid. The database determines the type of histogram to be created, based on the number of distinct values in the column. (With Oracle Database 12c, height-balance histograms are deprecated and replaced by hybrid histograms.)

Question 4

Which two conditions create a top-frequency histogram on columns in a table?

a. A column has more than 254 distinct values.

b. A column has 254 distinct values, but 99 percent or more of the rows in the table have fewer than 254 distinct values in that column.


d. The ESTIMATE_PERCENT parameter of the DBMS_STATS .GATHER_SYSTEM_STATS procedure is set to a value greater than 100.

e. The METHOD_OPT parameter of the DBMS_STATS.GATHER_SYSTEM_STATS procedure is set to SKEWONLY.

f. The METHOD_OPT parameter of the DBMS_STATS.GATHER_SYSTEM_STATS procedure is set to a value greater than or equal to 254.

For the following question, note that histograms sort values into buckets.

Question 5

Which three statements are true about hybrid histograms?

a. A hybrid histogram distributes values so that no duplicate values exist across buckets, and it stores the endpoint repeat count value.

b. The sampling percentage should be user-specified instead of using AUTO_SAMPLE_SIZE.

c. The user-specified number of buckets should be less than the number of distinct values in a column.

d. Hybrid histograms are the new default histogram type for columns with more than 254 distinct values.

e. Oracle Database creates a hybrid histogram when the number of distinct values (NDV) is less than or equal to the number of buckets.

Next Steps

 LEARN more about the Oracle Certification Program.

 READ Inside OCP columns.


Photography by Ricardo Gomez, Unsplash