达梦统计信息收集情况检查

查询达梦某个对象上是否有统计信息

select id,T_TOTAL,N_SMAPLE,N_DISTINCT,N_NULL,BLEVEL,N_LEAF_PAGES,N_LEAF_USED_PAGES,LAST_GATHERED from sysstats where id IN (select id from sysobjects where upper(name)=upper('&objname'));

可能有系统对象，可以增加过滤条件

整体收集情况粗看

select a.object_name,a.owner,a.object_type, T_TOTAL,b.last_gathered from dba_objects a,SYSSTATS b where a.object_id = b.id and b.t_flag<>'C' --and a.OWNER = '用户名'
order by b.last_gathered limit 150;

看各列统计信息

--列统计信息
select c.name col_name,s.* from 
(select b.name tbl_name, n_distinct NUM_DISTINCT,
 n_null NUM_NULLS, n_leaf_pages LEAF_PAGES ,BLEVEL,t_total NUM_ROWS, N_SAMPLE SAMPLE_CNT,last_gathered, n_buckets ,col_avg_len,a.id,a.colid
from sysstats a,sysobjects b where a.id = b.id and b.name ='T1'   AND a.t_flag='C' ) s,
(select * from syscolumns where id IN (select id from sysobjects where name ='T1'))c
where c.colid=s.colid and c.id=s.id;

想看最大最小值，请参考

SELECT USR.NAME AS OWNER_NAME
      ,SCH.NAME AS SCHEMA_NAME
      ,TAB.NAME AS TABLE_NAME
      ,COL.NAME AS COLUMN_NAME
      ,COL.TYPE$ AS COLUMN_TYPE
      ,COL.COLID AS COLUMN_ID
      ,CASE COL.TYPE$ 
         WHEN 'NUMBER'    THEN ROUND(CAST(SF_BIN_GET_DOUBLE(S.V_MIN,0) AS NUMBER),COL.SCALE)
         WHEN 'INT'       THEN SF_BIN_GET_INT(S.V_MIN,0)
         WHEN 'INTEGER'   THEN SF_BIN_GET_INT(S.V_MIN,0)
         WHEN 'BIGINT'    THEN SF_BIN_GET_BIGINT(S.V_MIN,0)
         WHEN 'SMALLINT'  THEN SF_BIN_GET_SMALLINT(S.V_MIN,0)
         WHEN 'TINYINT'   THEN SF_BIN_GET_TINYINT(S.V_MIN,0)
         WHEN 'CHAR'      THEN BINTOCHAR2(S.V_MIN)
         WHEN 'NCHAR'     THEN BINTOCHAR2(S.V_MIN)
         WHEN 'NVARCHAR'  THEN BINTOCHAR2(S.V_MIN)
         WHEN 'NVARCHAR2' THEN BINTOCHAR2(S.V_MIN)
         WHEN 'VARCHAR2'  THEN BINTOCHAR2(S.V_MIN) 
         WHEN 'VARCHAR'   THEN BINTOCHAR2(S.V_MIN)
         WHEN 'VARBINARY' THEN NVL2(S.V_MIN,'0x'||RAWTOHEX(SUBSTRBLB(S.V_MIN,5,SF_BIN_GET_INT(S.V_MIN,0))),NULL)
         WHEN 'DATE'      THEN TO_CHAR(BINTODATE(S.V_MIN),'yyyy-mm-dd')
         WHEN 'DATETIME'  THEN TO_CHAR(BINTODATE(S.V_MIN),'yyyy-mm-dd hh24:mi:ss')
         WHEN 'DATETIME WITH TIME ZONE'      THEN TO_CHAR(BINTODATE(S.V_MIN),'yyyy-mm-dd hh24:mi:ss TZH:TZM') 
         WHEN 'TIMESTAMP' THEN TO_CHAR(BINTODATE(S.V_MIN),'yyyy-mm-dd hh24:mi:ss.ff6')
		 WHEN 'DEC' THEN SF_BIN_GET_INT(S.V_MIN,0)
         ELSE RAWTOHEX(S.V_MIN)
        END AS MIN_VAL
      ,CASE COL.TYPE$ 
         WHEN 'NUMBER'    THEN ROUND(CAST(SF_BIN_GET_DOUBLE(S.V_MAX,0) AS NUMBER),COL.SCALE)
         WHEN 'INT'       THEN SF_BIN_GET_INT(S.V_MAX,0)
         WHEN 'INTEGER'   THEN SF_BIN_GET_INT(S.V_MAX,0)
         WHEN 'BIGINT'    THEN SF_BIN_GET_BIGINT(S.V_MAX,0)
         WHEN 'SMALLINT'  THEN SF_BIN_GET_SMALLINT(S.V_MAX,0)
         WHEN 'TINYINT'   THEN SF_BIN_GET_TINYINT(S.V_MAX,0)
         WHEN 'CHAR'      THEN BINTOCHAR2(S.V_MAX)
         WHEN 'NCHAR'     THEN BINTOCHAR2(S.V_MAX)
         WHEN 'NVARCHAR'  THEN BINTOCHAR2(S.V_MAX)
         WHEN 'NVARCHAR2' THEN BINTOCHAR2(S.V_MAX)
         WHEN 'VARCHAR2'  THEN BINTOCHAR2(S.V_MAX) 
         WHEN 'VARCHAR'   THEN BINTOCHAR2(S.V_MAX)
         WHEN 'VARBINARY' THEN NVL2(S.V_MAX,'0x'||RAWTOHEX(SUBSTRBLB(S.V_MAX,5,SF_BIN_GET_INT(S.V_MAX,0))),NULL)  
         WHEN 'DATE'      THEN TO_CHAR(BINTODATE(S.V_MAX),'yyyy-mm-dd')
         WHEN 'DATETIME'  THEN TO_CHAR(BINTODATE(S.V_MAX),'yyyy-mm-dd hh24:mi:ss')
         WHEN 'DATETIME WITH TIME ZONE'      THEN TO_CHAR(BINTODATE(S.V_MAX),'yyyy-mm-dd hh24:mi:ss TZH:TZM') 
         WHEN 'TIMESTAMP' THEN TO_CHAR(BINTODATE(S.V_MAX),'yyyy-mm-dd hh24:mi:ss.ff6')
		 WHEN 'DEC' THEN SF_BIN_GET_INT(S.V_MAX,0)
         ELSE RAWTOHEX(S.V_MAX)
        END AS MAX_VAL
      ,S.T_TOTAL
      ,S.N_SAMPLE
      ,S.N_DISTINCT
      ,S.N_NULL
      ,S.COL_AVG_LEN
      ,S.LAST_GATHERED
      --,S.DATA
  FROM SYSSTATS S
      ,SYSOBJECTS TAB
      ,SYSCOLUMNS COL
      ,SYSOBJECTS SCH
      ,SYSOBJECTS USR
 WHERE S.ID = TAB.ID
   AND S.T_FLAG = 'C'
   AND COL.ID = S.ID
   AND COL.COLID = S.COLID
   AND SCH.ID = TAB.SCHID
   AND SCH.TYPE$ = 'SCH'
   AND TAB.TYPE$ = 'SCHOBJ'
   AND TAB.SUBTYPE$ IN ('UTAB','STAB')
   AND USR.TYPE$ = 'UR'
   AND USR.ID = SCH.PID
   AND SCH.NAME = 'TEST'
   AND TAB.NAME = 'T1'
 ORDER BY 1,2,3,6;

比较费劲。