Oracle 19c新特性 -HINT_REPORT

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:Oracle 19c新特性 -HINT_REPORT
在Oracle 19c之前,我们经常会遇到一个问题,我们使用了错误的hint,它不会告诉我们任何错误。这会导致我们在优化执行计划的时候有点手足无措,明明我设置了index的hint啊,怎么使用不了,怎么仍然是全表扫描。但是在新的19c下面,有一个新功能可以帮助我们查看hint使用的情况报告,我们使用dbms_xplan函数(display,display_cursor,display_workload_repository或者是display_sql_plan_baseline)时,默认情况下启动此功能。

接下来我们来演示一下这个功能,我们先早点数据,然后创建索引。在hint中,我们使用错误的索引看看会出现什么情况。

我们来创建一张表,一个索引,并收集统计信息。

create table test as select * from dba_objects;
create index idx_object_type on test(OBJECT_TYPE);
exec dbms_stats.gather_table_stats(OWNNAME=>'TEST',TABNAME=>'TEST' , cascade => true);

接下来我们来演示使用错误的hint,hint中索引名字写错
select /*+ INDEX (test,idxobjectype) */ distinct object_type from test where owner=’SYS’;

在执行dbms_xplan.display_cursor的时候,我们选择format的格式为HINT_REPORT,这里我们就可以看到Hint Report的提示,这个地方就提示我们INDEX (test,idxobjectype) / index specified in the hint doesn’t exist。我们就可以立马发现问题,指定的索引不存在。

SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	9s0p5vxpg01jg, child number 1
-------------------------------------
select /*+ INDEX (test,idxobjectype) */ distinct object_type from test
where owner='SYS'

Plan hash value: 2203132549

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |   393 (100)|	  |
|   1 |  HASH UNIQUE	   |	  |    45 |   675 |   393   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST |  2896 | 43440 |   392   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -	SEL$1 / TEST@SEL$1
	 U -  INDEX (test,idxobjectype) / index specified in the hint doesn't exist


27 rows selected.

我们在改回正确的索引,可以看到正确的就是OUTLINE中的内容。但是这里hint提示Unused,这里还是得说下优化器还是很强大的,仍然并没有使用我们的hint,在hint这个地方告诉我们Unused。

SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bqg63rfpsad9j, child number 0
-------------------------------------
select /*+ INDEX (test,idx_object_type) */ distinct object_type from
test where owner='SYS'

Plan hash value: 2203132549

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |   393 (100)|	  |
|   1 |  HASH UNIQUE	   |	  |    45 |   675 |   393   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST |  2896 | 43440 |   392   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -	SEL$1 / TEST@SEL$1
	 U -  INDEX (test,idx_object_type)

看了演示,可能会觉得这只能检测到名字之类的错误啊,其实还可以帮助我们检测到语法之类的错误,注意看这里提示变成了E – Syntax error。因为USE_NL中,我们没有写相关的驱动表。

select /*+ USE_NL */ distinct object_type  from test where owner='SYS';

SQL>  select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8xb3sgt6mkm1w, child number 0
-------------------------------------
select /*+ USE_NL */ distinct object_type  from test where owner='SYS'

Plan hash value: 2203132549

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |   393 (100)|	  |
|   1 |  HASH UNIQUE	   |	  |    45 |   675 |   393   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST |  2896 | 43440 |   392   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -	SEL$1
	 E -  USE_NL


26 rows selected.

还有一种情况是我们使用了多个hint,而这两个hint是有冲突的。我们在刚刚的语句中指定FULL(test) INDEX(test,idx_object_type),因为索引扫描和全表扫描是互斥的。在这种情况下,优化器会忽略两个冲突的提示。两个hint这里都会提示hint conflicts。

select /*+ FULL(test) INDEX(test,idx_object_type) */ distinct object_type  from test where owner='SYS';

SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	f199byqapmvxm, child number 0
-------------------------------------
select /*+ FULL(test) INDEX(test,idx_object_type) */ distinct
object_type  from test where owner='SYS'

Plan hash value: 2203132549

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |   393 (100)|	  |
|   1 |  HASH UNIQUE	   |	  |    45 |   675 |   393   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST |  2896 | 43440 |   392   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   2 -	SEL$1 / TEST@SEL$1
	 U -  FULL(test) / hint conflicts with another in sibling query block
	 U -  INDEX(test,idx_object_type) / hint conflicts with another in sibling query block


28 rows selected.

最后一种情况比较难模拟。这里介绍一下,转换会使某些hint无效。例如,一条语句指定PUSH_PRED(some_view) MERGE(some_view)。当some_view合并到其包含的查询块时,优化器无法应用PUSH_PRED提示,因为该提示some_view不可用。

有了这个报告,可以帮助我们分析为什么我们的hint为什么不起作用,酷!
参考文档:https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/influencing-the-optimizer.html#GUID-98F9788B-9162-4A12-9257-CC855A4136B3

Oracle Database 18c新特性-Scalable sequences

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:Oracle Database 18c新特性-Scalable sequences
在我们日常维护的数据库中,在大规模插入的数据库应用中,经常会遇到”enq:TX-index contention”这类型的等待事件。并且这个问题发生在通过序列生成的递增键值上的尤其常见。索引叶子节点争用发生在基于用户生成的键(通过序列生成)插入行时,在这种插入的情况下,最近的条目将位于B树索引的最右边的叶子块中。这也意味着所有新行都将存储在索引的最右边的叶子块中,随着越来越多的会话向表中插入行,最右边的叶子块将塞满。Oracle将最右边的叶子块分为两个叶子块,其中一个块包含除一行之外的所有行,而另一个新块仅包含一行。这种类型的增长称为”Right Handed Growth”索引。随着越来越多的并发会话插入到索引的最右边的叶块中,该索引块成为热块,并且该叶块上的并发导致性能问题。在Oracle RAC数据库中,此问题被放大并成为更大的瓶颈。如果序列缓存很小(在特定于实例默认为20),则最右边的叶块不仅在一个实例中而且在集群的所有实例中都成为热块,并且该热块需要通过gc来回传输。

在Oracle Database 18c引入了一种称为可伸缩序列的新型序列。在那些具有高并发性的数据插入的情况下,通过生成无序主键或唯一键值的新可伸缩序列,可以帮助大大减少由右手索引引起的序列和索引块争用,这种和之前配置巨大的CACHE序列缓存解决方案相比较,吞吐量,数据负载可伸缩性和性能更好。

使用语法
CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
]

默认我们创建一个索引,scale和extend都是关闭的

SQL> create sequence test_seq;
Sequence created.

SQL> create sequence test_seq scale;
Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences;

SEQUENCE_NAME															 S E
-------------------------------------------------------------------------------------------------------------------------------- - -
TEST_SEQ															 Y N

现在使用scale子句创建序列。当指定scale子句时,在序列的数字前添加一个6位可伸缩序列偏移量数。6位可伸缩序列偏移量数的公式如下:[(instance_id % 100) + 100] || [session_id % 1000]生成的。

SQL> select sequence_name,max_value,scale_flag, extend_flag from user_sequences;

SEQUENCE_NAME							     MAX_VALUE S E
---------------------------------------- ------------------------------------- - -
TEST_SEQ					  9999999999999999999999999999 Y N

SQL> select test_seq.nextval from dual;

			   NEXTVAL
----------------------------------
      1010310000000000000000000001

SQL> select test_seq.nextval from dual;

			   NEXTVAL
----------------------------------
      1010310000000000000000000002

我们在看一下我们会话的inst_id和sid,可以看到前面6位数字的偏移量101031就是通过这两个计算出来的。

SQL> SELECT sys_context('USERENV', 'INSTANCE') inst_id, sys_context('USERENV', 'SID') sid FROM dual;
INST_ID 				 SID
---------------------------------------- ------------------------------
1					 31

当使用SCALE关键字在指定EXTEND时,生成的序列值全为长度(x + y),其中x为可伸缩偏移量的长度(默认为6),y为序列maxvalue指定的位数。noextend表示序列总长度不超过maxvalue定义的长度,由于前面默认是6位数+正常序列号,所以长度至少是7位。SCALE子句的默认设置为NOEXTEND。

SQL> SELECT sys_context('USERENV', 'INSTANCE') inst_id, sys_context('USERENV', 'SID') sid FROM dual;

INST_ID 		       SID
------------------------------ ------------------------------
1			       275


SQL> create sequence seq_extend start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend;
Sequence created.

SQL> select seq_extend.nextval from dual;

   NEXTVAL
----------
 101275001

 SQL> create sequence seq_noextend start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend;

Sequence created.

SQL> select seq_noextend.nextval from dual;
select seq_noextend.nextval from dual
       *
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for SEQ_NOEXTEND. Widen the sequence
by 4 digits or alter sequence with SCALE EXTEND.

可以看到,我们使用NOEXTEND在当你的maxvalue小于7位的时候,就会报ORA-64603错误。
最后需要注意一点,Oracle建议不要为Scalable sequences指定顺序,因为Scalable sequences序列号是全局无序的。

参考链接:https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-views-sequences-and-synonyms.html#GUID-76663C84-D792-46A3-A25A-03C49DED71AD

Oracle Database 12C新hint—ENABLE_PARALLEL_DML

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:Oracle Database 12C新hint—ENABLE_PARALLEL_DML
在12c之前的版本中,通过alter session启用了并行DML。12c引入了新的hint,可以过SQL中加/ * + ENABLE_PARALLEL_DML * / 启用并行DML 。这就有一个好处,之前我们在session级别更改的,一旦更改了会话级,则所有的其他语句也将会并行执行DML。这显然不科学。现在通过新的hint,我们可以只对我们感兴趣的语句使用。当然我在测试中发现,我们使用了ENABLE_PARALLEL_DML这个hint开启并行DML,我们的语句并没有并行执行,仍然是串行的,这是因为我们仍然需要在里面写入并行的表。还有一种情况就是我们写了hint,但是违反了并行操作的限制也不会开启。那么我们来简单演示一下这个hint。

首先我使用普通的并行操作的hint,可以看到自动开启了并行操作,但是它显示“PDML is disabled in current session”.

SQL> update /*+parallel */test set OWNER='Buddy';
72392 rows updated.

SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8cbs8m8qrjy5b, child number 1
-------------------------------------
update /*+parallel */test set OWNER='Buddy'

Plan hash value: 3695425075

---------------------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      | 	 |	 |	 |   212 (100)| 	 |	  |	 |	      |
|   1 |  UPDATE 	      | TEST	 |	 |	 |	      | 	 |	  |	 |	      |
|   2 |   PX COORDINATOR      | 	 |	 |	 |	      | 	 |	  |	 |	      |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 | 72392 |   353K|   212   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR | 	 | 72392 |   353K|   212   (0)| 00:00:01 |  Q1,00 | PCWC |	      |
|*  5 |      TABLE ACCESS FULL| TEST	 | 72392 |   353K|   212   (0)| 00:00:01 |  Q1,00 | PCWP |	      |
---------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session

接下来,我使用ENABLE_PARALLEL_DML的hint,但是结果确显示“PDML disabled because object is not decorated with parallel clause”,这是因为我们虽然开启了ENABLE_PARALLEL_DML,但是没开启parallel,所以受到限制PDML也是disable的。

SQL> update /*+ ENABLE_PARALLEL_DML */  test set OWNER='Buddy';
72392 rows updated.

SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bb53bfzfr7z1n, child number 0
-------------------------------------
update /*+ ENABLE_PARALLEL_DML */  test set OWNER='Buddy'

Plan hash value: 839355234

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |	  |	  |	  |   383 (100)|	  |
|   1 |  UPDATE 	   | TEST |	  |	  |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 72392 |   353K|   383   (1)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - PDML disabled because object is not decorated with parallel clause

这次我们同时打开了parallel和ENABLE_PARALLEL_DML。现在的执行计划和我们单独开parallel的执行计划并不一样。如果只开parallel是仅仅查询的部分开启了并行,DML部分没有使用并行。两个提示都打开,则查询部分和DML操作都会使用并行。

SQL> update /*+ ENABLE_PARALLEL_DML parallel */  test set OWNER='Buddy';
72392 rows updated.


SQL>  select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	cd4s55gj7172y, child number 2
-------------------------------------
update /*+ ENABLE_PARALLEL_DML parallel */  test set OWNER='Buddy'

Plan hash value: 4152913824

------------------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |	 TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT	 |	    |	    |	    |	212 (100)|	    |	     |	    |		 |
|   1 |  PX COORDINATOR 	 |	    |	    |	    |		 |	    |	     |	    |		 |
|   2 |   PX SEND QC (RANDOM)	 | :TQ10001 | 72392 |	353K|	212   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE	 | TEST     |	    |	    |		 |	    |  Q1,01 | PCWP |		 |
|   4 |     PX RECEIVE		 |	    | 72392 |	353K|	212   (0)| 00:00:01 |  Q1,01 | PCWP |		 |
|   5 |      PX SEND RANGE	 | :TQ10000 | 72392 |	353K|	212   (0)| 00:00:01 |  Q1,00 | P->P | RANGE	 |
|   6 |       UPDATE		 | TEST     |	    |	    |		 |	    |  Q1,00 | PCWP |		 |
|   7 |        PX BLOCK ITERATOR |	    | 72392 |	353K|	212   (0)| 00:00:01 |  Q1,00 | PCWC |		 |
|*  8 | 	TABLE ACCESS FULL| TEST     | 72392 |	353K|	212   (0)| 00:00:01 |  Q1,00 | PCWP |		 |
------------------------------------------------------------------------------------------------------------------

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

   8 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

还有一种特殊的情况,假设一大堆语句,你都想用PDML,但是有那么一条你不想使用,这个时候你可以在session级别设置了DML parallel,然后在语句上设置disable_parallel_dml就可以了。

SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.

update /*+ disable_parallel_dml parallel */  test set OWNER='Buddy'

Plan hash value: 3695425075

---------------------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      | 	 |	 |	 |   212 (100)| 	 |	  |	 |	      |
|   1 |  UPDATE 	      | TEST	 |	 |	 |	      | 	 |	  |	 |	      |
|   2 |   PX COORDINATOR      | 	 |	 |	 |	      | 	 |	  |	 |	      |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 | 72392 |   353K|   212   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR | 	 | 72392 |   353K|   212   (0)| 00:00:01 |  Q1,00 | PCWC |	      |
|*  5 |      TABLE ACCESS FULL| TEST	 | 72392 |   353K|   212   (0)| 00:00:01 |  Q1,00 | PCWP |	      |
---------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session


27 rows selected.

参考文档:New 12c Hint: ENABLE_PARALLEL_DML Lets you Easily Enable Parallel DML (PDML) at the Statement Level (Doc ID 1991034.1)

oracle 19c新特性-Real-time Stats

在数据仓库环境中,我们经常会遇到表被truncate并且新数据(通常为千万行)插入的情况。如果针对包含新插入的数据的表运行报表,除非我们去收集新的统计信息,否则优化器很难选择正确的执行计划。因此,为了解决此问题,Oracle数据库12c引入了Online统计信息收集,但是这仅适用于通过CREATE TABLE AS SELECT语句以及使用APPEND hint进行直接路径插入的表。而为了进一步增强功能,Oracle Database 19c引入了实时统计信息,它扩展了在线统计信息的收集范围,还包括常规的DML语句。
通常,统计信息是由自动统计信息收集作业收集的,该作业在数据库维护窗口内运行,一般是一天一次。但是对于刚刚提到的场景,统计信息在DBMS_STATS作业执行之前就已经过期陈旧,因此,Oracle 19c新功能实时统计信息可以帮助应用程序不受此问题的困扰。
批量插入的操作将收集必要的统计信息(Oracle 19c之前的行为),而实时统计信息会增加而不是取代传统统计信息。下面我们来测试一下这个新功能

create table t1 as select * from dba_objects;

select distinct object_type from t1 where owner='SYS';

select * from table (dbms_xplan.display_cursor);

SQL> select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	7k4h0792x1vdx, child number 0
-------------------------------------
select distinct object_type from t1 where owner='SYS'

Plan hash value: 2134347679

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |   393 (100)|	  |
|   1 |  HASH UNIQUE	   |	  |    45 |   675 |   393   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2895 | 43425 |   392   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

19 rows selected.

现在,我们往表中插入一些数据,我们要让表中的行数加倍。在较早的Oracle版本中,优化器可能会选择错误的计划,因为它“不知道”表中发生了某些DML,而表中的行数却增加了2倍以上。但是现在在Oracle 19c中,我们可以看到,作为INSERT语句的一部分,还执行了OPTIMIZER STATISTICS GATHERING操作。

SQL> insert into t1 select * from dba_objects;
72375 rows created.

SQL>  commit;
Commit complete.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	db5vba95ya3r4, child number 1
-------------------------------------
insert into t1 select * from dba_objects

Plan hash value: 2114560210

--------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name 	     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT			 |		     |	     |	     |	 396 (100)|	     |
|   1 |  LOAD TABLE CONVENTIONAL		 | T1		     |	     |	     |		  |	     |
|   2 |   OPTIMIZER STATISTICS GATHERING	 |		     | 66748 |	  30M|	 396   (3)| 00:00:01 |
|   3 |    VIEW 				 | DBA_OBJECTS	     | 66748 |	  30M|	 396   (3)| 00:00:01 |
|   4 |     UNION-ALL				 |		     |	     |	     |		  |	     |
|   5 |      TABLE ACCESS BY INDEX ROWID	 | SUM$ 	     |	   1 |	  26 |	   0   (0)|	     |
|*  6 |       INDEX UNIQUE SCAN 		 | I_SUM$_1	     |	   1 |	     |	   0   (0)|	     |
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$  |	   1 |	   6 |	   2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN			 | I_USER_EDITIONING |	   2 |	     |	   1   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ 	     |	   1 |	  40 |	   3   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN 		 | I_OBJ1	     |	   1 |	     |	   2   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS BY INDEX ROWID	 | SUM$ 	     |	   1 |	  26 |	   0   (0)|	     |
|* 12 |       INDEX UNIQUE SCAN 		 | I_SUM$_1	     |	   1 |	     |	   0   (0)|	     |
|* 13 |      FILTER				 |		     |	     |	     |		  |	     |
|* 14 |       HASH JOIN 			 |		     | 72361 |	  10M|	 336   (2)| 00:00:01 |
|  15 |        TABLE ACCESS FULL		 | USER$	     |	 127 |	2286 |	   4   (0)| 00:00:01 |
|* 16 |        HASH JOIN			 |		     | 72361 |	9610K|	 332   (2)| 00:00:01 |
|  17 | 	INDEX FULL SCAN 		 | I_USER2	     |	 127 |	3175 |	   1   (0)| 00:00:01 |
|* 18 | 	TABLE ACCESS FULL		 | OBJ$ 	     | 72361 |	7843K|	 331   (2)| 00:00:01 |
|  19 |       NESTED LOOPS			 |		     |	   1 |	  32 |	   4   (0)| 00:00:01 |
|  20 |        NESTED LOOPS			 |		     |	   1 |	  23 |	   3   (0)| 00:00:01 |
|  21 | 	TABLE ACCESS BY INDEX ROWID	 | IND$ 	     |	   1 |	  10 |	   2   (0)| 00:00:01 |
|* 22 | 	 INDEX UNIQUE SCAN		 | I_IND1	     |	   1 |	     |	   1   (0)| 00:00:01 |
|* 23 | 	TABLE ACCESS CLUSTER		 | TAB$ 	     |	   1 |	  13 |	   1   (0)| 00:00:01 |
|* 24 |        INDEX RANGE SCAN 		 | I_OBJ1	     |	   1 |	   9 |	   1   (0)| 00:00:01 |
|* 25 |       TABLE ACCESS CLUSTER		 | TAB$ 	     |	   1 |	  13 |	   2   (0)| 00:00:01 |
|* 26 |        INDEX UNIQUE SCAN		 | I_OBJ#	     |	   1 |	     |	   1   (0)| 00:00:01 |
|* 27 |       TABLE ACCESS BY INDEX ROWID	 | SEQ$ 	     |	   1 |	   8 |	   1   (0)| 00:00:01 |
|* 28 |        INDEX UNIQUE SCAN		 | I_SEQ1	     |	   1 |	     |	   0   (0)|	     |
|* 29 |       TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$  |	   1 |	   6 |	   2   (0)| 00:00:01 |
|* 30 |        INDEX RANGE SCAN 		 | I_USER_EDITIONING |	   2 |	     |	   1   (0)| 00:00:01 |
|* 31 |       TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$  |	   1 |	   6 |	   2   (0)| 00:00:01 |
|* 32 |        INDEX RANGE SCAN 		 | I_USER_EDITIONING |	   2 |	     |	   1   (0)| 00:00:01 |
|  33 |       NESTED LOOPS SEMI 		 |		     |	   1 |	  29 |	   2   (0)| 00:00:01 |
|* 34 |        INDEX SKIP SCAN			 | I_USER2	     |	   1 |	  20 |	   1   (0)| 00:00:01 |
|* 35 |        INDEX RANGE SCAN 		 | I_OBJ4	     |	   1 |	   9 |	   1   (0)| 00:00:01 |
|  36 |      NESTED LOOPS			 |		     |	   1 |	  36 |	   3   (0)| 00:00:01 |
|  37 |       TABLE ACCESS FULL 		 | LINK$	     |	   1 |	  18 |	   2   (0)| 00:00:01 |
|  38 |       TABLE ACCESS CLUSTER		 | USER$	     |	   1 |	  18 |	   1   (0)| 00:00:01 |
|* 39 |        INDEX UNIQUE SCAN		 | I_USER#	     |	   1 |	     |	   0   (0)|	     |
--------------------------------------------------------------------------------------------------------------

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

   6 - access("S"."OBJ#"=:B1)
   7 - filter("UE"."TYPE#"=:B1)
   8 - access("UE"."USER#"=:B1)
  10 - access("EO"."OBJ#"=:B1)
  11 - filter(BITAND("S"."XPFLAGS",34368126976)=0)
  12 - access("S"."OBJ#"=:B1)
  13 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>2 AND "O"."TYPE#"<>6) OR ("O"."TYPE#"=1 AND  IS
	      NULL) OR ("O"."TYPE#"=2 AND =1) OR ("O"."TYPE#"=6 AND =1)) AND (BITAND("U"."SPARE1",16)=0 OR
	      BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND  IS NULL) OR ( IS NOT NULL AND
	      ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
	      "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
  14 - access("O"."SPARE3"="U"."USER#")
  16 - access("O"."OWNER#"="U"."USER#")
  18 - filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
	      "O"."TYPE#"<>10 AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0))
  22 - access("I"."OBJ#"=:B1)
  23 - filter(("I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=368934881474191032
	      32))
  24 - access("IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2)
       filter("IO"."TYPE#"=2)
  25 - filter(BITAND("T"."PROPERTY",36893488147419103232)=0)
  26 - access("T"."OBJ#"=:B1)
  27 - filter((BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS NULL))
  28 - access("S"."OBJ#"=:B1)
  29 - filter("TYPE#"=:B1)
  30 - access("UE"."USER#"=:B1)
  31 - filter("UE"."TYPE#"=:B1)
  32 - access("UE"."USER#"=:B1)
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))
	      ))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  39 - access("L"."OWNER#"="U"."USER#")

Note
-----
   - dynamic statistics used: statistics for conventional DML


92 rows selected.

当对SQL语句进行硬解析时,我们可以看到优化器检测到表中已经添加了其他行,在执行计划的第2步出现了”OPTIMIZER STATISTICS GATHERING”,同时在最下面注意部分中也指出了:dynamic statistics used: statistics for conventional DML。接下来由于方便快速演示,我们需要强制数据库将优化器统计信息写入到数据字典。

SQL>EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> select distinct object_type from t1 where owner='SYS';

OBJECT_TYPE
-----------------------
INDEX
JOB
CLUSTER
SYNONYM
LOB PARTITION
UNIFIED AUDIT POLICY

SQL> select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	7k4h0792x1vdx, child number 1
-------------------------------------
select distinct object_type from t1 where owner='SYS'

Plan hash value: 2134347679

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |  1173 (100)|	  |
|   1 |  HASH UNIQUE	   |	  |    45 |  3555 |  1173   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  8685 |   670K|  1172   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

Note
-----
   - dynamic statistics used: statistics for conventional DML

23 rows selected.

当对SQL语句进行硬解析时,我们可以看到优化器检测到表中已经添加了其他行,同时在最下面注意部分中也指出了这一点:dynamic statistics used: statistics for conventional DML
在DBA_TAB_STATISTICS中我们可以查询到实时统计信息。在notes列中表示为STATS_ON_CONVENTIONAL_DML.

SQL> SELECT NUM_ROWS,LAST_ANALYZED, BLOCKS, NOTES  FROM   DBA_TAB_STATISTICS WHERE  TABLE_NAME = 'T1' ;

  NUM_ROWS LAST_ANALYZED	   BLOCKS NOTES
---------- ------------------- ---------- -------------------------
     72374 2020-02-29 02:14:29	     1439
    217124 2020-02-29 02:19:05	     4308 STATS_ON_CONVENTIONAL_DML

如果想要禁用这个功能,可以使用NO_GATHER_OPTIMIZER_STATISTICS hint来实现。

参考文档:https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-D55D673B-1FD9-45C9-A109-F440B7E96E62

Oracle 19C新特性-Automatic Indexing

Oracle Database 19c最令人印象深刻的新功能之一是Automatic Indexing。可以说,这是一个非常伟大的创新功能。
Automatic Indexing索引的概述
在我们日常的工作中,我们经常要做优化方面的工作,Automatic Indexing提供自动连续的优化数据库工作负载,稳定可靠的性能以及几乎无需人工干预。它具备以下功能

  • 根据表列的使用情况,识别出潜在的自动索引。文档中称作“candidate indexes”。
  • 将自动索引创建为不可见索引,因此它们不会在执行计划中使用。索引名称包括“ SYS_AI”前缀。
  • 针对SQL语句测试不可见的自动索引,以确保它们可以提高性能。如果它们改善了性能,则将它们变为可见。如果性能没有提高,则将相关的自动索引标记为不可用,然后将其删除。针对失败的自动索引测试的SQL语句被列入黑名单,因此将来将不再考虑将它们用于自动索引。对于首次对数据库运行SQL的优化程序不会考虑自动索引。
  • 删除未使用的索引。

Automatic Indexing索引的如何工作
这里我参考了Automatic Indexing in Oracle Database 19c

自动索引过程每15分钟在后台运行一次,并执行以下操作:
1.识别自动索引候选者
根据SQL语句中表列的用法来标识自动索引候选者。确保表统计信息是最新的。具有过时统计信息的表不被视为自动索引。
2.为自动索引候选项创建不可见的自动索引
自动索引候被创建为不可见的自动索引,也就是说,这些自动索引不能在SQL语句中使用。
3.根据SQL语句验证不可见的自动索引
不可见的自动索引针对SQL语句进行了验证。如果通过使用这些索引提高了SQL语句的性能,则将这些索引配置为可见索引(visible indexes),以便可以在SQL语句中使用它们。如果使用这些索引不能提高SQL语句的性能,则将这些索引配置为不可用的索引(unusable indexes),并将SQL语句列入黑名单。稍后,自动索引过程将删除不可用的索引。列入黑名单的SQL语句将来不允许使用自动索引。
4.删除未使用的自动索引
长时间不使用的自动索引将被删除。

让我们在环境中测试一下Automatic Indexing,我使用的是Redhat 7.8操作系统和Oracle Database 19.3.0.0.0版本
我们可以从视图中CDB_AUTO_INDEX_CONFIG查看Automatic Indexing的设置通过查看视图可以发现默认AUTO_INDEX_MODE为OFF,默认是关闭的。

我们可以通过DBMS_AUTO_INDEX包来控制Automatic Indexing的开关。

  • IMPLEMENT:打开自动索引。使改进性能的新索引可见,并且可供优化程序使用。
  • REPORT ONLY :打开自动索引,但是新索引仍然不可见。
  • OFF :关闭自动索引。

打开Automatic Indexing,我们发现运行直接报错ORA-40216: feature not supported,这是什么情况?不支持这个功能?

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283
ORA-06512: at line 1

通过查询发现不支持这个功能?只有云数据库和Exadata才支持这个功能。通过网上搜索一番,发现可以设置参数“_exadata_feature_on”=true来开启进行测试,通过设置参数我们将”Automatic Indexing”功能打开了。

SQL> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.

默认情况之下,将在默认的永久表空间中创建自动索引,如果想指定一个表空间来保存它们,可以使用AUTO_INDEX_DEFAULT_TABLESPACE方法。下面,我们创建一个表空间来保存自动索引,并相应地设置属性。

SQL> alter session set container=ORCLPDB1; 
Session altered.

SQL> CREATE TABLESPACE AUTOINDEX_T1 DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/autoindex_t1.dbf' SIZE 500M AUTOEXTEND OFF;
Tablespace created.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTOINDEX_T1');
PL/SQL procedure successfully completed.

如果想用回默认表空间,把第二个参数设置成NULL就可以了。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

接下来我们可以使用AUTO_INDEX_SCHEMA包在SCHEMA级别进行控制。该属性指定包含或者排除的SCHEMA。

SQL> create user test identified by test;
User created.

SQL> grant dba to test;
Grant succeeded.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
PL/SQL procedure successfully completed.
如果要取消可以设置成NULL
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

DBMS_AUTO_INDEX包中还提供了一些方法。
AUTO_INDEX_RETENTION_FOR_AUTO:在数据库中保留未使用的自动索引的天数,之后将其删除。默认值为373天。
AUTO_INDEX_RETENTION_FOR_MANUAL:在数据库中保留未使用的手动创建的索引(非自动索引)的天数,之后将其删除。设置NULL为时,不会删除手动创建的索引。默认值为NULL。
AUTO_INDEX_REPORT_RETENTION:删除自动索引日志之前在数据库中保留的天数。由于自动索引报告是根据这些日志生成的,因此,在超过为所指定的值的时间内,无法生成自动索引报告
AUTO_INDEX_REPORT_RETENTION。自动索引日志的保留期。报告基于这些日志。默认31天。
AUTO_INDEX_SPACE_BUDGET:用于自动索引的表空间大小的百分比。仅当在数据库创建期间指定的默认表空间用于存储自动索引时,才可以使用此配置设置。
AUTO_INDEX_COMPRESSION 启用和禁用自动索引的高级索引压缩的值。支持的值为ON,为自动索引启用高级压缩。OFF:禁用自动索引的高级压缩。

DROP_SECONDARY_INDEXES方法可以帮助删除索引(用于约束的索引除外)。这样一来,我们就可以拥有一个干净的系统,自动索引就可以做出所有索引决策。

表级别的删除
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('SCHEMA','TABLE'); 
SCHEMA级别的删除
dbms_auto_index.drop_secondary_indexes('SCHEMA');
数据库级别的删除
dbms_auto_index.drop_secondary_indexes;

我们可以使用REPORT_ACTIVITY或者是REPORT_LAST_ACTIVITY来输出报告。REPORT_ACTIVITY功能允许您显示指定时间段内的数据,默认为最后一天。而REPORT_LAST_ACTIVITY功能报告上一次自动索引操作。两者都允许使用以下参数定制输出。
TYPE :允许的值(TEXT,HTML,XML)。
SECTION:允许的值(SUMMARY,INDEX_DETAILS,VERIFICATION_DETAILS,ERRORS,ALL)。您还可以使用带有“ +”和“-”字符的组合来指示是否应包含或排除某些内容。例如“ SUMMARY + ERRORS”或“ ALL -ERRORS”。
LEVEL :允许的值(BASIC,TYPICAL,ALL)。

下面显示了使用SQL中的这些功能的一些示例。

SET LONG 1000000 PAGESIZE 0
最近24小时的默认TEXT报告。
SELECT DBMS_AUTO_INDEX.report_activity()FROM dual; 
一天之前的HTML报告。
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end => SYSTIMESTAMP-1,
         type =>'HTML')
FROM dual; 
最新活动的HTML报告。
SELECT DBMS_AUTO_INDEX.report_last_activity(
         type =>'HTML')
FROM dual;

最后我们来做个案例。先创建一张表。

create table test (id1 number not null,id2 number not null,id3 number not null);

往表中插入数据。并且收集表的统计信息。

insert into test
select id1.n, id2.x, ceil(dbms_random.value(0, 100))
from (select level - 1 n from dual connect by level < 300) id1 , 
(select level x from dual connect by level < 50000) id2 
where id2.x <= id1.n * 3000; exec dbms_stats.gather_table_stats(OWNNAME=>'TEST',TABNAME=>'TEST' , cascade => true);

SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='TEST';

TABLE_NAME					     NUM_ROWS LAST_ANALYZED
-------------------------------------------------- ---------- -------------------
TEST						     14507718 2020-02-28 01:07:18

在其他节点开启sqlplus窗口,批量执行下列语句

select avg(id1) from test where id1 between 1 and 100;
select avg(id1) from test where id1 between 1 and 200;
select avg(id1) from test where id1 between 1 and 300;
select sum(id1) from test where id1 between 1 and 100;
select sum(id1) from test where id1 between 100 and 200;
select sum(id1) from test where id1 between 200 and 300;
select sum(id1) from test where id1=1;
select sum(id1) from test where id1=2;
select sum(id1) from test where id1=3;
select count(id1) from test where id1 between 1 and 100;
select distinct(id1) from test where id1 between 1 and 100;
select distinct id1 from test where id1 is not null;

然后执行报告查看。我这里其实已经有一个候选的索引,但是因为空间我弄的很小,所以创建的时候失败了。

SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start 	      : 27-FEB-2020 01:13:43
 Activity end		      : 28-FEB-2020 01:13:43
 Executions completed	      : 8
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates	     : 1
 Indexes created	     : 0
 Space used		     : 0 B
 Indexes dropped	     : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used	   : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
-------------
 - ORA-01652: unable to extend temp segment by 1024 in tablespace AUTOINDEX_T1
--------------------------------------------------------------------------------

我们可以查看DBA_AUTO_INDEX_IND_ACTIONS视图,来查看系统对自动索引执行的操作。可以看到最开始创建的索引是UNUSABLE和INVISIBLE的。这符合预期。后面发生了rebuild因为空间不足所以报了ORA-1652的错误。

 ACTION_ID INDEX_NAME		     INDEX_OWNER	  COMMAND	  STATEMENT								                  ERROR#
---------- ------------------------- -------------------- --------------- ---------------------------------------------------------------------- ----------
	 1 SYS_AI_6nfam1248fft7      TEST		  CREATE INDEX	  CREATE INDEX "TEST"."SYS_AI_6nfam1248fft7"  ON "TEST"."TEST"("ID1")         0
                                                                          TABLESPACE "AUTOINDEX_T1" UNUSABLE INVISIBLE AUTO  ONLINE
	 2 SYS_AI_6nfam1248fft7      TEST		  REBUILD INDEX   ALTER INDEX "TEST"."SYS_AI_6nfam1248fft7"   REBUILD  ONLINE		       -1652
	 3 SYS_AI_6nfam1248fft7      TEST		  REBUILD INDEX   ALTER INDEX "TEST"."SYS_AI_6nfam1248fft7"   REBUILD			       -1652

参考文档:https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-082972AD-1866-411A-8250-9B23D4088582

ORACLE 19C新特性-HYBRID PARTITIONED TABLES

这是我比较喜欢的一个新特性。在Oracle 12c第2版中,新功能之一是能够在外部表上创建分区。Oracle 19c中的新功能是我们可以创建混合分区表的功能。 可以在数据库中存在一些分区,某些分区托管在数据库外部 。这个可以在普通文件系统,ACFS文件系统,甚至是Hadoop文件系统上。使用此功能,我们可能希望将表中的较旧的非活动分区移动到数据库外部托管的廉价存储中,而活动数据则托管在Oracle数据库的分区中。而”HYBRID PARTITIONED TABLES”功能使我们能够将内部和外部分区进行这种集,所有数据都在同一个表中。

对于混合分区表,分区既可以在Oracle数据库中的表空间上,也可以在外部源上,例如具有逗号分隔值(CSV)的Linux文件或具有的Hadoop分布式文件系统(HDFS)上的文件。我们来测试一下这个新功能吧。

让我们假设我们有一些学生的信息,2017年和2018年的数据位于平面文件中(st2017.txt和st2018.txt)。现在,我们创建一个HYBRID分区表,该表在student_p1列上进行了范围分区。2017年和2018年的分区是外部分区,而2019年的分区是内部分区。

请注意EXTERNAL LOCATION子句,该子句指示该分区是外部分区,并且数据包含在上述文件中。外部文件位于为目录DATA_PUMP_DIR定义的目录位置中。

CREATE TABLE students
(student_id number, 
 student_name varchar2(20),
 sex number,
 birthday date)
EXTERNAL PARTITION ATTRIBUTES (
TYPE oracle_loader
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS(FIELDS TERMINATED BY ',')
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (student_id) 
(PARTITION student_2017 VALUES LESS THAN (200) EXTERNAL LOCATION ('st2017.txt'),
 PARTITION student_2018 VALUES LESS THAN (400) EXTERNAL LOCATION ('st2018.txt'),
 PARTITION student_2019 VALUES LESS THAN (600),
 PARTITION student_pmax VALUES LESS THAN (MAXVALUE)
)
;    
Table created.

SQL> select hybrid from dba_tables where table_name='STUDENTS';

HYB
---
YES

[oracle@oracle-19c-vagrant ]$ cat st2017.txt 
1,'zhangsan',0,20-JAN-2008,
2,'lisi',0,12-FEB-2008,
3,'wangyi',0,15-JUL-2008,
4,'zhangjian',0,20-MAR-2008,
5,'zhaoxiaoxia',1,5-APR-2008,
[oracle@oracle-19c-vagrant ]$ cat st2018.txt 
201,'liming',0,19-JAN-2009,
202,'lisisi',1,29-DEC-2008,
203,'wanghaili',1,7-MAR-2009,
204,'zengqian',1,16-JUL-2009,
205,'liufeng',0,14-APR-2009,

现在我们的混合分区表就创建好了,我们可以做一些基础的查询。

SQL>  select * from students where student_id < 200 ;

STUDENT_ID STUDENT_NAME 	       SEX BIRTHDAY
---------- -------------------- ---------- ---------
	 1 'zhangsan'			 0 20-JAN-08
	 2 'lisi'			 0 12-FEB-08
	 3 'wangyi'			 0 15-JUL-08
	 4 'zhangjian'			 0 20-MAR-08
	 5 'zhaoxiaoxia'		 1 05-APR-08

SQL> select * from students partition(student_2018);

STUDENT_ID STUDENT_NAME 	       SEX BIRTHDAY
---------- -------------------- ---------- ---------
       201 'liming'			 0 19-JAN-09
       202 'lisisi'			 1 29-DEC-08
       203 'wanghaili'			 1 07-MAR-09
       204 'zengqian'			 1 16-JUL-09
       205 'liufeng'			 0 14-APR-09

这里需要注意的一点是,我们不能在外部分区上执行DML操作。只能对数据位于数据库中的内部分区执行数据操作。例如。

SQL> insert into students values(6,'Test',0,sysdate);
insert into students values(6,'Test',0,sysdate)
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified

SQL> delete students where STUDENT_ID=205;
delete students where STUDENT_ID=205
       *
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table

SQL> insert into students values(401,'Test',0,sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from students partition (student_2019);

STUDENT_ID STUDENT_NAME 	       SEX BIRTHDAY
---------- -------------------- ---------- ---------
       401 Test 			 0 29-FEB-20

当然还有很多高级功能,例如怎么把普通分区表转换成混合分区表,将混合分区表转换成 Internal Partitioned Tables等等,详情参考参考文档:Managing Hybrid Partitioned Tables
官方链接:https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/manage_hypt.html#GUID-4D0F2F16-5601-4240-AD94-219D85FD6AED

一次因网络引起的诡异GC问题,DBA该怎么做?

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:一次因网络引起的诡异GC问题,DBA该怎么做?

我们在日常工作中,就像西天取经的僧人,总是会遇到各式各样的“妖怪”。这些“妖怪”总是一个又一个的阻挡在我们面前,你必须想办法击败它们。
听说小A同学最近遇到了一个很妖的问题,就这个问题我们来采访一下小A同学。
小B:你觉得RAC GC问题一般在什么情况下会产生?
小A:这个问题嘛其实很简单,我们要先从Oracle RAC的机制说起,RAC是一种共享磁盘的体系结构,多个服务器上的实例会同时打开数据库,并缓存磁盘中的数据。而当在一个节点上执行SQL,需要请求的buffer在remote实例上时,就会使用心跳进行传输。此时在本地节点上可能就会观察到GC类的等待事件。一般大量GC问题都是应用交叉访问引起的。
小B:那这一次的GC问题是应用交叉访问导致的吗?
小A:这次并不是,因为很多时候我们观察到系统权限类的SQL语句也在等待大规模的gc buffer busy acquire。
小B:系统语句?
小A:对,就是下面这个语句,他也产生了很多的GC。

select event,p1,p2,p3 from v$session where sql_id='05uqdabhzncdc'
EVENT P1 P2 P3
---------------------------------------------------------------- ---------- ---------- ----------
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc cr request 1 46842 1
gc buffer busy acquire 1 46842 1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05uqdabhzncdc, child number 1
-------------------------------------
select role# from defrole$ d,user$ u where d.user#=:1 and
u.user#=d.user# and u.defrole=2 union select privilege# from sysauth$
s,user$ u where (grantee#=:1 or grantee#=1) and privilege#>0 and not
exists (select null from defrole$ where user#=:1 and
role#=s.privilege#) and u.user#=:1 and u.defrole=3
Plan hash value: 552533229
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT UNIQUE | | 2 | 30 | 7 (29)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 1 | 14 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS CLUSTER| USER$ | 1 | 7 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_DEFROLE1 | 1 | 7 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 16 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS CLUSTER| USER$ | 1 | 7 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 10 | INLIST ITERATOR | | | | | |
|* 11 | INDEX RANGE SCAN | I_SYSAUTH1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_DEFROLE1 | 1 | 7 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("U"."DEFROLE"=2)
5 - access("U"."USER#"=:1)
6 - access("D"."USER#"=:1)
8 - filter("U"."DEFROLE"=3)
9 - access("U"."USER#"=:1)
11 - access((("GRANTEE#"=:1 OR "GRANTEE#"=1)) AND "PRIVILEGE#">0)
filter( IS NULL)
12 - access("USER#"=:1 AND "ROLE#"=:B1)

小B:这个语句感觉没什么问题啊,这是查数据字典权限的,执行计划很好,关键表走的UNIQUE SCAN,应该很快就返回结果的。
小A:是的,正常情况都是秒出结果的,并不会产生GC等待。但是我们这个有点小异常,经常看到100-200个GC等待事件。
小B:那究竟是什么问题呢?
小A:我们先来看AWR报告吧,从11.2.0.4我们就可以通过来Interconnect Ping Latency Stats查看网络延迟类的问题了。

你注意看,这是2节点的AWR报告。报告里ping 1和ping 3节点的延迟非常的高。这里分别是做了500字节和8KB的ping,平均延迟都是30几ms和10几ms。我们在看oswatch,从节点1到节点2的traceroute可以看到正常时间点是0.1ms,慢的时候足足5ms。

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
zzz ***Fri Sep 6 10:00:10 CST 2019
traceroute to 192.168.187.129 (192.168.187.129), 30 hops max, 60 byte packets
1 sid1-priv (192.168.187.129) 0.126 ms 0.103 ms 0.100 ms
traceroute to 192.168.187.130 (192.168.187.130), 30 hops max, 60 byte packets
1 sid2-priv (192.168.187.130) 5.015 ms 5.096 ms 5.074 ms <<<<<<<<<<<<<<<<<<<<<<<<<<<
traceroute to 192.168.187.131 (192.168.187.131), 30 hops max, 60 byte packets
1 sid3-priv (192.168.187.131) 1.286 ms 1.176 ms 1.156 ms
zzz ***Fri Sep 6 10:00:41 CST 2019
traceroute to 192.168.187.129 (192.168.187.129), 30 hops max, 60 byte packets

小B:嗯,这么看来,是网络问题啊,网络延迟这么高,GC高那应该是理所当然的!
小A:错了,表面看是网络延迟问题,但是经过我们的验证发现,把数据库停下来之后,网络延迟就消失了。当没有业务运行的时候,延迟都在0.00几,这说明是业务的压力上来导致的延迟。

这里bond0是双网卡绑定的私网的地址。rxkb/s是每秒收包的数量,而txkb/s是每秒发包的数量。这里数据库运行的时候每秒收发都上100MB/S了。
小B:那这个问题,怎么处理的?
小A:这个问题说实话是比较妖的,按照道理说这个网卡是万兆网卡,不至于100MB/S就处理不过来了,我们开始怀疑肯定是硬件之类的问题,或者是网络配置的问题,于是我们首先和其他数据库主机做了对比,就发现这个网卡的绑定模式和其他数据库不太一样。这个网卡的绑定模式是4。
说到网卡绑定模式,主要有7种模式。分别是:
mode=0 round-robin  轮询策略(Round-robin policy)
mode=1 active-backup  主备策略(Active-backup policy)
mode=2 load balancing (xor)  异或策略(XOR policy)
mode=3 fault-tolerance (broadcast)  广播策略(Broadcast policy)
mode=4 lacp IEEE 802.3ad   动态链路聚合(IEEE 802.3ad Dynamic link aggregation)
mode=5 transmit load balancing  适配器传输负载均衡(Adaptive transmit load balancing)
mode=6 adaptive load balancing  适配器负载均衡(Adaptive load balancing)

当前我们采取的是动态链路聚合模式,这种模式必须是两块网卡具备相同速率和双工模式才行。而且还需要交换机的支持。这种模式本身也没太大的问题,不过不是很常用。

一般应用的是mode=0的轮询策略、mode=1的主备策略,还有mode=6的负载均衡方式。由于其他数据库使用的是mode=1,唯独这个使用的是mode 4,首先怀疑的就是这个点。
但是因为停机时间一个月只有一次,没有办法进行测试,这次大家集体商量之后,决定双网卡绑定工作模式改造的同时,把网卡、网卡插槽、网线都更换一下。彻底的排除硬件上网络可能出现的问题。

小B:嗯,那做完这些操作之后,变好了吗?
小A:没有,当我们把这些操作都做了,数据库启动之后,白天工作时间段延迟依旧很高。
小B:额,那不是没找到问题的根源?
小A:是的,我们又仔细的检查了一遍,这次发现主要来源于oswatch中的mpstat,可以看到在业务高峰期cpu 15的%soft,总是100%。这个发现是非常重要的。

在网上搜索一番,可以发现大量的网卡软中断导致的网络延迟。

小B:越来越精彩了,这块属于网络问题了,我们DBA遇到这种问题该怎么办呢?
小A:我们要研究啊。DBA什么事情都要干,必须追求卓越。
网卡与操作系统的交互其中一个就是方式就是中断,网卡在收到了网络信号之后,主动就发送中断到cpu,而cpu会立即停止其他事情对这个中断信号进行处理。由于数据包速率的增长,带来的中断渐渐超过了单个cpu核可处理的范围。从而导致了网络延迟和丢包。在这里我还要提高Linux上的一个服务,叫做irqbalance。该服务就是专门解决网卡性能问题的,用于优化中断分配,将中断尽可能的均匀的分发给各个cpu core,充分利用cpu多核,提升性能。虽然开启了这个服务,但是我们实际情况是网卡中断就绑定在特殊的cpu 15上面。我们必须把这个中断手动重新绑一下。这个就不得不提到中断亲缘性(smp_affinity)设置。只有 kernel 2.4 以后的版本才支持把不同的硬件中断请求(IRQs)分配到特定的 CPU 上,这个绑定技术被称为 SMP IRQ Affinity。当前操作系统版本是RedHat 6,内核是2.6的。我们可以查看操作系统自带的说明:Linux-2.6.31.8/Documentation/IRQ-affinity.txt
至于绑定方式,因为购买的是华为服务器,在华为服务器的性能优化最佳的附录里面,会有网卡中断绑定的方法介绍。

操作方法有点复杂:
① 首先我们需要停止irqbalance服务。
Service irqbalance off
② 确认哪块网卡是私有网卡,然后执行下列语句,查看分配给网卡的中断号。
cat /proc/interrupts | grep -i ethx
③ 查看中断号和cpu绑定的情况,根据上面的中断号查看和cpu的亲缘性。
cat /proc/irq/126/smp_affinity
④ 中断绑定,将ethx的N个中断绑定到不同的cpu。
echo 16 > /proc/irq/126/smp_affinity

如果觉得麻烦,可以直接使用华为驱动包中提供的脚本。
当然做了这个操作之后缓解了一下症状。之前都是压到一个cpu核上造成100% soft,现在感觉还是压在一个核上,那么这个又是什么问题呢?在网上搜到了一篇美团点评的帖子,是这么理解这个问题的。当给中断设置了多个cpu core后,它也仅能由设置的第一个cpu core来处理,其他的cpu core并不会参与中断处理,原因猜想是当cpu平行收包时,不同的核收取了同一个queue的数据包,但处理速度不一致,导致提交到IP层后的顺序也不一致,这就会产生乱序的问题,由同一个核来处理可以避免了乱序问题。
参考链接:https://tech.meituan.com/2018/03/16/redis-high-concurrency-optimization.html
小B:问题还是没解决啊,看来也是有限制的。
小A:是的,当然也可以优化,根据上面的文档,咱们也可以把Oracle数据库的LMS进程的亲缘性设置到指定的cpu上去,然后把中断设置到另外的cpu上去,互相不冲突就可以解决了。但是我们没这么做,因为LMS进程比较多,主机上出cpu也比较多,设置起来较为麻烦,我们最后是通过参数优化来解决的。
第一个优化方式,是IRQ coalescing,中断合并主要是为了做延迟跟cpu开销之间的权衡。当网卡适配器收到一个帧之后,不会立即的对系统产生中断,而是等一段时间,收集到更多的包之后再一次性的处理,这会降低cpu的负载,但是会产生等待时间。

自适应模式使网卡能够自动调节中断聚合,在我们的机器上可以看到是没开启的。在自适应模式下,驱动程序将检查流量模式和内核接收模式,并在运行中估算合并设置,以防止数据包丢失。这里我们可以建议启动自适应模式。

# ethtool -C ethx adaptive-rx on

第二个优化的手段是,UDP根据源IP和目的IP,按照哈希结果将数据流分发到网卡的不同接收队列中。

# ethtool --config-ntuple ethx rx-flow-hash udp4 sdfn

在做了上面两个操作后,软中断的cpu使用率下降到了30%-60%之间,起到了明显的改善,处理中断的cpu只要不是100%,网络延迟丢包也就不存在了。后续观察网络的延迟都是在0.01ms以下,数据库的GC等待事件也随之消失了
小B:嗯,这个问题真是麻烦啊,还好你们一直坚持排查。
小A:那当然,DBA得追求卓越,把问题都搞清楚然后再解决。虽然这个问题是一只很厉害的“妖怪”,一度让我们很困扰,但是打败了这只“妖怪”之后,我们像经历了一次脱胎换骨,对网络问题又加深了理解,还是收获很大的。
小B:嗯,感谢分享,确实收获颇多,以后我也要像你一样在技术上追求卓越!