1 / 25

Reusability of SQL: Cursor Sharing, Bind Variables and Bind Peeking

Reusability of SQL: Cursor Sharing, Bind Variables and Bind Peeking. Sameer Malik sameer18july@gmail.com (770) 876-3281 Oracle 9i and Oracle 10g certified professional, Six Sigma Certified (Green Belt) Sun Certified Solaris -10 System Administrator. Topics.

Sophia
Download Presentation

Reusability of SQL: Cursor Sharing, Bind Variables and Bind Peeking

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Reusability of SQL: Cursor Sharing, Bind Variables and Bind Peeking Sameer Malik sameer18july@gmail.com (770) 876-3281 Oracle 9i and Oracle 10g certified professional, Six Sigma Certified (Green Belt) Sun Certified Solaris -10 System Administrator

  2. Topics 1) Cursors and Cursor Sharing. 2) Parent and child cursors. 3) Cursor_Sharing Parameter effect. 4) Bind Variables. 5) Bind variable Peeking. 6) Eliminating bind peeking problem. 7) Best practices for using bind variables.

  3. Life Cycle of a Cursor - I • Open cursor: A memory structure for the cursor is allocated in the server-side private memory of the server process associated with the session, the user global area (UGA). Note that no SQL statement is associated with the cursor yet. • Parse cursor: A SQL statement is associated with the cursor. Its parsed representation that includes the execution plan (which describes how the SQL engine will execute the SQL statement) is loaded in the shared pool, specifically, in the library cache. The structure in the UGA is updated to store a pointer to the location of the shareable cursor in the library cache. • Define output variables: If the SQL statement returns data, the variables receiving it must be defined. This is necessary not only for queries but also for DELETE, INSERT, and UPDATE statements that use the RETURNING clause.

  4. Life Cycle of a Cursor - II • Bind input variables: If the SQL statement uses bind variables, their values must be provided . No check is performed during the binding. If invalid data is passed, a runtime error will be raised during the execution. • Execute cursor: The SQL statement is executed. But be careful, because the database engine doesn’t always do anything significant during this phase. In fact, for many types of queries , the real processing is usually delayed to the fetch phase. • Fetch cursor:If the SQL statement returns data, this step retrieves it. Especially for queries , this step is where most of the processing is performed. In the case of queries, rows might be partially fetched. In other words, the cursor might be closed before fetching all the rows. • Close cursor: The resources associated with the cursor in the UGA are freed and consequently made available for other cursors. The shareable cursor in the library cache is not removed. It remains there in the hope of being reused in the future.

  5. How Parsing Works - I • Include VPD predicates: If Virtual Private Database (VPD, formerly known as row-level security) is in use and active for one of the tables referenced in the parsed SQL statement , the predicates generated by the security policies are included in its WHERE clause. • Check syntax, semantics, and access rights: This step makes sure not only that the SQL statement is correctly written but also that all objects referenced by the SQL statement exist and the current user parsing it has the necessary privileges to access them. • Store parent cursor in library cache: Whenever a shareable parent cursor is not yet available , some memory is allocated from the library cache, and a new parent cursor is stored inside it . The key information associated with the parent cursor is the text of the SQL statement.

  6. How Parsing Works - II • Logical optimization: During this phase, new and semantically equivalent SQL statements are produced by applying different transformation techniques. In doing so, the amount of execution plans considered, the search space, is increased. The purpose is to explore execution plans that would not be considered without such transformations. • Physical optimization:During this phase, At first, the execution plans related to each SQL statement resulting from the logical optimization are generated. Then, based on statistics found in the data dictionary or gathered through dynamic sampling, a cost is associated with each execution plan. Lastly, the execution plan with the lowest cost is selected. Simply put, the query optimizer explores the search space to find the most efficient execution plan. • Store child cursor in library cache:Some memory is allocated, and the shareable child cursor is stored inside it and associated with its parent cursor. The key elements associated with the child cursor are the execution plan and the execution environment.

  7. More on Parsing… • Once stored in the library cache, parent and child cursors are externalized through the views v$sqlarea and v$sql, respectively. The cursors are identified in three columns: address, hash_value, and child_number. With address and hash_value, the parent cursors are identified with all three values, the child cursors are identified. In addition, as of Oracle Database 10g, it is also possible, and it is more common as well, to use sql_id instead of the pair address and hash_value for the same purpose. • When shareable parent and child cursors are available and, consequently, only the first two operations are carried out, the parse is called a soft parse. When all operations are carried out, it is called a hard parse. • The result of a parse operation is a parent and a child cursor stored in the library cache. The aim of storing them in a shared memory area is to allow their reutilization and thereby avoid hard parses.

  8. Examples: Cursor Sharing • Example -1 :- The purpose of the first example is to show a case where the parent cursor cannot be shared. The key information related to a parent cursor is the text of a SQL statement. Therefore, several SQL statements share the same parent cursor if their text is exactly the same. • Example -2 :- It show a case where the parent cursor, but not the child cursor, can be shared. The key information related to a child cursor is an execution plan and the execution environment related to it. The execution environment is important because if it changes, the execution plan might change as well. As a result, several SQL statements are able to share the same child cursor only if they share the same parent cursor and their execution environments are compatible.

  9. Cursor_Sharing Parameter Effect • CURSOR_SHARING is a parameter Oracle Database uses to control whether it will "auto-bind" a SQL statement. • Oracle Database can take a query of the form SELECT * FROM TABLE WHERE COL = 'literal' and replace the 'literal' with a bind value , so the predicate will become WHERE COL = :"SYS_B_0". This permits the reuse of the generated query plan, perhaps leading to better utilization of the shared pool and a reduction of hard parses performed by the system. • The CURSOR_SHARING parameter can have one of three values: 1) Exact; 2) Force; 3)Similar

  10. 1. EXACT-This is the default setting. With this value in place, the query is not rewritten to use bind variables. With CURSOR_SHARING=EXACT (the default), every unique SQL statement executed will create a new entry in V$SQL, it will be hard-parsed, and an execution plan will be created just for it. There can be hundreds or thousands of very similar queries in the shared pool that differ only in the literals used in the SQL statement itself. This implies that the database is forced to hard-parse virtually every query, which, in turn, not only consumes a lot of CPU cycles but also leads to decreased scalability. The database just cannot hard-parse hundreds or thousands of SQL statements concurrently—the application ends up waiting for the shared pool to become available. One of the major scalability inhibitors in the database is not using bind variables. That was the motivation behind adding CURSOR_SHARING=FORCE .

  11. 2. FORCE-This setting rewrites the query, replacing all literals with bind values and setting up a one-size-fits-all plan—a single plan for the rewritten query. With CURSOR_SHARING=FORCE in place the database generated only one shareable query in the shared pool—it replaced 'A' and 'B' with :"SYS_B_0" and made the cursor shareable by as many sessions as would need it. In general, just one query plan would be reused by all sessions. This would turn the hard parse into a soft parse, which would consume fewer resources and simultaneously increase the scalability of the system, by allowing for more concurrent work, because a soft parse needs to "latch" (use a certain type of lock on) the shared pool less than a hard parse.

  12. 3. SIMILAR - This setting also rewrites the query, replacing the literals with bind variables, but can set up different plans for different bind variable combinations. This setting may or may not reduce the number of actual plans you observe in the shared pool. When CURSOR_SHARING is set to SIMILAR, Oracle will replace all literals with bind variables, just as FORCE would, but the SIMILAR value does one other thing—it looks at each literal it replaces and asks, "Could different values for this bind variable lead, in turn, to different plans?" For example, if the predicate WHERE X=6 will have a full scan but the predicate WHERE X=5 have index range scan, the database would recognize that and set up different plans for me. In the case of different plans, you mark the bind variable as unsafe and add its value to the signature of the query, so to reuse this cursor, you must not only have the same SQL statement but also the same value for that particular bind variable. That is why SIMILAR might reduce the number of plans you see in the shared pool but, then again, might not

  13. In the example three plans were generated, because the optimizer detected that a different value used when searching against the ID column could lead to a different plan. Hence the actual bind variable value was added to the signature of that query plan and only a query with exactly the same signature could reuse the plan. There were not six queries in V$SQL, just four. It is only when the bind variable substitution is deemed unsafe that CURSOR_SHARING=SIMILAR will generate a new plan. • If we varied the literals used to search against the OBJECT_ID column—but not the ID column—in . The optimizer recognized that OBJECT_ID is safe. it would not generate different plans based on different inputs for that column, so it did not add OBJECT_ID to the signature of the cursor. • If the application were to vary the literals used against the ID column and use hundreds or thousands of unique values, it would not have a significant impact on the shared pool utilization. On the other hand, if the application used only two values against the ID column, it could have a dramatic, positive effect on shared pool utilization.

  14. Bind Variables - I • Bind variables allow the sharing of cursors in the library cache and that way avoid hard parses and the overhead associated with them. • There are, however, situations where several child cursors are created even with bind variables. The following example shows such a case: • Notice that the INSERT statement is the same as in the previous example. Only the maximum size of the VARCHAR2 variable has changed (from32 to 33). The new child cursor (1) is created because the execution environment between the first three INSERT statements and the fourth has changed. The mismatch, as can be confirmed by querying the view v$sql_shared_cursor is because of the bind variables.

  15. Bind variables - II • Bind variable graduation :-The aim of this feature is to minimize the number of child cursors by graduating bind variables (which vary in size) into four groups depending on their size. The first group contains the bind variables with up to 32 bytes, the second contains the bind variables between 33 and 128 bytes, the third contains the bind variables between 129 and 2,000 bytes, and the last contains these bind variables of more than 2,000 bytes. Bind variables of datatype NUMBER are graduated to their maximum length, which is 22 bytes. As the following example shows, the view v$sql_bind_metadata displays the maximum size of a group. Notice how the value 128 is used, even if the variable of child cursor 1 was defined as 33. • Each time a new child cursor is created, an execution plan is generated. Whether this new execution plan is equal to the one used by another child cursor also depends on the value of the bind variables.

  16. Disadvantages of Bind Variables • While using bind variables in WHERE clauses, crucial information is hidden from the query optimizer. With literals, it is able to improve its estimations. This is especially true when it checks whether a value is outside the range of available values (that is, lower than the minimum value or higher than the maximum value stored in the column) and when it takes advantage of histograms. To illustrate, let’s take a table t with 1,000 rows that store, in the column id, the values between 1 (the minimum value) and 1,000 (the maximum value): • When a user selects all rows that have an id of less than 990, the query optimizer knows (thanks to object statistics) that about 99 percent of the table is selected. Therefore, it chooses an execution plan with a full table scan. Also notice how the estimated cardinality (column Rows in the execution plan) corresponds to the number of rows returned by the query.

  17. When another user selects all rows that have an id of less than 10, the query optimizer knows that only about 1 percent of the table is selected. Therefore, it chooses an execution plan with an index scan. Also in this case, notice the good estimation. • Of course, as shown in the following example, if the first execution takes place with the value 10, the query optimizer chooses an execution plan with the index scan—and that, once more, occurs for both queries. Note that to avoid sharing the cursor used for the previous example, the queries were written in lowercase letters.

  18. Adaptive/Extended Cursor Sharing It is essential to understand that as long as the cursor remains in the library cache and can be shared, it will be reused. This occurs regardless of the efficiency of the execution plan related to it. To solve this problem, as of Oracle Database 11g, a new feature called extended cursor sharing (also known as adaptive cursor sharing) is available. Its purpose is to automatically recognize when the reutilization of an already available cursor leads to inefficient executions. The following new columns are available as of Oracle Database 11g is_bind_sensitive indicates not only whether bind variable peeking was used to generate the execution plan but also whether the execution plan depends on the peeked value. If this is the case, the column is set to Y; otherwise, it’s set to N. is_bind_aware indicates whether the cursor is using extended cursor sharing. If yes, the column is set to Y; if not, it’s set to N. If set to N, the cursor is obsolete, and it will no longer be used. is_shareable indicates whether the cursor can be shared. If it can, the column is set to Y; otherwise, it’s set to N. If set to N, the cursor is obsolete, and it will no longer be used.

  19. In the following example, the cursor is shareable and sensitive to bind variables, but it is not using extended cursor sharing: SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = '7h6n1xkn8trkd’ ORDER BY child_number; CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE 0 Y N Y When the cursor is executed several times with different values for the bind variable. After a few executions with the values 10 and 990, the information provided by the view v$sql is different. Notice that child number 0 is no longer shareable and that two new child cursors use extended cursor sharing. • SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable • FROM v$sql WHERE sql_id = '7h6n1xkn8trkd‘ ORDER BY child_number; • CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE • ----------- ----------------- ------------- ----------- • 0 Y N N • 1 Y YY • 2 Y YY Looking at the execution plans related to the cursor, as you might expect, you see that one of the new children has an execution plan based on the full table scan, while the other is based on the index scan:

  20. To further analyze the reason for the generation of the two child cursors, new dynamic performance views are available: • v$sql_cs_statistics • v$sql_cs_selectivity • v$sql_cs_histogram. The first shows whether peeking was used and the related execution statistics for each child cursor. In the following output, it is possible to confirm that for one execution, the number of rows processed by child cursor 1 is higher than for child cursor 2 Hence, in one case the query optimizer chose a full table scan and in the other an index scan.

  21. SQL> SELECT child_number, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id = '7h6n1xkn8trkd' ORDER BY child_number; CHILD_NUMBER PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS ------------ ------ ---------- -------------- ------------------------------------------------------------------------------------------------- 0 Y 1 19 3 1 Y 1 990 1 9 2 Y 1 19 3 The view v$sql_cs_selectivity shows the selectivity range related to each predicate of each child cursor. In fact, the database engine does not create a new child cursor for each bind variable value. Instead, it groups values together that have about the same selectivity and, consequently, should lead to the same execution plan. SQL> SELECT child_number, predicate, low, high FROM v$sql_cs_selectivity WHERE sql_id = '7h6n1xkn8trkd' ORDER BY child_number; CHILD_NUMBER PREDICATE LOW HIGH ------------ --------- ---------- -------------------------------------------------------------------------------------------------------------- 1 <ID 0.89099 1 1.088989 2 <ID 0.00810 8 0.009910 In summary, to increase the likelihood that the query optimizer will generate efficient execution plans, you should not use bind variables. Bind variable peeking might help. Unfortunately, it is sometimes a matter of luck whether an efficient execution plan is generated. The only exception is when the new extended cursor sharing of Oracle Database 11g automatically recognizes the problem.

  22. Best Practices 1) Any feature should be used only if the advantages related to its utilization outweigh the disadvantages. In some situations, it is easy to decide. For example, there is no reason for not using bind variables with SQL statements without a WHERE clause (for example, plain INSERT statements).On the other hand, bind variables should be avoided at all costs whenever histograms provide important information to the query optimizer. 2) SQL statements processing little data: Whenever little data is processed, the parsing time might be close to or even higher than the execution time. In that kind of situation, using bind variables is usually a good thing. This is especially true for SQL statements that are expected to be executed frequently. Typically, such SQL statements are used in data entry systems (commonly referred to as OLTP systems). 3) SQL statements processing a lot of data: Whenever a lot of data is processed, the parsing time is usually several orders of magnitude less than the execution time. In that kind of situation, using bind variables is not only irrelevant for the whole response time, but it also increases the risk that the query optimizer will generate very inefficient execution plans.

  23. How to eliminate Bind Variable Peeking -I 1)Only create histograms on skewed columns. 2)Use literals in where clauses on columns where you have histograms and want to use them. Note that it’s not necessary to use literals for every possible value of a skewed column. There may be only a few outlier values that result in significantly different plans. With a little extra code you can use literals for those values and bind variables for the rest of the values that don’t matter. 3) If you can’t modify the code, consider turning off Bind Variable Peeking by setting the _OPTIM_PEEK_USER_BINDS parameter to false. You won’t get the absolute best performance for every possible statement, but you will get much more consistent performance, Keep in mind that this is a hidden parameter and so should be carefully tested and probably discussed with Oracle support prior to implementing it in any production system.

  24. How to eliminate Bind Variable Peeking -II 4) You can also consider stronger methods of forcing the optimizer’s hand such as Outlines. This option provides a quick method of locking in a single plan, but it’s not fool proof. Even with outlines, there is some possibility that the plan can change. Also note that this option is only palatable in situations where you have a relatively small number of problem SQL statements. 5) Upgrade to 11g and let Adaptive Cursor Sharing takes care of all your problems.

  25. References & Readings Antognini, Christian. 2008. Troubleshooting Oracle Performance. Apress: Berkeley, CA. Lewis, Jonathan. 2006. Cost Based Oracle Fundamentals. Apress: Berkeley, CA. Robert G Freeman.2009.Oracle Database 11g New Features.TataMcgraw- Hill. Sam Alapati. 2009.OCP Oracle Database 11g New features for Administrators. Tata Mcgraw- Hill. Ask Tom (Kyte) http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html Kerry Osborne’s Oracle Blog: http://kerryosborne.oracle-guy.com/

More Related