Oracle Tips Archive

Donald K. Burleson

On Joins and Query Plans (Tom Kyte)

11g Сергей Томин

Использование переменных привязки в SQLPLUS

Оригинальная статья
Using bind variables in SQL*Plus

Трассировка в Oracle

Расмотрены вопросы использования трассировки в Oracle.

Дополнительно:
http://oradba.com.ru/tuning/optimizer/tips/t2_sqltrace.shtml


Пример:
grant alter session to ;

-- On:
begin
execute immediate 'alter session set max_dump_file_size = UNLIMITED';
execute immediate 'alter session set tracefile_identifier=TRACE_LABEL';
execute immediate 'alter session set events=''10046 trace name context forever,level 12''';
end;
/

-- Select:
select * from dual;

-- Off:
begin
execute immediate 'alter session set events=''10046 trace name context off''';
end;
/


Еще один пример:
-- Включение:
begin
dbms_system.set_bool_param_in_session(129,9871,'timed_statistics',true);
dbms_system.set_int_param_in_session(129,9871,'max_dump_file_size',2147483647);
dbms_system.set_ev(129,9871,10046,12,'');
end;
/

-- Выключение:
begin
--dbms_system.set_bool_param_in_session(129,9871,'timed_statistics',true);
--dbms_system.set_int_param_in_session(129,9871,'max_dump_file_size',2147483647);
dbms_system.set_ev(129,9871,10046,0,'');
end;
/

System Stats strategy

В оригинальной статье рассмотриваются вопросы калибровки стоимости чтения при индексном доступе и при многоблочном чтении - полном сканировании таблицы.

При значениях, получаемых по умолчанию в Oracle 10g - оказывается что полное сканирование таблицы с 30 млн. строк дешевле чем индексный доспуп по первичному ключу.

Для операционной системы SOLARIS можно попробовать "пузомерку" для определения истинных значений характеристик IO (исключая буферизирующее чтение и прочие кэши)
FileBench

Для этих же целей можно использовать Oracle утилиту Orion.

Также рассматриваются настройки параметров в случае сбора системной статистики
optimizer_index_cost_adj
optimizer_index_caching

Выполнение настроек сессии - after_logon_trigger

create or replace trigger bkw$_after_logon_on_database
after logon on database
declare
-- Target parameters:
l_tracefile_id      varchar(100);
l_event             varchar(100);

l_target_username   varchar(100);
l_targer_ip_address varchar(100);


-- Current Parameters:
l_cur_username   varchar(100);
l_cur_ip_address varchar(100);
begin
--
-- Setup Target parameters:
--
l_tracefile_id      := 'BKW_TRACE_001';
l_event             := '10046 trace name context forever,level 12';

l_target_username   := '';
l_targer_ip_address := '';


--
-- Read current parameters From Syscontext:
--

-- Username:
select sys_context('USERENV','SESSION_USER') into l_cur_username from dual;

-- Client IP:
select sys_context('USERENV','IP_ADDRESS')  into l_cur_ip_address from dual;


--
-- Check Conditions:
--
if (l_cur_username=l_target_username) and (l_cur_ip_address=l_targer_ip_address) then
execute immediate 'alter session set max_dump_file_size = UNLIMITED';
execute immediate 'alter session set tracefile_identifier='||l_tracefile_id;
execute immediate 'alter session set events=''||l_event||''';
end if;
exception
when others then
null;
end;
/

Oracle Events

Diagnostic events

10046
Трассировка sql запросов.
Источник

10053
Отладка стоимостного оптимизатора (optimizer debug event)
Источник

10195
Отключение генерации транзитивных предикатов
Что-то в инете

Настройка производительности приложения

Общая методика настройки приложения в среде Oracle 10g заключается в последовательном выполнении серии мероприятий. На первой стадии настраиваются общие параметры экземпляра СУБД.

Необходимо выбрать значение параметра optimizer_mode на уровне экземпляра СУБД. Что позволит перейти к дальнейшим настройкам на следующих уровнях.

Выбор значения параметра optimizer_mode зависит от специфики приложения. Для транзакционных систем - "OLTP систем" установите значение в FIRST_ROWS, а для гибридных и аналитических "OLAP систем" - рекомендуется ALL_ROWS, что является значением по умолчанию. Существуют и другие значения параметра, но они в контексте СУБД Oracle версии 10g менее "привлекательны" - речь идет о значении параметра optimizer_mode=CHOOSE, т.к. оптимизатор по правилам в редакции СУБД ORACLE начиная с версии 10g не поддерживается.

На примере одной Автоматизированной Банковской Системы (АБС) - пробовались следующие значения параметра optimizer_mode: ALL_ROWS, FIRST_ROWS и CHOOSE.
После миграции на версию СУБД Oracle 10g sql запросы АБС сменили планы выполнения. Часть sql запросов показали сравнительно лучшую производительность по сравнению с предыдущими версиями СУБД Oracle (8i) - однако получить положительный отклик от пользователей АБС не представляется возможным. Другая часть sql запросов кардинально не изменила своих временных характеристик.
А вот с чем придется столкнуться, так это подмножество SQL запросов - которое стало выполняться значительно медленнее.

Продолжение следует...
Статья не закончена...