Oracle Enqueue Waits

队列用来有序地锁定关系与非关系型的Oracle结构。关系型结构可能是Oracle的数据字典表或应用程序表。例如,当Oracle更新sys.col$表或一个应用程序更新它的employee表,队列将会被调用。如果一个服务器进程被锁定的表所阻止,不仅仅会post一个enqueue wait等待事件,还会在v$lock,dba_lock,v$enqueue_statistics与其它视图中显示锁信息。非关系型结构被锁定是为了阻止不合适的更改比如library cache cursor。

顾名思义,队列是非常有序的,并确保以非常确定的方式更改结构。进程的入队列请求会被推送到适当的队列上,当它需要处理时,它的入队列条目会从队列中弹出(也叫作dequeue)。这里并没有什么令人兴奋的地方,但是排队不是为了冒险,而是为了确保以一种非常有序的、类似会计的方式更改Oracle结构。

Oracle维护了数量惊人的队列。在Oracle 10gr2中有208种队列,在Oracle 11gr1中有247种队列。但不必惊慌,因为你可能只会遇到几个排队的人。另外,如果您是一位经验丰富的DBA,您已经处理过使用enqueue的行级和表级锁。

诊断Enqueue等待
当解决队列问题时,首先判断队列类型,然后确定所涉及的SQL,最后根据您对应用程序和相关Oracle内部的知识开发解决方案。在深入研究最常见的排队等待(事务(TX)排队)之前,务必了解如何确定正在等待哪个排队和oracle 10g之前和之后版本中的相关会话。

在Oracle 10g之前,所有队列的等待事件都是enqueue。这确实很不幸,因为这要从v$lock或v$session_wait中取样来确定队列名称。下面的SQL语句用来从v$session_wait中来确实enqueue名。会话4388已经锁表,没有等待锁,因此没有显示。队列中的第一个会话是4387,紧接着是会话4393。判断正在运行的SQL与所涉及的表最简单的方式就是从v$session中查询会话的sql_address或sql_hash_values。对于TM队列,表可以通过p2列(ID 1列)来识别。它包含object_id,可以使用它来从dba_objects中进行查询。这使得确定争用对象非常简单。

SQL> col sid format 9999 heading "Sid"
SQL> col enq format a4 heading "Enq."
SQL> col edes format a30 heading "Enqueue Name"
SQL> col md format a10 heading "Lock Mode" trunc
SQL> col p2 format 9999999 heading "ID 1"
SQL> col p3 format 9999999 heading "ID 2"
SQL> select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) enq,
  4         decode(chr(bitand(p1, -16777216) / 16777215) ||
  5                chr(bitand(p1, 16711680) / 65535),
  6                'TX',
  7                'Row related lock (row lock or ITL)',
  8                'TM',
  9                'Table related lock',
 10                'TS',
 11                'Tablespace and Temp Seg related lock',
 12                'TT',
 13                'Temporary Table',
 14                'ST',
 15                'Space Mgt (e.g., uet$, fet$)',
 16                'UL',
 17                'User Defined',
 18                chr(bitand(p1, -16777216) / 16777215) ||
 19                chr(bitand(p1, 16711680) / 65535)) edes,
 20         decode(bitand(p1, 65535),
 21                1,
 22                'Null',
 23                2,
 24                'Sub-Share',
 25                3,
 26                'Sub-Exlusive',
 27                4,
 28                'Share',
 29                5,
 30                'Share/Sub-Exclusive',
 31                6,
 32                'Exclusive',
 33                'Other') md,
 34         p2,
 35         p3
 36    from v$session_wait
 37   where event = 'enqueue'
 38     and state = 'WAITING'
 39  /
SQL>
Sid   Enq. Enqueue Name                   Lock Mode  ID 1     ID 2
----- ---- ------------------------------ ---------- -------- --------
4387  TM   Table related lock             Exclusive     49911        0
4393  TM   Table related lock             Sub-Exlusi    49911        0



SQL> @swswp enq%
Database: prod16 31-MAR-10 04:32pm
Report: swswp.sql OSM by OraPub, Inc. Page 1
Session Wait Real Time w/Parameters
Sess
ID    Wait Event                   P1           P2        P3
----- ---------------------------- ------------ --------- -----
4383  enq: TM – contention         1414332422   49911     0
4388  enq: TM – contention         1414332422   49911     0
2 rows selected.
SQL> l
1 select sid, event,
2 p1, p2, p3
3 from v$session_wait
4 where event like '&input%'
5 and state = 'WAITING'
6* order by event,sid,p1,p2

与latch等待事件一样,从Oracle 10g开始,每一种队列都有它自己的等待事件。这节省了诊断步骤,因为我们可以通过一个简单的查询确定所涉及的会话和队列类型。会话4393已经持有表锁并且没有等待所以没有显示,会话4383和4388正等待锁表因此post一个TM队列等待。通过使用P2列(49911)来与dba_objects视图的object_id关联进行查询来获得被调用的表。

TX Enqueue等待
TX队列等待是最常见的队列等待事件。这也是最迷人的。想深入研究这个等待事件,因为它将使您更深入地了解Oracle如何管理事务并发性,这与块克隆、undo、读取一致性和相关事务列表有关。

TX队列也叫作行级锁队列,实际上出现TX队列有三个原因,并且只有一个实际上是行级锁。每一个Oracle数据块可以被抽象为三个区域:
.行数据包含真实的Oracle行记录并且是每个数据块最重要的一个部分。

.可变数据包含事务元数据

.可用空间数量可以通过行数据增长与可变数据增长而减小

相关事务列表(ITLs)
内置在每个Oracle数据块的可以数据区域的结构叫作相关事务列表(ITLs)。这些结构最主要是用来负责Oracle的行级锁与读一致性。从高度抽象的角度来看,可以认为ITLs就像检查框,每个检查框与一个特定的事务相关。如果想要更新行记录,但被锁定的行已经与其它事务的ITL关联,你将会收到一个TX队列等待,这确实是行级锁。

每个Oracle数据块都创建了特定数量的ITLs。ITLs的初始值是由表的initrans空间参数所控制的并且可以通过dba_tables视图的ini_trans列来查看。从Oracle 9i开始,缺省的ini_trans值为1,然而通过简单的块dump可以清楚的看到创建了两个ITL。使用两个ITLs,单个数据块可以同时并发地执行两个事务。

假设第三个事务想要修改块中没有被锁定的行而只在两个ITL存在时,第三个事务的服务器进程将尝试动态创建一个额外的ITL。然而服务器进程必须首先确保ITL的最大数(max_trans)不会被超过并且在数据块中要有可用空间。如果服务器进程不能创建额外的ITL,它将发出一个TX队列等待事件,并且这个进程将耐心等待。为了减小这种情况的出现,单个块的ITLs的缺省值与最大值都可以设置为255。当不超过这个值时可以执行alter table命令来修改。

一旦在数据块中创建了一个ITL后,唯一能获得空间的方式是重新创建整个表。修改空间参数将不会影响已经创建的ITL。这就是为什么缺省的ITLs为1(实际上创建了两个ITL)并且最大值设置为255的原因。如果数据块的并发请求更多的ITLs,Oracle宁愿消耗空间也不愿意发出TX队列等待事件而让事务等待。

初看,ITL的最大数是255可能看上去非常有限,但请考虑这种情况:想想在最高并发应用程序中,在最高并发的数据库中的最高并发表。也许有一个表可能有250个并发进程正在更新,删除与插入记录。现在真正有多少进程将会并发更新,删除或插入记录到一个数据块中,而不是整个表或区,是单个块。即使使用最高并发性的应用程序,在一个块中激活超过255个并发事务也是极不可能的。所以ITL的最大数255并没有太大的限制。然而如果确实出现了问题,可以通过增加表的pct_free参数来减小数据块的并发性或者为了减少存储在块中的行记录可以增加固定长度的列。

Unod段的事务表
每个undo段在它的头块中包含一个结构叫事务表。Oracle开发人员将事务表中的行称作slots(插槽)。每一个已经占用的slot都与正在或已经在undo段中存储undo信息的事务相关。如果一个事务已经提交或者回滚,它确实是一个非活动事务,否则它就是一个活动事务。除了包含slot号与事务状态,每个slot也包含一个序列号。为了区分不同的事务使用相同的slot并能让slot重用,序列号可以增长。UBA是undo块地址,提供到事务的undo的直接链接。SCN是当相关事务开始时事务的系统改变号。

事务表与性能分析人员相关因为它们提供了事务号。每个事务有一个相关的事务号,并且事务号是基于事务的事务表条目生成的。事务号由三组数字组成。第一部分是事务表号,第二部分是slot号,最后是相关序列号。例如,一个事务号为00100.000.00007。ITLs与事务表之间的联系是每个ITL条目关联到一个特定的事务并且在ITL条目中包含事务号,比如00100.000.00007。

深入了解相关事务列表(ITL)
已经了解了ITL与undo段事务表,现在是将它们作为单个工作单元组合在一起的时候了,并展示在事务活动期间ITLs是如何变化的。深入了解相关事务列表可以让你深入理解Oracle如何管理事务并发性,如何创建读一致性块以及为什么要小心“snapshot too old”错误。

下面通过执行命令alter system dump datafile 1 block 75847来dump数据块。在执行块dump时,这个块(1,75847)包含了许多行记录并且有三个活动事务更新四行不同的记录。第一个与第三个事务显示正在更新一行记录,第二个事务正在更新二行记录。

$ cat prod5_ora_21741.trc
...
Block header dump: 0x00412847
Object id on Block? Y
seg/obj: 0xff6b csc: 0x00.50fcb6 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x412848 ver: 0x01
Itl         Xid                 Uba         Flag  Lck      Scn/Fsc
0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c ----   1    fsc 0x0000.00000000
0x02 0x0008.01b.00000340 0x00c41bce.0481.24 ----   2    fsc 0x0000.00000000
0x03 0x0001.000.00000320 0x00c45fa0.0599.0b ----   1    fsc 0x0000.00000000
...

ITL条目包含以下内容:
itl:这是事务的ITL号

xid:这是事务ID,它由事务表ID(0003),事务表slot号(00d)与序列号(00000318)组成。事务ID是很重要的,因为它用于确保看起来相关的undo信息是真正相关的。

uba:这是undo块地址。这直接指向事务的最新更改undo,对于回滚事务和读取一致性(克隆缓冲区构造)都是必要的。

flag:事务的状态它可以有许多值,以下是常见值
***212;***211; 意味着事务是活动的,DML在执行事务没有提交或回滚

***211;U***211; 意味着事务已经提交,因此任何行数据都可以引用在活动事务中没有被使用的ITL并且它们没有被锁定。事务的行数据可能没有被合并。例如,如果一个列被更新,在更改之前与之前的值可能保留在行数据中。

***211;C***211; 意味着事务已经提交,行数据已经合并,并且行数据中的ITL条目已经被删除。任何块touch可能触发对这个flag的改变,包括select语句。我知道这很难相信。这种看似延迟的更改通常称为延迟块清除,或者简单地称为块清除。

Lck:这是事务在某个时刻锁定在这个块中的行数。大于0的值不能够说时行被锁定。如果这个值为2,就像第二个事务一样,这个事务关联两行记录。锁会保持到flag改变为C***212;-为止。这意味着在一个事务提交后且不再被认为是活动(***211;U***211;)状态时,Lck值可能大于0

Scn/Fsc:SCN是系统改变号并用来判断事务是何时结束的(提交或回滚)。上面的例子中SCN没有被指泒,但在事务提交后,SCN被设置了如下所示。当创建一个buffer的读一致性版本判断是否需要检索undo时SCN是很重要的。FSC引用可用空间信用。它用于未提交的事务当一个更新或删除操作造成行记录长度收缩使用。Oracle将保护这个空闲空间,以防事务回滚和需要重新填充空间。如果空闲空间用于其他用途,然后事务回滚,则可能需要迁移行!。在下面的dump结果中,前两个事务(ITLs x01与x02)已经提交标记它们的事务为非活动状态。第三个事务,ITL x03,还没有提交。在前两个事务提交后,相同的块dump命令,alter system dump datafile 1 block 75847。注意flag已经改变了,一个SCN已经指泒给事务了。

$ cat prod5_ora_21741.trc
...
Block header dump: 0x00412847
Object id on Block? Y
seg/obj: 0xff6b csc: 0x00.50fcb6 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x412848 ver: 0x01
Itl          Xid                Uba         Flag Lck      Scn/Fsc
0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c --U- 1   fsc 0x0000.0050fd6f
0x02 0x0008.01b.00000340 0x00c41bce.0481.24 --U- 2   fsc 0x0000.0050fd6b
0x03 0x0001.000.00000320 0x00c45fa0.0599.0b ---- 1   fsc 0x0000.00000000

两个flags ***212;-与***211;U-是必需的,因为活动事或者过去的活动事务中涉及的行可以在其行数据中具有有效的ITL条目。因为简单引用行数据与查看ITL条目不能说明行当前被活动事务调用与锁定。为了检查行是否被锁定,一个服务器进程必须从行数据中得到ITL引用然后检查数据块的可变ITL区域中的flag。如果flag为 ***212;-,那么服务器进程知道行确实被一个活动事务所调用且被锁定。然而如果falg为***211;U,服务器进程知道行没有被锁定。

块清除进程的部分工作将删除非活动事务行数据ITL条目,将它们各自的ITL条目在数据块的可变部分的flag的状态修改为C***212;,并合并行数据。

这是一种聪明的策略,因为Oracle可以快速使用最小的改变来记录数据块中的改变,但仍然在行级别维护并发控制。最终需要对块进行最后的更改,但这可能发生在工作负载较低的时期,比如基准测试完成之后。

执行查询语句来touch块1,75847后,再执行dump命令的结果如下,数据块(1,75847)在执行查询语句touch数据块后事务flags从***211;U-变为了C***212;,指示块清除已经发生了。

$ cat prod5_ora_21741.trc
...
Block header dump: 0x00412847
Object id on Block? Y
seg/obj: 0xff6b csc: 0x00.510047 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x412848 ver: 0x01
Itl          Xid                Uba         Flag Lck     Scn/Fsc
0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c C--- 0     scn 0x0000.0050fd6f
0x02 0x0008.01b.00000340 0x00c41bce.0481.24 C--- 0     scn 0x0000.0050fd6b
0x03 0x0001.000.00000320 0x00c45fa0.0599.0b ---- 1     fsc 0x0000.00000000
...

现在我意识到这很有趣,但是我也理解一些读者可能认为这个block dump和ITL的东西并没有那么强的关联。但我不敢苟同。您不仅对TX排队有了更全面的了解,而且还清楚地了解了如何排队Oracle实现了它的专利行级锁方案。

深入了解Buffer克隆
介绍块克隆是因为它与CBC latch竞争。现在将深入学习Oracle如何使用ITLs,undo块,SCNs与其它有趣的Oracle技术。当一个服务器进程要定位一个请求的buffer并且发现请求的行在查询开始后发生改变了,它必须为buffer创建一个时光倒流的镜像。这就叫作当前(CU)buffer的一致性读(CR)buffer。一旦buffer被拷贝,合适的undo被应用后,使被拷贝的buffer回退直到CR buffer被成功克隆好为止。

假设我们的查询执行时间是SCN 12330,查询最终得到要访问的buffer 7,678。然而,我们注意到存在一些ITL活动事务 7.3.8当前是活动状态并且buffer可能在我们查询开始后发生了改变。事务5.2.6是非活动状态(flag为C,并指派了SCN,并且Lck为0),但是改变的提交时间在我们查询开始之后并且影响这个当前(CU)buffer。这些块改变意味着在CU buffer在我们查询在SCN 12330时间点开始后已经发生改变了并且不能用于我们的查询。我们需要一个一致性读副本,它可以时光倒流回到SCN 12330时间点。因此CU buffer 7,678必须被克隆并应用undo,来创建一个SCN12330时间点的CR buffer。

在执行buffer克隆之前,必须找到一个不被频繁访问的free buffer然后使用7,678的CU buffer来替换它。服务器进程将获得LRU chain latch与相关的LRU chain,然后从LRU chain的LRU端开始扫描,查找不被频繁访问的free buffer。最终将找到一个不被频繁访问的free buffer并使用CU buffer 7,678的副本来替换它。当然CBC结构也将被更新来映射克隆buffer在buffer cache中的位置。

从与第一个ITL相关的活动事务7.3.8开始。服务器进程需要检索在我们查询开始时间scn 12330之后所有生成的undo记录。事务7.3.8的最近生成的undo可以通过它的ITL的undo块地址(UBA)所链接到的undo块2,45中找到。服务器进程然后必须访问undo块2,45。这需要请求CBC活动并且也可能请求LRU活动来执行IO调用。一旦访问到undo buffer 2,45,将会通过比较事务号来检查确保我们使用正确的事务在工作。数据块与undo块事务号需要匹配(7.3.8),因为事务是活动的,所以undo信息应该没有铺覆盖。

undo块2,45的SCN是12348,这意味着undo块代表的块改变出现在我们查询开始时间scn 12330之后,因此,我们需要对克隆的CR buffer应用undo数据,让它回退到过去一点点。

undo块2,45也链接到了另一个undo块2,90。这是一种undo链并且可能持续一段时间,消耗大量的计算资源。服务器进程现在必须访问undo块2,90(请求CBC活动并且也可能请求LRU活动来执行IO调用)并且再次比较事务号来确保它们是否匹配。它们匹配,现在检查SCN。undo块2,90的SCN是12320,它在我们的查询开始时间SCN 12330之前,因此我们不需要应用undo。如果不应用undo,我们的CR buffer将代表的是块7,678在SCN 12320时间点的版本,这比我们要查询的时间SCN 12330早了。

现在查看第二个ITL,它与事务5,2.6关联。这个事务在SCN 12350时间点已经提交了,在我们的查询开始时间之后,因此我们需要应用它的undo。从ITL条目来看,我们将得到ndo块地址2,70并且访问这个undo块。现在比较事务号,因为事务已经提交,undo信息将不再受保护。增加undo保留期可以让udno信息保留更长的境,但也不受保护。

假设另一个服务器进程覆盖了undo块2,70中的相关事务undo信息。如果出现这种情况,服务器进程的事务号将被记录并且这里将记录为5.2.6。通过事务号比较,我们注意到差异并且立即知道undo块2,70中的undo不能应用于我们的CR buffer。在这时,服务器进程将会发出快照太旧的错误信息并停止我们的查询。很明显,undo块快照太旧因为被其它进程覆盖了。

幸运地是,事务号是匹配的。undo块2,70中的undo是在SCN 12340时间点发生的改变,它在我们的查询开始之后,因此我们应用这个undo到我们的CR buffer。下一个undo链接是空的,因此没有其它undo需要应用了。

现在返回到ITL条目,这里没有更多的ITL需要考虑,因此我们完成的数据块的克隆。任何一个服务器进程现在都可以访问CR buffer 7,678它包含了SCN 12330时间所代表的内容。

现在应该很清楚为什么ITLs如此重要了,而且Oracle的读取一致性模型虽然非常强大、必要且高效,但仍然相对昂贵,因为它可能会消耗大量CPU和IO,从而减慢应用程序的响应时间。Oracle非常清楚这一点,并且从Oracle 10gr2开始使用内存优化结构来临时存储undo信息。这些对象不是段类型并且不受与段相关的CBC和LRU chain活动的影响。在内存中,undo被存储在shared pool中。

Oracle Buffer Busy Waits

buffers不能被锁定,因为他们不是关系型结构。然而有许多原因,它们可以会临时不能使用。当出现这种情况时,buffer确实很忙。将所有复杂性能提练为本质的东西,一个buffer busy waits等待事件是关于受限的并发性。一个进程需要访问一个buffer,但不能访问它因为其它的进程正在访问buffer且不允许并发访问。在很多情况下都会出现这种情况,每种情况都有特定的解决方案。

使用buffer busy waits等待事件具有挑战性的是有多种可能的诊断方法。一种最常见的诊断buffer busy的方法是使用reason code。reason code由三个数字组成,每一个数字的值都揭示了为什么buffer正忙且不能被立即访问的的部分原因。但在Oracle 11g中,oracle从v$session_wait与v$session视图中删除了p3这一列。

在Oracle 10g中,Oracle采用了一种最常见的buffer busy情况并且指定了它特有的等待事件。因此可以看到read by other session等待事件用于buffer busy waits等待事件的一部分。虽然可能引起混淆但也使用的等待事件更加具体。

四步诊断法
解决buffer busy类型的等待事件(包括read by other session等待事件)的关键是首先要了解正在等待的buffer。仅仅知道top等待事件是buffer busy waits是不够的。在了解正确解决方案前将需要收集额外的信息。额外需要的信息是buffer的类型与是否它是header块。有了这些信息后,就能对这个问题制定一些解决方案集。为了解决这个问题有一个四步诊断方法如下:
1.确定是否存在参数模式
2.识别buffer类型
3.确定是否是header块
4.实现合适的解决方案集

确定是否存在参数模式
为了诊断重复抽样buffer busy waits等待事件的p1与p2参数。p1参数是buffer的文件号,p2参数是块号。正常情况当重复抽样p1与p2值时,它们将会出现变化,指示正在等待不同的buffers。通常也可以看到一些小数据块集关联到特定的对象。如果相同的buffer或看到两次出现虽然很罕见但要特别注意。它们确实是hot块。当你重复抽样时,注意参数值,分析时需要使用它们。

SQL> @swswp buffer%busy
Database: prod16 30-MAR-10 02:22pm
Report: swswp.sql OSM by OraPub, Inc. Page 1
Session Wait Real Time w/Parameters
Sess
   ID Wait Event                   P1           P2        P3
----- ---------------------------- ------------ --------- -----
4391  buffer busy waits            4            54        0
4379  buffer busy waits            4            54        0
4381  buffer busy waits            4            54        0
4405  buffer busy waits            5            10340
4 rows selected.
SQL> l
1 select sid, event,
2 p1, p2, p3
3 from v$session_wait
4 where event like '&input%'
5 and state = 'WAITING'
6* order by event,sid,p1,p2
SQL>

识别buffer类型
每一个oracle块buffer是Oracle段的一部分。每个Oracle段是一个段类型,比如数据段,索引段,undo段或者临时段。解决方案集部分基于段类型。使用buffer busy waits等待事件的p1(文件号)与p2(块号),通过查询dba_extents 与dba_data_files可以识别段类型

def file_id=&1
def block_id=&2
col a format a77 fold_after
set heading off verify off echo off
set termout off
col tablespace_name new_value ts_name
select ts.name tablespace_name
from v$tablespace ts, v$datafile df
where file# = &file_id
and ts.ts# = df.ts#
/

set termout on
select
'File number :'||&file_id a,
'Block number :'||&block_id a,
'Owner :'||owner a,
'Segment name :'||segment_name a,
'Segment type :'||segment_type a,
'Tablespace :'||e.tablespace_name a,
'File name :'||f.file_name a
from dba_extents e,
dba_data_files f
where e.file_id = f.file_id
and e.file_id = &file_id
and e.block_id < = &block_id
and e.block_id + e.blocks > &block_id
and e.tablespace_name = '&ts_name'

上面三个会话正在等待文件4的块54。执行下面的脚本来查看对象类型

SQL> @objfb 4 54
File number :4
Block number :54
Owner :OE
Segment name :ORDERS
Segment type :TABLE
Tablespace :USERS
File name :/u01/oradata/prod16/OE01.dbf
1 row selected.

确定是否是header块
每个Oracle段有一个header块。表段与undo块有一个单独的header块。段的第一个extent中的第一个块就是段的header块。header块不同于其它的对象块,因为他们包含一些特定的信息。这种特殊性依赖于段类型。这也就是为什么首先要确实段类型的原因。

视图dba_segments包含了关于单个Oracle段的信息。它也包含了header块的文件号与块号。因此一个简单查询将返回busy buffer是否是一个header块。

SQL>
1 select *
2 from dba_segments
3 where header_file = 4
4* and header_block = 54
SQL> /
no rows selected

在识别buffer busy模式,对象类型与是否是header块之后,就有足够的信息来直接选择可用的解决方案了。

实现合适的解决方案集
buffer busy waits等待事件在我们选择合适的解决方案集之前之所以能给我们诊断带来许多麻烦, 是因为我们必须首先确定busy pattern,buffer类型与是否是header块。

单个忙表块的解决方案
如果相同的单个buffer几乎总是busy buffer,那么我们需要找出原因。在我们的示例中,一组缓冲区处于繁忙状态,包括缓冲区4、54;也就是说,不仅仅是一个缓冲区几乎总是繁忙的。但是,如果只有一个繁忙的缓冲区,那么需要清楚地了解存储在块中的信息(可能只查询该块中的行)以及为什么应用程序对该块如此感兴趣。

可以基于等待会话的v$session.sql_id列来判断正在等待的SQL语句。甚至你可能需要与开发人员沟通,因为这种情况通常是与应用程序相关的。最常见的情况(但不总是)是Oracle 序列号不能被使用。当问为什么Oracle序列号不能被使用时,可能收到的回答是“我们想我们的应用程序独立于数据库”。

这种问题的原因是这种快速buffer访问给buffer的内部结构提出了难以置信的高并发要求。当内部结构被改变后,块对于其它进程是不能使用的,因此出现buffer busy waits等待事件。因为应用程序架构进行修改通常是不现实的,创造性的找到其它方法来解决问题。例如,如果每一行包含一个应用程序序列号,那么将行记录移动到它自己的块中并增加一个大的固定长度的列,或将块的pct_free属性设置为一个很高的值来保证存储最少的行记录。这种解决方法是痛苦,但没有buffer busy waits痛苦。

多个忙表的解决方案
多个忙表块最有可能出现buffer busy waits与read by other session等待事件。在这种情况下,每次检查busybuffers都是不同的,busy buffers是数据段并且它们不是头段。当出现这种情况时,原因通常是查询或者DML与查询的混合。

对于纯查询来说,这将发生在多个会话查询相关数据块时,并且相同块没有存放在buffer cache。第一个会话调用IO子系统并post一个db file sequential read或db file scattered read等待事件。对于其它会话也调用IO子系统来查询数据块是愚蠢的,因此它们不仅仅要等待第一个会话完成IO调用,还要等待数据块被存入buffer cache中。那么其它会话就可以像任何其它会话一样来访问buffer。当其它会话正等待第二个会话完成时,从Oracle 10g开始,等待会话将post一个read by other session等待事件,对于之前的版本,等待会话将post一个buffer busy waits等待事件。因此会话实际上是在等待另一个会话完成读取操作。

解决方案非常简单,并且主要集中在增加数据块存放在buffer cache中的可能性。如果buffer存放在buffer cache中,那么这种busy buffer waits的情况将不会发生。因此请考虑如何增加数据块在buffer cache中的可能性。

应用程序所关注的解决方案集中在找到top物理IO SQL语句并优化语句,关注减少物理IO。这个解决方案不仅由于块访问的减少而提高了语句性能,而且还减少了缓冲区繁忙等待的机会,并允许将来自其他对象的块存储在缓冲区缓存中。Oracle所关注的解决方案是增加buffer cache来增加数据块存放在buffer cache中的可能性因此而不需要进行IO调用。另一种有创意但不切实际的是减小数据库块大小。小块与随机行访问模式组合会造成更有效的buffer cache.换句话说,缓存更有效的存放真正被频繁访问的行。操作系统所关注的解决方案是减小IO读取响应时间。快速检索数据块,会话将等待更少的时间。

就我个人而言,我会同时认真考虑每个选项。但我预测性能变化是吞吐量增加且响应时间减少。

还有一些我可能一无所知的问题和事情需要考虑,也许还要考虑相关的业务与预算。因此我不会简单地从SQL或增加buffer cache开始。我将收集信息并帮助他人收集信息。然后一起共同制订计划。

另一种常见的非header数据块问题是在buffer被改变与被查询时出现的。通常这会调用DML与查询SQL语句,但DML SQL也可以在过滤时touch大量的buffers。可能出现的问题是DML正在更新内部buffer结构时而另一个进程想要查询数据块的内容或者也正想要更新内部buffer结构。记住这些改变不是行改变而是内部Oracle结构改变。如果有行或表锁问题,一个队列等待将会被posted。好的策略是集中减少并发。考虑减小块地密度(移动行到其它块或增加块的pct_free属性),在高峰期间减小工作负载,并发活动,减少由DML与查询SQL所touched的buffers数量。

表段头块解决方案
表的第一个区的第一个块叫作段头块。与所有的段头块一样,它们包含非常特定的内部Oracle结构与它们的段类型相关。对于表段,头块的部分内容是关于块可以接受额外插入记录的位置。这些块也叫作free块。当一个进程必须插入行记录到一个表中,为了找到一个free块首先检索表的段头块,检索buffer,然后插入行记录。如果有许多进程并发插入行记录到相同的表中,一个表段头块将导致buffer busy waits等待事件。

幸运地是解决方案非常简单并且工作的很好。如果正使用手动段空间管理,那么段空间管理由free lists控制。Oracle的free list方法通常工作的很好,但在高并发情况下,现有的free lists无法处理工作负载。幸运地是,我们能很容易地修改表来在另一个段块中创建额外的free lists。这将导致一个头块减少被频繁访问的机率,因此减少buffer busy wait竞争。只要增加free lists直到竞争平息。free lists的数量可以在dba_segments视图中的free lists列中找到。如果这个列为空,那么我们知道free lists没有被使用而是使用自动段空间管理(ASSM)。

另一种选项更侧重于长期并且在维护期间可以执行。这就是将高并发段移动到使用ASSM的本地管理表空间中。ASSM不使用free lists,但使用位图来管理可用空间。这通常增加了表段头块的并发性。

Undo段头块解决方案
Undo段不同于表段因为它包括了与事务回滚和读一致性相关的信息。对于回滚与读一致性有一个Oracle结构叫作事务表。简单地说,事务表是到一个undo段内容的映射。每个undo段包含一个单独的事务表,它位于undo段头块中。当出现大量的DML,特别是与读一致性活动组合出现时,事务表将成为竞争点,在undo段头块上会出现buffer busy waits等待事件。在讨论如何解决这个问题之前先来了解事务表。

缺省情况下,每个Oracle事务会生成redo(前滚)与undo(回滚)信息,就像实际数据更改与可能的索引更改一样。undo信息被存储在undo段中,并且每个undo段的映射被存储在它的事务表中。每个undo段的事务表可以持有多个事务条目。从关系型数据库角度来考虑,每个条目关联事务表中的一行记录。有关键字表作为名字的一部分是不幸的,因为事务表不是一个关系型数据库结构。事实上,Oracle内核开发人员将事务表条目称作为slots(插槽)。

因为事务表被存储在Oracle块中,所以它能持有的slots数是有限的,这依赖于Oracle块大小。如果事务表被填满并且有新条目必须被添加,旧的非活动事务条目将被新条目覆盖。如果事务是活动的且在事务表中没有空间或者多个会话需要改变事务表,那么将出现buffer busy waits等待事件。

如果数据库没有使用自动undo管理(AUM),而是使用了传统回滚段,那么解决方案很简单。就是创建额外的回滚段,这也将创建额外的事务表,因此分布事务表活动。将会注意到buffer busy waits竞争消失。记住增加额外的回滚段直到buffer busy waits等待事件从top等待事件中消失。

大多数Oracle系统现在都利用Oracle AUM功能。缺省情况下,Oracle尝试给每个undo段只指派一个活动事务。如果每个undo段有一个活动事务并且在undo表空间中有可用空间,Oracle将会自动创建额外的undo段。这通常要小心buffer busy waits等待。然而,如果在undo表空间中没有可用空间,多个事务将会被指派到一个undo段上,并且这最终将导致undo段头块竞争。解决方案就是向undo表空间增加另外的数据文件,因此能让Oracle创建额外的undo段,就是这么简单。

索引叶子块解决方案
简单地说,索引是有序结构。这种有序结构允许索引被用来执行快速搜索。对索引的任何改变必须导致对有序结构进行维护。如果有序结构不被维护,搜索将不能快速完成,并且索引将毫无价值且被损坏。因此有序结构是一个索引必须被维护的。这对性能有深远的影响。

当索引排序后能使用快速搜索,在高并发插入的情况下,它可能造成严重的性能问题。例如,假设一个索引是基于一个增长序列号(例如,1,2,3等等),这通常叫作单调递增值。如果一个表列包含这个序列号并且列被索引,在索引叶子块中,各个索引项将彼此相邻放置。因为索引必须维护顺序,并且索引是基于升序排列的。

当许多会话并发插入记录时会出现问题。当每个插入语句获得下一个序列号时,索引条目很可能被物理地放置在相同的索引叶子块中,或者不太可能被放置在相邻的索引叶子块中。如果并发数足够,会话将需要等待另一个会话完成索引的叶子块更改而等待很长时间。当会话等待时,它将post一个buffer busy waits等待事件,并且被等待的buffer是一个索引叶子块。这种情况可能变得非常严重并且严重影响性能。

一种解决方案是让Oracle对该列创建反向键索引。从DBA角度来说,序列号和以前一样,但它的内部字节被反转了。因为索引条目必须以内部排序的方式来存储,索引条目将可能被存放在不同的叶子块中,这将有效的消除buffer busy waits等待。假设序列号用4个字节来表示。因此前四个序列号(1,2,3,4)将以0001,0010,0011与0100来表示。如果这四个值被索引,因为索引排序结构必须被维护,它们将彼此相邻而存储。然而如果它们的字节被反转,那么它们将以1000,0100,1100与0010来表示。因为它们必须被以排序的方式存储索引,它们将很可能不会被存储在相同的索引叶子块中。事实,它们将分散到所有的索引叶子块中。由于索引键反转的结果因此buffer busy waits将会消除。

创建一个反向键索引非常怎么着,使用与正常创建索引相同的DDL语句,只是简单地在结尾处增加了一个reverse关键字。

SQL> create unique index special_cases_rk_u1 on special_cases (object_id) reverse;
Index created.

因为在每个索引叶子块中有频繁的索引插入操作,为了减少叶子块的频繁分裂,录创建反向键索引时可以考虑增加pctfree 50的存储参数。

反向键的优点与缺点
反向键的缺点是当解决了buffer busy wait问题时,可能对查询性能有显著的影响。假设我们基于序列号列创建的索引优化了查询。但因为buffer busy waits问题,我们删除了nonreversed索引,然后创建了一个reversed索引。现在这些排序的序列号被分散到所有的索引叶子块中。高性能的索引范围扫描可能不能执行地很好。事实上基于成本的优化器应该能识别并选择另一种执行计划。否则查询可能潜在touch每个索引叶子块和大量的数据块。

当反向键索引可能增加插入并发与吞吐量时,也可能对之前已经优化过的查询有一些负面影响。对于性能优化来说,你的职责就是找到一种性能折中的方案或实现一种创造性解决方案(比如对索引或表进行分区)来对插入与查询操作来实现最大性能优化。

Oracle Free Buffer Waits

等待事件free buffer waits与数据库写进程的活动紧密相关并且通常也会看到等待事件db file parallel write与log file parallel write(日志写进程等待)。然而free buffer waits等待事件是唯一要数据库写进程,IO子系统和服务器进程活动有趣的给合才能出现。

理解free buffer waits的关键是要了解push-to-disk问题与pull-from-LRU-chain问题之间的区别。当我们看到等待事件db file parallel write进入到报告的top等待事件中时,这个问题说明有太多的时间被花费在写或pushingdirty buffers到磁盘上了(buffers不是真实的移动到磁盘,而是复制,将造成块与buffer匹配来创建free buffer)。一个服务器进程当它不能足够快速地找到一个free buffer时就会posts一个free buffer waits事件。一种原因是数据库写进程没有从LRU chian中pulled足够的dirty buffers并且使它们再次变为free状态。因此它不是一个push-to-disk的问题,而是一个pull-from-LRU-chain问题。虽然这种差异可能被夸大了,但它对我们的解决方法有很大的影响。

如果我们关注pull问题,那么将尽一切可能确保数据库写进程不会落后于从buffer cache中获取dirty buffers太多。换句话说,我们的目标是让数据库写进程做更多的工作因为问题不是写入磁盘的问题。如果问题是写入磁盘的问题,那么top等待事件将是db db file parallel write而不是free buffer waits。

通过另一种buffer cache场景来突出显示free buffer wait的情况。假设你是一个服务器进程必须要查询一行记录,而记录存放在一个特定的数据块中。基于SQL语句与数据字典,你知道文件号与块号。你肯定希望这个数据块已经存放到buffer cache中了,但为了确认并获得它的内存地址,你必须访问CBC结构。通过哈希文件号与块号指向到一个特定的哈希桶。基于这个哈希桶,查找相关的latch并争取拿到这个latch。在经过一些spins操作后,可能能获得这个latch,因此可以开始序列化CBC搜索操作。然而这个CBC是空的并且没有包含buffer headers。因此知道这个数据块不在buffer cache中。为了得到这个数据块,给IO子系统执行读取调用并等待,将post一个db file squential read。最后,获得了这个数据块,停止等待,并且开始消耗CPU,现在这个数据块已经到了你的PGA内存中。

你需要找到一个free buffer来缓存这个块,因此要定位你的LRU chain。但在你可以开始扫描你的LRU chain之前,你必须要获得合适的LRU chain latch。最后使用你的LRU chain latch与在PGA内存中的块,你开始扫描LRU来查找一个free buffer。你首先遇到一个buffer header并检查这个buffer header。好消息是这个buffer是free状态,但是坏消息是它不被频繁访问,且touch count为12。因此promote这个buffer到LRU列表的MRU端,并且将touch count减小到0。

继续查找到下一个buffer header,检查buffer header并发现它是一个dirty buffer且touch count为1。不满足缺省频繁访问的阈值2。因此,移动buffer header到LRU chain***217;s写列表。在完成移动后,检查drity list长度确保
它小于_db_large_dirty_queue的值。dirty list只有6,它小于缺省值25,因此不需要通知数据库写进程执行写操作。

现在假设你已经扫描了比_db_writer_max_scan_pct更多的buffer headers。如果是这样将会很沮丧。将会要消耗大量的CPU与持有LRU chain latch相当长的时间。假设你已经扫描的buffer headers比这个阈值多,你现在停止扫描,释放LRU chain latch,通知数据库写进程释放一些buffers,并post等待事件free buffer waits而耐心等待10ms。当你正在感叹“free buffer wait!”有10ms时,数据库写进程正在忙于将dirty buffer写入磁盘,并释放它们,然后将它们再插入到LRU chain的LRU端。

现在已经等待了10ms,你被唤醒,再次获取LRU chain latch,并开始从LRU chain的LRU端搜索。现在很有可能有一个不被频繁访问的free buffer正在等待你执行替换操作。现在pin住buffer header,释放LRU latch,更新buffer header,合理移动CBC结构中的buffer header因此在你正将这个块放入cache时其它的进程可以找到这个块,使用从磁盘中读取的块来替换free buffer,然后unpin这个buffer header。

注意是什么导致服务器进程post这个free buffer wait事件了。首先,执行一个物理IO读取,将强制服务器进程来搜索一个free buffer。再次,需要扫描太多的dirty buffers,这意味着必须存在活动的DML语句。最后,数据库写进程没能确保在LRU chain的LRU端有足够的free buffers。这三种条件都会造成这种情况,这也意味着对于这个问题有三种解决方案。

如果top等待事件是free buffer waits,关注pull,而不是push问题。如果忘记这一点,将会采用不合适的解决方案。

操作系统可能会遇到CPU或IO瓶颈或都两者都有,但可能是IO瓶颈。等待事件free buffer waits从来没有通过关注操作系统而被解决。应该从Oracle与应用程序角度来解决这个问题。如果是CPU瓶颈,查找非Oracle消耗CPU的进程。
这里有以下解决方案:
.增加buffer cache
如果有可用内存,增加buffer cache大小。这将允许更多的buffers可用,将增加找到一个free buffer的可能性。

.增加数据库写进程的pull能力
例如增加数据库写进程的数量。做任何你认为可以帮助数据库写进程可以增加dirty buffer写入效率的事情。除非buffer cache非常小,那么这可能是你最好的解决方案了。

.增加_db_writer_max_scan_pct参数
这将给数据库写进程更多的时间来清除它的写列表。这将造成LRU chain latch的竞争,因为服务器进程在你放弃与post一个free buffer waits事件之前搜索更多的free buffers。

.减小写批处理大小的阈值
这将强制数据库更频繁的flush写列表,增加在LRU chain的LRU端存在free buffer的可能性。为了减小写批处理大小,减小_db_large_dirty_queue参数的大小。如果数据库写进程正忙于写dirty list中的buffer到磁盘时,服务器进程将不能移动一个dirty buffer到写列表中。如果一个服务器进程正在寻找一个free buffer,并尝试移动一个不被频繁访问的dirty buffer到正执行写操作的dirty列表中,它将等待,并post一个free buffer waits等待事件。如果为了解决db file parallel write问题而增加了写批理处理大小,它可能增加的大多了。这不是很常见,但可能发生。

应用层面有两种解决方案:
.查找并优化物理IO语句
没有从磁盘读取数据块就不会出现free buffer waits等待事件。找到top物理IO SQL语句。通常只有少量大的SQL语句消耗物理IO很明显。通过优化或降低它们的执行频率来减少物理IO量。

.查找并减少DML SQL语句的影响。
因为free buffer waits等待事件与LRU chain中有太多的dirty buffers相关,这就肯定存在DML SQL语句。DMLSQL可能很难被找到因为它可能是一个高物理IO,高逻辑IO,高执行频率或高CPU消耗的语句。它可能是很多统计信息的巧妙组合。如果不能查看SQL的类型,那么查看top物理IO与逻辑IO SQL语句,然后检查语句本身。很有可能DML SQL也是top物理IO SQL语句。如果是这样,你就已经找到了关键SQL语句。

Oracle Respones-Time Analysis Reports

Oracle响应时间分析报告分为系统级与会话级,报告相比awr报告更加直观清楚有助于快速分析定位性能问题,这里使用OSM工具来生成这两种类型的报告,该工具是由Craig Shallahamer所写。
在数据库中创建osm用户并安装osm脚本所需要使用的对象

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 27 15:43:54 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user osm   identified by "osm" default tablespace sx temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to osm;

Grant succeeded.

SQL> conn osm/osm
Connected.
SQL>  exec sys.dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL> @osmprep.sql

OraPub System Monitor - Interactive (OSM-I) installation script.

(c)1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008 by OraPub, Inc.
(c)2009,2010,2011,2012,2013,2014,2015 by OraPub, Inc.

There is absolutely no guarantee with this software.  You may
use this software at your own risk, not OraPub's risk.
No value is implied or stated.

You may need to run $ORACLE_HOME/rdbms/admin/catblock.sql

Connect as the user who will be using the OSM.

Press ENTER to continue.
.....

To categorize wait events for OSM reports, run:

For pre-10g systems, run event_type_nc.sql
For 10g and beyond, run event_type.sql

Once you cateogrize the wait events, the installation is complete.

Menu is osm.sql

ENJOY!!

SQL> @event_type.sql

file: event_type.sql for Oracle 10g and beyond...

About to categorize wait events for OSM reports.

Press ENTER to re-create the o$event_type table.

.....

OraPub Categorization Summary
-----------------------------------------------

TYPE                   COUNT(*)
-------------------- ----------
bogus                       126
ior                          20
iow                          59
other                      1162


  COUNT(*)
----------
      1367


Oracle Categorization Summary
-----------------------------------------------

WAIT_CLASS             COUNT(*)
-------------------- ----------
Administrative               55
Application                  17
Cluster                      50
Commit                        2
Concurrency                  33
Configuration                24
Idle                         96
Network                      35
Other                       958
Queueing                      9
Scheduler                     8

WAIT_CLASS             COUNT(*)
-------------------- ----------
System I/O                   32
User I/O                     48

13 rows selected.


  COUNT(*)
----------
      1367

OSM工具包中的rtsess.sql是用来生成会话级报告,rtsysx.sql,rtpctx.sql用来生成实例级报告

实例级Oracle响应时间分析报告是基于rtsysx.sql脚本,它将捕获指定时间间隔内实例范围内关于响应时间方面的详细信息。这个脚本将对实例级统计信息(v$sysstat,v$sys_time_model)与实例级等待事件统计信息(v$system_event)生成快照。下面的例子在120秒的时间间隔内,脚本每10秒被唤醒一次,从v$session视图中查询活动的SQL并存储当前正在运行的SQL_ID。在报告生命周期结束后,其它的统计住处快照会生成,计算出时间差异并生成报告。几乎报告中的所有信息都可以从Statspack或AWR报告中进行收集。使用rtsysx.sql脚可以生成格式化的输出可以快速的执行Oracle响应时间分析。使用脚本rtsysx.sql脚本生成的报告包括以下几个组成部分:
.第一部分是关注工作量负载情况
.第二部分是高级别的响应时间分类信息
.第三部分是IO与非IO情况
.第四部分是没有使用绑定变量的SQL语句
.对于Oracle 10g及以上版本,第五部分是关于操作系统CPU利用率

下面执行rtsysx.sql脚本,执行生命周期是120秒,脚本每10秒被唤醒一次。

SQL> @rtsysx.sql 120 10

OraPub's Response Time Analysis (RTA) interactive system level delta report

Initializing response time delta objects...
Sleeping and probing active SQL for next 120 seconds...
Done sleeping...gathering and storing current values...

*** Response Time Ratio and Workload Metrics

RT Ratio  Ora Trx/s Block Changes/s User Calls/s      Execs/s
-------- ---------- --------------- ------------ ------------
   0.325       0.54           90.63        27.41       100.00

*** Response Time System Summary (delta - interactive - system level)

   Tot CPU   CPU SP   CPU BG CPU Parse CPU Recur         Tot Wait  IO Wait Other Wait
      Time     Time     Time      Time      Time Ora CPU     Time     Time       Time       %          %
     (sec)    (sec)    (sec)     (sec)     (sec)  Util %    (sec)    (sec)      (sec) IO Wait Other Wait
---------- -------- -------- --------- --------- ------- -------- -------- ---------- ------- ----------
        34       26        8         2        15     0.4       17       15          1      92          8

*** I/O Wait Time Summary w/Event Details (delta - interactive - system level)

 IO Wait  IO WRITE    IO READ
    Time Wait Time  Wait Time  % IO % IO
   (sec)     (sec)      (sec) Write Read
-------- --------- ---------- ----- ----
      15        10          5    66   34

                                                                          Tot Call    Avg Call
                                                                         Wait Time   Wait Time
IO Wait Event                                                R,W     %       (sec)        (ms) Tot Waits
------------------------------------------------------------ --- ----- ----------- ----------- ---------
LGWR real time apply sync                                    W      57        8.69       65.83       132
db file sequential read                                      R      31        4.67        4.97       940
LGWR-LNS wait on channel                                     W       5        0.71        1.74       408

*** Other Wait Time (non-I/O) Event Detail (delta - interactive - system level)

                                                                      Tot Call    Avg Call
                                                                     Wait Time   Wait Time
Non IO (other) Wait Event                                        %       (sec)        (ms) Tot Waits
------------------------------------------------------------ ----- ----------- ----------- ---------
gc current block 2-way                                          26        0.35        0.62       569
gc cr grant 2-way                                               17        0.23        0.48       484
reliable message                                                10        0.14        1.21       116
os thread startup                                               10        0.14       23.33         6
enq: US - contention                                             6        0.08        0.49       163
library cache pin                                                6        0.08        0.56       144
library cache lock                                               4        0.06        0.61        98
gc current grant 2-way                                           4        0.05        0.45       111


*** SQL Activity Details During Probe

                           Phys Rds  Log Rds  Tot Time   CPU Time                             Rows        Stmt
SQL ID             Sec/EXE      (k)      (k)     (sec)      (sec) Sec/PIO Sec/LIO     Runs     (k)  Sorts Type
---------------- --------- -------- -------- --------- ---------- ------- ------- -------- ------- ------ -----
gz5bfrcjq060u         0.01        0        0       0.3        0.3 #######   0.001       24       0     23 INSER
c77k33u5u7zgc         0.06        0       17       0.1        0.1 #######   0.000        2       0      2 SELEC
8fb44rrg8a5rh         0.13        0       15       0.1        0.1 #######   0.000        1       0      2 SELEC
98564h3vavfcm       -25.78       -0       -0     -51.6       -0.6  51.552   0.276        2       0      0 inser


*** SQL Similar Statements During Delta

SQL Statement (shown if first 10 chars)                                   Count
---------------------------------------------------------------------- --------
SELECT NVL                                                                    2

*** OS CPU Breakdown During Delta

Category                             Percent
----------------------------------- --------
Idle                                   96.51
IO Wait                                 0.44
Nice                                    0.00
System                                  0.38
User                                    2.47

Delta is 123.53 seconds

Number of CPU cores is 80

报告的第一部分:Response Time Ratio and Workload Metrics
报告的第一部分提供了与Statspack与AWR中Load Profile部分相同的Workload Metrics。这部分信息在比较响应时间快照之间的差异时非常有用。如果工作负载减少那么可以预期响应时间减少。

*** Response Time Ratio and Workload Metrics

RT Ratio  Ora Trx/s Block Changes/s User Calls/s      Execs/s
-------- ---------- --------------- ------------ ------------
   0.325       0.54           90.63        27.41       100.00

报告的第二部分:Response Time System Summary
这部分信息显示总的CPU时间为34秒,总的等待时间为17秒,也可以说是在120秒的时间间隔内,Oracle进程消耗的CPU时间只有34秒,消耗的等待时间只有17秒。而且还可以看到17秒的等待时间中IO等待时间为15秒,非IO等待时间为1秒。在120秒的时间间隔内,Oracle进程只消耗了总CPU可用时间的0.4%,这个数据是使用Oracle进程消耗的总CPU时间除以主机可用CPU时间。在120秒时间间隔的主机的CPU可用时间为CPU的内核数量乘以报告时间间隔。在这里主机的CPU内核数量为80,报告时间间隔为120秒,所以Oracle所消耗的CPU时间为34/(120*80)=0.4%。如果主机上只运行该实例,那么它也提供了操作系统CPU利用率给我们,因此也不用执行操作系统命令来查看CPU利用情况了。

*** Response Time System Summary (delta - interactive - system level)

   Tot CPU   CPU SP   CPU BG CPU Parse CPU Recur         Tot Wait  IO Wait Other Wait
      Time     Time     Time      Time      Time Ora CPU     Time     Time       Time       %          %
     (sec)    (sec)    (sec)     (sec)     (sec)  Util %    (sec)    (sec)      (sec) IO Wait Other Wait
---------- -------- -------- --------- --------- ------- -------- -------- ---------- ------- ----------
        34       26        8         2        15     0.4       17       15          1      92          8

报告的第三部分:I/O Wait Time Summary w/Event Details
如果IO有问题,那么你肯定想知道是读还是写有问题,管理员可以从了解IO负载类型来给出相关的解决方案。比如一个IO读问题可以通过将常被访问的数据块保存在Oracle Cache中来使用IO读的影响降低到最小,如果一个IO写问题可以通过配置,比如联机重做日志文件的数量与大小来使IO写的影响降低到最小。从报告中可以看到IO总等待时间为15秒,其中IO写为10秒,IO读为5秒。其中LGWR real time apply sync事件平均等待一次的时间是65.83毫秒,这是因为配置了ADG,对于同城异地容灾来说这个等待时间也还是正常的,db file sequential read事件平均等待一次的时间为4.97毫秒也是正常的。

*** I/O Wait Time Summary w/Event Details (delta - interactive - system level)

 IO Wait  IO WRITE    IO READ
    Time Wait Time  Wait Time  % IO % IO
   (sec)     (sec)      (sec) Write Read
-------- --------- ---------- ----- ----
      15        10          5    66   34

                                                                          Tot Call    Avg Call
                                                                         Wait Time   Wait Time
IO Wait Event                                                R,W     %       (sec)        (ms) Tot Waits
------------------------------------------------------------ --- ----- ----------- ----------- ---------
LGWR real time apply sync                                    W      57        8.69       65.83       132
db file sequential read                                      R      31        4.67        4.97       940
LGWR-LNS wait on channel                                     W       5        0.71        1.74       408

报告的第四部分:Other Wait Time (non-I/O) Event Detail
这部分显示了非IO等待事件的汇总与底层相关的等待事件详细信息,因为非IO等待时间总共才只有1秒,这并不影响性能。所以相关的等待事件我们也就不用查看了。

*** Other Wait Time (non-I/O) Event Detail (delta - interactive - system level)

                                                                      Tot Call    Avg Call
                                                                     Wait Time   Wait Time
Non IO (other) Wait Event                                        %       (sec)        (ms) Tot Waits
------------------------------------------------------------ ----- ----------- ----------- ---------
gc current block 2-way                                          26        0.35        0.62       569
gc cr grant 2-way                                               17        0.23        0.48       484
reliable message                                                10        0.14        1.21       116
os thread startup                                               10        0.14       23.33         6
enq: US - contention                                             6        0.08        0.49       163
library cache pin                                                6        0.08        0.56       144
library cache lock                                               4        0.06        0.61        98
gc current grant 2-way                                           4        0.05        0.45       111

报告的第五部分:SQL Activity Details During Probe
为了帮助分析应用程序,报告捕获了直接影响响应时间的SQL语句并显示了资源消耗情况,以下面的数据来看,在捕获的SQL语句所消耗的资源都是很少的不会影响性能,其中语句的物理读为0,逻辑读总大小也才32K。

*** SQL Activity Details During Probe

                           Phys Rds  Log Rds  Tot Time   CPU Time                             Rows        Stmt
SQL ID             Sec/EXE      (k)      (k)     (sec)      (sec) Sec/PIO Sec/LIO     Runs     (k)  Sorts Type
---------------- --------- -------- -------- --------- ---------- ------- ------- -------- ------- ------ -----
gz5bfrcjq060u         0.01        0        0       0.3        0.3 #######   0.001       24       0     23 INSER
c77k33u5u7zgc         0.06        0       17       0.1        0.1 #######   0.000        2       0      2 SELEC
8fb44rrg8a5rh         0.13        0       15       0.1        0.1 #######   0.000        1       0      2 SELEC
98564h3vavfcm       -25.78       -0       -0     -51.6       -0.6  51.552   0.276        2       0      0 inser

报告的第六部分:SQL Similar Statements During Delta
在执行rtsysx.sql脚本所指定的第二个参数就与查找类似SQL语句相关,类似SQL语句是除了where子句中的过滤与连接条件不同之外其它完全相同的语句。第二个参数我们指定的是10,也就是说类似语句会被统计且统计数大于1的语句的前10个字符才会被显示。

*** SQL Similar Statements During Delta

SQL Statement (shown if first 10 chars)                                   Count
---------------------------------------------------------------------- --------
SELECT NVL                                                                    2

报告的第七部分:Operating System CPU Utilization
这部分显示了操作系统使用的详细情况。从Oracle 10g开始,Oracle捕获操作系统CPU的使用的详细信息并且这些信息可以通过v$osstat视图来查看。

*** OS CPU Breakdown During Delta

Category                             Percent
----------------------------------- --------
Idle                                   96.51
IO Wait                                 0.44
Nice                                    0.00
System                                  0.38
User                                    2.47

Delta is 123.53 seconds

Number of CPU cores is 80

会话级Oracle响应时间分析报告
执行脚本rtsess9.sql来对指定会话1110来生成会话级Oracle响应时间分析报告,从下面的报告中可以看到会话的响应时间为699.29秒,其中队列时间为608.20秒,非计数时间为91.09秒,而队列时间中IO队列时间只有0.3秒,Net+Client队列时间占了607.73秒。这说明会话一直在等待客户端程序进行调用。

SQL> @rtsess9 1110
===================================================================
Session Level Response Time Profile

Oracle session 1110
CPU statistics number is 12

......


Session level response time details for SID 1110

*** Response Time Summary

      Response   Service     Queue Unaccount   % CPU % Queue    % UAT
     Time(sec) Time(sec) Time(sec) Time(sec)      RT      RT       RT
[rt=st+qt+uat]      [st]      [qt]     [uat] [st/rt] [qt/rt] [uat/rt]
-------------- --------- --------- --------- ------- ------- --------
        699.29      0.00    608.20     91.09    0.00   86.97    13.03

*** Queue Time Summary

                      QT              QT         QT
Queue Time(sec) I/O(sec) Net+Client(sec) Other(sec)
  [qio+qnc+qot]    [qio]           [qnc]      [qot]
--------------- -------- --------------- ----------
         608.20     0.03          607.73       0.44

*** Queue Time IO Timing Detail

           QT             QT            QT
     I/O(sec) Write I/O(sec) Read I/O(sec) % Writes Time % Read Time
[tio=wio+rio]          [wio]         [rio]     [wio/tio]   [rio/tio]
------------- -------------- ------------- ------------- -----------
         0.03           0.03          0.00         99.97        0.00

*** Queue Time IO Event Timing Detail

                                         Wait Time
Wait Event Name                              (sec)
---------------------------------------- ---------
direct path write                             0.01
log file sync                                 0.02

*** Queue Time Other Event Timing Detail

                                         Wait Time
Wait Event Name                              (sec)
---------------------------------------- ---------
gc cr block 2-way                             0.08
library cache pin                             0.01
gc current block congested                    0.01
gc current block 2-way                        0.31
row cache lock                                0.01
events in waitclass Other                     0.01
library cache lock                            0.01

*** Wait Event Time Not Categorized (for QA)
......

如果应用程序用户与Oracle服务器进程都在等待这是不正常的。如果用户已经执行了命令并且正等待命令执行结束,同时,相关的Oracle服务器进程正等待从客户端进程接收信息,那么在这两者之间存在问题。那么大概问题区域就是网络与客户端进程了。

Manage SQL Plan Baselines in Oracle 12c

使用dbms_spm与dbms_xplan包来执行大部分的SQL执行计划管理任务。SQL执行计划管理可以分为以下基本任务:
.配置SQL执行计划管理
.显示SQL执行计划基线中的执行计划
.加载SQL执行计划基线
.手动evolve执行计划基线中的执行计划
.删除SQL执行计划基线
.管理SQL Management Base(SMB)
.迁移Stored Outlines to SQL Plan Baselines

配置SQL执行计划管理
.配置捕获与使用SQL Plan Baselines
.管理SPM Evolve Advisor Task

配置捕获与使用SQL Plan Baselines
可以使用optimizer_capture_sql_plan_baselines与optimizer_use_sql_plan_baselines参数来控制SQL plan管理。

SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

optimizer_capture_sql_plan_baselines的缺省值为false。对于不在执行计划历史中的任何重复的SQL语句,数据库不会对SQL语句自动创建一个初始的SQL Plan Baseline。如果optimizer_capture_sql_plan_baselines参数设置为true,那么可以使用dbms_spm.configure过程来配置过滤器来判断哪些SQL语句满足捕获条件。缺省情况是没有配置过滤器的,这意味着所有重复执行的SQL语句都满足捕获条件。

optimizer_use_sql_plan_baselines的缺省值为true。对于已经在SQL plan baseline中存在的任何SQL语句,数据库会自动向SQL plan baselines中以未接受的执行计划来添加新的SQL plan。

对SQL Plan管理启用自动初始化Plan捕获
将optimizer_capture_sql_plan_baselines参数设置为true是对在plan历史中不存在的任何SQL语句自动创建一个初始化SQL Plan baseline所必要的。缺省情况下,当自动SQL plan baseline捕获被启用后,数据库会为每个重复的SQL语句,包括所有递归SQL语句与监控SQL语句创建一个SQL Plan baseline。因此,自动捕获功能可能会造成大量的SQL Plan Baseline。 为了限制捕获的SQL Plan Baselines的数量可以使用dbms_spm.configure过程来配置过滤条件。optimizer_capture_sql_plan_baselines参数不控制自动向之前创建的SQL plan baseline添加新发现的执行计划。

启用自动捕获SQL plan baseline操作如下:
1.以有相关权限的用户用SQL*Plus登录数据库

[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2.显示当前SQL Plan管理的设置情况

SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

3.为了对重复的SQL语句启用自动生成SQL Plan Baseline执行下面的语句

SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*';

System altered.

SQL> show parameter optimizer_capture_sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE

当启用SQL Plan Baselines自动捕获功能后可以从下面的结果看到对重复的所有SQL语句进行了执行执行的捕获

SQL> select t.sql_handle,t.sql_text,t.creator,t.origin from DBA_SQL_PLAN_BASELINES t;

SQL_HANDLE               SQL_TEXT                                                                         CREATOR   ORIGIN
------------------------ -------------------------------------------------------------------------------- --------- ---------------
SQL_187ebe987c151d1b     select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'      SYS       AUTO-CAPTURE
SQL_65afdf280fbfa69f     select * from DBA_SQL_PLAN_BASELINES t                                           SYS       AUTO-CAPTURE
SQL_6807bab99db0361a     select value from v$sesstat where sid = :sid order by statistic#                 SYS       AUTO-CAPTURE

为自动SQL Plan Baseline捕获配置过滤条件
如果optimizer_capture_sql_plan_baselines设置为true,那么你可以使用dbms_spm.configure过程来对重复执行的SQL语句创建一个自动捕获过滤条件。自动过滤可以只捕获想要的SQL语句并排除非关键语句,这样可以节省SYSAUX表空间的使用。可以对不同的类型配置多个参数,也可以在单独的语句中对相同的参数指定多个参数值,数据库会进行组合。这种设置是附加型的:一个参数设置不会覆盖之前的设置。例如,下面的过滤设置用来捕获解析方案SYS或SYSTEM中的SQL语句:

exec dbms_spm.configure('auto_capture_parsing_schema_name','sys',true);
exec dbms_spm.configure('auto_capture_parsing_schema_name','system',true);

然而,不能在相同的过程中对相同的参数指定多个参数值。例如不能对AUTO_CAPTURE_SQL_TEXT指定多个SQL文本字符串。DBA_SQL_MANAGEMENT_CONFIG视图可以用来显示当前参数值。

下面的操作假设optimizer_capture_sql_plan_baselines参数被设置为true。只要捕获sh方案所有执行的SQL语句并且想要排除包含test_only文本的语句
1.以有相关权限的用户用SQL*Plus登录数据库

[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2.为了删除对解析方案与SQL文本已经存在的任何过滤条件执行以下语句:

SQL> exec dbms_spm.configure('auto_capture_parsing_schema_name',null,true);

PL/SQL procedure successfully completed.

SQL> exec dbms_spm.configure('auto_capture_sql_text',null,true);

PL/SQL procedure successfully completed.

SQL> select parameter_name, parameter_value
  2  from dba_sql_management_config
  3  where parameter_name like '%AUTO%';

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT

3.只对sh方案所执行的语句启用自动捕获

SQL> exec dbms_spm.configure('auto_capture_parsing_schema_name','sh',true);

PL/SQL procedure successfully completed.

4.从自动捕获中排除任何包含test_only文本的语句

SQL> exec dbms_spm.configure('auto_capture_sql_text','%test_only%',false);

PL/SQL procedure successfully completed.

5.通过查询dba_sql_management_config视图来确认配置的过滤条件

SQL> col parameter_name format a32
SQL> col parameter_value format a32
SQL> select parameter_name, parameter_value
  2  from dba_sql_management_config
  3  where parameter_name like '%AUTO%';

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH)
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT            (sql_text NOT LIKE %test_only%)

禁用所有SQL Plan Baselines
当optimizer_use_sql_plan_baselines参数设置为false时,数据库不会使用任何SQL Plan Baseline。为了禁用所有SQL Plan baselines执行以下操作:
1.以有相关权限的用户用SQL*Plus登录数据库

[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

2.为了忽略所有现存的SQL Plan Baselines执行以下语句

SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*';

System altered.

SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     FALSE

管理SPM Evolve Advisor Task
SPM Evolve Advisor是一个SQL Advisor可以对最近添加到SQL Plan Baseline中的SQL Plan进行evolve。缺省情况下,SYS_AUTO_SPM_EVOLVE_TASK在调度维护窗口中每天运行。SPM Evolve Advisor Task执行以下操作:
1.定位未接受的SQL Plan
2.对所有未接受的SQL Plan进行排名
3.在维护窗口尽可能的对大量的SQL Plan进行测试执行
4.选择一个成本最低的执行计划与每个未接受的执行计划进行比较
5.使用基于成本的算法来自动接受比现有已接受的执行计划性能更好的任何未接受的执行计划

启用与禁用SPM Evolve Advisor Task
对于自动SPM Evolve Advisor Task没有单独的调度客户端存在。一个调度客户端控制着自动SQL Tuning Advisor与自动SPM Evolve Advisor。

配置自动SPM Evolve Advisor Task
通过使用dbms_spm.set_evolve_task_parameter过程来指定任务参数来配置自动SQL Plan Evolve。因为SYS_AUTO_SPM_EVOLVE_TASK任务的所有者为SYS,只有SYS用户可以设置任务参数。

dbms_spm.set_evolve_task_parameter有以下参数
alternate_plan_source:决定添加SQL Plan的搜索源:cursor_cache,automatic_workload_repository或sql_tuning_sets。可以使用+号来组合多个参数值,缺省值为cursor_cache+automatic_workload_repository

alternate_plan_baseline:决定那个替代plan应该被加载。EXISING它是缺省值,使用现有的SQL Plan baseline来为语句加载SQL plan。NEW不使用现有SQL plan baseline来为语句加载SQL plan,并且会创建一个新的SQL Plan baseline。可以使用+号来组合多个参数值。

alternate_plan_limit:指定可以加载SQL Plan的最大数量,缺省值为0。

accept_plans:指定是否自动接受建议的SQL Plan。当accept_plans设置为true(缺省值)时,SQL Plan管理自动接受由SPM Evolve Advisor Task所建议的所有SQL Plan。当设置为false时,如果找到替代的SQL plan,SPM Evolve Advisor Task会验证SQLPlan并生成一个报告,但不会evolve这个SQL plan。

下面的操作假如满足以下条件
.想要数据库自动接受SQL Plan
.想在任务每次执行1200秒后就会超时
.想要evolve任务在共享SQL区与AWR档案库中查找最多500个SQL Plan

设置自动evolve任务参数
1.以sys用户登录数据库

[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2.查询sys_auto_spm_evolve_task任务的当前参数设置情况

SQL> col parameter_name format a25
SQL> col value format a42
SQL> select parameter_name, parameter_value as "value"
  2  from dba_advisor_parameters
  3  where ( (task_name = 'SYS_AUTO_SPM_EVOLVE_TASK') and
  4  ( (parameter_name = 'ACCEPT_PLANS') or
  5  (parameter_name LIKE '%ALT%') or
  6  (parameter_name = 'TIME_LIMIT') ) );

PARAMETER_NAME            value
------------------------- ------------------------------------------
TIME_LIMIT                3600
ALTERNATE_PLAN_LIMIT      10
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              TRUE

3.使用以下PLSQL块来配置sys_auto_spm_evolve_task任务自动接收SQL plan,在共享SQL区与AWR档案库中查找最多500个SQL plan,并且在执行20分钟后任务就会超时终止。

SQL> begin
  2  dbms_spm.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'TIME_LIMIT', value => '1200');
  3  dbms_spm.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ACCEPT_PLANS', value => 'true');
  4  dbms_spm.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_LIMIT', value => '500');
  5  end;
  6  /

PL/SQL procedure successfully completed.

4.确认sys_auto_spm_evolve_task任务的当前参数设置情况

SQL> col parameter_name format a25
SQL> col value format a42
SQL> select parameter_name, parameter_value as "value"
  2  from dba_advisor_parameters
  3  where ( (task_name = 'SYS_AUTO_SPM_EVOLVE_TASK') and
  4  ( (parameter_name = 'ACCEPT_PLANS') or
  5  (parameter_name LIKE '%ALT%') or
  6  (parameter_name = 'TIME_LIMIT') ) );

PARAMETER_NAME            value
------------------------- ------------------------------------------
TIME_LIMIT                1200
ALTERNATE_PLAN_LIMIT      500
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true

显示SQL执行计划基线中的执行计划
为了查看指定SQL语句存储在SQL Plan Baseline中的SQL Plan,可以使用dbms_xplan.display_sql_plan_baseline 函数。这个函数使用存储在plan history中的执行计划信息来显示SQL Plan。它有以下参数:
sql_handle:语句的 SQL handle可以通过连接v$sql.sql_plan_baseline与dba_sql_plan_baselines.plan_name列来进行查询
plan_name:语句执行计划的名字

假设要显示SQL ID为34q7g1h49b79n的语句所存储在SQL Plan Baseline中的执行计划执行下面的语句

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500

19 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4gc64454ax64x, child number 1
-------------------------------------
select * from hr.jobs

Plan hash value: 944056911

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| JOBS |    19 |   627 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / JOBS@SEL$1

Outline Data
-------------

  /*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "JOBS"@"SEL$1")
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "JOBS"."JOB_ID"[VARCHAR2,10], "JOBS"."JOB_TITLE"[VARCHAR2,35],
       "JOBS"."MIN_SALARY"[NUMBER,22], "JOBS"."MAX_SALARY"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre
shold

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   - SQL plan baseline SQL_PLAN_bmz5xhst6b31y41975532 used for this statement


45 rows selected.



SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
  3  TABLE(
  4  DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
  5  ) t
  6  WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
  7  AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
  8  AND s.SQL_ID='4gc64454ax64x';

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_b9fcbd8632658c3e
SQL text: select * from hr.jobs
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bmz5xhst6b31y41975532         Plan id: 1100436786
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 944056911

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| JOBS |
----------------------------------

20 rows selected.

上面的结果显示SQL ID为4gc64454ax64x的执选执行计划名字叫SQL_PLAN_bmz5xhst6b31y41975532并且是被自动捕获的。

加载SQL执行计划基线
使用dbms_spm可以批量加载一组现有的执行计划到一个SQL Plan Baseline中。dbms_spm包可以从以下来源加载执行计划:
.AWR:要从AWR快照加载执行计划,那么必须指定快照开始与结束的范围,另外也可以应用过滤条件来只加载满足条件的执行计划。缺省情况下,数据库在下一次执行SQL语句时优化器就会使用加载到SQL Plan Baseline中的执行计划。

.共享SQL区:直接从共享SQL区来加载执行计划。通过对模块名,方案名或SQL ID应用过滤条件可以标识需要被捕获的SQL语句或一组SQL语句。数据库在下一次执行SQL语句时优化器就会使用加载到SQL Plan Baseline中的执行计划。当应用程序SQL已经通过手写hints进行过优化之后直接从共享SQL区中加载执行计划是非常有用的。因为你可能不能更改SQL包括hint,使用SQL Plan Baseline可以确保应用程序SQL使用最优的执行计划。

.SQL tuning set(STS):捕获SQL工作量的执行计划到一个STS中,然后加载执行计划到SQL Plan Baselines中。数据库在下一次执行SQL语句时优化器就会使用加载到SQL Plan Baseline中的执行计划。从STS中批量加载执行计划是在数据库升级后防止执行计划回归有效的方法。

.Staging table:使用dbms_spm包可以定义一个staging表,dbms_spm.pack_stgtab_baseline过程可以复制SQLPlan baseline到一个staging表中,并使用Oracle data pump将共staging表传输到另一个数据库。在目标数据库中,使用dbms_spm.unpack_stgtab_baseline过程来从staging表中把SQL plan baseline加载到SMB中。

.Stored outline:迁移stroed outlines到SQL Plan Baselines中。在迁移之后,你可以通过SQL Plan管理所提供的更高级的功能来维护相同的执行计划稳定性。

从AWR加载执行计划
假设我们要将下面的查询语句的执行计划加载到SQL Plan Baseline中,那么要确保用户sh有查询dba_hist_snapshot和dba_sql_plan_baselines视图,执行dbms_workload_repository.create_snapshot和dbms_spm.load_plans_from_awr的权限

SELECT /*LOAD_AWR*/ *
FROM sh.sales
WHERE quantity_sold > 40
ORDER BY prod_id;

为了从AWR中加载执行计划到SQL Plan Baselines中执行以下操作
1.以有相关权限的用户登录到数据库,然后查询最近生成的3个AWR快照

SQL> select *
  2  from (select instance_number,snap_id, snap_level,
  3  to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
  4  from dba_hist_snapshot
  5  order by snap_id desc)
  6  where rownum < = 3;

INSTANCE_NUMBER    SNAP_ID SNAP_LEVEL BEGIN
--------------- ---------- ---------- -----------------
              1       7061          1 14/02/19 16:00:09
              2       7061          1 14/02/19 16:00:09
              1       7060          1 14/02/19 15:00:35

2.查询sh.sales表,使用load_awr标记来识别这个SQL语句

SQL> select /*load_awr*/ *
  2  from sh.sales
  3  where quantity_sold > 40
  4  order by prod_id;

no rows selected

3.生成一个新的AWR快照

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

4.查询最近生成的3个AWR快照来确保新的AWR快照已经生成了

SQL> select *
  2  from (select instance_number,snap_id, snap_level,
  3  to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
  4  from dba_hist_snapshot
  5  order by snap_id desc)
  6  where rownum < = 3;

INSTANCE_NUMBER    SNAP_ID SNAP_LEVEL BEGIN
--------------- ---------- ---------- -----------------
              1       7062          1 14/02/19 17:00:09
              2       7062          1 14/02/19 17:00:09
              1       7061          1 14/02/19 16:00:09

5.使用最近生成的2个AWR快照来加载执行计划

SQL> variable v_plan_cnt number
SQL> exec :v_plan_cnt := dbms_spm.load_plans_from_awr(begin_snap => 7061, end_snap =>7062);

PL/SQL procedure successfully completed.

6.查询数据字典来确保load_awr语句的执行计划被加载到SQL Plan Baselines中了

SQL> col sql_handle format a20
SQL> col sql_text format a20
SQL> col plan_name format a30
SQL> col origin format a20
SQL> select sql_handle, sql_text, plan_name,
  2  origin, enabled, accepted
  3  from dba_sql_plan_baselines
  4  where sql_text like '%load_awr%';

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN               ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---

SQL_495d29c5f4612cda select /*load_awr*/  SQL_PLAN_4kr99sru62b6u54bc8843 MANUAL-LOAD-FROM-AWR YES YES
                     *
                     from sh.sales
                     where quantity_sold
                     > 40
                     order by prod_id

7.再次执行load_awr语句,查看其执行计划可以看到SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement这样的信息,说明生成的执行计划基线应用到该语句了

SQL> select /*load_awr*/ *
  2  from sh.sales
  3  where quantity_sold > 40
  4  order by prod_id;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  dybku83zppk0d, child number 1
-------------------------------------
select /*load_awr*/ * from sh.sales where quantity_sold > 40 order by
prod_id

Plan hash value: 3803407550

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |       |       |   511 (100)|          |       |       |
|   1 |  SORT ORDER BY       |       |     1 |    29 |   511   (2)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ALL|       |     1 |    29 |   510   (2)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |     1 |    29 |   510   (2)| 00:00:01 |     1 |    28 |
----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / SALES@SEL$1
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("QUANTITY_SOLD">40)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22],
       "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22],
       "SALES"."PROMO_ID"[NUMBER,22], "SALES"."QUANTITY_SOLD"[NUMBER,22],
       "SALES"."AMOUNT_SOLD"[NUMBER,22]
   2 - (rowset=256) "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22],
       "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22],
       "SALES"."PROMO_ID"[NUMBER,22], "QUANTITY_SOLD"[NUMBER,22],
       "SALES"."AMOUNT_SOLD"[NUMBER,22]
   3 - (rowset=256) "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22],
       "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22],
       "SALES"."PROMO_ID"[NUMBER,22], "QUANTITY_SOLD"[NUMBER,22],
       "SALES"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement


64 rows selected.

从共享SQL区加载执行计划
假设要从共享SQL区将下面的查询语句的执行计划加载到SQL Plan Baseline中需要执行以下操作
1.执行SQL语句

SQL> SELECT /*LOAD_CC*/ *
  2  FROM sh.sales
  3  WHERE quantity_sold > 40
  4  ORDER BY prod_id;

no rows selected

2.查询v$sql视图查询执行语句的SQL ID

SQL> SELECT SQL_ID, CHILD_NUMBER AS "Child Num",
  2  PLAN_HASH_VALUE AS "Plan Hash",
  3  OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash"
  4  FROM V$SQL
  5  WHERE SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%';

SQL_ID         Child Num  Plan Hash Opt Env Hash
------------- ---------- ---------- ------------
09x8cz4wrn655          0 3803407550   4099961812

3.从共享SQL区加载指定语句的执行计划到SQL Plan Baseline中

SQL> VARIABLE v_plan_cnt NUMBER
SQL> EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'09x8cz4wrn655');

PL/SQL procedure successfully completed.

4.查询dba_sql_plan_baselines视图来确认语句的执行计划是否加载到SQL Plan Baselines中了

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  2  ORIGIN, ENABLED, ACCEPTED
  3  FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%LOAD_CC%';

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN               ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_f6cb7f742ef93547 SELECT /*LOAD_CC*/ * SQL_PLAN_gdkvzfhrgkda754bc8843 MANUAL-LOAD-FROM-CUR YES YES
                                                                         SOR-CACHE
                     FROM sh.sales
                     WHERE quantity_sold
                     > 40
                     ORDER BY prod_id

从SQL Tuning Set中加载执行计划
一个SQL Tuning Set是一个数据库对象它包括一个或多个SQL语句,执行统计信息与执行上下文信息。假设SQLTuning Set包含下面的语句,要从SQL Tuning Set中加载该语句的执行计划到SQL Plan Baselines中要执行以下操作
1.执行SQL语句并找到其SQL ID

SQL> SELECT /*LOAD_STS*/ *
  2  FROM sh.sales
  3  WHERE quantity_sold > 40
  4  ORDER BY prod_id;

no rows selected

SQL> SELECT SQL_ID, CHILD_NUMBER AS "Child Num",
  2  PLAN_HASH_VALUE AS "Plan Hash",
  3  OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash"
  4  FROM V$SQL
  5  WHERE SQL_TEXT LIKE 'SELECT /*LOAD_STS*/%';

SQL_ID         Child Num  Plan Hash Opt Env Hash
------------- ---------- ---------- ------------
bma11r5a6r26j          0 3803407550   4099961812

2.将执行的SQL语句加载到SQL Tuning Set中

SQL> exec dbms_sqltune.create_sqlset(sqlset_name=>'sql_tuning_set');

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    cur DBMS_SQLTUNE.SQLSET_CURSOR;
  3  BEGIN
  4    OPEN cur FOR
  5      SELECT VALUE(P)
  6        FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id=''bma11r5a6r26j''',
  7                                                    NULL,
  8                                                    NULL,
  9                                                    NULL,
 10                                                    NULL,
 11                                                    1,
 12                                                    NULL,
 13                                                    'ALL')) P;
 14    DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name     => 'sql_tuning_set',
 15                             populate_cursor => cur);
 16  END;
 17  /

PL/SQL procedure successfully completed.



SQL> SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'sql_tuning_set';

SQL_TEXT
--------------------
SELECT /*LOAD_STS*/
*
FROM sh.sales
WHERE quantity_sold
> 40
ORDER BY prod_id

3.从SQL Tuning Set中加载执行计划到SQL Plan Baseline中

SQL> VARIABLE v_plan_cnt NUMBER
SQL> EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'sql_tuning_set',basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' );

PL/SQL procedure successfully completed.

basic_filter参数指定了一个where子句用来只加载需要的SQL语句,v_plan_cnt用来存储从SQL Tuning Set所加载的执行计划数。

4.查询数据字典来确保SQL Tuning Set中的语句的执行计划是否成功加载到SQL Plan Baselines中

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  2  ORIGIN, ENABLED, ACCEPTED
  3  FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%LOAD_STS%';

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN               ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_a8632bd857a4a25e SELECT /*LOAD_STS*/  SQL_PLAN_ahstbv1bu98ky54bc8843 MANUAL-LOAD-FROM-STS YES YES
                     *
                     FROM sh.sales
                     WHERE quantity_sold
                     > 40
                     ORDER BY prod_id

5.删除SQL Tuning Set

SQL> exec dbms_sqltune.drop_sqlset(sqlset_name=>'sql_tuning_set');

PL/SQL procedure successfully completed.

从Staging Table中加载执行计划
有时可能需要从一个源数据库传输最优化的执行计划到一个目标数据库那么需要执行以下操作
1.使用create_stgtab_baseline过程来创建一个staging表

SQL> BEGIN
  2  DBMS_SPM.CREATE_STGTAB_BASELINE (
  3  table_name => 'stage1');
  4  END;
  5  /

PL/SQL procedure successfully completed.

2.在源数据库中,将SQL Plan Baseline从SQL管理基础框架中打包到staging表中

DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
table_name => 'stage1'
, enabled => 'yes'
, creator => 'spm'
);
END;
/

3.将staging表stage1使用Oracle Data Pump Export导出到一个dump文件中

4.将dump文件传输到目标数据库

5.在目标数据库中,使用Oracle Data Pump Import将dump文件中的数据导入到staging表stage1中

6.在目标数据库中,将SQL Plan Baseline从staging表中解压到SQL管理基础框架中

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

SQL Plan baselines Evolve
这里将介绍如何使用命令行来evolve sql plan baselines。为了evolve一个特定的sql执行计划执行以下操作:
1.创建一个evolve任务
2.设置evolve任务参数
3.执行evolve任务
4.实现任务中给出的建议
5.显示任务执行的结果

下面将举例来说明,假设满足以下条件
.数据库没有启用自动evolve任务
.对下面的查询创建一个SQL Plan Baseline

SELECT /* q2_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_category_id =204
GROUP BY prod_name;

.想要创建两个索引来提高查询语句的性能,如果使用索引的性能比SQL Plan Baseline中的当前执行计划的性能好那么就evolve该执行计划

为了evolve一个特定的执行计划需要执行以下操作
1.执行初始化设置操作

清空共享池与缓冲区缓存

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

启用自动捕获SQL Plan Baselines

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

以sh用户登录到数据库,然后设置SQLPLUS的显示参数

[oracle@jytest1 ~]$ sqlplus sh/sh@jypdb

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:30:09 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Feb 14 2019 23:01:23 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> SET PAGES 10000 LINES 140
SQL> SET SERVEROUTPUT ON
SQL> COL SQL_TEXT FORMAT A20
SQL> COL SQL_HANDLE FORMAT A20
SQL> COL PLAN_NAME FORMAT A30
SQL> COL ORIGIN FORMAT A12
SQL> SET LONGC 60535
SQL> SET LONG 60535
SQL> SET ECHO ON

2.执行SQL语句,因此可以自动捕获它
执行下面的SQL语句

SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  2  FROM products p, sales s
  3  WHERE p.prod_id = s.prod_id
  4  AND p.prod_category_id =203
  5  GROUP BY prod_name;

PROD_NAME                                          SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
Envoy External 6X CD-ROM                                        11526
Model SM26273 Black Ink Cartridge                               15910
PCMCIA modem/fax 28800 baud                                     19278
Multimedia speakers- 3" cones                                   10969
Internal 8X CD-ROM                                              11197
Deluxe Mouse                                                    11609
Model CD13272 Tricolor Ink Cartridge                            12321
Model NM500X High Yield Toner Cartridge                          6466
18" Flat Panel Graphics Monitor                                  4415
External 8X CD-ROM                                              13886
SIMM- 8MB PCMCIAII card                                         17544
PCMCIA modem/fax 19200 baud                                     20467
Envoy External 8X CD-ROM                                        14850
Envoy External Keyboard                                          2857
External 6X CD-ROM                                              11732
Model A3827H Black Image Cartridge                              17314
Internal 6X CD-ROM                                               8533
17" LCD w/built-in HDTV Tuner                                    4874
SIMM- 16MB PCMCIAII card                                        14191
Multimedia speakers- 5" cones                                   10419
Standard Mouse                                                   8714

21 rows selected.

查询数据字典确认在SQL Plan Baseline中不存在执行计划,因为只有重复执行的SQL语句才会被捕获

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED,
  2  ACCEPTED, FIXED, AUTOPURGE
  3  FROM DBA_SQL_PLAN_BASELINES
  4  WHERE SQL_TEXT LIKE '%q1_group%';

no rows selected

再次执行SQL语句

SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  2  FROM products p, sales s
  3  WHERE p.prod_id = s.prod_id
  4  AND p.prod_category_id =203
  5  GROUP BY prod_name;

PROD_NAME                                          SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
Envoy External 6X CD-ROM                                        11526
Model SM26273 Black Ink Cartridge                               15910
PCMCIA modem/fax 28800 baud                                     19278
Multimedia speakers- 3" cones                                   10969
Internal 8X CD-ROM                                              11197
Deluxe Mouse                                                    11609
Model CD13272 Tricolor Ink Cartridge                            12321
Model NM500X High Yield Toner Cartridge                          6466
18" Flat Panel Graphics Monitor                                  4415
External 8X CD-ROM                                              13886
SIMM- 8MB PCMCIAII card                                         17544
PCMCIA modem/fax 19200 baud                                     20467
Envoy External 8X CD-ROM                                        14850
Envoy External Keyboard                                          2857
External 6X CD-ROM                                              11732
Model A3827H Black Image Cartridge                              17314
Internal 6X CD-ROM                                               8533
17" LCD w/built-in HDTV Tuner                                    4874
SIMM- 16MB PCMCIAII card                                        14191
Multimedia speakers- 5" cones                                   10419
Standard Mouse                                                   8714

21 rows selected.

3.查询数据字典来确保执行计划已经被加载到SQL Plan Baseline中了,下面的查询显示执行计划已经被接受,这意味着执行计划已经存储在SQL Plan Baselines中了。origin列显示为AUTO-CAPTURE,这意味着执行计划是被自动捕获的

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  2  ORIGIN, ENABLED, ACCEPTED, FIXED
  3  FROM DBA_SQL_PLAN_BASELINES
  4  WHERE SQL_TEXT LIKE '%q1_group%';

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN       ENA ACC FIX
-------------------- -------------------- ------------------------------ ------------ --- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =203
                     GROUP BY prod_name

4.下面对SQL语句进行解析并验证优化器是否会使用SQL Plan Baseline中的执行计划

SQL> EXPLAIN PLAN FOR
  2  SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  3  FROM products p, sales s
  4  WHERE p.prod_id = s.prod_id
  5  AND p.prod_category_id =203
  6  GROUP BY prod_name;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3535171836

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH GROUP BY        |          |
|   2 |   HASH JOIN           |          |
|   3 |    TABLE ACCESS FULL  | PRODUCTS |
|   4 |    PARTITION RANGE ALL|          |
|   5 |     TABLE ACCESS FULL | SALES    |
------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

16 rows selected.

从执行计划的Note部分可以看到SQL Plan Baseline已经应用到这个SQL语句了

5.创建两个索引用来提高上面SQL语句的性能

SQL> CREATE INDEX ind_prod_cat_name ON products(prod_category_id, prod_name, prod_id);

Index created.

SQL> CREATE INDEX ind_sales_prod_qty_sold ON sales(prod_id, quantity_sold);

Index created.

6.再次执行SQL语句,因为启用了自动捕获功能,所以新的执行计划会被加载到SQL Plan Baseline中

SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  2  FROM products p, sales s
  3  WHERE p.prod_id = s.prod_id
  4  AND p.prod_category_id =203
  5  GROUP BY prod_name;

PROD_NAME                                          SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
Envoy External 6X CD-ROM                                        11526
Model SM26273 Black Ink Cartridge                               15910
PCMCIA modem/fax 28800 baud                                     19278
Multimedia speakers- 3" cones                                   10969
Internal 8X CD-ROM                                              11197
Deluxe Mouse                                                    11609
Model CD13272 Tricolor Ink Cartridge                            12321
Model NM500X High Yield Toner Cartridge                          6466
18" Flat Panel Graphics Monitor                                  4415
External 8X CD-ROM                                              13886
SIMM- 8MB PCMCIAII card                                         17544
PCMCIA modem/fax 19200 baud                                     20467
Envoy External 8X CD-ROM                                        14850
Envoy External Keyboard                                          2857
External 6X CD-ROM                                              11732
Model A3827H Black Image Cartridge                              17314
Internal 6X CD-ROM                                               8533
17" LCD w/built-in HDTV Tuner                                    4874
SIMM- 16MB PCMCIAII card                                        14191
Multimedia speakers- 5" cones                                   10419
Standard Mouse                                                   8714

21 rows selected.

7.查询数据字典来确保新的执行计划被加载到SQL Plan Baseline中了

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
  2  FROM DBA_SQL_PLAN_BASELINES
  3  WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
  4  ORDER BY SQL_HANDLE, ACCEPTED;

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN       ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =203
                     GROUP BY prod_name

SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =203
                     GROUP BY prod_name

上面的查询结果显示新的执行计划是为被接受的。

8.再次解析SQL语句并验证优化器是不是使用原始没有索引的执行计划

SQL> EXPLAIN PLAN FOR
  2  SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  3  FROM products p, sales s
  4  WHERE p.prod_id = s.prod_id
  5  AND p.prod_category_id =203
  6  GROUP BY prod_name;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3535171836

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH GROUP BY        |          |
|   2 |   HASH JOIN           |          |
|   3 |    TABLE ACCESS FULL  | PRODUCTS |
|   4 |    PARTITION RANGE ALL|          |
|   5 |     TABLE ACCESS FULL | SALES    |
------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

16 rows selected.

上面的Note部分指示优化器使用了原始的没有索引的执行计划

9.以管理员用户登录数据库,然后创建一个evolve任务它包含未被接受执行计划相关的所有SQL语句

[oracle@jytest1 ~]$ sqlplus sys/xxzx7817600@jypdb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:48:51 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> VARIABLE cnt NUMBER
SQL> VARIABLE tk_name VARCHAR2(50)
SQL> VARIABLE exe_name VARCHAR2(50)
SQL> VARIABLE evol_out CLOB
SQL> EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_07f16c76ff893342',plan_name => 'SQL_PLAN_0gwbcfvzskcu20135fd6c');

PL/SQL procedure successfully completed.

SQL> SELECT :tk_name FROM DUAL;

:TK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_11

10.执行evolve任务

SQL>EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);
PL/SQL procedure successfully completed.

SQL>SELECT :exe_name FROM DUAL;
:EXE_NAME
---------------------------------------------------------------------------
EXEC_1

11.查看报告

EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name,
execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_11
Task Owner : SYS
Execution Name : EXEC_1
Execution Type       : SPM EVOLVE
Scope                : COMPREHENSIVE
Status               : COMPLETED
Started              : 02/15/2019 17:49:32
Finished             : 02/15/2019 17:49:35
Last Updated         : 02/15/2019 17:49:35
Global Time Limit    : 2147483646
Per-Plan Time Limit  : UNUSED
Number of Errors     : 0
---------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_0gwbcfvzskcu20135fd6c
Base Plan Name : SQL_PLAN_0gwbcfvzskcu242949306
SQL Handle : SQL_07f16c76ff893342
Parsing Schema : SH
Test Plan Creator : SH
SQL Text : SELECT /*q1_group_by*/ prod_name,
sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id=s.prod_id AND p.prod_category_id=203
GROUP BY prod_name
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ------------------------
Elapsed Time (s): .044336 .012649
CPU Time (s): .044003 .012445
Buffer Gets: 360 99
Optimizer Cost: 924 891
Disk Reads: 341 82
Direct Writes: 0 0
Rows Processed: 4 2
Executions: 5 9
FINDINGS SECTION
---------------------------------------------------------------------------
Findings (1):
-----------------------------
1. The plan was verified in 2.18 seconds. It passed the benefit criterion
because its verified performance was 2.01 times better than that of the
baseline plan.
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', object_id => 2,
task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 1
Plan Hash Value : 1117033222
---------------------------------------------------------------------------
| Id| Operation               | Name     | Rows | Bytes   |Cost | Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT        |          |   21 |     861 | 924 | 00:00:12|
| 1 |   HASH GROUP BY         |          |   21 |     861 | 924 | 00:00:12|
| *2|    HASH JOIN            |          |267996|10987836 | 742 | 00:00:09|
| *3|     TABLE ACCESS FULL   | PRODUCTS |   21 |     714 |   2 | 00:00:01|
| 4 |     PARTITION RANGE ALL |          |918843| 6431901 | 662 | 00:00:08|
| 5 |      TABLE ACCESS FULL  | SALES    |918843| 6431901 | 662 | 00:00:08|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_CATEGORY_ID"=203)
Test Plan
-----------------------------
Plan Id : 2
Plan Hash Value : 20315500
---------------------------------------------------------------------------
|Id| Operation           | Name             | Rows | Bytes  | Cost| Time   |
---------------------------------------------------------------------------
| 0|SELECT STATEMENT     |                  |    21|     861|  891|00:00:11|
| 1| SORT GROUP BY NOSORT|                  |    21|     861|  891|00:00:11|
| 2|  NESTED LOOPS       |                  |267996|10987836|  891|00:00:11|
|*3|   INDEX RANGE SCAN  |IND_PROD_CAT_NAME |    21|     714|    1|00:00:01|
|*4|   INDEX RANGE SCAN  |IND_SALES_PROD_QTY| 12762|   89334|   42|00:00:01|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("P"."PROD_CATEGORY_ID"=203)
* 4 - access("P"."PROD_ID"="S"."PROD_ID")

报告显示使用两个索引的执行计划比原始执行计划性能更好

12.实现evolve任务所给出的建议

SQL>EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.

13.查询数据字典来确保新的执行计划已经是接受状态

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
  2  FROM DBA_SQL_PLAN_BASELINES
  3  WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
  4  ORDER BY SQL_HANDLE, ACCEPTED;

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN               ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE         YES YES
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =203
                     GROUP BY prod_name


SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN               ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE         YES YES
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =203
                     GROUP BY prod_name

14.执行清除操作

SQL> VARIABLE cnt NUMBER
SQL> EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');

PL/SQL procedure successfully completed.


SQL> DELETE FROM SQLLOG$;

13 rows deleted.

SQL> commit;

Commit complete.


SQL> DROP INDEX IND_SALES_PROD_QTY_SOLD;
Index dropped.



SQL> DROP INDEX IND_PROD_CAT_NAME;

Index dropped.

删除SQL Plan Baselines
可以从SQL Plan Baselines中删除一些或所有执行计划。

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  2  ORIGIN, ENABLED, ACCEPTED
  3  FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%q3_group_by%';

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN               ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_50c02f29322b0d02 SELECT SQL_HANDLE, S SQL_PLAN_51h1g54t2q38276fe3bd1 AUTO-CAPTURE         YES YES
                     QL_TEXT, PLAN_NAME,
                     ORIGIN, ENABLED, ACC
                     EPTED
                     FROM DBA_SQL_PLAN_BA
                     SELINES WHERE SQL_TE
                     XT LIKE '%q3_group_b
                     y%'

SQL_6d39c79190585ca9 SELECT /* q3_group_b SQL_PLAN_6uff7k685hr5942949306 AUTO-CAPTURE         YES YES
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =205
                     GROUP BY prod_name

SQL_6d39c79190585ca9 SELECT /* q3_group_b SQL_PLAN_6uff7k685hr59ae9b4305 AUTO-CAPTURE         YES NO
                     y */ prod_name, sum(
                     quantity_sold)
                     FROM products p, sal
                     es s
                     WHERE p.prod_id = s.
                     prod_id
                     AND p.prod_category_
                     id =205
                     GROUP BY prod_name


SQL> DECLARE
  2  v_dropped_plans number;
  3  BEGIN
  4   v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_6d39c79190585ca9');
  5   DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  2  ORIGIN, ENABLED, ACCEPTED
  3  FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE='SQL_6d39c79190585ca9';

no rows selected

管理SQL Management Base
SQL Management Base是数据字典的一部分,它存储在SYSAUX表空间中。它存储语句日志,执行计划历史记录,SQL执行计划基线与SQL Profiles。使用dbms_spm.configure过程可以对SMB进行选项设置与维护SQL Plan Baselines。dba_sql_management_config视图可以用来查看SMB的当前配置信息。下面介绍parameter_name列可以设置的参数列表:
space_budget_percent:SQL Management Base可以使用的SYSAUX表空间的最大百分比。缺省值是10%。允许的范围是1%到50%。

plan_retention_weeks:在清除之前没有被使用的执行计划需要保留多少周,缺省值是53。

auto_capture_parsing_schema_name:它是(% LIKE a OR % LIKE b ***230;) AND (%NOT LIKE c AND % NOT LIKE d ***230;)形式的列表,它代表了解析方案名过滤。

auto_capture_module:它是(% LIKE a OR % LIKE b ***230;) AND (%NOT LIKE c AND % NOT LIKE d ***230;)形式的列表,它代表了模块过滤。

auto_capture_action:它是(% LIKE a OR % LIKE b ***230;) AND (%NOT LIKE c AND % NOT LIKE d ***230;)形式的列表,它代表了操作过滤。

auto_capture_sql_text:它是(% LIKE a OR % LIKE b ***230;) AND (%NOT LIKE c AND % NOT LIKE d ***230;)形式的列表,它代表了SQL文本过滤。

修改SMB空间使用限制
一个由SMB所调用的每周运行一次的后台进程将会检测空间使用情况。当超过定义限制时,后台进程将会写一个告警信息到alert日志文件中。数据库会每周生成一个告警信息直到SMB空间限制被增加为止,SYSAUX表空间被增加为止或者通过清除SQL Management对象(sql plan baselines或sql profiles)来减少SMB所使用的空间为止。

1.查看当前SMB所用空间的限制大小,从查询结果可以看到当前大小是SYSAUX表空间大小的10%

SQL> col parameter_name for a30
SQL> col %_LIMIT for a20
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT",
  2  ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
  3  WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
  4  PARAMETER_VALUE/100 *
  5  ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
  6  WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
  7  FROM DBA_SQL_MANAGEMENT_CONFIG
  8  WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

PARAMETER_NAME                 %_LIMIT              SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
------------------------------ -------------------- ----------------- -------------------
SPACE_BUDGET_PERCENT           10                                1260                 126

2.将SMB所用空间限制大小修改为SYSAUX表空间大小的30%

SQL> EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);

PL/SQL procedure successfully completed.

3.确认SMB所有空间限制大小是否成功被修改为SYSAUX表空间大小的30%

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT",
  2  ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
  3  WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
  4  PARAMETER_VALUE/100 *
  5  ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
  6  WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
  7  FROM DBA_SQL_MANAGEMENT_CONFIG
  8  WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

PARAMETER_NAME                 %_LIMIT              SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
------------------------------ -------------------- ----------------- -------------------
SPACE_BUDGET_PERCENT           30                                1260                 378

修改SMB中的Plan Retention Policy
每周调度清除任务来管理由SQL Plan Management所使用的空间。这个任务是一个在维护窗口内自动执行的任务。数据库会自动清除超过Plan Retention期限而没有被使用的执行计划,它是执行计划存储在SMB中的last_executed字段来标识的。缺生活上的执行计划保留周期是53周。这个周期可以设置的范围是5到523周。

1.查看当前执行计划保留周期

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
  2  FROM DBA_SQL_MANAGEMENT_CONFIG
  3  WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';

PARAMETER_NAME                 PARAMETER_
------------------------------ ----------
PLAN_RETENTION_WEEKS           53

2.修改执行计划保留周期为105周

SQL> EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);

PL/SQL procedure successfully completed.

3.确保执行计划保留周期是否成功被修改为105周

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
  2  FROM DBA_SQL_MANAGEMENT_CONFIG
  3  WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';

PARAMETER_NAME                 PARAMETER_
------------------------------ ----------
PLAN_RETENTION_WEEKS           105
Proudly powered by WordPress | Indrajeet by Sus Hill.