We have hit what is probably a bug on our 11.1 database. Our optimizer statistics gathering job has been running for hours and consuming a ton of CPU resources. When we look at an AWR report for this period of time we see inserts into the stattab as the longest running SQL! Here are the two top inserts, with the stattab name renamed to make it generic:
insert into “TEST”.”STATTAB” select /*+ rule */ :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, ot.name c1, null c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.hist_head$ h, histgrm$ hg where :3 is null and u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and h.obj# = ot.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, ot.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.tabpart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and tp.bo# = ot.obj# and tp.obj# = op.obj# and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, op.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, op.name c1, op.subname c2, os.su bname c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.tabsubpart$ ts, sys.obj$ os, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and ts.pobj# = tp.obj# and ts.obj# = os.obj# and ((:3 is null and :vc_cascade_parts is not null) or (op.subname = :3 and (:vc_cascade_parts is not null — cascade to subpart or os.subname is null)) — matches partition itself or os.subname = :3) and — matches subpartition h.obj# = os.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# order by c5, c1, c2, c3, c4, n10
insert into “TEST”.”STATTAB” (statid, type, version, flags, c1, c4, c5, cl1) select /*+ rule */ :5 statid, ‘E’ type, :6 version, 256, ot.name c1, c.name c4, u.name c5, to_lob(c.default$) cl1 from sys.user$ u, sys.obj$ ot, sys.col$ c where u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and bitand(c.property, 32) = 32 and bitand(c.property, 65536) = 65536 and substr(c.name, 1, 6) = ‘SYS_ST’ and (:4 is null or c.name = :4) and (:5, ‘E’, :6, ot.name, c.name, u.name) not in (select statid, type, version, c1, c4, c5 from “TEST”.”STATTAB”)
We have some custom written statistics gathering scripts that were written before 11g came out. Part of the design was to use a stattab to store the previous version of the statistics. Theoretically if you had a performance problem you could revert back to the previous statistics or at least see what they were before the performance went south. I believe that 11g comes with a feature like this built in. Anyway, the database in question was setup with the standard pre-11g scripts and was working fine at first but then started getting the described terrible performance. I traced this down to the tables with extended statistics. In 11g you have the ability to add statistics on a grouping of columns in a table. This helps counter issues with correlated predicates on those columns. But, there must be some bug related to backing up the extended statistics because in my testing I made a copy of a table without the extended stats and it ran fine. With the extended stats added the above inserts were prevalent.
Anyway, this may not be of much use to anyone else, but if someone searches the web for the inserts listed they might be helped by this post. Bottom line – extended stats on 11g and stattab – if the stattab insert is slow get rid of the stattab. You probably don’t need it anyway on 11g.
– Bobby
