手把手教你21c新特性(3)：性能及高可用新特性

作者: admin 分类: 公众号存档 0 次浏览发布时间: 2022-05-17 23:59

【转载声明】本文为转载文章，原作者「Dr.Henry」，版权归原作者所有。　原文链接：https://mp.weixin.qq.com/s/w_TIcSB1ogSZpPdAbUsmAw

公众号：杨建荣的学习笔记 · 作者：Dr.Henry · 发布：2022-05-17 23:59:30 · 原文链接

于提高性能和高可用性的增强功能。具体包括：

通过自动区域映射允许，基于查询中的谓词修剪块和分区，无需任何用户干预
压缩SecureFile LOB回收空间并提高性能
通过Automatic In-Memory 自动动态创建内存对象
通过内存混合扫描，自动选择最佳方法来扫描内存中和非内存中列数据的记录。进而将性能提高几个数量级
使用新的初始化参数 MAX_IDLE_BLOCKER_TIME 终止阻塞会话

实验1：使用自动区域映射

在这个实验中将为您介绍如何启用自动区域映射，以及如何在无人干预的情况下，为所有用户表创建和维护自动区域映射。关于自动区域映射，在2020年5月5日的20c新特性公开课中，我已经为大家做了介绍。感兴趣的朋友可以去B站检索当时的公开课视频。通过这项技术可以透明、自动地提高查询性能，而无需管理开销。如下图所示，通过这项技术可以大量减少数据的扫描量，从而将查询性能提升几个数量级。

这项技术在19c当中已经为大家提供，但那时需要DBA手工指定，在21c当中，创建和维护区域映射可以自动完成。

在这个实验中，我们首先查询几次sales_zm表，然后在统计信息中的一致性读的计数。比如在下面，我们执行了两次查询，得到的一致性读计数都为15370。

SQL> SET AUTOTRACE ON STATISTICSQL> SELECT COUNT(DISTINCT sale_id) FROM sales_zm WHERE customer_id = 50;
COUNT(DISTINCTSALE_ID)----------------------                   100

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets      15370  consistent gets          0  physical reads       7084  redo size        582  bytes sent via SQL*Net to client         52  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed
SQL> SELECT COUNT(DISTINCT sale_id) FROM sales_zm WHERE customer_id = 50;
COUNT(DISTINCTSALE_ID)----------------------                   100

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets      15370  consistent gets          0  physical reads       7084  redo size        582  bytes sent via SQL*Net to client         52  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

在没有启动自动区域映射之前，和之前版本的数据库一样，如果要对某个表设置区域映射，需要手工完成。这里需要注意的是，因为当前的表中已经有数据了，所以我们在对表启用区域映射之后，对它使用move，进行“重整”。

SQL> ALTER TABLE sales_zm ADD CLUSTERING BY LINEAR ORDER (customer_id) WITH MATERIALIZED ZONEMAP;
Table altered.
SQL> ALTER TABLE sales_zm MOVE;
Table altered.

这回我们再次执行之前的相同查询，观察一致性读的计数，我们会发现由原来的15370，降低到1096，性能提升了14倍。

SQL> SELECT COUNT(DISTINCT sale_id) FROM sales_zm WHERE customer_id = 50;
COUNT(DISTINCTSALE_ID)----------------------                   100

Statistics----------------------------------------------------------         70  recursive calls          7  db block gets       1096  consistent gets          0  physical reads       1308  redo size        582  bytes sent via SQL*Net to client         52  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          2  sorts (memory)          0  sorts (disk)          1  rows processed

如果想了解哪些表已经创建区域映射，可以通过如下语句进行查询。

SQL> select FACT_TABLE,ZONEMAP_NAME  FROM dba_zonemaps;
FACT_TABLE           ZONEMAP_NAME-------------------- --------------------SALES_ZM             ZMAP$_SALES_ZM

如果想让系统自动为新创建的表开启区域映射功能，可以使用DBMS包来完成，让我们通过如下实验来验证，首先，我们将之前创建的表删除，然后开启系统自动区域映射，然后再创建表，在表中的数据稳定之后，我们先收集一下表上面的统计值。

SQL> DROP TABLE sales_zm PURGE;
Table dropped.
SQL> SELECT zonemap_name, automatic, partly_stale, incomplete    FROM   dba_zonemaps;  
no rows selected
SQL> EXEC DBMS_AUTO_ZONEMAP.CONFIGURE('AUTO_ZONEMAP_MODE','ON')
PL/SQL procedure successfully completed.
SQL>  CREATE TABLE sales_zm (sale_id NUMBER(10), customer_id NUMBER(10));
Table created.
SQL> DECLARE  i NUMBER(10);BEGIN  FOR i IN 1..80  LOOP    INSERT /*+ APPEND */ INTO sales_zm    SELECT ROWNUM, MOD(ROWNUM,1000)    FROM   dual    CONNECT BY LEVEL <= 100000;    COMMIT;  END LOOP;END;/ 
PL/SQL procedure successfully completed.
SQL>  EXEC dbms_stats.gather_table_stats(ownname=>NULL, tabname=>'SALES_ZM')
PL/SQL procedure successfully completed.

接下来，让我们执行与之前相同的查询，看看一致性读的计数。

SQL> SELECT COUNT(DISTINCT sale_id) FROM sales_zm WHERE customer_id = 50;
COUNT(DISTINCTSALE_ID)----------------------                   100

Statistics----------------------------------------------------------         11  recursive calls         11  db block gets      15365  consistent gets      15280  physical reads       1964  redo size        582  bytes sent via SQL*Net to client         52  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

通过观察，我们发现一致性读的计数很高，似乎区域映射没有起作用。这是因为区域映射将在后台延迟执行，如果您迫不及待想看到区域映射的结果，可以使用sys用户通过如下语句进行刷新。

SQL> exec sys.dbms_auto_zonemap_internal.zmap_execute;
PL/SQL procedure successfully completed.

这回再让我们看看查询结果。

SQL> SELECT zonemap_name, automatic, partly_stale, incomplete    FROM   dba_zonemaps;
ZONEMAP_NAME         AUTOMATIC PARTLY_STALE INCOMPLETE-------------------- --------- ------------ ------------ZMAP$_SALES_ZM       YES       NO           NO

如果想了解系统创建自动区域映射的情况，可以通过DBA_ZONEMAP_AUTO_ACTIONS进行查询。

SQL> SELECT task_id, msg_id, action_msg  FROM dba_zonemap_auto_actions;
   TASK_ID     MSG_ID ACTION_MSG---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------         7        188 BS:Current execution task id: 7 Execution name: SYS_ZMAP_2022-04-11/07:19:13 Task Name: ZMAP_TASK1         7        189 BS:******** Zonemap Background Action Report for Task ID: 7 ****************         7        190 BS:****** End of Zonemap Background Action Report for Task ID: 7 **********         7        164 BS:Current execution task id: 7 Execution name: SYS_ZMAP_2022-04-11/07:17:04 Task Name: ZMAP_TASK1         7        165 BS:******** Zonemap Background Action Report for Task ID: 7 ****************         7        166 TP:Trying to create zonemap on table: SALES owner:SH         7        167 AL:Block count : 16384, sample percent is : 3.051758         7        168 TP:col name:AMOUNT_SOLD: clustering ratio: .28         7        169 TP:col name:CHANNEL_ID: clustering ratio: 1         7        170 TP:col name:CUST_ID: clustering ratio: .14         7        171 TP:col name:PROD_ID: clustering ratio: .97         7        172 TP:col name:PROMO_ID: clustering ratio: .2         7        173 TP:col name:QUANTITY_SOLD: clustering ratio: 1         7        174 TP:col name:TIME_ID: clustering ratio: .03         7        175 TP:Candidate column list:AMOUNT_SOLD,CUST_ID,PROMO_ID,TIME_ID         7        176 TP:New zonemap name: ZMAP$_SALES         7        177 TP:Creating new zonemap ZMAP$_SALES on table SALES owner SHtable space USERS         7        178 BS:succesfully created zonemap: ZN:ZMAP$_SALES BT:SALES SN:SH CL:AMOUNT_SOLD,CUST_ID,PROMO_ID,TIME_ID CT:+00 00:00:00.836940 TS:2022-04-11/07:17:06 DP:8         7        179 TP:Trying to create zonemap on table: SALES_ZM owner:SALES         7        180 AL:Block count : 15280, sample percent is : 3.272251         7        181 TP:col name:CUSTOMER_ID: clustering ratio: .99         7        182 TP:col name:SALE_ID: clustering ratio: .03         7        183 TP:Candidate column list:SALE_ID         7        184 TP:New zonemap name: ZMAP$_SALES_ZM         7        185 TP:Creating new zonemap ZMAP$_SALES_ZM on table SALES_ZM owner SALEStable space SYSTEM         7        186 BS:succesfully created zonemap: ZN:ZMAP$_SALES_ZM BT:SALES_ZM SN:SALES CL:SALE_ID CT:+00 00:00:01.527002 TS:2022-04-11/07:17:07 DP:8         7        187 BS:****** End of Zonemap Background Action Report for Task ID: 7 **********
27 rows selected.

显示自动任务运行的活动报告的另一种方法是使用DBMS_AUTO_ZONEMAP.ACTIVITY_REPORT 函数。

SQL> SELECT dbms_auto_zonemap.activity_report(systimestamp-2, systimestamp, 'TEXT') FROM dual;
DBMS_AUTO_ZONEMAP.ACTIVITY_REPORT(SYSTIMESTAMP-2,SYSTIMESTAMP,'TEXT')--------------------------------------------------------------------------------/orarep/autozonemap/main%3flevel%3d GENERAL SUMMARY------------------------------------------------------------------------------- Activity Start    09-APR-2022 07:28:11.000000000 +00:00 Activity End      11-APR-2022 07:28:11.359120000 +00:00 Total Executions  2-------------------------------------------------------------------------------

EXECUTION SUMMARY------------------------------------------------------------------------------- zonemaps created                      2 zonemaps compiled                     0 zonemaps dropped                      0 Stale zonemaps complete refreshed     0 Partly stale zonemaps fast refreshed  0 Incomplete zonemaps fast refreshed    0-------------------------------------------------------------------------------

NEW ZONEMAPS DETAILS------------------------------------------------------------------------------- Zonemap         Base Table  Schema  Operation time  Date created         DOP  Column list ZMAP$_SALES     SALES       SH      00:00:00.83     2022-04-11/07:17:06  8    AMOUNT_SOLD,CUST_ID,PROMO_ID,TIME_ID ZMAP$_SALES_ZM  SALES_ZM    SALES   00:00:01.52     2022-04-11/07:17:07  8    SALE_ID-------------------------------------------------------------------------------

ZONEMAPS MAINTENANCE DETAILS------------------------------------------------------------------------------- Zonemap  Previous State  Current State  Refresh Type  Operation Time  Dop  Date Maintained-------------------------------------------------------------------------------

FINDINGS------------------------------------------------------------------------------- Execution Name  Finding Name  Finding Reason  Finding Type  Message

可以通过如下查询了解所有执行中创建了多少区域映射。

SQL> SELECT * FROM dba_zonemap_auto_actionsWHERE action_msg LIKE '%succesfully created zonemap:%' ORDER BY TIME_STAMP;  
   TASK_ID     MSG_ID EXEC_NAME                                                                                                                        ACTION_MSG                                            TIME_STAMP---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------         7        178 SYS_ZMAP_2022-04-11/07:17:04                                                                                                     BS:succesfully created zonemap: ZN:ZMAP$_SALES BT:SALES SN:SH CL:AMOUNT_SOLD,CUST_ID,PROMO_ID,TIME_ID CT:+00 00:00:00.836940 TS:2022-04-11/07:17:06 DP:8                                               11-APR-22 07.17.06.000000000 AM         7        186 SYS_ZMAP_2022-04-11/07:17:04                                                                                                     BS:succesfully created zonemap: ZN:ZMAP$_SALES_ZM BT:SALES_ZM SN:SALES CL:SALE_ID CT:+00 00:00:01.527002 TS:2022-04-11/07:17:07 DP:8

接下来我们对数据表进行大量的更新，然后观察系统对区域映射的维护情况。PARTLY_STALE下方出现了Yes。

8000 rows updated.

8000 rows updated.

8000 rows updated.

8000 rows updated.

Commit complete.SQL> SELECT zonemap_name, automatic, partly_stale, incomplete    FROM   dba_zonemaps;  2
ZONEMAP_NAME         AUTOMATIC PARTLY_STALE INCOMPLETE-------------------- --------- ------------ ------------ZMAP$_SALES_ZM       YES       YES          NO

实验2：收缩SecureFile LOB

LOB是我们经常使用的数据类型，用来存储较大的对象，根据数据库的配置，该类型可以存储8TB到128TB的对象。BasicFiles LOB 和 SecureFiles LOB 是Oracle 数据库的两种存储类型。某些高级功能可应用于 SecureFiles LOB，包括压缩和重复数据删除（高级压缩选件的一部分）和加密（高级安全选件的一部分）。在21c中，我们可以对SecureFiles LOB对象进行收缩，从而提高空间利用率。

首先我们创建一个带有CLOB的表，并开启数据文件的自动扩展：

SQL> CREATE TABLE hr.t1 ( a CLOB) LOB(a) STORE AS SECUREFILE TABLESPACE users;
Table created.
SQL> alter database datafile '/u02/app/oracle/oradata/pdb21/users01.dbf' autoextend on;
Database altered.

接下来我们尝试插入和更新行后收缩 SecureFile LOB，看看效果如何。通过观察，本次没有block被释放。

SQL> INSERT INTO hr.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
1 row created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
1 row created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
2 rows created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
4 rows created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
8 rows created.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
16 rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
16 rows updated.
SQL> COMMIT;
Commit complete.
SQL> ALTER TABLE hr.t1 MODIFY LOB(a) (SHRINK SPACE);
Table altered.
SQL> SELECT * FROM v$securefile_shrink;
  LOB_OBJD SHRINK_STATUS                            START_TIME                                                                  END_TIME                                                                    BLOCKS_MOVED BLOCKS_FREED BLOCKS_ALLOCATED EXTENTS_ALLOCATED EXTENTS_FREED EXTENTS_SEALED   CON_ID---------- ---------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------ ------------ ---------------- ----------------- ------------- -------------- ----------     76993 COMPLETE                                 11-APR-22 08.11.31.595 AM +00:00                                            11-APR-22 08.11.31.792 AM +00:00                                                    0     0                0                 1             1              1          4

接下来，我们对这个表进行一些更新，然后在收缩这个表，最后查询一下这次的收缩效果。

SQL>  UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
16 rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
16 rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
16 rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
16 rows updated.
SQL> COMMIT;
Commit complete.
SQL>  ALTER TABLE hr.t1 MODIFY LOB(a) (SHRINK SPACE);
Table altered.SQL> select blocks_freed FROM v$securefile_shrink WHERE LOB_OBJD=76993;
BLOCKS_FREED------------        4410

通过观察，我们发现，本次的效果比较明显，释放了4410个块。随着对这个表操作的增加，对表进行收缩的效果越加明显。建议您在不影响系统性能的前提下，经常对带有SecureFile LOB的表进行收缩，从而合理利用存储资源。

实验3：Automatic In-Memory

In-Memory技术在12c当中就已经为大家提供了，不算是什么新的技术。但是在21c中，提供了自动IM功能，进一步减轻DBA的负担，其实这项功能，在之前的数据库版本中就有体现。只不过当时需要配合特定环境才能使用。

首先让我们查询数据字典，以确定 HR 表是否指定为 INMEMORY。

SQL> SELECT table_name, inmemory, inmemory_compression FROM   dba_tables WHERE  owner='HR';
TABLE_NAME         INMEMORY INMEMORY_COMPRESS------------------ -------- -----------------COUNTRIES          DISABLEDEMP                ENABLED  FOR QUERY LOWREGIONS            DISABLEDLOCATIONS          DISABLEDDEPARTMENTS        DISABLEDJOBS               DISABLEDEMPLOYEES          DISABLEDJOB_HISTORY        DISABLED
8 rows selected.

接下来，我们修改HR.JOB_HISTORY表的IM属性，为他添加INMEMORY MEMCOMPRESS FOR CAPACITY LOW。修改后，再次查询，我们发现最后一行的表属性已经发生了变化。

SQL> ALTER TABLE hr.job_history INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
Table altered.
SQL> SELECT table_name, inmemory, inmemory_compression FROM   dba_tables WHERE  owner='HR';
TABLE_NAME         INMEMORY INMEMORY_COMPRESS------------------ -------- -----------------COUNTRIES          DISABLEDEMP                ENABLED  FOR QUERY LOWREGIONS            DISABLEDLOCATIONS          DISABLEDDEPARTMENTS        DISABLEDJOBS               DISABLEDEMPLOYEES          DISABLEDJOB_HISTORY        ENABLED  FOR CAPACITY LOW
8 rows selected.

上面介绍的是手动指定IM的情况。接下来，让我们看看如何制动指定IM。

SQL> CONNECT / AS SYSDBAConnected.SQL> ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL=HIGH SCOPE=SPFILE;
System altered.
SQL> exit

设定之后，需要重启数据库。接下来，让我们看看HR中表的情况。

SQL> SELECT table_name, inmemory, inmemory_compression FROM   dba_tables WHERE  owner='HR';
TABLE_NAME                     INMEMORY INMEMORY_COMPRESS------------------------------ -------- -----------------COUNTRIES                      DISABLEDEMP                            ENABLED  FOR QUERY LOWREGIONS                        DISABLEDLOCATIONS                      DISABLEDDEPARTMENTS                    DISABLEDJOBS                           DISABLEDEMPLOYEES                      DISABLEDJOB_HISTORY                    ENABLED  FOR CAPACITY LOW
8 rows selected.

为什么没有变化，除了我们之前手动设定IM的表之外，其他表依旧没有启动IM功能？让我们查看一下初始化参数设置。

SQL> SHOW PARAMETER INMEMORY_AUTOMATIC_LEVEL
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------inmemory_automatic_level             string      LOWSQL> SELECT ispdb_modifiable FROM v$parameter WHERE name='inmemory_automatic_level';
ISPDB-----TRUE

我们发现是当前的INMEMORY_AUTOMATIC_LEVEL设定为LOW，我们将它修改为HIGH，然后在重启数据库。

SQL> SELECT table_name, inmemory, inmemory_compression FROM   dba_tables WHERE  owner='HR';
TABLE_NAME           INMEMORY INMEMORY_COMPRESS-------------------- -------- -----------------COUNTRIES            DISABLEDEMP                  ENABLED  FOR QUERY LOWREGIONS              ENABLED  AUTOLOCATIONS            ENABLED  AUTODEPARTMENTS          ENABLED  AUTOJOBS                 ENABLED  AUTOEMPLOYEES            ENABLED  AUTOJOB_HISTORY          ENABLED  FOR CAPACITY LOW
8 rows selected.

除了JOB_HISTORY和EMP是之前手工设定的之外，其他标的IM属性都发生了变化。需要注意的是，如果您看到的结果与上面不同，依旧没有变成ENABLED AUTO，别着急，请稍等片刻，后台刷新需要一点时间。

但是我们发现，在上面的结果中，有一张表名字为COUNTRIES，它的IM状态为DISABLED。我们通过执行下面的语句，您就知道为什么它无法使用IM技术了。

SQL> ALTER TABLE hr.countries INMEMORY;ALTER TABLE hr.countries INMEMORY*ERROR at line 1:ORA-64358: in-memory column store feature not supported for IOTs

因为它是IOT表。

接下来我们看看刚才设定的表，在被查询的时候，是否可以被载入系统的IM列存储。我们首先执行大量查询，然后通过动态视图查看系统的IM使用情况。

SQL> SELECT /*+ FULL(hr.employees) NO_PARALLEL(hr.employees) */ count(*) FROM hr.employees;  COUNT(*)----------      107
SQL> SELECT /*+ FULL(hr.departments) NO_PARALLEL(hr.departments) */ count(*) FROM hr.departments;
  COUNT(*)----------        27
SQL> SELECT /*+ FULL(hr.locations) NO_PARALLEL(hr.locations) */ count(*) FROM hr.locations;
  COUNT(*)----------        23
SQL> SELECT /*+ FULL(hr.jobs) NO_PARALLEL(hr.jobs) */ count(*) FROM hr.jobs;
  COUNT(*)----------        19
SQL> SELECT /*+ FULL(hr.regions) NO_PARALLEL(hr.regions) */ count(*) FROM hr.regions;
  COUNT(*)----------        4
SQL> SELECT /*+ FULL(hr.emp) NO_PARALLEL(hr.emp) */ count(*) FROM hr.emp;
  COUNT(*)----------  3506176

通过下方的查询，发现有两张表已经在IM的列存储当中。

SQL> SELECT segment_name, inmemory_size, bytes_not_populated, inmemory_compression FROM v$im_segments;
SEGMENT_NAME                             INMEMORY_SIZE BYTES_NOT_POPULATED INMEMORY_COMPRESS---------------------------------------- ------------- ------------------- -----------------EMP                                           44433408                   0 FOR QUERY LOWEMPLOYEES                                      1310720                   0 AUTO

实验4：使用新的MAX_IDLE_BLOCKER_TIME初始化参数处理阻塞问题

在数据库操作中，有时会遇到会话阻塞的问题，在21c中，可以通过初始化参数MAX_IDLE_BLOCKER_TIME来自动解决阻塞的会话。这个参数的单位为分钟。比如通过如下语句，将该参数设定为2分钟。

SQL system> ALTER SYSTEM SET max_idle_blocker_time=2;System altered.
SQL system> SHOW PARAMETER max_idle_blocker_time
NAME                            TYPE        VALUE------------------------------- ----------- ------------------------------max_idle_blocker_time           integer     2SQL system>

我们新开一个Terminal，然后对数据进行更新，但不提交。

SQL hr> UPDATE hr.employees SET salary=salary*2;    107 rows updated.
    SQL hr>

然后会到原来的Terminal，也做更新的动作。我们会发现，该更新动作被阻塞。

SQL system> UPDATE hr.employees SET commission_pct=0;

两分钟之后，将看到如下结果，更新成功。

107 rows updated.SQL system>

然后回到上一个Terminal（提示符为hr的Terminal），将看到如下输出：

SQL hr> SELECT salary FROM hr.employees;    SELECT salary FROM hr.employees       *    ERROR at line 1:    ORA-03113: end-of-file on communication channel    Process ID: 32314    Session ID: 274 Serial number: 8179

如果想了解具体后台的处理动作，您可以检查数据库的trace文件，获取更多详细信息。