Enter binds oracle что это
- Parse. The Oracle engine verifies the SQL statement’s syntax, table and column names, and access rights.
- Optimize. The Oracle engine finds the best way to process the SQL statement using statistics from the indexes and tables being accessed.
- Execution. The data is accessed using the execution path from the Optimizing step.
- Fetch. For SELECT statements only, where rows of data are returned to the application.
The first execution of DML statements (insert, update or delete) will go through the first three stages, but will not go through the fourth stage.
The execution of the first two steps may consume considerable resources. SQL statements can be written in such a way as to eliminate the need to execute the first two steps, increasing the efficiency of future operations of that statement. Additionally, efficient applications (such as those that use cursors in PL/SQL) should try to minimize overhead by performing the Parse and Optimize steps as few times as possible.
All currently executing SQL statements are cached in the shared SQL area within the Shared Pool structure of the SGA. When a SQL statement is parsed, it is compared to the currently cached statements. If a match is found then the parse step can be minimized to checking access rights. This is called a soft parse. If no match is found a full parse is performed called a hard parse. Soft parses will take less time than hard parses.
The first time a SQL statement is executed the optimization of that statement occurs. It is during this operation that the Oracle engine defines the access path that Oracle needs to take to access the data in the database. Once the optimize stage is complete, the SQL statement and the data access paths and associated parse trees are stored in the Library Cache. The SQL statement is then executed. Once initial parsing and execution have occurred, the same SQL statement can be executed again very efficiently because the statement does not need to be parsed or optimized. This is facilitated through the use of a hashing algorithm that is applied to the issued SQL statement. The hash value of the issued SQL statement is then compared to hash values within the library cache and if a match is found, a parse is not required. Note that in Oracle8i and earlier different spaces and line feeds in a given SQL statement will cause it not to match an otherwise identical SQL statement. This is not true in Oracle9i and later versions.
Just like the database Buffer Cache, SQL statements can be aged out of the Library Cache, which will cause a parse to occur during any following execution. The Library Cache is also purged if the database is restarted or the shared pool is flushed.
Optimizing SQL
It should be the goal of all developers and DBA’s to write efficient code. Poorly tuned SQL code is one of the main causes of database performance problems. Most of the resources of an Oracle database are dedicated to processing queries on the data contained in the database and to making modifications to the data contained in the database. These requests are generally received by the database through the use of SQL statements. Therefore, making sure that SQL is optimally tuned should be one of your top priorities when performing tuning tasks.
The following are some of the reasons why you should make sure that SQL is optimally tuned:
- To improve interactive response time of an ORACLE based application. A major component of the response time of these applications is the amount of time it takes to retrieve or update data in the database. By tuning the SQL underlying these applications, response times can be reduced from excessive to acceptable or outstanding.
- To improve batch throughput. Batch systems can be required to process thousands, or millions of rows of data in rigidly defined batch windows (the period of time allocated for batch jobs). Improving the SQL that drives batch jobs allows more rows to be processed in a given time period and allows these jobs to complete in their allotted time. Often, problems in batch reports are not noticed until a steadily degrading batch job exceeds the time limit (for example, when the daily report takes longer than 24 hours to run).
- To ensure scalability in an application. As you increase the load on your system (as measured by the number of users connected to the system or the data volume in the database) performance (as measured by response time or throughput) should degrade gradually. However, many applications degrade quickly, rather than gradually, as load increases.
- To reduce system load. Even when performance is within acceptable bounds, tuning the application can free up system resources for other purposes.
- To avoid hardware upgrades. Hardware upgrades are often recommended as a solution for applications that perform poorly. However, hardware upgrades may allow you to avoid tuning, but they introduce other issues. Non-scaleable applications can require a series of hardware upgrades, which can reach the limits of availability and affordability.
Use of bind variables is one method of improving performance of SQL operations. Let’s look at how they can be used to improve response time.
Bind Variables
Hard parses are bad. Let’s start with that statement. Avoiding hard parses can reduce overall run time of a given SQL statement be a second or more. That might not seem like a lot, but if that statement is running 100,000 times in several hours time, that one second can be an eternity. Since Oracle only considers identical SQL statements to be the same, hard parses will be performed for both of these statements
To the optimizer, the two SQL statements above are not identical. This will result in two different cursors being stored in the Library Cache.
You can avoid this problem through the use of bind variables used within your SQL and PL/SQL code. To use a bind variable, prepare your SQL statement or PL/SQL block and use a colon followed by a variable name to indicate a bind variable is being used. You can use bind variables for input variables in any DELETE, INSERT, SELECT, or UPDATE statement, or PL/SQL block, in any position in the statement where you can use an expression or a literal value.
Here is a piece of PL/SQL written without the use of a bind variable:
and one written using bind variables:
The later PL/SQL can be executed many times with different values for :v_value, and yet will only be hard parsed once. Take care to write your applications so that they are designed using bind variables rather than hard coded values so they will only need to do a single parse. This approach not only performs better but also is a much more scalable way of writing SQL and PL/SQL. This is because multiple concurrent statements will be able to be executed, and since there will be reduced parsing there will be much less latch contention in the shared pool. Each development platform is different, so you will have to find out how to use bind variables within that platform. If we had used bind variables in our first example, the SQL statement would have looked like:
Both executions of we first talked about can use this same SQL statement. The bind variable, :b0, would have a value of 1234 for the first run, and a value of 435 for the second run.
Reuse Those Cursors
Cursors are the memory areas in the SGA that store the SQL statements and its parsed information. When executing the same SQL more than once, the Oracle engine will reuse the cursor. Creating applications that do not needlessly close cursors helps maximize soft parsing and minimizing hard parses. Different development platforms handle cursors differently, be sure to understand how the development tool of choice handles cursors.
Cursor Sharing
Bind variables are sometimes hard to implement because the application code is from a vendor and you do not have access to the source code. However the Oracle Cursor Sharing feature can still help you take advantage of the benefits of bind variables. Oracle offers the cursor_sharing initialization parameter starting with Oracle 8i. When cursor sharing is enabled via the setting of the parameter cursor_sharing to FORCE, EXACT, or SIMILAR (SIMILAR only available in 9i and 10g), Oracle will replace literal values with system generated bind values. This can have the effect of reducing hard parsing immensely.
When cursor_sharing is set to EXACT, SQL statements must be exactly the same to use the same cursor. This is the default value. When cursor_sharing is set to FORCE, it will force cursors that are the same except for literals to use the same cursor. Oracle 9i and 10g enhances cursor sharing by adding the SIMILAR option. Choosing this option allows the optimizer to make smarter choices in the way it approaches the reuse of cursors. Setting cursor_sharing = SIMILAR will allow to Oracle optimizer to consider issues such as data distribution when deciding to reuse a cursor. Beware of cursor sharing though; our experience with it is touch and go at best. In 8i there were several bugs associated with cursor sharing as well as in 9i. Most of these bugs should have been removed in the Oracle 10g release. So, before you implement, make sure you test it carefully.
You can implement cursor sharing on a session-by-session basis as well. This change will only be effective for that session. This change is made using the alter session command as seen in this example:
As with the cursor sharing parameter in the database parameter file, you can set cursor_sharing to FORCE, SIMILAR or EXACT in the alter session command we just demonstrated.
Important Points
- If bind variables are not used, then there is hard parsing of all SQL statements. This has a severe impact on performance, and it is non-scalable.
- Not using cursors results in repeated parses.
- Use cursors with bind variables that open the cursor and re-execute it many times.
- Be suspicious of applications generating dynamic SQL.
Implementing Bind Variables in Various Languages
- Precompilers (PRO*C, PRO*COBOL) - Precompilers fully support bind variables. In versions 1.x of the precompilers bind, variables should be declared in the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION statements. In version 2.x of the precompilers, any variable can be used as a bind variable.
- OCI7 - Any host variable can become a bind variable in OCI. Bind variables are prefixed by colons in the SQL statement in the normal manner. When you are using the largely obsolete OBNDRN call to bind your variables, the bind variables are denoted by numbers (for example, :1, :2) in the SQL statement. Otherwise, the bind variables are defined as colon-prefixed names (for example, :SURNAME).
You bind each variable to the SQL statement with a separate call to the bind function. Three bind functions exist in OCI (OBNDRN, OBNDRV, and OBNDRA). The OBNDRA call provides the most functionality and should be used in new applications.
- OCI8 - OCI8 supports bind variables. The OCIBindByName() function allows you to bind a named parameter. The OCIBindByPos() function allows you to bind positional parameters.
- JDBC - Prepared statements can contain bind variables that allow a single statement to be reused with different input or selection criteria. Parameter values are set using Statement methods of the form setDtype method, where Dtype represents the datatype of the column for which the value is retrieved (for example, setString, setInt, setFloat, and so forth). The parameters are identified by their position in the SQL statement.
- SQLJ - In SQLJ, any Java variable of an appropriate type can be a SQL bind variable. Bind variables are prefixed by a colon (:) in SQL statements.
- Oracle Objects for OLE - ORACLE Objects for OLE supports bind variables. These bind variables are represented by the OraParameter object and the OraParameters collection of an OraDatabase object. In an SQL statement, the parameters are referenced using the usual leading colon convention. To create a bind variable, you use the Parameter.Add method of the database object.
- ODBC - The ODBC API fully supports bind variables through the SQLbindparameter call. Bind variables are represented by a question mark (?) in the source SQL.
The ODBC API has a low-level implementation and is commonly accessed using middleware layers that allow a more high-level and productive programming environment. Therefore, the vendors who develop ODBC-based middleware products must often compromise performance for portability. For example, not all server databases support bind variables, and as a result, many ODBC products do not implement a bind variable capability. Array fetch and cursor reuse are more frequently, but not universally, implemented. When you are using such a middleware product, you should consult your user documentation.
Below is an example of using variables in SQL Server 2000.
I want to do the exact same thing in Oracle using SQL Developer without additional complexity. It seems like a very simple thing to do, but I can't find a simple solution. How can I do it?
As a stored procedure or as a script? If you're hardcoding the value for EmpIDVar, why use a variable at all?
Выполнение PL/SQL блоков
Пример PL/SQL блока:
Правила выполнения PL/SQL блоков:
- Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен. - Блок может состоять из нескольких строк
- Можно вставлять /* комментарии */, они также могут быть на несколько строк
- Пустые строки не разрешены внутри блока
Сигнал к выполнению блока может быть подан двумя путями:
- Строка, содержащая только слеш «/» после блока — выполнить сразу
- Строка, содержащая точку «.» после блока — поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша «/» или команды RUN.
Одиночное выражение PL/SQL может быть выполнено, используя:
В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:
Oracle 12: Адаптивные планы
Смена плана во время выполнения запроса. На основе кол-ва данных полученных из шагов запроса генерируется оптимальный план следующего шага.
Адаптивность может сменить:
* Тип соединения NL HJ
* тип parallel distribution
* Bitmap Index Pruning
Автор статьи – Виктор Варламов(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
SQL*Plus — программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.
Основное преимущество SLQ*Plus — доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.
Настройки выполнения скриптов
Действуют на протяжении сессии в SQLplus.
Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:
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
Выполнение SQL запросов
Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.
Запрос может быть выполнен тремя способами:
- точка с запятой в конце запроса
- строка с слешем «/» после запроса
- пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
10 Answers 10
I am using the SQL-Developer in Version 3.2. The other stuff didn't work for me, but this did:
Also it's the slickest way presented here, yet.
(If you omit the "define"-part you'll be prompted for that value)
If comparing &&value1 to a string value like: &&value1 = 'Some string' then &&value1 needs to be wrapped in single quotes like: '&&value1' = 'Some string'
In SQL Developer, substitution variables defined by DEFINE seem to be persistent between query executions. If I change the variable value, but do not explicitly highlight the DEFINE line when executing, the previous value remains. (Is this because of the double && ?)
This page in section 2.4 talks about the difference between the single ampersand (&) and double ampersand (&&)
For those of us used to working with queries with variables in Microsoft SQL Server Management Studio, this is the best answer. We may need to get used to highlighting the whole query before executing, though.
This answer was the solution that worked for me in SQL-Developer 2.1. It's definitely the most straight-forward method to implement a way for the user to change a value above the query and not have to edit the query itself.
There are two types of variable in SQL-plus: substitution and bind.
This is substitution (substitution variables can replace SQL*Plus command options or other hard-coded text):
This is bind (bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result sets):
SQL Developer supports substitution variables, but when you execute a query with bind :var syntax you are prompted for the binding (in a dialog box).
-
SQL*Plus Substitution Variables, Christopher Jones, 2004
UPDATE substitution variables are a bit tricky to use, look:
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 переменные.
Работа с переменными
Переменные могут быть заданы двумя способами:
Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.
Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus — будет использовано старое значение переменной. Чтобы этого избежать — можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:
ACCEPT можно использовать для валидации:
Для ввода дат в определенном формате:
SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.
Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.
Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:
Присвоить bind-переменной значение &-переменной:
Вывести значение bind-переменной:
Присвоить &-переменной значение bind-переменной:
Получаем OUT-параметр процедуры в bind-переменную:
Условное выполнение в SQLplus:
Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:
Параметры, подключение к базе, запуск скриптов
Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:
Читайте также: