Histograms Intro

The following are three great posts by Jonathan Lewis explaining all about histograms up to all Oracle versions prior to 12c. You will learn about the two main types of histograms: frequency histograms and height-balanced histograms, what they are, how you create them, and how they are used.

• Histograms - Part 1
• Histograms - Part 2
• Histograms - Part 3

Also, a great article about optimizer statistics in general but including a nice intro on histograms also, can be found in the following OTN white paper "Understanding Optimizer Statistics".

Enjoy!

Oracle Learner

CURSOR_SHARING explained

In Oracle8i, release 2 (version 8.1.6), Oracle introduced a new feature called CURSOR_SHARING. Cursor sharing is an 'auto binder' of sorts. It causes the database to rewrite your query using bind variables before parsing it. This feature will take a query such as:

select * from emp where ename = 'KING'; 

and will automatically rewrite it as: 

select * from emp where ename = :SYS_B_0

CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache.

In this great article by Maria Colgan the possible values of CURSOR_SHARING are explained.

Also check out this great collection of blog posts related to cursor sharing from the Oracle Optimizer blog.

Enjoy!
Oracle Learner.