OTN 虚拟技术峰会上机操作

这个 VirtualBox 映像就包含上机练习所需的全部软件和指南。该映像压缩后大小约为 4.7GB（新映像即将发布，发布后大小将更新），因此您应先下载 VirtualBox 并导入映像。

上机操作说明

我们将通过一个非常简单的查询使用一个简单的示例表。我们将先对未建立索引的列运行查询，这将返回全表扫描结果。然后，我们将在该列上添加一个索引，看看是否仍然执行全表扫描并添加一个新的基线，其状态为未接受。我们将生成一个发展报告，最终发展成新基线，删除旧基线。

在开发人员 VM 中，以 pmuser/oracle 身份运行以下命令并收集统计信息。您可以从命令行或 SQLDeveloper GUI 工具使用 sqlplus。

[oracle@localhost ~]$ sqlplus pmuser/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 12 09:48:13 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1@ORCL> create table t as select * from dba_objects;

Table created.

PDB1@ORCL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('PMUSER');

PL/SQL procedure successfully completed.

第 1 步：验证 OPTIMIZER_USE_SQL_BLAN_BASELINES 是否设置为 true（默认值）

PDB1@ORCL> show parameter baselines


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

第 2 步：为此会话启用自动捕获，运行一条语句两次，并再次禁用自动捕获。

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> variable var42 varchar2(42);
PDB1@ORCL> exec :var42 := 'PMUSER';

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

现在，我们应得到该 sql 的基线：

PDB1@ORCL> set linesize 300
PDB1@ORCL> column sql_handle format a20
PDB1@ORCL> column plan_name format a42
PDB1@ORCL> column sql_text format a42
PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO 

注意，现在该语句有了一条基线，并自动设置为 ACCEPTED。现在我们创建一个索引，启用自动捕获重新运行查询，通过索引扫描收集新基线。

PDB1@ORCL> create index t_idx on t (owner);

Index created.

PDB1@ORCL> exec dbms_stats.gather_schema_stats ('PMUSER');

PL/SQL procedure successfully completed.

PDB1@ORCL> alter system flush shared_pool;

System altered.

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

检查用于执行查询的计划，并注意解释计划介绍的基线使用方法：

PDB1@ORCL> set pagesize 1000
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 364z0straymuv, child number 0
-------------------------------------
select count(*) from t where owner = :var42

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time         |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |      |      |       | 426 (100)  |              |
| 1 | SORT AGGREGATE    |      | 1    | 6     |            |              |
|* 2 | TABLE ACCESS FULL| T    | 5    | 30    | 426 (1)    | 00:00:01     |
---------------------------------------------------------------------------

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

2 - filter("OWNER"=:VAR42)

Note
-----
- SQL plan baseline SQL_PLAN_arrxanznkdmsa3fdbb376 used for this statement


23 rows selected.

检查新建但未接受的基线的基线表。注意标题描述中是否接受计划。

PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42 YES NO  NO

看看该 sql 句柄的两个执行计划：

PDB1@ORCL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_abdfaaa7e926cf0a'));

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

--------------------------------------------------------------------------------
SQL handle: SQL_abdfaaa7e926cf0a
SQL text: select count(*) from t where owner= :var42
--------------------------------------------------------------------------------

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

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |          | 1    | 6     | 426 (1)    | 00:00:01 |
| 1 | SORT AGGREGATE     |          | 1    | 6     |            |          |
|* 2 | TABLE ACCESS FULL | T        | 3143 | 18858 | 426 (1)    | 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("OWNER"=:VAR42)

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

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation       | Name     | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 1    | 6     | 1 (0)      | 00:00:01  |
| 1 | SORT AGGREGATE   |          | 1    | 6     |            |           |
|* 2| INDEX RANGE SCAN | T_IDX    | 5    | 30    | 1 (0)      | 00:00:01  |
---------------------------------------------------------------------------

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

2 - access("OWNER"=:VAR42)

47 rows selected.

创建发展报告，但将 commit 设置为 no，这样实际上并不会更改 ACCEPTED 标志，如下所示：

  set serveroutput on
  declare evolve_out CLOB;
  begin
  evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'NO' );
  dbms_output.put_line(evolve_out);
  end; 
/

报告应如下所示：

GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

Task Information:
---------------------------------------------
Task Name : TASK_21
Task Owner : PMUSER

Execution Name : EXEC_131
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/03/2014 10:03:15
Finished : 07/03/2014 10:03:16
Last Updated :
07/03/2014 10:03:16
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_arrxanznkdmsaded8ae2f
Base Plan Name : SQL_PLAN_arrxanznkdmsa3fdbb376
SQL Handle : SQL_abdfaaa7e926cf0a
Parsing Schema : PMUSER

Test Plan Creator : PMUSER
SQL Text : select count(*) from t where owner= :var42

Bind Variables:
-----------------------------
1 - (VARCHAR2(128)): PMUSER


Execution Statistics:
-----------------------------
                     Base Plan                    Test Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    .00099                       .000002
CPU Time (s):        .000489                       0
Buffer Gets:         153                           0
Optimizer Cost:      426                           1
Disk Reads:          0                             0

Direct Writes:       0                             0
Rows Processed:      0                             0
Executions:          10                            10


FINDINGS
SECTION
---------------------------------------------------------------------------------------------

Findings (1):
-----------------------------
1. The plan was verified in 0.29000 seconds. It passed the benefit criterion
because its verified performance was 767.74440 times better than that of
the baseline plan.

Recommendation:
-----------------------------
Consider accepting the plan.


EXPLAIN PLANS
SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
Plan Id :         1
Plan Hash Value : 1071362934

----------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost | Time         |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT   |      | 1    | 6     | 426  | 00:00:01     |
| 1 | SORT AGGREGATE     |      | 1    | 6     |      |              |
| * 2| TABLE ACCESS FULL | T    | 5    | 30    | 426  | 00:00:01     |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("OWNER"=:VAR42)


Test Plan
-----------------------------
Plan Id : 2
Plan
Hash Value : 3738742319

----------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes  | Cost | Time       |
----------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1   |  6     | 1    | 00:00:01   |
| 1 | SORT AGGREGATE      |       | 1   |  6     |      |            |
| * 2 | INDEX RANGE SCAN  | T_IDX | 5   | 30     | 1    | 00:00:01   |
----------------------------------------------------------------------

Predicate Information (identified by operation
id):
------------------------------------------
* 2 - access("OWNER"=:VAR42)

---------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

检查之后，再次运行发展报告，但这次将 commit 设置为 yes，以实际发展新基线。

set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'YES' ); 
dbms_output.put_line(evolve_out); 
end; 
/

再次检查基线表，注意现在两个计划都已接受。

PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                       SQL_TEXT                                    ENA ACC FIX
--------------------  ------------------------------  ------------------------------------------  --- --- ---
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42  YES YES NO
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42  YES YES NO

我们来验证从现在起将新计划用于查询：

PDB1@ORCL> set autotrace on
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5


Execution Plan
----------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1    | 6     | 8 (0)      | 00:00:01  |
| 1  | SORT AGGREGATE     |       | 1    | 6     |            |           |
|* 2 | INDEX RANGE SCAN   |T_IDX  | 3143 | 18858 | 8 (0)      | 00:00:01  |
---------------------------------------------------------------------------

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

2 - access("OWNER"=:VAR42)

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

最后一步将是考虑如何处置原始基线。由于现在两个计划都已接受，两个都可以执行，这对于启用自适应游标共享而实际上以全表扫描计划更佳的情况可能很有用。但在本例中，我决定删除旧基线：

 declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => 'SQL_abdfaaa7e926cf0a',  
 plan_name => 'SQL_PLAN_arrxanznkdmsa3fdbb376'); 
 dbms_output.put_line(drop_result);    
 end; 
/

本上机操作演示了如何使用自动捕获为查询创建 SQL 计划基线。它演示了如何即使在添加索引之后，实际上也只使用接受的基线（使用全表扫描），直至检查和发展新的基线。

关于作者

Bjoern Rost 是 portrix Systems 的联合创始人。Portrix Systems 是一家服务提供商和咨询公司，重点关注服务器、存储、Oracle Linux 和 Solaris、Real Application Cluster 数据库等 Oracle 技术。他喜欢与软件开发人员合作，紧密集成现有 Oracle 特性，监管大量云或 SaaS 平台的运行，并在高可用性、性能调优以及操作系统和数据库管理方面提供咨询。他乐于分享知识，经常在世界各地用户组会议上发表演讲，遍及五大洲，累积逾 42 场次。他还是 IOUG 的 RAC 特殊兴趣小组的欧洲主席。