Глава 10

tkyte@TKYTE816> set autotrace traceonly

tkyte@TKYTE816> select * from t tl where object name like :search str 2 and object type in(FUNCTION, PROCEDURE, TRIGGER);

58 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=291)

1 0 INLIST ITERATOR

2 1 TABLE ACCESS (BY INDEX ROWID) OF T (Cost=5 Card=3 Bytes=291)

3 2 INDEX (RANGE SCAN) OF T IDX2 (NON-UNIQUE) (Cost=2 Card=3)

Statistics

222 recursive calls

0 db block gets

45 consistent gets

3 physical reads

0 redo size 6930 bytes sent via SQL*Net to client

762 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

1 sorts (memory) 0 sorts (disk)

58 rows processed

то с учетом того, что мы знаем о распределении данных, кажется очевидным, что оптимизатор должен использовать индекс по столбцу OBJECT TYPE для выборки 58 строк из 21000, а затем применять к этим строкам конструкцию LIKE. Это показывает, что иногда использование констант оправдано. В данном случае использовать связываемую переменную не стоит - лучше указать константу. При этом оптимизатор сможет определить, что запрос будет выполняться быстрее при использовании конкретного индекса. Если же сделать так:

tkyte@TKYTE816> alter session set cursor sharing = force; Session altered.

tkyte@TKYTE816> select * from t t2 where object name like :search str 2 and object type in(FUNCTION, PROCEDURE, TRIGGER);

58 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=291)

1 0 INLIST ITERATOR

2 1 TABLE ACCESS (BY INDEX ROWID) OF T (Cost=5 Card=3 Bytes = 291)

3 2 INDEX (RANGE SCAN) OF T IDX2 (NON-UNIQUE) (Cost=2 Card=3)

Стратегии и средства настройки 539

statistics

О recursive calls

О db block gets

19256 coneietent gets

169 physical reads

0 redo size

7480 bytes sent via SQL*Net to client 762 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 58 rows processed

то, хотя выдаваемый план выполнения запроса якобы не изменился (AUTOTRACE показывает точно такой же план), различие в количестве consistent gets (логических чтений) получается существенное, так что явно что-то изменилось. Сервер фактически выполняет запрос:

select *

from t t2 where object name like :search str and object type in( :SYS B 0,:SYS B l, :SYS B 2 )

и уже не может определить, сколько строк он будет выбирать через индекс по столбцу OBJECT TYPE. Этот пример также показывает, как установка параметра CURSOR SHARING затрудняет настройку. Выдаваемый утилитой SQL*Plus план запроса заставляет думать, что считывается индекс T IDX2, но если посмотреть на количество consistent gets (логических чтений), окажется, что их выполнено 19256. Первый запрос, действительно использующий индекс T IDX2, обработал 45 блоков. В данном случае установка autotrace выдает нам некорректный план. Утилита SQL*Plus не знает, какой запрос в действительности выполняется. Я включил трассировку SQL TRACE (подробнее об этом - в следующем разделе), и теперь для двух запросов можно четко увидеть различие:

select * from t tl where object name like :search str and object type in(FUNCTION, PROCEDURE, TRIGGER)

58 INLIST ITERATOR

58 TABLE ACCESS BY INDEX ROWID T

61 INDEX RANGE SCAN (object id 25244)

Глава 10

select * from t t2 where object name and object type in(:SYS B O,:SYS B 1,

like :search str

:SYS B 2)

Rows

Row Source Operation

58 TABLE ACCESS BY INDEX ROWID T 21783 INDEX RANGE SCAN (object id 25243)

Средства SQLTRACE и TKPROF могут показать, что происходит на самом деле. Второй запрос б1л выполнен с использованием другого индекса (идентификатор объекта - другой), что в данном случае не оптимально. Наш запрос выполняется в 15-20 раз дольше и обрабатывает огромный объем данных. Эта проблема ненужных связываемых переменных будет проявляться во многих приложениях, намеренно использующих как связываемые переменные, так и константы. Без дополнительной информации, обеспечиваемой константами в запросе, оптимизатор может принять неверное решение. Только при корректном использовании связываемых переменных, где только возможно, и констант - там, где это обязательно, можно достичь сбалансированности. Установка CURSOR SHARING - временное решение, с которого можно начать, но по изложенным выше причинам оно не может быть долговременным.

Параметр CURSOR SHARING на только сбивает с толку оптимизатор, как было показано выше, он может повлиять и на другие средства Oracle. Например, в главе 7 описывалось такое средство как индексы по функции. По сути, в Oracle можно создать индекс по функции. Используя пример из той главы, где индекс создавался следующим образом:

tkyte@TKYTE816> create index test soundex idx on

2 test soundex(substr(my soundex(name),1,6))

Index created.

можно выяснить, что установка параметра CURSORSHARING не позволит для выполнения запроса

tkyte@TK:E816> select name

2 from testsoundex С

3 where substr(my soundex(name),1,6)

ray soundex(FILE$)

использовать этот индекс, поскольку литералы 1 и 6 будут заменены связываемыми переменными. В данном случае можно решить эту проблему, спрятав константы в представлении, но ее все равно надо учитывать. Не будет ли других неожиданных отличий?