Buggy bugs

This past Sunday (05-NOV-2006) we installed patch 4752541 (Intermittent PLS-306 / ORA-1722 / ORA-1858 under load) to fix a problem our webapp was having on overloaded procedure calls. We were looking golden up until Wednesday when our Production system starting spiking on library cache latch waits (and log file sync waits were in there as well). Being good little Oracle DBAs, we filed a case with Oracle Support, and learned a bit, but nothing really concrete. That evening we hit another major slowdown. It was decided to yank out the Sunday patch because it did exhibit some relationship and we thought it best to be safe. So here we are, Friday, with no more critical slow downs but still hitting the original webapp errors. We now have 3 SRs open with Oracle, 4 engineers working on them, two "team leads"/duty managers keeping tabs, and a cell-phone number for the Director of Oracle Support.

All this to say that Oracle is really, very complex. We still do not even know for sure if the patch caused the slowdown, all we have is the circumstantial evidence. This also showcases why you get a 10gR2 patchset that is rife with bug fixes and has a footprint that is as large as a baseline install. 10.2.0.3 promises to be more of the same, meaning that 10.2.0.2 had little to no impact on the number of bugs. Granted, 10.2.0.2 did fix a large number of problems, but it looks like the sum of the ones that got through the cracks and newly introduced bugs (ala "Buggy bugs") totals the bugs fixed.

Just to set the record straight, I only complain and gripe about software I love.

Support Analyst caught asking Tom

I found this very interesting.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:707586567563
Search for "We have noticed several system tables where"

Keeping that in mind, here is my question in SR# 5684375.992 on 11-AUG-06:

This SR is for information only. We have noticed several system tables where dba_tab_col_statistics reports HEIGHT BALANCED histograms where bucket_cnt > distcnt (count of distinct values). It was my understanding that a similar problem was fixed in 10.2.0.1 (dbms_stats method_opt size n generates a FREQUENCY histogram where n >= NDV). However, even more perplexing is why do we have many height balanced histogram buckets on a column with only 1 distinct value?

SQL > select ENDPOINT_NUMBER,ENDPOINT_VALUE 
from dba_histograms 
where table_name = 'INDPART$' 
and column_name = 'TS#';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
            217             79

So, some questions?
1. in sys.hist_head$, how is bucket_cnt calculated?
2. what determines how many buckets a column gets?

Is it just me, or did my support analyst ask my question on AskTom, verbatim?

While I like Tom's cavalier attitude in general (ala "and you don't need/want to be querying hist_head$, just use the normal views like the rest of us :)"), I sure wish he answered the question.

Another interesting point: this question was posed on August 14th, the same day I got fed up with the Support Analyst and asked to switch to someone else. I liked the 2nd guy much better! You could give him awards. But that first guy… wow, he needs help.

As I said, just very interesting. I wonder how many other Support Analysts I can find on AskTom.