签到成功

知道了

CNDBA社区CNDBA社区

Oracle 性能优化 之 扩展统计信息

2021-01-25 16:10 2744 0 原创 Oracle 19c
作者： dave
本文链接：http://www.cndba.cn/cndba/dave/article/4367

1 扩展统计信息说明

统计信息是Oracle 生产执行计划的重要依据，保证统计信息正确也是DBA 的一项重要工作。 关于统计信息的说明，可以参考我之前的博客：

Oracle Statistic 统计信息 小结
http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

Oracle 判断 并 手动收集 统计信息 脚本
https://www.cndba.cn/dave/article/1558

在上篇讲用SQL Monitoring 分析SQL 语句时， 如果定位到时关联列的统计信息不准，就需要创建扩展统计信息来解决问题。

Oracle 19c 性能优化 之 Real-Time SQL Monitoring
https://www.cndba.cn/dave/article/4366

扩展统计信息是Oracle 11g引入的特性, 该特性实际上是基于表达式或一组列创建一个隐藏列，叫做扩展（extension），再在扩展列（隐藏列）上收集统计信息和直方图。

扩展统计信息主要用来收集: 关联列 或者 表达式 的扩展统计信息。扩展统计信息使用的是选择率，从而使优化器可以正确地计算这些谓词的选择性。因此，优化器可以了解正确选择性（基数）。

1）关联列：

在实际数据中，表中的两个或多个列之间通常存在一个关联。例如，职务和薪酬相关联，或者汽车品牌和价格相关联。到目前为止，优化器还没有办法了解这些关系存在于表中的列之间。针对具有多个单列谓词的表执行查询时，优化器无法计算这些谓词的正确选择性，因为它无法确定这些列是否相关。

2) 表达式：

同样让优化器为应用了函数的列计算正确选择性也极其困难。例如 UPPER(surname)=CNDBA。

2 操作示例

2.1 创建测试表并收集统计信息

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Mon Jan 25 14:13:49 2021

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DAVE                           READ WRITE NO
         4 USTC                           READ WRITE NO
SQL> alter session set container=dave;

Session altered.

SQL> create user dave identified by dave;

User created.

SQL> grant connect,resource,dba to dave;

Grant succeeded.

SQL> create table dave.dave as select * from dba_objects;

Table created.
--把object_name 更新为和object_type一样,用于测试.
SQL> update dave.dave set object_name=object_type;

72636 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> exec dbms_stats.gather_table_stats(OWNNAME =>'DAVE',TABNAME =>'DAVE');

PL/SQL procedure successfully completed.

2.2 查看执行计划

我们这里查看的是真实的执行计划，注意里面的SQL。 http://www.cndba.cn/cndba/dave/article/4367

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Mon Jan 25 14:13:49 2021

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> 
SQL> alter session set statistics_level=all;

Session altered.

SQL> select * from dave.dave where object_name='INDEX' and object_type='INDEX';


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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  1x9gukh6kd3qf, child number 1
-------------------------------------
select * from dave.dave where object_name='INDEX' and object_type='INDEX'

Plan hash value: 3458767806

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   2915 |00:00:00.01 |    1603 |
|*  1 |  TABLE ACCESS FULL| DAVE |      1 |     33 |   2915 |00:00:00.01 |    1603 |

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

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

   1 - filter(("OBJECT_NAME"='INDEX' AND "OBJECT_TYPE"='INDEX'))


18 rows selected.

SQL>

此时我们通过执行计划可以发现预估的是33行数据，但实际上是2915 行。 存在较大出入。 如果是生产环境，数据量更大一些，那么该SQL 的性能就会较差。 解决方案就是对这2个列创建扩展统计信息。http://www.cndba.cn/cndba/dave/article/4367

2.3 创建扩展统计信息

收集多列扩展统计信息http://www.cndba.cn/cndba/dave/article/4367

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Mon Jan 25 14:13:49 2021

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> 
SQL> exec dbms_stats.gather_table_stats(OWNNAME =>'DAVE',TABNAME =>'DAVE',method_opt =>'for columns(object_name,object_type)');

PL/SQL procedure successfully completed.

再次查看执行计划，预估值和实际值就相等了：

SQL> select * from dave.dave where object_name='INDEX' and object_type='INDEX';

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  85rj255u8y1kx, child number 0
-------------------------------------
select * from dave.dave where object_name='INDEX' and
object_type='INDEX'

Plan hash value: 3458767806

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   2915 |00:00:00.01 |    1606 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| DAVE |      1 |   2915 |   2915 |00:00:00.01 |    1606 |
------------------------------------------------------------------------------------

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

   1 - filter(("OBJECT_NAME"='INDEX' AND "OBJECT_TYPE"='INDEX'))


19 rows selected.

SQL>

查询多列扩展统计信息

http://www.cndba.cn/cndba/dave/article/4367 

SQL> col EXTENSION_NAME for a40
SQL> col extension for a50
SQL> Select extension_name, extension from dba_stat_extensions where table_name='DAVE';

EXTENSION_NAME                           EXTENSION
---------------------------------------- --------------------------------------------------
SYS_STUQLXR85D5QYPFTPP96K8HAPD           ("OBJECT_NAME","OBJECT_TYPE")

SQL>

查看distinct数和柱状图使用情况

SQL> col col_group for a40
SQL> select e.extension col_group, t.num_distinct, t.histogram from dba_stat_extensions e, dba_tab_col_statistics t where e.extension_name = t.column_name and e.table_name = t.table_name and t.table_name = 'DAVE';

COL_GROUP                                NUM_DISTINCT HISTOGRAM
---------------------------------------- ------------ ---------------
("OBJECT_NAME","OBJECT_TYPE")                      45 FREQUENCY

SQL>

2.4 验证虚拟列

在前面提到扩展统计信息会创建虚拟列，我们这先查询DDL:http://www.cndba.cn/cndba/dave/article/4367

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Mon Jan 25 14:13:49 2021

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> 
SQL> set long 100000
SQL> set pages 0
SQL> select dbms_metadata.get_ddl('TABLE','DAVE','DAVE') from dual;

  CREATE TABLE "DAVE"."DAVE"
   (    "OWNER" VARCHAR2(128),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(128),
        "OBJECT_ID" NUMBER,
……
        "CREATED_VSNID" NUMBER,
        "MODIFIED_APPID" NUMBER,
        "MODIFIED_VSNID" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


SQL>

DDL 并没有该列的的信息。 但通过USER_TAB_COLS 可以查看到该列：SYS_STUQLXR85D5QYPFTPP96K8HAPD。

http://www.cndba.cn/cndba/dave/article/4367
http://www.cndba.cn/cndba/dave/article/4367

SQL> col data_default for a55
SQL> SELECT COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN, DATA_DEFAULT  FROM USER_TAB_COLS  WHERE TABLE_NAME = 'DAVE';

COLUMN_NAME                              DATA_TYPE            HID DATA_DEFAULT
---------------------------------------- -------------------- --- -------------------------------------------------------
OWNER                                    VARCHAR2             NO
OBJECT_NAME                              VARCHAR2             NO
SUBOBJECT_NAME                           VARCHAR2             NO
OBJECT_ID                                NUMBER               NO
……
MODIFIED_APPID                           NUMBER               NO
MODIFIED_VSNID                           NUMBER               NO
SYS_STUQLXR85D5QYPFTPP96K8HAPD           NUMBER               YES SYS_OP_COMBINED_HASH("OBJECT_NAME","OBJECT_TYPE")

27 rows selected.

通过对比可以验证扩展统计信息是基于虚拟列来操作的，但还不等同与虚拟列，至少在表的DDL 中是看不到该列的存在。

3 扩展列统计信息操作小结

3.1 创建扩展统计信息

扩展统计信息的创建其实有两种方法，第一种方法就是我们上文使用的，直接收集：

SQL> exec dbms_stats.gather_table_stats(OWNNAME =>'DAVE',TABNAME =>'DAVE',method_opt =>'for columns(object_name,object_type)');
SQL> exec dbms_stats.gather_table_stats(OWNNAME =>'DAVE',TABNAME =>'DAVE',method_opt =>'for columns(UPPER(STATUS))');

收集时如果没有扩展统计信息，会自动创建扩展统计并收集统计数据.

另一种方法就是先创建， 在收集：

--基于函数的：
SQL> select dbms_stats.create_extended_stats(ownname=> 'DAVE',tabname=>'DAVE',extension=>'(upper(OBJECT_NAME))') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'DAVE',TABNAME=>'DAVE',EXTENSION=>'(UPPER(OBJECT_NAME))')
----------------------------------------------------------------------------------------------------------------------------------
SYS_STUUZF$GSUU#20JQDJBSQ5DK_Y

SQL> 

--基于关联列：
SQL> select dbms_stats.create_extended_stats(ownname=> 'DAVE',tabname=>'DAVE',extension=>'(OWNER,OBJECT_NAME)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'DAVE',TABNAME=>'DAVE',EXTENSION=>'(OWNER,OBJECT_NAME)')
----------------------------------------------------------------------------------------------------------------------------------
SYS_STUO1U7YI38X#Q9QU_P6R6XP9A

SQL>

然后收集统计信息 ; 

SQL> exec dbms_stats.gather_table_stats('dave','dave',method_opt =>'for all columns size auto');

3.2 查看column group name:

[dave@www.cndba.cn admin]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Mon Jan 25 15:49:11 2021

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

Last Successful login time: Mon Jan 25 2021 15:49:11 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> 
SQL> select dbms_stats.show_extended_stats_name('dave','dave','(upper(OBJECT_NAME))') from dual;

DBMS_STATS.SHOW_EXTENDED_STATS_NAME('DAVE','DAVE','(UPPER(OBJECT_NAME))')
--------------------------------------------------------------------------------
SYS_STUUZF$GSUU#20JQDJBSQ5DK_Y

3.3 查询多列扩展统计信息

SQL> col EXTENSION_NAME for a40
SQL> col extension for a50
SQL> set lin 130
SQL> select extension_name, extension from dba_stat_extensions where table_name='DAVE';

EXTENSION_NAME                           EXTENSION
---------------------------------------- --------------------------------------------------
SYS_STUQLXR85D5QYPFTPP96K8HAPD           ("OBJECT_NAME","OBJECT_TYPE")
SYS_STUUZF$GSUU#20JQDJBSQ5DK_Y           (UPPER("OBJECT_NAME"))
SYS_STUO1U7YI38X#Q9QU_P6R6XP9A           ("OWNER","OBJECT_NAME")
SYS_STULU6GNN8SCWSGC2AUGN7PPFA           (UPPER("STATUS"))

3.4 收集多列统计信息

收集指定列组的统计信息：http://www.cndba.cn/cndba/dave/article/4367http://www.cndba.cn/cndba/dave/article/4367

SQL> exec dbms_stats.gather_table_stats(OWNNAME =>'DAVE',TABNAME =>'DAVE',method_opt =>'for columns(object_name,object_type)');

收集所有已存在的列组统计信息

http://www.cndba.cn/cndba/dave/article/4367 

SQL> exec dbms_stats.gather_table_stats('dave','dave',method_opt =>'for all columns size auto');

3.5 删除Column Group

SQL> exec dbms_stats.drop_extended_stats('dave','dave','(UPPER("STATUS"))');

PL/SQL procedure successfully completed.

SQL> select extension_name, extension from dba_stat_extensions where table_name='DAVE';

EXTENSION_NAME                           EXTENSION
---------------------------------------- --------------------------------------------------
SYS_STUQLXR85D5QYPFTPP96K8HAPD           ("OBJECT_NAME","OBJECT_TYPE")
SYS_STUUZF$GSUU#20JQDJBSQ5DK_Y           (UPPER("OBJECT_NAME"))
SYS_STUO1U7YI38X#Q9QU_P6R6XP9A           ("OWNER","OBJECT_NAME")

SQL>

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2296
    原创
  • 3
    翻译
  • 582
    转载
  • 198
    评论
  • 访问：8515151次
  • 积分：4477
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ