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 How to Generate AWRs in Active Data Guard Standby Databases

从Oralce 12.2开始, 可以对Active Data Guard(ADG) standby database捕获AWR数据。这个功能可以对ADG备库的性能问题执行分析。在下面的例子中主库与备库都是两节点的RAC(db_name=cs)。主库(db_unique_name=cs),备库(db_unique_name=cs_dg)分别运行在cs1,cs2与jytest1,jytest2节点上。

1对备库确认数据库的open mode与database role

SQL> select inst_id, open_mode, database_role from gv$database order by 1;

   INST_ID OPEN_MODE            DATABASE_ROLE
---------- -------------------- ----------------
         1 READ ONLY WITH APPLY PHYSICAL STANDBY
         2 READ ONLY WITH APPLY PHYSICAL STANDBY

2.在主库中对sys$umf用户解锁并设置密码,因为sys$umf用户默认是被锁定的。sys$umf用户是缺省的数据库用户它有访问系统级别远程管理框架(RMF)视图与表的所有权限。在RMF中所有AWR相关的操作只能通过sys$umf用户来执行。

SQL> alter user sys$umf account unlock identified by xxzx7817600;

User altered.

3.创建两个dblink,一个是从主库到备库(cs_to_cs_dg),另一个是从备库到主库(cs_dg_to_cs)。但因为ADG库是只读的,所以创建dblink的操作都需要在主库运行

SQL> create database link cs_to_cs_dg connect to sys$umf identified by xxzx7817600 using 'cs_dg';

Database link created.

SQL> create database link cs_dg_to_cs connect to sys$umf identified by xxzx7817600 using 'cs';

Database link created.

4.需要将数据库节点配置到拓朴(topology)结构中,在拓朴结构中的每个数据库节点必须被指定一个唯一名(缺省值为db_unique_name),在这个例子中主库为cs,备库为cs_dg,在主库中执行以下命令

SQL> exec dbms_umf.configure_node ('cs');

PL/SQL procedure successfully completed.

5.在备库中执行以下命令,通过备库到主库的链路名来进行注册

SQL> exec dbms_umf.configure_node ('cs_dg','CS_DG_TO_CS');

PL/SQL procedure successfully completed.

6.创建RMF拓朴,在主库中执行以下命令

SQL> exec dbms_umf.create_topology ('topology_1');

PL/SQL procedure successfully completed.

7.执行以下语句来对上面的操作进行验证

SQL> set line 132
SQL> col topology_name format a15
SQL> col node_name format a15
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
topology_1      1789571709                1 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
topology_1      cs              1789571709          0 FALSE FALSE OK

8.向拓朴中注册备库,在主库中执行下面的命令

SQL> exec dbms_umf.register_node ('topology_1', 'cs_dg', 'CS_TO_CS_DG', 'CS_DG_TO_CS', 'FALSE', 'FALSE');

PL/SQL procedure successfully completed.

9.在主库上执行下面的操作把拓扑的ADG库cs_dg的AWR service开启:

SQL> exec dbms_workload_repository.register_remote_database(node_name=>'cs_dg');

PL/SQL procedure successfully completed.

如果遇到“ORA-15766: already registered in an RMF topology” 那么要先执行DBMS_UMF.unregister_node清除注册的节点然而再次执行DBMS_UMF.register_node来注册节点

如果遇到”ORA-13519: Database id (1730117407) exists in the workload repository”那么要先执行DBMS_WORKLOAD_REPOSITORY.unregister_remote_database清除远程数据库然后再次执行
DBMS_WORKLOAD_REPOSITORY.register_remote_database注册远程数据库。

10.配置ADG的AWR功能就做完了,可以检查相关视图来验证这个拓扑配置

SQL> set line 132
SQL> col topology_name format a15
SQL> col node_name format a15
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
topology_1      1789571709                4 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
topology_1      cs              1789571709          0 FALSE FALSE OK
topology_1      cs_dg           2145432700          0 FALSE FALSE OK

SQL> select * from dba_umf_link;

TOPOLOGY_NAME   FROM_NODE_ID TO_NODE_ID LINK_NAME
--------------- ------------ ---------- ----------------------------------------
topology_1        2145432700 1789571709 CS_DG_TO_CS
topology_1        1789571709 2145432700 CS_TO_CS_DG

SQL> select * from dba_umf_service;

TOPOLOGY_NAME      NODE_ID SERVICE
--------------- ---------- -------
topology_1      2145432700 AWR

11.这时候每当默认生成AWR snapshot时,主库与ADG库会同时生成。如果要手工生成ADG的AWR快照,可以在主库中执行下面的命令(参数值为ADG库的node_id或node_name)

SQL> exec dbms_workload_repository.create_remote_snapshot(2145432700);

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('cs_dg');

PL/SQL procedure successfully completed.

至少要执行两次命令来得到快照的begin_snap和end_snap。如果遇到 “ORA-13516: AWR Operation failed:Remote source not registered for AWR” 那么需要对主库执行alter system switch logfile命令
再切换日志文件两到三次。

12.如果要生成ADG库的AWR report,可以执行下面的操作:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt. Default value is 'html'.

   'html'          HTML format (default)
   'text'          Text format
   'active-html'   Includes Performance Hub active report

Enter value for report_type: html



Type Specified: html





Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1386528187     1      CS           cs1          cs1.jy.net
  1386528187     2      CS           cs2          cs2.jy.net
  2145432700     1      CS           cs1          jytest1.jydb
  2145432700     2      CS           cs2          jytest2.jydb

Enter value for dbid: 2145432700 这里需要指定备库的dbid
Using 2145432700 for database Id
Enter value for inst_num: 2  这里指定备库的实例ID
Using 2 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

cs2          CS                   1  24 Jan 2019 23:50    1
                                  2  24 Jan 2019 23:51    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_1_2.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/cs_dg_1_2.html

Using the report name /home/oracle/cs_dg_1_2.html

下面是一个ADG库的AWR report的例子,可以看到数据库的Role为PHYSICAL STANDBY。

Oracle 12.2在Active Dataguard上引入AWR功能,可以让Active dataguard的数据库性能诊断更加容易。

Oracle 12.2 使用Database Link优化Standby Database Workload

从Oracle 12.2开始,可以通过在database_link_to参数来指定一个数据库链路来优化备库workload。由于安全原因,Oracle建议使用私有数据库链路。这个链路必须属于sys用户并且能让有权限的用户来访问。Oracle数据库包含一个缺省的权限用户名为SYS$UMF。

在主库中使用sys用户创建表t1与配置sys$umf用户,这个用户默认是被锁定的,所以需要先解锁这个用户并设置密码:

SQL> alter user sys$umf account unlock identified by xxzx7817600;

User altered.

SQL>create table t1 as select * from user_tables;
Table created.

SQL> create index idx_t1_tablename on t1(table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats('sys','t1',degree => 4,cascade => true);

PL/SQL procedure successfully completed.

创建一个备库(cs_dg)到主库的链路,由于ADG是只读的,所以创建数据库链路操作都需要在主库中执行:

SQL> create database link cs_dg2_to_cs2 connect to sys$umf identified by abcd1234 using   'CS';

Database link created.

在备库中验证数据库链路是否创建成功

 col owner for a20
SQL> col object_name for a50
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                OBJECT_NAME
-------------------- --------------------------------------------------
SYS                  CS_DG2_TO_CS2
SYS                  SYS_HUB

SQL>  select table_name from user_tables@cs_dg2_to_cs2;

no rows selected

SQL> select sysdate from dual@cs_dg2_to_cs2;

SYSDATE
------------
24-JAN-19

在备库中对表t1执行查询,database_link_to参数用来指定备库到主库的数据链路名cs_dg2_to_cs2

SQL> variable tname varchar2(30);
SQL> variable query varchar2(500);
SQL> exec :tname := 'jy_task';

PL/SQL procedure successfully completed.

SQL> exec :query := 'select /*+ full(t)*/ col1 from t1 t where table_name=''T1''';

PL/SQL procedure successfully completed.

SQL> begin
  2  :tname := dbms_sqltune.create_tuning_task
  3  (
  4  sql_text => :query,
  5  task_name => :tname,
  6  database_link_to => 'CS_DG2_TO_CS2',
  7  scope=>'COMPREHENSIVE',
  8  time_limit=>60,
  9  description=>'Task to tune a query_on adg');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task(:tname);

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task(:tname) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------

Oracle 12C Statistics on Column Groups

Statistics on Column Groups
单个列统计信息对于判断where子句中的单个谓词的选择性是非常有用的。然而,当where子句中包含来自相同表的不同列的多个谓词时,单个列统计信息不能显示列之间的关系。使用列组(column group)就是用来解决这个问题的。优化器单独计算谓词的选择性,然后合并它们。然而,如果在单列之间存在关联,那么优化器当评估基数时不会考虑它,优化器会使用每个表谓词的选择性来乘以行数来评估基数。

下面的语句查询dba_tab_col_statistics表来显示关于sh.customers表中列cust_state_province与country_id列的统计信息。

SQL> COL COLUMN_NAME FORMAT a20
SQL> COL NDV FORMAT 999
SQL> SELECT COLUMN_NAME, NUM_DISTINCT AS "NDV", HISTOGRAM
  2  FROM DBA_TAB_COL_STATISTICS
  3  WHERE OWNER = 'SH'
  4  AND TABLE_NAME = 'CUSTOMERS'
  5  AND COLUMN_NAME IN ('CUST_STATE_PROVINCE', 'COUNTRY_ID');

COLUMN_NAME           NDV HISTOGRAM
-------------------- ---- ---------------
CUST_STATE_PROVINCE   145 FREQUENCY
COUNTRY_ID             19 FREQUENCY

下面的语句查询住在California的客户人数3341人:

SQL> SELECT COUNT(*)
  2  FROM sh.customers
  3  WHERE cust_state_province = 'CA';

  COUNT(*)
----------
      3341

来显示查询state为CA,country_id为52790(USA)的客户人数的查询执行

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM sh.customers
  4  WHERE cust_state_province = 'CA'
  5  AND country_id=52790;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1115 |   205K|   423   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |  1115 |   205K|   423   (1)| 00:00:01 |
-------------------------------------------------------------------------------

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

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

   1 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)

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


17 rows selected.

基于单列country_id和cust_state_province列的统计信息,优化器评估住在USA的California的客户人数是1115,而实际上有3341人,但优化器不知道,所以通过所有谓词减少了返回的行数因此大大降低了评估基数。可以通过收集列组统计信息来让优化器知晓列country_id与cust_state_province之间的真实关系。

自动与手动列组统计信息
Oracle数据库可以自动或手动创建列组统计信息。优化器可以使用SQL执行计划指令来生成更优的执行计划。如果dbms_stats引用参数auto_stat_extensions被设置为ON(缺省值为OFF),那么SQL执行计划指令基于工作量中谓词的使用情况可以自动触发来创建列组统计信息。可以通过set_table_prefs,set_global_prefs或set_schema_prefs过程来设置auto_stat_extensions。

当想要手动管理列组统计信息时,可以使用dbms_stats来执行以下操作:
.探测列组
.创建以前探测到的列组
.手动创建列组并收集列组统计信息

列组统计信息用户接口
有几个dbms_stats程序单元有与列组相关的引用参数
seed_col_usage过程,迭代指定工作量中的SQL语句,编译它们,然后查看在这些语句谓词中出现列的使用信息。为了决定合适的列组,数据库必须观察一个有代表性的工作量。在监控期间不需要运行查询本身。可以对在工作量中那些运行时间长的查询执行explain plan来确保数据库记录这些查询所使用的列组信息。

report_col_usage函数,生成一个报告列出在工作量中所看到的过滤谓词,连接谓词与group by子句中的列。可以使用这个函数来检查对于指定表所记录的列使用信息。

create_extended_stats函数,创建扩展,它可以是列组或表达式。当用户手动或自动统计信息收集任务对表收集统计信息时数据库会对扩展收集统计信息。

auto_stat_extensions引用参数,控制自动创建扩展,包括列组,当优化器统计信息被收集时,使用set_table_prefs,set_schema_prefs或set_global_prefs来设置这个引用参数。当auto_stat_extensions被设置为off(缺省值)时,数据库不会自动创建列组统计信息。为了创建扩展,你必须执行create_extended_stats函数或在dbms_stats API中的method_opt参数中显性指定扩展统计信息。当auto_stat_extensions设置为ON时,一个SQL执行计划指令基于工作量中谓词中列的使用信息可以触发自动创建列组统计信息。

为特定的工作量检测有用的列组
可以使用dbms_stats.seed_col_usage与report_col_usage来基于特定工作量来决定那个表需要列组。当你不知道需要创建什么样的扩展统计信息时这种技术很有用。这种技术对于扩展统计信息不会工作。

假设存在以下情况:
.查询sh.customers_test表(用customers表来创建)并在谓词中使用了country_id与cust_state_province列但基数评估不正确。

.想要数据库监控工作量5分钟(300秒)。

.想要数据库自动判断需要那些列组。

为了检测列组需要执行以下操作:
1.启动SQL*Plus或SQL Developer,并以用户sh登录数据库

2.创建表customers_test并收集统计信息:

SQL> DROP TABLE customers_test;

Table dropped.

SQL> CREATE TABLE customers_test AS SELECT * FROM customers;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

PL/SQL procedure successfully completed.

3.启用工作量监控
在不同的SQL*Plus会话中,以sys用户登录并执行以下的PL/SQL程序来启用监控300秒:

SQL> BEGIN
  2  DBMS_STATS.SEED_COL_USAGE(null,null,300);
  3  END;
  4  /

PL/SQL procedure successfully completed.

4.以用户sh来在使用工作量的情况下对两个查询解析它们的执行计划。

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM customers_test
  4  WHERE cust_city = 'Los Angeles'
  5  AND cust_state_province = 'CA'
  6  AND country_id = 52790;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156

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

8 rows selected.

SQL> EXPLAIN PLAN FOR
  2  SELECT country_id, cust_state_province, count(cust_city)
  3  FROM customers_test
  4  GROUP BY country_id, cust_state_province;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |  1949 |
|   1 |  HASH GROUP BY     |                |  1949 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

第一个执行计划显示基数为1而查询返回932行记录,第二个执行计划显示基数为1949而查询返回145行记录。

5.可选操作,检查对表customers_test所记录的列使用信息

SQL> SET LONG 100000
SQL> SET LINES 120
SQL> SET PAGES 0
SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
  2  FROM DUAL;
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
.........................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###############################################################################

在上面的报告中,前三个列是第一个监控查询中等值谓词中所使用的三个列:

...
WHERE cust_city = 'Los Angeles'
AND cust_state_province = 'CA'
AND country_id = 52790;

所有三个列出现在相同的where子句中,因此报告显示他们作为一组。在第二个查询中,两个列出现在group by子句中,因此报告标记它们作为group_by。在filter与group_by中的列组就是列组的候选者。

在工作量监控下创建所检测到的列组
可以使用dbms_stats.create_extended_stats函数来为执行dbms_stats.seed_col_usage所检测到的列组来创建列组,具体操作如下:
1.基于在监控窗口期间所捕获到的列使用信息来为customers_test表创建列组,执行下面的查询

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
###############################################################################

EXTENSIONS FOR SH.CUSTOMERS_TEST
................................

1. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID)   : SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###############################################################################

数据库将为customers_test表创建两个列组:一个列组是过滤谓词,一个列组是group by操作。

2.重新收集表统计信息

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

PL/SQL procedure successfully completed.

3.以用户sh来查询user_tab_col_statistics视图来判断数据库创建了那些额外统计信息:

SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME = 'CUSTOMERS_TEST'
  4  ORDER BY 1;

COUNTRY_ID                                                   19 FREQUENCY
CUST_CITY                                                   620 HYBRID
CUST_CITY_ID                                                620 NONE
CUST_CREDIT_LIMIT                                             8 NONE
CUST_EFF_FROM                                                 1 NONE
CUST_EFF_TO                                                   0 NONE
CUST_EMAIL                                                 1699 NONE
CUST_FIRST_NAME                                            1300 NONE
CUST_GENDER                                                   2 NONE
CUST_ID                                                   55500 NONE
CUST_INCOME_LEVEL                                            12 NONE
CUST_LAST_NAME                                              908 NONE
CUST_MAIN_PHONE_NUMBER                                    51344 NONE
CUST_MARITAL_STATUS                                          11 NONE
CUST_POSTAL_CODE                                            623 NONE
CUST_SRC_ID                                                   0 NONE
CUST_STATE_PROVINCE                                         145 FREQUENCY
CUST_STATE_PROVINCE_ID                                      145 NONE
CUST_STREET_ADDRESS                                       49900 NONE
CUST_TOTAL                                                    1 NONE
CUST_TOTAL_ID                                                 1 NONE
CUST_VALID                                                    2 NONE
CUST_YEAR_OF_BIRTH                                           75 NONE
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                              145 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N                              620 HYBRID

25 rows selected.

上面的查询显示了由dbms_stats.create_extended_stats函数所返回的两个列组名。为CUST_CITY, CUST_STATE_PROVINCE和COUNTRY_ID列所创建的列组有一个HYBRID类型的直方图统计信息。

4.再次解析之前的两个查询语句的执行计划

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM customers_test
  4  WHERE cust_city = 'Los Angeles'
  5  AND cust_state_province = 'CA'
  6  AND country_id = 52790;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |   874 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |   874 |
----------------------------------------------------

8 rows selected.

SQL> EXPLAIN PLAN FOR
  2  SELECT country_id, cust_state_province, count(cust_city)
  3  FROM customers_test
  4  GROUP BY country_id, cust_state_province;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |   145 |
|   1 |  HASH GROUP BY     |                |   145 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

第一个查询评估的基数是874,要返回的记录数是932,第二个查询评估的基数是145,要返回的记录数是145,这样基数评估的记录数与实际返回的记录已经非常接近了,这就是列组统计信息所带来的好处。

手动创建与收集列组统计信息
在有些情况下,可能知道想要创建的列组。dbms_stats.gather_table_stats函数的method_opt参数可以自动创建与收集列组统计信息。可以通过使用for columns来指定列组从而来创建一个新的列组。

假设存在以下情况:
.想要对sh.customers表上的cust_state_province与country_id列创建列组。

.想要对sh.customers表与新的列组收集统计信息。

手动创建与收集列组统计信息执行以下操作:
1.启动SQL*Plus并以sh用户登录数据库。

2.使用以下PL/SQL程序来创建列组并收集统计信息:

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers',
  3  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
  4  'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
  5  END;
  6  /

PL/SQL procedure successfully completed.

显示列组信息
为了获得列组名,可以使用dbms_stats.show_extended_stats_name函数或数据库视图。也可以使用视图来获得信息比如,distinct值的数量与列组是否有直方图统计信息。
1.启动SQL*Plus并以sh用户登录数据库。

2.为了获得列组名,执行以下PL/SQL程序

SQL> SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 'sh','customers',
  2  '(cust_state_province,country_id)' ) col_group_name
  3  FROM DUAL;

COL_GROUP_NAME
------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

查询user_stat_extensions视图

SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM USER_STAT_EXTENSIONS
  3  WHERE TABLE_NAME='CUSTOMERS';
EXTENSION_NAME                                                                                                                   EXTENSION
-----------------------------------------------------------------------                                                          ------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                                                                                                   ("CUST_STATE_PROVINCE","COUNTRY_ID")

3.查询创建的列组的distinct值的数量并查看是否创建了直方图

SQL> SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
  2  FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
  3  WHERE e.EXTENSION_NAME=t.COLUMN_NAME
  4  AND e.TABLE_NAME=t.TABLE_NAME
  5  AND t.TABLE_NAME='CUSTOMERS';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
----------------------------------------------------------------------           ------------ ---------
("CUST_STATE_PROVINCE","COUNTRY_ID")                                                      145 FREQUENCY

删除列组
可以使用dbms_stats.drop_extended_stats函数来从表中删除列组

SQL> BEGIN
  2  DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers',
  3  '(cust_state_province, country_id)' );
  4  END;
  5  /

PL/SQL procedure successfully completed.
Proudly powered by WordPress | Indrajeet by Sus Hill.