Hard parse oracle что это
1. При первом разборе происходит полный разбор запроса (hard parse)
План запроса помещается в глобальный кэш БД с определенным sql_id
2. При повторном выполнении происходит частичный разбор (soft parse)
Происходит только синтаксический разбор, проверки прав доступа и проверки bind переменных. Что для разных вариаций sql_id создает дочерние child_sql_id
Из-за такого механизма работы Oracle вытекает частая проблема oltp систем, где существует огромное число маленьких запросов, отличающихся друг от друга только фильтрами или параметрами. Это приводит к быстрому вытеснению планов из кэша и их последующему повторному hard parse.
В итоге может оказаться, что большую часть времени БД занимается разбором запросов, а не собственно их выполнением.
Отсюда вывод: по возможности используйте bind переменные в вариациях одного запроса, замен константных фильтров, т.к. это даст нам только один план запроса (child_sql_id) при разных значениях переменных на равномерно распределенном столбце.
Я не зря сказал ранее "на равномерно распределенном столбце", т.к. с bind переменными есть проблема: по умолчанию Oracle не знает какие данные будут переданы в запрос и из-за этого может сгенерить неверный план запроса.
Посмотрим на примере по умолчанию. Создадим таблицу с неравномерно распределенным столбцом "n" (9 строк со значением = 1, и 1млн-9 строк со значением 2):
Столбец не имеет гистограмм, но есть статистика по уникальным значениям. Построим план запроса с bind переменной = 1:
Oracle закономерно ожидает в результате половину таблицу и выбирает full scan, хотя мы то знаем, что тут был бы лучше Index scan.
К счастью с 11 версии Oracle может заглядывать в значения bind переменных и подбирать под них нужные планы.
Для этого соберем гистограмму с 2 вершинами и повторим эксперимент:
Oracle сгенерировал новый child_sql_id под новое значение bind переменной и выбрал правильный доступ по индексу.
Данный план был закеширован в глобальную память и если прямо сейчас выполнить заново с параметром 2, то мы получим тотже план (child number 2).
Замечу что на этом этапе уже надо смотреть план уже выполненного запроса, т.к. oracle не умеет показывать план и заглядывать в bind переменные, но при реальном выполнении запроса значения bind переменных смотрятся.
но oracle пометит этот запрос на пересмотр, т.к. план совсем не сошелся с реальными данными и при последующем применении сгенерирует новый child_sql_id (child number 3) под нашу bind переменную:
Из всего этого можно сделать вывод, что вопреки частому заблуждению, Oracle умеет генерировать правильные планы по bind переменным, но делает это не сразу, а при повторном вызове и при наличии гистограммы.
Второе: реальный план запроса с bind переменными можно узнать только во время или после выполнения запроса, т.к. "explain plan" не подсматривает в bind переменные.
About SQL Processing
SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement. Depending on the statement, the database may omit some of these stages.
The following figure depicts the general stages of SQL processing.
Figure 3-1 Stages of SQL Processing
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
3.1 About SQL Processing
SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement.
The following figure depicts the general stages of SQL processing. Depending on the statement, the database may omit some of these stages.
Figure 3-1 Stages of SQL Processing
3.1.1 SQL Parsing
The first stage of SQL processing is parsing.
The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor , which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the program global area (PGA).
During the parse call, the database performs checks that identify the errors that can be found before statement execution . Some errors cannot be caught by parsing. For example, the database can encounter deadlocks or errors in data conversion only during statement execution.
3.1.1.1 Syntax Check
Oracle Database must check each SQL statement for syntactic validity.
A statement that breaks a rule for well-formed SQL syntax fails the check. For example, the following statement fails because the keyword FROM is misspelled as FORM :
3.1.1.2 Semantic Check
The semantics of a statement are its meaning. A semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist.
A syntactically correct statement can fail a semantic check, as shown in the following example of a query of a nonexistent table:
3.1.1.3 Shared Pool Check
During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing.
To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown in V$SQL.SQL_ID . This hash value is deterministic within a version of Oracle Database, so the same statement in a single instance or in different instances has the same SQL ID.
When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value. The hash value of a SQL statement is distinct from the following values:
Memory address for the statement
Oracle Database uses the SQL ID to perform a keyed read in a lookup table. In this way, the database obtains possible memory addresses of the statement.
Hash value of an execution plan for the statement
A SQL statement can have multiple plans in the shared pool. Typically, each plan has a different hash value. If the same SQL ID has multiple plan hash values, then the database knows that multiple plans exist for this SQL ID.
Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:
If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse , or a library cache miss .
The database always performs a hard parse of DDL.
During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.
A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit .
Soft parses can vary in how much work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."
In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.
The following graphic is a simplified representation of a shared pool check of an UPDATE statement in a dedicated server architecture.
Figure 3-2 Shared Pool Check
If a check determines that a statement in the shared pool has the same hash value, then the database performs semantic and environment checks to determine whether the statements have the same meaning. Identical syntax is not sufficient. For example, suppose two different users log in to the database and issue the following SQL statements:
The SELECT statements for the two users are syntactically identical, but two separate schema objects are named my_table . This semantic difference means that the second statement cannot reuse the code for the first statement.
Even if two statements are semantically identical, an environmental difference can force a hard parse. In this context, the optimizer environment is the totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode). Consider the following series of SQL statements executed by a single user:
In the preceding example, the same SELECT statement is executed in three different optimizer environments. Consequently, the database creates three separate shared SQL areas for these statements and forces a hard parse of each statement.
Oracle Database Concepts to learn about private SQL areas and shared SQL areas
Oracle Database Performance Tuning Guide to learn how to configure the shared pool
This chapter explains how database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.
This chapter contains the following topics:
3.1 About SQL Processing
SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement.
The following figure depicts the general stages of SQL processing. Depending on the statement, the database may omit some of these stages.
Figure 3-1 Stages of SQL Processing
3.1.1 SQL Parsing
The first stage of SQL processing is parsing.
The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor , which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the program global area (PGA).
During the parse call, the database performs checks that identify the errors that can be found before statement execution . Some errors cannot be caught by parsing. For example, the database can encounter deadlocks or errors in data conversion only during statement execution.
3.1.1.1 Syntax Check
Oracle Database must check each SQL statement for syntactic validity.
A statement that breaks a rule for well-formed SQL syntax fails the check. For example, the following statement fails because the keyword FROM is misspelled as FORM :
3.1.1.2 Semantic Check
The semantics of a statement are its meaning. A semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist.
A syntactically correct statement can fail a semantic check, as shown in the following example of a query of a nonexistent table:
3.1.1.3 Shared Pool Check
During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing.
To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown in V$SQL.SQL_ID . This hash value is deterministic within a version of Oracle Database, so the same statement in a single instance or in different instances has the same SQL ID.
When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value. The hash value of a SQL statement is distinct from the following values:
Memory address for the statement
Oracle Database uses the SQL ID to perform a keyed read in a lookup table. In this way, the database obtains possible memory addresses of the statement.
Hash value of an execution plan for the statement
A SQL statement can have multiple plans in the shared pool. Typically, each plan has a different hash value. If the same SQL ID has multiple plan hash values, then the database knows that multiple plans exist for this SQL ID.
Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:
If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse , or a library cache miss .
The database always performs a hard parse of DDL.
During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.
A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit .
Soft parses can vary in how much work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."
In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.
The following graphic is a simplified representation of a shared pool check of an UPDATE statement in a dedicated server architecture.
Figure 3-2 Shared Pool Check
If a check determines that a statement in the shared pool has the same hash value, then the database performs semantic and environment checks to determine whether the statements have the same meaning. Identical syntax is not sufficient. For example, suppose two different users log in to the database and issue the following SQL statements:
The SELECT statements for the two users are syntactically identical, but two separate schema objects are named my_table . This semantic difference means that the second statement cannot reuse the code for the first statement.
Even if two statements are semantically identical, an environmental difference can force a hard parse. In this context, the optimizer environment is the totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode). Consider the following series of SQL statements executed by a single user:
In the preceding example, the same SELECT statement is executed in three different optimizer environments. Consequently, the database creates three separate shared SQL areas for these statements and forces a hard parse of each statement.
Oracle Database Concepts to learn about private SQL areas and shared SQL areas
Oracle Database Performance Tuning Guide to learn how to configure the shared pool
This chapter explains how database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии "estimated rows" оценки на основе реальных "actual rows"
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии "estimated rows" оценки на основе реальных "actual rows"
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Oracle 12: Адаптивные планы
Смена плана во время выполнения запроса. На основе кол-ва данных полученных из шагов запроса генерируется оптимальный план следующего шага.
Адаптивность может сменить:
* Тип соединения NL HJ
* тип parallel distribution
* Bitmap Index Pruning
This chapter explains how database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.
SQL Parsing
The first stage of SQL processing is parsing .
The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor , which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the program global area (PGA).
During the parse call, the database performs the following checks:
The preceding checks identify the errors that can be found before statement execution . Some errors cannot be caught by parsing. For example, the database can encounter deadlocks or errors in data conversion only during statement execution.
Syntax Check
Oracle Database must check each SQL statement for syntactic validity.
A statement that breaks a rule for well-formed SQL syntax fails the check. For example, the following statement fails because the keyword FROM is misspelled as FORM :
Semantic Check
The semantics of a statement are its meaning. A semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist.
A syntactically correct statement can fail a semantic check, as shown in the following example of a query of a nonexistent table:
Shared Pool Check
During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing.
To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown in V$SQL.SQL_ID . This hash value is deterministic within a version of Oracle Database, so the same statement in a single instance or in different instances has the same SQL ID.
When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value. The hash value of a SQL statement is distinct from the following values:
Memory address for the statement
Oracle Database uses the SQL ID to perform a keyed read in a lookup table. In this way, the database obtains possible memory addresses of the statement.
Hash value of an execution plan for the statement
A SQL statement can have multiple plans in the shared pool. Typically, each plan has a different hash value. If the same SQL ID has multiple plan hash values, then the database knows that multiple plans exist for this SQL ID.
Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:
If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse , or a library cache miss .
The database always performs a hard parse of DDL.
During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.
A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit .
Soft parses can vary in how much work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."
In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.
The following graphic is a simplified representation of a shared pool check of an UPDATE statement in a dedicated server architecture.
Figure 3-2 Shared Pool Check
If a check determines that a statement in the shared pool has the same hash value, then the database performs semantic and environment checks to determine whether the statements have the same meaning. Identical syntax is not sufficient. For example, suppose two different users log in to the database and issue the following SQL statements:
The SELECT statements for the two users are syntactically identical, but two separate schema objects are named my_table . This semantic difference means that the second statement cannot reuse the code for the first statement.
Even if two statements are semantically identical, an environmental difference can force a hard parse. In this context, the optimizer environment is the totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode). Consider the following series of SQL statements executed by a single user:
In the preceding example, the same SELECT statement is executed in three different optimizer environments. Consequently, the database creates three separate shared SQL areas for these statements and forces a hard parse of each statement.
Oracle Database Concepts to learn about private SQL areas and shared SQL areas
Oracle Database Performance Tuning Guide to learn how to configure the shared pool
Автор статьи – Виктор Варламов(varlamovVp18), OCP.
Оригинал статьи опубликован 07.07.2017.
Отдельное спасибо автору перевода — brutaltag.
В нашей системе подготовки отчетности обычно выполняются сотни длительных запросов, которые вызываются различными событиями. Параметрами запросов служат список клиентов и временной интервал (дневной, недельный, месячный). Из-за неравномерных данных в таблицах один запрос может выдать как одну строку, так и миллион строк, в зависимости от параметров отчета (у разных клиентов — различное количество строк в таблицах фактов). Каждый отчет выполнен в виде пакета с основной функцией, которая принимает входные параметры, проводит дополнительные преобразования, затем открывает статический курсор со связанными переменными и в конце возвращает этот открытый курсор. Параметр БД CURSOR_SHARING выставлен в FORCE.
В такой ситуации приходится сталкиваться с плохой производительностью, как в случае повторного использования плана запроса оптимизатором, так и при полном разборе запроса с параметрами в виде литералов. Связанные переменные могут вызвать неоптимальный план запроса.
В своей книге “Oracle Expert Practices” Алекс Горбачев приводит интересную историю, рассказанную ему Томом Кайтом. Каждый дождливый понедельник пользователям приходилось сталкиваться с измененным планом запроса. В это трудно поверить, но так и было:
«Согласно наблюдениям конечных пользователей, в случаях, когда в понедельник шел сильный дождь, производительность базы данных была ужасной. В любой другой день недели или же в понедельник без дождя проблем не было. Из разговора с администратором БД Том Кайт узнал, что трудности продолжались до принудительного рестарта базы данных, после чего производительность становилась нормальной. Вот такой был обходной маневр: дождливый понедельник – рестарт».
Это реальный случай, и проблема была решена совершенно без всякой магии, только благодаря отличным знаниям того, как работает Oracle. Я покажу решение в конце статьи.
Вот небольшой пример, как работают связанные переменные.
Создадим таблицу с неравномерными данными.
Другими словами, у нас есть таблица VVP_HARD_PARSE_TEST с миллионом строк, где в 10.000 случаев поле C2 = 99, 8 записей с C2 = 1, а остальные с C2 = 1000000. Гистограмма по полю С2 указывает оптимизатору Oracle об этом распределении данных. Такая ситуация известна как неравномерное распределение данных, и гистограмма может помочь выбрать правильный план запроса в зависимости от запрашиваемых данных.
Понаблюдаем за простыми запросами к этой таблице. Очевидно, что для запроса
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :p
если p = 1, то наилучшим выбором будет INDEX RANGE SCAN, для случая p = 1000000 лучше использовать FULL TABLE SCAN. Запросы Query1 и Query1000000 идентичны, за исключением текста в комментариях, это сделано чтобы получить различные идентификаторы планов запроса.
Теперь посмотрим на планы запросов:
Как можно видеть, план для разных запросов создается только один раз, в момент первого выполнения (только один дочерний курсор с CHILD_NUMBER = 0 существует для каждого запроса). Каждый запрос выполняется дважды (EXECUTION = 2). Во время жесткого разбора Oracle получает значения связанных переменных и выбирает план соответственно этим значениям. Но он использует тот же самый план и для следующего запуска, несмотря на то что связанные переменные изменились во втором запуске. Используются неоптимальные планы – Query1000000 с переменной C2 = 1 использует FULL TABLE SCAN вместо INDEX RANGE SCAN, и наоборот.
Понятно, что исправление приложения и использование параметров как литералов в запросе – это самый подходящий способ решения проблемы, но он ведет к динамическому SQL с его известными недостатками. Другой путь – отключение запроса связанных переменных ( ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) или удаление гистограмм (ссылка).
Одно из возможных решений — это альтернативное использование политик на доступ к данным, также известных как Virtual Private Database (детальный контроль доступа, Fine Grained Access Control, контроль на уровне строк). Это позволяет менять запросы на лету и поэтому может вызвать полный разбор плана запроса каждый раз, когда запрос использует детальный контроль доступа. Эта техника подробно описана в статье Рэндальфа Гейста. Недостатком этого метода является возрастающее число полных разборов и невозможность манипулировать планами запросов.
Посмотрите, что мы сейчас сделаем. После анализа наших данных мы решаем разбить клиентов на три категории – Большие, Средние и Маленькие (L-M-S или 9-5-1) – согласно количествам сделок или транзакций в течение года. Также количество строк в отчете строго зависит от периода: Месячный – Large, Недельный – Middle, Дневной – Small или 9-5-1. Далее решение простое – сделаем предикат политики безопасности зависящим от каждой категории и от каждого периода. Так, для каждого запроса мы получим 9 возможных дочерних курсоров. Более того, запросы с разными политиками приведут нас к одним и тем же идентификаторам запросов, это дает возможность реализовать SQL PLAN MANAGEMENT (sql plan baseline).
Теперь, если мы хотим встроить такую технологию в отчет, нам надо добавить HARD_PARSE_TABLE в запрос (это ни капельки его не испортит) и вызывать CALC_PREDICATES перед тем, как выполняется основной запрос.
Посмотрим, как эта техника может преобразить предыдущий пример:
Посмотрим на планы выполнения:
Выглядит здорово! Каждый запрос выполняется дважды, с различными дочерними курсорами и разными планами. Для параметра C2 = 1000000 мы видим FULL TABLE SCAN в обоих запросах, а для параметра C1 = 1 мы видим всегда INDEX RANGE SCAN.
В конце привожу разгадку случая с дождливыми понедельниками:
«Оказывается, каждые выходные в воскресенье происходил холодный бэкап, так что все планы запросов перегенерировались по первому выполнению утром в понедельник. Один из сотрудников обычно начинал свою работу раньше остальных, и его план запроса выполнялся хорошо и для остальных пользователей в течение недели. Однако если шел дождь, этот пользователь опаздывал к началу рабочего дня из-за проблем с его утренним маршрутом. Тогда первым запускался пакетный расчет отчетов, но план запроса из-за неподходящих значений связанных переменных был совершенно плох для остальных случаев».
И несколько полезных системных представлений:
• dba_tab_histograms, all_tab_histograms, user_tab_histograms
• v$vpd_policy
• v$sql_bind_capture
• dba_hist_sqlbind
Читайте также: