Oracle SQL Tuning Database optimization steps Graphic tutorial

SQL turning is a tool in the Quest Central software produced by Quest Corporation. Quest is an integrated, graphical, Cross-platform database management solution that can manage Oracle, DB2, and SQL Server databases simultaneously.

Introduction to SQL tuning for SQL Server

The optimization of SQL statements is critical to the best performance of the database. Unfortunately, however, application optimization is often overlooked because of time and resource constraints. SQL tuning (SQL optimized) module can compare and evaluate the running performance of SQL statements in specific applications, and propose intelligent optimization suggestions to help users improve the response time of application. SQL Optimization module has the functions of non intrusive SQL collection, automatic optimization and expert advice, and improves the SQL optimization work comprehensively.

Second, SQL tuning for SQL Server use

1, open the Quest Database Management Solutions pop-up window as shown in Figure 1

Figure 1

2, at the red mark to open SQL Tuning optimization sql

(1) Establish a connection.

On the Quest Central main interface, select SQL Server on the Database tree, and then in the Tools box that appears below, select the SQL tuning option to open the Lanch SQL tuning for SQL Server Connections dialog box (Figure 2, Figure 3). We set up a connection to the database server here, and future analytics will be done on it.

Figure 2 "Establish a Connection" dialog box

Figure 3

Double-click the "New Connection" icon to enter the information in the database in the pop-up window, clicking OK, and then clicking Connect.

(2) Analysis of the original SQL statement, after clicking "Connect" will pop up a new window, as shown in Figure 4

Figure 4

In the open window, in the Oriangal SQL text box, enter the original SQL statement you want to analyze, select the corresponding database name at the red mark, and the SQL statement code as follows:

Figure 5 Parsing the original SQL statement

Original SQL statement

Then click on the "Execute" button on the toolbar to execute the original SQL statement, SQL tuning will automatically parse the SQL execution plan and display the analysis results to the interface (Figure 5).

(3) Optimizing SQL.

Now we click on the "Optimize Statement" button on the toolbar to get SQL tuning to start optimizing SQL, and when you're done, you can see that the SQL tuning generates 19 optimization scenarios that are equivalent to the original SQL (Figure 6).

Figure 6 SQL Optimization Scenario

(4) Obtain the optimal SQL.

Next, we will perform the optimization scenario above to select the best performance equivalent SQL statement. Select the optimization scheme you want to perform in the list (all selected by default), and then click the Drop-down menu next to the Execute button on the toolbar to select Execute Selected. When all SQL runs are complete, click on the "Tuning Resolution" button on the left side of the interface,

Can see the best SQL has come out, 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.employcorp as Employcorp,

Dbo. Graduater_business.jobremark as Jobremark,

case when dbo.Graduater_Business.ComeFrom = ' WS ' THEN ' online registration '

When dbo.Graduater_Business.ComeFrom = ' HP ' THEN ' Hua Pu mansion '

When dbo.Graduater_Business.ComeFrom = ' JD ' THEN ' Canon mansion '

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.foreignlanguagelevel as Foreignlanguagelevel,

Dbo. Person_skill.cantoneselevel as Cantoneselevel,

Dbo. Person_skill.mandarinlevel as Mandarinlevel,

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 ' not print ' end as Printstatus,

Dbo. Graduater_business.printtime as Printtime,

case when dbo. Graduater_business.employstatus = ' is ' THEN ' has been employed '

ELSE ' not employed ' 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)

(

With the above steps, we can already implement automatic optimization of SQL statements, but more importantly, we can also learn how to write such high-performance SQL statements. Click on the "Compare Scenarios" button to the left of the interface, we can compare the optimization scheme and the original SQL of any 2 SQL statements, SQL tuning will be different between them in different colors expressed,

You can also understand the differences in the execution plan below by comparing the execution plans of the two SQL statements (Figure 8).

Figure 8 "Compare Scenarios" interface

Targets for Oracle SQL tuning

Oracle SQL tuning is a complex subject. Oracle tuning:the Definitive Reference This entire book describes the details of the SQL tuning. Nevertheless

To improve system systems, Oracle DBAs should follow some of the general guidelines below.

1. SQL Tuning Target

is to extract more data rows with minimal database access to generate the best execution plan (Minimize physical read (PIO) and logical Read (Lio) as much as possible.

Guiding Principles

Remove unnecessary large full table scans

Full table scans of large tables will result in large system I/O and degrade the overall database performance. The tuning expert first evaluates the number of rows returned by the current SQL query. The most common thing to do

The method is to increase the index of the large table scanned by the whole table. A B-tree index, bitmap indexing, and a function based index can avoid full table scans. Sometimes, for some unnecessary full table sweeps

Strokes avoid full table scans by adding hints.

Cache Small table Full table scan

Sometimes full table scanning is the quickest way to access, and administrators should ensure that dedicated data buffers (keep buffer cache,nk buffer cache) are available for these tables. 

After Oracle 8 small tables can be forcibly cached to the keep pool.

Using the best index

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

Materialized aggregation operations with static table statistics

One of the features of Oracle 10g SQL Access Advisor gives suggestions for index recommendations and materialized views. Materialized views can preinstall tables and summary table data. (translator

By, that is, Oracle can update the data in the materialized view in advance according to the specific update method, but only when querying materialized view to get the final statistical data

Results. Materialized views are actually a single entity table)

These summarize the goals of SQL tuning. It's easy to adjust, however, because it requires a thorough understanding of Oracle SQL. Next let's start with

Understand Oracle SQL optimization as a whole.

2. Oracle SQL Optimizer

Oracle DBA first wants to see the default optimizer mode for the current database. Oracle initialization parameters provide many optimizer patterns based on cost optimization and previously obsolete rule-based

Optimizer mode (or hint) to choose from. The cost-based optimizer relies primarily on the statistics collected by the Table object using the Analyze command. Oracle is determined by the statistics on the table

and generate the most efficient execution plan for the current SQL. It should be noted that the cost optimizer may make incorrect decisions on some occasions. The cost-based optimizer continues to improve,

But there are still many occasions when using a rule-based optimizer to make queries more efficient.

Prior to Oracle 10g, the Oracle default optimizer pattern was choose mode. In this mode, if there is a lack of statistical information on the Table object, Oracle uses rule-based optimizations at this time

If the statistics exist, the cost-based optimizer is used. The hidden danger of using choose mode is that some objects have statistics on some complex queries, while others

Lack of statistical information.

At the beginning of Oracle 10g, the default optimizer mode is All_rows, which helps to make full table scans better than index scans. The All_rows optimizer model is designed to minimize computational resources and has

Help with full table scans. The index Scan (first_rows_n) adds additional I/O overhead. But they can return data more quickly.

As a result, most OLTP systems choose first_rows,first_rows_100 or FIRST_ROWS_10 to allow Oracle to use index scans to reduce the number of read blocks.

Note: Starting with Oracle 9i R2, Oracle Performance Tuning guidelines indicate that the First_rows optimizer model has been deprecated and that first_rows_n is used instead

When only a few tables contain CBO statistics and others lack statistics, Oracle uses a cost-based optimization pattern to estimate the statistics of other tables at run time (that is, dynamic sampling,which largely affects the performance degradation of individual queries).

In summary, Oracle database administrators should always try to change the optimizer pattern as the first step in SQL tuning. The primary principle of Oracle SQL tuning is to avoid the dreaded full table sweep

Stroke One of the attributes is an unmanaged SQL statement that uses all the indexes to improve query performance. This is still a failed SQL statement.

Of course, sometimes it is appropriate to use full table scanning, especially when doing aggregation operations like SUM,AVG, because most data rows on a table must be read into the cache. The SQL tuning master should reasonably evaluate each full table scan and verify that the use of the index can improve performance.

In most Oracle systems, SQL statements retrieve only a subset of the data on a table. The Oracle optimizer checks whether using an index can cause more I/O. However, if you build a inefficient query, the cost-based optimizer has difficulty choosing the best data access path, preferring to use full table scans. So Oracle database administrators should always review those who go a full table-scanned SQL statement.

For more questions about the full table scan and choose the correct optimization mode: Oracle tuning:the Definitive Reference

3, the SQL adjustment strategy step

A lot of people ask SQL tuning where to start. The first thing to do is to capture the SQL statements from the library cache according to their activity status.

1, looking for large impact of the SQL statement

We can sort through the number of times the SQL statement executes to get the SQL statement that executes more times. Executions columns and table Stats$sql_summary in the V$sqlarea view

Or dba_hist_sql_summary can locate the SQL statement that is currently used most frequently. Note: You can also list SQL statements in the following ways.

Rows processed

The more rows you handle, the higher I/O will be, and the likelihood of consuming a large amount of temporary tablespace

Buffer gets

The high Buffer gets may indicate that the resource is excessively centralized query, there is hot block phenomenon

Disk Reads

High disk reads will cause excessive I/O

Memory KB

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

CPU secs

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

Sorts

The more sorts, the worse the SQL performance and the large amount of temporary tablespace space

Executions

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

2. Decide the execution plan of SQL

Each SQL statement can get its execution plan based on sql_id. There are a number of third-party tools available to get the execution plan for the SQL statement. The most common way to get execution is to use Oracle

The explain plan program is taken from you. 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. Oracle provides a UTLXPLAN.SQL script to create the table. Executes the script and creates a

Common synonyms.

Sqlplus > @utlxplan

Table created.

Sqlplus > Create public synonym plan_table for sys.plan_table;

Synonym created.

Most relational databases use an interpreter to take the SQL statement as input, and then run the SQL optimizer to output access path information to a plan_table. So that we can view and tune

The way the entire access is. The following is a complex SQL query.

EXPLAIN plan SET statement_id = ' test1 ' for

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

.pac1 in (' N33 ', ' the ', ') ', ' 195 ', ' 201 ', ' BAI ',

' P51 ', ' Q27 ', ' 180 ', ' 181 ', ' 183 ', ' 184 ', ' 186 ', ' 188 ',

' 198 ', ' 204 ', ' 207 ', ' 209 ', ' 211 ')

GROUP by ' T ' | | Plansnet.terr_code, ' P ' | | Detplan.pac1 | | Detplan.pac2 | | DETPLAN.PAC3;

This syntax uses pipe input to the SQL optimizer, parses SQL, stores execution plan information to table plan_table, and RUN1 as an identifier to authenticate the current SQL statement. Note that the query

Does not execute, it simply creates an internal access information and outputs it to the plan_table. The Plan table contains the following fields.

Operation

Indicates the action that the current statement completes, typically including table access, table merge, sort, or index operation

Options

Supplemental description operation, like full table, range table, join

object_name

The name of the query component

Process ID

The ID number of the query component

parent_id

Query the parent ID of the build, note that some queries will have an identical parent ID

Now that Plan_table has been populated, you can use the following query to view the execution plan for the current SQL statement.

Plan.sql-displays contents of the Explain plan table

SET PAGES 9999;

SELECT Lpad (', 2* (level-1)) | | Operation 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 current statement execution plan information and the order in which each part is executed are given below.

Sql> @list_explain_plan

OPERATION

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

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

It is known from the execution plan above that the current SQL statement has a table scan phenomenon. To adjust the SQL statement, we should look for the columns on the Planset in the table WHERE clause. Here we

See the column MGC that exists in the WHERE clause and that belongs to the table Planset is used as a join condition. This shows that an index based on the table Planset.mgs column is necessary.

The plan table does not show the details of the entire SQL statement, but is useful for obtaining a data access path. The SQL Optimizer knows the number of rows (cardinality) for each table and some index words

The status of the paragraph. But you don't understand the distribution of data like the number of rows a component expects to return.

3, adjust the SQL statement

For those with an optimized child execution plan, SQL should be adjusted in the following manner.

Modify the SQL execution plan by adding a hint

Use global temporary tables to override SQL

Use pl/sql to rewrite SQL. For some specific queries, this method can be up to 20 times times the elevation. Encapsulate these SQL into a package that contains stored procedures to complete the query.

Use hints to adjust SQL

Most SQL tuning tools use more than hints. An SQL statement that prompts to add makes the SQL query accessible 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 result comparison before and after the adjustment. Use the new Opt_param hint to get

The same effect.

Select/*+ opt_param (' Optimizer_mode ', ' first_rows_10 ') * * col1, col2 ...

Select/*+ opt_param (' Optimizer_index_cost_adj ',) * * col1, col2. .

Oracle has released a large number of SQL prompts, and hints are constantly being enhanced and complicated by the different versions of Oracle.

Note: Tips are often used to debug SQL, the best way is to adjust the optimizer's statistics so that the CBO model automatically get the best execution path, equivalent to the use of the hint function.

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 the I/Tune table join order with histograms

Dynamic sampling:dynamic_sampling

Oracle SQL undocumented tuning Hints-guru ' s only

The cardinality hint

Table Join Order

When the order of table joins is optimized, we can use the ordered prompt to force the table to join in the order in which it appears in the FROM clause

First_rows_n Tips

Oracle has two tips based on cost optimization, one is First_rows_n, and the other is all_rows. First_rows mode will be returned to the customer as soon as the data is queried

Household end. While the all_rows pattern is designed to optimize resources, it needs to wait until all results have been completed before returning data to the client.

SELECT/*+ first_rows * *

4. Case

The same SQL statement has different wording. That is, a simple SQL query can produce the same result set in different ways, but its execution efficiency and access patterns vary widely.

The SQL statement in the following example uses 3 different ways to return the same result

A Standard join:--> standard Connection

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 subqueries

SELECT *

From STUDENT

WHERE

0 <

(SELECT count (*)

From registration

WHERE

Grade = ' A '

and

student_id = student.student_id

);

We should optimize the current SQL statements according to the basic SQL principles.

5, writing efficient SQL statement skills

Here are some general guidelines for writing efficient SQL statements, regardless of the optimization pattern chosen by the Oracle Optimizer. These are simple ways to look but follow their

To do so will receive a multiplier effect (already proven in practice).

A. Overriding a complex subquery with a temporary table

Oracle uses global temporary tables and the with operator to resolve complex SQL subqueries. In particular, the subquery in the WHERE clause, the SELECT phrase scalar quantum query,

The inline view of the FROM clause. Using temporary tables to implement SQL tuning (and materialized views using with) enables a stunning performance boost.

B. Using minus instead of exist subqueries

 the minus action instead of the not in or not exists will result in a more efficient execution plan (translator: This requires testing).

C. Using SQL analysis functions

Oracle analysis functions can extract data at once to do multidimensional aggregation operations (like Rollup,cube) to improve performance.

D. Overriding not EXISTS and queries as outer joins not EXISTS subqueries

In some cases the not query (where a column is defined as a null value) can overwrite this unrelated subquery to an external link to is NULL. The following example:

Select Book_key from Book

where

Book_key not in (select Book_key from Sales);

Here we use an outer join in the WHERE clause instead of 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. Indexed null value columns

If your SQL statements frequently use null values, you should consider creating an index based on a null value. For this query to be optimized, you can create an index function that uses a null value.

(Translator press, such as CREATE INDEX i_tb_col on tab (NVL (Col,null)); CREATE Index i_tb_col on tab (col,0);)

F. Avoiding index based operations

Do not perform any operations based on the indexed columns unless you create a corresponding index function. or reset the design column so that predicates on the WHERE clause column do not need to be converted.

--> below are all inefficient SQL notation.

where salary*5 >: myvalue

where substr (ssn,7,4) = "1234"

where To_char (Mydate,mon) = "January"

G. Avoiding the use of not in and having

Using NOT exists subqueries is more efficient when appropriate.

H. Avoid using the LIKE predicate

When appropriate, if you are able to use the = operation, avoid like operations as much as possible.

I. Avoid data type conversions

If a WHERE clause column is a numeric type, do not use quotation marks. For a character indexed column, quotation marks are always used. The following is a case of data type blending.

where CUST_NBR = "123"

where substr (ssn,7,4) = 1234

J. Use of Decode and case

Use the decode and case functions to minimize the number of query tables.

K. Don't be afraid of full table scans

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

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

L. Use of aliases

Table aliases are always used where the reference columns are.

--> Author:robinson Cheng