1411g新特性SQL 计划管理.docx
《1411g新特性SQL 计划管理.docx》由会员分享，可在线阅读，更多相关《1411g新特性SQL 计划管理.docx（21页珍藏版）》请在冰豆网上搜索。
1411g新特性SQL计划管理
OracleDatabase11g：
面向DBA和开发人员的重要特性
作者：
ArupNanda
自适应游标与SQL计划管理
使用每次都智能选择正确计划的绑定变量，并确保新的执行计划在使用前已经过完善。
到目前为止，很多人都已了解大量使用绑定变量来提高性能的方法；对于尚不清楚这种方法的用户，我将尽量以最简单的方式介绍其核心概念。
（同时，我还建议您访问TomKyte的。
在那里，您将了解使用绑定变量改善SQL语句性能的重要性，以及如何在几种语言中使用这些变量。
）
假定您的CUSTOMERS表有一个名为STATE_CODE的列，该列使用美国州名两位字母的缩写存储客户所在州的信息，如CT、NY等等。
如果希望找出来自康涅狄格州（'CT'）且购买次数达三次以上的客户的数量，您最可能执行以下查询：
selectcount
（1）
fromcustomers
wherestate_code='CT'
andtimes_purchased>3;
当您执行上述查询时，Oracle需要执行分析活动，为您执行的SQL语句生成执行计划。
分析过后，即可执行查询。
从概念上讲，分析与软件中的代码编译类似；如果您使用C++编写代码，则不能在操作系统中运行这些代码－首先，您必须对这些代码进行编译，使其成为可执行文件。
分析活动从SQL语句中生成可执行文件。
现在，假设另一位用户执行了如下所示的语句：
selectcount
（1）
fromcustomers
wherestate_code='NY'
andtimes_purchased>3;
该语句几乎与上述查询完全相同，除了一点：
搜索的state_code为NY而非CT。
理想情况下，分析过的代码与前一查询相同，并且将在运行时应用文字值。
但Oracle将查询的编写方式解释为不同的方式，因此必须再次对第二个查询进行分析。
假设查询按以下方式编写：
selectcount
（1）
fromcustomers
wherestate_code=
andtimes_purchased>3;
第一个查询和第二个查询将分别传递CT和NY作为的值。
此时，不必重新对查询进行分析。
在此示例中，在概念上被称为绑定变量，该变量是将在执行期间传递的值的占位符。
绑定变量以:
VariableName这种形式表示，如下所示：
wherestate_code=:
state_code
如果您的代码中不包含绑定变量，而是使用wherestate_code='CT'等对文字值的引用，您可以通过指定一个初始化参数将所有文字强制转换成绑定变量：
cursor_sharing=force
该参数将导致语句wherestate_code='CT'被重新编写为wherestate_code=":
SYS_0001"，其中SYS_0001是系统生成的变量名。
此方法将使这些语句变成相同的语句。
绑定变量的问题
既然绑定变量如此有效，我们为什么不一直使用这种变量呢？
我们不是拥有一种灵丹妙药—cursor_sharing—可以将所有糟糕的代码转换成可共享的语句吗？
（那些已经熟悉其中理由，尤其是熟悉绑定观察概念的读者可以直接跳至“自适应游标”一节。
）
假设STATE_CODE列有一个索引。
该列中的值如下所示：
selectstate_code,count
（1）
fromcustomers
groupbystate_code;
STCOUNT
（1）
------------
NY994901
CT5099
如您所见，数据出现了严重的偏差；大约5%的行中含有'CT'，而其余的行中含有'NY'。
考虑到各州的人口数量，得到这种结果不足为奇。
现在，让我们看一看为之前显示的查询生成了哪种类型的执行计划：
SQL>setautottraceonlyexplain
SQL>select*fromcustomerswherestate_code='NY'andtimes_purchased>3
2/
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
2008213504
-------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-------------------------------------------------------------------------------
|0|SELECTSTATEMENT||895K|26M|1532（9）|00:
00:
19|
|*1|TABLEACCESSFULL|CUSTOMERS|895K|26M|1532（9）|00:
00:
19|
-------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
1-filter（"TIMES_PURCHASED">3AND"STATE_CODE"='NY'）
该查询执行了一次全表扫描－由于该查询返回95％的行，且索引扫描将非常昂贵，因此这是一次正确的操作。
现在，使用'CT'执行相同的查询：
SQL>c/NY/CT
1*select*fromcustomerswherestate_code='CT'andtimes_purchased>3
SQL>/
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
4876992
---------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
---------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||4589|138K|56
（2）|00:
00:
01|
|*1|TABLEACCESSBYINDEXROWID|CUSTOMERS|4589|138K|56
（2）|00:
00:
01|
|*2|INDEXRANGESCAN|IN_CUST_STATE|5099||12（0）|00:
00:
01|
---------------------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
1-filter（"TIMES_PURCHASED">3）
2-access（"STATE_CODE"='CT'）
它使用了索引。
同样，这也是正确的操作。
含有CT的行数仅占总行数的5%，因此进行索引扫描是有利的。
让我们看一看使用绑定变量时的行为。
以下是OracleDatabase10g中的演示行为。
SQL>varstate_codevarchar2
（2）
SQL>exec:
state_code:
='CT'
PL/SQLproceduresuccessfullycompleted.
SQL>selectmax（times_purchased）fromcustomerswherestate_code=:
state_code
2/
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
296924608
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|6|1511（8）|00:
00:
19|
|1|SORTAGGREGATE||1|6|||
|*2|TABLEACCESSFULL|CUSTOMERS|500K|2929K|1511（8）|00:
00:
19|
--------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-filter（"STATE_CODE"=:
STATE_CODE）
优化器选择对CUSTOMERS表进行全表扫描。
当我们仅搜索CT（其数量只占记录总数的5％）时，难道不应该使用索引吗？
是什么原因使优化器选择全表扫描而非索引扫描呢？
答案是一种称为绑定观察的现象。
先前，当您使用设置为'NY'的绑定变量值运行该查询时，优化器必须为查询的第一次运行进行艰难的分析。
在这样做的同时，优化器观察绑定变量来确定为其分配的值。
该值是'NY'。
由于'NY'的数量大约占总行数的95%，优化器选择了全表扫描（与预期的情况相同）。
另外，它还冻结了查询的计划。
接下来，当我们使用设置为'CT'的变量值运行同一个查询时，优化器不会重新计算计划，而是使用了与之前相同的计划，即使该计划不是满足目标的最佳方案。
如果您在查询中使用了文字值'CT'而非绑定变量，那么优化器会选择正确的计划。
因此，如您所见，尽管绑定变量在大多数情况下都非常有效，当值的选择性将显著影响计划时（正如在此示例中，值'CT'和'NY'的选择性分别为5％和95％），绑定变量并不可靠。
如果数据分布均匀，所有值的选择性几乎相同，执行计划将保持不变。
因此，聪明的SQL编码人员将选择在何时打破使用绑定变量的基本准则，改用文字值。
自适应游标
但如果您没有很多聪明的编程人员，或者没有时间重新编写这些语句，该怎么办？
Oracle是否提供了一些智能的替代方案？
是的。
使用OracleDatabase11g，游标突然拥有了一种新的智能。
不是在执行查询的时候盲目使用已缓存的执行计划，而是在绑定变量的值更改时，根据实际情况确定是否需要重新计算计划。
如果游标中含有绑定变量，数据库会对其进行观察，确定传递给变量的值的类型以及是否需要重新计算计划。
如果需要重新计算计划，则游标标记为“绑定敏感”。
之前显示的示例查询可以很好地表现这一点。
数据库将基于绑定变量的值使用正确的优化器方案。
您不需要执行任何操作；上述操作将自动执行。
字典视图V$SQL已经修改，添加了两列：
IS_BIND_SENSITIVE和IS_BIND_AWARE。
让我们看一看它们的使用方法：
selectis_bind_sensitive,is_bind_aware,sql_id,child_number
fromv$sql
wheresql_text='selectcount
（1）fromcustomerswherestate_code=:
state_codeandtimes_purchased>3'
IISQL_IDCHILD_NUMBER
---------------------------
YY7cv5271zx2ttg0
YN7cv5271zx2ttg1
让我们看一看这些列的含义。
Oracle对游标进行观察，并确定值变化的方式。
如果不同的值可能会改变计划，则游标标记为“绑定敏感”，IS_BIND_SENSITIVE列显示“Y”。
在几次执行后，数据库对游标和值有了更多了解，并确定游标是否应根据值的变化来改变计划。
如果情况如此，则游标被称为“绑定感知”，IS_BIND_AWARE列显示“Y”。
总结：
绑定敏感游标是可能会更改计划的游标，而绑定感知游标是实际更改计划的游标。
一个新视图V$SQL_CS_HISTOGRAM显示了SQL语句执行的次数，为每个子游标划分了三个存储区，如下所示：
select*fromv$sql_cs_histogram
wheresql_id='7cv5271zx2ttg'
/
ADDRESSHASH_VALUESQL_IDCHILD_NUMBERBUCKET_IDCOUNT
---------------------------------------------------------------
45C8218C21444298717cv5271zx2ttg500
45C8218C21444298717cv5271zx2ttg512
45C8218C21444298717cv5271zx2ttg520
45C8218C21444298717cv5271zx2ttg408
...andsoon...
45C8218C21444298717cv5271zx2ttg020
由于自适应游标共享特性根据绑定变量的值使用正确的计划，数据库必须在某处存储这些信息。
它通过另一个新视图V$SQL_CS_SELECTIVITY显示这些信息，该视图显示传递给绑定变量的不同值的选择性。
select*fromv$sql_cs_selectivity
wheresql_id='7cv5271zx2ttg'
/
ADDRESSHASH_VALUESQL_IDCHILD_NUMBERPREDICATERLOWHIGH
------------------------------------------------------------------------
45C8218C21444298717cv5271zx2ttg5=STATE_CODE00.8954101.094391
45C8218C21444298717cv5271zx2ttg4=STATE_CODE00.0045890.005609
45C8218C21444298717cv5271zx2ttg4=STATE_CODE10.0022950.002804
45C8218C21444298717cv5271zx2ttg3=STATE_CODE00.0022950.002804
45C8218C21444298717cv5271zx2ttg0=STATE_CODE00.0045890.005609
该视图显示了大量信息。
PREDICATE列显示了用户使用的各种谓词（WHERE条件）。
LOW和HIGH值显示传递的值的范围。
最后，第三个新视图V$SQL_CS_STATISTICS显示了标记为绑定感知或绑定敏感的游标执行的操作。
selectchild_number,
bind_set_hash_value,
peeked,
executions,
rows_processed,
buffer_gets,
cpu_time
fromv$sql_cs_statistics
wheresql_id='7cv5271zx2ttg';
CHILD_NUMBERBIND_SET_HASH_VALUEPEXECUTIONSROWS_PROCESSEDBUFFER_GETSCPU_TIME
-----------------------------------------------------------------------------
122981142Y1959232190
022981142Y1959232810
该视图显示了数据库记录的有关执行的统计数据。
EXECUTIONS列显示了使用绑定变量的不同的值执行查询的次数。
输出中的PEEKED列（显示为“P”）显示优化器是否通过观察绑定变量获得适当的方案。
这些视图显示了一些额外信息，您不需要通过这些信息了解此特性的工作方式。
数据库自动激活和使用自适应游标。
SQL计划管理
您看到过多少次下面的情况：
一个查询拥有可能的最佳计划，但一些事情突然发生，导致该计划被抛弃。
这些事情可能是某人重新对表进行了分析，或者star_transformation等影响优化器的参数被改变—可能性是无穷无尽的。
由于受挫，您可能会禁止对数据库进行任何更改，这意味着不收集数据库统计数据、不更改任何参数等等。
但这说起来容易做起来难。
当数据模式改变时会发生什么？
以自适应游标一节中显示的示例为例。
现在，CUSTOMERS表中填充了来自纽约的客户，因此STATE_CODE大部分为“NY”。
所以，当执行含有如下所示谓词的查询时：
wherestate_code='NY'
系统执行一次全表扫描而非索引扫描。
当谓词为：
wherestate_code='CT'
...
由于将仅返回几行结果，系统使用索引。
然而，如果模式发生改变－假设，突然出现大量来自康涅狄格（state_code='CT'）的客户，导致含有CT的结果的百分比升至70％，此时会发生什么？
在该情况下，CT查询应使用全表扫描。
但是，由于您已禁止收集优化器统计数据，优化器不会了解模式的更改，并且会继续提供无效率的索引扫描路径。
您可以做些什么？
如果Oracle使用最优计划，但在统计数据收集或数据库参数等底层因素更改时重新评估该计划，此时，当且仅当新计划更有效时数据库才会使用，结果如何？
该方案非常理想，不是吗？
它在OracleDatabase11g中已成为可能。
让我们看一看这种方案的实现方式。
SQL计划基线设定
在OracleDatabase11g中，当一个已经计算好的优化器计划由于底层因素的更改而需要更新时，新计划不会立即实施。
Oracle会对这个新计划进行评估。
仅当它比原有计划更有效时，Oracle才会实施新计划。
此外，还可以使用工具和接口来查看为每个查询计算的计划的历史，以及这些计划的对比情况。
当Oracle将一个语句确定为多次执行或“可重复的”语句，声明周期开始。
一旦确定了一个可重复语句，数据库即会捕获它的计划，并将该计划作为SQL计划基线存储在数据库一个称为SQL管理库（SMB）的逻辑结构中。
当出于任何原因为该查询计算新计划时，新计划也存储在SMB中。
因此，SMB用于存储查询的每个计划、计划的生成方式等等。
计划不会自动存储在SMB中。
如果上述情况属实，SMB将存储每类查询的所有计划，并将变得十分庞大。
因此，您可以并且应该控制SMB存储的查询的数量。
执行该操作有两种方法：
自动为SMB中的所有可重复查询设定基线，或手动加载应设定基线的查询
让我们先看一个简单的示例：
通过将数据库参数optimizer_capture_sql_plan_baselines（默认值为FALSE）的值设置为TRUE，您可以使SQL计划管理特性自动捕获所有可重复查询的SQL计划基线。
很幸运，这是一个动态参数。
SQL>altersystemoptimizer_capture_sql_plan_baselines=true;
该语句执行后，所有可重复语句的执行计划都作为SQL计划基线存储在SMB中。
SQL计划基线存储在名为DBA_SQL_PLAN_BASELINES的视图中。
您也可以在EnterpriseManager中看到这些内容。
要检查设定了基线的计划，请打开EM并单击“Server”选项卡，如下图所示：
在该页单击QueryOptimizer部分中的SQLPlanControl，这将打开下方显示的SPM主页面。
单击SQLPlanBaseline选项卡，这将打开如下所示的屏幕：
这是SQLPlanBaseline的主屏幕。
您将在屏幕左上角看到配置参数。
CaptureSQLPlanBaselines显示为TRUE，该值是您使用ALTERSYSTEM命令启用的。
该参数下方是设置为TRUE（默认值）的UseSQLPlanBaselines。
它表示，如果存在SQL计划基线，则为查询使用该基线。
每次为查询生成一个新计划，原有计划就保留在SMB的历史记录中。
然而，这也意味着SMB中将挤满计划历史记录。
一个参数可以控制计划保留的星期数，它显示在PlanRetention（Weeks）的文本框中。
在这个屏幕中，该参数设置为53周。
如果一个SQL计划基线的未使用时间超过53周，该基线将被自动清除。
该屏幕的中间部分有一个搜索框，可用于搜索SQL语句。
在此处输入一个搜索字符串，然后按下Go，您将看到如上图中显示的SQL语句和相关计划。
每个设定了基线的计划都有大量与之相关的状态信息。
让我们看一看这些信息：
▪Enabled—一个设定了基线、必须启用以便加以考虑的计划
▪Accepted—一个设定了基线的计划，该计划被
升级会员 