Статистика бд

Системная статистика / 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 ;)

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

SQL> select sname, spare4 from sys.optstat_hist_control$;

SNAME                          SPARE4
------------------------------ ----------------------------------------
SKIP_TIME
STATS_RETENTION
TRACE                          0
DEBUG                          0
SYS_FLAGS                      1
CASCADE                        DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                         32768
METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE                  DBMS_STATS.AUTO_INVALIDATE
GRANULARITY                    AUTO
AUTOSTATS_TARGET               AUTO

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;

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;
3  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
2  from dba_tab_col_statistics
3  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
2  where table_name = 'SALES' and column_name like 'SYS%$'
3  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
2  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
2  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","PAGE_ID","DT") USER    YES

SQL> exec dbms_stats.gather_table_stats('SCOTT','T_BUFFER',method_opt => 'for all hidden columns size 254');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct, num_nulls, global_stats, histogram from dba_tab_col_statistics
  2   where table_name = 'T_BUFFER' and column_name like 'SYS\_%' escape '\';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS GLOBAL_STATS HISTOGRAM
------------------------------ ------------ ---------- ------------ ---------------
SYS_STU53CCDSV4NF9Y0SMRU325JN5     22149120          0 YES          HEIGHT BALANCED
SYS_STU0#QXKYAPUGOL##CAX8QIO0V        75256          0 YES          HEIGHT BALANCED
SYS_STUJ267CNC7INAPEZ3B70QJYJV        72784          0 YES          HEIGHT BALANCED

Удаление расширенной статистики для группы столбцов или выражений:

SQL> exec dbms_stats.drop_extended_stats('SH', 'SALES', '(prod_id,cust_id)');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.drop_extended_stats('SH', 'SALES', '(prod_id + cust_id)');

Ручное управление статистикой

Handling statistics for a column with ever increasing or decreasing values… [ID 877645.1] — процедура установки LOW_VALUE / HIGH_VALUE с помощью dbms_stats.set_column_stats для таблицы, значение столбцов в которой изменяются часто (чаще, чем может собираться статистика)

Пример создания frequency histogram — Wolfgang Breitling «Active Statistics» :

DECLARE
SREC DBMS_STATS.STATREC;
CHVALS DBMS_STATS.CHARARRAY;
BEGIN
SREC.EAVS := 0;
SREC.CHVALS := NULL;
SREC.EPC := 5;
CHVALS := DBMS_STATS.CHARARRAY('D','H','I','N','Z');
SREC.BKVALS := DBMS_STATS.NUMARRAY(2852104,22,11414,5993,28);
DBMS_STATS.PREPARE_COLUMN_VALUES (SREC,CHVALS);
DBMS_STATS.SET_COLUMN_STATS(…);
END;

для таблицы с неравномерным распределением кол-ва строк по значениям стобца (skewed column)

VALUE ROW_COUNT
    D   2852104
    H        22
    I     11414
    N      5993
    Z        28

Пример ручного создания height balanced histogram — Jonathan Lewis «Fake Histograms»

Как предотвратить (или, наоборот, стимулировать) создание гистограмм для конкретного столбца на версиях с использованием dbms_stats.lock_table_stats, dbms_stats.gather_table_stats( …, force => true) в 10g и dbms_stats.set_table_prefs в 11g — Optimizer development team «How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?»

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

Установка параметра METHOD_OPT для процедур пакета DBMS_STATS на уровне системы — на примере отключения создания гистограмм для всех таблиц:

SQL> exec dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE 1');

Включение обязательного создания гистограмм для отдельного столбца на уровне таблицы (Oracle 11+):

11.2|SQL> exec dbms_stats.set_table_prefs('AR', 'AR_RECEIVABLE_APPLICATIONS_ALL','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 CASH_RECEIPT_ID');

PL/SQL procedure successfully completed

SQL> select * from dba_tab_stat_prefs where table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL';

OWNER  TABLE_NAME                     PREFERENCE_NAME  PREFERENCE_VALUE
------ ------------------------------ ---------------- ---------------------------------------------------------------
AR     AR_RECEIVABLE_APPLICATIONS_ALL METHOD_OPT       FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 CASH_RECEIPT_ID

SQL> exec DBMS_STATS.set_table_prefs('SCOTT','EMP','STALE_PERCENT','0.1');

PL/SQL procedure successfully completed

SQL> select * from dba_tab_stat_prefs where table_name = 'EMP';

OWNER  TABLE_NAME  PREFERENCE_NAME  PREFERENCE_VALUE
------ ----------- ---------------- ----------------
SCOTT  EMP         STALE_PERCENT    0.1

Новое в статистике: о новом параметре TABLE_CACHED_BLOCKS для расчёта Index Clustering Factor и ограничении действия процедуры DBMS_STATS.SET_SCHEMA_PREFS только на объекты схемы, существующие на момент запуска для версий 11.2.0.2+

Параметр пакета DBMS_STATS.NO_INVALIDATE

Статистика временных таблиц / Global Temporary Table

… no statistics are gathered about them than what can be collected on the fly — т.е. автоматически статистика GTT не собирается, однако в DBA_TAB_MODIFICATIONS временные таблицы попадать могут, например, в бд OEBS:

SQL> select *
  2    from dba_tab_modifications m
  3    join dba_tables t
  4      on m.table_owner = t.OWNER
  5     and m.table_name = t.TABLE_NAME
  6   where t.TEMPORARY = 'Y'
  7  /
 
INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS STATUS   NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED TEMPORARY GLOBAL_STATS USER_STATS DURATION        SEGMENT_CREATED
------- ---------- ---------- ----------- --------- ------------- ------ ---------- ---------- ----------- ----------- ------------- --------- ------------ ---------- --------------- ---------------
      0          0          0 13.11.2011  NO                    0 VALID           0          0           0           0 24.09.2011 22 Y         YES          NO         SYS$SESSION     YES
      0          0          0 29.09.2011  NO                    0 VALID         100         10         100        2000 24.09.2011 16 Y         YES          YES        SYS$TRANSACTION YES
      0          0          0 04.07.2013  NO                    0 VALID    10000000     100000         256        2000 07.08.2012 17 Y         YES          YES        SYS$TRANSACTION YES

— при этом изменений в таблицах не фиксируется INSERTS = UPDATES = DELETES = 0, плюс попали временные таблицы либо с собранной (USER_STATS=NO), либо с искусственно установленной статистикой (USER_STATS=YES)

Прочее

How to copy partition stats? — методы копирования / дублирования статистики партиций для версий < 10.2.0.4, до появления процедуры DBMS_STATS.COPY_TABLE_STATS

PL/SQL процедура переноса статистики таблицы/схемы с использованием dbms_stats.export_…/dbms_stats.import_… через db link (например, для синхронизации тестовой среды с production)

Doug Burns: STATISTICS ON PARTITIONED OBJECTS — про сбор (параметр GRANULARITY пакета DBMS_STATS) и использование оптимизатором статистики объектов/партиций/субпартиций и партицированных инддексов

Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1) — правильный сбор статистики для приложения OEBS