Oracle SQL tuning database optimization steps graphic tutorial

SQL Turning is a tool in Quest Central software developed by Quest. Quest Central is an integrated, graphical, and cross-platform database management solution that allows you to manage both Oracle, DB2, and SQL server databases.

I. Introduction to SQL Tuning for SQL Server

The optimization of SQL statements is critical to the optimal performance of the database. Unfortunately, application optimization is often ignored due to time and resource factors. The SQL Tuning (SQL optimization) module compares and evaluates the Running Performance of SQL statements in a specific application, and provides intelligent optimization suggestions to help users improve application response time. The SQL optimization module provides functions such as non-invasive SQL collection, automatic optimization, and expert suggestions to comprehensively improve SQL optimization.

Ii. Use of SQL Tuning for SQL Server

1. Open the Quest Database Management Solutions pop-up window 1, as shown in.

Figure 1

2. enable SQL Tuning in red to optimize SQL

(1) Establish a connection.

Select "SQL Server" in the "Database" tree on the main interface of Quest Central, and then select the "SQL Tuning" option in the "Tools" box that appears below, open the "Lanch SQL Tuning for SQL Server Connections" dialog box (figure 2 and Figure 3 ). We will establish a connection to the database server here, and further analysis will be completed on it.

Figure 2 "establish connection" dialog box

Figure 3

Double-click the New Connection icon. In the displayed window, enter the database information, click OK, and then click Connect.

(2) analyze the original SQL statement. After you click Connect, a new window is displayed.

Figure 4

In the "Oriangal SQL" text box that opens the window, enter the original SQL statement to be analyzed, and select the corresponding database name in red. The SQL statement code is as follows:

Figure 5 analyze the original SQL statement

Original SQL statement

Click the Execute button on the toolbar to Execute the original SQL statement. SQL Tuning automatically analyzes the SQL Execution Plan and displays the analysis result on the interface (figure 5 ).

(3) Optimize SQL statements.

Click the "Optimize Statement" button on the toolbar to enable SQL Tuning to Optimize the SQL Statement, we can see that SQL Tuning has produced 19 optimization solutions equivalent to the original SQL (figure 6 ).

Figure 6 SQL optimization solution

(4) obtain the optimal SQL statement.

Next, we will execute the above optimization scheme to select the equivalent SQL statement with the best performance. Select the optimization scheme to be executed in the list (all Selected by default), click the drop-down menu next to the "Execute" button on the toolbar, and select "Execute Selected ". After running all the SQL statements, click the "Tuning Resolution" button on the left of the page,

We can see that the optimal SQL has come out, and the running time can be increased by 21%! (Figure 7)

Figure 7 Tuning Resolution Interface

The optimal SQL statement is as follows:

5) learn to write expert-level SQL statements.

Optimized SQL statement

SELECT dbo. Person_BasicInfo .*,

Dbo. Graduater_GraduaterRegist.RegistNO AS RegistNO,

Dbo. Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,

Dbo. Graduater_GraduaterRegist.RegistMan AS RegistMan,

Dbo. Graduater_Business.ComeFrom AS ComeFrom,

Dbo. Graduater_Business.Code AS Code,

Dbo. Graduater_Business.Status AS Status,

Dbo. Graduater_Business.ApproveResult AS ApproveResult,

Dbo. Graduater_Business.NewCorp AS NewCorp,

Dbo. Graduater_Business.CommendNumber AS CommendNumber,

Dbo. Graduater_Business.EmployStatus AS EmployStatus,

Dbo. Graduater_Business.NewCommendTime AS NewCommendTime,

Dbo. Graduater_Business.GetSource AS GetSource,

Dbo. Graduater_Business.EmployTime AS EmployTime,

Dbo. Graduater_Business.Job AS Job,

Dbo. Graduater_Business.FillMan AS FillMan,

Dbo. Graduater_Business.FillTime AS FillTime,

Dbo. Graduater_Business.IsCommendOK AS IsCommendOK,

Dbo. Graduater_Business.ApproveUser AS ApproveUser,

Dbo. Graduater_Business.ApproveTime AS ApproveTime,

Dbo. Graduater_Business.RegistTime AS RegistTime,

Dbo. graduater_business.employee Corp AS employee Corp,

Dbo. Graduater_Business.JobRemark AS JobRemark,

Case when dbo. Graduater_Business.ComeFrom = 'ws 'then' online registration'

WHEN dbo. Graduater_Business.ComeFrom = 'hp 'then' UPC tower'

WHEN dbo. Graduater_Business.ComeFrom = 'jd 'then'

WHEN dbo. Graduater_Business.ComeFrom = 'mc 'then' Racecourse'

WHEN ComeFrom = 'zx' then' high finger center 'end AS ComeFromName,

Dbo. Person_Contact.Address AS Address,

Dbo. Person_Contact.Zip AS Zip,

Dbo. Person_Contact.Telephone AS Telephone,

Dbo. Person_Contact.Mobile AS Mobile,

Dbo. Person_Contact.Email AS Email,

Dbo. Person_Contact.IM as im,

Dbo. Person_Skill.ForeignLanguage AS ForeignLanguage,

Dbo. person_skill.foreign?agelevel AS foreign=agelevel,

Dbo. Person_Skill.CantoneseLevel AS CantoneseLevel,

Dbo. person_skill.manarinlevel AS manarinlevel,

Dbo. Person_Skill.Language AS Language,

Dbo. Person_Skill.TechnicalTitle AS TechnicalTitle,

Dbo. Person_Skill.ComputerLevel AS ComputerLevel,

Dbo. Person_EmployPurpose.JobType AS JobType,

Dbo. Person_EmployPurpose.Vocation AS Vocation,

Dbo. Person_EmployPurpose.JobPlace AS JobPlace,

Dbo. Person_EmployPurpose.Salary AS Salary,

Dbo. Person_EmployPurpose.OnJobDate AS OnJobDate,

Dbo. Person_EmployPurpose.CorpType AS CorpType,

Dbo. Person_EmployPurpose.Job AS RequireJob,

YEAR (GETDATE ()-YEAR (dbo. Person_BasicInfo.Birthday) AS Age,

Dbo. Graduater_Business.EmployType AS EmployType,

Dbo. Graduater_Business.EmployTypeCode AS EmployTypeCode,

Dbo. Graduater_Business.EmployCorpType AS EmployCorpType,

Case when dbo. Graduater_Business.PrintStatus = 'printed 'then' printed'

ELSE 'unprinted 'end AS PrintStatus,

Dbo. Graduater_Business.PrintTime AS PrintTime,

Case when dbo. Graduater_Business.EmployStatus = 'is 'then' already hired'

ELSE 'unemployed 'end as EmployStatusView

FROM dbo. Person_BasicInfo

Inner join dbo. Graduater_Business

ON dbo. Person_BasicInfo.PersonID = dbo. Graduater_Business.PersonID

Left outer join dbo. Graduater_GraduaterRegist

ON dbo. Graduater_Business.GradBusinessID = dbo. Graduater_GraduaterRegist.GraduaterGUID

Inner join dbo. Person_Contact

ON dbo. Person_BasicInfo.PersonID = dbo. Person_Contact.PersonID

Inner join dbo. Person_Skill

ON dbo. Person_BasicInfo.PersonID = dbo. Person_Skill.PersonID

Inner join dbo. Person_EmployPurpose

ON dbo. Person_BasicInfo.PersonID = dbo. Person_EmployPurpose.PersonID

OPTION (force order)

(

Through the above steps, we can automatically optimize SQL statements, but more importantly, we can also learn how to write such high-performance SQL statements. Click the "Compare Scenarios" button on the left of the interface. We can Compare the optimization scheme with any two SQL statements in the original SQL statement. SQL Tuning will display the differences in different colors,

You can also compare the execution plans of the two SQL statements in the "Execution Plan" below to understand the differences (figure 8 ).

Figure 8 "Compare Scenarios" Page

Oracle SQL tuning goals

Oracle SQL tuning is a complex topic. Oracle Tuning: The Definitive Reference describes The details of SQL tuning. Nevertheless,

To improve system performance, Oracle DBA should follow the following general guidelines.

1. SQL tuning target

It extracts more data rows to generate the best execution plan (physically read (PIO) and logical read (LIO) as much as possible) with minimal database access times ).

Guiding principles

Remove unnecessary large full table scans

Full table scanning of large tables will generate a large system I/O and degrade the performance of the entire database. Optimization experts first evaluate the number of rows returned by the current SQL query. The most common

To add an index to a large table scanned by the entire table. B-tree indexes, bitmap indexes, and function-based indexes can avoid full table scanning. Sometimes, you can scan unnecessary full tables.

You can add a prompt to avoid full table scan.

Cache small table full table Scan

Sometimes full table scan is the fastest way to access these tables. The Administrator should ensure that the dedicated data buffer (keep buffer cache, nk buffer cache) is available to these tables. In

Small tables can be forcibly cached to the keep pool after Oracle 8.

Use the best Index

Oracle access objects sometimes have more than one index option. Therefore, you should check each index on the current query object to ensure that Oracle uses the best index.

Materialized aggregation operations are calculated in a static table

SQL Access advisor, one of the 10 Gb features of Oracle, provides recommendations for indexing and materialized views. Materialized views can be used to pre-connect tables and pre-Digest Table data. (Translator)

Oracle can update the data in the materialized view in advance based on the specific update method. In the query, only the materialized view can be queried to obtain the final required statistical data.

Result. Materialized View is actually an entity table)

These summarize the objectives of SQL tuning. However, it seems simple and not easy to adjust, because it requires a thorough understanding of Oracle SQL. Next let's start from

Understand Oracle SQL optimization as a whole.

2. Oracle SQL Optimizer

Oracle DBA must first check the default optimizer mode of the current database. Oracle initialization parameters provide a lot of Cost-optimized optimizer modes and old obsolete rules-based

Optimizer mode (or hint). The cost-based optimizer mainly depends on the statistical information collected by table objects using the analyze command. Oracle depends on the table statistics.

And generate the most efficient execution plan for the current SQL statement. In some cases, cost Optimizer may make incorrect decisions. Cost-based optimizer is constantly improving,

However, there are still many scenarios where the rule-based optimizer can make queries more efficient.

Before Oracle 10 Gb, Oracle's default optimizer mode is CHOOSE. In this mode, if the table object lacks statistics, Oracle uses rule-based optimization.

If the statistical information exists, the cost-based optimizer is used. The hidden danger of using the CHOOSE mode is that some complex query objects have statistical information, while other objects

There is no statistical information.

Starting from Oracle 10 Gb, the default optimizer mode is ALL_ROWS, which helps full table scan better than index scan. The ALL_ROWS optimizer mode is designed to minimize computing resources and

Full table scan. Index scanning (first_rows_n) adds additional I/O overhead. But they can return data faster.

Therefore, first_rows, first_rows_100 or first_rows_10 are selected for most OLTP systems, so that Oracle uses index scanning to reduce the number of read blocks.

Note: Starting from Oracle 9i R2, the Oracle performance adjustment guide indicates that the first_rows optimizer mode has been deprecated and replaced by first_rows_n.

When only some tables contain CBO statistics, while others do not, Oracle uses the cost-based optimization mode to estimate the statistics of other tables at run time (that is, dynamic sampling ).

), Which affects the performance of a single query to a large extent.

In short, the Oracle database administrator should always try to change the optimizer mode as the first step of SQL tuning. The primary principle of Oracle SQL tuning is to avoid the terrible full table scan.

Description. One feature is that a non-efficient SQL statement uses all indexes to improve query performance. This is still a failed SQL statement.

Of course, it is appropriate to use full table scan in some cases, especially for aggregate operations such as sum and avg, because to obtain results, most data rows on the table must be read

Cache. SQL tuning experts should reasonably evaluate each full table scan and verify whether the index can improve the performance.

In most Oracle Systems, SQL statements retrieve only a subset of table data. The Oracle optimizer checks whether the index causes more I/O. However, if

For an inefficient query, it is difficult for the cost-based optimizer to select the optimal data access path. Instead, full table scan is preferred. Therefore, the Oracle database administrator should always review those steps

SQL statement for full table scan.

For more questions about full table scan and The correct optimization mode, see "Oracle Tuning: The Definitive Reference"

Iii. SQL adjustment strategy steps

Many people ask where SQL tuning starts. First, capture SQL statements from the Library cache based on their activity status.

1. Search for SQL statements with greater impact

We can sort the execution times of SQL statements to obtain SQL statements with more execution times. Executions column and table stats $ SQL _summary in the v $ sqlarea View

Or dba_hist_ SQL _summary can locate the most frequently used SQL statements. Note: You can also list SQL statements in the following ways.

Rows processed

The more rows processed, the higher I/O will be generated, and a large amount of temporary tablespace may be consumed.

Buffer gets

High Buffer gets may indicate excessive centralized resource query and hot block

Disk reads

High Disk Read will cause excessive I/O

Memory KB

The memory allocation size can identify whether the SQL statement uses a large number of table connections in the memory.

CPU secs

CPU overhead indicates which SQL statements consume a large amount of CPU resources

Sorts

The more data is sorted, the worse the SQL performance, and a large amount of temporary tablespace is occupied.

Executions

The number of executions indicates the current SQL statement frequency and should be adjusted first because these statements affect the overall performance of the database.

2. Determine the SQL Execution Plan

Each SQL statement can obtain its execution plan based on SQL _ID. There are a large number of third-party tools to obtain the execution plan of SQL statements. The most common way to obtain execution is to use Oracle

The built-in explain plan Program. By using this program, Oracle DBA can parse and display the execution plan of the SQL statement without executing the SQL statement.

To view the output of the SQL Execution plan, you must first create a plan table. Oracle provides an utlxplan. SQL script to create the table. Execute this script and create

Public synonyms.

Sqlplus> @ utlxplan

Table created.

Sqlplus> create public synonym plan_table for sys. plan_table;

Synonym created.

Most relational databases use the interpreter to input SQL statements, and then run the SQL optimizer to output the access path information to a plan_table. So that we can view and adjust

The access method. The following is a complex SQL query.

Explain plan set STATEMENT_ID = 'test1'

SET STATEMENT_ID = 'run1'

INTO plan_table

FOR

SELECT 'T' | plansnet. terr_code, 'P' | detplan. pac1

| Detplan. pac2 | detplan. pac3, 'p1', sum (plansnet. ytd_d_ly_tm ),

Sum (plansnet. ytd_d_ty_tm ),

Sum (plansnet. jan_d_ly ),

Sum (plansnet. jan_d_ty ),

FROM plansnet, detplan

WHERE

Plansnet. mgc = detplan. mktgpm

AND

Detplan. pac1 in ('n33', '20160301', '20160301', '20160301', 'ba ',

'P51', 'q27', '123', '123', '123', '123', '123', '123 ',

'123', '123', '123', '123', '123 ')

Group by 'T' | plansnet. terr_code, 'P' | detplan. pac1 | detplan. pac2 | detplan. pac3;

This syntax uses pipelines to input SQL optimizer, parse SQL, store execution plan information to the plan_table table, and RUN1 as the identifier to identify the current SQL statement. Note: This query

Not executed. It only creates an internal access information and outputs it to plan_table. The plan table contains the following fields.

Operation

Operations completed by the current statement, usually including table access, table merge, sort, or index operation

Options

Operation, such as full table, range table, and join

Object_name

Query component name

Process ID

Query the ID of a component

Parent_ID

Query the created parent ID. Note that some queries have the same parent ID.

Now that plan_table has been filled, you can use the following query to view the execution plan of the current SQL statement.

Plan. SQL-displays contents of the explain plan table

Set pages 9999;

SELECT lpad ('', 2 * (level-1) | operation,

Options,

Object_name,

Position

FROM plan_table

Start with id = 0

AND

Statement_id = 'run1'

Connect by prior id = parent_id

AND

Statement_id = 'run1 ';

The following describes the execution plan information of the current statement and the execution sequence of each part.

SQL> @ list_explain_plan

OPERATION

Bytes -------------------------------------------------------------------------------------

OPTIONS OBJECT_NAME POSITION

-------------------------------------------------------------------------------------

SELECT STATEMENT

SORT

Group by 1

CONCATENATION 1

Nested loops 1

Table access full plansnet 1

Table access by rowid detplan 2

Index range scan DETPLAN_INDEX5 1

NESTED LOOPS

The preceding execution plan shows that the current SQL statement has a table scan. To adjust the SQL statement, we should look for columns on the planset IN THE where clause of the table. Here we

We can see that the where clause contains a column mgc that belongs to the planset table and is used as the connection condition. This indicates that an index based on the planset. mgs column is necessary.

The plan table does not show the details of the entire SQL statement, but it is very useful for obtaining the data access path. The SQL optimizer knows the number of rows (base number) and index words of each table.

Segment status. However, we do not know the data distribution, such as the number of rows that a Component expects to return.

3. Adjust SQL statements

For child execution plans that can be optimized, the SQL statement should be adjusted as follows.

Add a prompt to modify the SQL Execution Plan

Use a global temporary table to override SQL statements

Use PL/SQL to rewrite the SQL statement. For some specific queries, this method can be improved by about 20 times. Encapsulate these SQL statements into packages containing stored procedures for query.

Use the prompt to adjust the SQL

In most SQL tuning tools, the usage tips are mostly used. After an SQL statement is added, the SQL query is accessed by the specified path.

Troubleshooting tip!

To facilitate testing, we can use the alter session command at any time to modify the value of an optimization Parameter to observe the comparison of the results before and after adjustment. Use the new opt_param prompt to obtain

The same effect.

Select/* + opt_param ('optimizer _ mode', 'First _ rows_10 ') */col1, col2...

Select/* + opt_param ('optimizer _ index_cost_adj ', 20) */col1, col2 ..

Oracle has released a large number of SQL prompts, and prompts that the Oracle version will continue to increase and complicate.

Note: The prompt is usually used to debug SQL. The best way is to adjust the statistics of the optimizer so that the CBO mode automatically obtains the optimal execution path, which is equivalent to the function of using the prompt.

Let's take a look at the most common tips for improving performance.

Mode hints: first_rows_10, first_rows_100

Oracle leading and ordered hints Also see how to tune table join order with histograms

Dynamic sampling: dynamic_sampling

Oracle SQL unmarshented tuning hints-Guru's only

The cardinality hint

Table connection Sequence

When the order of table join can be optimized, we can use the ORDERED prompt to force tabulation to connect according to the order in which the from clause appears.

First_rows_n prompt

Oracle provides two cost-based optimization tips: first_rows_n and all_rows. In first_rows mode, a user is returned whenever data is queried.

User. The all_rows mode is designed to optimize resources. Data is returned to the client only after all results are calculated and executed.

SELECT/* + first_rows */

4. Case studies

The same SQL statement is written differently. That is to say, a simple SQL query can generate the same result set in different ways, but its execution efficiency and access method vary greatly.

The SQL statement in the following example uses three different statements to return the same result.

A standard join: --> standard join

SELECT *

From student, REGISTRATION

WHERE

STUDENT. student_id = REGISTRATION. student_id

AND

REGISTRATION. grade = 'a ';

A nested query: --> nested query

SELECT *

FROM STUDENT

WHERE

Student_id =

(SELECT student_id

FROM REGISTRATION

WHERE

Grade = 'A'

);

A correlated subquery: --> related subquery

SELECT *

FROM STUDENT

WHERE

0 <

(SELECT count (*)

FROM REGISTRATION

WHERE

Grade = 'A'

AND

Student_id = STUDENT. student_id

);

We should optimize the current SQL statement according to the Basic SQL principles.

5. Skills for writing efficient SQL statements

The following provides some general guidelines for writing efficient SQL statements, regardless of the optimization mode selected by the Oracle optimizer. These are simple methods, but according to their

Doing so will get twice the result with half the effort (proven in practice ).

A. Use a temporary table to override complex subqueries

Oracle uses global temporary tables and the WITH operator to solve complex SQL subqueries. In particular, subqueries in the where clause, the SELECT clause scalar quantum query,

Inline view of the FROM clause. Using temporary tables to implement SQL tuning (and using the materialized view of WITH) can greatly improve the performance.

B. Use MINUS instead of EXIST subquery

Replacing not in or not exists with the MINUS operation will generate a more efficient execution plan (you can test the execution plan as needed ).

C. Use SQL analysis functions

Oracle analysis functions can extract data at a time for multi-dimensional aggregation operations (such as ROLLUP and CUBE) to improve performance.

D. Rewrite not exists and query as external connections not exists subqueries

In some cases, a NOT query (a column in where IS defined as a NULL value) can rewrite this non-correlated subquery to an external link of is null. For example:

Select book_key from book

Where

Book_key not in (select book_key from sales );

In the where clause, we use an external connection to replace the original not exits to get a more efficient execution plan.

Select B. book_key from book B, sales s

Where

B. book_key = s. book_key (+)

And

S. book_key is null;

E. Index NULL value column

If your SQL statements frequently use the NULL value, you should consider creating an index based on the NULL value. To optimize the query, you can create a NULL-value-based index function.

(For example, create index I _tb_col on tab (nvl (col, null); create index I _tb_col on tab (col, 0 );)

F. Avoid indexing-based operations

Do not perform any operations based on the index column unless you create an index function. You can also reset the design column so that the predicates in the where clause column do not need to be converted.

--> The following are inefficient SQL statements.

Where salary * 5>: myvalue

Where substr (ssn, 1234) ="

Where to_char (mydate, mon) = "January"

G. Avoid using not in and HAVING

When appropriate, use the not exists subquery to make it more efficient.

H. Avoid using LIKE predicates

If the = operation can be used, the LIKE operation should be avoided as much as possible.

I. Avoid data type conversion

If a where clause column is numeric, do not use quotation marks. Quotation marks are always used for a character index column. The following describes how to mix data types.

Where cust_nbrs = "123"

Where substr (ssn, 7,4) = 1234

J. Use decode and case

The decode and case functions can be used to minimize the number of queries to a table.

K. Do not be afraid of full table Scan

Not all OLTP systems are optimized when using indexes. If your query returns the vast majority of data in the table, the full table scan performance is superior to the index scan. It depends on

Some factors include your configuration (db_file_multiblock_read_count, db_block_size), parallel query, and the number of table blocks and index blocks in the buffer cache.

L. use aliases

The table alias is always used in the reference column.

--> Author: Robinson Cheng