Думаю, эта статья будет интересной не только тем, кто планирует перейти на сервер Oracle 10g, но и всем, кто занимается оптимизацией производительности систем баз данных Oracle 8i и 9i, поскольку в статье уделяется много внимания методам измерения производительности. Кроме того, особый интерес представляет описание организации приложения для удаленного администрирования, использованного для демонстрации процесса модернизации.
Искренне признателен Владимиру Бегун, который работает менеджером по выпуску программного обеспечения в штаб-квартире Oracle USA Inc., за внимательное прочтение текста перевода, комментарии и конструктивные замечания, позволившие улучшить качество перевода.
Перевод планируется опубликовать в двух выпусках журнала. Для удобства читателя приводится полное содержание статьи.
Об авторе
SQL-операторы, которые в сервере Oracle 10g выполняются быстрее
Ко всем запросам, которые выполнялись ощутимо медленно, мы уже давно добавили подсказки, поэтому в сервере Oracle 8i все функции приложения уже выполнялись гладко и "достаточно быстро". Поэтому мы не ожидали, что что-то в сервере Oracle 10g будет выполняться заметно быстрее. Действительно, при переходе к серверу Oracle 10g мы не смогли обнаружить никакого меньшего времени реакции или большей пропускной способности нашей системы.
Когда мы удалили подсказки из операторов нашего приложения, которые в сервере Oracle 8i мы оптимизировали вручную, мы обнаружили, что сервер Oracle 10g по сравнению с сервером Oracle 8i обычно мог сам находить лучший план выполнения. Однако мы обнаружили, что в некоторых случаях план выполнения, выбранный сервером Oracle 10g был все еще хуже плана выполнения, сгенерированного с помощью добавленных нами подсказок. Рассмотрим теперь два примера.
Один запрос, который появляется при генерации нескольких отчетов, регенерирует список последних уведомлений о событиях для всех баз данных, к которым указанный пользователь имеет доступ. Запрос соединяет семь таблиц. Это далеко не самый сложный запрос, когда-либо написанный, но он также и нетривиален. Чтобы эффективно выполнять этот запрос в сервере Oracle 8i, мы добавили подсказку, чтобы указать серверу на необходимость доступа к ведущей таблице по индексу, на порядок соединения других таблиц и на алгоритм каждого соединения (хеш-соединение или вложенные циклы). Запрос получился следующим:
SELECT /*+ ORDERED INDEX (privs) USE_NL (i s ar acr) USE_HASH (t l) */
t.test_severity_id severity, i.instance_id,
NVL (privs.instance_nickname, i.current_instance_name) inst_name,
ar.first_detected, t.short_description brief_description,
l.report_section_id
FROM customer_user_instance_privs privs, customer_instances i,
samples s, analysis_results ar, analysis_common_results acr,
analysis_tests t, lookup_report_40000_formats l
WHERE privs.user_id = :cp_user_id
AND privs.current_cust_user_priv_level IN ('admin', 'read only')
AND i.instance_id = privs.instance_id
AND privs.user_wishes_to_see = 'y'
AND s.instance_id = i.instance_id
AND s.sample_type IN ('ping', 'full_stat')
AND s.sample_date_db_local_time >
(
SELECT s2.sample_date_db_local_time -
(i.display_events_for_so_many_hrs / 24)
FROM samples s2
WHERE s2.sample_id =
rpt_util.most_recent_analyzed_sample (i.instance_id)
)
AND ar.sample_id = s.sample_id
AND acr.analysis_common_result_id = ar.analysis_common_result_id
AND t.test_id = acr.test_id
AND t.alert_type = 'event'
AND l.test_id = t.test_id
ORDER BY severity, first_detected DESC, inst_name;
Данные в таблицах, на которые ссылается этот запрос, никогда, как мы могли заметить, не бывают "перекошенными". Мы выполняли этот запрос в наших тестовых серверах Oracle 8i и 10g с типичным значением переменной связывания, и вот резюме того, что мы увидели:
|
Ресурсы, использованные запросом
последних уведомлений о событиях |
Запрос с подсказкой |
Запрос без подсказки |
|
Oracle 8i |
Oracle 10g |
Oracle 8i |
Oracle 10g |
|
Секунды центрального процессора |
0.10 |
0.09 |
51.84 |
2.91 |
|
Логические чтения |
2,208 |
1,451 |
1,678,011 |
4,111 |
|
Физические чтения |
7 |
0 |
27,551 |
0 |
Как видите, без подсказки сервер Oracle 10g работает с этим запросом намного лучше сервера Oracle 8i. К сожалению, выбор сервером Oracle 10g плана выполнения все еще далек от совершенства. Добавление подсказки уменьшило время центрального процессора на 97%, а количество операций логического чтения на 65%.
Для тех, кому интересно: сервер Oracle 10g смог выполнить версию запроса с подсказкой быстрее сервера Oracle 8i. Это произошло потому, что сервер Oracle 10g переместил подзапрос в начало плана выполнения и, таким образом, скорее отфильтровывал больше строк. Без подсказки сервер Oracle 8i вытолкнул подзапрос на более дальнее и позднее по времени место плана выполнения, тогда как подзапрос эффективно уменьшал возможный результирующий набор с почти миллиона строк до несколько дюжин. Сервер Oracle 10g без подсказки разумно выполнил подзапрос как можно скорее, но, к сожалению, выбрал хеш-соединение с таблицей размером более 800 000 строк, когда возможный результирующий набор имел только 300 строк, и можно было выполнить соединение вложенными циклами с поиском в уникальном индексе.
Для тех, кому интересно: план выполнения запроса без подсказки в тестовом сервере Oracle 8i был следующим:
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
0 SORT (ORDER BY)
0 FILTER
7093 HASH JOIN
71 TABLE ACCESS MODE: ANALYZED (FULL) OF
'LOOKUP_REPORT_40000_FORMATS'
7092 HASH JOIN
4 TABLE ACCESS MODE: ANALYZED (FULL) OF
'ANALYSIS_TESTS'
512382 HASH JOIN
512382 NESTED LOOPS
832470 HASH JOIN
465504 HASH JOIN
41 TABLE ACCESS MODE: ANALYZED (FULL) OF
'CUSTOMER_INSTANCES'
465504 TABLE ACCESS MODE: ANALYZED (FULL) OF
'SAMPLES'
832469 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'ANALYSIS_RESULTS_PK' (UNIQUE)
512382 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'CUSTOMER_USER_INST_PRIVS_PK' (UNIQUE)
126110 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'ANALYSIS_COMMON_RESULTS_N1' (NON-UNIQUE)
42 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'SAMPLES'
42 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SAMPLES_PK'
(UNIQUE)
План выполнения запроса без подсказки в тестовом сервере Oracle 10g был следующим. (Заметим, встроенная функция PL/SQL rpt_util.most_recent_analyzed_sample () выполнила 101 операцию логического чтения. Поэтому в этом плане выполнения показывается в общей сложности 4 212 логических чтений, а не 4 111, упомянутых выше.)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=4212 pr=0 pw=0 time=3573213 us)
0 HASH JOIN (cr=4212 pr=0 pw=0 time=3573077 us)
71 TABLE ACCESS FULL LOOKUP_REPORT_40000_FORMATS (cr=3 pr=0 pw=0 time=489 us)
0 HASH JOIN (cr=4209 pr=0 pw=0 time=3562005 us)
4 TABLE ACCESS FULL ANALYSIS_TESTS (cr=18 pr=0 pw=0 time=853 us)
243 HASH JOIN (cr=4191 pr=0 pw=0 time=3554047 us)
126110 INDEX FAST FULL SCAN ANALYSIS_COMMON_RESULTS_N1 (cr=341 pr=0 pw=0 time=126363 us)(object id 49302)
243 HASH JOIN (cr=3850 pr=0 pw=0 time=2830427 us)
343 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=391 pr=0 pw=0 time=19666 us)
359 NESTED LOOPS (cr=292 pr=0 pw=0 time=578919 us)
15 NESTED LOOPS (cr=58 pr=0 pw=0 time=1791 us)
41 TABLE ACCESS FULL CUSTOMER_INSTANCES (cr=15 pr=0 pw=0 time=759 us)
15 INDEX UNIQUE SCAN CUSTOMER_USER_INST_PRIVS_PK (cr=43 pr=0 pw=0 time=1588 us)(object id 49663)
343 INLIST ITERATOR (cr=234 pr=0 pw=0 time=40802 us)
343 INDEX RANGE SCAN SAMPLES_UK2 (cr=234 pr=0 pw=0 time=40979 us)(object id 49504)
14 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=147 pr=0 pw=0 time=33644 us)
14 INDEX UNIQUE SCAN SAMPLES_PK (cr=133 pr=0 pw=0 time=33165 us)(object id 49501)
832469 INDEX FAST FULL SCAN ANALYSIS_RESULTS_PK (cr=3459 pr=0 pw=0 time=1665167 us)(object id 49571)
Другой запрос, который многократно появляется при генерации отчетов, находит число транзакций, которые были выполнены для указанной базы данных за указанный период времени. Запрос соединяет шесть таблиц. Чтобы эффективно выполнять этот запрос в сервере Oracle 8i, мы добавили подсказку, чтобы указать серверу порядок соединения таблиц и алгоритм каждого соединения (всегда вложенные циклы). Запрос получился следующим:
SELECT /*+ ORDERED USE_NL (D A C E F) */
A.sample_id, A.sample_date_db_local_time,
C.value + E.value transaction_count,
F.startup_time_db_local_time
FROM (
SELECT common_stat_name_id
FROM common_stat_names
WHERE name = 'user rollbacks'
) B,
(
SELECT common_stat_name_id
FROM common_stat_names
WHERE name = 'user commits'
) D,
samples A, sample_sysstats C,
sample_sysstats E, sample_instance_info F
WHERE A.instance_id = :cp_instance_id
AND A.sample_sequence BETWEEN :cp_sequence_start AND :cp_sequence_end
AND A.sample_type = 'full_stat'
AND C.sample_id = A.sample_id
AND C.common_stat_name_id = B.common_stat_name_id
AND E.sample_id = A.sample_id
AND E.common_stat_name_id = D.common_stat_name_id
AND F.sample_id (+) = A.sample_id
ORDER BY A.sample_sequence;
И снова данные в таблицах, на которые ссылается этот запрос, никогда, как мы могли заметить, не бывают особенно "перекошенными". Мы выполняли этот запрос в наших тестовых серверах Oracle 8i и 10g с типичными значениями переменных связывания, и вот резюме того, что мы увидели:
|
Ресурсы, использованные запросом
подсчета числа транзакций |
Запрос с подсказкой |
Запрос без подсказки |
|
Oracle 8i |
Oracle 10g |
Oracle 8i |
Oracle 10g |
|
Секунды центрального процессора |
0.01 |
0.01 |
0.71 |
0.40 |
|
Логические чтения |
290 |
226 |
20,514 |
8,256 |
|
Физические чтения |
0 |
0 |
0 |
0 |
И снова тестовый сервер Oracle 10g работал с этим запросом намного лучше сервера Oracle 8i. К сожалению, выбор сервером Oracle 10g плана выполнения по-прежнему далек от совершенства. Добавление подсказки уменьшило время, использованное центральным процессором, и количество операций логического чтения на 97%.
Как упоминалось ранее, таблицы и индексы в нашем тестовом сервере Oracle 10g были размещены более компактно. Поэтому сервер Oracle 10g смог выполнить запрос с подсказкой, используя меньше логических чтений по сравнению с сервером Oracle 8i. План выполнения этой версии запроса был одинаковым в обоих тестовых серверах.
Для тех, кому интересно: оба сервера Oracle при выполнении запроса без подсказки слишком поздно выполняли соединение с таблицей common_stat_names. Хотя в ней находились сотни различных имен статистических показателей, а этому запросу требовалось только два имени. Но без подсказки серверы Oracle для каждой выборки сначала извлекали все статистические данные и позднее отбрасывали ненужные строки. Сервер Oracle 10g сумел сделать это быстрее сервера Oracle 8i, потому что он использовал лучший порядок соединения и в одном случае уместно выбирал хеш-соединения, а не вложенные циклы.
Для тех, кому интересно: план выполнения запроса без подсказок в тестовом сервере Oracle 8i был следующим:
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
34 NESTED LOOPS
7957 NESTED LOOPS
35 NESTED LOOPS
7957 NESTED LOOPS
35 NESTED LOOPS (OUTER)
35 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'SAMPLES'
35 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'SAMPLES_UK1'
(UNIQUE)
34 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'SAMPLE_INSTANCE_INFO_PK' (UNIQUE)
7990 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'SAMPLE_SYSSTATS_PK' (UNIQUE)
7990 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'COMMON_STAT_NAMES_UK1' (UNIQUE)
7990 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'SAMPLE_SYSSTATS_PK'
(UNIQUE)
34 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'COMMON_STAT_NAMES_UK1'
(UNIQUE)
План выполнения этого запроса без подсказок в тестовом сервере Oracle 10g был следующим:
Rows Row Source Operation
------- ---------------------------------------------------
34 SORT ORDER BY (cr=8256 pr=0 pw=0 time=399893 us)
34 FILTER (cr=8256 pr=0 pw=0 time=374969 us)
34 NESTED LOOPS OUTER (cr=8256 pr=0 pw=0 time=374828 us)
34 NESTED LOOPS (cr=8186 pr=0 pw=0 time=372718 us)
7956 NESTED LOOPS (cr=194 pr=0 pw=0 time=55490 us)
34 HASH JOIN (cr=104 pr=0 pw=0 time=38388 us)
1 INDEX RANGE SCAN COMMON_STAT_NAMES_PK (cr=2 pr=0 pw=0 time=120 us)(object id 49622)
7956 NESTED LOOPS (cr=102 pr=0 pw=0 time=24098 us)
34 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=12 pr=0 pw=0 time=1475 us)
34 INDEX RANGE SCAN SAMPLES_UK1 (cr=3 pr=0 pw=0 time=77 us)(object id 49503)
7956 INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=90 pr=0 pw=0 time=25624 us)(object id 49777)
7956 INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=90 pr=0 pw=0 time=33206 us)(object id 49777)
34 INDEX RANGE SCAN COMMON_STAT_NAMES_UK1 (cr=7992 pr=0 pw=0 time=3015 71 us)(object id 49623)
34 INDEX UNIQUE SCAN SAMPLE_INSTANCE_INFO_PK (cr=70 pr=0 pw=0 time=1279 us)(object id 49746)
SQL-операторы, которые в сервере Oracle 10g выполняются медленнее
К счастью, мы обнаружили в нашем приложении только несколько запросов, которые в сервере Oracle 10g выполнялись заметно медленнее. В одном случае это была генерация отчета, для которой использовалось нескольких дюжин запросов, которые в сервере Oracle 10g по сравнению с сервером Oracle 8i потребляли больше времени центрального процессора более чем в два раза, и выполняли логических чтений на порядок больше. Проблема всецело была связана с одним очень невинно выглядящим запросом:
SELECT B.value
FROM common_stat_names A, sample_sysstats B
WHERE A.name = :p_statname
AND B.common_stat_name_id = A.common_stat_name_id
AND B.sample_id = :p_sample_id;
Этот запрос извлекает значение одного указанного статистического показателя представления v$sysstat из одной указанной выборки данных (sample). (Термин "sample" в нашем приложении аналогичен термину "snapshot" в пакете Statspack или автоматически управляемом репозитории рабочей нагрузки.) Этот запрос настолько прост, что не имеет много возможных планов выполнения. Один способ выполнять его заключается в том, чтобы искать указанное имя статистического показателя в таблице common_stat_names, а затем выполнять соединение с таблицей sample_sysstats. Доступ к таблице common_stat_names можно осуществлять через лидирующий столбец уникального индекса, а доступ к таблице sample_sysstats – через уникальный индекс. Мы назовем это "лучшим" планом выполнения.
Другой способ выполнения этого запроса заключается в том, чтобы извлечь все 200 с лишним строк таблицы sample_sysstats, соответствующим указанному идентификатору выборки sample_id, и для каждой из них найти строку в таблице common_stat_names v$sysstat, чтобы увидеть, соответствуют ли им имена. Мы назовем это "неудачным" планом выполнения.
В наших промышленной и тестовых средах сервер Oracle 8i без подсказки выбирал лучший план выполнения. Удивительно, но тестовый сервер Oracle 10g выбрал неудачный план выполнения. При генерации отчета этот запрос выполняется больше 50 раз, плохой выбор планов выполнения заметно влияет на время реакции.
Фрагмент отчета утилиты TKPROF (включая план выполнения) в тестовом сервере Oracle 8i выглядит так:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 47 (DBRX_OWNER)
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
2 INDEX RANGE SCAN (object id 8383)
1 INDEX UNIQUE SCAN (object id 8514)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
1 NESTED LOOPS
2 INDEX MODE: ANALYZED (RANGE SCAN) OF 'COMMON_STAT_NAMES_PK'
(UNIQUE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SAMPLE_SYSSTATS_PK'
(UNIQUE)
Соответствующий фрагмент отчета утилиты TKPROF в тестовом сервере Oracle 10g выглядит так:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 244 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 244 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57 (DBRX_OWNER)
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=244 pr=0 pw=0 time=893 us)
234 INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=5 pr=0 pw=0 time=1152 us)(object id 49777)
1 INDEX RANGE SCAN COMMON_STAT_NAMES_UK1 (cr=239 pr=0 pw=0 time=9472 us)(object id 49623)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 NESTED LOOPS
234 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SAMPLE_SYSSTATS_PK'
(INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF 'COMMON_STAT_NAMES_UK1'
(INDEX (UNIQUE))
Вы можете сказать, что разница периода времени, использованного центральным процессором, составляющая 0.01 секунды, сомнительна из-за погрешностей измерения времени, использованного центральным процессором сервера Oracle. Однако на практике разница была весьма существенной, поскольку при генерации отчета запрос выполнялся очень много раз.
Добавление к запросу подсказки ORDERED заставило сервер Oracle 10g выбирать лучший план выполнения. Это представляло собой очень простое изменение кода приложения. В учебных целях мы сначала, чтобы заставить сервер Oracle использовать лучший план выполнения без необходимости изменения кода приложения, попробовали использовать консультанта SQL Tuning Advisor с его опцией профилирования. Но об этом более подробно в следующем разделе. Следует еще указать, что в этом запросе обе таблицы – индекс-таблицы, и, возможно, оптимизатор сервера Oracle 10g "страдает" от этого.
Консультант SQL Tuning Advisor
Сервер Oracle 10g является счастливым обладателем целого семейства консультантов, которые претендуют на автоматизацию многих задач, часто отнимающих массу времени у администраторов баз данных. Один из них заинтересовал нас больше всего – консультант по настройке SQL-операторов SQL Tuning Advisor.. Вероятно потому, что приблизительно одна треть работы нашей фирмы связана с оптимизацией производительности систем баз данных Oracle. (Если функциональные возможности, встроенные в сервер Oracle 10g, собираются сделать ненужным одно из моих наиболее ходовых умений, то я хочу знать об этом!)
Наше приложение, на самом деле, работало на сервере Oracle 10g гладко. Как упоминалось ранее, за эти годы мы добавили подсказки к запросам, которые сервер Oracle 8i, в противном случае, не мог выполнять эффективно, и обнаружили очень мало запросов, которые в сервере Oracle 10g выполнялись медленнее чем в сервере Oracle 8i. Итак, мы решили предложить консультанту SQL Tuning Advisor некоторые из наших вручную оптимизированных запросов без подсказок и посмотреть, что он придумает.
API-интерфейс для консультанта SQL Tuning Advisor продуман основательно. Вы можете получить доступ к нему через инструмент Enterprise Manager Database Control или вызывая PL/SQL-пакет dbms_sqltune. Вы можете, среди прочих опций, попросить консультанта рассмотреть явный запрос, предоставляемый вами; запрос, который в настоящее время находится в разделяемой области SQL или в автоматически управляемом репозитории рабочей нагрузки. Вы задаете консультанту временной предел, а затем даете команду начинать анализ. Когда анализ завершается, вы для получения отчета консультанта вызываете функцию PL/SQL.
В документации говорится, что в некоторых случаях консультант предлагает рекомендуемый альтернативный план выполнения, и вы можете сохранить в словаре данных "профиль", чтобы при последующих выполнениях этого SQL-оператора альтернативные данные, собранные консультантом, могли быть приняты во внимание. Цель здесь заключается в том, чтобы заставить сервер Oracle выполнять SQL-оператор более эффективно без необходимости изменения текста SQL-оператора в приложении. Идея кажется великолепной.
Первый запрос, заданный нами консультанту SQL Tuning Advisor, был запросом, который мы обсуждали ранее. Мы удалили использованную в нем подсказку и добавили комментарий, чтобы легко найти запрос в разделяемой области SQL. Мы указали консультанту затратить на анализ запроса до десяти минут, но он закончил свою работу ровно через 14 секунд. К сожалению, он не смог сказать ничего полезного:
SQL> SELECT dbms_sqltune.report_tuning_task
2 ('Tuning case 47696', 'TEXT', 'ALL', 'ALL')
3 FROM SYS.dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47696','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Tuning case 47696
Tuning Task ID : 951
Scope : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at : 01/27/2005 13:42:34
Completed at : 01/27/2005 13:42:48
-------------------------------------------------------------------------------
SQL ID : b6c2qka14951z
SQL Text: SELECT t.test_severity_id severity, i.instance_id,
NVL (privs.instance_nickname, i.current_instance_name)
inst_name,
ar.first_detected, t.short_description brief_description,
l.report_section_id /* tuning case 47696 */
FROM customer_user_instance_privs privs, customer_instances i,
samples s, analysis_results ar, analysis_common_results
acr,
analysis_tests t, lookup_report_40000_formats l
WHERE privs.user_id = :cp_user_id
AND privs.current_cust_user_priv_level IN ('admin', 'read
only')
AND i.instance_id = privs.instance_id
AND privs.user_wishes_to_see = 'y'
AND s.instance_id = i.instance_id
AND s.sample_type IN ('ping', 'full_stat')
AND s.sample_date_db_local_time >
(
SELECT s2.sample_date_db_local_time -
(i.display_events_for_so_many_hrs / 24)
FROM samples s2
WHERE s2.sample_id =
rpt_util.most_recent_analyzed_sample (i.instance_id)
)
AND ar.sample_id = s.sample_id
AND acr.analysis_common_result_id =
ar.analysis_common_result_id
AND t.test_id = acr.test_id
AND t.alert_type = 'event'
AND l.test_id = t.test_id
ORDER BY severity, first_detected DESC, inst_name
-------------------------------------------------------------------------------
There are no recommendations to improve the statement
(нет никаких рекомендаций для улучшения этого оператора).
-------------------------------------------------------------------------------
SQL>
В документации сервера Oracle 10g и документах сайта Metalink говорится, что консультанту SQL Tuning Advisor SQL потребуется дополнительное время, необходимое на рассмотрение планов выполнения, которые нельзя было рассмотреть во время обычного разбора из-за ограничений времени, что консультант будет делать динамическую выборку данных и более тщательно, чем при разборе, проверять распределение данных, и что дополнительное время позволяет консультанту найти лучшие планы выполнения, чем это можно было бы сделать во время разбора. В данном случае мы знаем, что лучший план выполнения существует (в промышленном сервере наш подсказка гарантирует это), но консультант бросил заниматься анализом через 14 секунд.
Итак, мы дали консультанту намного более простой запрос:
SELECT B.value /* tuning case 47694 */
FROM common_stat_names A, sample_sysstats B
WHERE A.name = :p_statname
AND B.common_stat_name_id = A.common_stat_name_id
AND B.sample_id = :p_sample_id;
Мы также обсуждали этот запрос в предыдущем разделе. Как мы отмечали, если сервер Oracle начинает с таблицы common_stat_names, он может выполнять поиск по индексу очень селективного столбца, а затем – по уникальному индексу таблицы sample_sysstats. Наши промышленный и тестовый серверы Oracle 8i выбирали этот план выполнения, но наш тестовый сервер Oracle 10g по каким-то причинам настаивал на том, что начинать нужно с просмотра диапазона по индексу таблицы sample_sysstats. Плану выполнения сервера Oracle 10g требовалось 244 логических чтений, в то время как лучшему плану –только 6. (Различие существенно, поскольку в промышленной системе этот запрос выполнялся очень часто.)
Итак, мы полагали, что это будет легкой задачей для консультанта SQL Tuning Advisor – в запросе используются только две таблицы, одна из которых очень мала. К сожалению, консультант бросил анализировать сразу же после одной секунды:
SQL> SELECT dbms_sqltune.report_tuning_task
2 ('Tuning case 47694', 'TEXT', 'ALL', 'ALL')
3 FROM SYS.dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47694','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Tuning case 47694
Tuning Task ID : 950
Scope : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at : 01/27/2005 13:32:02
Completed at : 01/27/2005 13:32:03
-------------------------------------------------------------------------------
SQL ID : g5pqqgcuq8pma
SQL Text: SELECT B.value /* tuning case 47694 */
FROM common_stat_names A, sample_sysstats B
WHERE A.name = :p_statname
AND B.common_stat_name_id = A.common_stat_name_id
AND B.sample_id = :p_sample_id
-------------------------------------------------------------------------------
There are no recommendations to improve the statement
(нет никаких рекомендаций для улучшения этого оператора).
-------------------------------------------------------------------------------
SQL>
Нас это очень обескуражило. Чтобы дать консультанту еще один шанс, мы переписали запрос, используя литеральные значения, и попросили консультанта оценить конкретный экземпляр запроса:
SQL> DECLARE
2 v_sql_text VARCHAR2(1000);
3 v_task_name VARCHAR2(100);
4 BEGIN
5 v_sql_text := '
6 SELECT B.value
7 FROM common_stat_names A, sample_sysstats B
8 WHERE A.name = ''user commits''
9 AND B.common_stat_name_id = A.common_stat_name_id
10 AND B.sample_id = 575783';
11 v_task_name := dbms_sqltune.create_tuning_task
12 (
13 sql_text=>v_sql_text, user_name=>'DBRX_OWNER',
14 scope=>'COMPREHENSIVE', time_limit=>600,
15 task_name=>'Tuning case 47725', description=>NULL
16 );
17 dbms_output.put_line ('Created tuning task ' || v_task_name || ' at ' ||
18 TO_CHAR (SYSDATE, 'hh24:mi:ss'));
19 dbms_sqltune.execute_tuning_task (v_task_name);
20 dbms_output.put_line ('Executed tuning task ' || v_task_name || ' at ' ||
21 TO_CHAR (SYSDATE, 'hh24:mi:ss'));
22 END;
23 /
Created tuning task Tuning case 47725 at 15:09:12
Executed tuning task Tuning case 47725 at 15:09:13
PL/SQL procedure successfully completed.
SQL>
К сожалению, консультант SQL Tuning Advisor по-прежнему не выдал никакого результата:
SQL> SELECT dbms_sqltune.report_tuning_task
2 ('Tuning case 47725', 'TEXT', 'ALL', 'ALL')
3 FROM SYS.dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47725','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Tuning case 47725
Tuning Task ID : 956
Scope : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at : 01/27/2005 15:09:12
Completed at : 01/27/2005 15:09:13
-------------------------------------------------------------------------------
SQL ID : 3kt66qm84bcnz
SQL Text: SELECT B.value
FROM common_stat_names A, sample_sysstats B
WHERE A.name = 'user commits'
AND B.common_stat_name_id = A.common_stat_name_id
AND B.sample_id = 575783
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
(нет никаких рекомендаций для улучшения этого оператора).
-------------------------------------------------------------------------------
SQL>
Здесь мы решили, что что-то делаем не так, и написали простой запрос с очевидным изъяном, чтобы посмотреть, справится ли с ним консультант SQL Tuning Advisor. На этот раз консультант выдал отчет, имеющий смысл:
SQL> SELECT dbms_sqltune.report_tuning_task
2 ('Tuning case 47702', 'TEXT', 'ALL', 'ALL')
3 FROM SYS.dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47702','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Tuning case 47702
Tuning Task ID : 952
Scope : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at : 01/27/2005 13:51:45
Completed at : 01/27/2005 13:51:57
-------------------------------------------------------------------------------
SQL ID : 9cz4z8xvtxbm1
SQL Text: SELECT instance_id, sample_type, sample_date_db_local_time
/* tuning case 47702 */
FROM samples
WHERE sample_id + 1 = :sample_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate "SAMPLES"."SAMPLE_ID"+1=:B1 used at line ID 1 of the execution
plan contains an expression on indexed column "SAMPLE_ID". This expression
prevents the optimizer from selecting indices on table
"DBRX_OWNER"."SAMPLES".
Recommendation
--------------
Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3806118825
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4656 | 122K| 2375 (4)| 00:00:29 |
| 1 | TABLE ACCESS FULL| SAMPLES | 4656 | 122K| 2375 (4)| 00:00:29 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / SAMPLES@SEL$1
-------------------------------------------------------------------------------
SQL>
Столбец Sample_id – первичный ключ таблицы данных выборок. Добавляя в предложении WHERE константу к значению этого столбца, мы запретили использовать индекс и вынудили сервер Oracle выполнять полный просмотр таблицы. Если бы мы вместо этого вычитали константу из переменной связывания, сервер Oracle смог бы выполнить вместо полного просмотра таблицы поиск по уникальному индексу.
Мы собирались загружать в тестовую базу данных Oracle 10g данные, экспортируемые из некоторых сайтов наших клиентов, и использовать консультанта SQL Tuning Advisor, чтобы сравнить его рекомендации с вариантами оптимизации запросов, которые мы разработали для этих клиентов. Но когда консультант не смог помочь нам с нашими собственными запросами, мы решили потратить наше время на что-то другое.
Концепции, стоящие за консультантом SQL Tuning Advisor, весьма убедительные и имеют большой потенциал. Но пока консультант не сможет выдавать полезные результаты в реальных ситуациях, он останется просто интересной темой беседы. Мы должны указать, что наша оценка консультанта SQL Tuning Advisor никоим образом не была всесторонней. Мы дали ему несколько SQL-операторов, которые в нашей среде были ресурсоемкими, а он не смог помочь нам. Но это могло произойти из-за аномалии в нашей среде или даже пользовательской ошибки.
Накладные расходы и использование ресурсов инфраструктуры
Как и следовало ожидать, СУБД Oracle 10g имеет больший объем и более сложна, чем предыдущие версии СУБД Oracle. Инфраструктура и внутренние механизмы СУБД Oracle сложнее, чем когда-либо прежде, и этот уровень сложности предъявляет больше требований к ресурсам и накладным расходам.
Наш промышленный сервер Oracle 8i имел 2 303 объекта в схеме SYS и 100 МБ дискового пространства, выделенного сегментам табличного пространства SYSTEM. Наш сервер Oracle 10g имеет более 21 000 объектов в схеме SYS и почти 800 МБ, выделенными в табличных пространствах SYSTEM и SYSAUX. Это сравнение точно не "яблок с яблоками", потому что мы работаем с сервером Oracle 8i в редакции Standard Edition с минимумом инсталлированных опций, а наш сервер Oracle 10g – в редакции Enterprise Edition с набором по умолчанию опций, инсталлированных для пусковой базы данных, поставляемой с СУБД. Но все же вы получаете представление о порядке увеличения сложности.
Чтобы дать представление о накладных расходах в среде нашего сервера Oracle 10g мы сосредоточимся на обсуждении использования памяти, синтаксического разбора и потребления ресурсов средствами автоматизации сервера Oracle 10g.
Использование оперативной памяти
Мы обсуждали ранее, что в сервере Oracle 10g мы должны были увеличить значение параметра shared_pool_size. Общий размер нашей области SGA увеличился примерно с 84 МБ в сервере Oracle 8i почти до 200 МБ в сервере Oracle 10g. (Большая часть этого увеличения приходилась на разделяемый пул; мы не изменяли размер кеша буферов.)
Кроме дополнительной памяти, требуемой для области SGA, мы обнаружили, что серверные процессы также использовали больше памяти. Наше приложение не имеет большого количества параллельных сеансов, поэтому мы используем архитектуру выделенного сервера. Некоторые статистические данные использования памяти в процессах выделенного сервера Oracle были следующими:
|
Процессы выделенного сервера Oracle |
Oracle 8i |
Oracle 10g |
|
Размер резидентного набора процесса Oracle |
97 МБ |
224 МБ |
|
Общий размер виртуальной памяти процесса Oracle |
121 МБ |
301 МБ |
|
Размер области SGA из представления v$sgastat |
84 МБ |
197 МБ |
|
Размер исполняемого кода Oracle |
32 МБ |
95 МБ |
Мы измеряли использование памяти процессами Oracle с помощью команды ОС Solaris "prstat" и проверяли с помощью утилиты "top". Оба этих инструмента включают размер области SGA в размер виртуальной памяти каждого процесса Oracle. Чтобы избегать двойного подсчета, мы вычитали размер области SGA из размера виртуальной памяти. Даже после этого было ясно: в сервере Oracle 10g серверные процессы используют больше памяти, чем такие же процессы в сервере Oracle 8i. Мы должны указать, что использовали 32-битовую версию сервера Oracle 8i (в 64-битовой среде ОС Solaris), а для сервера Oracle 10g на платформе Solaris доступна только 64-битовая версия.
Переход с 32-битовой версии на 64-битовую, вероятно, частично несет ответственность за увеличение размера используемой памяти и исполняемого кода Oracle. Кроме того, переход от редакции Standard Edition (без многих опций) к редакции Enterprise Edition (с большим количеством опций), вероятно, также частично несет ответственность за увеличение размера исполняемого кода Oracle.
Синтаксический разбор
Полные разборы в сервере Oracle всегда были дороги. Это одна из причин, почему администраторы баз данных советуют разработчикам использовать переменные связывания, и, возможно, поэтому в сервере Oracle 8i появился параметр cursor_sharing. По нашему опыту, в сервере Oracle 10g "поднята планка" стоимости полного разбора.
В одном из предыдущих разделов этой статьи мы обсуждали демон-процесс загрузки в нашем приложении, пакет PL/SQL размером в 7 800 строк, который в ходе чтения, синтаксического разбора и загрузки в базу данных одного агентского файла выполняет примерно 100 SQL-операторов. Код нашего демон-процесса не содержит каких-либо операторов динамического SQL с встраиваемыми литералами, поэтому после обработки первого агентского файла все последующие разборы в сеансах демон-процесса загрузки будут неполными.
Для лучшего понимания накладных расходов при разборе SQL-операторов мы запустили экземпляр нашего тестового сервера Oracle 8i и трассировали демон-процесс загрузки, когда он обрабатывал свой первый агентский файл. Как и следовало ожидать, примерно каждый оператор подвергался полному разбору. Затем мы обработали второй агентский файл и трассировали в другой трассировочный файл демон-процесс загрузки. На этот раз каждый разбор был неполным. Сравнивая два трассировочных файла, мы могли получить приблизительное представление об объеме работы, которую сервер Oracle выполнял при полных разборах. Затем мы повторили наше упражнение с теми же самыми агентскими файлами в нашем тестовом сервере Oracle 10g.
Как мы обсуждали ранее, когда никакие полные разборы не требовались, демон-процесс загрузки потреблял очень небольшой объем ресурсов наших тестовых серверов Oracle 8i и 10g. Однако когда полные разборы требовались, различие в использовании ресурсов было существенным. В следующей таблице подведены итоги этого нашего тестирования:
|
Ресурсы, использованные демон-процессом загрузки |
Агентский файл 1 (полный разбор) |
Агентский файл 2 (неполный разбор) |
Различие |
|
Oracle 8i |
Oracle 10g |
Oracle 8i |
Oracle 10g |
Oracle 8i |
Oracle 10g |
|
Оттрассированные пользовательские SQL-операторы |
110 |
127 |
110 |
127 |
0 |
0 |
|
Оттрассированные внутренние SQL-операторы |
402 |
977 |
9 |
9 |
393 |
968 |
|
Оттрассированные уникальные SQL-операторы |
139 |
149 |
109 |
110 |
30 |
39 |
|
Всего OCI-вызовов |
9,094 |
10,754 |
1,800 |
1,784 |
7,294 |
8,970 |
|
Секунды центрального процессора |
7.49 |
10.94 |
3.10 |
3.09 |
4.39 |
7.85 |
|
Логические чтения |
26,776 |
27,373 |
13,763 |
12,912 |
13,013 |
14,461 |
|
Физические чтения |
695 |
959 |
8 |
13 |
687 |
946 |
Мы полагаем, что большинство различий в использовании ресурсов для обработки первого и второго агентского файла может быть приписано объему работы, требуемой серверу Oracle для полного разбора всех операторов. (Некоторые различия могут быть связаны с несовершенством проектирования тестирования. Например, обработка первого агентского файла, конечно, столкнулась с большим количеством непопаданий в кеш буферов.)
Если наши представления верны, то показанная выше таблица наводит на мысль, что в данном случае нашему тестовому серверу Oracle 10g требуется почти в два раза больше времени центрального процессора (и более чем вдвое больше внутренних SQL-операторов), чтобы выполнить полный разбор операторов кода нашего демон-процесса загрузки. В нашей среде мы платим за полный разбор только один раз – при первом выполнении операторов после перезапуска экземпляра – и это не влияет на все дальнейшие выполнения операторов в течение срока жизни экземпляра. Поэтому увеличенная стоимость полного разбора не беспокоила нас. Однако мы полагали, что на это стоит обратить внимание.
Для тех, кому интересно: итоговый раздел в конце отчета утилиты TKPROF тестового сервера Oracle 8i для первого прогона демон-процесса загрузки выглядит так:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 502 1.84 1.64 0 1540 1 0
Execute 3251 4.83 8.67 243 2953 9393 2115
Fetch 5341 0.82 3.57 452 12265 624 4268
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9094 7.49 13.88 695 16758 10018 6383
Misses in library cache during parse: 129
Misses in library cache during execute: 3
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 664 0.11 4.67
file open 6 0.00 0.00
db file scattered read 5 0.02 0.06
sort segment request 1 1.04 1.04
110 user SQL statements in session.
402 internal SQL statements in session.
512 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.
402 internal SQL statements in trace file.
512 SQL statements in trace file.
139 unique SQL statements in trace file.
70921 lines in trace file.
33 elapsed seconds in trace file.
Итоговый раздел в конце отчета утилиты TKPROF тестового сервера Oracle 10g для первого прогона демон-процесса загрузки выглядит так:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1103 0.56 0.66 0 68 0 0
Execute 3656 9.21 11.46 346 4907 9247 2115
Fetch 5995 1.17 3.54 613 13151 0 5363
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10754 10.94 15.67 959 18126 9247 7478
Misses in library cache during parse: 149
Misses in library cache during execute: 137
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 514 0.17 3.63
db file scattered read 88 0.03 0.86
127 user SQL statements in session.
977 internal SQL statements in session.
1104 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.
977 internal SQL statements in trace file.
1104 SQL statements in trace file.
149 unique SQL statements in trace file.
72971 lines in trace file.
37 elapsed seconds in trace file.
Итоговый раздел в конце отчета утилиты TKPROF для второго прогона демон-процесса загрузки в основном похож на раздел, представленный ранее в разделе "Оптимизация запросов", и мы не будем его здесь повторять.
Ресурсы, потребляемые средствами автоматизации сервера Oracle 10g
Говорилось, что сервер Oracle 10g, возможно, следовало назвать сервером Oracle 10a, где "a" означает "автоматический". Действительно, в любой момент времени кажется, что в экземпляре сервера Oracle 10g развивается кипучая деятельность – "крутятся" различные демон-процессы, управляя за вас экземпляром или, по меньшей мере, собирая информацию, которую вы, надо надеяться, сочтете полезной.
ADDM, ASH, AWR, ASM, ASMM и EM – больше чем только аббревиатуры названий функциональных возможностей сервера Oracle 10g. За каждой стоят серверные процессы, которые, чтобы сделать свою работу, потребляют ресурсы вашего сервера базы данных. Мы хотели, в частности, лучше понять, к каким накладным расходам это приводит. Мы также хотели знать, нужно ли при переходе на сервер Oracle 10g увеличивать мощность центрального процессора или пропускную способность дисковой подсистемы нашего сервера базы данных.
Наша тестовая среда была установлена на платформе Sun Solaris, которая была специально выделена для этого проекта модернизации сервера Oracle. На этой платформе не запускалось ничего, кроме тестовых серверов Oracle 8i и 10g, используемых в этом проекте. Следовательно, мы могли запускать и останавливать экземпляры серверов баз данных Oracle и сервисы по своему желанию.
Для сбора статистических данных об использовании центрального процессора мы запускали инструмент ОС Unix "sar". Сначала для получения базового уровня измерений мы остановили все процессы Oracle в сервере базы данных, чтобы не работало абсолютно ничего, связанного с сервером Oracle. Инструмент sar выводил следующее:
SunOS blue 5.8 Generic_108528-27 sun4u 02/02/05
02:00:03 %usr %sys %wio %idle
02:05:03 0 4 0 96
02:10:03 0 4 0 96
02:15:03 0 4 0 96
02:20:03 0 4 0 96
02:25:03 0 4 0 96
02:30:03 0 4 0 96
02:35:03 0 4 0 96
02:40:03 0 4 0 96
02:45:03 0 4 0 96
02:50:03 0 4 0 96
02:55:03 0 4 0 96
03:00:03 0 4 0 96
Average 0 4 0 96
Затем мы запустили тестовый сервер Oracle 10g и инструмент Enterprise Manager Database Control. Мы не запускали серверные демон-процессы нашего приложения или веб-сервер, который позволяет пользователям получать доступ к базе данных через пользовательский веб-интерфейс. Насколько нам известно, ничто не работало в сервере базы данных, кроме тестового сервера Oracle 10g, и подключений пользователей приложения не было. Теперь вывод инструмента sar была похож на следующее:
SunOS blue 5.8 Generic_108528-27 sun4u 02/02/05
13:00:05 %usr %sys %wio %idle
13:05:05 5 6 3 87
13:10:05 3 6 2 89
13:15:05 3 6 4 88
13:20:05 3 6 2 89
13:25:05 4 6 2 89
13:30:05 4 6 2 88
13:35:05 3 6 2 89
13:40:05 3 5 2 89
13:45:05 3 6 2 90
13:50:05 3 6 2 89
13:55:05 4 6 2 89
14:00:05 4 6 2 88
Average 3 6 2 89
Общее время простоя упало с 96% (когда не работали никакие процессы Oracle) до 89% (когда работали сервер Oracle 10g и инструмент Enterprise Manager Database Control). Это наводит на мысль, что сервер Oracle в нашем однопроцессорном тестовом сервере потребляет около 7% времени центрального процессора. Это число оказалось ниже, чем мы ожидали. Вряд ли нам нужно думать о добавлении в наш промышленный сервер еще одного центрального процессора для поддержки накладных расходов автоматизации.
Для сравнения мы остановили тестовый сервер Oracle 10g и запустили тестовый сервер Oracle 8i. Инструмент sar выводил следующее:
SunOS blue 5.8 Generic_108528-27 sun4u 02/03/05
02:00:03 %usr %sys %wio %idle
02:05:03 1 4 1 94
02:10:03 0 4 1 95
02:15:03 0 4 0 95
02:20:03 0 4 1 95
02:25:03 0 4 0 95
02:30:03 0 4 0 96
02:35:03 0 4 0 95
02:40:03 0 4 1 96
02:45:03 0 4 0 95
02:50:03 0 4 1 95
02:55:03 0 4 0 95
03:00:03 0 4 0 96
Average 0 4 0 95
Общее время простоя упало с 96% (когда не работали никакие процессы Oracle) до 95% (когда работал сервер Oracle 8i). Итак, в нашей среде казалось, что фоновые процессы сервера Oracle 8i использовали примерно 1% времени центрального процессора сервера, когда сервер базы данных простаивал (по сравнению с 7% сервера Oracle 10g).
Мы не знаем в действительности, насколько точным был этот тест, поскольку мы не знаем, насколько точен инструмент sar, это могло быть связано и с несовершенством проектирования тестирования. Другое существенное неизвестное – постоянны или нет накладные расходы фоновых процессов сервера Oracle. Кажется, накладные расходы должны увеличиваться, когда сервер базы данных будет активен, поскольку, например, репозиторий Automatic Workload Repository будет сохранять больше содержательных данных и каждую секунду будет выполняться мониторинг большего числа активных сеансов (Active Session History). Однако это только наше предположение.
Мы сгенерировали отчет репозитория Automatic Workload Repository (по существу, это новая инкарнация того, что мы называем отчетом пакета Statspack) для периода времени, в течение которого сервер Oracle 10g и инструмент Enterprise Manager Database Control работали, но без активности пользователей. Инструмент sar сообщил нам о потреблении фоновыми процессами сервера Oracle 10g примерно 7% времени одного центрального процессора, когда сервер базы данных простаивает. За один час это составило бы 252 секунды.
В отчете же Automatic Workload Repository сообщается только о 49 секундах времени центрального процессора. Мы не знаем, на что пошла остальная часть времени центрального процессора, но что-то, возможно, пошло на работу процессов сервера Oracle, которая не отслеживается самим сервером. Какое-то несоответствие, возможно, было вызвано погрешностью измерения или несовершенством проектирования тестирования. В отчете также указано, что простаивающий сервер базы данных в течение часа без какой бы то ни было пользовательской активности сгенерировал приблизительно 8 МБ журнальных данных и выполнил более 27 000 операторов.
Для тех, кому интересно: первая часть отчета репозитория Automatic Workload Repository выглядит так:
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DBRX10g 3211571339 dbrx10g 1 10.1.0.3.0 NO blue
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 435 02-Feb-05 13:00:58 30 16.4
End Snap: 436 02-Feb-05 14:00:35 30 16.4
Elapsed: 59.62 (mins)
DB Time: 1.04 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 40M Std Block Size: 8K
Shared Pool Size: 144M Log Buffer: 256K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,428.02 12,371.52
Logical reads: 40.81 207.95
Block changes: 16.62 84.70
Physical reads: 0.31 1.58
Physical writes: 0.90 4.60
User calls: 3.23 16.44
Parses: 1.50 7.63
Hard parses: 0.01 0.03
Sorts: 0.62 3.13
Logons: 0.04 0.22
Executes: 7.59 38.65
Transactions: 0.20
% Blocks changed per Read: 40.73 Recursive Call%: 83.66
Rollback per transaction%: 8.26 Rows per Sort: 15.17
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait%: 100.00 Redo NoWait%: 100.00
Buffer Hit %: 99.25 In-memory Sort%: 100.00
Library Hit %: 99.96 Soft Parse%: 99.66
Execute to Parse%: 80.26 Latch Hit%: 100.00
Parse CPU to Parse Elapsd%: 83.40 % Non-Parse CPU: 95.61
Shared Pool Statistics Begin End
------ ------
Memory Usage%: 77.28 77.54
% SQL with executions>1: 92.88 92.96
% Memory for SQL w/exec>1: 91.35 91.42
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) DB Time Wait Class
------------------------------ ------------ ----------- --------- --------------
CPU time 49 78.92
control file parallel write 1,176 27 43.42 System I/O
db file parallel write 1,408 27 42.90 System I/O
log file parallel write 1,865 16 26.20 System I/O
process startup 73 9 14.12 Other
-------------------------------------------------------------
Заключение
В целом мы были довольны сервером Oracle 10g. Его было легче инсталлировать по сравнению с предыдущими версиями, модернизация нашей промышленной базы данных прошла без помех, и мы не столкнулись с проблемами совместимости. Хотя наше приложение сразу же после перехода на сервер Oracle 10g не начало работать быстрее, мы надеемся добиться для нашей системы большего, как только начнем использовать новейшие функциональные возможности (осторожно, много тестируя). После модернизации очень немногие SQL-операторы нашего приложении стали работать ощутимо медленнее, но их было легко найти и исправить.
Сервер Oracle 10g требует больше накладных расходов по сравнению с предыдущими версиями во многих областях, от использования оперативной памяти до полных разборов и числа объектов в схеме SYS. Однако мы обнаружили, что эти увеличенные потребности были управляемыми и терпимыми – справедливый обмен на множество новых функциональных возможностей, доступных теперь для нас. Мы были разочарованы начальной демонстрацией консультанта SQL Tuning Advisor, но в этой области мы видим большое поле для усовершенствований.
И снова, пожалуйста, помните: каждая система базы данных Oracle уникальна и будет иметь свои собственные проблемы. В этой статье мы намеревались только передать наши собственные впечатления – мы не хотели давать какие-то обобщенные оценки или суждения о сервере Oracle 10g. Единственный способ узнать, как ваша конкретная система будет работать в среде сервера Oracle 10g, состоит в испытании ее – в тестовой, а не промышленной среде.
Если наш опыт как-то сможет помочь спланировать вашу собственную модернизацию, можно считать, что эта статья достигла своей цели.
Об авторе
Роджер Шраг (Roger Schrag) – сертифицированный специалист OCP (Oracle Certified Professional), был администратором баз данных Oracle и архитектором приложений свыше 15 лет, начав работать в корпорации Oracle в группе разработчиков приложений Oracle Financials. В 1995 г. он основал фирму Database Specialists, Inc – консалтинговую фирму, которая специализируется на технологиях Oracle, удаленном администрировании и оптимизации производительности. С этого времени Роджер специализируется на вопросах оптимизации производительности. Роджер – частый докладчик на конференциях OracleWorld и International Oracle Users Group (IOUG) Live, и часто входит в число лучших десяти докладчиков. Он также был инструктором мастер-классов IOUG и является директором программного комитета конференций северокалифорнийской группы пользователей Oracle (NoCOUG, Northern California Oracle Users Group). Вы можете связаться с Роджером по электронной почте: rschrag@dbspecialists.com.
Вам нужна дополнительная помощь по этой тематике?
Получите консультацию
Мы будем рады поговорить с вами о наших услугах и о том, как наша команда высококвалифицированных специалистов по технологиям баз данных может помочь вам. Позвоните в фирму Database Specialists по телефонам 1-415-344-0500 или 1-888-648-0500 или заполните форму запроса бесплатной консультации.
Бесплатный информационный бюллетень
Если вы хотите получать наш бесплатный ежемесячный информационный бюллетень с советами по технологиям баз данных и сообщениями о новых фирменных описаниях, подпишитесь на The Specialist.
