SQL Performance Explained by Markus Winand

Author:Markus Winand [Winand, Markus]
Language: eng
Format: epub, pdf
ISBN: 9783950307801
Publisher: Markus Winand
Published: 2011-01-28T17:45:17.375851+00:00

Note

Bind parameters cannot change the structure of an SQL statement.

That means, you cannot use bind parameters in the from clause or change the where clause dynamically. Non of the following two bind parameters works:

String sql = prepare("SELECT * FROM ? WHERE ?"); sql.execute('employees', 'employee_id = 1');

If you need to change the structure of an SQL statement, use dynamic SQL.

Oracle Cursor Sharing, Bind Peeking and Adaptive Cursor Sharing

Because bind parameters and histograms are very important, the Oracle database has undergone various attempts to make them work together.

The by far most common problem are applications that do not use bind parameters at all. For that reason, Oracle has introduced the setting CURSOR_SHARING that allows the database to re-write the SQL to use bind parameters (typically named :SYS_Bx). However, that feature is a workaround for applications that do not use bind parameters and should never be used for new applications.

With release 9i, Oracle introduced the so-called bind peeking. Bind peeking enables the optimizer to use the actual bind values of the first execution during parsing. The problem with that approach is its nondeterministic behavior: whatever value was used in the first execution (e.g., since database startup) affects all executions. The execution plan can change every time the database is restarted or, more problematic, the cached plan expires. In other words; the execution plan can change at any time.

Release 11g introduced adaptive cursor sharing to cope with the problem. This feature enables the database to have multiple execution plans for the same SQL statement. The “adaptive” approach is to run everything as usual, but to take note of the time each execution takes. In case one execution runs much slower than the others, the optimizer will create a tailor-made plan for the specific bind values. However, that tailor-made plan is created the next time the statement executes with the problematic bind values. That means that the first execution must run slow before the second execution can benefit.

All those features attempt to cope with a problem that can be handled by the application. If there is a heavy imbalance upon the distribution of search keys, using literal variables should be considered.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.