Эти материалы являются объектом авторского права и защищены законами РФ и международными соглашениями о защите авторских прав. Перед использованием материалов вы обязаны принять условия лицензионного договора на использование этих материалов, или же вы не имеете права использовать настоящие материалы

Авторская площадка "Наши орбиты" состоит из ряда тематических подразделов, являющихся моими лабораторными дневниками, содержащими записи за разное, иногда продолжительно отличающееся, время. Эти материалы призваны рассказать о прошедшем опыте, они никого ни к чему не призывают и совершенно не обязательно могут быть применимы кем-то ещё. Это только лишь истории о прошлом

Об оптимизации прикладных запросов

Обычно в администраторы баз данных приходят двумя путями. Это либо ищущие большего покоя бывшие разработчики, или системные администраторы, расширяющие свои навыки ещё одним продуктом. Я пришёл вторым путём, и считаю, что этот путь оптимален - потому что специфические для профессии администратора требования к консервантивизму, надёжности, перестраховке и предпочтению самых простых решений как самых надёжных характерна именно для системных администраторов, тогда как часто приходилось слышать, что разработчики склонны к авантюрным решениям, риску, более поверхностному подходу и гораздо меньшей перестраховке. Девиз "а давайте попробуем так на продуктовой системе" - это совершенно точно про разработчиков. К чему это я ? Есть позиция вендора. Оптимизация прикладных запросов - удел отдельной профессии под названием разработчик, с отдельными и отличающимися от таковых у администратора, морально - волевыми особенностями и подходами

Однако что делать, если инициировать прикладную оптимизацию специально обученными людьми нельзя ? Нет разработчиков, или приклад является "чёрным ящиком" без возможности оптимизации, или разработчики используют свой прикладной язык для последующей генерации SQL запросов, как в EMC Documentum ? Конечно правильный и самый результативный путь - прикладная оптимизация разработчиком, но кое что можете и вы - как администратор. Но, занимаясь оптимизацией прикладных запросов, помните, что это задачи отдельной профессии, и если вы "дерётесь только по праздникам ((С) Квентин Дорвард)", то разработчик - по крайней мере профессиональный разработчик - делает это ежедневно, и выполнит свою работу гораздо быстрее и качественнее вас, администратора баз данных

У вас, как администратора баз данных, масса своих задач, и жадное коллекционирование намоленных технологий представляется мне очень вредным и не совместимым с профессией администратора хоть систем, хоть баз данных. Впрочем очень образно об этом уже сказали Роберт Асприн в "Великолепном мифе" - в истории про солдата, которому оказалось недостаточно кучи фунтовых камней, в результате чего он раздробил себе ногу трёхфунтовым камнем и пропустил через перевал вражескую армию, а также Стругацкие - описывая откровения разведчика, который должен осторожничать, беречь себя и не соваться неизвестно куда без многократной перестраховки, чтобы вернуться живым и принести информацию, что так не совпадает с обывательским восторженным мифом о героизме и самопожертвовании

Обзор инструментария и методик

Что вы можете предпринять самостоятельно ? Найти неоптимальные запросы и подменить им план часто возможно и без разработчика - знать базовые вещи и владеть инструментарием оптимизации всё же бывает полезно и администратору, пусть и в гораздо меньшем объёме. Ниже будут представлены заметки по такому инструментарию, обзорные и неполные, но всё же

• выявить интересующие запросы (по топам логических или физических чтений, CPU, топу EM консоли и т.п.)
• если прибегают с проблемой - сначала и независимо от конкретной прикладной проблемы проверить общее состояние экземпляра, потолкой железа, и состояние текущих блокировок на экземпляре
• проанализировать статистику проблемного запроса (сколько раз выполнялся, полных и неполных разборов, время выполнения и общее, сколько инвалидаций и перезагрузок в библиотечном кеше
• посмотреть на оптимальность плана и на статистики объектов - объём (количество строк), сбор объёктной статистики
• сделать предположение о причинах (потолок железа, неоптимально сконфигурирован экземпляр, проблема в статистике, отсутствии или неиспользовании индексов, неоптимальный в ином план, проблема в архитектуре запроса или ещё чём-то)
• собрать значения bind переменных для отладки
• подготовить текущий запрос и оптимизированный вариант, прогнать их в sqlplus со включенным автотрэйсом (SET AUTOT ON|TRACE EXPL STAT ; здесь важны планы и статистики исполнения, вывод результата можно выключить), сравнить статистики выполнения. Альтернативным механизмом является использование SQL Tuning задания в EM консоли, как минимум показывающее мнение автоматики СУБД

Дальше, если проба sqlplus подтверждает, что найден оптимальный вариант - возникает задача его установки. Какие есть варианты подмены плана - старейший каркасный план исполнения (outline, сохранение рекомендованных подсказок оптимизатору), в 11 версии появился автоматический интеллектуальный механизм выбора планов на основе baseline (выборки, с которой сравнивался текущий план, чтобы не допустить деградации производительности. Детали у меня в переводе глав из руководства по тюнингу для 11 версии, раздел 15, а также появилась возможность подмены именно плана выполнения за счёт явного создания baseline через Oracle SQL Plan Management (SPM) - смотри приложение в этой статье

Чем плох outline ? На неоптимальном коде без использования binding вы получаете не один запрос с разными аргументами, который можно корректировать через каркасный план, а, возможно - тысячи. Впрочем это касается и SMP. Если профиль статистики изменится - outline может стать сильно неоптимальным. Ну и это официально устаревший механизм. Чем хорош ? Работает не только в Enterprise Edition, и не только с 11 версии. Технические детали коротко об outlines в FAQ

Появившееся в 10 версии профилирование я всегда считал механизмом необязательных подсказок на основании расширенного статистического профиля, вот выборки из официального руководства по тюнингу 12 "(22) A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile stores this information in the data dictionary. The optimizer uses this information at optimization time to determine the correct plan ... ... Internally, a SQL profile is implemented using hints that address different types of problems. These hints do not specify any particular plan. Rather, the hints correct errors in the optimizer estimation algorithm that lead to suboptimal plans. For example, a profile may use the TABLE_STATS hint to set object statistics for tables when the statistics are missing or stale ... ... (23) SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates. A SQL plan baseline is a set of accepted plans. Each plan is implemented using a set of outline hints that fully specify a particular plan. SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. For example, a hint may correct the cardinality estimate of a table. Because a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline. For example, changes in initialization parameters and optimizer statistics allow the optimizer to choose a better plan"

Интересно, что при принятии профиля можно обойти проблему использования литеральных переменных в запросах, для чего в true выставляется параметр force_match процедуры DBMS_SQLTUNE.ACCEPT_SQL_PROFILE. Список профилей хранится в DBA_SQL_PROFILES. В сентябре 2016 наткнулся на статью Ильи Деева, в которой он показывает возможности использования ещё двух технологий подмены плана - собственно профиля SQL и патчей SQL Repair Advisor. Это интересно, нужно будет как нибудь попробовать

В случае, когда отрабатывает процедура, удобным механизмом её анализа и поиска узких мест является трассировка по событию 10046. Вы определяетесь с сессией и включаете для неё трассировку по событию 10046, после чего в указанной сессии запускается процедура, а по её окончании трассировка отключается, а полученный сырой файл скармливается утилите tkprof для преобразования в читаемый вид и последующей передачи девелоперам на предмет анализа, или же для не прикладных (административных) процедур можно анализировать самостоятельно. В соответствующем пункте моего Oracle FAQ описаны технические детали включения и отключения такой трассировки

Приложение. Готовим коллектор связанных переменных

Адаптированный вариант этого коллектора позволил собрать данные о реальных bind переменных на одной из высоконагруженных систем (45 ядер AIX, несколько десятков тысяч пользователей по всей России) и предоставил разработчикам нужную для проведения тестов на копии реальной базы информацию

-- создаём таблицы
drop table belstat.belstat_v$sql ;
create table belstat.belstat_v$sql
       as select 'baza--------' DBNAME,TO_DATE('2012-09-09 22:22:22','YYYY-MM-DD HH24:MI:SS') TMSTAMP,a.*
       from v$sql a where rownum < 3 ;
delete from belstat.belstat_v$sql ;

drop table belstat.belstat_v$sql_bind_capture ;
create table belstat.belstat_v$sql_bind_capture
       as select 'baza--------' DBNAME,TO_DATE('2012-09-09 22:22:22','YYYY-MM-DD HH24:MI:SS') TMSTAMP,a.*
       from v$sql_bind_capture a where rownum < 3 ;
delete from belstat.belstat_v$sql_bind_capture ;


-- организуем периодическую выборку bind переменных для нагруженных запросов
exec DBMS_OUTPUT.ENABLE(20000) ;
set serveroutput on
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ;
select sysdate from dual ;
DECLARE
TMSTAMP date ;
DBLINK_NAME VARCHAR2(40) ;
BEGIN
TMSTAMP := sysdate() ;
DBLINK_NAME := 'BAZAIMYA' ;
FOR I IN (SELECT SQL_ID,SQL_TEXT, BUFFER_GETS, EXECUTIONS,ROWS_PROCESSED
    FROM v$sql WHERE  executions > 1000 and buffer_gets > 10000 and buffer_gets / executions > 1000) LOOP

    DBMS_OUTPUT.PUT_LINE(TMSTAMP||' '||DBLINK_NAME||' '||i.SQL_ID||' '||i.BUFFER_GETS||' '||i.EXECUTIONS||' '||i.ROWS_PROCESSED) ;

    insert into belstat.belstat_v$sql select DBLINK_NAME,TMSTAMP,a.* from v$sql a where sql_id = i.SQL_id ;
    insert into belstat.belstat_v$sql_bind_capture select DBLINK_NAME,TMSTAMP,a.* from v$sql_bind_capture a where sql_id = i.SQL_id ;
    END LOOP ;
END ;
/

select sysdate from dual ;
show errors
exit ;

-- получаем отчёты
exec DBMS_OUTPUT.ENABLE(20000) ;
set serveroutput on linesize 4000 pagesize 40000
set long 4000
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ;
select sysdate from dual ;

DECLARE
TMSTAMP date ;
DBLINK_NAME VARCHAR2(40) ;
tmp_var varchar2(50) ;
full_text varchar2(30000);
BEGIN
TMSTAMP := sysdate() ;
DBLINK_NAME := 'BAZAIMYA' ;
-- get sql_id list for some timestamp
for sqlid_list in (select tmstamp,sql_id,sql_fulltext,address,hash_value,child_number
               from belstat.belstat_v$sql order by tmstamp,sql_id,child_number) loo
    DBMS_OUTPUT.PUT_LINE('=== new query ===') ;
    full_text := sqlid_list.sql_fulltext ;
    for bindings_list in (select name,value_string from belstat.belstat_v$sql_bind_capture
        where tmstamp = sqlid_list.tmstamp and sql_id = sqlid_list.sql_id and
              child_number = sqlid_list.child_number) loop
        tmp_var := substr(bindings_list.name,2,50) ;
        full_text := replace(full_text,':"'||tmp_var||'"',''''||bindings_list.value_string||'''') ;
        end loop ;
    DBMS_OUTPUT.PUT_LINE(full_text) ;
    end loop ;
END ;
/

select sysdate from dual ;
show errors
exit ;

Приложение. Используем SPM для подмены плана (EE c 11 версии)

По умолчанию сбор бэйслайнов планов выключен переменной OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. Для автоматического сбора переменную можно выставить в TRUE, и эволюционировать один из собранных планов до активного. Детали у меня в переводе глав из руководства по тюнингу для 11 версии, раздел 15. Однако при выклеченном автоматическом сборе есть возможность создать baseline руками с указанием плана не от проблемного, а от оптимизированного запроса

-- создать baseline для подмены плана
DECLARE
SQL_TEXT_OLD clob;
RSLT NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(20000) ;
SELECT sql_fulltext INTO SQL_TEXT_OLD FROM v$sqlarea
       WHERE sql_id = 'подставить_для_оптимизируемого_запроса' ;
RSLT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CASHE(sql_id => 'ID_оптимизированного_запроса',
        plan_hash_value => PHV_оптимизированного_запроса,
        sql_text => SQL_TEST_OLD) ;
DBMS_OUTPUT.PUT_LINE(RSLT) ;
END;
/

-- удалить baseline для подмены плана
DECLARE
SQL_HANDLER VARCHAR2(30);
RSLT NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(20000) ;
SELECT sql_handle INTO SQL_HANDLER FROM dba_sql_plan_baselines
       WHERE plan_name = 'подставить_имя_из_автотрэйса' ;
RSLT := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => SQL_HANDLER) ;
DBMS_OUTPUT.PUT_LINE(RSLT) ;
END ;
/

-- с версии 12.2 появляется возможноть добавлять план из AWR
variable nVal number
begin
:nVal := dbms_spm.load_plans_from_awr( begin_snap=>2566,end_snap=>2567,
             basic_filter=>q'# sql_id='aqq1234hggcge' and plan_hash_value='1543252653' #' );
end;
/

статья не завершена ...

Белонин С.С. (С), октябрь 2010 года