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服务器进程正等待从客户端进程接收信息,那么在这两者之间存在问题。那么大概问题区域就是网络与客户端进程了。

服务器字符集对DM7中List分区表使用中文的影响

1.在Linux平台上使用disql工具创建List分区表sales时对分区键指定英文字符时,当指定多个值时可以创建成功

SQL> CREATE TABLE sales
2   (
3   sales_id INT,
4   saleman CHAR(20),
5   saledate DATETIME,
6   city CHAR(10)
7   )
8   PARTITION BY LIST(city)
9   (
10  PARTITION p1 VALUES ('a','b'),
11  PARTITION p2 VALUES ('c','d','e'),
12  PARTITION p3 VALUES ('f','g'),
13  PARTITION p4 VALUES ('h','i')
14  );
warning: List partition not include default,partition may be not located
executed successfully
used time: 115.412(ms). Execute id is 2094.

2.在Linux平台上使用disql工具创建List分区表sales时对分区键指定中文时,当指定多个列表值时创建报错

SQL> drop table sales;
executed successfully
used time: 307.935(ms). Execute id is 2095.

SQL> CREATE TABLE sales
2   (
3   sales_id INT,
4   saleman CHAR(20),
5   saledate DATETIME,
6   city CHAR(50)
7   )
8   PARTITION BY LIST(city)
9   (
10  PARTITION p1 VALUES ('北京','天津'),
11  PARTITION p2 VALUES ('上海','南京','杭州'),
12  PARTITION p3 VALUES ('武汉','长沙'),
13  PARTITION p4 VALUES ('广州','深圳')
14  );
CREATE TABLE sales
(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(50)
)
PARTITION BY LIST(city)
(
PARTITION p1 VALUES ('北京','天津'),
PARTITION p2 VALUES ('上海','南京','杭州'),
PARTITION p3 VALUES ('武汉','长沙'),
PARTITION p4 VALUES ('广州','深圳')
);

PARTITION p2 VALUES ('上海','南京','杭州'),
                             *             
line 11, column 30, nearby [藝娴穄 has error[-2007]:
Syntax error.
used time: 0.328(ms). Execute id is 0.

3.在Linux平台上使用disql工具创建List分区表sales时对分区键指定中文时,当指定一个列表值时创建成功

SQL> CREATE TABLE sales
2   (
3   sales_id INT,
4   saleman CHAR(20),
5   saledate DATETIME,
6   city CHAR(10)
7   )
8   PARTITION BY LIST(city)
9   (
10  PARTITION p1 VALUES ('北京'),
11  PARTITION p2 VALUES ('上海'),
12  PARTITION p3 VALUES ('武汉'),
13  PARTITION p4 VALUES ('广州')
14  );
warning: List partition not include default,partition may be not located
executed successfully
used time: 19.809(ms). Execute id is 2096.

4.在win平台使用disql工具创建List分区表sales时对分区键指定中文时,当指定多个列表值时创建成功

disql V7.1.6.48-Build(2018.03.01-89507)ENT
SQL> conn jy/abcd@10.10.10.1:5236

服务器[10.10.10.1:5236]:处于普通打开状态
登录使用时间: 14.001(毫秒)
SQL> drop table sales;
操作已执行
已用时间: 352.729(毫秒). 执行号:2118.
SQL> CREATE TABLE SALES(
2   SALES_ID INT,
3   SALEMAN CHAR(20),
4   SALEDATE DATETIME,
5   CITY CHAR(10)
6   )
7   PARTITION BY LIST(CITY)
8   SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE(
9   SUBPARTITION P11 VALUES LESS THAN ('2012-04-01'),
10  SUBPARTITION P12 VALUES LESS THAN ('2012-07-01'),
11  SUBPARTITION P13 VALUES LESS THAN ('2012-10-01'),
12  SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE))
13  (
14  PARTITION P1 VALUES ('北京','天津')
15  (
16  SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01'),
17  SUBPARTITION P11_2 VALUES EQU OR LESS THAN (MAXVALUE)
18  ),
19  PARTITION P2 VALUES ('上海','南京','杭州'),
20  PARTITION P3 VALUES (DEFAULT)
21  );
操作已执行
已用时间: 26.710(毫秒). 执行号:2119.
SQL>

5.在Linux平台上使用管理工具创建List分区表sales时对分区键指定中文时,当指定多个列表值时创建也能成功

6.查看操作系统字符集为UTF-8

[root@shard1 /]# locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

7.修改操作系统字符集

[root@shard1 /]# vi /etc/locale.conf
#LANG="en_US.UTF-8"
LANG="zh_CN.gb2312"

[root@shard1 ~]# locale
LANG=zh_CN.gb2312
LC_CTYPE="zh_CN.gb2312"
LC_NUMERIC="zh_CN.gb2312"
LC_TIME="zh_CN.gb2312"
LC_COLLATE="zh_CN.gb2312"
LC_MONETARY="zh_CN.gb2312"
LC_MESSAGES="zh_CN.gb2312"
LC_PAPER="zh_CN.gb2312"
LC_NAME="zh_CN.gb2312"
LC_ADDRESS="zh_CN.gb2312"
LC_TELEPHONE="zh_CN.gb2312"
LC_MEASUREMENT="zh_CN.gb2312"
LC_IDENTIFICATION="zh_CN.gb2312"
LC_ALL=

8.当修改字符集后在Linux平台使用disql工具创建List分区表sales时对分区键指定中文时,当指定多个列表值时创建成功

[dmdba@shard1 bin]$ ./disql jy/abcd@10.10.10.1:5236

Server[10.10.10.1:5236]:mode is normal, state is open
login used time: 9.677(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT 
Connected to: DM 7.1.6.46
SQL> drop table sales;
executed successfully
used time: 351.233(ms). Execute id is 2120.
SQL> CREATE TABLE SALES(
2   SALES_ID INT,
3   SALEMAN CHAR(20),
4   SALEDATE DATETIME,
5   CITY CHAR(10)
6   )
7   PARTITION BY LIST(CITY)
8   SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE(
9   SUBPARTITION P11 VALUES LESS THAN ('2012-04-01'),
10  SUBPARTITION P12 VALUES LESS THAN ('2012-07-01'),
11  SUBPARTITION P13 VALUES LESS THAN ('2012-10-01'),
12  SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE))
13  (
14  PARTITION P1 VALUES ('北京','天津')
15  (
16  SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01'),
17  SUBPARTITION P11_2 VALUES EQU OR LESS THAN (MAXVALUE)
18  ),
19  PARTITION P2 VALUES ('上海','南京','杭州'),
20  PARTITION P3 VALUES (DEFAULT)
21  );
executed successfully
used time: 22.411(ms). Execute id is 2121.

从上面的测试来看,字符集对List分区表使用中文存在影响。

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 …) AND (%NOT LIKE c AND % NOT LIKE d …)形式的列表,它代表了解析方案名过滤。

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

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

auto_capture_sql_text:它是(% LIKE a OR % LIKE b …) AND (%NOT LIKE c AND % NOT LIKE d …)形式的列表,它代表了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

Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12c

这里将介绍如何使用命令行来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.

Disable the Evolve Job SYS_AUTO_SPM_EVOLVE_TASK in Oracle 12c

为了禁用自动SPM EVOLVE TASK需要执行以下操作
1. 查看自动SPM Evolve Task是否启用

SQL> COL CLIENT_NAME FORMAT a20
SQL>
SQL> SELECT CLIENT_NAME, STATUS
  2  FROM   DBA_AUTOTASK_CLIENT
  3  WHERE  CLIENT_NAME = 'sql tuning advisor';

CLIENT_NAME          STATUS
-------------------- --------
sql tuning advisor   ENABLED

2.禁用自动SPM Evolve Task

SQL> BEGIN
  2    DBMS_AUTO_TASK_ADMIN.DISABLE (
  3      client_name => 'sql tuning advisor'
  4  ,   operation   => NULL
  5  ,   window_name => NULL
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SELECT CLIENT_NAME, STATUS
  2  FROM   DBA_AUTOTASK_CLIENT
  3  WHERE  CLIENT_NAME = 'sql tuning advisor';

CLIENT_NAME          STATUS
-------------------- --------
sql tuning advisor   DISABLED

3. 查看SPM Evolve Task的参数设置

SQL> SELECT parameter_name, parameter_value
  2  FROM dba_advisor_parameters
  3  WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
  4  AND parameter_value != 'UNUSED'
  5  ORDER BY parameter_name;

PARAMETER_NAME                                                                   PARAMETER_VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
ACCEPT_PLANS                                                                     true
ALTERNATE_PLAN_BASELINE                                                          EXISTING
ALTERNATE_PLAN_LIMIT                                                             0
ALTERNATE_PLAN_SOURCE                                                            CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
DAYS_TO_EXPIRE                                                                   UNLIMITED
DEFAULT_EXECUTION_TYPE                                                           SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE                                                         30
JOURNALING                                                                       INFORMATION
MODE                                                                             COMPREHENSIVE
TARGET_OBJECTS                                                                   1
TIME_LIMIT                                                                       3600
_SPM_VERIFY                                                                      TRUE

12 rows selected

4. 关闭Automatic Evolving of Baselines

SQL> BEGIN
  2  DBMS_SPM.set_evolve_task_parameter(
  3  task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
  4  parameter => 'ACCEPT_PLANS',
  5  value => 'FALSE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value
  2  FROM dba_advisor_parameters
  3  WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
  4  AND parameter_value != 'UNUSED'
  5  ORDER BY parameter_name;

PARAMETER_NAME                                                                   PARAMETER_VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
ACCEPT_PLANS                                                                     FALSE
ALTERNATE_PLAN_BASELINE                                                          EXISTING
ALTERNATE_PLAN_LIMIT                                                             0
ALTERNATE_PLAN_SOURCE                                                            CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
DAYS_TO_EXPIRE                                                                   UNLIMITED
DEFAULT_EXECUTION_TYPE                                                           SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE                                                         30
JOURNALING                                                                       INFORMATION
MODE                                                                             COMPREHENSIVE
TARGET_OBJECTS                                                                   1
TIME_LIMIT                                                                       3600
_SPM_VERIFY                                                                      TRUE

12 rows selected

5. 启用Automatic Evolving of Baselines

SQL> BEGIN
  2  DBMS_SPM.set_evolve_task_parameter(
  3  task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
  4  parameter => 'ACCEPT_PLANS',
  5  value => 'TRUE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parameter_name, parameter_value
  2  FROM dba_advisor_parameters
  3  WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
  4  AND parameter_value != 'UNUSED'
  5  ORDER BY parameter_name;

PARAMETER_NAME                                                                   PARAMETER_VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
ACCEPT_PLANS                                                                     true
ALTERNATE_PLAN_BASELINE                                                          EXISTING
ALTERNATE_PLAN_LIMIT                                                             0
ALTERNATE_PLAN_SOURCE                                                            CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
DAYS_TO_EXPIRE                                                                   UNLIMITED
DEFAULT_EXECUTION_TYPE                                                           SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE                                                         30
JOURNALING                                                                       INFORMATION
MODE                                                                             COMPREHENSIVE
TARGET_OBJECTS                                                                   1
TIME_LIMIT                                                                       3600
_SPM_VERIFY                                                                      TRUE

12 rows selected

Oracle 12.2 ORA-01113 ORA-01110 recover after normal shutdown

Oracle Linux 7.1数据库为Oracle 12.2.0.1 RAC,数据库启用了归档,在手动关闭数据库后启动数据库时提示PDB库有数据文件需要进行介质恢复

[grid@jytest1 ~]$ srvctl stop database -db jy
[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.FRA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest2                  169.254.237.250 88.8
                                                             8.88.2,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  STABLE
      3        ONLINE  OFFLINE                               STABLE

ora.cvu
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.jy.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.jy.jy_srv.svc
      1        OFFLINE OFFLINE                               STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest2                  Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
--------------------------------------------------------------------------------

提示PDB的数据文件需要进行介质恢复,文件号是38

[grid@jytest1 ~]$ srvctl start database -db jy
PRCR-1079 : Failed to start resource ora.jy.db
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest2' failed
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest1' failed
CRS-2632: There are no more servers to try to place resource 'ora.jy.db' on that would satisfy its placement policy

对38号文件执行介质恢复

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 17:47:55 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1610616096 bytes
Database Buffers          520093696 bytes
Redo Buffers                7979008 bytes
Database mounted.
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'


SQL> recover datafile 38;
Media recovery complete.

在对38号文件进行介质恢复后打开CDB时提示39号文件也需要进行介质恢复

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery
ORA-01110: data file 39:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649'

如是执行recover database命令来对CDB进行介质恢复并打开CDB

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

在打开JYPDB时提示PDB的45号文件需要进行介质恢复

SQL> alter pluggable database jypdb open;
alter pluggable database jypdb open
*
ERROR at line 1:
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'

如是对45号文件进行介质恢复,虽然介质恢复成功但在打开jypdb时仍然提示需要进行介质恢复

SQL>  recover datafile 45;
Media recovery complete.

SQL> alter pluggable database jypdb open;
alter pluggable database jypdb open
*
ERROR at line 1:
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'

如是查询数据文件检查点SCN与数据文件头SCN,发现44与45号文件的数据文件检查点SCN与数据文件头SCN号不一致并且数据文件检查点SCN比数据文件头SCN号大,因此需要进行日志文件来进行恢复。但在执行recover datafile命令成功后,打开JYPDB时仍然提示需要进行介质恢复。

SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1          176117998
         3          176117998
         4          176117998
         5            1449535      1449535
         6            1449535      1449535
         7          176117998
         8            1449535      1449535
         9          176117998
        38          176098593    176098593
        39          176098593    176098593
        40          176098593    176098593

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
        41          176098593    176098593
        42          176098593    176098593
        43          176098593    176098593
        44          176098593    175898322
        45          176098593    175898322
        46           21664676     21664676
        47           21664676     21664676
        48           21664676     21664676
        49           21664676     21664676
        50           21664676     21664676
        51           21664676     21664676

22 rows selected.

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1          176117998
         3          176117998
         4          176117998
         5            1449535
         6            1449535
         7          176117998
         8            1449535
         9          176117998
        38          176098593
        39          176098593
        40          176098593

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        41          176098593
        42          176098593
        43          176098593
        44          175898322
        45          175898322
        46           21664676
        47           21664676
        48           21664676
        49           21664676
        50           21664676
        51           21664676

22 rows selected.

如是打算重建控制文件再执行介质恢复,下面先备份控制文件到跟踪文件,在跟踪文件中有重建控制文件的相关命令

SQL> alter database backup controlfile to trace as '/tmp/ctl.txt';

Database altered.

关闭RAC数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

将RAC数据库启动到nomount状态

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1610616096 bytes
Database Buffers          520093696 bytes
Redo Buffers                7979008 bytes

重建控制文件,提示数据库不是排他模式,也就是说在RAC环境不能重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705'  SIZE 200M BLOCKSIZE 512,
 12    GROUP 6 (
 13      '+DATA/JY/ONLINELOG/group_6.280.972435899',
 14      '+FRA/JY/ONLINELOG/group_6.354.972435909'
 15    ) SIZE 200M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/JY/DATAFILE/system.317.962209603',
 19    '+DATA/JY/DATAFILE/sysaux.298.962209605',
 20    '+DATA/JY/DATAFILE/undotbs1.277.962209605',
 21    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
 22    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
 23    '+DATA/JY/DATAFILE/users.301.962209605',
 24    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
 25    '+DATA/JY/DATAFILE/undotbs2.312.962209605',
 26    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
 27    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
 28    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
 29    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
 30    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
 31    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
 32    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
 33    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783',
 34    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
 35    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
 36    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
 37    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
 38    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
 39    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409'
 40  CHARACTER SET ZHS16GBK
 41  ;
CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

修改数据库为排他模式

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string      jy
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

重启数据库到nomount状态

SQL> shtudown immediate
SP2-0734: unknown command beginning "shtudown i..." - rest of line ignored.
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1459621152 bytes
Database Buffers          671088640 bytes
Redo Buffers                7979008 bytes

重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705'  SIZE 200M BLOCKSIZE 512,
 12    GROUP 6 (
 13      '+DATA/JY/ONLINELOG/group_6.280.972435899',
 14      '+FRA/JY/ONLINELOG/group_6.354.972435909'
 15    ) SIZE 200M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/JY/DATAFILE/system.317.962209603',
 19    '+DATA/JY/DATAFILE/sysaux.298.962209605',
 20    '+DATA/JY/DATAFILE/undotbs1.277.962209605',
 21    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
 22    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
 23    '+DATA/JY/DATAFILE/users.301.962209605',
 24    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
 25    '+DATA/JY/DATAFILE/undotbs2.312.962209605',
 26    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
 27    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
 28    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
 29    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
 30    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
 31    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
 32    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
 33    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783',
 34    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
 35    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
 36    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
 37    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
 38    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
 39    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409'
 40  CHARACTER SET ZHS16GBK
 41  ;

Control file created.

如是查询数据文件检查点SCN与数据文件头SCN,现在44与45号文件的数据文件检查点SCN与数据文件头SCN号仍然不一致并且数据文件检查点SCN比数据文件头SCN号大

SQL> col name for a100
SQL> select name,checkpoint_change# from v$datafile_header;

NAME                                                                                                 CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
+DATA/JY/DATAFILE/system.317.962209603                                                                        176144167
+DATA/JY/DATAFILE/sysaux.298.962209605                                                                        176144167
+DATA/JY/DATAFILE/undotbs1.277.962209605                                                                      176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675                                         1449535
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675                                         1449535
+DATA/JY/DATAFILE/users.301.962209605                                                                         176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675                                       1449535
+DATA/JY/DATAFILE/undotbs2.312.962209605                                                                      176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649                                     176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649                                        176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609                                         176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353                                          175898322
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783                                           175898322
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409                                      21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409                                         21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409                                          21664676

22 rows selected.

SQL> select name,checkpoint_change# from v$datafile;

NAME                                                                                                 CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
+DATA/JY/DATAFILE/system.317.962209603                                                                        176144167
+DATA/JY/DATAFILE/sysaux.298.962209605                                                                        176144167
+DATA/JY/DATAFILE/undotbs1.277.962209605                                                                      176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675                                       176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675                                       176144167
+DATA/JY/DATAFILE/users.301.962209605                                                                         176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675                                     176144167
+DATA/JY/DATAFILE/undotbs2.312.962209605                                                                      176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649                                     176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649                                        176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609                                         176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353                                          176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783                                           176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409                                     176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409                                        176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409                                         176144167

22 rows selected.

对CDB执行介质恢复操作,根据提示输入相关的归档日志文件名来进行恢复并将CDB成功打开

SQL> recover database
ORA-00279: change 175898322 generated at  needed for thread 2


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/JY/ARCHIVELOG/2019_02_13/thread_2_seq_1073.422.1000144183
ORA-00279: change 176098395 generated at 02/13/2019 17:49:43 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1074_961976319.dbf
ORA-00280: change 176098395 for thread 2 is in sequence #1074


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/JY/ARCHIVELOG/2019_02_13/thread_2_seq_1074.382.1000144185
ORA-00279: change 176098399 generated at 02/13/2019 17:49:45 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1075_961976319.dbf
ORA-00280: change 176098399 for thread 2 is in sequence #1075


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/arch/2_1075_961976319.dbf
ORA-00279: change 176098399 generated at 02/13/2019 17:49:45 needed for thread 1
ORA-00289: suggestion : +TEST/arch/1_1182_961976319.dbf
ORA-00280: change 176098399 for thread 1 is in sequence #1182


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/arch/1_1182_961976319.dbf
ORA-00279: change 176098401 generated at 02/13/2019 17:49:45 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1076_961976319.dbf
ORA-00280: change 176098401 for thread 2 is in sequence #1076


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/arch/2_1076_961976319.dbf
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file '+TEST/arch/2_1076_961976319.dbf' no longer needed for this recovery


ORA-00279: change 176098489 generated at 02/13/2019 17:49:50 needed for thread 1
ORA-00289: suggestion : +TEST/arch/1_1183_961976319.dbf
ORA-00280: change 176098489 for thread 1 is in sequence #1183


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/arch/1_1183_961976319.dbf
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

将PDB数据库打开

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          READ WRITE NO

根据备份控制文件到跟踪文件重建控制文件的命令来添加临时表空间的相关文件

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/TEMPFILE/temp.299.961976339';

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865'  SIZE 67108864  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = JYPDB;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.967852391' REUSE;

Tablespace altered.

将数据库设置为集群模式并关闭数据库

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

使用集群命令来启动数据库

[grid@jytest1 ~]$ srvctl start database -db jy

将JYPDB以读写方式打开

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 22:30:53 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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          MOUNTED
SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          READ WRITE NO


[oracle@jytest2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 22:31:17 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 pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          MOUNTED
SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          READ WRITE NO

到此数据库的恢复操作就完成了