tkyte@TKYTE816> begin

2 dbms stats.set tale stats (ovjnname => USER,

3 tabname => T,

4 numrows => 500,

5 numblks => 7,

6 avgrlen => 100) ;

7 end;

PL/SQL procedure successfully completed.

tkyte@TKYTE816> select table name, num rows, blocks, avg row len

2 from user tables

3 where table name = T;

TABLE NAME NUM ROWS BLOCKS AVG ROW LEN

Т 500 7 100

Теперь оптимизатор больше не будет использовать свои предположения - он будет использовать наши. Если продолжить в том же духе, можно заставить сервер использовать куда более точную статистическую информацию. Следующий пример показывает использование временной таблицы оптимизатором, основанным на стоимости. План выполнения запроса, сгенерированный без учета статистической информации, - не оптимален. Оптимизатор решил использовать индекс, хотя этого не надо было делать. Такой план был выбран, исходя из стандартной информации об избирательности индекса, количества строк в таблице и выбираемого количества строк и т.п. Чтобы исправить это, я ненадолго удалил временную таблицу, создал постоянную таблицу с тем же именем и структурой и поместил в нее ренрезентативные данные. Затем я тщательно проанализировал таблицу (я мог бы также сгенерировать гистограммы и т.п.) и с помощью пакета DBMSSTATS экспортировал статистическую информацию для этой постоянной таблицы. Затем я удалил постоянную таблицу и пересоздал временную. После этого осталось только импортировать репрезентативную статистическую информацию, и оптимизатор выбрал правильный план:

tkyte@TKrE816> create global temporary table temp all objects

2 as

3 select * from all objects where 1=0

Table created.

tkyte@TKYTE816> create index temp all objects idx on temp all objects(object id) 2 /

Index created.

tkyte@TKYTE816> insert into temp all objects

2 select * from all objects where rownum < 51

50 rows created.

tkyte@TKYTE816> set autotrace on explain tkyte@TKYTE816> select /*+ ALL ROWS */ object type,

2 FROM temp all objects

3 where object id < 50000

4 group by object type

count(*)

OBJECT TYPE

JAVA CLASS

COUNT(*) 50

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: ALL ROWS (Cost=13 Card=409

1 0 SORT (GROUP BY) (Cost=13 Card=409 Bytes=9816)

2 1 TABLE ACCESS (BY INDEX ROWID) OF TEMP ALL OBJECTS (Cost=10

3 2 INDEX (RANGE SCAN) OF TEMP ALL OBJECTS IDX (NON-UNIQUE)

tkyte@TKYTE816> set autotrace off

Это показывает, что оптимизатор, основанный на стоимости, выбрал неверный план. При обращении к более чем 10-20 процентам таблицы индекс использовать не стоит. Мы же обращаемся ко всей таблице; и таблица настолько маленькая, что использование индекса ничего не даст в любом случае. Вот как предоставить оптимизатору информацию, необходимую для выработки правильного плана:

tkyte@TKYTE816> drop table temp all objects;

Table dropped.

tkyte@TKYTE816> create table temp all objects 2 as

from all objects where 1=0

3 select *

Table created.

tkyte@TKYTE816> create index temp all objects idx on temp all objects(object id)

2 /

Index created.

tkyte@TKYTE816> insert into temp all objects

2 select * from all objects where rownum < 51; 50 rows created.

tkyte@TKYTE816> analyze table temp all objects compute statistics; Table analyzed.

tkyte@TKYTE816> analyze table temp all objects compute statistics for all 2 indexes;

Table analyzed.

Я всего лишь создал постоянную таблицу, аналогичную временной и наполнил ее репрезентативными данными. В этом - весь фокус; необходимо хорошо продумать, какие данные помешаются в таблицу перед анализом. Предположения оптимизатора будут заменены этими данными, так что надо предоставить ему данные поточнее, чем он строит сам. В некоторых случаях бывает достаточно просто установить статистическую информацию о таблице или индексе вручную, как было сделано в предыдущем примере, чтобы сообщить оптимизатору количество и диапазон значений. В других случаях, чтобы дать оптимизатору, основанному на стоимости, необходимые данные, придется добавить в словарь данных много разнообразной информации. Можно не добавлять ее вручную, а поручить это серверу Oracle. Представленный ниже метод позволяет получить и легко задать всю информацию:

tkyte@TKYTE816> begin

2 dbms stats.create stat table (ownname => USER,

3 stattab => STATS);

5 dbms stats.export table stats (ownname => USER,

6 tabname => TEMP ALL OBJECTS ,

7 stattab => STATS) ;

8 dbms stats.export index stats (ownname => USER,

9 indname => TEMP ALL OBJECTS IDX ,

10 stattab => STATS);

11 end;

12 /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> drop table temp all objects; Table dropped.

tkyte@TKYTE816> create global temporary table temp all objects

2 as

3 select * from all objects where 1=0

Table created.

tkyte@TKYTE816> create index temp all objects idx on temp all objects(object id)

2 /

Index created.

tkyte@TKYTE816> begin

2 dbms stats.import table stats(ownname => USER,

3 tabname => TEMP ALL OBJECTS,

4 stattab => STATS);

5 dbms stats.import index stats(ownname => USER,

6 indname => TEMP ALL OBJECTS IDX,

7 stattab => STATS);

8 end;

PL/SQL procedure successfully completed.