Системная статистика / System Statistics
select * from sys.aux_stats$;
Параметры автоматического сбора статистики объектов бд
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; 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
How does AUTO_SAMPLE_SIZE work in Oracle Database 11g[+]?:
Summary:
New AUTO_SAMPLE_SIZE algorithm does a full table scan to gather basic column statistics
…
Статистика временных таблиц / 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
Doug Burns: STATISTICS ON PARTITIONED OBJECTS — про сбор (параметр GRANULARITY пакета DBMS_STATS) и использование оптимизатором статистики объектов/партиций/субпартиций и партицированных инддексов
Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1) — правильный сбор статистики для приложения OEBS
Добавить комментарий