第十五章 using sql plan management

如何使用sql plan管理执行计划。

一、sql plan baselines
sql plan magagement记录并评估sql语句的执行计划。这种机制能够创建一个sql plan baseline，即一系列可接受的sql计划。

1. sql plan baselines的目的
保持sql语句的性能，不管数据库发生怎样的变化，如：
新的优化器版本
优化其统计信息变化或参数变化
用户或元数据定义的变化
系统设置的变化
sql profile的创建

当发生不可挽回的执行计划修改时，sql plan baselines也是没有效果的。如索引删除

oracle的sql tuning features生成sql profiles来帮助优化器产生好的执行计划。然而这种机制是相对有效的，不能在数据库剧烈变化时保证性能稳定。
sql tuning只能在问题发生并被定为后才能解决问题。例如，一条sql由于计划改变而性能很差，但sql tuning只有在计划改变以后才能解决这个问题。

2.sql plan baselines的结构
sql plan基线包含一或多个可接受的计划，每个有如下信息：
hints设置
计划的hash value
计划相关信息

将计划加入到sql plan基线的过程叫做plan evolution。

Figure 15–1 SQL Plan Baseline and SQL Plan History
The SQL management base (SMB), which is part of the data dictionary, stores the SQL plan baselines and plan history in the SYSAUX tablespace. The SMB also contains

SQL profiles. The SMB uses automatic space management.

385页

二、sql plan baselines的管理
1.捕获sql plan baselines
在sql plan baseline捕获阶段，数据库检测到计划的修改并记录新的计划，以便这些信息可以被dba得到。
为了达到此目的，数据库为每个sql语句存储历史计划。
由于临时性的sql不重复执行，不会使数据库性能得到大的影响，因此数据库不记录这些语句的历史信息。

为了识别语句是否重复执行，数据库对优化器评估后的语句记录包含sql id的日志。当该语句或记录日志后重复解析、执行是判断是否记录了日志。

如果要开启自动计划捕获，可以将optimizer_capture_sql_plan_baselines设置为true。默认是false

计划基线（plan baselines）的创建：
（1）从sql tuning sets和awr快照中加载
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
END;
/
如果是awr报告，需要先将计划存到sql tuning set中，然后再用以上方法。

（2）从shared sqlarea中加载
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');
END;
/
可以通过sql_id、sql_text或PARSING_SCHEMA_NAME、MODULE、ACTION定位shared sql area中的plan

sql plan baselines的选择：
oracle在解析时给予存储的历史选择baseline，选择plans来避免可能的性能问题。
每次编译时需要如下操作：
用基于成本的查询方法创建成本最优的计划
尝试在sql plan baseline中找到匹配的计划
如果找到了，使用匹配的计划；如果没找到，优化器评估基线中的每个可接受的执行计划，选出成本最低的计划。

优化器找到的成本最优计划如果不能和历史计划匹配时，产生一个新的计划。
数据库将这个计划存为不可接受的计划，知道确认不会产生性能问题才会使用该计划。
但是如果系统变化（如索引删除）导致所有的可接受计划都不会产生时，优化器选择最优成本计划。
optimizer_use_sql_plan_baselines=true设置启用sql plan baselines

2.sql plan baselines的转化
通过手工plan加载：
将shared sql area中的计划手工加载为sql plan baseline，这时oracle认为加载的是可接受的计划

用dbms_spm.evolve_Sql_plan_baseline转化
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_593bc74fca8e6738');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

可选的参数：
The name of a particular plan to evolve
A list of plans to evolve
No value（By specifying no value, you enable Oracle Database to evolve all nonaccepted plans currently in the SMB.）

三、using sql plan baselines with sql tuning advisor
When tuning SQL statements with SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the

corresponding SQL plan baseline, then it makes a recommendation to accept a SQL profile
However, SQL Tuning Advisor does not verify existing unaccepted plans in the plan history.

四、using fixed sql plan baselines
A SQL plan baseline is fixed when it contains at least one enabled plan whose FIXED attribute is set to YES.

五、displaying sql plan baselines

SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_209d10fabbedc741',
format=>'basic'));

从视图中查看：
SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES;

六、sql management base（SMB）
是sysaux表空间中数据字典的一部分。存储语句日志、计划历史、计划基线和sql profiles

To allow weekly purging of unused plans and logs, the SMB uses automatic space management.

一般来说SMB占sysaux的10%以下，允许范围是1%~50%
超过指定限制时，将会向alert log日志信息，数据库每周向alert log写入告警信息，直到满足以下条件之一
SMB空间限制增加了
sysaux表空间大小增加了
SMB使用的空间通过清理sal management objects而减少了

修改站用表空间比例：
BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/

清理监测（purging policy）
维护窗口每周会自动运行清理作业。
数据库清空53周内没有再使用的语句，通过SMB中的last_Executed时间戳

可以用以下方法修改保留周期：
exec DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);

查看当前配置：
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_SQL_MANAGEMENT_CONFIG;

    PARAMETER_NAME PARAMETER_VALUE
1 SPACE_BUDGET_PERCENT 10
2 PLAN_RETENTION_WEEKS 53
分别表示SMB占sysaux的限制为10%
sql在SMB中保留53周

七、sql plan baselines的导入导出

1.On the original database, create a staging table using the CREATE_STGTAB_BASELINE procedure.
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'stage1');
END;
/

2.Pack the SQL plan baselines you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function.
The following example packs enabled plan baselines created by user dba1 into staging table stage1. You can select SQL plan baselines using the plan name

(plan_name), SQL handle (sql_handle), or any other plan criteria. The table_name parameter is mandatory.

DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
creator => 'dba1');
END;
/

3.Export the staging table stage1 into a flat file using the Oracle Data Pump Export utility.

4.Transfer the flat file to the target system.

5.Import the staging table stage1 from the flat file using the Oracle Data Pump Import utility.

6.Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function.
The following example unpacks all fixed plan baselines stored in the staging table stage1:

DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'stage1',
fixed => 'yes');
END;
/

八、migrating stored outlines to sql plan baselines

stored outline是一系列的sql语句的hint。可以是语句的执行计划更稳定。
stored outline migration就是将stored outline转化成sql plan baselines

步骤略

第十六章  sql tuning overview

一、sql tuning简介
步骤：
通过查看sql执行历史信息，定位高负载的或称为top sql的语句
找到查询优化器为这些语句生成的执行计划
为性能差的sql生成更好的执行计划

二、调优的目标
减少最终相应时间或减少完成相同任务耗费的资源
通过三方面完成此目标：
减少负载
平衡负载
并行化负载

1.减少负载
例如在经常查询表中少量数据时，在对应列上建索引；利用索引的排序功能，只返回大量数据中的一部分

2.平衡负载
如将不紧急的事务放到晚上

3.并行化负载
数据仓库系统中查询大量数据时可以通过并行操作减少响应时间。但是oltp系统中由于存在多用户，并行可能会造成资源争用

三、定位高负载的sql
高负载的sql指性能很差、消耗大量资源的语句
可以通过如下方法定位：
ADDM
automatic sql tuning
AWR
V$SQL
custom workload
sql trace

1.定位消耗资源多的sql
是否是单个的或少数的几个程序？
问题是发生在应用上吗？

可以通过OEM定位耗费资源多的语句，如果是动态sql等不能定位的，可以使用sql trace生成trace 文件，再用tkprof生成结果文件。

2.收集sql_id的数据
如果比较关注cpu，应该查看一段时间内的buffer_gets
需要收集的信息包括：
v$sqltext中完整的sql_text
表结构（desc）
索引的定义，是否唯一索引
表段的统计信息，包括行数、索引列的可选择性等
sql中视图的定义
执行计划
以前的执行计划

通过比较可以更快地找到问题症结

四、自动调优特性
包括addm、sql tuning advisor、sql tuning sets、sql access advisor

oracle自动定位问题sql，并用sql tuning advisor推荐的方法提高性能，这个操作在维护窗口作为一个自动执行的任务执行。
也可以通过手工运行sql tuning advisor提高性能。

When multiple SQL statements serve as input to ADDM, SQL Tuning Advisor, or SQL Access Advisor, the database constructs and stores a SQL tuning set (STS). The STS includes the set of SQL statements along with their associated execution context and basic execution statistics.

In addition to SQL Tuning Advisor, SQL Access Advisor provides advice on materialized views, indexes, and materialized view logs.
通过推荐设置物化视图、物化视图日志和索引来提高性能。sql access advisor可以权衡空间和性能，给出成本最小的建议。

五、开发高效的sql
1.校验统计信息
收集统计信息时最好对全表进行收集，由其是对sql中的联合查询中的表
及时收集统计信息，如果数据倾斜，考虑使用柱状图

2.查看执行计划
otlp环境中调优，用选择性好的条件作为驱动。确保给下一步操作的是最少的数据。

查看执行计划是注意以下信息：
驱动表的过滤作用最好
每次连接操作都返回最少的数据
连接方法与行数相匹配，如通过索引进行nested loop joins可能在语句中返回大量数据时并非最优
合理使用了视图，查看select列表，是否有必要使用视图
没有隐藏的笛卡尔积连接
每个表的访问方式是否高效

如果没满足以上条件，可以通过重构sql、重建索引等方式解决

3.重构sql
通常来说，重写比修改容易，前提是了解sql的含义

在可能的时候用and和=谓词
避免在where子句中对列进行转化
避免nvl（：b，col1）、nvl（col，999）、to_date()、to_number()等函数
这些操作阻止了对cardinality和可选择性的评估，影响整体的执行计划和连接方法。
尽量不要在索引列的条件上加函数，而是在其对立的条件上。

为不同的任务分别写sql
用一条sql做很多不同的事通常会造成效率的下降

4.利用hint控制访问路径和连接方式
通常不建议在复杂视图上进行连接。
如果需要使用视图中的部分列与其他表关联，首先确认视图中的表是否都用到了，如果没有，就考虑用基表或新建一个视图。

考虑使用中间表存储中间数据
如果重复使用这些数据，再考虑

5.重构索引
删除用不到的索引，提高dml效率
重建以优化索引结构
为索引增加列来提高可选择性

6.修改或禁用触发器、约束
使用触发器是消耗系统资源的，用的太多会影响性能

7.重构数据
重新审查数据的设计是否合理
考虑是否需要分区

8.保存执行计划
可以通过存储统计信息或sql plan baselines两种方式

9.减少数据查询次数
使用case：
SELECT COUNT (*)
FROM employees
WHERE salary < 2000;
SELECT COUNT (*)
FROM employees
WHERE salary BETWEEN 2000 AND 4000;
SELECT COUNT (*)
FROM employees
WHERE salary>4000;
改为：
SELECT COUNT (CASE WHEN salary < 2000
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN salary BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN salary > 4000
THEN 1 ELSE null END) count3
FROM employees;

使用带returning子句的dml（减少数据调用次数）

使用array方式一次选出多条数据

六、创建sql test cases
11.2版本以后，oracle提供了sql test case builder，用于解决sql相关的问题。

The information gathered by SQL Test Case Builder includes the query being executed, table and index definitions (but not the actual data), PL/SQL functions, procedures, and packages, optimizer statistics, and initialization parameter settings.

可以通过OEM或用dbms_sqldiag包创建test case

使用dbms_sqldiag