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
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
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
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.
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.
The more rows you handle, the higher I/O will be, and the likelihood of consuming a large amount of temporary tablespace
The high Buffer gets may indicate that the resource is excessively centralized query, there is hot block phenomenon
High disk reads will cause excessive I/O
The allocation size of memory can identify whether the SQL statement uses a large number of table joins in memory
CPU overhead indicates which SQL statements consume a large amount of CPU resources
The more sorts, the worse the SQL performance and the large amount of temporary tablespace space
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
Sqlplus > @utlxplan
Sqlplus > Create public synonym plan_table for sys.plan_table;
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 '
SELECT ' T ' | | Plansnet.terr_code, ' P ' | | Detplan.pac1
|| Detplan.pac2 | | DETPLAN.PAC3, ' P1 ', sum (PLANSNET.YTD_D_LY_TM),
From Plansnet, Detplan
PLANSNET.MGC = DETPLAN.MKTGPM
.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.
Indicates the action that the current statement completes, typically including table access, table merge, sort, or index operation
Supplemental description operation, like full table, range table, join
The name of the query component
The ID number of the query component
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,
START with Id=0
statement_id = ' RUN1 '
CONNECT by Prior ID = parent_id
statement_id = ' RUN1 ';
The current statement execution plan information and the order in which each part is executed are given below.
OPTIONS object_name POSITION
GROUP by 1
NESTED LOOPS 1
TABLE ACCESS Full Plansnet 1
TABLE ACCESS by ROWID Detplan 2
INDEX RANGE SCAN detplan_index5 1
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.
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
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
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 * *
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
From STUDENT, registration
student.student_id = registration.student_id
Registration.grade = ' A ';
A nested query:--> nested query
Grade = ' A '
A correlated subquery:--> related subqueries
(SELECT count (*)
Grade = ' A '
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
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
B.book_key = S.book_key (+)
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