Очередная небольшая революция - переход на Oracle

[fed]

Кстати - в продолжение давней дискуссии на которую тут ссылаются - нашел интересную статью по поводу того, как работать со статистикой при использовании FBI:
http://richardfoote.wordpress.com/20...-no-surprises/

[Андре]

Цитата:

Сообщение от fed

Кстати - в продолжение давней дискуссии на которую тут ссылаются - нашел интересную статью по поводу того, как работать со статистикой при использовании FBI:
http://richardfoote.wordpress.com/20...-no-surprises/

А в чем суть поста? Не забывать использовать 'method_opt=> ‘FOR ALL HIDDEN COLUMNS SIZE 1′ в dbms_stats.gather_table_stats?

Согласен, но зачем вообще использовать dbms_stats.gather_table_stats, когда есть dbms_stats.gather_scheme_stats, которая прекрасно обновляет статистику по FBI:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

demas@ORCL> create table pink_floyd(table_name varchar2(50));

Table created.

demas@ORCL> insert into pink_floyd values('one');

1 row created.

demas@ORCL> insert into pink_floyd values('One');

1 row created.

demas@ORCL> insert into pink_floyd values('ONE');

1 row created.

demas@ORCL> select * from pink_floyd;

TABLE_NAME
--------------------------------------------------
one
One
ONE

demas@ORCL> select column_name, num_distinct, hidden_column, virtual_column from dba_tab_cols where 
table_name='PINK_FLOYD';

COLUMN_NAME                    NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
TABLE_NAME                                  NO  NO
SYS_NC00002$                                YES YES


demas@ORCL> begin
  2  dbms_stats.gather_schema_stats(ownname=>'DEMAS',cascade=>TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

demas@ORCL>  select column_name, num_distinct, hidden_column, virtual_column from dba_tab_cols where
 table_name='PINK_FLOYD';

COLUMN_NAME                    NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
TABLE_NAME                                3 NO  NO
SYS_NC00002$                              1 YES YES

[ZVV]

Цитата:

Сообщение от Андре

А в чем суть поста? Не забывать использовать 'method_opt=> ‘FOR ALL HIDDEN COLUMNS SIZE 1′ в dbms_stats.gather_table_stats?

Согласен, но зачем вообще использовать dbms_stats.gather_table_stats, когда есть dbms_stats.gather_scheme_stats, которая прекрасно обновляет статистику по FBI:

.................

забыли создать индекс

Да нет, суть поста немножко не в этом, как я понял. Скорее в основном он в том, чтоб знать о том, что статистика на эти спрятанные колонки должна быть собрана. Ну и в том, что она не собирается автоматом когда вы создаёте индекс (FBI), а при этом создаётся только статистика на сам индекс.
В итоге, если вы хотите, чтоб оптимизатор нормально работал с новым индексом, то вам нужно дособрать статистику на "спрятанные" колонки.

А dbms_stats.gather_table_stats собирает статистику на эти колонки так же хорошо, как и dbms_stats.gather_scheme_stats - проверьте сами.
И в режиме for all columns и в режиме for all indexed columns.
И стандартный GATHER_STATS_JOB, кстати, тоже...

[Андре]

Цитата:

Сообщение от ZVV

забыли создать индекс

Не, создать не забыл, а вот в приведенный листинг не включил.

create index pf_i on pink_floyd(upper(table_name));

Если бы индекс не создал - вот этого не было бы:

demas@ORCL> select column_name, num_distinct, hidden_column, virtual_column from dba_tab_cols where 
table_name='PINK_FLOYD';

COLUMN_NAME                    NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
TABLE_NAME                                  NO  NO
SYS_NC00002$                                YES YES

Цитата:

Сообщение от ZVV

Да нет, суть поста немножко не в этом, как я понял. Скорее в основном он в том, чтоб знать о том, что статистика на эти спрятанные колонки должна быть собрана. Ну и в том, что она не собирается автоматом когда вы создаёте индекс (FBI), а при этом создаётся только статистика на сам индекс.

При создании индекса статистика не создается даже на индекс. Ее надо собрать.

Цитата:

Сообщение от ZVV

А dbms_stats.gather_table_stats собирает статистику на эти колонки так же хорошо, как и dbms_stats.gather_scheme_stats - проверьте сами.

Дык не в том вопрос. dbms_stats.gather_scheme_stats - стандартная периодическая операция выполняемая минимум раз в неделю. Почему именно scheme? Потому что мне лень все таблички перебирать

А если мы уже используем dbms_stats.gather_scheme_stats, то смысл в exec dbms_stats.gather_table_stats пропадает.

[ZVV]

Цитата:

Сообщение от Андре

Не, создать не забыл, а вот в приведенный листинг не включил.

Это был оффтоп-юмор, именно что забыли включить в листинг, то что вы его реально создали и так понятно.

Цитата:

Сообщение от Андре

При создании индекса статистика не создается даже на индекс. Ее надо собрать.

А вот тут вы не правы! Цитата из ссылки:

Цитата:

However, and here comes the trap, when a function-based index is created, Oracle will now (since 10g) automatically calculate the statistics associated with the index...

Проверка:

>sqlplus scott/tiger@zvvdb

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 11 12:20:42 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set linesize 125
SQL> Drop Table Mytable
  2  /

Table dropped.

SQL> Create Table Mytable(A Varchar2(30))
  2  /

Table created.

SQL> Insert Into Mytable
  2     Select Object_name
  3       From All_objects
  4      Where Rownum < 11
  5  /

10 rows created.

SQL> Create Index Mytable_fbi On Mytable(Upper(A))
  2  /

Index created.

SQL> Create Index Mytable_ni On Mytable(A)
  2  /

Index created.

SQL> Select Table_name, Num_rows, Last_analyzed
  2    From User_tables
  3   Where Table_name = 'MYTABLE'
  4  /

TABLE_NAME                       NUM_ROWS LAST_ANA
------------------------------ ---------- --------
MYTABLE

SQL> Select Table_name, Column_name, Num_distinct
  2    From User_tab_cols
  3   Where Table_name = 'MYTABLE'
  4  /

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
MYTABLE                        A
MYTABLE                        SYS_NC00002$

SQL> Select Table_name, index_name, distinct_keys, num_rows
  2    From User_indexes
  3   Where Table_name = 'MYTABLE'
  4  /

TABLE_NAME                     INDEX_NAME                     DISTINCT_KEYS   NUM_ROWS
------------------------------ ------------------------------ ------------- ----------
MYTABLE                        MYTABLE_NI                                10         10
MYTABLE                        MYTABLE_FBI                               10         10

SQL>

Так что, как видите, начиная с 10-ки статистика на индекс собирается автоматом, и именно об этом пишет автор в приведённой цитате. И именно в этом основной смысл статьи, что на индекс то статистика собралась, но надо не забыть и про hidden columns. До 10-ки действительно не собиралась статистика на индекс автоматом, если не указать compute statistics, и проблемы, соответсвенно, такой не было.
При этом, в документации Оракла есть такая фраза:

Цитата:

After creating a function-based index, collect statistics on both the index and its base table using the DBMS_STATS package. Such statistics will enable Oracle Database to
correctly decide when to use the index.

А по поводу этого:

Цитата:

Сообщение от Андре

Дык не в том вопрос. dbms_stats.gather_scheme_stats - стандартная периодическая операция выполняемая минимум раз в неделю. Почему именно scheme? Потому что мне лень все таблички перебирать

А если мы уже используем dbms_stats.gather_scheme_stats, то смысл в exec dbms_stats.gather_table_stats пропадает.

Да, это стандартная операция, но, возможно, вы захотите чтоб оптимизатор захотел использовать ваш новый индекс "по-максимуму" до того как запустится "развнедельная" операция. В этой ситуации вам dbms_stats.gather_table_stats очень даже поможет.

И не всё так однозначно с dbms_stats.gather_table/scheme_stats.
Начиная с 10-ки есть, конечно, джоб GATHER_STATS_JOB, который типа делает всё сам.
Но в 9-ке мне приходилось, например, делать сбор статистики по гораздо более сложной схеме, чем "dbms_stats.gather_scheme_stats раз в неделю", потому что на огромных объёмах схемы такая концепция становится крайне непродуктивной. Приходилось разделять как-то таблицы на группы и собирать статистику по частям, какие-то чаще, какие-то реже, ну и т.д.. В зависимости от структуры данных.
А лень таблички перебирать - это только до тех пор, пока полная статистика на схему не начинает день-другой пересобираться.