VIEW: SYS.DBA_TAB_COL_STATISTICS

Source

View Text - Preformatted

select owner, table_name, column_name, num_distinct, low_value, high_value,
       density, num_nulls, num_buckets, last_analyzed, sample_size,
       global_stats, user_stats, notes, avg_col_len, HISTOGRAM,
       'SHARED'
from dba_tab_cols_v$
where last_analyzed is not null
union all
select /* fixed table column stats */
       'SYS', ft.kqftanam, c.kqfconam,
       h.distcnt, h.lowval, h.hival,
       h.density, h.null_cnt,
       case when nvl(h.distcnt,0) = 0 then h.distcnt
            when h.row_cnt = 0 then 1
	    when exists(select 1 from sys.histgrm$ hg
                        where c.kqfcotob = hg.obj# and c.kqfcocno = hg.intcol#
                          and hg.ep_repeat_count > 0 and rownum < 2) then h.row_cnt
             when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
                   (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
                then h.row_cnt
            else h.bucket_cnt
       end,
       h.timestamp#, h.sample_size,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       null, -- notes
       h.avgcln,
       case when nvl(h.row_cnt,0) = 0 then 'NONE'
            when exists(select 1 from sys.histgrm$ hg
                        where c.kqfcotob = hg.obj# and c.kqfcocno = hg.intcol#
                          and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
             when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
                   (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
                then 'FREQUENCY'
            else 'HEIGHT BALANCED'
       end,
       'SHARED'
from   sys.x$kqfta ft, sys.fixed_obj$ fobj,
         sys.x$kqfco c, sys.hist_head$ h
where
       ft.kqftaobj = fobj. obj#
       and c.kqfcotob = ft.kqftaobj
       and h.obj# = ft.kqftaobj
       and h.intcol# = c.kqfcocno
       /*
        * if fobj and st are not in sync (happens when db open read only
        * after upgrade), do not display stats.
        */
       and ft.kqftaver =
             fobj.timestamp - to_date('01-01-1991', 'DD-MM-YYYY')
       and h.timestamp# is not null
UNION ALL
select /* session private stats for GTT */
       u.name                  owner,
       o.name                  table_name,
       c.name                  column_name,
       h.distcnt_kxttst_cs     num_distinct,
       h.lowval_kxttst_cs      low_value,
       h.hival_kxttst_cs       high_value,
       h.density_kxttst_cs     density,
       h.null_cnt_kxttst_cs    num_nulls,
       case when nvl(h.distcnt_kxttst_cs,0) = 0 then h.distcnt_kxttst_cs
            when h.row_cnt_kxttst_cs = 0 then 1
            when exists(select 1 from sys.x$kxttstehs hg
                        where c.obj# = hg.obj#_kxttst_hs
                          and c.intcol# = hg.intcol#_kxttst_hs
                          and hg.ep_repeat_count_kxttst_hs > 0
                          and rownum < 2)
                 then h.row_cnt_kxttst_cs
            when bitand(h.spare2_kxttst_cs, 64) > 0
                then h.row_cnt_kxttst_cs
	    when (bitand(h.spare2_kxttst_cs, 32) > 0 or
                  h.bucket_cnt_kxttst_cs > 2049 or
                  (h.bucket_cnt_kxttst_cs > h.distcnt_kxttst_cs
                   and h.row_cnt_kxttst_cs = h.distcnt_kxttst_cs
                   and h.density_kxttst_cs*h.bucket_cnt_kxttst_cs < 1))
                then h.row_cnt_kxttst_cs
            else h.bucket_cnt_kxttst_cs
       end num_buckets,
       h.timestamp#_kxttst_cs  last_analyzed,
       h.sample_size_kxttst_cs sample_size,
       decode(bitand(h.spare2_kxttst_cs, 2), 2, 'YES', 'NO') global_stats,
       decode(bitand(h.spare2_kxttst_cs, 1), 1, 'YES', 'NO') user_stats,
       null, -- notes
       h.avgcln_kxttst_cs      avg_col_len,
       case when nvl(h.row_cnt_kxttst_cs,0) = 0 then 'NONE'
            when exists(select 1 from x$kxttstehs hg
                        where c.obj# = hg.obj#_kxttst_hs
                          and c.intcol# = hg.intcol#_kxttst_hs
                          and hg.ep_repeat_count_kxttst_hs > 0
                          and rownum < 2) then 'HYBRID'
            when bitand(h.spare2_kxttst_cs, 64) > 0
              then 'TOP-FREQUENCY'
            when (bitand(h.spare2_kxttst_cs, 32) > 0 or
                  h.bucket_cnt_kxttst_cs > 2049 or
                  (h.bucket_cnt_kxttst_cs >= h.distcnt_kxttst_cs
                   and h.density_kxttst_cs*h.bucket_cnt_kxttst_cs < 1))
                then 'FREQUENCY'
            else 'HEIGHT BALANCED'
       end histogram,
       'SESSION'               scope
from x$kxttstecs h, obj$ o, user$ u, col$ c, dba_tables t
where h.obj#_kxttst_cs = o.obj# and
      o.owner# = u.user# and
      o.name = t.table_name and
      u.name = t.owner and
      c.obj# = o.obj# and
      h.intcol#_kxttst_cs = c.intcol#

View Text - HTML Formatted

SELECT OWNER
, TABLE_NAME
, COLUMN_NAME
, NUM_DISTINCT
, LOW_VALUE
, HIGH_VALUE
, DENSITY
, NUM_NULLS
, NUM_BUCKETS
, LAST_ANALYZED
, SAMPLE_SIZE
, GLOBAL_STATS
, USER_STATS
, NOTES
, AVG_COL_LEN
, HISTOGRAM
, 'SHARED' FROM DBA_TAB_COLS_V$ WHERE LAST_ANALYZED IS NOT NULL UNION ALL SELECT /* FIXED TABLE COLUMN STATS */ 'SYS'
, FT.KQFTANAM
, C.KQFCONAM
, H.DISTCNT
, H.LOWVAL
, H.HIVAL
, H.DENSITY
, H.NULL_CNT
, CASE WHEN NVL(H.DISTCNT
, 0) = 0 THEN H.DISTCNT WHEN H.ROW_CNT = 0 THEN 1 WHEN EXISTS(SELECT 1
FROM SYS.HISTGRM$ HG
WHERE C.KQFCOTOB = HG.OBJ#
AND C.KQFCOCNO = HG.INTCOL#
AND HG.EP_REPEAT_COUNT > 0
AND ROWNUM < 2) THEN H.ROW_CNT WHEN (BITAND(H.SPARE2
, 32) > 0 OR H.BUCKET_CNT > 2049 OR (H.BUCKET_CNT >= H.DISTCNT
AND H.DENSITY*H.BUCKET_CNT < 1)) THEN H.ROW_CNT ELSE H.BUCKET_CNT END
, H.TIMESTAMP#
, H.SAMPLE_SIZE
, DECODE(BITAND(H.SPARE2
, 2)
, 2
, 'YES'
, 'NO')
, DECODE(BITAND(H.SPARE2
, 1)
, 1
, 'YES'
, 'NO')
, NULL
, -- NOTES H.AVGCLN
, CASE WHEN NVL(H.ROW_CNT
, 0) = 0 THEN 'NONE' WHEN EXISTS(SELECT 1
FROM SYS.HISTGRM$ HG
WHERE C.KQFCOTOB = HG.OBJ#
AND C.KQFCOCNO = HG.INTCOL#
AND HG.EP_REPEAT_COUNT > 0
AND ROWNUM < 2) THEN 'HYBRID' WHEN (BITAND(H.SPARE2
, 32) > 0 OR H.BUCKET_CNT > 2049 OR (H.BUCKET_CNT >= H.DISTCNT
AND H.DENSITY*H.BUCKET_CNT < 1)) THEN 'FREQUENCY' ELSE 'HEIGHT BALANCED' END
, 'SHARED' FROM SYS.X$KQFTA FT
, SYS.FIXED_OBJ$ FOBJ
, SYS.X$KQFCO C
, SYS.HIST_HEAD$ H WHERE FT.KQFTAOBJ = FOBJ. OBJ#
AND C.KQFCOTOB = FT.KQFTAOBJ
AND H.OBJ# = FT.KQFTAOBJ
AND H.INTCOL# = C.KQFCOCNO /* * IF FOBJ
AND ST ARE NOT IN SYNC (HAPPENS WHEN DB OPEN READ ONLY * AFTER UPGRADE)
, DO NOT DISPLAY STATS. */
AND FT.KQFTAVER = FOBJ.TIMESTAMP - TO_DATE('01-01-1991'
, 'DD-MM-YYYY')
AND H.TIMESTAMP# IS NOT NULL UNION ALL SELECT /* SESSION PRIVATE STATS FOR GTT */ U.NAME OWNER
, O.NAME TABLE_NAME
, C.NAME COLUMN_NAME
, H.DISTCNT_KXTTST_CS NUM_DISTINCT
, H.LOWVAL_KXTTST_CS LOW_VALUE
, H.HIVAL_KXTTST_CS HIGH_VALUE
, H.DENSITY_KXTTST_CS DENSITY
, H.NULL_CNT_KXTTST_CS NUM_NULLS
, CASE WHEN NVL(H.DISTCNT_KXTTST_CS
, 0) = 0 THEN H.DISTCNT_KXTTST_CS WHEN H.ROW_CNT_KXTTST_CS = 0 THEN 1 WHEN EXISTS(SELECT 1
FROM SYS.X$KXTTSTEHS HG
WHERE C.OBJ# = HG.OBJ#_KXTTST_HS
AND C.INTCOL# = HG.INTCOL#_KXTTST_HS
AND HG.EP_REPEAT_COUNT_KXTTST_HS > 0
AND ROWNUM < 2) THEN H.ROW_CNT_KXTTST_CS WHEN BITAND(H.SPARE2_KXTTST_CS
, 64) > 0 THEN H.ROW_CNT_KXTTST_CS WHEN (BITAND(H.SPARE2_KXTTST_CS
, 32) > 0 OR H.BUCKET_CNT_KXTTST_CS > 2049 OR (H.BUCKET_CNT_KXTTST_CS > H.DISTCNT_KXTTST_CS
AND H.ROW_CNT_KXTTST_CS = H.DISTCNT_KXTTST_CS
AND H.DENSITY_KXTTST_CS*H.BUCKET_CNT_KXTTST_CS < 1)) THEN H.ROW_CNT_KXTTST_CS ELSE H.BUCKET_CNT_KXTTST_CS END NUM_BUCKETS
, H.TIMESTAMP#_KXTTST_CS LAST_ANALYZED
, H.SAMPLE_SIZE_KXTTST_CS SAMPLE_SIZE
, DECODE(BITAND(H.SPARE2_KXTTST_CS
, 2)
, 2
, 'YES'
, 'NO') GLOBAL_STATS
, DECODE(BITAND(H.SPARE2_KXTTST_CS
, 1)
, 1
, 'YES'
, 'NO') USER_STATS
, NULL
, -- NOTES H.AVGCLN_KXTTST_CS AVG_COL_LEN
, CASE WHEN NVL(H.ROW_CNT_KXTTST_CS
, 0) = 0 THEN 'NONE' WHEN EXISTS(SELECT 1
FROM X$KXTTSTEHS HG
WHERE C.OBJ# = HG.OBJ#_KXTTST_HS
AND C.INTCOL# = HG.INTCOL#_KXTTST_HS
AND HG.EP_REPEAT_COUNT_KXTTST_HS > 0
AND ROWNUM < 2) THEN 'HYBRID' WHEN BITAND(H.SPARE2_KXTTST_CS
, 64) > 0 THEN 'TOP-FREQUENCY' WHEN (BITAND(H.SPARE2_KXTTST_CS
, 32) > 0 OR H.BUCKET_CNT_KXTTST_CS > 2049 OR (H.BUCKET_CNT_KXTTST_CS >= H.DISTCNT_KXTTST_CS
AND H.DENSITY_KXTTST_CS*H.BUCKET_CNT_KXTTST_CS < 1)) THEN 'FREQUENCY' ELSE 'HEIGHT BALANCED' END HISTOGRAM
, 'SESSION' SCOPE FROM X$KXTTSTECS H
, OBJ$ O
, USER$ U
, COL$ C
, DBA_TABLES T WHERE H.OBJ#_KXTTST_CS = O.OBJ# AND O.OWNER# = U.USER# AND O.NAME = T.TABLE_NAME AND U.NAME = T.OWNER AND C.OBJ# = O.OBJ# AND H.INTCOL#_KXTTST_CS = C.INTCOL#