Сентябрь 2005

Профессионалу администратору

Роджер Шраг,
Database Specialists, Inc.
( http://www.dbspecialists.com)

Разбор конкретного перехода на сервер Oracle 10g:
производительность системы до и после модернизации
(An Oracle 10g Upgrade Case Study:
Looking at System Performance Before and After the Upgrade Oracle 10g,
by Roger Schrag)

Источник: http://www.dbspecialists.com/presentations/case_study_10g.html .
Перевод выполнен под редакцией А.П. Соколова, компания РДТЕХ.

О фирме Database Specialists, Inc.
Фирма Database Specialists, Inc. предлагает удаленное администрирование и локальную поддержку ваших критически важных систем баз данных Oracle. С 1995 г. мы занимаемся консалтингом в области систем баз данных Oracle на платформах Solaris, HP-UX, Linux, AIX и Windows. Мы – администраторы баз данных, лекторы, преподаватели и авторы. Наши специалисты постоянно участвуют в национальных конференциях Oracle, широко известны по высокопрофессиональным публикациям. Более подробно о наших услугах см. удаленное администрирование , настройка систем баз данных , консалтинг . Или звоните: 1-415-344-0500 или 1-888-648-0500.

Предисловие к русскому переводу

Думаю, эта статья будет интересной не только тем, кто планирует перейти на сервер Oracle 10g, но и всем, кто занимается оптимизацией производительности систем баз данных Oracle 8i и 9i, поскольку в статье уделяется много внимания методам измерения производительности. Кроме того, особый интерес представляет описание организации приложения для удаленного администрирования, использованного для демонстрации процесса модернизации.

Искренне признателен Владимиру Бегун, который работает менеджером по выпуску программного обеспечения в штаб-квартире Oracle USA Inc., за внимательное прочтение текста перевода, комментарии и конструктивные замечания, позволившие улучшить качество перевода.

Перевод планируется опубликовать в двух выпусках журнала. Для удобства читателя приводится полное содержание статьи.

Содержание
Часть 1


Часть 2
  • SQL-операторы, которые в сервере Oracle 10g выполняются быстрее
  • SQL-операторы, которые в сервере Oracle 10g выполняются медленнее
  • Консультант SQL Tuning Advisor
  • Накладные расходы и использование ресурсов инфраструктуры
  • Использование оперативной памяти
  • Синтаксический разбор
  • Ресурсы, потребляемые средствами автоматизации сервера Oracle 10g
  • Заключение
  • Об авторе

Введение

В этой статье мы намерены поделиться опытом нашей компании по замене СУБД Oracle версии 8i на новую версию, Oracle 10g. С одной стороны, многие из наших коллег, администраторов баз данных, хотели бы перейти на последнюю версию сервера Oracle, чтобы воспользоваться новейшими функциональными возможностями. Но, с другой стороны, большинство из самых консервативных этих администраторов не решается перейти на отлично функционирующую систему из-за опасения столкнуться с нежелательными особенностями этого последнего предложения корпорации Oracle. В течение нескольких последних лет мы придерживались решения оставлять у себя сервер Oracle 8i – эта версия удовлетворяла все наши потребности, мы считали ее устоявшейся и стабильной. В конце 2004 г. перед нами замаячил отказ (с деталями читатель может ознакомиться в документе #161818.1 – прим. В. Бегун) от ее поддержки и мы наконец решили, что пришло время модернизации. Этот наш переход на сервер Oracle 10g мы и обсуждаем в этой статье. Если наш опыт как-то сможет помочь спланировать вашу собственную модернизацию, можно считать, что эта статья достигла своей цели.

В следующем разделе этой статьи мы рассмотрим некоторые общие результаты, полученные в процессе нашей модернизации. Мы расскажем в общих чертах о нашей среде, выбранной стратегии модернизации и о том, как в целом прошла сама модернизация. Мы не будем заострять внимание непосредственно на проведении модернизации: вы можете прочитать об этом в документации Oracle (читатель также может обратить внимание на документ #263809.1 – прим. В. Бегун). Вместо этого мы сосредоточимся на наших впечатлениях.

В третьем разделе мы рассмотрим конкретные темы, такие, как размер области SGA и оптимизация запросов, а также подробно обсудим нашу работу с сервером Oracle 10g . И вновь мы сосредоточим свое внимание на нашем опыте, а не на пересказе документации. Чтобы помочь вам понять, как мы пришли к своим выводам, мы предоставим данные, полученные из утилиты TKPROF и v$-представлений. Если вы находите эту статью пугающе длинной, пожалуйста, наберитесь терпения: большую ее часть составляют фрагменты отчетов утилиты TKPROF и другая вспомогательная информация, в которой вы, по своему усмотрению, можете покопаться или пропустить ее.

Пожалуйста, помните: каждая система базы данных Oracle уникальна и будет иметь свои собственные проблемы. Никогда не верьте на слово, если это касается технологий Oracle. В этой статье мы только делимся нашим опытом – мы не намерены делать какие-то глобальные утверждения или общие заявления о сервере Oracle 10g. Единственный способ узнать, как ваша конкретная система будет работать в среде сервера Oracle 10g, состоит в испытании ее – в тестовой, а не промышленной среде.

Наш переход на сервер Oracle 10g – взгляд с расстояния 30 000 футов

В этом разделе мы сначала дадим краткий обзор нашей среды Oracle и стратегии модернизации, чтобы ввести вас в курс дела и подготовить к дальнейшему чтению этой статьи. Затем мы поделимся нашими общими впечатлениями о процессе модернизации, совместимости, а также о самом сервере Oracle 10g. В этом разделе мы будем рассматривать все в общих чертах, в подробности мы углубимся позже.

Наша среда Oracle

Одна из услуг, предлагаемых нашей фирмой Database Specialists, – удаленное администрирование и поддержка промышленных систем баз данных Oracle. У нас есть разработанное нами же инструментальное средство мониторинга систем клиентов, которое много лет модернизировалось, чтобы помогать нам делать эту работу эффективно. На сервере базы данных каждого клиента мы инсталлируем агента, который регулярно посылает информацию (в виде агентских файлов) в сервер нашего центра обработки данных. Демон-процессы, работающие на нашем сервере, расшифровывают эти файлы, выполняют их синтаксический разбор, загружают информацию в базу данных Oracle, анализируют данные на предмет обнаружения проблем и трендов и высылают по электронной почте соответствующие уведомления. Пользовательский веб-интерфейс позволяет нам и нашим клиентам просматривать отчеты и, в случае необходимости, настраивать предпочтения и конфигурационные параметры.

Для нашей промышленной базы данных мы использовали сервер Oracle 8i версии 8.1.7. Мы работали с 32-битовой версией сервера Oracle редакции Standard Edition на платформе Sun, функционирующей под управлением 64-битовой версии ОС Solaris 8. Приблизительно 99% кода приложения, как для демон-процессов, так и для пользовательского веб-интерфейса, написаны на языке PL/SQL.

Наше приложение с самого начала было спроектировано и разработано небольшой группой опытных администраторов баз данных Oracle и разработчиков. Мы полагаем, что архитектура приложения, схемы базы данных и PL/SQL-код разработаны хорошо и обеспечивают эффективность и масштабируемость. Мы используем различные функциональные возможности, которые сегодня кажутся обыденными, но были новыми в сервере Oracle 8i, такие, как глобальные временные таблицы, массовое связывание и функция table() для запросов к PL/SQL-коллекциям так, как если бы они были таблицами базы данных.

За эти годы всякий раз, когда мы имели проблемы со временем реакции системы или обнаруживали угрозу ее масштабируемости, мы применяли стандартные методы управления производительностью, точно такие же, что и для любого из наших клиентов. Иногда в результате этого в запросах появлялись подсказки, и сегодня во всем нашем приложении имеется приблизительно 50 SQL-операторов, которые содержат подсказки оптимизатору. Вероятно, в нашем приложении все еще есть какие-то аспекты, которые теоретически являются оптимальными лишь частично, но мы не планируем заниматься ими, пока они не станут причиной появления проблем со времени реакции. Мы – занятые люди, мы не имеем времени для того, что Гаджа Вайдьяната (Gaja Vaidyanatha) называет "маниакальным настроечным расстройством" (compulsive tuning disorder).

Наша промышленная среда Oracle 8i была в высшей степени устойчивой. Мы давно изучили, как обходить немногие ошибки сервера Oracle, которые появляются в нашей системе, и обычно мы занимаемся экземпляром Oracle только несколько раз в год. Мы постоянно добавляем к приложению новые функциональные возможности, но мы делаем эту работу в среде разработки и внедряем изменения в промышленную систему только после полного тестирования. Короче говоря, сервер Oracle 8i удовлетворял наши потребности.

Наша стратегия модернизации

Мы разработали и проверили нашу стратегию модернизации, восстанавливая "горячую" резервную копию промышленной базы данных на тестовом сервере, подобному промышленному серверу, и обновляя эту базу данных до версии Oracle 10g. Ранее для выполнения обновления мы решили использовать методику экспорта-импорта. Это позволило нам перенести все табличные пространства в локально управляемые табличные пространства и компактно разместить все наши табличные и индексные сегменты. Мы, возможно, для этого могли бы использовать инструмент Database Upgrade Assistant (ассистент модернизации сервера базы данных), но нас привлекла перспектива начать со "свежего" словаря данных.

После постановки шоу "процедура модернизации" и успешной премьеры на тестовом сервере мы получили два экземпляра тестового сервера базы данных – экземпляр сервера Oracle 8i, который был точной копией промышленного экземпляра, и экземпляр сервера Oracle 10g, база данных которого содержала те же самые данные, что и промышленная. Это позволило нам прогонять все виды тестов для сравнения того, как две версии справляются с идентичными рабочими нагрузками и проблемами. (К сожалению, тот факт, что во время импорта таблицы и индексы были размещены в базе данных Oracle 10g в меньшем количестве блоков данных, приводил к искажению результатов тестирования.)

Наша промышленная база данных была не очень большой (приблизительно 15 гигабайтов), поэтому мы могли воспользоваться стратегией переноса так, что во время импорта данных в новую базу данных Oracle 10g промышленная база данных Oracle 8i оставалась открытой; позднее мы дозагрузили в базу данных Oracle 10g недостающие агентские файлы. Это позволило нам использовать методику модернизации с помощью экспорта-импорта со временем простоя, которое не превышало одного часа.

Наши общие впечатления от модернизации и совместимости

Мы обнаружили, что как сервер Oracle 10g версии 10.1.0.2, так и набор исправлений 10.1.0.3 на нашей платформе Sun Solaris 8 инсталлируются удивительно гладко. Перед запуском инсталлятора Oracle Universal Installer (OUI) потребовалось внести в операционную систему несколько исправлений, но все это было понятно описано в документации. Хорошим признаком был факт, что инсталлятор OUI перепроверял исправления нашей операционной системы.

Во время первой попытки модернизации мы столкнулись с двумя проблемами несовместимости, но они имели очень простые решения и появились весьма своевременно. Во-первых, мы должны были заново конфигурировать наши параметры настройки агентов внешних процедур EXTPROC и повторно скомпоновать наши внешние библиотеки, которые мы вызываем из PL/SQL-кода. Изменение в параметрах настройки требовалось, потому что корпорация Oracle в сервере Oracle 9i версии 9.2.0 сделала инфраструктуру EXTPROC более безопасной. Перекомпоновка была необходима, потому что наша модернизация включала в себя переход с 32-битовой версии сервера Oracle к 64-битовой.

Вторая проблема, с которой мы столкнулись, заключалась в том, что в нескольких наших хранимых процедурах при их компиляции в тестовом сервере Oracle 10g возникали внутренние ошибки PL/SQL. Мы довольно быстро выясняли, что все эти проблемные процедуры были свернуты утилитой PL/SQL Wrapper сервера Oracle 8i. Мы повторно свернули их утилитой PL/SQL Wrapper сервера Oracle 10g и они стали компилироваться без ошибок.

Эти две проблемы стали единственной помехой – в остальном все прошло очень гладко. Работая с сервером базы данных Oracle, начиная с версии 5.1 и участвуя за эти годы во многих процессах перехода на новые версии, мы были приятно поражены.

В пользовательском веб-интерфейсе для нашего приложения вызов хранимых процедур PL/SQL для обработки HTTP-запросов выполняется с помощью модуля modplsql – программного модуля, подключаемого к веб-серверу Apache. При модернизации сервера базы данных мы планировали оставить веб-сервер и модуль modplsql такими, какими они были в сервере Oracle 8i, не изменяя эту часть системы. Мы обнаружили, что наш сервер Oracle 10g прекрасно взаимодействует с клиентом Oracle 8i modplsql.

Наши общие впечатления от сервера Oracle 10g

"Коробочные" варианты сервера базы данных Oracle 10g, инструмента управления сервером базы данных Oracle Enterprise Manager Database Control и утилиты iSQLPlus работали просто замечательно . (Последние два продукта были ужасно медленными, но они делали то, что от них требовалось.) Утилита импорта сервера Oracle 10g без проблем читала наш экспортный файл сервера Oracle 8i, и наша система была модернизирована в мгновение ока практически без обычно сопутствующих этому процессу мучений и раздражения. Сервер Oracle 10g даже помог привести в порядок несколько вещей, сделать их менее сложными, например, объединение нескольких табличных пространств в одно табличное пространство SYSAUX.

Наш сервер базы данных Oracle 10g кажется таким же устойчивым, каким был и наш сервер Oracle 8i. Мы не столкнулись ни с какими ошибками ORA-600 или каким-то "странным" поведением. Конечно, мы запускаем наше программное обеспечение Oracle на платформе Solaris, которая много лет, как нам кажется, является самой устойчивой платформой для сервера Oracle. И, вероятно, более существенно то, что на этот момент в нашем приложении еще не используются никакие новые самые современные функциональные возможности, появившиеся в сервере Oracle 10g. И мы даже были очень консервативны в использовании функциональных возможностей сервера Oracle 9i, сопротивляясь введению основных усовершенствований, таких, как автоматическое управление сегментами отката, локально управляемое табличное пространство SYSTEM и файл параметров сервера (вместо файла init.ora).

Вообще говоря, мы обнаружили, что сервер Oracle 10g (как и следовало ожидать) больше по размерам и требует больше ресурсов, чем когда-либо. Исполняемый код сервера Oracle 10g увеличился, и размер области SGA тоже нужно увеличивать. Такие операции, как полные разборы и сбор статистических данных оптимизатора, потребляют больше ресурсов, чем это было раньше. Фоновые демон-процессы Oracle используют больше ресурсов, и схема SYS содержит больше объектов, чем когда-либо. Мы могли бы продолжать, но правда заключается в том, что, как мы обнаружили, рост накладных расходов сервера Oracle 10g связан с повышением управляемости.

В целом наше приложение работает в сервере Oracle 10g так же хорошо, как оно работало и в сервере Oracle 8i. Некоторые запросы выполняются быстрее, а некоторые – медленнее, но плюсы и минусы имеют тенденцию уравновешивать друг друга. Мы обнаружили в нашем приложении только несколько запросов, которые прекрасно работали в сервере Oracle 8i, но были причиной проблем производительности в сервере Oracle 10g. Относительно большинства этих запросов, в которые ранее были добавлены подсказки, мы обнаружили, что сервер Oracle 10g работает лучше без подсказок (по сравнению с работой сервера Oracle 8i без подсказок), но сервер Oracle 8i с подсказками работал значительно лучше сервера Oracle 10g без подсказок. Итак, исчезла ли потребность в подсказках для некоторых наших запросов, если сервер Oracle 10g делает все сам? Мы так не думаем.

В сервере Oracle 10g нас разочаровал консультант по настройке SQL-операторов (SQL Tuning Advisor). Примеры в документации тривиальны и, действительно, консультант выдавал осмысленные рекомендации для наших собственных простых контрольных примеров. Однако, когда мы давали консультанту проблемные запросы нашего приложения (запросы, для которых мы в прошлом разработали подсказки), он отвечал единообразно: "There are no recommendations to improve the statement" (нет никаких рекомендаций для улучшения этого оператора). Одним из проблемных запросов, когда консультант не смог нам помочь, было простое соединение двух таблиц. Мы по-прежнему так и не понимаем, почему консультант не смог найти лучший план выполнения такого относительно простого запроса, и хотели бы надеяться, что консультант SQL Tuning Advisor будет работать лучше в других средах, отличных от нашей.

В целом, переход на сервер Oracle 10g прошел для нас намного более гладко, чем мы этого ожидали. Мы были в состоянии мириться с увеличенными требованиями ресурсов, и ситуаций, когда наше приложение в сервере Oracle 10g действительно работало хуже, оказалось очень немного. Когда же мы начнем использовать новые функциональные возможности сервера, увеличенные требования ресурсов должны будут окупиться, поскольку мы ожидаем добиться от нашей системы большего, чем когда-либо прежде.

Наш переход на сервер Oracle 10g – подробности

В этом разделе мы более подробно обсудим некоторые наши впечатления от сервера Oracle 10g, а также расскажем, как наша система вела себя до и после модернизации. В частности, мы рассмотрим многие из вопросов, которые мы затрагивали в предыдущем разделе.

Размеры разделяемого пула и области SGA

Наш сервер промышленной базы данных Oracle 8i несколько лет спокойно работал с размером разделяемого пула (параметр shared_pool_size), равным 40 МБ, и общим размером области SGA, примерно равным 84 МБ. Когда мы занимаемся определением размера области SGA, мы всегда следуем методике создания вещей такого размера, какой они должны иметь. Хотя наше приложение состоит из более чем 50 000 строк PL/SQL-кода, мы не используем динамический SQL с встраиваемыми литеральными значениями. Таким образом, в то время как наш сервер базы данных выполнял в секунду в среднем 15-20 операторов, в день в среднем выполнялось меньше 660 полных разборов. Тем временем, усредненный коэффициент попадания в кеш буферов превышал 97%, а усредненный коэффициент попадания в библиотечный кеш был равен почти 100%. По этим причинам на сервере Oracle 8i нам было комфортно с размером области SGA, равным 84 МБ.

Мы знали, что наша область SGA маловата и во время перехода к серверу Oracle 10g мы должны будем увеличить ее. С другой стороны, в документе 263809.1 сайта Metalink говорится, что минимальное значение параметра shared_pool_size для сервера Oracle 10g на 64-битовых платформах равно 144 МБ. Учитывая, что наш сервер базы данных имеет 2 гигабайта оперативной памяти, это не было проблемой.

В сервере Oracle 10g для помощи в планировании модернизации предусмотрен информационный инструмент модернизации сервера (Upgrade Information Tool). Этот инструмент, по существу, представляет собой скрипт утилиты SQL*Plus, который вы запускаете на сервере существующей базы данных, и он посоветует вам, как более гладко выполнить модернизацию. Этот скрипт подсказал нам изменить значение параметра shared_pool_size на 150944944 (или, приблизительно, 144 МБ).

Ради забавы (или, скажем, в "образовательных целях") мы проверили несколько установок параметра shared_pool_size. Наш экземпляр сервера Oracle 10g не запускался со значением параметра shared_pool_size, равным 48 МБ. Увеличение этого размера до 64 МБ позволило нам открывать базу данных, но часто возникала ошибка выделения памяти ORA-4031. С размером разделяемого пула, равным 96 МБ, система, как нам казалось, функционировала нормально, но, в конечном счете, мы прислушались к рекомендации корпорации Oracle и установили 144 МБ.

Мы нашли три причины, почему сервер Oracle 10g нуждается в большем значении параметра shared_pool_size, чем в предыдущих версиях сервера Oracle. Если мы увеличили этот параметр на 260%, то не разумно говорить, что вы должны будете делать то же самое. В следующих подразделах мы рассмотрим каждый из этих трех факторов, влияющих на размер разделяемого пула.

Выделение для накладных расходов

Часть разделяемого пула "жертвуется" на хранение внутренних структур памяти, которые представляют собой накладные расходы. В серверах Oracle 8i и 9i корпорация Oracle делает фактический размер разделяемого пула больше, чем размер, заданный параметром shared_pool_size, чтобы обеспечить место для этих накладных структур. Следовательно, в серверах Oracle 8i и 9i параметр shared_pool_size определяет объем полезного пространства, желательный именно для разделяемого пула.

В сервере Oracle 10g параметр shared_pool_size задает реальный размер разделяемого пула. Часть этого пространства отводится на хранение этих накладных структур, следовательно, в сервере Oracle 10g фактическое полезное пространство в разделяемом пуле будет меньше определенного параметром shared_pool_size. Это изменение в поведении объясняется в документе 270935.1 сайта Metalink и не обязательно само по себе приводит к увеличению размера области SGA.

В нашем сервере промышленной базы данных Oracle 8i мы могли видеть, что размер разделяемого пула приблизительно на 3 МБ (примерно на 8%) больше размера, заданного в параметре shared_pool_size: 

SQL> SELECT SUM (bytes) / 1024 / 1024 actual_pool_size
  2  FROM   v$sgastat
  3  WHERE  pool = 'shared pool';

ACTUAL_POOL_SIZE
----------------
      43.1291847

SQL> SHOW PARAMETER shared_pool_size

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
shared_pool_size                     string  41943040

SQL>

Нам встречались ситуации, когда несоответствие могло быть более значительным. Например, у одного из наших клиентов, использующего сервер Oracle 9i, значение параметра shared_pool_size равно 416 МБ, а реальный размер разделяемого пула равен 528 МБ – разница в 112 МБ или, примерно, в 27%.

Использование памяти для разделяемой области SQL

В дополнение к разделяемому пулу, который в сервере Oracle 10g имеет меньше полезного пространства (при одинаковых значениях параметра shared_pool_size), можно отметить следующее: кажется, что в сервере Oracle 10g отдельные SQL-операторы занимают больше места в разделяемой области SQL (shared SQL area) – в нашем случае по сравнению с сервером Oracle 8i почти в два раза. Мы обнаружили это, выполняя запросы к столбцам sharable_mem, runtime_mem и persistent_mem представления v$sql. (Помните: у нас 32-битовая версия сервера Oracle 8i, а наш сервер Oracle 10g имеет 64-битовое программное обеспечение, поэтому это как-то влияет на размер.)

Мы нагрузили серверные демон-процессы нашего приложения типичным объемом работы с нашими тестовыми базами данных Oracle 8i и 10g, в то время как сами серверы баз данных были свободны. После этого мы выполнили запросы к представлению v$sql. Эти демон-процессы выполняют пакеты PL/SQL в схеме DBRX_OWNER.

Посмотрим в нашем тестовом сервере Oracle 8i на использование памяти для операторов, синтаксический разбор которых выполнялся в схеме DBRX_OWNER: 

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username = 'DBRX_OWNER'
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
DBRX_OWNER          362    6,275,020        256,176   1,996,324   8,527,520

SQL>

Посмотрим в нашем тестовом сервере Oracle 10g: 

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username = 'DBRX_OWNER'
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
DBRX_OWNER          360   12,941,006        487,048   3,361,160  16,789,214

SQL>

Эти два сервера баз данных имели в разделяемой области SQL примерно одинаковое количество операторов приложения, но в сервере Oracle 10g для них потребовалось почти в два раза больше памяти.

Можно предположить, что при переходе на сервер Oracle 10g размер разделяемого пула должен быть удвоен, но мы не хотим делать какие-либо общие умозаключения. В нашем случае мы должны были более чем утроить значение параметра shared_pool_size, чтобы обеспечить выполнение требования минимального размера разделяемого пула в сервере Oracle 10g, следовательно, дополнительная память, используемая для каждого SQL-оператора, не была для нас проблемой.

На сайте Metalink мы не нашли какого-либо обсуждения увеличения размера разделяемой области SQL. Мы знаем только, что такое увеличение, возможно, происходит, если сравнивать серверы Oracle 8i и 9i. Мы также не знаем, насколько это увеличение связано с заменой 32-битового программного обеспечения на 64-битовое.

SQL-операторы, генерируемые сервером Oracle

В разделяемой области SQL любого экземпляра Oracle вы найдете операторы, которые были выполнены пользовательскими приложениями, а также операторы, которые были выполнены непосредственно самим сервером Oracle. Иногда последние называют "рекурсивным SQL" или "SYS SQL". Независимо от того, как вы называете их, кажется, что в сервере Oracle 10g эта категория операторов поднялась на целый новый уровень.

После тестирования рабочей нагрузки, рассмотренного в предыдущем разделе, мы выполнили запросы к представлению v$sql, чтобы посмотреть, какие операторы, выполненные не приложением, находятся в разделяемой области SQL. В сервере тестовой базы данных Oracle 8i мы видим: 

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username IN ('DBSNMP', 'SYS', 'SYSTEM', 'SYSMAN')
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
SYS                 192    2,331,619        125,356     569,688   3,026,663
SYSTEM               30      810,325         19,644     163,480     993,449
                        ------------ -------------- ----------- -----------
sum                        3,141,944        145,000     733,168   4,020,112

SQL>

В сервере тестовой базы данных Oracle 10g мы видим: 

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username IN ('DBSNMP', 'SYS', 'SYSTEM', 'SYSMAN')
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
DBSNMP               99    4,161,758        137,504   1,701,032   6,000,294
SYS                 695   24,402,627      1,024,744   8,103,496  33,530,867
SYSMAN              670   16,644,400        806,904   4,403,720  21,855,024
SYSTEM               14      533,442         18,152     290,280     841,874
                        ------------ -------------- ----------- -----------
sum                       45,742,227      1,987,304  14,498,528  62,228,059

SQL>

Это одно из мест в сервере базы данных, где вы можете видеть плату за услуги средств автоматизации – автоматически управляемый репозиторий рабочей нагрузки (AWR, Automatic Workload Repository), монитор автоматической диагностики сервера базы данных (ADDM, Automatic Database Diagnostic Monitor), инструментарий Enterprise Manager (EM) и т.д. SQL-код, генерируемый нашим тестовым сервером Oracle 10g, занимает на порядок (по сравнению с нашим тестовым сервером Oracle 8i) больше места в разделяемой области SQL. В нашем тестовом сервере Oracle 10g SQL-код, сгенерированный сервером, занимает больше места, чем весь код нашего приложения, которое содержит незначительное число SQL-операторов.

Чтобы быть справедливыми, мы должны указать, что наш сервер Oracle 8i имеет стандартную редакцию СУБД (Standard Edition) с минимумом инсталлированных опций и работает без инструментария Enterprise Manager. А наш сервер Oracle 10g имеет корпоративную редакцию СУБД (Enterprise Edition) с инсталлированными инструментом Enterprise Manager Database Control и набором опций "по умолчанию". Следовательно, это точно не сравнение "яблок с яблоками".

Сбор статистических данных оптимизатора и их точность

В нашем сервере промышленной базы данных Oracle 8i каждое воскресенье ночью мы, используя процедуру dbms_job, запускаем задание, которое обновляет статистические данные оптимизатора по всем таблицам и индексам нашего приложения. Эти данные собираются с помощью оператора ANALYZE типа: 

ANALYZE TABLE имя_таблицы ESTIMATE STATISTICS SAMPLE 5 PERCENT;

Эта схема хорошо работала в течение нескольких лет, поэтому мы никогда не пытались перейти на использование пакета dbms_stats.

Сервер Oracle 10g имеет встроенное задание очереди заданий с именем GATHER_STATS_JOB, предназначенное для сбора статистических данных всей базы данных. Это задание использует пакет dbms_stats и собирает статистические данные только по тем объектам, для которых эти данные отсутствуют или считаются устаревшими. Мы не смогли найти в документации более или менее точного описания того, как определяется объем выборки и как выбираются столбцы таблиц для построения гистограмм.

Для тестирования мы в наших серверах Oracle 8i и 10g удалили статистические данные оптимизатора по всем объектам в схеме DBRX_OWNER. Затем мы трассировали процесс очереди заданий Oracle, когда он снова собирал эти данные. Мы обнаружили, что в нашей системе сбор статистических данных в сервере Oracle 10g – намного более ресурсоемкая операция, чем это было в сервере Oracle 8i. Естественно, сервер Oracle 10g собирает статистические данные в окне времени, которое вы задаете (по умолчанию с 22:00 до 6:00), и только по тем объектам, для которых эти данные отсутствуют или считаются устаревшими. Сводка информации из трассировочных файлов:

Ресурсы, использованные для сбора
статистических данных оптимизатора

Oracle 8i
 (ANALYZE) 

Oracle 10g
 (автоматически) 

Секунды центрального процессора

1,101

2,595

Всего секунд

2,044

5,244

Логические чтения

597,717

73,082,675

Физические чтения

545,844

2,926,625

Одна из причин большего потребления ресурсов сервера Oracle 10g связана с гистограммами. Наши операторы ANALYZE в сервере Oracle 8i не создавали никаких гистограмм, а сервер Oracle 10g захотел построить гистограммы для более чем 20% столбцов нашей схемы DBRX_OWNER: 

SQL> SELECT   histogram, COUNT(*)
  2  FROM     user_tab_columns
  3  GROUP BY histogram;

HISTOGRAM         COUNT(*)
--------------- ----------
FREQUENCY              267
HEIGHT BALANCED         74
NONE                  1202
                ----------
sum                   1543

SQL>

Нам кажется, будто сервер Oracle 10g хотел создавать частотные гистограммы (FREQUENCY histogram) для столбцов, которые имели низкую кардинальность значений, и сбалансированные по высоте гистограммы (HEIGHT BALANCED histogram) для столбцов с перекосами или промежутками в распределении данных. Наличие или отсутствие индексов по столбцам, казалось, никак не влияло на создание гистограмм. Сервер Oracle поддерживает в схеме SYS таблицу col_usage$, где он отслеживает, какие столбцы таблиц появляются в предложении WHERE и как они используются. Сервер Oracle не будет создавать гистограмму по столбцу, если он никогда не использовался в предложении WHERE.

В нашей среде многие из гистограмм, которые создавал сервер Oracle, не кажутся полезными, поскольку в некоторых случаях столбцы почти никогда не появлялись в предложении WHERE. В других случаях, столбцы, по которым были созданы гистограммы, не были проиндексированы и появлялись только в предложении WHERE вместе с проиндексированными столбцами этой же таблицы. Итак, мы полагаем, что в нашей системе сервер Oracle пошел на большие затраты ради создания ненужных гистограмм. Чтобы быть справедливыми, заметим однако, что серверу Oracle было бы чрезвычайно трудно точно установить, какие гистограммы выгодны или не выгодны.

Другая возможная причина большего потребления ресурсов сервером Oracle 10g при сборе статистических данных могла быть связана с объемом выборки. В операторах ANALYZE, которые мы использовали в сервере Oracle 8i, задан объем выборки, равный 5 процентам. Просматривая столбец sample_size в представлении user_tables нашего тестового сервера Oracle 8i, мы могли видеть, что на самом деле объем выборки для таблиц с размером более 1 МБ почти всегда находился между 4.5% и 5.4%. Для таблиц с размером меньше 1 МБ сервер Oracle делал выборку для всех строк.

Как мы упоминали ранее, мы не смогли найти в документации никакой информации, как при сборе статистических данных оптимизатора сервер Oracle 10g определяет те или иные объемы выборок (за исключением того, что это делается автоматически). Вот то, что мы действительно обнаружили в нашем тестовом сервере Oracle 10g:

  • сервер Oracle решил выбирать каждую строку таблицы с 6 346 638 строками, которая занимала сегмент размером 760 МБ. Для этого, вероятно, потребовалось намного больше ресурсов, чем необходимо;
  • сервер Oracle решил выбирать только 0.40% строк таблицы с 14 938 632 строками, которая занимала сегмент размером 1 088 МБ. Это приводило к довольно значительной потере точности статистических данных, о чем мы раскажем немного позже;
  • в большинстве случаев, сервер Oracle выбирал каждую строку таблиц, меньших приблизительно 90 МБ, и выбирал 6% или меньше строк в больших таблицах;
  • для нескольких наших таблиц сервер Oracle выбирал 23-25% строк. Это казалось нам странным, поскольку здесь можно было бы выбирать все строки.

Здесь показано, как мы собирали вышеприведенную информацию в нашем тестовом сервере Oracle 10g: 

SQL> SELECT   A.table_name, A.num_rows, B.bytes / 1024 / 1024 mb,
  2           100 * (A.sample_size / A.num_rows) sample_pct
  3  FROM     user_tables A, user_segments B
  4  WHERE    A.table_name IN
  5          ('SAMPLE_DATA_FILES', 'SAMPLE_JOBS',
  6           'COMMON_SQL_PLAN_PARTS', 'SAMPLE_SQL_TEXTS',
  7           'SAMPLE_LIBRARY_CACHE_STATS')
  8  AND      B.segment_type = 'TABLE'
  9  AND      B.segment_name = A.table_name
 10  ORDER BY sample_pct;

TABLE_NAME                    NUM_ROWS         MB SAMPLE_PCT
-------------------------- ----------- ---------- ----------
SAMPLE_DATA_FILES           14,938,632   1,088.00        0.4
SAMPLE_JOBS                  1,360,429      54.00        4.1
COMMON_SQL_PLAN_PARTS          174,851       9.00        6.9
SAMPLE_LIBRARY_CACHE_STATS   1,414,830      80.00      100.0
SAMPLE_SQL_TEXTS             6,346,638     760.00      100.0

SQL>

Кажется, что сервер Oracle начинает с очень маленьких выборок из каждой средней или большой таблицы, постепенно производя большие выборки для тех столбцов, в которых обнаруживаются перекосы, промежутки в распределении данных или низкая кардинальность. Например, в трассировочном файле мы можем увидеть, с каким неимоверным трудом сервер Oracle собрал статистические данные для таблицы размером 760 МБ, когда, в конечном счете, была произведена выборка каждой строки:

  • сначала, сервер Oracle произвел выборку для всех 35 столбцов таблицы на 0.0892929621 процентах строк;
  • затем, сервер Oracle произвел выборку для восьми столбцов таблицы на 0.8929296209 процентах строк;
  • затем, сервер Oracle произвел выборку для трех столбцов таблицы на 8.9292962091 процентах строк;
  • наконец, сервер Oracle выполнил операцию COUNT (DISTINCT) на одном из столбцов без предложения SAMPLE, то есть был выполнен полный просмотр таблицы, и на этом шаге была прочитана каждая строка таблицы.

Во многих случаев сервер Oracle 10g недостаточно точно оценивал число уникальных значений в столбце. В большинстве случаев оператор ANALYZE в тестовом сервере Oracle 8i давал более точную оценку. Некоторые из неточных оценок, возможно, произошли из-за размеров выборок, которые были слишком маленькими, но это только предположение.

Имейте в виду: статистические данные оптимизатора не обязательно должны быть точными, чтобы быть эффективными. Поэтому мы в первую очередь оцениваем их, а не вычисляем. Однако возникает вопрос: "Насколько точными должны быть статистические данные?" Если сервер Oracle выбирает приемлемый план выполнения каждого запроса, то статистические данные достаточно точны. Но если некоторые запросы имеют неприемлемые планы выполнения, трудно сказать, что причина заключается в неточных статистических данных. Нас заинтересовало то, что в нашей среде выбор сервером Oracle 10g объема выборки был настолько странен. Приведет ли это в действительности к возникновению проблем, мы не можем сказать. (В следующем разделе мы увидим, что сервер Oracle 10g выбирал плохие планы выполнения некоторых наших запросов, но мы не можем просто определить, виноваты ли в этом неточные статистические данные оптимизатора.)

Оптимизация запросов

В веб-средствах генерации отчетов и логике серверных демон-процессов нашего приложения скрываются некоторые довольно сложные SQL-операторы. Однако рабочая нагрузка нашего сервера базы данных формируется по модели OLTP, и все SQL-операторы в системе выполняются очень быстро. В нашем промышленном сервере Oracle 8i редко встречаются какие-либо SQL-операторы, выполняемые дольше долей секунды, за исключением ежеквартального процесса чистки данных.

Мы полагаем, что проделали разумную работу, написав во всем приложении практичные, логичные SQL-операторы. В большинстве случаев сервер Oracle 8i не имел никаких проблем с выбором эффективных планов выполнения наших запросов. В тех немногих местах, где сервер Oracle 8i выбирал неэффективные планы выполнения, которые приводили к истинным проблемам со временем реакции, мы добавили подсказки, улучшавшими планы. За эти годы мы добавили подсказки приблизительно к 50 SQL-операторам во всем нашем приложении.

Оптимизация запросов была главной причиной нашего беспокойства при переходе к серверу Oracle 10g. Приложение содержит в себе много SQL-операторов и действительно плохой выбор плана выполнения почти любого запроса мог значительно ухудшить пропускную способность системы и время ее реакции. Было бы лучше, если бы сервер Oracle 10g смог выполнять наши запросы быстрее, но о чем мы действительно волновались, так это о запросах, которые станут выполняться медленнее. Если модуль приложения состоит из 100 SQL-операторов, и только один из них свернет на "большую дорогу", это может стать заметной проблемой.

Мы обнаружили в нашей тестовой среде, что большинство SQL-операторов нашего приложения потребляло примерно то же самое время центрального процессора, а число логических чтений на сервере Oracle 10g было таким же, как и на сервере Oracle 8i. Некоторые операторы выполнялись чуть быстрее, а немногие – несколько медленнее. Только несколько SQL-операторов на сервере Oracle 10g выполнялось достаточно медленно, что вызывало беспокойство. В целом, большинство операций рабочей нагрузки демонстрировало похожие периоды времени реакции в обеих версиях сервера Oracle. Поскольку наше приложение состоит прежде всего из многих быстро работающих SQL-операторов, мы не ожидали, что "коробочная" версия сервера Oracle 10g сильно повысит производительность. Этого и не произошло.

Как только мы выяснили, что у нас имеется только несколько проблемных запросов, мы заинтересовались, действительно ли серверу Oracle 10g нужны были подсказки, которые за эти годы мы добавили к нашим самым хитроумным запросам. Интересно, будет ли сервер Oracle 10g достаточно сообразительным, чтобы найти эффективный план выполнения, который не мог найти сервер Oracle 8i? Мы обнаружили, что в некоторых случаях наш тестовый сервер Oracle 10g выполнял проблемные запросы с удаленными подсказками намного быстрее нашего тестового сервера Oracle 8i (также с удаленными подсказками), но оба тестовых сервера могли выполнять проблемные запросы со вставленными подсказками еще быстрее. Другими словами, мы обнаружили, что в некоторых случаях сервер Oracle 10g работал с нашими проблемными запросами лучше сервера Oracle 8i, но разработанные нами подсказки давали намного лучшие результаты, чем сервер Oracle 10g мог придумать сам.

В следующих разделах мы рассмотрим, что из себя представляют эти проблемы оптимизации запросов и какие мы сделали выводы.

SQL-операторы, которые выполняются одинаково в серверах Oracle 8i и 10g

Демон-процесс загрузки в нашем приложении разбирает и проверяет файлы, полученные от наших контролирующих агентов, и загружает данные в базу данных для последующего анализа и генерации отчетов. Этот демон-процесс загрузки представляет собой пакет PL/SQL размером примерно в 7 800 строк. Семь SQL-операторов пакета имеют подсказки.

Используя в тестовых серверах Oracle 8i и 10g одни и те же данные, мы трассировали этот демон-процесс в каждом сервере во время загрузки одного и того же агентского файла. Некоторые операторы выполнялись на одном сервере по сравнению с другим быстрее на 0.01 секунды времени центрального процессора, в то время как некоторые операторы выполняли немного меньше операций логического чтения. Однако различие казалось почти случайным шумом, который компенсировал сам себя. Вот итоговые статистические данные того, что мы наблюдали:

Ресурсы, использованные демон-процессом загрузки
для загрузки одного агентского файла

 Oracle 8i 

 Oracle 10g 

Оттрассированные пользовательские SQL-операторы

110

127

Оттрассированные внутренние SQL-операторы

9

9

Оттрассированные уникальные SQL-операторы

109

110

Всего OCI-вызовов

1,800

1,792

Секунды центрального процессора

3.13

3.12

Логические чтения

13,767

12,920

Физические чтения

6

13

Число пользовательских оттрасированных SQL-операторов и число логических чтений в сервере Oracle 8i достаточно сильно отличаются от сервера Oracle 10g, и это нужно обсудить. Различие в логических чтениях легко объяснить: как мы упоминали ранее, таблицы и индексы в нашем тестовом сервере Oracle 10g были размещены более компактно. Это позволяло сэкономить некоторые логические чтения, поскольку данные, которые должны были быть прочитаны, занимали меньше блоков.

Различие в оттрасированных SQL-операторах объяснить немного сложнее. Машина PL/SQL поддерживает кеш курсоров. Если хранимая процедура закрывает курсор, машина PL/SQL скрытно сохраняет его в открытом состоянии, чтобы можно было избежать дополнительного вызова разбора при повторном выполнении этого же оператора. В сервере Oracle 8i размером кеша курсоров PL/SQL управляет параметр open_cursors, а в сервере Oracle 10g (и в сервере Oracle 9i версии 9.2.0.5 и более поздних версиях) –параметр session_cached_cursors. Это изменение поведения объясняется в документе 274496.1 сайта Metalink.

В сервере Oracle 10g значение по умолчанию параметра session_cached_cursors равно 0, в этом случае машина PL/SQL кеширует 50 курсоров. В нашем тестовом сервере Oracle 8i значение параметра open_cursors было равно 300, и представление v$open_cursor показывало, что в нашем сеансе демон-процесса считалось открытыми 117 курсоров. Поскольку кеш курсоров PL/SQL в нашем тестовом сервер Oracle 10g был меньше, курсоры вытеснялись из кеша по времени. Это приводило к большему числу запросов разбора, чем это было необходимо, и заставляло утилиту TKPROF сообщать о большем числе пользовательских SQL-операторов в трассировочном файле Oracle 10g. (Заметим, эти дополнительные разборы были неполными и поэтому не были очень дороги.)

Для тех, кому интересно: итоговый раздел в конце отчета утилиты TKPROF тестового сервера Oracle 8i выглядит так: 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PL/SQL lock timer                               5        5.13         20.46


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      119      0.09       0.08          0          0          0           0
Execute   1079      2.83       3.30          6       1936       9315        2115
Fetch      602      0.21       0.12          0       1892        624         586
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1800      3.13       3.50          6       3828       9939        2701

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  file open                                       3        0.00          0.00
  db file sequential read                         6        0.02          0.07

  110  user  SQL statements in session.
    9  internal SQL statements in session.
  119  SQL statements in session.
********************************************************************************
Trace file: 8i_load.trc
Trace file compatibility: 8.00.04
Sort options: execpu
       1  session in tracefile.
     110  user  SQL statements in trace file.
       9  internal SQL statements in trace file.
     119  SQL statements in trace file.
     109  unique SQL statements in trace file.
   46637  lines in trace file.
      22  elapsed seconds in trace file.

Итоговый раздел в конце отчета утилиты TKPROF тестового сервера Oracle 10g выглядит так: 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PL/SQL lock timer                               6        5.00         19.73
  HS message to agent                            11        0.06          0.13


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      136      0.05       0.04          0          0          0           0
Execute   1082      2.95       4.20         13       1954       9205        2115
Fetch      574      0.12       0.27          0       1761          0         572
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1792      3.12       4.51         13       3715       9205        2687

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                          2        0.02          0.03
  latch: library cache                            1        0.01          0.01
  latch: shared pool                              1        0.00          0.00

  127  user  SQL statements in session.
    9  internal SQL statements in session.
  136  SQL statements in session.
********************************************************************************
Trace file: 10g_load.trc
Trace file compatibility: 10.01.00
Sort options: execpu
       1  session in tracefile.
     127  user  SQL statements in trace file.
       9  internal SQL statements in trace file.
     136  SQL statements in trace file.
     110  unique SQL statements in trace file.
   36351  lines in trace file.
      21  elapsed seconds in trace file.

Наш отчет Performance Summary (сводный отчет о производительности) представляет собой средства генерации отчета в пользовательском веб-интерфейсе, которые обеспечивают, наряду со строками отчета пакета Statspack, подготовку сводки по многим статистическим показателям для одного контролируемого сервера базы данных Oracle в заданном интервале времени. Эти средства реализованы как пакет PL/SQL размером приблизительно в 3 200 строк, который по входным параметрам извлекает из базы данных нужную информацию, помещает результаты в глобальную временную таблицу, чтобы другой модуль приложения искал в ней и представлял то, что нужно.

Мы закодировали простую "заглушку", чтобы вызывать этот пакет с фиксированным набором параметров. Используя в тестовых серверах Oracle 8i и 10g одни и те же данные, мы трассировали вызовы этой "заглушки". И снова некоторые операторы выполнялись на одном сервере по сравнению с другим немного быстрее, в то время как некоторые операторы выполняли немного меньше операций логического чтения. Вот итоговые статистические данные того, что мы наблюдали:

Ресурсы, использованные для генерации
сводного отчета о производительности

 Oracle 8i 

 Oracle 10g 

Оттрассированные пользовательские SQL-операторы

98

98

Оттрассированные внутренние SQL-операторы

10

10

Оттрассированные уникальные SQL-операторы

98

97

Всего OCI-вызовов

654

531

Секунды центрального процессора

0.89

0.88

Логические чтения

4,641

3,661

Физические чтения

1

0

И снова различие в логических чтениях может быть приписано тому факту, что в сервере Oracle 10g таблицы и индексы были размещены более компактно. Но на этот раз число пользовательских SQL-операторов в обоих серверах одинаково, поскольку в этом отчете нет SQL-операторов, многократно выполняемых в цикле, как это было в демон-процессе загрузки.

Также значительно различие в OCI-вызовах. В трассировочных файлах обоих серверов содержалось одно и то же число вызовов разбора (parse) и выполнения (execute), в то время как сервер Oracle 8i имел в два раза больше вызовов выборки (fetch), чем сервер Oracle 10g (246 против 123). Из-за этого может казаться, что в сервере Oracle 8i машина PL/SQL выполняет дополнительные вызовы выборки, чтобы определить, все ли строки были извлечены, в то время как сервер Oracle 10g, чтобы избежать дополнительного вызова, возможно, запрашивает больше строк при первом вызове.

Для тех, кому интересно: итоговый раздел в конце отчета утилиты TKPROF тестового сервера Oracle 8i выглядит так: 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.11       0.10          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.11       0.10          0          0          0           1

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      105      0.05       0.10          0          0          0           0
Execute    298      0.39       0.42          1       1631        657         204
Fetch      246      0.34       0.46          0       2263         90         236
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      649      0.78       0.98          1       3894        747         440

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  file open                                       1        0.00          0.00
  db file sequential read                         1        0.00          0.00

   98  user  SQL statements in session.
   10  internal SQL statements in session.
  108  SQL statements in session.
********************************************************************************
Trace file: rpt1-8ib.trc
Trace file compatibility: 8.00.04
Sort options: fchqry
       1  session in tracefile.
      98  user  SQL statements in trace file.
      10  internal SQL statements in trace file.
     108  SQL statements in trace file.
      98  unique SQL statements in trace file.
    5424  lines in trace file.
       1  elapsed seconds in trace file.

Итоговый раздел в конце отчета утилиты TKPROF тестового сервера Oracle 10g выглядит так: 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.21       0.17          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.21       0.18          0          0          0           1

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      105      0.04       0.02          0          0          0           0
Execute    298      0.39       0.43          0       1134        650         205
Fetch      123      0.24       0.25          0       1877          0         334
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      526      0.67       0.71          0       3011        650         539

Misses in library cache during parse: 0

   98  user  SQL statements in session.
   10  internal SQL statements in session.
  108  SQL statements in session.
********************************************************************************
Trace file: rpt1-10gb.trc
Trace file compatibility: 10.01.00
Sort options: fchqry
       1  session in tracefile.
      98  user  SQL statements in trace file.
      10  internal SQL statements in trace file.
     108  SQL statements in trace file.
      97  unique SQL statements in trace file.
    4504  lines in trace file.
       0  elapsed seconds in trace file.

Окончание статьи – в следующем выпуске OM/RE.

E-mail this page