Skip Headers
Oracle® Database 2 Day + Data Warehousing Guide
11g Release 2 (11.2)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

10 Eliminating Performance Bottlenecks

This chapter describes how to identify and reduce performance issues and contains the following topics:

Verifying That SQL Runs Efficiently


Analyzing Optimizer Statistics

Optimizer statistics are a collection of data that describes more details about the database and the objects in the database. These statistics are stored in the data dictionary and are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:







Because the objects in a database can constantly change, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle Database, or you can maintain the optimizer statistics manually using the DBMS_STATS package.

Analyzing an Execution Plan



General guidelines for using the EXPLAIN PLAN statement are:


  • To include the EXPLAIN PLAN FOR clause before the SQL statement.



Example: Analyzing Explain Plan Output

The following statement shows the output of two EXPLAIN PLAN statements, one with dynamic pruning and one with static pruning.

To analyze EXPLAIN PLAN output:

SELECT p.prod_name
, c.channel_desc
, SUM(s.amount_sold) revenue
FROM products p
, channels c
, sales s
WHERE s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
GROUP BY p.prod_name
, c.channel_desc;
Predicate Information (identified by operation id):
Note the values of KEY KEY for Pstart and Pstop.
Predicate Information (identified by operation id):
Note the values of 20 20 for Pstart and Pstop.

The first execution plan shows dynamic pruning using the KEY values for Pstart and Pstop respectively. Dynamic pruning means that the database will have to determine at execution time which partition or partitions to access. With static pruning, the database knows at parse time which partition or partitions to access, which leads to more efficient execution.


Using Hints to Improve Data Warehouse Performance

Hints enable you to make decisions usually made by the optimizer. As an application developer, you might have information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on specific criteria.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In this case, use hints to instruct the optimizer to use the optimal execution plan.


Example: Using Hints to Improve Data Warehouse Performance


To use a hint to improve data warehouse performance:

FROM sales s


INSERT /* +APPEND */ INTO my_materialized_view

Using Advisors to Verify SQL Performance



Example: Using the SQL Tuning Advisor to Verify SQL Performance

You can use the SQL Tuning Advisor to tune a single SQL statement or multiple SQL statements. When tuning multiple SQL statements, remember the SQL Tuning Advisor does not recognize interdependencies between the SQL statements. Instead, it is just meant to be a convenient way for you to run the SQL Tuning Advisor for a large number of SQL statements.

To run the SQL Tuning Advisor to verify SQL performance:


    The SQL Advisors page is displayed.

  2. Click Schedule SQL Tuning Advisor.

    The Schedule SQL Tuning Advisor page is displayed. A suggested name will be in the Name field, which you can modify. Then select Comprehensive to have a comprehensive analysis performed. Select Immediately for the Schedule. Select a appropriate SQL Tuning Set, and then click OK.


    The Recommendations page is displayed.

  4. The recommendation is to create an index, which you can implement by clicking Implement. You may also want to run the SQL Access Advisor.

Improving Performance by Minimizing Resource Use

You can minimize resource use, and improve your data warehouse's performance through the use of the following capabilities:

Improving Performance: Partitioning

Data warehouses often contain large tables and require techniques both for managing these large tables and for providing good query performance across these large tables. This section describes partitioning, a key method for addressing these requirements. Two capabilities relevant for query performance in a data warehouse are partition pruning and partitionwise joins.

Improving Performance: Partition Pruning


Partition pruning dramatically reduces the amount of data retrieved from disk and shortens the use of processing time, which improves query performance and resource use. If you partition the index and table on different columns (with a global partitioned index), partition pruning eliminates index partitions even when the partitions of the underlying table cannot be eliminated.

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile time; the information about the partitions is accessed beforehand, dynamic pruning occurs at run time; the partitions are accessed by a statement and are not known beforehand. A sample scenario for static pruning is a SQL statement that contains a WHERE clause with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the WHERE clause.

Partition pruning affects the statistics of the objects where pruning will occur and will affect the execution plan of a statement.

Improving Performance: Partitionwise Joins



Example: Evaluating Partitioning with the SQL Access Advisor

You should always consider partitioning in data warehousing environments.

To evaluate partitioning:

  1. In the Advisor Central page, click SQL Advisors.

    The SQL Advisors page is displayed.

  2. Click SQL Access Advisor.

    The SQL Access Advisor page is displayed.

  3. From the Initial Options menu, select Use Default Options and click Continue.


    The Recommendation Options page is displayed.


    The Schedule page is displayed.

  6. Enter SQLACCESStest1 into the Task Name field and click Next.

    The Review page is displayed. Click Submit.

  7. Click Submit.

    The Confirmation page is displayed.

  8. Select your task and click View Result. The Results for Task page is displayed, showing possible improvements as a result of partitioning.

    Figure 10-1 Partitioning Results

    Description of Figure 10-1 follows
    Description of "Figure 10-1 Partitioning Results"

Improving Performance: Query Rewrite and Materialized Views

In data warehouses, you can use materialized views to compute and store aggregated data such as the sum of sales. You can also use materialized views to compute joins with or without aggregations, and they are very useful for frequently executed expensive joins between large tables and expensive calculations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries because it computes and stores summarized data before processing large joins or queries. Materialized views in these environments are often referred to as summaries.


When underlying tables contain large amounts of data, it is a resource intensive and time-consuming process to compute the required aggregates or to compute joins between these tables. In these cases, queries can take minutes or even hours to return the answer. Because materialized views contain already computed aggregates and joins, Oracle Database uses the powerful query rewrite process to quickly answer the query using materialized views.

Improving Performance: Indexes


  • Reduced response time for large classes of ad hoc queries

  • Reduced storage requirements compared to other indexing techniques


  • Efficient maintenance during parallel DML and loads

Improving Performance: Compression

During bulk-load operations, Oracle Database can compress the data being loaded. Oracle Database handles data transformation and compression internally and requires no application changes to use compression. Compression can help improve performance for queries that scan large amounts of data, by reducing the amount of I/O required to scan that data.

No special installation is required to configure this feature. However, to use this feature, the database compatibility parameter must be set to 11.2.0 or higher.


Hybrid Columnar Compression is a feature of certain Oracle storage systems. See Oracle Database Concepts for more information.

Improving Performance: DBMS_COMPRESSION Package

The PL/SQL package DBMS_COMPRESSION provides a compression advisor interface to help choose the correct compression level for an application. The compression advisor analyzes the objects in the database and estimates the possible compression ratios that could be achieved.

See Also:

Improving Performance: table_compress clause of CREATE TABLE and ALTER TABLE

The table_compress clause of the CREATE TABLE and ALTER TABLE statements provides COMPRESS, which takes a parameter for compression level. Use COMPRESS to instruct the database whether to compress data segments to reduce disk use. All forms of table compression are generally useful in OLAP environments and data warehouses, where the number of insert and update operations is small; some forms are also useful in OLTP environments.


For compression to be enabled on a table, it must be turned on at table creation time, or the table must be changed to enable it.

Using Resources Optimally

You can maximize how resources are used in your system by ensuring that operations run in parallel whenever possible. Database operations run faster if they are not constrained by resources. The operation may be constrained by CPU resources, I/O capacity, memory, or interconnection traffic (in a cluster). To improve the performance of database operations, you focus on the performance problem and try to eliminate it (so that the problem might shift to another resource). Oracle Database provides functions to optimize the use of available resources and to avoid using unnecessary resources.

Optimizing Performance with Parallel Execution



  • Creation of large indexes

  • Creation of large tables (including materialized views)

  • Bulk insert, update, merge, and delete operations


Parallel execution benefits systems with all of the following characteristics:


  • Sufficient I/O bandwidth



If your system lacks any of these characteristics, then parallel execution might not significantly improve performance. In fact, parallel execution might reduce system performance on overutilized systems or systems with small I/O bandwidth.

How Parallel Execution Works

Parallel execution divides the task of running a SQL statement into multiple small units, each of which is executed by a separate process. Also, the incoming data (tables, indexes, partitions) can be divided into parts called granules. The user shadow process takes on the role as parallel execution coordinator or query coordinator. The query coordinator performs the following tasks:

  • Parses the query and determines the degree of parallelism


  • Controls the query and sends instructions to the parallel query slaves

  • Determines which tables or indexes must be scanned by the parallel query slaves

  • Produces the final output to the user

Setting the Degree of Parallelism


A single operation is a part of a SQL statement, such as an ORDER BY operation or a full table scan to perform a join on a non-indexed column table.

The degree of parallelism is specified in the following ways:

  • At the statement level with PARALLEL hints


  • At the table level in the table's definition

  • At the index level in the index's definition

Example: Setting the Degree of Parallelism


To set the degree of parallelism:

Issue the following statement:


About Wait Events

