DeepEdit!

Одним из первых клиентов hotsos.com была компания, в которой рабо­тали с Oracle Financials и Oracle Manufacturing. Наблюдалось неудов­летворительное время отклика в нескольких программах, как собст­венных, так и приобретенных на стороне. Клиент пригласил нас не для того, чтобы исправить сложившееся положение, а для того, чтобы мы обучили его сотрудников тому, как это сделать. Для начала следо­вало научить нового аналитика по производительности, как собирать и анализировать правильные диагностические данные. Вторым пунк­том было облечение нашей пре-бета версии программ Sparky и Hotsos Profiler в некую подобающую форму, с тем чтобы клиент мог пользоваться ими и после нашего отъезда. Для нас эта работа была очень хо­рошим опытом. Новый аналитик по производительности был админи­стратором приложений и никогда ранее особо не занимался задачами повышения производительности.

Через пару месяцев наше общение с новым аналитиком от ежеднев­ных телефонных звонков перешло к еженедельному обмену электрон­ными письмами. Однажды он позвонил, чтобы просто поздороваться и рассказать об одном из своих достижений. Эту историю мы вам и рас­скажем.

Определение цели

За несколько предшествующих недель он проделал замечательную ра­боту, исследовав список медленных пользовательских операций, важ­ных для его компании. Он признался, что решил заняться этим, когда подозрительное отсутствие жалоб на производительность неожиданно обеспечило ему некоторое количество свободного времени. Он решил проверить, почему одно пакетное задание работает так долго (если вы помните, прежде чем заняться усовершенствованием производительно­сти, новый аналитик работал непосредственно с приложениями и по­этому хорошо знал, как долго оно работает). Он выполнил трассировку программы. Профиль ресурсов для данного файла трассировки приве­ден в примере 12.3.

Как видите, в профиле доминируют обслуживание процессором и чте­ние файлов базы данных, причем вместе они образуют почти 80% об­щего времени отклика. Около 10% времени отклика занимают опера­ции блокировки глобального кэша, необходимые Oracle Parallel Server, а оставшиеся 10% распределены между событием unaccounted-for и мно­жеством несущественных событий.

На основе одного лишь профиля ресурсов невозможно определить, чрезмерно ли загружен процессор. Но очевидно, что для заметного уменьшения 42-минутного значения времени отклика потребуется уменьшить и составляющую CPU service. В подобном случае первым должен возникать вопрос: «Какая из команд SQL несет ответствен­ность за такую загрузку процессора?». Благодаря Hotsos Profiler полу­чить ответ на этот вопрос чрезвычайно просто, предоставляя в своем выводе специальную секцию, которая перечисляет пять команд SQL, внесших наибольший вклад в каждую из составляющих времени от­клика (см. пример 12.4).

SQL Statement Id	Du ration
704365403	1,066.4s	69.8%
3277176312	371.9s	24.3%
1107640601	8.5s	0.6%
3705838826	6.5s	0.4%
529440951	6.0s	0.4%
111 others	68.7s	4.5%
Total	1,527.5s	100.0%%

Основную долю процессорного времени потребляют всего две команды SQL. Выходные данные Hotsos Profiler содержат идентификаторы ко­манд, которые являются гиперссылками, приводящими вас к данным, показанным в примере 12.5. Подобные сведения предоставляет утили­та Oracle tkprof, которой надо указать порядок сортировки sort=prscpu, execpu,fchcpu. При таком упорядочивании интересующие вас команды SQL будут находиться в верхней части списка.

Cursor	Action		Response Time			LIO	PIO
Action	Count	Rows	Elapsed	CPU	Other	Blocks	Blocks
Parse	0	0	0.0	0.0	0.0	0	0
Execute	1,166	0	1,455.0	1,066.4	388.6	8,216,887	3,547
Fetch	0	0	0.0	0.0	0.0	0	0
Total	1,166	0	1,455.0	1,066.4	388.6	8,216,887	3,547
Per Exe	1	0	1.3	0.9	0.3	7,047	3
Per Row	1,166	1	1,455.0	1,066.4	388.6	8,216,887	3,547

Чрезвычайно полезная информация, позволяющая сделать ряд инте­ресных наблюдений:

Команда, потребляющая основное время процессора, - это очень простая команда UPDATE, которая выполнялась 1166 раз.

В ходе 1166 выполнений данной команды UPDATE ни разу не была об­работана ни одна строка.

Для каждого выполнения потребовалось в среднем 7047 операций LIO (8 216 887 операций LIO, поделенных на 1166 исполнений), по­зволивших установить, что ни одна строка не соответствует просто­му условию фразы WHERE.

Коэффициент попаданий в кэш буферов базы данных для данной команды весьма «хорош», он составляет:

Ирония в том, что одной из причин, по которой данная команда ни­когда не привлекала серьезного внимания аналитиков по произво­дительности системы, мог быть как раз хороший коэффициент по­паданий - средства мониторинга производительности воспринима­ли эту команду как образцовую.

Диагностика и лечение

В главе 11 приводилось простое эмпирическое правило, относящееся к количеству вызовов LIO. Оно гласит, что если команде SQL требует­ся более десяти операций LIO для каждой возвращаемой строки каж­дой таблицы из фразы FROM, то команда, скорее всего, выполняет слиш­ком много операций LIO. Конечно, наша команда UPDATE - это не за­прос с фразой FROM, но она исполняет практически такой же фрагмент кода ядра Oracle, как следующий запрос:

select requisition_header_id=:b0

Какое количество LIO необходимо для того, чтобы определить, что за­прос не возвращает строк? Мне кажется, что меньше десяти, и вот по­чему: если бы по двум столбцам REQNUMBERSEGMENT1 и REQUESTID суще­ствовал составной индекс, то ядро Oracle могло бы определить, что за­прос не возвращает строк, просто пройдя по индексу от корня к листу. Количество операций LIO, необходимых для выполнения такого про­хода, совпадает с высотой индекса. Высота индекса - это его значение BLEVEL (например, из DBA_INDEXES, для исследуемых сегментов индекса) плюс один. Высота самых гигантских индексов, о которых я когда-ли­бо слышал, не превышала семи. Следовательно, будем ожидать, что при наличии составного индекса по столбцам REQ_NUMBER_SEGMENT1 и REQUEST_ID количество операций LIO в расчете на одно выполнение не будет больше семи.

Вы, наверное, помните, что потребляемое вызовом базы данных про­цессорное время обычно пропорционально количеству выполняемых им операций LIO. И если удастся уменьшить количество операций LIO для вызова с 7047 до просто 7, то можно ожидать, что уменьшится об­щее потребление процессорного времени вызовами базы данных тоже в 1000 раз. Следовательно, можно ожидать, что уменьшение количест­ва LIO приведет к уменьшению суммарного потребления процессорно­го времени данной командой UPDATE с 1066,4 секунды до приблизительно 1 секунды. Ожидаемое улучшение приблизительно на 1000 секунд -это достаточно значительное улучшение времени отклика, которое стоит того, чтобы проверить такое предположение. Рекомендуемая деятельность по повышению производительности состоит в создании

составного индекса по столбцам REQ_NUMBER_SEGMENT1 и REQUEST_ID. Результаты

Общее время отклика программы действительно уменьшилось намного больше, чем на предполагавшиеся 1000 секунд. Такое достижение обу­словлено сопутствующими факторами, в числе которых следующие:

Второй по значимости вклад в загрузку процессора в рамках сеанса вносит команда 3277176312, в которой есть точно такая же фраза WHERE, как и в команде 704365403. Поэтому создание индекса ока­зывает потрясающий эффект на оба самых значимых компонента общего времени отклика.

Уменьшение количества LIO снижает общую рабочую нагрузку се­анса не только в смысле процессорного времени, но и в других. А именно, устранение многих обращений к буферам базы данных в сеансе обычно приводит к сокращению количества операций дис­кового чтения. Избавление от операций LIO почти всегда сопровож­дается уменьшением количества вызовов PIO. Кроме того, чем меньше вызовов LIO, тем короче может стать ожидание событий

global cache lock..., latch free и других.

Однако создание нового индекса также вызывает риск привнесения со­путствующего ущерба. В данном случае вероятность нанесения запро­су ущерба минимальна, т. к. базовая таблица является интерфейсной, и к ней обращаются лишь несколько SQL-команд приложения. Для подстраховки следует при создании нового индекса (или удалении ста­рого) перепроверить все планы выполнения приложения и убедиться, что любые их изменения, вызванные изменением схемы, не принесут вреда (это можно сделать, например, при помощи средства Project Laredo, представленного на сайте http://www. hotsos.com).

Мораль

Этот случай обращает наше внимание на следующие важные факты:

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

Сопутствующая выгода от уменьшения количества вызовов LIO мо­жет быть весьма значительной.

Создание или удаление индекса может иметь как положительные, так и отрицательные последствия. Для уменьшения риска необхо­димо проанализировать все потенциальные изменения планов вы­полнения, к которым может привести манипулирование индексами.

Коэффициент попаданий в кэш буферов базы данных для команды SQL не может выступать в качестве надежного критерия ее эффек­тивности.