Oracle как ускорить update

Обновлено: 05.07.2024

Оптимизация SQL и оптимизация экземпляра Oracle на основе CBO

Оптимизация SQL является важным аспектом оптимизации данных.В этой статье будет проанализирована собственная CBO-оптимизация Oracle, которая представляет собой метод оптимизации, основанный на затратах. Oracle нужны различные статистические данные в качестве основы для оптимизации, чтобы автоматически оптимизировать операторы SQL. Внешние будут анализировать процесс выполнения sql и информацию о потреблении ресурсов через отслеживание sql. Проблемы с производительностью базы данных часто возникают после того, как система развернута в течение определенного периода времени, то есть когда большое количество пользователей начинает использовать систему, когда увеличивается объем обработки данных и различная вычислительная сложность системы, на этот раз часто прослеживается до начального этапа проектирования системы. Таким образом, нам все еще нужно писать эффективные операторы SQL на этапе кодирования. Я видел много статей по оптимизации sql в Интернете, но они не удовлетворительны, некоторые представляют собой очень общие описания, а некоторые - в корне неправильные методы, поэтому я повторно делюсь своим процессом обучения.

Обработка запросов и оптимизация запросов - это две взаимосвязанные концепции: процесс выполнения операторов SQL для получения данных во время обработки запроса, а оптимизация запросов - это процесс получения наилучшего плана выполнения путем анализа операторов SQL и других ресурсов. Вот лучший план выполнения. Я имею в виду план, который потребляет меньше всего ресурсов, таких как ЦП и системный ввод-вывод, содержащий сервер базы данных. Выполнение SQL делится на три этапа: этап синтаксического анализа, этап оптимизации оператора и этап выполнения запроса.

1.1 Этап синтаксического анализа

Анализ синтаксиса выполняется в SGA (SGA относится к глобальной области системы, включая буфер базы данных, буфер журнала повторного выполнения, общий пул, пул Java, большой пул, пул потоков), где оператор SQL разлагается на запрос реляционной алгебры, т. Е. , с помощью этих запросов реляционной алгебры, чтобы проверить, неверна ли грамматика этого sql, правильны ли ключевые слова и т. д.

1.2 Этап оптимизации предложения

Это самая важная часть из этих трех шагов. По умолчанию Oracle использует CBO для выбора наилучшего плана выполнения. Вы можете спросить, что такое CBO? ,Ok! CBO на самом деле является программой оптимизации, основанной на затратах, то есть она будет оценивать потребление затрат и преобразовывать такие ресурсы, как цикл выполнения ЦП, память, скорость ввода-вывода и т. Д., Во временные затраты. Конечно, лучше всего тот, у кого меньше всего времени. Например, синтаксический анализ Oracle также делится на жесткий и мягкий синтаксический анализ. Для разных версий Oracle количество жесткого синтаксического анализа разное. В Oracle12 количество жесткого синтаксического анализа составляет 19, а в Oracle11g количество жесткого синтаксического анализа - 59. .

На этом этапе Oracle преобразует дерево синтаксического анализа в логический запрос, а затем преобразует логический запрос в план физического запроса. И существует несколько видов физических планов запросов, потому что оптимизатор часто генерирует несколько эффективных планов запросов, а затем на основе этих планов делает оценки потребления затрат. Обратите внимание, что это всего лишь оценка волонтеров, и не все планы были реализованы. Итак, что является основанием для оценки оракула? Как правило, оценка будет основываться на следующих факторах: a, операция соединения и последовательность соединения, задействованного в запросе b, алгоритм, выполняемый операцией c, метод чтения данных, такой как чтение памяти или диска d, запрашивайте метод передачи данных между каждой операцией.

Приходит оператор sql, и до того, как будет сгенерирован окончательный план выполнения для оператора sql, он должен пройти процесс, как показано на следующем рисунке (эй, рисунок, нарисованный вручную, уродливый!)

1.3 Выполнение запроса

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

2.1 Метод оптимизации

Смысл метода оптимизации - это метод оптимизации, выбранный для достижения цели оптимизации SQL.По умолчанию в качестве цели оптимизации используется пропускная способность оператора SQL.

Ниже представлены три метода оптимизации для удовлетворения различных требований запроса:

1. All_Rows: метод по умолчанию, цель оптимизации - достичь максимальной пропускной способности запроса.

2. FIRST_ROWS_n: оптимизировать вывод первых n строк данных в запросе, цель - удовлетворить потребности быстрого ответа.

3. FIRST_ROWS: используйте оптимизацию затрат CBO, чтобы вывести первые несколько строк данных как можно скорее, чтобы удовлетворить минимальные требования к времени отклика.

Oracle обеспечивает оптимизацию на трех уровнях: уровне экземпляра, уровне сеанса и уровне оператора.

Запросите метод оптимизации CBO для текущей базы данных:

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

2.2 Рабочий процесс оптимизатора

CBO выполняет оптимизацию SQL за 4 шага

1. Преобразование операторов SQL на основе статистических данных: это означает, что CBO считает, что преобразованный запрос оператора будет более эффективным, поэтому преобразуйте свой оператор sql в другую форму, например, OR, которое вы пишете, преобразуется в UNION ALL, а между преобразуется в> = и <= и т. д.

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

3. Выберите метод подключения на основе статистических данных: если задействовано несколько таблиц, CBO выберет метод подключения на основе статистических данных и ключевой информации таблицы и выберет метод с наименьшими вычислительными затратами среди нескольких методов подключения как лучший. способ подключения.

4. Определите порядок подключения. Относится к количеству строк данных, используемых для определения наилучшего порядка подключения.

2.3 Статистика

--Просмотр текущего статуса работы gather_stats_job

- запросить статистический анализ таблицы, принадлежащей пользователю scott: sample_size представляет количество строк выборки.
select last_analyzed,table_name,owner,num_rows,sample_size from dba_tables where owner='SCOTT';

--- Статистика для всех таблиц режима Скотт (собрана вручную)
execute dbms_stats.gather_schema_stats(ownname => 'scott');

3.1 Оптимизировать запрос

1. Оптимизируйте запрос: объясните. Для запроса индекса при использовании Like, только% находится не в первой позиции, будет эффективным. При использовании запроса с несколькими столбцами, только когда первое поле этих полей используется в условии запроса, index будет использоваться только для ссылки, или при запросе условий индекс будет использоваться в запросе, когда все столбцы в двух условиях до и после являются индексами.

2. Оптимизировать структуру базы данных, разложить таблицы с множеством полей на несколько таблиц, добавить промежуточные таблицы, добавить избыточные поля, оптимизировать скорость вставки, отключить проверки уникальности, использовать пакетную вставку, запретить проверки внешнего ключа, запретить автоматическую отправку, оптимизировать таблицы оптимизировать
3. Оптимизировать сервер базы данных, оборудование: память, io, оптимизировать параметры.

4. Используйте переменные связывания: все мы знаем, что Oracle делится на жесткий анализ и мягкий анализ. В SGA общий пул предназначен для хранения проанализированного оператора SQL. В настоящее время общий пул содержит окончательный план выполнения оператора SQL. . Если есть тот же оператор SQL-запроса, нет необходимости повторно анализировать этот оператор SQL, но план выполнения оператора SQL выполняется непосредственно из общего пула. Общий пул используется, чтобы избежать жесткого синтаксического анализа, потому что каждый раз, когда вы переходите к жесткому синтаксическому анализу, вам необходимо повторно анализировать синтаксис и семантику оператора, а затем оптимизировать сгенерированный окончательный план выполнения с помощью CBO, который потребляет ресурсы ЦП. Использовать переменные связывания, которые мы обычно добавляем в оператор SQL при разработке Java? Выполнить, а затем передать параметры.

Например: выберите ename, job, sal из scott.emp, где deptno =?

Затем мы передаем параметры, которые не только предотвращают внедрение SQL, но и оптимизируют SQL.

5. Исключите подзапросы: для некоторых вложенных подзапросов добавьте вложенные операторы SQL, например:

select * from scott.emp e1 where e1.sal>

(select avg(sal) from scott.emp e2 where e2.deptno=e1.deptno);

Такой оператор SQL должен каждый раз выполнять N * M операций. Для определенных значений вы можете использовать трассировку SQL ниже для анализа производительности.

from scott.emp e1,(select e2.deptno ,avg(e2.sal) avg_sal from scott.emp e2 group by deptno) d

where e1.deptno=d.deptno and e1.sal >d.avg_sal

Оптимизированному sql нужно только выполнить операцию N + M, и его масштабируемость выше, и результаты вычислений не будут увеличиваться экспоненциально. Хотя изначально кажется, что оптимизированный оператор SQL кажется немного длиннее, если вам интересно, правильно он или нет, вы можете использовать инструмент анализа операторов SQL, о котором мы поговорим дальше, чтобы сравнить его, и вы можете проверить его с помощью план его выполнения.

3.2 Инструмент оптимизации операторов SQL

Используйте план объяснения для команды, чтобы получить план выполнения оператора SQL, поэтому сначала мы создаем таблицу, необходимую для выполнения этой команды.В каталоге установки oracle нам нужно найти файл utlxplan.sql и выполнить его. Путь к этому файлу, который я здесь, находится в E: \ oracle \ app \ product \ 11.2.0 \ dbhome_1 \ RDBMS \ ADMIN \ utlxplan.sql, команда выполнения выглядит следующим образом:

Просмотрите эту структуру таблицы:

SQL> desc plan_table;

SQL> desc plan_table

Name Type Nullable Default Comments

STATEMENT_ID VARCHAR2(30) Y

PLAN_ID NUMBER Y

TIMESTAMP DATE Y

REMARKS VARCHAR2(4000) Y

OPERATION VARCHAR2(30) Y

OPTIONS VARCHAR2(255) Y

OBJECT_NODE VARCHAR2(128) Y

OBJECT_OWNER VARCHAR2(30) Y

OBJECT_NAME VARCHAR2(30) Y

OBJECT_ALIAS VARCHAR2(65) Y

OBJECT_INSTANCE INTEGER Y

OBJECT_TYPE VARCHAR2(30) Y

OPTIMIZER VARCHAR2(255) Y

SEARCH_COLUMNS NUMBER Y

PARENT_ID INTEGER Y

DEPTH INTEGER Y

POSITION INTEGER Y

CARDINALITY INTEGER Y

BYTES INTEGER Y

OTHER_TAG VARCHAR2(255) Y

PARTITION_START VARCHAR2(255) Y

PARTITION_STOP VARCHAR2(255) Y

PARTITION_ID INTEGER Y

DISTRIBUTION VARCHAR2(30) Y

CPU_COST INTEGER Y

IO_COST INTEGER Y

TEMP_SPACE INTEGER Y

ACCESS_PREDICATES VARCHAR2(4000) Y

FILTER_PREDICATES VARCHAR2(4000) Y

PROJECTION VARCHAR2(4000) Y

QBLOCK_NAME VARCHAR2(30) Y

OTHER_XML CLOB Y

Затем мы используем эту команду для анализа выполнения оператора SQL:

SQL> explain plan for
2 select count(*) from scott.emp;
Explained

Давайте посмотрим на информацию о плане выполнения инструкции sql в таблице plan_table:

SQL> col id for 999
SQL> col operation for a20
SQL> col options for a20
SQL> col object_name for a20
SQL> select id,operation,options,object_name,options from plan_table;
ID OPERATION OPTIONS OBJECT_NAME OPTIONS
--- -------------------- -------------------- -------------------- --------------------
0 SELECT STATEMENT
1 SORT AGGREGATE AGGREGATE
2 INDEX FULL SCAN PK_EMP FULL SCAN

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

Что, если мы хотим проанализировать этот sql более глубоко, например, мы хотим узнать объект доступа, потребление ЦП и другую информацию об этом. Затем мы можем включить трассировку SQL.

1. Используйте команду autotrace.

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

Я пытаюсь обновить целевую таблицу из исходной таблицы, используя один массивный оператор UPDATE , но время выполнения намного больше, чем должно.

Запрос

Обе таблицы содержат примерно от 10 до 13 миллионов строк, у них совпадают первичные ключи, и мы можем с уверенностью предположить, что каждая строка в целевой таблице имеет соответствующую строку в исходной таблице.

Определения таблиц

План выполнения

Вопросы

Вынужден ли приведенный выше запрос UPDATE использовать медленный, построчный план выполнения?

Могу ли я оптимизировать вышеуказанный запрос, только переписав его как оператор MERGE , например следующий?

Альтернативный запрос, переписанный как MERGE

Альтернативный план выполнения

Могу ли я получить такую же хорошую производительность альтернативного оператора MERGE при использовании UPDATE ?

Я использую Oracle 12c.

1 ответ

1) Да, вы никогда не захотите совершать (приблизительно) 12 миллионов раз доступ к индексу для большой таблицы.

Индексные блоки будут в основном на диске, поэтому вы можете ожидать доступа к 100 строкам в секунду.

Даже если вы получаете 1000 обращений к индексу в секунду из-за кеширования, вам все равно придется часами ждать обработки 12 миллионов строк.

Таким образом, план выполнения 1 - НЕ ПЕРЕДАЧА для большого стола.

2) План выполнения MERGE (хеш-соединение с двумя полными сканированиями таблицы) выглядит нормально и должен работать.

3) Да, вы можете без проблем использовать оператор UPDATE в своей настройке. Вы будете использовать функцию обновляемого соединения просмотреть

Вы видите, что создан аналогичный план выполнения, что и для MERGE , так что вы ожидаете также аналогичной производительности.

Заключительные примечания

Вы можете дополнительно использовать параллельную подсказку для ускорения.

Не забывайте, что вы должны включить parallel dml в сеансе.

Ваше утверждение MERGE не равно вашему первому утверждению UPDATE . Разница появляется, когда первичный ключ существует в целевой таблице, но отсутствует в исходной таблице.

UPDATE сбрасывает целевые столбцы на NULL , а MERGE позволяет им без изменений .

Мой оператор UPDATE использует внешнее соединение , поэтому он ведет себя как ваше UPDATE - переключитесь на внутреннее соединение , чтобы получить поведение MERGE .

Эта статья посвящена обсуждению производительности достаточно сложного оператора UPDATE с вложенными. По мотивам очередного ответа Тома Кайта на вопрос, заданный в июне 2003 года.

Как повысить производительность этого оператора UPDATE в версии 8.1.7?

Я использую следующий оператор update для таблицы t1 :

В таблице t1 - около 10000 строк, а в таблице t2 - около 2 миллионов. Я пытаюсь получить в таблице t1 информацию о сумме и дате последней банковской транзакции для всех идентификаторов из таблицы t2 (таблицы транзакций). Таблица t3 (код транзакции) определяет, какие транзакции следует учитывать.

Этот оператор update выполняется достаточно долго. Что бы ты порекомендовал для повышения производительности этого оператора update?

Ответ Тома Кайта

Ну, этот оператор update , при наличии соответствующих индексов и использовании стоимостного оптимизатора, должен работать "сравнительно быстро". На моей персоналке он работает менеее 2 минут - разве это долго?

В любом случае, я сымитировал вашу ситуацию так:

Итак, у меня тоже 100000 строк, 2000000 строк и отдельная таблица для поиска кода транзакции. Я создал следующее представление:

И попробовал выполнить изменение процедурно:

Затем попробовал сделать то же самое одним оператором update :

Для PL/SQL-блока потребовалось:

около 80 секунд, а для update :

Так что, можете попробовать процедурный подход, но, при наличии соответствующих индексов, изменение 100000 строк не должно вызывать особых проблем.

Почему SQL выполняется медленнее, чем PL/SQL?

Том, что в этом примере делает его исключением из твоего простого правила: один SQL-оператор работает быстрее и использует меньше ресурсов, чем процедурное решение?

Ответ Тома Кайта

Дело в сути изменения: в нем используется много коррелированных подзапросов для поиска max enter_date и т.п. Это усложняет при оптимизации выбор любого другого плана кроме вложенных циклов (именно так выполняются коррелированные подзапросы).

Я бы хотел сделать следующее:

Но, поскольку нет защиты ключом (key preservation) и используются аналитические функции (что подразумевает "упорядочивание" подставляемого представления), так делать нельзя. Я хотел бы выбирать строки с помощью именно такого запроса, поскольку он выполняет множественные операции (что ускоряет работу при большом количестве строк), а не 100000 сложенных циклов.

Итак, чтобы "изменить результаты этого запроса", мне, к сожалению (в 8i), приходится использовать запрос + update . Выполняя множественные операции.

В версии 9i появился оператор MERGE :

Он делает то же самое, что и мой PL/SQL-код, но немного эффективнее.

Аналогичная проблема

Я пытался разобраться, почему мой оператор update выполняется часами , а если разделить его на две части - эти части выполняются несколько секунд. Возможно, проблема связана с самосоединением, но я не уверен.

Моя проблема в том, что первоначально не была учтена возможность рождения однополых двойняшек. В таблице есть столбцы CLIID, пол и дата рождения. Я просто хочу пометить возможные дублирующиеся строки (часть из них может быть связана с двойняшками, а часть - с опечатками при наборе) по каждому CLIID. В таблице - около 150000 записей, из которых около 10000 потенциально могут быть дублирующимися.

Я написал следующий оператор:

Сам запрос выполняется около 6 секунд. Если я выполняю CREATE TABLE AS SELECT, а затем изменяю таблицу testchild на основе этой "временной" таблицы, требуется менее минуты. Добавление индекса по ID для временной таблицы особой роли не играет. А вот если выполнять представленный выше полный update . Он начался в 11 утра вчера, и сегодня в 9 утра еще выполняется. Количество consistent gets измеряется миллиардами. Все это работает в версии 9i на персоналке.

Что происходит, не подскажешь?

Ответ Тома Кайта

Этот 6-секундный запрос выполняется 152k раз (представленная статистика свидетельсвует, что изменять в таблице придется 152k строк) Представленный ранее прием позволит выполнять 6-секундный запрос блоком (IN BULK), а не построчно, и изменять его результаты.

Прекрасное решение

Я попробовал предложенное процедурное решение, и оно потребовало около 60 секунд. Аналитические функции - это замечательно.

У меня, однако, осталась одна проблема. Вместо создания представления я попытался создать курсор:

Но этот же запрос в окне SQL*Plus прекрасно работает. Почему нельзя создать курсор?

Ответ Тома Кайта

Я специально создал представление, увидев в вопросе упоминание версии 8.1.7

Я запускаю обновление таблицы (Table_A), которая имеет 9.5 миллионов записей. Я запускаю его уже 13 часов, а он все еще не закончился! Может ли кто-нибудь указать мне правильное направление с точки зрения того, как его ускорить? Я могу использовать PL / SQL для хранения суб-выборок в переменные, если это сделает его быстрее. Я новичок Oracle и буду признателен за любую помощь.

Table_A имеет 9.5 миллионов записей
Table_B имеет 430 000 записей
Table_C имеет 3050 записей

4 ответа

У меня есть несколько очень больших таблиц (во всяком случае, для меня), как в миллионах строк. Я загружаю их из устаревшей системы, и это занимает целую вечность. Предполагая, что аппаратное обеспечение в порядке, это быстро. Как я могу ускорить это? Я попробовал экспортировать из одной системы в.

В настоящее время я использую тонкий драйвер ojdbc14.jar Oracle 10g для доступа к базе данных Oracle 10g. Я хотел бы обновить драйвер до тонкого драйвера ojdbc6.jar Oracle 11g до того, как произойдет фактическое обновление сервера DB. Использование драйвера 11g против 10g DB пока кажется хорошим.

Попробуйте написать оба подзапроса, используя EXISTS вместо in-list. Это гораздо более эффективно.

EDIT 2015-03-02 Вот (надеюсь, правильный) запрос с использованием exists:

сначала создайте индекс на table_b для Table_C_ID,

затем создайте статистику из 3 таблиц,

после этого используйте подсказку paralell:

это значительно увеличит скорость

Как долго длится соответствующий select ?

Предположительно, это не быстро, и я предполагаю, что это не быстро из-за отсутствия индексов. Для этого запроса я бы рекомендовал следующие составные индексы: table_b(Table_C_ID, ID) и table_c(xyz_id, id) . Я бы рекомендовал вам быстро заставить select работать, прежде чем работать с update .

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

Спасибо за все предложения. Я пробую их по одному.

Очевидно, использование exists не во всех случаях совпадает с in . Например, приведенный ниже запрос возвращает другие результаты, если я заменю in на exists :

Можно ли установить в мою машину как Oracle XE, так и Oracle 11g? В чем разница между ними? Я хочу подключиться к Oracle с помощью Access. Требуется ли для этого Oracle 11g или я могу также использовать Oracle XE?

Oracle как ускорить update

Оптимизация SQL и оптимизация экземпляра Oracle на основе CBO

1.1 Этап синтаксического анализа

1.3 Выполнение запроса

2.1 Метод оптимизации

2.2 Рабочий процесс оптимизатора

2.3 Статистика

3.1 Оптимизировать запрос

3.2 Инструмент оптимизации операторов SQL

1 ответ

Как повысить производительность этого оператора UPDATE в версии 8.1.7?

Ответ Тома Кайта

Почему SQL выполняется медленнее, чем PL/SQL?

Ответ Тома Кайта

Аналогичная проблема

Ответ Тома Кайта

Прекрасное решение

Ответ Тома Кайта

4 ответа

Похожие вопросы: