SQL Tuning

The progress of a SQL statement is divided into 3 phases:

1. parsing (and analysis)
2. execution
3. the fetch

Parsing

The analysis of the SQL statement consists of:

• Checking the syntax of the SQL statement.
• research in the data dictionary information:
• The existence and structures of objects referenced.
• The user rights on the objects referenced.
• Translation of employee names (synonyms).
• Putting on the library cache of the SQL query.

Using the information collected, the ORACLE optimizer develops an implementation plan. This execution plan is then stored in the shared pool.

Execution

During the implementation phase of the SQL statement, the server process:

Applies the implementation plan to read data (which are therefore in the data buffers in SGA). Performs physical reads from data files or read / write memory.

Fetch

During the phase of ‘Fetch’, the records are transferred from the buffers of data to the user process.

Implementation plan

The implementation plan is a sequence of physical operations that oracle must perform to extract the data requested. These physical operations are performed on tables and / or indexes.

Definition of an index

• An index is a schema object that accelerates the extraction of lines through a pointer. You can create indexes explicitly or implicitly (through the creation of unique constraint for example).
• At runtime an application (on a table without indexes or columns not covered by the indexes already created), the entire table will be scanned. …
• If correct indexes have been created on a table if a request comes, the entire table will not be swept.
• Finally, when you delete a table, the corresponding indexes are also deleted.
• An index covers one or more columns of the table (Composite Index).
• In the case where multiple columns appear in the index, the column order is important.

Some operations and options of an implementation plan

TABLE ACCESS

• • FULL: accesses all rows of the table
  • BY ROWID: access via rowid.
  • BY INDEX ROWID: accesses the table after accessing an index

INDEX

• • Unique index: access via a unique index
  • Non Unique index: access via a non-unique index

NESTED LOOPS, MERGE JOIN, HASH JOIN:

• • Joins

Oracle Optimizer

The Oracle optimizer is a component of Oracle server, which examines all SQL statements and selects the optimal execution plan for each SQL. To choose the execution plan, the optimizer is based on the syntax for the sql, existing indexes on the tables accessed, the general parameters of the body, .

The optimizer can work in several modes (the mode is set by a parameter of init.ora or spfile):

Rule: This method uses a set of predefined rules to calculate the execution paths

Choose: Choose mode uses more predefined rules data base (table size, number of rows per index

Tuning tools

Oracle provides several tools for sql tuner orders:

Explain

Explain is a utility to know the execution plan of a SQL statement. Typically, it is used during the phases of development: before joining a sql in a program, we check if the execution plans are correct.

TKPROF

TKPROF can trace a program, and for each SQL statement for this program, to know are “cost” number of nblocks read, execution time, He can ….. when used with option ” explain = userid / password “also provide the execution plan of each level.

Auto trace

Specific tools to sqlplus, auto trace provides the following options to know the implementation plans, costs … each SQL statement executed in a sqlplus session

Warning: These tools require a different table “PLAN_TABLE” to display the results to explain. The creation script utlxplan.sql this table is in the $ ORACLE_HOME / rdbms / admin on the Oracle server.

The tools presented above are the basic tools for Oracle in the sql tuner. There are many tools, graphical or otherwise, encapsulating these tools (Tuning pack, statpacks ORACLE instance).

Optimization

For “large” tables, always use indexes (not FULL ACCESS) And always use good indexes. There are some rules:

• Index on select
• Recognize a “good” index

Index on select

The basic rule is to use the index to refer in WHERE clause for indexed columns.

For information on indexed columns of a table, you can view user_ind_columns:

Select index_name, column_name, COLUMN_POSITION from user_ind_columns where table_name = name> <table

But in some cases, the SQL reference indexed columns, but the index is not used:

Function on an indexed column

If a clause that refers to an indexed column using a function, the optimizer will not use the index.

Composite Index

If the index is composed of several columns, where clause must reference at least the first column for the index is used.

Therefore, when defining an index column order is very important (the order can be visualized in the column of the table COLUMN_POSITION user_ind_columns)

IS NULL Operator

Do not use the IS NULL operator in an indexed column, otherwise the optimizer ignores the index (idem for IS NOT NULL).

The LIKE operator

When the LIKE operator is used on an indexed column should not begin with ‘%’, otherwise the index will not be used.

Use the right index

Selectivity

A good index is a selective index. Selectivity is the ratio between the number of values in the index and the number of rows in the table.

• The higher the number is closer to 1 (UNIQUE INDEX), the index is more selective
• The higher the number is closer to 0, the index is less selective

Example

Also in the basic example on the table command_line Several indexes are positioned to accelerate research:

index on article_code: Search command lines on this Article

index on code_num: Search command lines in such order

selectivity of these indexes:

· article_code: 1001 / 5496069 = 0.00018

· code_num: 1000001 / 5496069 = 0.18

We can see that the index is more selective than the other one has some command line or a few dozen, then the index on code_num going to be very efficient and process a few lines, while the same article can be found on thousands of lines of commands, so if we use this index, Oracle will treat thousands of lines.

Distribution of index values

Selectivity is not everything: you must also check the distribution. Imagine, for example, the ORDERS table schema example, there is an index on the status field contains two values ‘E’ (for “current command”) and ‘S’ (for “command resulted”).

Sub-queries

Avoid sub-queries in the select. This often causes performance problems, and it is usually complicate to tune sub-queries.

Try to use EXISTS class over IN where ever possible.

As in case of EXISTS outer query execute when first row found in sub-queries. Other hand in case of IN sub-query executes completely then Data of sub-query being used by outer query.

Warning: NOT IN will always cause a FULL ACCESS, preferring in general the NOT EXISTS.

Views

• • Oracle views are simply re-selecting entries.
  • They are neither more nor less quickly than selects.
  • They are often extremely complex and sometimes poorly written software packages.

In case it is not possible to improve the performance of a view it is better to use materialized views. A materialized view is a physical representation of data views, ie the data is stored (like a table), then duplicated and frozen, but also much faster to access. It is possible to update data for this purpose, either periodically or on demand.

Example: Creating a materialized view refresh every day:

create materialized view v1

refresh start with sysdate next sysdate + 1

as select * from customer orders

WHERE numero = numeroclient;

A materialized view is stored physically, you can define a clause strorage at its creation, and also many other options on the refresh, the frequency of these, etc….