Tuning the warehouse

7.9 Tuning the warehouse

One should not forget that a data warehouse needs to be tuned just like any other database, although you may be tuning for reasons such as:

• Improving data load time

• Improving query response

• Refreshing data performance

• Improving time to perform management tasks

When people think of tuning, their first thoughts are likely to be improving query performance. Traditional techniques used to resolve this problem include creating indexes and placing data in the buffer cache so that the data is faster to read. In a data warehouse, the index is still extremely important, but placing the data in the buffer cache is unlikely to help, due to the huge volumes of data typically being searched in a data warehouse.

Therefore, one of the best techniques to use to improve query performance is to create materialized views. Provided the query is used several times, if a materialized view is created that precalculates the query, the optimizer will execute the query by retrieving the results from the materialized view rather than the detail table. If your detail table is large, this can represent a very significant improvement in query response time.

7.9.1 Tuning tools

There are many tuning tools available to tune an Oracle database, from both Oracle and third-party software suppliers. If you purchase the optional tuning packs for Oracle Enterprise Manager, the following tools will be available to you:

• Oracle Expert-creates a database design (only available on NT)

• SQL Analyze-identifies and tunes resource-intensive SQL statements, provides graphical EXPLAIN PLAN (only available on NT)

• Tablespace Map-identifies space management problems

• Reorg Wizard-repairs space problems, resizes, and relocates objects

• Outline Management-manages stored outlines

Some other useful tuning tools are in the Diagnostics Pack, including:

• Performance Manager-shows how the database is being used, including CPU, memory, and I/O resources

• TopSessions-displays user's database use

• Capacity Planner-collects performance baseline statistics

Several advisors are available to give you recommendations to improve performance:

• Summary Advisor-recommends materialized views (available with Enterprise edition)

• Index Tuning Wizard-recommends indexes (only available on NT)

• Buffer Cache Advisor-determines the optimal size of the buffer cache and shared pool

Just because the database is being used as a data warehouse should be no excuse to forget to tune it. However, if you are familiar with tuning OLTP systems, the techniques used will be slightly different, although the end result is the same: faster query response.

Tuning is a bit of a black art, and it is impossible in just a few pages to tell you how to tune a data warehouse. Instead, we suggest that you become familiar with the tuning tools available to you and determine what information you need to know. For instance, how much I/O was used to perform this query? Then determine how, if at all possible, you can obtain this data from your tuning tool.

Practice this technique in a controlled environment on a small system before you attempt to analyze a production system. Keep practicing to improve your confidence, and then, when you really do have to tune, it shouldn't be such an ordeal. There are many books available on Oracle tuning, and all should provide useful advice on tuning your data warehouse.

7.9.2 Plan stability

One of the significant advantages of using a relational database is that you don't have to tell the database how to access the data when a query is defined. Instead, the query optimizer determines the most efficient query execution path. Usually, the optimizer selects the best strategy, but problems can occur when new releases of the software are installed or when the database cardinality statistics change, because the optimizer may choose a different strategy. Therefore, there is a risk that your query could suddenly take much longer to complete. One solution to this problem is to use a hint, but this means amending the code, which may not be possible.

To overcome this problem, Oracle 8i introduced a new feature called plan stability. This allows you to create a stored outline for a specific SQL statement or all SQL statements. When plan stability is enabled, each query is compared with each query that has a stored outline. If the query matches a query with an outline exactly, then the strategy for the outline is used.

Creating an outline

An outline is made up of a set of hints that represents the optimizer's execution plan for a SQL statement. The hints are similar to the hints you might add to a query to influence the optimizer strategy. Outlines can either be created for all SQL statements or for specific SQL statements.

Outlines can be grouped together into categories so they can be better organized and managed. To create stored outlines for all SQL statements set the parameter CREATE_STORED_OUTLINES to true or to a category name, as shown in the following example, where the category is EASYDW_CAT.

 SQL>ALTER SESSION SET CREATE_STORED_OUTLINES = EASYDW_CAT 

Once you set this, outlines will automatically be created for every SQL statement. This is an easy way to create the outlines but may result in a large number of outlines. This parameter can be turned on when an application starts running and turned off by setting CREATE_STORED_OUTLINES to false when the application has finished.

Alternatively, you can create stored outlines for specific statements using the CREATE OUTLINE statement or the Outline Management Tool in Oracle Enterprise Manager, as shown in Figure 7.45.

Figure 7.45: Outline management.

The three outlines with names starting with SYS_OUTLINE were created automatically by enabling create_stored_outlines shown previously. Notice how outlines were created for every SQL statement-including some that are probably not needed, such as SELECT * FROM USER_OUTLINES.

The outline named SUM_OF_SALES was created for an individual SQL statement by selecting Create from the Outlines menu, as shown in Figure 7.45. The SQL for the outline is listed in the text box.

Using an outline

To use stored outlines, set the parameter USE_STORED_OUTLINES to true or to a category name. When Oracle compiles a SQL statement, it looks to see if there is an outline with exactly the same text as the query. If it finds one, then the strategy for the outline is used when executing the query.

 SQL> ALTER SESSION SET USE_STORED_OUTLINES = easydw_cat 

In Figure 7.45, two outlines had been used at least once, and two have not been used yet. To disable the use of outlines, set USE_STORED_OUTLINES to false.

Editing an outline

While the cost-based optimizer generates the optimal execution plan most of the time, manual query tuning can lead to more efficient plans in some cases. As mentioned previously, you could use hints, or, if you want to modify the execution strategy, Oracle 9i gives you the ability to edit an outline. To invoke the outline editor double-click on the selected outline.

Figure 7.46 shows a SQL query that computes the sum of sales and total sales by product, month, and year. To get the result, the TIME, PRODUCT, and PURCHASES tables must be joined. The strategy chosen by the optimizer is shown in graphical format. The PURCHASES table is joined to the TIME table using a hash join. The result is then joined to the PRODUCT table using a nested loop join via the primary key index, PRODUCT_PK_INDEX.

Figure 7.46: The original outline.

This query could also be executed by changing the join order-first joining the TIME table with the PRODUCT table and then joining the result with the PURCHASES table.

The join order can be changed from the Outline menu. Figure 7.47 shows how you can select a node in the tree and move it up or down. For the next example, the PURCHASES node was moved down.

Figure 7.47: Changing the join order.

We now have a new strategy, as shown in Figure 7.48. The TIME table will be joined to the PRODUCT table using a nested loop join and the primary key for the PRODUCT table. The result will be joined to the purchases table using a hash join.

Figure 7.48: Executing the query with a revised outline.

You can validate the changes and execute the query to see if it will run faster, as shown in Figure 7.48. Compare the elapsed time, CPU time, and other statistics with the execution statistics from the original strategy to see if your edits made the plan better (or worse).

If you do not use Oracle Enterprise Manager, you can edit the outlines using the DBMS_OUTLN_EDIT package. To see what outlines have been created look at the USER_OUTLINES view, shown in the following code statement.

 SQL> SELECT NAME, USED, SQL_TEXT FROM USER_OUTLINES; NAME                  USED   SQL_TEXT ----------------------------------------------------------------- SYS_OUTLINE_020817142 USED   SELECT t.month, t.year, p.product_id SYS_OUTLINE_020817142 USED   select * from user_outlines SYS_OUTLINE_020817142 UNUSED select * from user_outline_hints SUM_OF_SALES          UNUSED SELECT t.month, t.year, p.product_id 

By default outlines are stored in the OL$, OL$HINTS, and OL$NODES tables in the SYSTEM tablespace in the OUTLN schema. You probably noticed this schema in your database starting with Oracle 8i. If there are a large number of outlines, they may use too much space in the SYSTEM tablespace and can be moved. Refer to the Oracle documentation for the steps to do this.

In summary, if you are concerned about strategies changing, especially as a result of a software upgrade, consider creating some outlines for your important queries. Once they have been created and stored in the database, they do not have to be used. Instead, they can be kept in case of emergency and turned on only when required. Outlines are also useful when building applications that are deployed at a number of sites to ensure that the same optimizer strategy is chosen even when there are different data cardinalities.