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.
ownname => ‘M’,
tabname => ‘WFA’,
method_opt =>’FOR COLUMNS PROCESSNAME SIZE 12, ESCROLE SIZE 5, ASSIGNSTATUS SIZE 7′);
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.