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

甲骨文云技术 2022-04-13

475

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

通过自动区域映射允许，基于查询中的谓词修剪块和分区，无需任何用户干预
压缩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 STATISTIC
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

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  C
olumn list
 ZMAP$_SALES     SALES       SH      00:00:00.83     2022-04-11/07:17:06  8    A
MOUNT_SOLD,CUST_ID,PROMO_ID,TIME_ID
 ZMAP$_SALES_ZM  SALES_ZM    SALES   00:00:01.52     2022-04-11/07:17:07  8    S
ALE_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_actions
WHERE 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          DISABLED
EMP                ENABLED  FOR QUERY LOW
REGIONS            DISABLED
LOCATIONS          DISABLED
DEPARTMENTS        DISABLED
JOBS               DISABLED
EMPLOYEES          DISABLED
JOB_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          DISABLED
EMP                ENABLED  FOR QUERY LOW
REGIONS            DISABLED
LOCATIONS          DISABLED
DEPARTMENTS        DISABLED
JOBS               DISABLED
EMPLOYEES          DISABLED
JOB_HISTORY        ENABLED  FOR CAPACITY LOW

8 rows selected.

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

SQL> CONNECT  AS SYSDBA
Connected.
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                      DISABLED
EMP                            ENABLED  FOR QUERY LOW
REGIONS                        DISABLED
LOCATIONS                      DISABLED
DEPARTMENTS                    DISABLED
JOBS                           DISABLED
EMPLOYEES                      DISABLED
JOB_HISTORY                    ENABLED  FOR CAPACITY LOW

8 rows selected.

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

SQL> SHOW PARAMETER INMEMORY_AUTOMATIC_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_automatic_level             string      LOW
SQL> 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            DISABLED
EMP                  ENABLED  FOR QUERY LOW
REGIONS              ENABLED  AUTO
LOCATIONS            ENABLED  AUTO
DEPARTMENTS          ENABLED  AUTO
JOBS                 ENABLED  AUTO
EMPLOYEES            ENABLED  AUTO
JOB_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 LOW
EMPLOYEES                                      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     2
SQL 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