Вы здесь

Параметры автоматического сбора статистики объектов бд

Системная статистика / System Statistics

select * from sys.aux_stats$;

Don’t collect system stats in Oracle unless you are using Exadata. The default system stats are perfect. /cc Tom Kyte ;)

 

Параметры автоматического сбора статистики объектов бд

12.1.0.2@ SQL> select sname, spare4 from sys.optstat_hist_control$ order by 1;
 
SNAME                          SPARE4
------------------------------ --------------------------------------------------------------------------------
APPROXIMATE_NDV                TRUE
AUTOSTATS_TARGET               AUTO
CASCADE                        DBMS_STATS.AUTO_CASCADE
CONCURRENT                     OFF
DEBUG                          0
DEGREE                         NULL
ENABLE_HYBRID_HISTOGRAMS       3
ENABLE_TOP_FREQ_HISTOGRAMS     3
ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE
GATHER_AUTO                    AFTER_LOAD
GLOBAL_TEMP_TABLE_STATS        SESSION
GRANULARITY                    AUTO
INCREMENTAL                    FALSE
INCREMENTAL_INTERNAL_CONTROL   TRUE
INCREMENTAL_LEVEL              PARTITION
INCREMENTAL_STALENESS         
JOB_OVERHEAD                   -1
JOB_OVERHEAD_PERC              1
METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO
MON_MODS_ALL_UPD_TIME         
NO_INVALIDATE                  DBMS_STATS.AUTO_INVALIDATE
OPTIONS                        GATHER
PUBLISH                        TRUE
SKIP_TIME                     
SPD_RETENTION_WEEKS            53
STALE_PERCENT                  0
STATS_RETENTION               
STAT_CATEGORY                  OBJECT_STATS
SYS_FLAGS                      1
TABLE_CACHED_BLOCKS            1
TRACE                          0
10g
SQL> select
 DBMS_STATS.GET_PARAM ('AUTOSTATS_TARGET'),
 DBMS_STATS.GET_PARAM ('CASCADE'),
 DBMS_STATS.GET_PARAM ('DEGREE'),
 DBMS_STATS.GET_PARAM ('ESTIMATE_PERCENT'),
 DBMS_STATS.GET_PARAM ('METHOD_OPT'),
 DBMS_STATS.GET_PARAM ('NO_INVALIDATE'),
 DBMS_STATS.GET_PARAM ('GRANULARITY')
 from dual;
11g
SQL> select
 DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET'),
 DBMS_STATS.GET_PREFS ('CASCADE'),
 DBMS_STATS.GET_PREFS ('DEGREE'),
 DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT'),
 DBMS_STATS.GET_PREFS ('METHOD_OPT'),
 DBMS_STATS.GET_PREFS ('NO_INVALIDATE'),
 DBMS_STATS.GET_PREFS ('GRANULARITY'),
 DBMS_STATS.GET_PREFS ('PUBLISH'),
 DBMS_STATS.GET_PREFS ('INCREMENTAL'),
 DBMS_STATS.GET_PREFS ('STALE_PERCENT')
 from dual;
12c
SQL> select
 DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET')        as AUTOSTATS_TRGT,
 DBMS_STATS.GET_PREFS ('CASCADE')                 as CASCADE,
 DBMS_STATS.GET_PREFS ('DEGREE')                  as DEGREE,
 DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT')        as ESTIMATE_PRCNT,
 DBMS_STATS.GET_PREFS ('METHOD_OPT')              as METHOD_OPT,
 DBMS_STATS.GET_PREFS ('NO_INVALIDATE')           as NO_INVALIDATE,
 DBMS_STATS.GET_PREFS ('GRANULARITY')             as GRANULARITY,
 DBMS_STATS.GET_PREFS ('PUBLISH')                 as PUBLISH,
 DBMS_STATS.GET_PREFS ('INCREMENTAL')             as INCREMENTAL,
 DBMS_STATS.GET_PREFS ('INCREMENTAL_LEVEL')       as INCREMENTAL_LEVEL,
 DBMS_STATS.GET_PREFS ('INCREMENTAL_STALENESS')   as INCREMENT_STALENESS,
 DBMS_STATS.GET_PREFS ('STALE_PERCENT')           as STALE_PERCENT,
 DBMS_STATS.GET_PREFS ('TABLE_CACHED_BLOCKS')     as TABLE_CACHED_BLOCKS,
 DBMS_STATS.GET_PREFS ('GLOBAL_TEMP_TABLE_STATS') as GLOBAL_TEMP_TABLE_STATS,
 DBMS_STATS.GET_PREFS ('OPTIONS')                 as OPTIONS
 from dual;
11g+ Пользовательские параметры сбора статистики отдельных объектов
SQL> select * from sys.optstat_user_prefs$;
 
OBJ#       PNAME      VALNUM   VALCHAR                    CHGTIME                          SPARE1
---------- ---------- -------- -------------------------- --------------------------------- ----------
803018     METHOD_OPT          FOR ALL COLUMNS SIZE 10    21-JUL-11 13.15.32.791784 +04:00
803016     METHOD_OPT          FOR ALL COLUMNS SIZE 10    21-JUL-11 14.04.21.504976 +04:00
Таблицы с блокированной (процедурой DBMS_STATS.LOCK_TABLE_STATS) статистикой
SQL> select owner, table_name, stattype_locked
from dba_tab_statistics
where  stattype_locked is not null
and owner not in ('SYS','SYSTEM');

Про фиксацию статистики таблиц с частоменяющимся содержимым — сценарии использования блокированной статистики для волатильных таблиц

Процедуры автоматического сбора статистики

10g
select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
 select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB';
 select * from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB';
11g
select * from dba_autotask_task where client_name = 'auto optimizer stats collection';
select * from dba_autotask_client where client_name = 'auto optimizer stats collection';
select * from dba_autotask_client_history where client_name = 'auto optimizer stats collection';
select * from dba_autotask_job_history where client_name = 'auto optimizer stats collection';
select * from dba_autotask_operation where client_name = 'auto optimizer stats collection';

Управление автоматической задачей (Automatic Maintenance Task) сбора статистики:

SQL> exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);
PL/SQL procedure successfully completed
 
SQL> exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);

Интересно, что если при создании бд с помощью dbca или вручную не были разрешены Automatic Maintenance Task— записи в обзоре dba_autotask_task появятся только после выполнения dbms_auto_task_admin.enable, точнее, после наступления следующего интервала выполнения (Maintenance Windows)

Почему может не собираться статистика в Oracle 11g?

С 11.2.0.3 появился параметр:

SQL> @param_ _optimizer_autostats_job
 
NAME                      VALUE   IS_DEF DSC
------------------------- ------- ------ ----------------------------------------
_optimizer_autostats_job  TRUE    TRUE   enable/disable auto stats collection job

— Automatic Optimizer Statistics Collection Maintenance Tasks Does Not Work. (Doc ID 1900018.1)

Параметры автоматического сбора статистики

SQL> select DBMS_STATS.GET_PREFS ('STALE_PERCENT') as "STALE_PERCENT" from dual
  2  /
 
STALE_PERCENT
-------------
10            -- по умолчанию статистика автоматически собирается после изменения 10% строк таблицы
 
SQL> begin DBMS_STATS.set_global_prefs('STALE_PERCENT', 0); end;
  2  /
 
PL/SQL procedure successfully completed
 
SQL> select DBMS_STATS.GET_PREFS ('STALE_PERCENT') as "STALE_PERCENT" from dual
  2  /
 
STALE_PERCENT
-------------
0             -- статистика будет автоматически собираться после изменения ЛЮБОГО кол-ва строк

Мониторинг изменений данных с целью определения кандидатов для сбора статистики

USER | DBA| ALL_TABLES.MONITORING — атрибут мониторинга изменений данных таблицы для автоматического сбора статистики процедурами DBMS_STATS, начиная с с 11g признак deprecated, т.е. все таблицы автоматически мониторятся

USER | DBA| ALL_TAB_MODIFICATIONS — список таблиц, данные которых были модифицированы со времени последнего сбора статистики

Список таблиц со устаревшей (STALE) статистикой:

SQL> declare
2    mystaleobjs dbms_stats.objecttab;
begin
4    dbms_stats.FLUSH_DATABASE_MONITORING_INFO();           -- to flush monitoring info from sga to mon_mods_all$ / ALL_TAB_MODIFICATIONS
5    dbms_stats.gather_schema_stats(ownname => 'SCOTT',
6                                   options => 'LIST STALE',
7                                   objlist => mystaleobjs);
8    for i in 1 .. mystaleobjs.count loop
9      dbms_output.put_line(mystaleobjs(i).objname);
10     end loop;
11   end;
12  /
 
LINES_SUMMARY
 
PL/SQL procedure successfully completed
 
SQL> -- или так, запросом:
select T.OWNER,
       T.TABLE_NAME,
       TM.timestamp,
       sysdate,
       round(((TM.inserts + TM.updates + TM.deletes) /
             decode(t.NUM_ROWS, 0, (TM.inserts + TM.updates + TM.deletes), t.NUM_ROWS)
             ) * 100
             ,1) || ' %' AS CHANGE_PCT
  from DBA_TAB_MODIFICATIONS TM, dba_tables T
 where TM.table_owner = T.OWNER
   and T.OWNER = 'OWNER_NAME'
   and TM.table_name = T.TABLE_NAME
   and (TM.inserts + TM.updates + TM.deletes) > 0
   and ((TM.inserts + TM.updates + TM.deletes) /
       decode(t.NUM_ROWS, 0, (TM.inserts + TM.updates + TM.deletes), t.NUM_ROWS)) * 100
       >
       DBMS_STATS.GET_PREFS('STALE_PERCENT', T.OWNER, T.TABLE_NAME)
 order by ((TM.inserts + TM.updates + TM.deletes) /
          decode(t.NUM_ROWS, 0, (TM.inserts + TM.updates + TM.deletes), t.NUM_ROWS)) desc
/
 
OWNER TABLE_NAME       TIMESTAMP   SYSDATE     CHANGE_PCT
----- ---------------- ----------- ----------- ----------
SCOTT LINES_SUMMARY    02.04.2012  02.04.2012  100 %

Cкрытые столбцы (hidden columns) и выражения (expression), статистика по которым использовуется CBO

10g Function based indexes в статусе unusable

Элегантный способ создания «виртуальных столбцов» со статистикой и гистограммами для версий до 11g — Jonathan Lewis «Virtual Columns revisited»:

SQL> create index SH.IDX_SALES_FBI on SH.SALES(round(AMOUNT_SOLD,-2), AMOUNT_SOLD/QUANTITY_SOLD) unusable;
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname => 'SH', tabname => 'SALES', method_opt => 'for all hidden columns size skewonly', cascade => false);
 
PL/SQL procedure successfully completed.
 
SQL> select column_name, num_distinct, low_value, high_value, HISTOGRAM
from dba_tab_col_statistics
where table_name = 'SALES' and column_name like 'SYS%$';
 
COLUMN_NAME    NUM_DISTINCT LOW_VALUE HIGH_VALUE HISTOGRAM
-------------- ------------ --------- ---------- ---------------
SYS_NC00011$            672      C107     C21254 HEIGHT BALANCED
SYS_NC00010$             18        80       C213       FREQUENCY
 
SQL> select column_name, count(*) as bucket_count from dba_tab_histograms
where table_name = 'SALES' and column_name like 'SYS%$'
group by column_name;
 
COLUMN_NAME          BUCKET_COUNT
-------------------- ------------
SYS_NC00010$                   18
SYS_NC00011$                  105
11g+
Extended statistics:
SQL> select dbms_stats.create_extended_stats('scott','emp','(ename,sal)') HIDDEN_COLUMN from dual;
 
HIDDEN_COLUMN
------------------------------
SYS_STUA11ZDTGW$SYV6W40D3EV5X5
 
SQL> select table_name, column_name, hidden_column, virtual_column from dba_tab_cols
where table_name = 'EMP' and column_name = 'SYS_STUA11ZDTGW$SYV6W40D3EV5X5';
 
TABLE_NAME                     COLUMN_NAME                    HID VIR
------------------------------ ------------------------------ --- ---
EMP                            SYS_STUA11ZDTGW$SYV6W40D3EV5X5 YES YES

или используя DBMS_STATS.GATHER_TABLE_STATS:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (prod_id + cust_id)');
 
PL/SQL procedure successfully completed.
 
SQL> select table_name, column_name, hidden_column, virtual_column from dba_tab_cols
where table_name = 'SALES' and column_name like 'SYS%';
 
TABLE_NAME                     COLUMN_NAME                    HID VIR
------------------------------ ------------------------------ --- ---
SALES                          SYS_STU4MNAVU7LCB$KQOBWOQ29#W4 YES YES

Информация о расширениях статистики/группах столбцов

SQL> select * from dba_stat_extensions where owner in ('SH','SCOTT');
 
OWNER      TABLE_NAME   EXTENSION_NAME                 EXTENSION                                CREATOR    DROPPABLE
---------- ------------ ------------------------------ ---------------------------------------- ---------- ----------
SCOTT      EMP          SYS_STUA11ZDTGW$SYV6W40D3EV5X5 ("ENAME","SAL")                          USER       YES
SH         SALES        SYS_STU4MNAVU7LCB$KQOBWOQ29#W4 ("PROD_ID"+"CUST_ID")                    USER       YES
SH         SALES        SYS_NC00011$                   ("AMOUNT_SOLD"/"QUANTITY_SOLD")          SYSTEM     NO
SH         SALES        SYS_NC00010$                   (ROUND("AMOUNT_SOLD",(-2)))              SYSTEM     NO

Какая extended statistics рекомендуется Oracle при определённой нагрузке — на примере создания рекомендованной расширенной статистики, рекомендуемой для конкретного запроса sql_id 8ypggjw7dz6mv:

SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_cf');
 
PL/SQL procedure successfully completed
 
SQL> -- Наполнение SQL Tuning Set конкретным содержимым / курсором
SQL> DECLARE
       cur DBMS_SQLTUNE.SQLSET_CURSOR;
     BEGIN
       OPEN cur FOR
         SELECT VALUE(P)
           FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(basic_filter => 'sql_id = ''8ypggjw7dz6mv''')) P;
       DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_cf', populate_cursor => cur);
     END;
     /
 
PL/SQL procedure successfully completed
 
SQL> -- Просмотр содержимого
SQL> SELECT * FROM TABLE(dbms_xplan.display_sqlset('my_cf','8ypggjw7dz6mv'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL Tuning Set Name: my_cf
SQL Tuning Set Owner: SCOTT
SQL_ID: 8ypggjw7dz6mv
SQL Text: select place_id, page_id, dt, sum(hits) hits,
...
--------------------------------------------------------------------------------
 
Plan hash value: 585681291
 
----------------------------------------------------------------------
| Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------
...
 
SQL> -- Запуск мониторинта SQL Set на 100 секунд
SQL> exec dbms_stats.seed_col_usage(sqlset_name => 'my_cf', owner_name => 'SCOTT', time_limit => 100);
 
PL/SQL procedure successfully completed
 
SQL> -- Выполнение запроса sql_id 8ypggjw7dz6mv
SQL> select place_id, page_id, dt, sum(hits) hits,
...
 
SQL> -- Рекомендации по использованию групп столбцов
SQL> select o.object_name, u.cols, u.timestamp, u.flags from col_group_usage$ u, dba_objects o where u.obj# = o.object_id;
 
OBJECT_NAME                  COLS   TIMESTAMP        FLAGS
---------------------------- ------ ----------- ----------
...
T_BUFFER                     1,2    23.05.2013           2
T_BUFFER                     1,2,3  23.05.2013           4
T_BUFFER                     2,3    23.05.2013           4
...
 
SQL> select dbms_stats.report_col_usage('SCOTT','T_BUFFER') from dual;
 
###############################################################################
 
COLUMN USAGE REPORT FOR SCOTT.T_BUFFER
......................................................
 
1. BUCKS                               : EQ RANGE EQ_JOIN NONEQ_JOIN
2. DT                                  : EQ RANGE EQ_JOIN NONEQ_JOIN
3. HITS                                : EQ RANGE NULL
4. OBJECT_TYPE                         : EQ
5. PAGE_ID                             : EQ EQ_JOIN
6. PLACE_ID                            : EQ EQ_JOIN
7. SOURCE_ID                           : EQ NULL
8. TEXT                                : EQ NULL
9. TYPE                                : EQ RANGE
10. (PLACE_ID, PAGE_ID)                : JOIN
11. (PAGE_ID, DT)                      : GROUP_BY
12. (PLACE_ID, PAGE_ID, DT)            : GROUP_BY
###############################################################################
 
SQL> -- Создание расширенной статистики
SQL> select dbms_stats.create_extended_stats('SCOTT','T_BUFFER') HIDDEN_COLUMN from dual;
...
 
SQL> -- Проверка
SQL> select * from dba_stat_extensions where table_name = 'T_BUFFER';
 
OWNER  TABLE_NAME                     EXTENSION_NAME                 EXTENSION   CREATOR DROPPABLE
------ ----------- --------------------------------- --------------------------- ------- ---------
SCOTT     T_BUFFER    SYS_STUJ267CNC7INAPEZ3B70QJYJV ("PLACE_ID","PAGE_ID")      USER    YES
SCOTT     T_BUFFER    SYS_STU0#QXKYAPUGOL##CAX8QIO0V ("PAGE_ID","DT")            USER    YES
SCOTT     T_BUFFER    SYS_STU53CCDSV4NF9Y0SMRU325JN5 ("PLACE_ID"