1411g新特性SQL 计划管理.docx
- 文档编号:6184197
- 上传时间:2023-01-04
- 格式:DOCX
- 页数:21
- 大小:81.51KB
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—一个设定了基线的计划,该计划被
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 1411g新特性SQL 计划管理 1411 特性 SQL 计划 管理
![提示](https://static.bdocx.com/images/bang_tan.gif)