SQL and PL/SQL Performance Enhancements - Oracle 11g

Oracle Database 11g introduces several enhancements in PL/SQL programming and execution.Some of these improvements are transparent, such as the PL/SQL result cache, which caches execution results so that frequently executed identical PL/SQL statements can use those results instead of executing the same statement repeatedly.

In addition to performance-improving enhancements, several languagefeatures improve the functionality and usability of PL/SQL programming.You’ll learn about these programming enhancements in Chapter. Let’s turn to a review of the important PL/SQL performance enhancing new features.

Automatic “Native” PL/SQL Compilation

In previous releases of the database,Oracle transformed all PL/SQL code to C code,which in turn was compiled by a third-party C compiler. This posed a problem in organizations that didn’t want to or couldn’t install a C compiler on their servers. In Oracle Database 11g, Oracle directly translates the PL/SQL source code to the DLL for the server.In addition, Oracle bypasses the file system directories by doing the linking and loading itself.

Except setting one new initialization parameter,you don’t need to configure anything to take advantage of the PL/SQL native compilation.This parameter is plsql_code type, which you can use to turn automatic native PL/SQL compilation on and off.Tests per formed by Oracle showed a performance improvement of up to 20 times with native PL/SQL compilation.

Using Real Native Compilation

By default, the database interprets PL/SQL code instead of directly compiling it.You can use PL/SQL native compilation now without any third-party software such as a C compiler or a dynamic link library (DLL) loader.The new initialization parameter plsql_code_type specifies the compilation mode for PL/SQL library units and can take one of two values: interpreted or compiled.

  • If you set the parameter to interpreted, the database will compile all PL/SQL code to PL/SQL bytecode format, and the PL/SQL interpreter engine will execute them.
  • If you set the parameter to native, most PL/SQL code will be compiled to machine code and executed natively without the involvement of an interpreter, thus making execution faster.

The default value of the plsql_code_type parameter is set tointerpreted.To turn on native PL/SQL compilation, set the plsql_code_type initialization parameter as follows:


You can change the value of the plsql_code_type para meter dynamically,with either an alter system or alter session state ment.However, the switch in compilation mode won’t affect the PL/SQL units that havealready been compiled.Once the database compiles a PL/SQL unit all recompilations will continue to use the original mode in which that unit was compiled, be it native or interpreted.

Oracle Data base 11g generates DLLs from the PL/SQL source code and stores the code in the database catalog from where it loads the code directly without having to stage it on a file system firstOracle claims the following regarding real native PL/SQL compilation:

  • Compilation is twice as fast as C native compilation.
  • The Whetstone Benchmark shows that real native compilation is two-and-a-half timesfaster than C native compilation.

Setting Up a PL/SQL Program Unit for Native Compilation

In this section, we’ll show how to set up the PL/SQL native compilation for a single procedure.To enable native compilation of our test procedure, first change the value of the plsql_code_type parameter from its default value of interpreted to native, either by placing this in the parameter file or by using the alter system or alter session command. Once you do this, the database will natively compile any PL/SQL program unit you create.

The other way is to take an already created PL/SQL program unit, which is going to be interpreted by default, and use the alter <PLSQL unit type> statement to enable PL/SQL native compilation. In the example here, you first create a test procedure with the plsql_code_ type parameter still set to the default value of interpreted.

Once you create the test procedure (test_native), you issue the alter procedure statement in the following way to change the code type to native:

You can confirm that the test procedure is now compiled for native execution by executing the following query on the DBA_PLSQL_OBJECT_SETTINGS view:

The plsql_code_type column has the value native for the test procedure named test_native,indicating that the alter procedure statement has succeeded.

Recompiling a Database for PL/SQL Native Compilation

If you want to recompile all existing PL/SQL modules in your database to native, you must start your database in upgrade mode and execute the dbmsupgnv.sql script provided by Oracle.Here are the steps:

  1. Shut down the database in normal or immediate mode:
  2. SQL> shutdown immediate;
  3. Edit the parameter file for the instance, and set the plsql_code_type parameter to native, as shown here:
  4. plsql_code_type=native
  5. Also make sure the value of the plsql_optimize_level parameter is at least 2 (which is the default):
  6. plsql_optimize_level=3
  7. Start the database in upgrade mode:
  8. Once the instance comes up, execute the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as the user sys:
  9.  SQL> @$ORACLE_HOP ME/rdbms/admin/dbsupgnv.sql
     DOC> dbmsupgnv.sql completed successfully. All PL/SQL procedures,
     DOC> functions, type bodies, triggers, and type bodies objects in
     DOC> the database have been invalidated and their settings set to
     DOC> native.
     DOC> Shut down and restart the database in normal mode and
     DOC> run utlrp.sql to recompile invalid objects.
    The dbmsupgnv.sql script updates the execution mode of all PL/SQL modules to native.
  10. Shut down and restart the database after the updating of the PL/SQL units is complete.Since the update invalidates all the PL/SQL units, run the utlrp.sql script located inthe $ORACLE_HOME/rdbms/admin directory to recompile all the invalidated PL/SQL units:

Once you update your database to compile PL/SQL in native mode, you don’t have to use any alter <PL/SQL unit> commands any longer. By default, the database will now execute all PL/SQL units in native mode.

The effect of the recompilation process isn’t permanent. If you decide to take the database back to the default mode of interpreted execution, follow the same process as shown here, with one exception—just replace the dbmsupgnv.sql script with the dbmsupgin.sql script.

Faster DML Triggers

This is one of the new features of Oracle Database 11g that doesn’t require you to do anything to reap the benefits! All DML triggers run much faster in Oracle Database 11g.According to one of Oracle’s internal tests,there was a 25 percent improvement in the performance speed of a row-level update trigger fired during DML.Please see Chapter 11 for several new triggerrelated features

Adaptive Cursor Sharing

Using bind variables reduces the amount of shared memory the database needs to allocate to parse SQL statements, because bind variables reuse a single cursor for multiple executions of the same (or similar) SQL statement.Bind variables, because they reduce parse time and memory usage, enhance the per formance and scalability of the database, especially when you have a large number of concurrent users.

The initialization parameter cursor_sharing determines which SQL statements can share the same cursor.Setting the cursor_sharing parameter to exact stipulates that only identical statements can share a cursor. Setting the para meter to force means statements that differ in some literals can share a cursor. Setting the parameter to the value similar leads to the same behavior as setting it to force unless the different values of the literals affect the degree of optimization of the execution plan.

However,cursor sharing inherently conflicts with SQL optimization, because specifying literal values instead of bind values provides richer information to the optimizer, leading to the evolution of better plans.For this reason, forced cursor sharing especially could lead to suboptimal execution plans. In some cases, some users of a SQL statement may get highly optimal executions, while others may get quite inferior executions because of the specific values of the actual bindings.

Columns with heavily skewed data distribution need different execution plans based on the actual values of the bind variables in a SQL query, and when you bind variables,you may end up with suboptimal execution plans as a result. Oracle uses the concept of bind peeking, under which the optimizer examines the bind values the first time you execute a statement in order to evolve an optimal execution plan for subsequent executions of that statement.

The optimizer will look at the bind variable values during the first hard parse and base its plan strategy on those values. Bind variable peeking helps you only when the optimizer first generates the execution plan for a new SQLstatement.However,if the data is heavily skewed, bind peeking has little value because different data for the bind variables requires different execution plans,making the use of literals a better strategy.For example,if the values the optimizer sees during its “peeking” warrant using an index, it’ll continue to use the index even for other values of the bind variable when a full scan may be a better strategy.

Oracle Database 11g takes a major step to resolve the inherent conflict between cursor sharing and query optimization by introducing the concept of adaptive cursor sharing.Under adaptive cursor sharing, the SQL statements automatically share a cursor.Oracle generates multiple execution plans for a statement that uses bind variables,when it detects that the cost of doing so for a particular SQL statement outweighs the benefit of a lower parse time and memory usage flowing from using the same cursor.Oracle still attempts to keep the number of generated child cursors to a minimum to take advantage of cursor sharing.In a nutshell, what Oracle is attempting to do is avoid the “blind sharing” of cursors while minimizing the number of child cursors.

Adaptive Cursor Sharing

How Adaptive Cursor Sharing Works

The key to adaptive cursor sharing is the bind sensitivity of a cursor and the concept of a bindaware cursor.A query is considered bind-sensitive if the optimizer performs bind peeking when figuring out the selectivity of the predicates and a change in bind variable values potentially leads to different execution plans. If a cursor in the cursor cache has been marked for bindaware cursor sharing, the cursor is called bind-aware.

How Adaptive Cursor Sharing Works

Adaptive cursor sharing uses bind-aware cursor matching. Unlike in previous releases, the cursors can be bind-sensitive now. We’ll now show a simple example that illustrates how adaptive cursor sharing works in practice.

Let’s say you have the following SQL query that the database executes multiple times:

SQL> select * from hr.employees where salary = :1 and department_id = :2;

The previous SQL statement uses two bind variables, one for the salary column and the other for the department_id column.

The first time the database executes the SQL statement,there is a hard parse.If the optimizer peeks at the bind values and uses histograms to compute the selectivity of the predicate with the two bind variables,the cursor is marked as a bind-sensitive cursor.The predicate selectivity information is stored in a cube, let’s say (0.15, 0.0025).Once the database executes the query, it stores the execution statistics of the cursor in the cursorUnder adaptive cursor sharing, the database monitors the query execution of a new SQL statement for a while and gathers information to help decide whether it should switch to bind-aware cursor sharing forthe query.

When the database executes a SQL statement the next time with a different pair of bind values,the database performs a soft parse as usual and compares the execution statistics to those stored in the cursor. Based on all the previous execution statistics for this cursor, the database makes a decision whether to mark this cursor as bind-aware.

If the cursor is marked bind-aware, the database uses bind-aware cursor matching during the next soft parse of the query.Each time the database executes the query, it performs a cursor sharing check using the predicat ’s bind variable selectivity estimates, which are stored in the select ivity cubes.Each plan has a selectivity range or cube associated with it.If the new bind values fall within this range, they will use the same plan.That is, if the selectivity of the predicate with the new pair of bind values is within the existing cube or range of values,the data base uses the same execution plan as that of the existing child cursor.

If the selectivity of the predicate doesn’t fall inside the existing cube, then the database can’t find a matching child cursor. As a result, the database performs a hard parse, generating a new child cursor with a different execution plan. If two hard parses generate an identical execution plan, the child cursors are merged.

Briefly,bind-aware cursor sharing means that the database will share an execution plan when the bind values are roughly equivalent.The optimizer will delineate a selectivity range for an execution plan,and it’ll use the same plan if the new bind values fall within this selectivity range.If the binds are not equivalent according to the selectivity range, the optimizer will generate a new execution plan with a different selectivity range.

Monitoring Adaptive Cursor Sharing

The V$SQL view has several new database views as well as new columns that help you monitor adaptive cursor sharing in the database.Since adaptive cursor sharing is automatic, you don’t have to actually do anything. Here’s a brief description of the new views:

  • V$SQL_CS_HISTOGRAM: Shows the distribution of the execution count across the execution history histogram.
  • V$SQL_CS_SELECTIVITY: Shows the selectivity cubes or ranges stored in cursors for predicates with bind variables.
  • V$SQL_CS_STATISTICS: Contains the execution information gathered by the database to decide on whether it should use bind-aware cursor sharing and includes buffer gets and CPU time, among the statistics.

In addition to these new views, the V$SQL view has two important new columns to support adaptive cursor sharing.The IS_BIND_SENSITIVE column showswhether a cursor is bind-sensitive.The IS_BIND_AWARE column shows whether a cursor in the cursor cache has been marked to use bind-aware cursorsharing. Here’s a simple query showing the two new columns:

If the IS_BIND_SENSITIVE column shows Y, it means the optimizer is planning to use multiple execution plans, depending on the value of the bind variable. If the IS_BIND_AWARE column shows Y, it means the optimizer knows that the bind variable values result in different data patterns.In this case, the optimizer may hard-parse the statement.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics