Interview Questions

Performance Management

oracle Database 11g adds several weapons to the arsenal of the DBA looking to improve performance and scalability. Automatic native compil ation of PL/SQL code means you’ll now get much faster PL/SQL performance, automatically when you upgrade applications to the new release. The new server cache feature dramatically increases performance for SQL queries as well as for PL/SQL functions by retrieving results straight from the cache instead of reexecuting code.There is also a new client-side result caching feature for OCI applications.

The new SQL Plan Management (SPM) feature helps you stabilize SQL execution plans to prevent a performance regression due to un expected changes in the execution plans of SQL statements. By default, in Oracle Database 11g, the database automatically tunes SQL statements by running a new daily task called the Automatic SQL Tuning Advisor task as part of the automated task management feature.

There are major innovations in the use of the Automatic Database Diagnostic Monitor (ADDM), including the ability to run the ADDM in dual modes at the database level and at the cluster level to help diagnose performance problems in an Oracle Real Application Cluster (RAC) environment. There are significant improvements in the automatic workload repository (AWR) baselines, including the new concept of moving window baselines and baseline templates.You can now configure adaptive thresholds for various database alerts based on AWR’s new system_moving_window baseline. The database advisor framework, introduced in Oracle Data base 10g, is extended in this release.

Here are the main topics we discuss in this chapter:

  • SQL and PL/SQL performance improvements
  • New result caching functionality
  • Automatic SQL Tuning Advisor task
  • SQL Access Advisor enhancements
  • ADDM enhancements
  • Adaptive cursor sharing
  • Performance-related changes in Database Control
  • Optimizer and statistics collection enhancements
  • SQL Plan Management

We’ll now review the major new performance-related features in the Oracle Database 11g release, starting with SQL and PL/SQL performance improvements.

SQL and PL/SQL Performance Enhancements

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 language features 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 to interpreted.To turn on native PL/SQL compilation, set the plsql_code_type initialization parameter as follows:

plsql_code_type=native

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 have already 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 first Oracle 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.

SQL> select name, value from v$parameter where
 name like '%plsql%;
     NAME                      VALUE
 ---------------------    ----------------------
 plsql_code_type            INTERPRETED
 plsql_optimize_level          2
 ...
 9 rows selected.
SQL>

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.

SQL> create or replace procedure test_native as
2 begin
3 dbms_output.put_line('Test Procedure.');
4* end test_native;
SQL> /
Procedure created.
SQL>
SQL> select plsql_code_type
2 from all_plsql_object_settings
3 where name='TEST_NATIVE';
PLSQL_CODE_TYPE
-----------------
INTERPRETED
SQL>

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:

SQL> alter procedure test_native compile plsql_code_type=native;
 Procedure altered.
SQL>

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:

SQL> select plsql_code_type
 2 from all_plsql_object_settings
 3* where name='TEST_NATIVE';
 PLSQL_CODE_TYPE
 ---------------------------
 NATIVE
SQL>

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.  SQL> connect sys/sammyy1 as sysdba
      Connected to an idle instance.
      SQL> startup upgrade
      ORACLE instance started.
      ...
      Database opened.
  9. Once the instance comes up, execute the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as the user sys:
  10.  SQL> @$ORACLE_HOP ME/rdbms/admin/dbsupgnv.sql
     OC>################################################################
     DOC>###############################################################
     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>
     DOC> Shut down and restart the database in normal mode and
     DOC> run utlrp.sql to recompile invalid objects.
     SQL>
    The dbmsupgnv.sql script updates the execution mode of all PL/SQL modules to native.
  11. 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 in the $ORACLE_HOME/rdbms/admin directory to recompile all the invalidated PL/SQL units:
  12.  SQL> shutdown immediate;
     SQL> startup
     ORACLE instance started.
     ...
     Database opened.
     SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
     ...
     SQL> Rem END utlrp.sql
     SQL> exit

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 SQL statement.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.

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.

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 shows whether 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 cursor sharing. Here’s a simple query showing the two new columns:

SQL> select sql_id, executions, is_bind_sensitive, is_bind_aware
 from v$sql;
 SQL_ID                        EXECUTIONS          I    I
 ------------------          -------------------  ---  ---
 57pfs5p8xc07w                  21                 Y    N
 1gfaj4z5hn1kf                  4                  Y    N
 1gfaj4z5hn1kf                  4                  N    N
 ...
 294 rows selected.
SQL>

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.


Pragna Meter
Next Chapter  
e-University Search
Related Jobs