Системная статистика / 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 %
Мониторинг использования объектов БД
12cR2 new index usage tracking / DBA_INDEX_USAGE :
SQL> @param+ _iut_ NAME SESS_VALUE INST_VALUE Default Value IS_MODIFIED IS_SESS_MOD IS_SYS_MOD IS_PDB_MODIFIABLE IS_ADJ DSC --------------------------- ---------- ---------- ------------- ----------- ----------- ---------- ----------------- -------- ------------------------------------------- _iut_enable TRUE TRUE TRUE FALSE FALSE IMMEDIATE TRUE FALSE Control Index usage tracking _iut_max_entries 30000 30000 TRUE FALSE FALSE IMMEDIATE TRUE FALSE Maximum Index entries to be tracked _iut_stat_collection_type SAMPLED SAMPLED TRUE FALSE TRUE IMMEDIATE TRUE FALSE Specify Index usage stat collection type SQL> @pvalid_ _iut_stat_collection_type PVALID_NAME PVALID_VALUE PVALID_ --------------------------- ------------------------------ ------- _iut_stat_collection_type SAMPLED DEFAULT _iut_stat_collection_type ALL
12cR2 Index Usage Tracking Manual Flushing:
SQL> oradebug call keiut_flush_all
Relationship between Table Monitoring and STATISTICS_LEVEL Parameter (Doc ID 252597.1)
By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled
…
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. This information on «changes made» is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). The data dictionary information is made visible through the views: DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS.
возможно, имеет смысл заказать патч/запатчить:
11.2:Incorrect values in dba_tab_modifications insert-select (Doc ID 2714897.1)
CAUSE This is due Bug 20479545 - INCORRECT VALUES IN DBA_TAB_MODIFICATIONS INSERT-SELECT SOLUTION Apply the fix:20479545, The bug is fixed in 20.1 on-wards
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)
DBMS_STATS.DELETE_TABLE_STATS Not Deleting Statistics On a Global Temporary Table (Doc ID 2312836.1) как следствие Session-Private Statistics for GTT Oracle Database 12c
DBMS_STATS Tracing
dbms_stats.set_global_prefs(‘trace’,:trace_flags)
Following are the possible values for the trace flags:
1 = use dbms_output.put_line instead of writing into trace file
2 = enable dbms_stat trace only at session level
4 = trace table stats
8 = trace index stats
16 = trace column stats
32 = trace auto stats – logs to sys.stats_target$_log
64 = trace scaling
128 = dump backtrace on error
256 = dubious stats detection
512 = auto stats job
1024 = parallel execution tracing
2048 = print query before execution
4096 = partition prune tracing
8192 = trace stat differences
16384 = trace extended column stats gathering
32768 = trace approximate NDV (number distinct values) gathering
Например, to dump a trace on stats job error:
exec dbms_stats.set_global_prefs('trace',to_char(512+128))
exec dbms_stats.set_global_prefs('trace', 65532)
— для более полного трейса
Прочее
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
Tim Hall. Pipelined Table Functions#cardinality — перевод в журнале ФОРС / оригинал
Mohamed Houri. Historical column histogram: WRI$_OPTSTAT_HISTHEAD_HISTORY, DBMS_STATS.REPORT_STATS_OPERATIONS / REPORT_SINGLE_STATS_OPERATION
Oracle mechanics 
Оставьте комментарий