20120505--20120507 文档

警示自己：允许我仅仅是每天翻译一点文档，但是让我了解了更多的正确的信息。值得！！

 

11.1 Introduction to SQL Tuning

An important facet of database system performance tuning is the tuning of SQL statements. SQL tuning involves three basic steps:

·         Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system.

·         Verifying that the execution plans produced by the query optimizer for these statements perform reasonably.

·         Implementing corrective actions to generate better execution plans for poorly performing SQL statements.

These three steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned.

 

SQL调优是数据库调优一个很重要的部分。（自我解析：硬件调优》SQL调优》实例级调优 ||     SQL调优这个过程更能体现DBA的价值。）

 

SQL调优3个基本步骤：

1、  高负载或TOP SQL 占用了很大工作量和系统资源，根据系统回顾SQL执行的历史信息找出它们。

2、  根据查询优化器验证执行计划对报表查询是否合理。

3、  实施修正非执行SQL语句，SQL语句走好的执行计划。

 

 

（自我解析：

1、  通过ORACLE history ，找到高负载的SQL和 TOP SQL。

2、  通过优化器查看 SQL的执行计划是否是最优  CBO  cost最低。

3、  通过各种优化手段使SQL走最优的执行计划。）

 

 

 

11.2 Goals for Tuning

The objective of tuning a system is either to reduce the response time for end users of the system, or to reduce the resources used to process the same work. You can accomplish both of these objectives in several ways:

·         Reduce the Workload

·         Balance the Workload

·         Parallelize the Workload

 

 

1、  减少工作量

2、  平衡工作量

3、  并行化工作量

 

（自我解析：

当我看到这几个标题的时候很多遗憾：

1、  减少工作量？

 这个应该从应用程序上减少对数据库的操作。能应用做到的尽量不去用数据库，把数据库只当做数据储存站就可以了。不要让数据库当成服务员。

2、平衡工作量？

 工作量平衡化，减少多个程序同一时间进行操作。

3、并行化工作量？

 充分的利用你的CPU ，可以让其并行化操作。速度也很快哦。）

 

11.2.1 Reduce the Workload

SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.

Two examples of how resource usage can be reduced are:

·         If a commonly executed query needs to access a smallpercentage of data in the table, then it can be executed more efficiently by using an index. By creating such an index, you reduce the amount of resources used.

·         If a user is looking at the first twenty rows of the 10,000 rows returned in a specific sort order, and if the query (and sort order) can be satisfied by an index, then the user does not need to access and sort the 10,000 rows to see the first 20 rows.

SQL调优通常包括更高效的方式来处理相同的工作。修改执行计划是可以得到相同的功能而减少资源的。

 

怎么减少使用资源的2个例子；

1、  如果执行一个普遍的查询需要访问表上的一个很小的百分比的数据，这时候建立索引可以使他更有效的执行。通过建立这样的索引，你可以减少资源数量的使用。（建立索引减少资源的使用）

2、  如果一个用户正在看前20行10000排在一个特定的排序回来，如果通过一个索引查询(排序)都能满足，那么用户就不需要查阅和整理10000行，可以直接看到第一个20行。（建立索引更快的找到所需的数据）

 

11.2.2 Balance the Workload

Systems often tend to have peak usage in the daytime when real users are connected to the system, and low usage in the nighttime. If noncritical reports and batch jobs can be scheduled to run in the nighttime and their concurrency during day time reduced, then it frees up resources for the more critical programs in the day.

系统经常白天是用户真正连接的高峰期，晚上比较低使用。如果非紧急的报告和批量工作跑在夜间，白天的并发就减少。然后释放资源执行更重要的项目在白天。（合理的分配计划任务，和资源。让数据库更平均的运行着。）

11.2.3 Parallelize the Workload

Queries that access large amounts of data (typical data warehouse queries) often can be parallelized. This is extremely useful for reducing the response time in low concurrency data warehouse. However, for OLTP environments, which tend to be high concurrency, this can adversely impact other users by increasing the overall resource usage of the program.

查询大量的数据存取(典型的数据仓库的查询)通常可以并行，低并发数据仓库为减少响应时间这是非常有用的。然而，OLTP环境，通常会并发高,这可能负面影响其他用户通过增加整体资源使用的程序。（特别是单CPU不建议使用并行，效果并不是很好）

 

 

11.3 Identifying High-Load SQL

This section describes the steps involved in identifying and gathering data on high-load SQL statements. High-load SQL are poorly-performing, resource-intensive SQL statements that impact the performance of the Oracle database. High-load SQL statements can be identified by:

·         Automatic Database Diagnostic Monitor

·         Automatic Workload Repository

·         V$SQLview

·         Custom Workload

·         SQL Trace

识别高负载SQL：本节描述识别和收集高负荷的SQL语句步骤。高负载SQL是低性能的，资源密集型的SQL语句（争用）影响Oracle数据库的性能。高负载SQL语句可以通过下面识别：

1、  自动数据库诊断监视器        （ADDM）

2、  自动工作量库                      （AWR）

3、  V$SQL 视图                       

4、  定制工作量

5、  SQL跟踪                            

 

（自我解析：这里提供的方法。我仅仅对 1 2 比较了解，3 视图知道但是，不是很确定，4 这个不理解，5 跟踪SQL 不是很了解）

总结：V$SQL 视图总结，SQL跟踪，Custom workload 下一步学习目标。

 

11.3.1 Identifying Resource-Intensive SQL

The first step in identifying resource-intensive SQL is to categorize the problem you are attempting to fix:

·         Is the problem specific to a single program (or small number of programs)?

·         Is the problem generic over the application?

识别占用大量资源的SQL；

第一步识别占用大量资源的SQL分类问题，尝试解决。

1、  问题具体到一个单一的程序或一些小量的计划？

2、  问题是一般性的应用吗？