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

Еше один побочный эффект установки параметра CURSOR SHARING -возможные неожиданные изменения размера столбцов, возвращаемых запросом. Рассмотрим следующий пример, в котором выдается размер возвращаемых столбцов до и после установки CURSOR SHARING. В этом примере для динамического разбора и описания запроса используется пакет DBMS SQL. Он выдает размеры столбцов, которые сообщает приложению сервер Oracle:

tkyte@TKYTE816> declare

2 3 4

6 7 8

10 11 12 13 14 15 16 17 18 19 20 21 22 23

25 26 27 28 29 30 31 32 33

l theCursor

l descTbl l colCnt

integer default dbms sql.open cursor;

dbms sql.desc tab;

number;

begin

execute immediate alter session set cursor sharing=exact; dbms output.put line(Without Cursor Sharing:); f or i in 1.. 2 loop

dbms sql.parse(

l theCursor,

select substr(object name, 55 c2 , Hello c3 from all objects tjji, dbms sql.native);

1, 5) cl.

dbms sql.describe columns(l theCursor,

l colCnt,

l descTbl);

i in 1 . . l colCnt loop dbms output.put line(Column jj

l descTbl(i).col name jj has a length of jj l descTbl(i).col max len);

end loop;

execute immediate alter session set cursor sharing=force;

dbms output.put line(With Cursor Sharing:);

loop;

dbms sql.close cursor(l theCursor); execute immediate alter session set

cursor sharing=exact;

end;

Without Cursor Sharing: Column Cl has a length Column C2 has a length Column C3 has a length With Cursor Sharing: Column Cl has a length Column C2 has a length Column C3 has a length

of of of

of of of

5 2 5

30 22 32

PL/SQL procedure successfully completed.

Глава 10

Размер первого столбца с 5 байт вырос до 30, потому что функция SUBSTR(OBJECT NAME, 1, 5) б1ла переписана как SUBSTR(OBJECT NAME, :SYS B 0, :SYS B l) Сервер не знает , что функция может вернуть максимум 5 байт, поэтому теперь возвращается значение 30 (длина поля OBJECTNAME). Длина второго столбца выросла с 2 до 22 байт, потому что сервер больше не знает , что будет возвращено значение 55 - известно только, что будет возвращаться число, а длина числа может составлять до 22 байт. Для последнего столбца выдано стандартное значение - если бы строка HELLO была больше, то и стандартное значение оказалось бы больше (например, если бы использовалась 35-байтовая строка, стандартное значение составляло бы 128 байт).

Можно возразить: Ну, длина возвращаемых данных не изменится, изменится только значение длины, выдаваемое сервером по запросу описания результирующего множества... . Проблемы возникнут во всех сценариях SQL*Plus, во всех отчетах, создаваемых с помощью различных инструментальных средств, и вообще во всех приложениях, запрашивающих у сервера характеристики результирующего множества для соответствующего форматирования столбцов. Результаты, выдаваемые этими приложениями, при установке параметра CURSOR SHARING изменятся; форматирование тщательно подготовленных отчетов во многих случаях окажется неадекватным. Только подумайте, как это может повлиять на множество уже существующих приложений! Этот побочный эффект очень легко продемонстрировать:

tkyte@TKYTE816> select substr(object name,1,2)

2 from all objects tl

3 where rownum = 1

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

tkyte@TKYTE816> select substr(object name,1,2)

2 from all objects t2

3 where rownum = 1

SUBSTR(OBJECT NAME,1,2) /1

Утилита SQL*Plus перешла от столбца из 2 символов к столбцу из 30. Это, несомненно, повлияет на ранее успешно работавшие отчеты.

Используются ли связываемые переменные?

Когда я спрашиваю: Используете ли вы связываемые переменные? , в ответ получаю вопрос: А как узнать, используются ли связываемые переменные? . К счастью, выяснить это весьма просто; вся необходимая информация находится в разделяемом пуле.

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

Я создал сценарий, который часто использую (и распространяю), для выявления операторов, которые совпадали бы при использовании связываемых переменных. Чтобы показать, как работает этот сценарий, я искусственно заполнил разделяемый пул операторами SQL, не использующими связываемых переменных:

tkyte@TKYTE816> create table t (x int) ; Table created.

tkyte@TKYTE816> begin

2 for i in 1 .. 100

3 loop

4 execute immediate insert into t values ( jj i jj );

5 end loop;

6 end;

PL/SQL procedure successfully completed.

Теперь можно демонстрировать сценарий. Он начинается с создания функции, удаляющей константы из строк. Она будет преобразовывать SQL-операторы вида:

insert into t values (hello, 55); insert into t values (world, 66);

в такой вид:

insert into t values (#, @) ;

Все операторы, которые могли бы совпасть при использовании связываемых переменных, теперь легко вхявить: оба представленных выше уникальных оператора INSERT после подстановки станут одинаковыми. Такое преобразование позволяет выполнить следующая функция:

tkyte@TKYTE816> create or replace

2 function remove constants(p query in varchar2)

3 return varchar2

4 as

5 l query long;

6 l char varchar2 (1) ;

7 l in quotes boolean default FALSE;

8 begin

9 for i in 1 .. length(p query)

10 loop

11 l char := substr(p query,i,l);

12 if (l char = and l in quotes)

13 then

14 l in quotes := FALSE;

15 elsif (l char = and NOT l in quotes)

16 then

17 l in quotes := TRUE;

18 l query := l query jj #;

19 end if;

20 if ( NOT l in quotes ) then

21 l query := l query jj l char;