Larry Miller is an Europe-based Oracle consultant who specializes in database administration and tuning. The current paper is based on works performed during an assignment with a major Telecoms operator.

This study was conducted in order to optimize the upload of a large data warehouse in an organization where volumes are evolving very fast (mobile phones - 2,000+ new customers per day at the time of the study!). Initially, and for historical reasons, a middleware product was used to carry out the extraction from the production database and the loading into the datawarehouse. As both the source and the target ended up being Oracle databases running on powerful Unix servers, the potential benefit of being able to process heterogeneous sources of data disappeared while the overhead induced by an additional, and not quite up-to-the task, software layer remained. It was decided to drop the middleware product altogether and replace it with custom PL/SQL procedures.

As a matter of fact, the first release of these procedures proved rather disappointing, which justified the commando operation to optimize them. Quite interestingly, many of the results fly in the face of conventional wisdom and allow us to define a set of rules which can be used in similar large volume contexts.

This case study more specifically references the optimization steps undertaken upon a series of PL/SQL procedures updating client information from a remote database onto the data warehouse.

The main elements that have a direct impact on the performance of SQL statements are:

• the system parameters and resources that hold the database
• the instance parameters
• the physical data model
• the indexes
• the wording of the statement
• the optimization mode
• the database organization and its condition - healthy or otherwise

The points that we will address are the following:

• the indexes

The indexes permit Oracle to ensure, for transactional statements, an equal or constant response time whatever the data volume. We will show that the use of the indexes is to be avoided in the customer subset of our system.

• the wording of the statement

This is the most crucial element. This is also the point where we obtain most performance gains. When the statement is written incorrectly Oracle will choose the incorrect path as shown by the explain plan. This point alone can show factor of performance that varies between one and twenty, if not more.

• the optimization mode

This is another major point confirmed in the current context. Oracle analyzes a statement and determines its execution plan based upon the optimization mode. The plan shows which algorithms and methods the kernel uses to access the data.

Oracle offers two modes of optimization: optimization based upon syntactic rules [RULE] and optimization based upon the costs [COST]. These modes have been explained in detail in numerous other documents. The aim here is to eliminate certain problems encountered in the customer application subset.

• Just a reminder, the RULE based mode uses format rules (syntax, operator precedence, sub-query,...) and the indexes available. The COST mode uses data stored with the tables (volume, number of rows, average length, block usage) and the indexes (volume, selectivity, depth, tree balance, eventual distribution of keys). The second mode is the "" more "intelligent" as it takes into account the data held in the tables referenced by the statement.

In this case study we limit ourselves to checking that the objects are not too fragmented or chained so that the performance is not adversely affected. We will not be dealing with the problem of separating the I/Os onto different disks, another performance criterion.

The other points have been dealt with previously in an audit that was performed on the database. The Data Warehouse then was tuned, as well as specific system parameters. The physical data model is also not brought into question at this time.

The Data Warehouse or Info Center database used for reference was running Oracle 732 on a DIGITAL UNIX 8400 server with DEC UNIX 3.2g.

The tools used to generate the execution plans or to trace during the execution were (Oradebug and TKPROF) (cf. Oracle 7 Server Tuning).

It is also possible to use the AUTOTRACE mode (SET AUTOTRACE ON) under SQL*Plus, this gives indications, as well as the plan, after an execution on the volumes of data treated (blocks, physical and logical I/O).

The aim is to regroup from the customer database (CUST) all information relating to one client, held in several tables, into a single client table in the data warehouse.

Number of rows in the customer database - CUST:

The client subset application which consists of 13 PL/SQL stored procedures executed in a sequential manner, essentially performs updates on the local CLIENT table.

The first procedure, pr_client_1, loads the tables if it is empty, or updates the relevant data. The other procedures complete the set of information with updates. We are therefore capable of distinguishing between the two types of transactions.

The updates in this case are always run and will be looked at in detail. The general transaction template below is applied to all the procedures where possible.

These views are joins between CUST tables that contain several hundred thousand rows. These views are not at all selective, as at each execution we are interested in all the customers and as a result, the joins deal with extremely large volumes of data. In these types of transactions we are interested in the overall transaction time (Batch) rather than first rows (On Line Transaction Processing - OLTP)

This will be shown in detail later in the explain plans.

Previous versions of the procedures performed systematic updates of all the clients.

Experience showed, that in a data warehouse model, the approach is not to use the same method practised by other tools, such as the middleware product used previously. With PL/SQL, transactions can be addressed in a more intelligent fashion given the power of SQL combined with a procedural language.

In only updating the data modified since the last transaction the following benefits are in evidence:

• few updates of the data and their indexes (7 on the CLIENT table)
• few locks placed on the rows
• considerable gain in elapsed time.

Note:

Numerous hints were present in the procedures as well as in the views. The hint was declaring the use of RULE (RULE based optimizer, therefore no knowledge of data volumes). The effect of these hints was that Oracle was choosing the indexes in the table joins as preferred with OLTP. The result being 2, 3or 4 times slower than the current optimized times.

Without the hints the database was in the CHOOSE mode (using statistics if present). The joins were performed without the indexes, using NESTED LOOPS and in most cases the HASH JOIN, which is well adapted to joins between large tables where full table scans are the norm.

View v_client_10 :

Execution Plan of the view v_client_10

In the first plan Oracle performs a loop (NESTED LOOP) to retrieve each line of CREDITTYPE_ALL using the index I_P_CREDITTYPE_ALL. This loop is executed 579471 times. It is much cheaper for Oracle to use the HASH JOIN to execute the transaction. Then the execution time is halved.

A word of caution in the use of the hints. A previous analysis of the procedures had shown that a missing space ensured that the hint was not used. Thus syntax is very important.

The CUST portion of the SELECT (columns have been omitted for clarity) :

A key typing error in the hint after the SELECT as follows:

A space before the plus sign made Oracle consider the hint a comment

A version of the transaction was tested with the error and then without:

Error - the explain plan testified to the COST mode

Correction - the explain plan testified to the RULE mode

This then, was the panic in production, when a new delivery of the software, which appeared correct and had previously shown results of 500,000 clients treated in 40 minutes, had now only processed 100,000 clients in 5 hours.

We can see that the number of nested loops increases the transaction time exponentially

ALL the HINTS were deleted from seven of the procedures and five of the views.

Several tests were performed with PCTFREE 15 for the CLIENT table instead of 35 as in production.

The transactions performed massive updates after the first insert and the result was 189000 lines chained and the performance plunged in consequence. So yet again we see that PCTFREE and chaining have to be dealt with carefully.

Attention must be taken with INITIAL and NEXT parameters. New deliveries of scripts from integration gave frequently tens of extents after the first transaction for the CLIENT table alone.

Creation order:

This transaction is even more particular. It’s a calculation of the average amount of the client’s bill excluding the first bill. (this only being distinguished by a value of a sequence). The negation blocks Oracle from using the HASH JOIN. Here we must wake up the gray matter to ensure our desired effect.

Statement before modification :

Here the select is easily understandable.

Execution Plan:

After being rewritten the result is:

Thus the select becomes slightly more difficult to understand.

The trick consists in transforming the negation into a join, so the hash join can be used. We therefore total all the bills and subtract the first bill.

Execution Plan:

Written thus, the transaction comes down from 20 minutes to less than 2 minutes.

Therefore we have a good illustration of the eternal dilemma: easily understood but not so efficient, or a little inspired and extremely fast.

This parameter is essential if we wish to benefit from the parallel running of certain types of transactions such as the full table scans. In production the value on the client table is set to 2. It was chosen in relation to the number of CPU’s and disks available.

Each new delivery of the application the scripts ignores the parameter and sets it to default.

This is just to ensure that we are on our toes, this is however typical of day to day problems.

This clause has been added to all the views - WITH READ ONLY. This ensures that nothing can be inserted across views, even if this is impossible with views that join several tables. This is a measure that is recommended for reasons of security and clarity.

With the modifications, the overall transaction time has been reduced from 5 hours to less than one hour.

• Identify the statements that consume 80-90% of the global time. Some companies, including Oracle, can provide some tools and Oriole Corporation will have one available soon. The analysis must then be performed by somebody with experience.
• The optimization of one query has nothing to do with the optimization of another query, even if the two look superficially similar. Beware of quick generalizations.