Системная статистика (System Statistics)
select * from sys.aux_stats$;
Параметры автоматического сбора статистики объектов бд
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?
Мониторинг изменений данных с целью определения кандидатов для сбора статистики
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
Ручное управление статистикой
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
Прочее
Параметр пакета DBMS_STATS.NO_INVALIDATE
Doug Burns: STATISTICS ON PARTITIONED OBJECTS – про сбор (параметр GRANULARITY пакета DBMS_STATS) и использование оптимизатором статистики объектов/партиций/субпартиций и партицированных инддексов
