Oracle Histogram Article

I subscribe to the Hotsos Monthly Newsletter, available to those who register for a free account at Hotsos.

In the most recent newsletter there is a case study article about using histograms to resolve a performance problem. I haven’t had much opportunity to use histograms so I discovered you can specify the column name and set the bucket size in the method_opt parameter in DBMS_STATS package.

Below is the example from the article itself.

BEGIN
DBMS_STATS.gather_table_stats(
ownname => ‘M’,
tabname => ‘WFA’,
method_opt =>’FOR COLUMNS PROCESSNAME SIZE 12, ESCROLE SIZE 5, ASSIGNSTATUS SIZE 7′);
END;

Due to my limited experience with this topic, it is unclear to me why those specific number of buckets were picked for each column. Was it arbitrary or was there an educated reason. Based on the author’s data distribution analysis, I thought it correlated to the number of distinct values, but that does not appear to be the case with the ESCROLE and ASSIGNSTATUS columns. I’ll add that to the next layer of the histogram onion to be peeled.

Anyway, I found the case study worth reading and thought I would share. Might come in handy someday soon.

References
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) – Using Histograms

Wolfgang Breitling Hotsos Symposium Papers/Presentations regarding histograms

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: