于提高性能和高可用性的增强功能。具体包括:
通过自动区域映射允许,基于查询中的谓词修剪块和分区,无需任何用户干预
压缩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
----------------------------------------------------------
recursive calls
db block gets
1096 consistent gets
0 physical reads
1308 redo size
582 bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
0 sorts (disk)
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.169tpCONFIGURE('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=>, 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
----------------------------------------------------------
recursive calls
db block gets
15365 consistent gets
15280 physical reads
1964 redo size
582 bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
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
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
188 BS:Current execution task id: 7 Execution name: SYS_ZMAP_2022-04-11/07:19:13 Task Name: ZMAP_TASK1
189 BS:******** Zonemap Background Action Report for Task ID: 7 ****************
190 BS:****** End of Zonemap Background Action Report for Task ID: 7 **********
164 BS:Current execution task id: 7 Execution name: SYS_ZMAP_2022-04-11/07:17:04 Task Name: ZMAP_TASK1
165 BS:******** Zonemap Background Action Report for Task ID: 7 ****************
166 TP:Trying to create zonemap on table: SALES owner:SH
167 AL:Block count : 16384, sample percent is : 3.051758
168 TP:col name:AMOUNT_SOLD: clustering ratio: .28
169 TP:col name:CHANNEL_ID: clustering ratio: 1
170 TP:col name:CUST_ID: clustering ratio: .14
171 TP:col name:PROD_ID: clustering ratio: .97
172 TP:col name:PROMO_ID: clustering ratio: .2
173 TP:col name:QUANTITY_SOLD: clustering ratio: 1
174 TP:col name:TIME_ID: clustering ratio: .03
175 TP:Candidate column list:AMOUNT_SOLD,CUST_ID,PROMO_ID,TIME_ID
176 TP:New zonemap name: ZMAP$_SALES
177 TP:Creating new zonemap ZMAP$_SALES on table SALES owner SHtable space USERS
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
179 TP:Trying to create zonemap on table: SALES_ZM owner:SALES
180 AL:Block count : 15280, sample percent is : 3.272251
181 TP:col name:CUSTOMER_ID: clustering ratio: .99
182 TP:col name:SALE_ID: clustering ratio: .03
183 TP:Candidate column list:SALE_ID
184 TP:New zonemap name: ZMAP$_SALES_ZM
185 TP:Creating new zonemap ZMAP$_SALES_ZM on table SALES_ZM owner SALEStable space SYSTEM
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
187 BS:****** End of Zonemap Background Action Report for Task ID: 7 **********
rows selected.
显示自动任务运行的活动报告的另一种方法是使用DBMS_AUTO_ZONEMAP.ACTIVITY_REPORT 函数,169tp。
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
---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
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
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');
row created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
row created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
rows created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
rows created.
SQL> INSERT INTO hr.t1 Select * from hr.t1;
rows created.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
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 -APR- 08.11.31.595 AM +00:00 -APR- 08.11.31.792 AM +00:00 0 0 0
接下来,我们对这个表进行一些更新,然后在收缩这个表,最后查询一下这次的收缩效果。
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
rows updated.
SQL> UPDATE hr.t1 SET a=a||a||a||a||a||a||a;
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
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 :
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(*)
----------
SQL> SELECT /*+ FULL(hr.locations) NO_PARALLEL(hr.locations) */ count(*) FROM hr.locations;
COUNT(*)
----------
SQL> SELECT /*+ FULL(hr.jobs) NO_PARALLEL(hr.jobs) */ count(*) FROM hr.jobs;
COUNT(*)
----------
SQL> SELECT /*+ FULL(hr.regions) NO_PARALLEL(hr.regions) */ count(*) FROM hr.regions;
COUNT(*)
----------
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
如果想了解具体后台的处理动作,您可以检查数据库的trace文件,获取更多详细信息。
今天的内容就到这里,我们将在下次的文章中为您介绍Oracle Database 21c中数据泵的新特性,感谢您的阅读,谢谢。
手把手系列文章:
手把手教你21c新特性(1):软件安装与应用程序开发
手把手教你21c新特性(2):大数据与数据仓库新特性
手把手教你升级到Database 19c(1)
手把手教你升级到Database 19c(2)
手把手教你升级到Database 19c(3)
手把手教你19c新特性:自动索引
手把手教你19c新特性:实时统计信息收集
手把手教你19c新特性:混合分区表
手把手教你19c新特性:SQL隔离
手把手教你:使用Kettle实现数据同步
手把手教你:使用Kettle实现数据同步(2)
手把手教你:搭建Data Science环境
手把手教你:使用Oracle Data Science分析纽约民宿数据
手把手教你:使用Oracle AutoML进行预测(实战教程)
手把手教你OCI机器视觉(1):通过控制台使用OCI Vision
编辑:殷海英