Using optimizer hints in an Oracle database.

Boicea, Alexandru ; Radulescu, Florin ; Bucur, Ion 等

1. INTRODUCTION

Optimizer hints can be used with SQL statements to alter execution plans. Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the 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 such a case, use hints to force the optimizer to use the optimal execution plan.

The following work presents the manner in which the hints can influenced the execution of an application following mainly the number of executions of stored procedures and functions and the total time of execution. For this reason the stored procedure Transformation_ Loading was chosen which executes cyclically other stored procedures and functions. The execution diagram is presented in Figure 1. Optimization is starting from the performances presented in the Table 1 which is showing the number of executions (Count) of every procedure and function and the average time of executions (Average Time). After modification of the system parameters, specified for every optimization type, were made in average 100 execution tests at different moments in time with maximum 300 simultaneous users connected to the database.

[FIGURE 1 OMITTED]

The procedure Start_Application is monitoring the total time of execution spent from the time of launching an execution until the finalization of the complete cycle, taking into consideration just the execution times over 1 second. The optimization will take place at the levels INSTANCE, SESSION or INSTRUCTIONS.

2. OPTIMIZATION AT THE LEVEL OF INSTANCE

Oracle offers several optimizer modes that allow to choose your definition of the "best" execution plan (Garmany et al., 2008). Optimization at the level of instance is made setting the parameter OPTIMIZER MODE on the Configuration menu, accessed from the Oracle console.

The values of this parameter can be:

Optimizer_Mode =CHOOSE -- optimization at the level of cost

Optimizer_Mode=FIRST_ROWS -- optimization of the answering times of interrogation

Optimizer_Mode=ALL_ROWS -- optimization of the consumption of resources

Optimizer_Mode = RULE -- optimization at the level of rules There were obtained the following times of execution in terms of options:

Optimizer_Mode = CHOOSE -- 541 sec

Optimizer_Mode = FIRST_ROWS -- 537 sec

Optimizer_Mode = ALL_ROWS -- 549 sec

Optimizer_Mode = RULE -- 504 sec

Therefore the optimization based on rules (indifferent of the existence of statistics) has the better performances.

3. OPTIMIZATION AT THE LEVEL OF SESSION

If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement. The optimizer goal applies only to queries submitted directly. For changing the optimization at the level of session would be using the command:

ALTER SESSION SET OPTIMIZER_GOAL= option where option is one of the option presented in the item 2.

The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans. Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query. The rule and choose modes reflect the obsolete rule-based optimizer, so we will focus here. The following times of execution were obtained in terms of options:

Optimizer_Goal = CHOOSE -- 453 sec

Optimizer_Goal = FIRST_ROWS -- 476 sec

Optimizer_Goal = ALL_ROWS -- 490 sec

Optimizer_Goal = RULE -- 429 sec

Optimization based on rules obtained the best time of execution at the session level too. Analyzing the results it can be observed that optimization at the level of session is always prevalently comparative with the one at the level of instance.

We can use the time performance views V$Sys_Time_Model and V$Sess_Time_Model. These views gather cumulative stats for either the entire instance, or per session(Burleson, 2006).

4. OPTIMIZATION AT THE LEVEL OF INSTRUCTION

Let's take a quick look at how hints are used to alter optimizer execution plans. A optimizer hint is an optimizer directive placed inside the SQL statement and used in those rare cases where the optimizer makes an incorrect decision about the execution plan.

Changing the optimization at the level of instruction can be made indicating the type of optimization respecting the following syntax:

SELECT/* hint_name * /column1, column2, ...

INTO var1, var2, ...

FROM table_name WHERE conditions;

where hint_name represent the type of optimization and can be CHOOSE, FIRST_ROWS, ALL_ROWS, RULE, COST, INDEX, CACHE, CLUSTER, so on.

Because SQL is a declarative language, a query can be written in many different ways, each with a different execution plan. For changing the optimization would be changed the sub application and cursors from procedures and functions, specifying the type of optimization.

From all the tested types the smallest time of execution was obtained using the optimization based on COST -- 309 sec.

5. CONCLUSIONS

Analyzing the results of testing it can be observed that the obtained time of execution through optimization at the session level is better then the one obtained through optimization at the level of instance. Nevertheless, optimization at level of instruction (based on costs) has the best results concerning the time of execution of procedures. This fact draws us to the conclusion to obtain higher performance an Oracle application depends in a highest degree of developers, which can intervene at the instruction level, when the administrator can intervene just at the level of parameters of system.

Always the optimal performances can be obtain through the setting of parameters of system correlated with the optimization of applications at the level of instruction, specially at the database with a big number of registrations.

A proper suggestive representation is observed the TIME in the histogram presents in Figure 2.

[FIGURE 2 OMITTED]

When using hints, in some cases, you might need to specify a full set of hints in order to ensure the optimal execution plan. For example, if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer might not necessarily use that hint, because the optimizer might have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer. However, Oracle does not recommend changing the default values for many of these parameters because the changes can affect the execution plans for thousands of SQL statements. For example, the optimizer is using very rarely an index in the case of an interrogation which is returning a very big number of lines from a table. Too many indexes will also slow down performance of updates and inserts (Garmany, 2007).

In this situation if the index is deactivated we will have better performances. If an SQL instruction is modifying an indexed column, this will make the optimizer to not use in execution the indexes that is having the respective column. Modifying the clause WHERE in an SQL instruction, without modifying the result of interrogation, can determine the optimizer to suppress the use of index.

The optimization depends on a model and it depends on a number of assumptions about how that model will be have at run-time. Sometimes the model isn't realistic enough; sometimes the run-time activity doesn't quite match the model(Lewis, 2006). And just to make things that little bit harder to understand, sometimes the model, the run-time action, or both, will change as you upgrade the version of Oracle.

6. REFERENCES

Burleson, D.K.(2006). Oracle Tuning: The Definitive Reference, ISBN 0-9744486-2-1, Ed. Library of Congress, USA

Garmany, J., Karam, S., Hartmann, L., Jain,V.J. & Carr, B., (2008). Oracle 11g New Features, Ed. Rampant TechPress, ISBN 978-0-9797951-0-70-9797951-0-9, USA

Garmany, J.(2007). The Power of Indexings, Oracle Tips by Burleson Consulting, February 16th, 2007, USA

Lewis, J.(2006). Cost Based Oracle: Fundamentals ISBN 1-59059-636-6, Ed. Apress, USA

(2007) http://www.oracle.com/technology/products/database/ oracle11g/index.html, Accesed on:2009-05-11

Tab. 1. The initial execution times for stored procedures

DESCRIPTION Count Average
 (exec) Time
 (sec)

Start_Application 1 628
Transformation_Loading 1 621
Execute_Begining_Statemens 1 610
Process_EDI_Data 39 534
Update_Invoice 39 49
Execute_Parse 39 178
Execute_Level_Two 9 354
Split_Child 220 23
Merge_Bills 1124 18
Empty_Table 2043 35
Write_Log 8838 160