Bind variables 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.
Manipulating Commands, 5 of 7
Using Bind Variables
Suppose that you want to be able to display the variables you use in your PL/SQL subprograms in SQL*Plus or use the same variables in multiple subprograms. If you declare a variable in a PL/SQL subprogram, you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL to access the variable from SQL*Plus.
Bind variables are variables you create in SQL*Plus and then reference in PL/SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms.
Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in other PL/SQL subprograms that you run in SQL*Plus.
Creating Bind Variables
You create bind variables in SQL*Plus with the VARIABLE command. For example
This command creates a bind variable named ret_val with a datatype of NUMBER. For more information, see the VARIABLE command in Chapter 8. (To list all of the bind variables created in a session, type VARIABLE without any arguments.)
Referencing Bind Variables
You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example
To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example
This command assigns a value to the bind variable named ret_val.
Displaying Bind Variables
To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example
This command displays a bind variable named ret_val. For more information about displaying bind variables, see the PRINT command in the "Command Reference" in Chapter 8.
Example 3-17 Creating, Referencing, and Displaying Bind Variables
To declare a local bind variable named id with a datatype of NUMBER, enter
Next, put a value of "1" into the bind variable you have just created:
If you want to display a list of values for the bind variable named id, enter
Если бы мне пришлось писать книгу о том, как создавать немасштабируемые приложения Oracle, первая и единственная ее глава называлась бы «Не используйте связываемые переменные».
Том Кайт, вице-президент Oracle
Недавно на Хабре появилась статья от AlexanderPHP «SQL injection для начинающих. Часть 1». По ее содержимому и комментарием к ней может создаться впечатление, что у многих разработчиков нет понятия, что такое связываемые переменные, зачем ими следует пользоваться и какие преимущества они дают. Попытаюсь в данной статье пролить небольшой свет на данные вопросы.
Определение связываемых переменных
Связываемые переменные, они же prepared statements, они же подготовленные выражения (четко устоявшегося перевода обнаружить не удалось; будем использовать и тот, и тот) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Например, у нас есть запрос:
insert into someTable(name) values(‘Вася’);
Что мы можем заметить, просто посмотрев на него? Во-первых, сам запрос insert обычно статичен и не меняется в разных запросах, в 90% случаев просто жестко вбит в коде или генерируется при помощи некоторого ORM; значение данных (в данном случае 'Вася') меняется постоянно и задается извне — из ввода пользователя или из других источников. Связываемые переменные позволяют задать запрос отдельно, а потом передавать данные в него отдельно, приблизительно так (псевдокод):
Так мы отдельно задаем запрос, вместо данных подставляя в него номера связываемых переменных (:1, :2. ) или просто вопросительные знаки. Далее вызываем запрос, указывая, какие именно данные надо подставить вместо указанных переменных.
Результат выполнения этого кода полностью аналогичен результату выполнения запроса insert into someTable(name) values(‘Вася’); , но есть несколько важных отличий, которые будут рассмотрены далее.
Преимущества и особенности связываемых переменных
При использовании связываемых переменных есть несколько преимуществ:
1. Очевидное преимущество — один и тот же подготовленный запрос можно использовать несколько раз для разных данных, тем самым сокращая код.
2. Запросы со связываемыми переменными лучше кэшируются сервером, сокращая время синтаксического разбора.
3. Запросы со связываемыми переменными обладают готовой встроенной защитой от SQL-инъекций.
Рассмотрим каждый пункт подробнее.
Первый пункт очевиден — при наборе данных можно использовать одно и тоже подготовленное выражение несколько раз:
Код генерации SQL-запроса сокращается, а вам любой разработчик скажет, что сокращение объемов кода — это сокращение вероятности ошибки в нем.
Для пояснения второго пункта следует рассказать подробнее, как именно сервер баз данных обрабатывает SQL-запрос. Первейшим этапом выполнения запроса является синтаксический разбор самого запроса, то есть сервер переводит запрос из SQL-языка в какой-то свой внутренний формат, чтобы определить, что именно хочет от сервера клиент. За синтаксическим разбором следует собственно выполнение — составление плана запроса, формирование индексов, сканирование таблиц и множество других неинтересных вещей. Надо отметить, что сам по себе синтаксический разбор — операция довольно «тяжелая» по времени выполнения (хотя бы по сравнению с поиском по индексу, например). Подавляющее большинство современных систем управления базами данных (увы, насколько я знаю, MySQL в данном случае к таковым не относится), «умеют» кэшировать результаты синтаксического разбора и заново использовать их. В этом случае становится очень выгодным, если есть возможность повторять один и тот же SQL-запрос не один раз — будет использоваться синтаксический кэш. Обратимся к примеру в пункте 1 — очевидно, что в данном случае синтаксический разбор выполняется один раз, хотя сам запрос — четыре раза. Если бы мы писали:
то в этом случае каждый раз запрос для сервера был бы новым (потому что анализируется полный текст запроса), и синтаксический разбор пришлось бы выполнить четырежды. Это еще не говоря о том, что такие запросы забивают «мусором» описанный синтаксический кэш.
Перейдем к третьему пункту. Почему же связываемые переменные — это гарантированная защита от SQL-инъекций (по крайней мере, того типа, который рассматривается в упомянутой статье)? Существует заблуждение (у меня оно точно было), что prepared statements – это просто синтаксическая «нашлепка» на команду sql_execute (mysql_real_query, например), которая просто
экранирует все указанные переменные, собирает в одну строку и просто вызывает команду sql_execute, избавляя программиста от некоторого ручного труда. Это не так. На самом деле prepared statement – отдельная возможность в любой вменяемой СУБД. Для этой возможности есть отдельные функции в библиотеке, отдельные места в бинарном протоколе между клиентской и серверной частью СУБД. Более того, собственно подготовленный запрос и данные, которые в нем используются, передаются на сервер отдельно. В клиентских библиотеках есть отдельные команды подготовки выражений (для примера можно посмотреть документацию MySQL C API, PostgreSQL C library).
Примечание: есть исключение — в PHP PDO связываемые переменные по умолчанию эмулируются именно описанным методом, то есть конструированием SQL-команды на клиентской стороне. Это лучше отключать (взято со StackOverflow):
Из этого следует важный вывод — поскольку данные передаются полностью отдельно от запроса, у этих данных нет никаких возможностей модифицировать запрос. Нет возможностей. Вообще никаких. (Экзотические атаки типа переполнения буфера здесь мы не рассматриваем — это совсем другой класс атак).
Данные не нужно экранировать, преобразовывать или как-то менять; они идут в базу данных в точно том виде, в каком нужны нам. Если нам передали строку Robert');drop table students; , не надо заботиться об экранировании, надо просто передать ее как связываемую переменную — ничего она нам не сделает, а так и будет просто лежать в базе данных, как самая обычная строка.
Комментарии к комментариям
В заключение рассмотрим несколько комментариев из уже упомянутой статьи и разберем, что же в них не так:
Это мы уже разобрали — проблема с функцией mysql_real_escape_string в том, что ей вообще пользуются. Со связываемыми переменными ей не надо пользоваться. Это экономия на клиентской части (представьте, что функции надо «шерстить» мегабайтную строку, чтобы найти места, где все-таки поставить обратный слэш), а остальные преимущества уже расписаны в статье.
@m_z21
PDO и ORM не панацея. И с использованием pdo можно наделать подобных дыр, если нет понимания как работают sql-инъекции.
Каким боком сюда приплели ORM – непонятно. А вот PDO (и MySQLi) как раз панацея, поскольку SQL injection при их грамотном использовании невозможны, как уже и было описано.
@VolCh21
Выигрыш по потребляемым ресурсам (скорости, памяти), т. к. mysql_* является по сути просто биндингами к libmysql, а mysqli/pdo создают ненужный во многих случаях объектный слой?
И это фактическая ошибка. Команды типа mysqli::prepare — это тоже всего лишь биндинги к соответствующим функциям клиентской библиотеки MySQL. Если желаете убедиться, то можете сами посмотреть на исходные коды PHP. Соответственно, расходы на (якобы ненужный) объектный слой даже если и есть, то они минимальные. Да и экономия на объектном слое уж очень сильно напоминает «экономию на спичках».
Заключение
Надеюсь, мне удалось прояснить для кого-то такую несомненно важную тему, как связываемые переменные (prepared statements). Надеюсь, что многие хотя бы задумаются над тем, чтобы всегда использовать связываемые переменные при работе с БД. Я не претендую на абсолютную полноту и точность изложения, так что буду только рад, если у кого-то найдется что добавить, убавить или откорректировать в написанном.
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 переменные.
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
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
Oracle 12: Адаптивные планы
Смена плана во время выполнения запроса. На основе кол-ва данных полученных из шагов запроса генерируется оптимальный план следующего шага.
Адаптивность может сменить:
* Тип соединения NL HJ
* тип parallel distribution
* Bitmap Index Pruning
Bind variables - The key to application performance
If you've been developing applications on Oracle for a while, you've no doubt come across the concept of « Bind Variables » . Bind variables are one of those Oracle concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and how you need to alter your programming style to use them.
To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example:
SELECT fname, lname, pcode FROM cust WHERE >
SELECT fname, lname, pcode FROM cust WHERE 234;
SELECT fname, lname, pcode FROM cust WHERE 332;Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a « hard parse » and for OLTP applications can actually take longer to carry out that the DML instruction itself.
When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from to and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive , and involves obtaining latches on key shared memory areas , which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time. The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set.
The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit
SELECT fname, lname, pcode FROM cust WHERE >
and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset.
Bind Variables in SQL*Plus
In SQL*Plus you can use bind variables as follows:
SQL> variable deptno number
SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement)
Bind Variables in PL/SQL
Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the issues to do with bind variables , to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL:
create or replace procedure dsal(p_empno in number)
as
begin
update emp
set sal=sal*2
where empno = p_empno;
commit;
end;
/Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.
In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.
Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:
create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set sal = sal*2 where empno = '||p_empno;
commit;
end;
/The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:
create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set
sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.
The Performance Killer
Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:
Here is the Performance Killer .
SQL> alter system flush shared_pool;
SQL> set serveroutput on;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds. ' );
end;
/
101.71 Seconds.
. and here is the Performance Winner:
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds. ' );
end;
/
1.9 Seconds.
That is pretty dramatic . The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously.
Bind Variables in VB, Java and other applications
The next question is though, what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself?
In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables , and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.
For example, Java has PreparedStatement , which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature.
Lastly, it's worth bearing in mind that there are some instances where bind variables are probably not appropriate, usually where instead of your query being executed many times a second (as with OLTP systems) your query in fact actually takes several seconds, or minutes, or hours to execute - a situation you get in decision support and data warehousing. In this instance, the time taken to hard parse your query is only a small proportion of the total query execution time, and the benefit of avoiding a hard parse is probably outweighed by the reduction in important information you're making available to the query optimizer - by substituting the actual predicate with a bind variable, you're removing the ability for the optimiser to compare your value with the data distribution in the column, which might make it opt for a full table scan or an index when this isn't appropriate. Oracle 9i helps deal with this using a feature known as bind variable peeking, which allows Oracle to look at the value behind a bind variable to help choose the best execution plan.
Another potential drawback with bind variables and data warehousing queries is that the use of bind variables disallows the potential for star transformations, taking away this powerful option for efficiently joining fact and dimension tables in a star schema.
Читайте также: