Oracle Database 21c新特性讲座（3）——自动化Zone映射、SecureFile LOB对象管理、MAX_IDLE_BLOCKER_TIME参数

甲骨文云技术 2020-12-30

728

这是Oracle Database 21c新特性讲座的第三讲，在今天的内容中将为大家介绍自动化zone映射，SecureFile LOB对象管理以及阻塞参数设定。

实验3-1:自动化Zone映射

自动化区域映射可以根据查询中的谓词对块和分区进行修剪，这项功能可以通过手工方式设定，也可以在系统全局范围内进行自动启用。接下来我们就通过实验为大家进行讲解。我们使用sales用户登录pdb，然后做几次相同的查询，观察一致性读的计数，第一次是15411，后面都稳定到15370.

SQL> SET AUTOTRACE ON STATISTIC
SQL> SELECT COUNT(DISTINCT sale_id) FROM sales_zm WHERE customer_id = 50;

COUNT(DISTINCTSALE_ID)
----------------------
                   100


Statistics
----------------------------------------------------------
         35  recursive calls
         13  db block gets
      15411  consistent gets
          2  physical reads
       9268  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 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
       7040  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

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>

复制

接下来我们启动Zone Map，然后对表进行move，对其重整。然后再次执行之前的查询观察一致性读的计数，我们发现即便是第一次查询，一致性读从原来的15370降低到1077，在后面的查询过程中，稳定到1032.说明Zone Map起了作用，根据查询中的谓词对block和分区进行了修建，减少了数据的读取，从而提升的查询性能。


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.

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

COUNT(DISTINCTSALE_ID)
----------------------
                   100


Statistics
----------------------------------------------------------
         67  recursive calls
          6  db block gets
       1077  consistent gets
          0  physical reads
       1368  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
----------------------------------------------------------
         14  recursive calls
          0  db block gets
       1032  consistent gets
          0  physical reads
          0  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
----------------------------------------------------------
         14  recursive calls
          0  db block gets
       1032  consistent gets
          0  physical reads
          0  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>

复制

可以通过dba_zonemaps查询zone map的创建情况，通过观察下面的结果，可以看到刚才的zone map不是自动创建的。

SQL> SELECT zonemap_name,automatic,partly_stale, incomplete FROM  dba_zonemaps;

ZONEMAP_NAME         AUTOMATIC PARTLY_STALE INCOMPLETE
-------------------- --------- ------------ ------------
ZMAP$_SALES_ZM       NO        NO           NO

复制

接下来我们启动自动的zone map创建，并重复上面的实验。首先我们将刚才创建的表删除，然后通过数据字典查询zone map的情况，之后开启自动zone map功能。

在代码的最后，重新创建刚才删除的表，注入数据并收集统计信息。

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>
SQL> @21c-3-4.sql
SQL> EXEC dbms_stats.gather_table_stats(ownname=>NULL, tabname=>'SALES_ZM');

PL/SQL procedure successfully completed.

SQL>

复制

我们继续执行上面的SQL语句查询并观察一致性读的情况，我们发现zone map并没有起作用，这是为什么？因为zone map的创建是后台Job调动的，并不是实时完成的，要么我们等一会儿，要么我们手动提交这个Job，大家可能会说，不是自动创建的zone map吗？zone map确实是自动创建的，只不过那些心急的小伙伴不想等它自动创建，非要push它一把罢了。

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
      15372  consistent gets
          0  physical reads
       9168  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

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>

复制

我们现在就为那些心急的小伙伴，push系统一下，让它立即自动创建zone map。以管理员身份登录pdb，然后执行如下代码：

SQL> BEGIN
 sys.dbms_auto_zonemap_internal.zmap_execute;
END;
/  

PL/SQL procedure successfully completed.

SQL>

复制

接下来，换回到sales用户登录pdb。查询数据字典，发现sales_zm表上的zone map已经被创建，并且在AUTOMATIC列上，已经显示YES，表示是自动创建的。

SQL> SELECT zonemap_name, automatic, partly_stale, incomplete FROM   dba_zonemaps;

ZONEMAP_NAME         AUTOMATIC PARTLY_STALE INCOMPLETE
-------------------- --------- ------------ ------------
ZMAP$_SALES_ZM       YES       NO           NO

复制

可以通过查询dbms_auto_zonemap.activity_report了解zone map是如何被创建的。

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    25-DEC-2020 04:54:42.000000000 +00:00
 Activity End      27-DEC-2020 04:54:42.109003000 +00:00
 Total Executions  1
-------------------------------------------------------------------------------


EXECUTION SUMMARY
-------------------------------------------------------------------------------
 zonemaps created                      1
 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_ZM  SALES_ZM    SALES   00:00:01.38     2020-12-27/04:47:28  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

复制

如果想了解所有的自动zone map创建情况，可以通过下面的数据字典来查询。

SQL> select  EXEC_NAME,ACTION_MSG from dba_zonemap_auto_actions WHERE action_msg LIKE '%succesfully created zonemap:%' ;

EXEC_NAME            ACTION_MSG
-------------------- --------------------------------------------------
SYS_ZMAP_2020-12-27/ BS:succesfully created zonemap: ZN:ZMAP$_SALES_ZM
04:47:26             BT:SALES_ZM SN:SALES CL:SALE_ID CT:+00 00:00:01.38
                     8929 TS:2020-12-27/04:47:28 DP:8

复制

接下来我们对Sales_zm这个表进行更新，然后再看看zone map的变化情况。一开始发现partly_stale下面写着YES，说明zone map正在等待更新，稍等一会儿再次查询，这里变成NO表示更新完毕。这个更新动作不是实时的，就和我们今年年初的时候讲19c新特性的时候一样，有些自动化功能是后台通过定期任务完成的，并非实时。这也很好理解，就如我们当初说统计信息一样，收集统计信息是为了系统加速，但是收集统计信息本身会消耗资源，过于频繁地收集统计信息，不会启动加速系统的作用，反而是拖慢了系统。所以要找到一个平衡点是很重要的。

SQL> @21c-3-5.sql

8000 rows updated.


8000 rows updated.


8000 rows updated.


8000 rows updated.


Commit complete.

SQL>
SQL> SELECT zonemap_name, automatic, partly_stale, incomplete FROM   dba_zonemaps;

ZONEMAP_NAME         AUTOMATIC PARTLY_STALE INCOMPLETE
-------------------- --------- ------------ ------------
ZMAP$_SALES_ZM       YES       YES          NO

SQL> SELECT zonemap_name, automatic, partly_stale, incomplete FROM   dba_zonemaps;

ZONEMAP_NAME         AUTOMATIC PARTLY_STALE INCOMPLETE
-------------------- --------- ------------ ------------
ZMAP$_SALES_ZM       YES       NO           NO

复制

实验3-2:SecureFile LOB对象管理

在这个实验当中，我们将为大家演示对securefile当中lob对象的压缩效果。首先我们创建一个表空间，然后在这个表空间当中创建一个带有SecureFile LOB字段的表在这个表空间上，并开启对应文件的数据文件自动扩展功能。

SQL> CREATE TABLESPACE users1 DATAFILE '/u02/app/oracle/oradata/DB21c_icn1b6/DB21C_ICN1B6/users01.dbf' SIZE 500M reuse;

Tablespace created.

SQL> CREATE TABLE hr.t1 ( a CLOB) LOB(a) STORE AS SECUREFILE TABLESPACE users1;

Table created.

SQL> alter database datafile '/u02/app/oracle/oradata/DB21c_icn1b6/DB21C_ICN1B6/users01.dbf' autoextend on;

Database altered.


复制

接下来我们向刚刚创建的表插入数据，然后做更新并提交。

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> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;

8 rows updated.

SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;

8 rows updated.

SQL> commit;

Commit complete.

SQL>

复制

接下来我们对这个lob进行压缩。并观察压缩效果。通过观察发现2个blocks被释放。我们这里记住这个lob的lob_objd，稍后对这个表进行更新，然后继续所压缩的动作，然后根据这个id去查询压缩的效果。

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
-------------- ----------
     75798 COMPLETE
27-DEC-20 05.33.13.520 AM +00:00
27-DEC-20 05.33.13.824 AM +00:00
           2            2                2                 1             1
             1          3



复制

接下来我们对这个表做更新的操作，然后去压缩，接下来根据上面查询出来的lob_objd看看这次的压缩效果。上次是释放了两个数据块，现在释放了3671个数据块。

SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;

8 rows updated.

SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;

8 rows updated.

SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;

8 rows updated.

SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;

8 rows updated.

SQL> commit;

Commit complete.

SQL> select BLOCKS_FREED FROM v$securefile_shrink WHERE LOB_OBJD=75798;

BLOCKS_FREED
------------
        3671
           2

SQL>

复制

如果大家感兴趣，可以继续重复上面的更新、压缩、查询的过程，观察securefile lob的压缩情况。

实验3-2:MAX_IDLE_BLOCKER_TIME参数

通过这个参数可以将阻塞其他进程的操作，在等待特定时间之后进行终止。

在这个实验中，我们开两个terminal，一个使用system用户登录，一个使用hr用户登录。然后使用system用户将设定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>

复制

我们在HR用户下面创建一张表叫做T2，随便插入一行数据，然后对这行数据进行更新，但不提交。

SQL hr> create table t2 (id number);

Table created.

SQL hr> insert into t2 values(100);

1 row created.

SQL hr> commit;

Commit complete.

SQL hr> update t2 set id=200;

1 row updated.

SQL hr>

复制

接下来我们对上面的提到的表，在system的terminal也做更新，大家会发现，这个更新将被阻塞，当时2分钟之后，这个更新就生效了。因为我们之前将MAX_IDLE_BLOCKER_TIME设定为2分钟。

SQL system> update hr.t2 set id=300;

复制

2分钟之后在system的session当中会看到：

1 row updated.

SQL system>

复制

再次回到HR的session看看，发现它已经被踢下线了。

SQL hr> commit;
commit
     *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 9182
Session ID: 1 Serial number: 44464


SQL hr> select * from t2;
ERROR:
ORA-03114: not connected to ORACLE


SQL hr>

复制

如果查询trace文件可以看到如下信息：

HM: Session with ID 1166 serial # 63603 (FG) on single instance 1 is hung
    and is waiting on 'enq: TX - row lock contention' for 96 seconds.
    Session was previously waiting on 'SQL*Net message from client'.
    Final Blocker is Session ID 1 serial# 44464 on instance 1
     which is waiting on 'SQL*Net message from client' for 137 seconds, wait id 250
     p1: 'driver id'=0x54435000, p2: '#bytes'=0x1, p3: ''=0x0


复制

今天的内容就到这里，将在下一讲当中为大家介绍Oracle Database 21c当中关于数据泵方面的改进，期待您的关注，谢谢。

文章被以下合辑收录

Oracle Database 21c 专栏（共73篇）

本专栏收录 Oracle 21c相关文章、文档。

Oracle Database 21c新特性讲座（3）——自动化Zone映射、SecureFile LOB对象管理、MAX_IDLE_BLOCKER_TIME参数

文章被以下合辑收录

评论

相关阅读