SQL statements, не использующие bind variables, просто засоряют Ваш library cache и легко могут привести к ORA-04031 (unable to allocate %s bytes of shared memory) у других пользователей. Попытки увеличить shared pool или сделать 'alter system flush shared_pool' не устраняют причину проблемы и могут спасти только временно.

Для устранения проблемы с ORA-04031 надо найти её причину. А именно -- sql statements, не использующие bind variables.

В Note:146599.1 (Diagnosing and Resolving Error ORA-04031) приводится пример запроса, который может показать Вам обрывки таких sql statements:

SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 80
ORDER BY 2;

Но мы не считаем это работающим решением и вот почему:

Если оттрассировать этот запрос и обработать trace файл с помощью OraSRP, то можно увидеть примерно следующую картину:

Response Time Components Summary

Response Time Component	Duration		# Calls	- Duration per Call -
				Avg	Min	Max
direct path write	6.15s	41.1%	963	0.006386s	0.00s	0.17s
CPU service	5.74s	38.3%	7	0.820000s	0.00s	5.72s
direct path read	4.15s	27.7%	1,007	0.004121s	0.00s	0.32s
latch free	0.06s	0.4%	73	0.000822s	0.00s	0.01s
SQL*Net message from client	0.02s	0.1%	4	0.005000s	0.00s	0.01s
file open	0.00s	0.0%	1	0.000000s	0.00s	0.00s
SQL*Net message to client	0.00s	0.0%	4	0.000000s	0.00s	0.00s
unaccounted-for	-1.15s	-7.7%
Total	14.97s	100.0%	2059	0.007271s	0.00s	5.72s

Обратите внимание на ожидания 'direct path read/write'. Две трети нашего времени мы просто сортируем данные. Почему? Потому что v$sqlarea построена на основе x$kglcursor с группировкой по нескольким полям, что и дает нам ожидания 'direct path read/write'.

Да и вообще -- вышеупомянутый запрос выводит лишь какие-то обрывки реальных запросов.

 

Другой вариант (который уже можно пробовать использовать в реальной жизни) был предложен Томом Кайтом еще в 2000 году. Отвечая на вопрос читателя о "Library cache latch contention", Том привел такой код для поиска statements, не использующих bind variables:

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function 
remove_constants( p_query in varchar2 ) return varchar2
as
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
begin
    for i in 1 .. length( p_query )
    loop
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;
    end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

"Лобовое" решение, которое тем не менее можно считать работающим. Умильно, конечно, глядеть на такой алгоритм "выкашивания" строковых и числовых констант. Но слегка печально видеть, что и здесь используется v$sqlarea, что приводит к излишней нагрузке на Oracle сервер. С другой стороны, если мы действуем только в рамках sql или pls/sql, то это, наверное, самый лучший вариант.

И вот тут может возникнуть идея не ограничиваться только рамками sql. В результате мы создали простенький скрипт bindless.py, который делает почти то же самое, что и вариант Тома, только с меньшими нагрузками на Oracle сервер и, соответственно, быстрее.

В принципе, такой скрипт можно легко написать на perl'е и, даже, наверное, без особого труда на ksh, но мы выбрали python, как более развлекательный для нас вариант.

Вот что делается в скрипте:

• с помощью sqlplus выполняется запрос 'select sql_text from v$sql'.
• содержимое получившегося spool файла быстренько загоняется в hash, в результате чего мы избавляемся от дубликатов, которые присутствуют в v$sql (для этих целей в v$sqlarea используется group by).
• с помощью регулярных выражений чуть более аккуратно и, конечно же, более быстро, чем у Тома, заменяем в каждом sql statement все строковые константы на символ '#', а все числовые константы на символ @.
• строим новый hash, в котором ключом является поправленный sql statement, а значением, соответственно, сколько раз он встречается.
• сортируем новый hash по значению и выводим в итоге sql statements, которые встречаются в library cache более ста раз (или сколько мы там укажем при запуске скрипта).
• Всё! Можно начинать искать хозяев этих sql statements и вести среди них просветительскую работу на тему использования bind variables в запросах.

 

Если у Вас возникли вопросы или замечания по скрипту bindless.py, то Вы всегда можете их написать мне.