USE OF BIND VARIABLES
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. This process is known as SOFT PARSE. 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. The search for the previously executed sql statement is case sensitive.
Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.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 «substitution» variables that are used in place of literals and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.
By reusing the execution plan every time, we reduce the time required for parsing the Query, 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.
Every reference to a PL/SQL variable is in fact a bind variable.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.
Eg:- execute immediate ' select emp_name from emp where emp_no = :i' using emp_number
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.
The Concept of allowing the Oracle optimizer to re use the sane execution plan might result in some performance degradation because of the data distribution of the column for which the value if passed. Oracle optimizer may use the same execution plan for two different values for the column with different data distributions for the column values. Oracle introduced the concept of Bind Variable peeking to peek into the value of the Bind Variable to decide upon the execution plan.
The implementation of bind variable peeking could cause performance problems, and many shops would disable bind variable peeking by getting permission to set the hidden parameter _optim_peek_user_binds=false.
Adaptive Cursor Sharing
In Oracle Database 11g, the database feature called adaptive cursor sharing enables a SQL statement with bind variables to use multiple execution plans, with each execution plan based on the values of the bind variable(s).Adaptive cursor sharing is enabled by default, and you can't disable it.
Adaptive cursor sharing is a new feature introduced in the Oracle Database 11g release. In earlier releases, DBAs often flushed the shared pool (and worse, sometimes restarted the database) when confronted with situations where the database apparently started using an inappropriate execution plan for a SQL statement, due to the bind peeking effect. In the 11g release, you don't have to do anything—the optimizer automatically changes execution plans when it encounters skewed data. With adaptive cursor sharing, the database uses multiple execution plans for a statement that uses bind variables, ensuring that the best execution plan is always used, depending on the value of the bind variable. Adaptive cursor sharing means that when different bind variable values indicate different amounts of data to be handled by the query, Oracle adapts its behavior by using different execution plans for the query instead of sticking to the same plan for all bind values. Since adaptive cursor sharing works only where literal values are replaced with binds, Oracle encourages you to use the FORCE setting for the cursor_sharing parameter. If you set the parameter to SIMILAR and you have a histogram on a column, the optimizer doesn't perform a literal replacement with bind variables, and thus adaptive cursor sharing won't take place. You must set the cursor_sharing parameter to FORCE for adaptive cursor sharing to work, thus letting the optimizer select the optimal execution plan for different values of the bind variable.
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. This process is known as SOFT PARSE. 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. The search for the previously executed sql statement is case sensitive.
Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.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 «substitution» variables that are used in place of literals and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.
By reusing the execution plan every time, we reduce the time required for parsing the Query, 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.
Every reference to a PL/SQL variable is in fact a bind variable.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.
Eg:- execute immediate ' select emp_name from emp where emp_no = :i' using emp_number
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.
The Concept of allowing the Oracle optimizer to re use the sane execution plan might result in some performance degradation because of the data distribution of the column for which the value if passed. Oracle optimizer may use the same execution plan for two different values for the column with different data distributions for the column values. Oracle introduced the concept of Bind Variable peeking to peek into the value of the Bind Variable to decide upon the execution plan.
The implementation of bind variable peeking could cause performance problems, and many shops would disable bind variable peeking by getting permission to set the hidden parameter _optim_peek_user_binds=false.
Adaptive Cursor Sharing
In Oracle Database 11g, the database feature called adaptive cursor sharing enables a SQL statement with bind variables to use multiple execution plans, with each execution plan based on the values of the bind variable(s).Adaptive cursor sharing is enabled by default, and you can't disable it.
Adaptive cursor sharing is a new feature introduced in the Oracle Database 11g release. In earlier releases, DBAs often flushed the shared pool (and worse, sometimes restarted the database) when confronted with situations where the database apparently started using an inappropriate execution plan for a SQL statement, due to the bind peeking effect. In the 11g release, you don't have to do anything—the optimizer automatically changes execution plans when it encounters skewed data. With adaptive cursor sharing, the database uses multiple execution plans for a statement that uses bind variables, ensuring that the best execution plan is always used, depending on the value of the bind variable. Adaptive cursor sharing means that when different bind variable values indicate different amounts of data to be handled by the query, Oracle adapts its behavior by using different execution plans for the query instead of sticking to the same plan for all bind values. Since adaptive cursor sharing works only where literal values are replaced with binds, Oracle encourages you to use the FORCE setting for the cursor_sharing parameter. If you set the parameter to SIMILAR and you have a histogram on a column, the optimizer doesn't perform a literal replacement with bind variables, and thus adaptive cursor sharing won't take place. You must set the cursor_sharing parameter to FORCE for adaptive cursor sharing to work, thus letting the optimizer select the optimal execution plan for different values of the bind variable.
No comments:
Post a Comment