[Oracle] DB Optimizer Part VI – Effects of disabled bind variable peeking, adaptive cursor sharing and cardinality feedback on the CBO in SAP environments
We already talked about dynamic sampling, hints, histograms or extending and interpreting execution plans in the previous blog posts of my Oracle DB Optimizer series (CBO). In many cases i have written something like this “we will use literals for simplification” or “we will use literals, because of SAP treats bind variables very rudimentarily”.
So this blog is about using bind variables in a SAP environment and how it effects the CBO calculations / decisions. The first important point to mention is, that bind variables are absolutely necessary in high transactional OLTP environments to avoid a high hard parsing rate and the corresponding latches/locks and CPU usage.
Bind variables have their side effects as well, but Oracle engineered several optimizer enhancements to workaround or avoid them. Before we start with the demo cases – let’s check the official documentation first.
A placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.
Bind Variable Peeking
In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement.
When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
Adaptive Cursor Sharing
The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is “adaptive” because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is “adaptive” because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.
Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.
Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.
During the first execution of a SQL statement, an execution plan is generated as usual. During optimization, certain types of estimates that are known to be of low quality (for example, estimates for tables which lack statistics or tables with complex predicates) are noted, and monitoring is enabled for the cursor that is produced. If cardinality feedback monitoring is enabled for a cursor, then at the end of execution, some of the cardinality estimates in the plan are compared to the actual cardinalities seen during execution. If some of these estimates are found to differ significantly from the actual cardinalities, the correct estimates are stored for later use. The next time the query is executed, it will be optimized again, and this time the optimizer uses the corrected estimates in place of its usual estimates.
SAPnote #1431798 – Oracle 11.2.0: Database Parameter Settings
_OPTIM_PEEK_USER_BINDS FALSE (Note 755342)
SAPnote #755342 – Incorrect execution plans with bind variable peeking
From time to time, you may notice incorrect execution plans for SQL statements that otherwise run without any problems. After executing the command “ALTER SYSTEM FLUSH SHARED_POOL;” and other actions that trigger additional parsing, the system often selects a different path.
Bind variable peeking is executed, that is, Oracle only decides on an execution plan once it knows the first value of the bind variable.
The phenomenon only occurs as of Oracle Version 9.2 in connection with SAP R3 Release 6.1 or higher, because the Oracle C-interface Version 8 (OCI8) is used there. OCI7 is used for earlier kernel releases. OCI7 does not permit any bind variable peeking.
This way of determining the execution plan is used as standard
– whenever histograms exist for the columns of the WHERE condition
– whenever there are statistics without histograms for these columns and the operators > or < (or <= and >=) are used or when the columns are marked with “=”, but the value lies outside of the min/max area of the relevant column.
To deactivate bind variable peeking, select
_optim_peek_user_binds = false
in init<sid>.ora and restart.
In older SAP kernel releases (lower than 6.1), the system does not take the value of the parameter into account. However you should still set it to avoid problems during upgrades. You can ignore any messages that the system may issue in Early Watch reports for these versions.
Aren’t these Oracle CBO enhancements great? Yes they are (except bugs of course) and they also make several workarounds in non-SAP Oracle environments unnecessary, but unfortunately all of these Oracle features are disabled in a SAP environment by default (referring to the hidden parameters in SAPnote #1431798 from above).
Why? I know that there were several (nasty) ACS bugs in the starting time of Oracle 11 g, but i guess that the main reason for SAP nowadays is that it makes (performance) troubleshooting more complex and so the clients and SAP support would need much more know how in this area. Finally i really don’t know.
How does the cost based optimizer behave in a SAP environment when there is no bind variable peeking, adaptive cursor sharing or cardinality feedback? The following demo will show the impact in several cases to get an idea of the basics.
The following demo was run with an Oracle database (18.104.22.168.2) on OEL 6.2. The following test case is a really simple one, but it illustrates the impact clearly.
Build up test data
SYS@T11:13> create table BINDTEST (num NUMBER, text VARCHAR2(40)); SYS@T11:13> begin for i in 1 .. 10000 loop insert into BINDTEST values (i, 'TEST TEXT TO FILL'); end loop; commit; end; / SYS@T11:13> exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'BINDTEST', method_opt => 'FOR ALL COLUMNS SIZE 1');
I created a table called BINDTEST with two columns (NUM and TEXT). The values of column NUM are distributed equally (1 to 10.000 for 10.000 rows) and the database knows all about it after the statistic collection. Column TEXT is irrelevant in the following test cases – i just used it to increase the table size a bit. Column NUM could be something like a order or idoc number in a SAP environment.
SQL*Plus environmental and session settings
SYS@T11:13> var A0 NUMBER; SYS@T11:13> var A1 NUMBER; SYS@T11:13> var A2 NUMBER; SYS@T11:13> exec :A0 := 2000; SYS@T11:13> exec :A1 := 8000; SYS@T11:13> exec :A2 := 12000; SYS@T11:13> alter session set "_OPTIM_PEEK_USER_BINDS" = FALSE; SYS@T11:13> alter session set "_OPTIMIZER_ADAPTIVE_CURSOR_SHARING" = FALSE; SYS@T11:13> alter session set "_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL" = 'NONE'; SYS@T11:13> alter session set "_OPTIMIZER_USE_FEEDBACK" = FALSE;
I created 3 bind variables with 3 different values (2000, 8000 and 12000) and i will also use these values as literals for comparison. I set the corresponding optimizer settings (disabled bind peeking, adaptive cursor sharing and cardinality feedback) on session level to get the same behavior as running this database for a SAP system.
Test case 1 – Using literals and bind variables with equal operator
In case of an equality operator the optimizer calculates the same amount of estimated rows (=cardinality). This estimation is correct as well in our case as the values of column NUM are distributed equally. This is a very common case in SAP OLTP environments and works pretty well.
In case of unequally distributed values we would need a histogram and literals to get a good cardinality estimation (as the optimizer is not able to take a look at the value of the bind variable due to disabled bind peeking).
Cardinality = 10000 rows * 0.0001 = 1 row
Test case 2 – Using literals and bind variables with unbounded and open range predicate
We see a huge underestimation of the cardinality with the bind variable in case of an unbounded and open range predicate (> 2000 / :A0). What is the reason for this?
Let’s start with simple arithmetic for the literal value: The optimizer is aware of the following variables (all values greater than 2000 are requested, the table has 10000 rows, the min value is 1 and the max value is 10000 for column NUM)
Optimizer calculation for literal value:
Selectivity = (high_value – limit) / (high_value – low_value) = (10000 – 2000) / (10000 – 1) = 0,8000800080008
Cardinality = 10000 rows * 0,8000800080008 = round(8000,800080008) = 8001 rows
… if you check the formula closely it is based on the known literal value 2000. The optimizer is not aware of this value in case of a bind variable with disabled bind peeking. The optimizer needs to make a guess for the bind variable and it simply sets the selectivity to 5 % in such cases.
Optimizer calculation for bind variable:
Cardinality = 10000 rows * 0.05 = 500 rows
Test case 3 – Using literals and bind variables with bounded and closed range predicate
We see a huge underestimation of the cardinality with the bind variables in case of a bounded and closed range predicate (between 2000 and 8000 / :A0 and :A1). What is the reason for this?
In addition you can see that the execution plan changed as well based on the added predicates by query transformation (additional check for runtime – maybe there is no need to run execution plan step 2 based on the bind variable values).
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "BINDTEST"."NUM" "NUM","BINDTEST"."TEXT" "TEXT" FROM "SYS"."BINDTEST" "BINDTEST" WHERE "BINDTEST"."NUM">=:B1 AND "BINDTEST"."NUM"<=:B2 AND :B3<=:B4 kkoqbc: optimizing query block SEL$1 (#0)
Let’s start with simple arithmetic for the literal values: The optimizer is aware of the following variables (all values between 2000 and 8000 including the limits are requested, the table has 10000 rows, the min value is 1 and the max value is 10000 for column NUM)
Optimizer calculation for literal values:
Selectivity = (high limit – low limit) / (high_value – low_value) + 1/num_distinct (=density) + 1/num_distinct (=density) = (8000 – 2000) / (10000 – 1) + 0.0001 + 0.0001 = 0,6002600060006
Cardinality = 10000 rows * 0,6002600060006 = round(6002,600060006) = 6003 rows
… if you check the formula closely it is based on the known literal values 2000 and 8000. The optimizer is not aware of these values in case of bind variables with disabled bind peeking. The optimizer needs to make a guess for the bind variables and it simply sets the selectivity to 0.25 % (= 5% of 5%) in such cases.
Optimizer calculation for bind variables:
Cardinality = 10000 rows * 0.0025 = 25 rows
Test case 4 – Using literals and bind variables with unbounded and open outside range predicate
We see a huge overestimation of the cardinality with the bind variable in case of an unbounded and open outside range predicate (> 12000 / :A2). What is the reason for this?
Optimizer calculation for literal value:
This is a very special case as the requested values are placed outside the known value range (1 to 10000) and so the optimizer assumes that no rows are returned (1 estimated row means 0 or 1 row returned). The exact calculation is very Oracle database version dependent (and on the range to the known value range) and changed very often in the past.
Optimizer calculation for bind variable:
Cardinality = 10000 rows * 0.05 = 500 rows
The optimizer behaves exactly like in test case 2 as it is not aware of the bind value (and that it is outside the known range).
We have seen that the optimizer does nonsense cardinality calculations in various cases by using bind variables (without bind peeking and all the additional features around it). Be aware of optimizer calculations, if you see cardinality estimations based on 5 % (or a multiplication of it) – it could be a guess.
“The CBO is only as clever as the provided statistic and runtime data (except bugs and limits).”
As SAP suggests to disable the mentioned features and most costumers follow them of course – the most important question is: “What can i do, if i notice that behavior?” … well as always – it depends 😉
Here are just a few possibilities:
- Use ABAP hints like “&SUBSTITUTE LITERALS&” or “&SUBSTITUTE VALUES&”, if the SQL statements are not executed too frequently with different values
- Use SQL profiles or SQL patches to force the right execution plan
- Adjust the statistics so that the optimizer calculates the right cardinality based on its assumptions
- … and so on
If you have any further questions – please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database (performance) issues.
- Oracle Documentation – Adaptive Cursor Sharing
- Oracle Documentation – Bind Variable
- Oracle Documentation – Bind Variable Peeking
- Oracle Optimizer Blog – Cardinality feedback
- SAPnote #755342 – Incorrect execution plans with bind variable peeking
- SAPnote #1431798 – Oracle 11.2.0: Database Parameter Settings