9 Optimizing Data Warehouse Operations

Monitoring System Performance

This section provides information on how to avoid system overload by regularly monitoring important metrics. You can monitor these metrics through the use of the Database Performance page in Oracle Enterprise Manager. This section includes the following topics:

• Monitoring Parallel Execution Performance

• Monitoring I/O

Monitoring Parallel Execution Performance

This section describes how to monitor parallel execution performance. Suppose that you see many parallel statements are being downgraded. This may indicate a performance problem. Statements that run with a degree of parallelism lower than expected can take much longer, and users may experience different execution times depending on whether statements got downgraded. Possible causes for downgraded parallel statements include the following:

• The initial degree of parallelism is higher than it should be and should be lowered.

• There are not enough parallel servers available, which may indicate the system is overloaded.

To monitor parallel execution performance:

1. On the Database Home page, click Performance.

   The Performance page is displayed.

2. Scroll down the page. Under the list of links, click PQ.

   The PQ page is displayed. Parallel query performance characteristics are shown for:

   • Parallel sessions

   • Parallel slaves

   • DML and DDL parallelization

   • Serialization and statement downgrades

Figure 9-1 Monitoring Parallel Execution

Description of "Figure 9-1 Monitoring Parallel Execution"

Monitoring I/O

This section describes how to monitor I/O performance. If the throughput on your system is significantly lower than what you expect based on the system configuration (see Chapter 2, "Setting Up Your Data Warehouse System"), and your users complain about performance issues, then there could be a performance problem. In a well-configured system that runs a typical data warehouse workload, you would expect a large portion of large I/Os and a relatively low latency (lower than 30ms) for a single block I/O.

To monitor I/O performance:

1. On the Database Home page, click Performance.

   The Performance page is displayed.

2. Scroll down the page. Under the list of links, click I/O.

   The I/O page is displayed, displaying I/O Megabytes per Second by Function and I/O Requests per Second by Function.

3. For details regarding read and write operations, select IO Type.

   I/O details are shown for the following:

   • Large Writes

   • Large Reads

   • Small Writes

   • Small Reads

Figure 9-2 Monitoring I/O

Description of "Figure 9-2 Monitoring I/O"

Using Database Resource Manager

The Database Resource Manager provides the ability to prioritize work within the Oracle system. Users with higher priority jobs get resources in order to minimize response time for online work, for example, while users with lower priority jobs, such as batch jobs or reports, might encounter slower response times. This priority assignment allows for more granular control over resources and provides features such as automatic consumer group switching, maximum active sessions control, query execution time estimation and undo pool quotas for consumer groups.

You can specify the maximum number of concurrently active sessions for each consumer group. When this limit is reached, the Database Resource Manager queues all subsequent requests and runs them only after existing active sessions complete.

The Database Resource Manager is part of the Oracle Database and can distinguish different processes inside the database. As a result, the Database Resource Manager can assign priorities to individual operations running inside the database.

With the Database Resource Manager, you can do the following:

• Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users.

• Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational online analytical processing) applications than to batch jobs.

• Allow automatic switching of users from one group to another based on administrator-defined criteria. If a member of a particular group of users creates a session that runs for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.

• Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

Example: Optimizing Indexes and Materialized Views Using the SQL Access Advisor

For this example, assume you have a workload running on the system that may benefit from certain indexes or materialized views.

To optimize an index and materialized view:

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

   The Advisors page is displayed.

2. From the Advisors page, click SQL Access Advisor.

   The SQL Access Advisor page is displayed.

3. Select Use Default Options and click Continue.

   The Workload Source page is displayed.

4. Select your workload source as Use an Existing SQL Tuning Set. Go to a SQL Tuning Set and click Select. Then click Next.

   The Recommendation Options page is displayed.

5. Select Indexes, Materialized Views, and Comprehensive Mode. Click Next.

   The Schedule page is displayed.

6. Click Submit.

   The Recommendations page is displayed.

7. Enter a name in the Name field and select Immediately for when it should start. Then click Next.

   The Review page is displayed.

8. Click Submit.

   The Confirmation page is displayed.

9. Select your task name and click View Result.

   You can view additional information under Recommendations, SQL Statements, or Details.

Using Data Compression to Improve Storage

You can compress several partitions or a complete partitioned heap-organized table. You do this either by defining a complete partitioned table as being compressed, or by defining it on a per-partition level. Partitions without a specific declaration inherit the attribute from the table definition or, if nothing is specified on table level, from the tablespace definition.

The decision about whether or not a partition should be compressed or stay uncompressed adheres to the same rules as a nonpartitioned table. However, due to the ability of range and composite partitioning to separate data logically into distinct partitions, such a partitioned table is an ideal candidate for compressing parts of the data (partitions) that are mainly read-only. It is, for example, beneficial in all rolling window operations as a kind of intermediate stage before aging out old data. With data compression, you can keep more old data online, minimizing the burden of additional storage consumption.

You can also change any existing uncompressed table partition later, add new compressed and uncompressed partitions, or change the compression attribute as part of any partition maintenance operation that requires data movement, such as MERGE PARTITION, SPLIT PARTITION, or MOVE PARTITION. The partitions can contain data or they can be empty.

The access and maintenance of a partially or fully compressed partitioned table are the same as for a fully uncompressed partitioned table. All rules that apply to fully uncompressed partitioned tables are also valid for partially or fully compressed partitioned tables.

To use data compression:

The following example creates a range-partitioned table with one compressed partition costs_old. The compression attribute for the table and all other partitions is inherited from the tablespace level.

CREATE TABLE costs_demo (
   prod_id     NUMBER(6),    time_id     DATE, 
   unit_cost   NUMBER(10,2), unit_price  NUMBER(10,2))
PARTITION BY RANGE (time_id)
   (PARTITION costs_old 
       VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS,
    PARTITION costs_q1_2003 
       VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
    PARTITION costs_q2_2003
       VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')),
    PARTITION costs_recent VALUES LESS THAN (MAXVALUE));