收集4T数据库的统计信息耗时多久？

开心时刻

一个传说中的库特别慢
看了看等待，发现Failed Logon Delay事件居前
其实不重要，非要查个明白的话，那就

SELECT username, to_char(timestamp,'mm-dd hh24:mi:ss') datetime, action_name, priv_used, obj_name, ses_actions
FROM sys.dba_audit_trail
WHERE timestamp>sysdate-1AND action_name = 'LOGON'
ORDER BY username;

或
SELECT audit_type,
         os_username,
         userhost,
         terminal,
         dbusername,
         dbproxy_username,
         client_program_name,
         to_char(event_timestamp,
        'mm-dd hh24:mi:ss') datetime, action_name, return_code, unified_audit_policies
FROM unified_audit_trail
WHERE event_timestamp>sysdate-1
        AND action_name = 'LOGON'
        AND return_code is NOT null
ORDER BY dbusername;

或者

col ntimestamp# for a30 heading "Timestamp"
col userid for a6 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a10 heading "OS User"
col comment$text for a80 heading "Details"

select ntimestamp#, userid, userhost, spare1, comment$text,returncode
from sys.aud$ where returncode=1017 or returncode=28000;

如果关闭审计，那么就不会看到真凶，解决办法可以直接设置初始化参数：

event="28401 trace name context forever, level 1" # disable logon delay.

前戏结束（详见 Doc ID 2724913.1）

收集统计信息技巧
如果因为某种原因（报错、提高效率。。。）收集全库（用户）统计信息时排除掉其他用户的表，可以这样：

EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');
EXEC DBMS_STATS.gather_database_stats;
EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('owner name', 'table name');

正题来了，收集方法如下：

exec dbms_stats.gather_database_stats(estimate_percent => 20, degree => 32, cascade => TRUE, gather_sys=> FALSE);

对参数不了解可以

select dbms_stats.get_prefs('CASCADE') pref_cascade from dual;
select dbms_stats.get_prefs('estimate_percent') pref_cascade from dual;

随着产品成熟，后期会倾向于越来越简单
exec dbms_stats.gather_database_stats 即可

深入一下：默认采样百分比多少，我这可是4T大库，要等多久？
终于可以引用这个：It depends on

先说明百分比，这个比较清晰：

https://blogs.oracle.com/optimizer/post/how-does-auto-sample-size-work-in-oracle-database-12c

so，具体要等多久，大概4小时吧。

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN DEFAULT FALSE,
   method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
   granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab VARCHAR2 DEFAULT NULL,
   statid VARCHAR2 DEFAULT NULL,
   options VARCHAR2 DEFAULT 'GATHER',
   objlist          OUT      ObjectTab,
   statown VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN DEFAULT TRUE,
   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN DEFAULT FALSE,
   method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
   granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab VARCHAR2 DEFAULT NULL,
   statid VARCHAR2 DEFAULT NULL,
   options VARCHAR2 DEFAULT 'GATHER',
   statown VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN DEFAULT TRUE,
   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

友情提示：

Good。