Как собрать статистику по таблице oracle

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

По умолчанию Oracle автоматически собирает статистику об объектах базы. Он дает большие возможности для управления расписанием этих действий и ручной её актуализации в случае необходимости, но иногда, обычно для опытов, требуется принудительно сказать, сколько есть строк в таблицы, как часто встречается какое-то значение, сколько есть уникальных значений. Такую возможность дает пакет dbms_stats.

Более подробное описание процедур можно найти на сайте Oracle. Лучше сразу искать документацию на целевую версию, в dbms_stats периодически появляются новые возможности, дополнительные параметры и поля.

APPS-ORACLE.RU

Если в базе данных имеются таблицы, которые часто обновляются, то частый сбор статистики может негативно повлиять на производительность базы данных. Для того, чтоб исключить объекты из автоматического или любого другого сбора статистики можно «закрепить» ее статистику:

Теперь, по этой таблице невозможно будет собрать статистику ни автоматически, ни вручную:

Снять блокировку статистики:

Посмотреть данные о сборе статистики можно в таблице DBA_TAB_STATISTICS

Собрать статистику для индекса

Статистика на секцию

Похожие записи:

Данное утверждение — «Если в базе данных имеются таблицы, которые часто обновляются, то частый сбор статистики может негативно повлиять на производительность базы данных.» — вообще говоря, неверно. Именно для таких таблиц важно поддерживать статистику в актуальном состоянии. Если, разумеется, запросы не используют RBO — что в современных версиях невозможно ввиду отсутствия RBO (вместо него начиная с 10g стоит заглушка).

Блокировку сбора статистики целесообразно выполнять в приложениях типа OEBS — с большим (реально большим) количеством неизменных справочников. И при этом не стоит забывать про существующие в БД по умолчанию задания автоматического обновления статистики.

Oracle mechanics

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

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

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

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

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

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

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

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

Мониторинг использования объектов БД

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.

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

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

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

Extended statistics:

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

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

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

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

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

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

Как предотвратить (или, наоборот, стимулировать) создание гистограмм для конкретного столбца на версиях с использованием 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 на уровне системы — на примере отключения создания гистограмм для всех таблиц:

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

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

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:

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

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:

— для более полного трейса

Прочее

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

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

Gather stats in Oracle : Schema,Tables and Indexes

In my previous articles i have given idea about the performance tuning techniques,Indexes in sql.In any performance tuning technique we require to create multiple indexes on table. After creating the indexes the stats of objects will change.So after any of the operations to see the good results in SQL performance we require to gather stats in Oracle . After gathering the statastics it will fine tune your query and it will fetch records fast.

How to gather stats in Oracle?

To gather stats in oracle we require to use the DBMS_STATS package.It will collect the statistics in parallel with collecting the global statistics for partitioned objects.The DBMS_STATS package specialy used only for optimizer statistics. As i explained in first paragraph the dbms_stats is very vital for good SQL performance. We require to gather the stats before adjusting or setting up any optimizer parameters in oracle.

Optimization is process where SQL can run in efficient time.

The less the query cost the execution time of query is fast. We must have to gather the statistics on regular basis for database object to give the best information to oracle optimizer to run queries in best possible time.Using the analyze statement is traditional way of checking the cost of query. But now a days to gather stats in oracle we need to use DBMS_STATS package.

Usages of DBMS_STATS Package :

1.To modify stats

3.To delete stats

4.To export or import stats

I would like to start with syntax of DBMS_STATS package. As this is package and we need to use multiple system generated procedures to gather the stats. I would like to start with gathering stas for Schema.We require to use GATHER_SCHEMA_STATS procedure of DBMS_STATS package.

Syntax for gathering stats for schema level:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);

Scenario 1 : Gather stats for Schema

If we have applied or recreated indexes to multiple data tables then we require to gather stats at schema level.

Scenario 2 : Gather Stats Percent-wise in schema :

exec dbms_stats.gather_schema_stats(ownname=>’Amit_Schema’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(ownname => ‘Amit_Schema’, estimate_percent => 50);

If we gather stats for a table, column, or index, if the data dictionary already containing statistics for the object, then Oracle will update the existing statistics. Oracle will save the older stats to reuse that again.

When you gather the statistics at object level oracle optimizer can re-parse the explain plan of query and will automatically chooses the explain plan of the query.

Gathering stats for table :

We can collect the stats in table level. If user creates the indexes or use any partitioning technique after that we require to gather stats. We can gather stats using the gather_table_stats procedure of dbms_stats package.

Example 1 :

It will gather the stats Employee table in Amit_Schema schema.

Example 2 :

Query :

exec dbms_stats.gather_table_stats (
ownname => ‘Amit_Schema’,
tabname => ‘Employee’,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => ‘for all columns size auto’,
cascade => true,
degree => 5
)
/
PL/SQL procedure successfully completed.

Gathering Stats for index :

Gathering index stats are also important. We have already shown the way to gather stats of index with table. If we require to gather stats for only index then following syntax is useful.

Syntax :

Deleting schema Stats :

We can delete the stats of schema using following statement :

The above statement will delete the stats of Amit_Schema.

Gathering stats other examples :

These are different procedures of DBMS_STATS package.

How to gather stats for partitioned schema object :

We can gather stats for partitioned schema object also. The partitioned schema object may contain multiple set of statistics. We can gather the stat using the gathering global statistics.So we require to collect global statistics of the schema.

I hope you get clear idea about the gather stats in oracle with examples. If you like this article or if you have any questions kindly comment in comments section.

12 Replies to “Gather stats in Oracle : Schema,Tables and Indexes”

great work buddy….
could you please provide info about stale stats also. it would be really helpfull.

Thanks for your good words.. In other article i will give information about stale stats.

What oracle version this works on?

For any version of oracle this syntax is been used for gathering stats. I have tested it on 11 G and 12 c.

Kindly let me know if you are facing any issues.

thanks for info
really good

Thanks luis foe good words!

Great posting ��������thanks a lot for explaining in detail , no matter how many years years of experience we posses it’s worth to refresh once the concepts and this also helps in better preparing for interviews.

now a days in google there are tons of content but always a challenge to pick the best content ,so this is best useful content totally useful and learned a lot

keep the good work ����
Thanks Again

Thanks Naresh! Appreciated!

SQL> BEGIN dbms_stats.gather_index_stats(‘t1′,’i1’); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX “T1”.”I1″, insufficient privileges or does
not exist

It should be “exec dbms_stats.gather_index_stats(‘owner’, ‘Index name’);”

Please connect to your DBA for giving you access of gather stats. You don’t have privileges’.