一次enq:TX-index contention故障分析

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:一次enq:TX-index contention故障分析
这个故障是头一天晚上9点多开始,客户发现索引enq:TX-index contention分裂很严重,系统程序插入的时候有大量的enq:TX-index contention等待,同是伴随着还有gc方面的等待。在12点多的时候,重建了索引最后恢复了。现在需要我们分析根本原因。

首先通过当天晚上10点多的AWR报告可以发现,enq:TX-index contention分裂很严重,两个节点之间的网络延迟非常严重。


 

 

通过对比正常时间段和异常时间段的leaf node splits,发现每秒种索引分裂的个数并没有异常增长,也就侧面说明业务量没有增长。

 

 

我们做个相关插入sql语句的sqlhc报告,发现引起索引争用的语句。执行的频次没有发现很大的增长。

 

 
通过分析,我们可以认定这个插入SQL是被影响的。那么究竟是被什么影响的呢?

 
通过分析7天的awr情况发现在5月20日从早上11点开始,数据库CPU就一直增长,到下午18点已经达到了80%,最终一直涨到100%。继续查询AWR报告,发现占用CPU高的语句就一条。占比达到95%。

 
对该语句做SQLHC报告,发现该语句的执行计划并没变,执行次数也没有明显的差异,但是逻辑读在问题期间大幅增长了。从而导致执行语句需要更多的cpu。

 

 

 
分析该语句的执行计划,可以发现,走了全表扫描。而它使用的谓词条件上正好有索引,优化器可以使用索引INX_OWN_O_I_REQ_ORDER_NO,但是没有使用,通过查看报告,发现索引的order_no上缺乏列的统计信息。

 
此时处理的办法有两种,一种是对表进行统计信息收集,在收集的时候可以选择,method_opt=> ‘for all indexed columns’。第二种方法就是对SQL语句进行绑定。客户选择了第二种方式,我们将SQL绑定之后,系统消耗的cpu逐步降低,上面那条插入语句瞬间就好了很多,客户反映插入速度变快了。最终这个问题被定义为CPU资源繁忙导致网络软中断,引起了gc,最终导致enq:TX-index contention争用严重的问题。

一次”qmxdpls_subhea”内存耗尽的问题分析

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:一次”QMXDPLS_SUBHEA”内存耗尽的问题分析
一套RAC数据库其中的一个节点在17点05分左发生了重启。通过分析发现是内存耗尽导致了交换分区的使用,同时system cpu 100%导致的crash。首先观察2节点16点-16点30的awr报告,可以发现主机内存256GB,SGA使用了80GB,PGA使用了90GB左右。

从系统残余内存来看,仍然有256-80-90=76G可以用,而操作系统正常大概能占用50GB,应该还剩余26GB可用。但是问题就在于这个机器的大页设置不正确(当前设置为60GB)。导致一部分大页的内存是空闲没法使用的。

HugePages_Total: 31748
HugePages_Free: 5831
HugePages_Rsvd: 5828
HugePages_Surp: 0
Hugepagesize: 2048 kB

在从oswatch来看,8号11点还剩30GB内存,而到了9号11点则剩下16GB内存,到了下午的15点左右下降到了1.68GB。可见内存是持续缓慢的下降耗尽的。不是一个并发或者大的事务上来导致的。

zzz ***Sun Dec 8 11:00:03 CST 2019
MemTotal: 264410232 kB
MemFree: 31472452 kB

zzz ***Mon Dec 9 10:00:15 CST 2019
MemTotal: 264410232 kB
MemFree: 17231808 kB

zzz ***Mon Dec 9 16:00:19 CST 2019
MemTotal: 264410232 kB
MemFree: 1765052 kB

那么内存缓慢耗尽是什么原因呢?通过观察节点1没重启的进程情况,发现以下问题:

DEDICATED 7062 RM bea 405.396004 405383755 428595614 3145728 428595614
DEDICATED 7856 RM bea 431.021004 432892859 457824670 5505024 457824670
DEDICATED 13649 RM bea 491.771004 496860795 516807070 786432 516807070
DEDICATED 14834 RM bea 503.458504 509014107 531356062 3080192 531356062
DEDICATED 12960 RM bea 539.208504 548221595 570612126 4849664 570612126
DEDICATED 3089 RM bea 541.458504 548346875 572774814 4653056 572774814
DEDICATED 3619 RM bea 565.958504 575983915 599382430 5570560 599382430
DEDICATED 231 RM bea 666.458504 680193475 704633246 5439488 704633246
DEDICATED 21156 RM bea 703.333504 718921091 738646430 786432 738646430
DEDICATED 19747 RM bea 820.896004 839278371 861723038 589824 861723038

SQL> select sid,event,program,machine,sql_id,status from v$session where sid=19747;

SID EVENT PROGRAM MACHINE SQL_ID STATUS
---------- ------------------------------ ------------------------------------------------ ---------------------------------------- ------------- --------
19747 SQL*Net message from client JDBC Thin Client whrm_app6 INACTIVE

SQL> select sid,event,program,machine,sql_id,PREV_SQL_ID,status from v$session where sid=19747;

SID EVENT PROGRAM MACHINE SQL_ID PREV_SQL_ID STATUS
---------- ------------------------------ ------------------------------------------------ ---------------------------------------- ------------- ------------- --------
19747 SQL*Net message from client JDBC Thin Client whrm_app6 bunvx480ynf57 INACTIVE

SQL> select sid,event,program,machine,sql_id,PREV_SQL_ID,status from v$session where sid=21156;
SID EVENT PROGRAM MACHINE SQL_ID PREV_SQL_ID STATUS
---------- ------------------------------ ------------------------------------------------ ---------------------------------------- ------------- ------------- --------
21156 SQL*Net message from client JDBC Thin Client whrm_app8 bunvx480ynf57 INACTIVE

SQL> select sid,event,program,machine,sql_id,PREV_SQL_ID,status from v$session where sid=231;

SID EVENT PROGRAM MACHINE SQL_ID PREV_SQL_ID STATUS
---------- ------------------------------ ------------------------------------------------ ---------------------------------------- ------------- ------------- --------
231 SQL*Net message from client JDBC Thin Client whrm_app6 bunvx480ynf57 INACTIVE

可以看到相关进程占用的内存很高,已经是inactive了,但是内存并没发生回收。通过oradebug dump heapdump 命令dump其中的800MB的进程,发现进程分配了12000个extent,而这些extend中的chunk都是”qmxdpls_subhea“。

而关于这个内存区域的问题,通过mos搜索,可以发现出现大量的memory leak(内存泄露)的情况。

我们把这个情况反映给了应用人员,应用人员根据程序定位到是没关闭xmltype相关对象导致的内存泄露。对于XMLTYPE这类的操作,需要打开后执行free()进行关闭,释放内存。

参考文档:
Best Practises for XMLType Temporary LOB Usage (Doc ID 1955135.1)
How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)

四个节点实例异常重启故障分析

今天发生了一个故障,居然是四个RAC节点全部同一时间发生重启了,这种情况还是很少见的。以下是问题分析过程

节点1日志

2020-05-12T04:50:13.955295+08:00
Errors in file /u01/app/oracle/diag/rdbms/dwdb/dwdb1/trace/dwdb1_ora_118868.trc  (incident=4418543):
ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2
skgxpvfynet: mtype: 61 process 123980 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
opidrv aborting process M003 ospid (123980) as a result of ORA-603
2020-05-12T05:07:29.342931+08:00
Reconfiguration started (old inc 8, new inc 10)
List of instances (total 1) :
1
Dead instances (total 3) :
2 3 4
My inst 1
2020-05-12T05:07:31.305816+08:00
NOTE: ASMB process state dumped to trace file /u01/app/oracle/diag/rdbms/dwdb/dwdb1/trace/dwdb1_gen0_178160.trc
2020-05-12T05:07:32.229320+08:00
skgxpvfynet: mtype: 61 process 124166 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
opiodr aborting process unknown ospid (124166) as a result of ORA-603
2020-05-12T05:09:27.659099+08:00
skgxpvfynet: mtype: 61 process 124818 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
2020-05-12T05:10:08.457843+08:00
Process m003 died, see its trace file
2020-05-12T05:10:34.164320+08:00
DDE: Problem Key 'ORA 603' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2020-05-12T05:10:39.931465+08:00
skgxpvfynet: mtype: 61 process 125146 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
2020-05-12T05:10:39.931458+08:00
opiodr aborting process unknown ospid (124818) as a result of ORA-603
2020-05-12T05:10:39.931454+08:00
GCR0[178417]: LMHB process succesfully killed
2020-05-12T05:11:40.473632+08:00
opiodr aborting process unknown ospid (125146) as a result of ORA-603
2020-05-12T05:18:01.842419+08:00
ksxp_exafusion_enabled_dcf: ipclw_enabled=0
Starting ORACLE instance (normal) (OS id: 108120)

节点2日志

2020-05-12T04:47:56.141213+08:00
Incremental checkpoint up to RBA [0x254f.3988e5.0], current log tail at RBA [0x254f.39cc3a.0]
2020-05-12T05:05:18.334836+08:00
NOTE: ASMB0 registering with ASM instance as Flex client 0x10006 (reg:3205650803) (reconnect)
2020-05-12T05:06:11.234166+08:00
Dumping diagnostic data in directory=[cdmp_20200512050610], requested by (instance=4, osid=605758 (GEN0)), summary=[abnormal instance termination].
2020-05-12T05:06:12.197007+08:00
ERROR: terminating instance because ASMB is stuck for 241 seconds
GEN0 (ospid: 110374): terminating the instance due to error 15082
2020-05-12T05:06:18.242304+08:00
Instance terminated by GEN0, pid = 110374
2020-05-12T05:15:33.418428+08:00
ksxp_exafusion_enabled_dcf: ipclw_enabled=0

节点3日志

2020-05-12T05:05:49.290986+08:00
LGWR (ospid: 364374) waits for event 'enq: CF - contention' for 74 secs.
2020-05-12T05:05:49.291033+08:00
LGWR (ospid: 364374) is hung in an acceptable location (cfio 0x11.00).
2020-05-12T05:06:05.433427+08:00
NOTE: ASMB0 registering with ASM instance as Flex client 0x10007 (reg:4119066454) (reconnect)
2020-05-12T05:06:11.239521+08:00
Dumping diagnostic data in directory=[cdmp_20200512050610], requested by (instance=4, osid=605758 (GEN0)), summary=[abnormal instance termination].
2020-05-12T05:06:16.340150+08:00
Dumping diagnostic data in directory=[cdmp_20200512050613], requested by (instance=2, osid=110374 (GEN0)), summary=[abnormal instance termination].
2020-05-12T05:06:34.967473+08:00
ERROR: terminating instance because ASMB is stuck for 242 seconds
GEN0 (ospid: 364027): terminating the instance due to error 15082
2020-05-12T05:06:38.182059+08:00
System state dump requested by (instance=3, osid=364027 (GEN0)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/dwdb/dwdb3/trace/dwdb3_diag_364051_20200512050638.trc
2020-05-12T05:06:40.996565+08:00
Instance terminated by GEN0, pid = 364027
2020-05-12T05:15:36.490770+08:00
ksxp_exafusion_enabled_dcf: ipclw_enabled=0 
Starting ORACLE instance (normal) (OS id: 10317)
2020-05-12T05:15:36.499203+08:00
CLI notifier numLatches:131 maxDescs:19888
2020-05-12T05:15:36.501049+08:00
**********************************************************************
2020-05-12T05:15:36.501093+08:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

节点4日志

2020-05-12T05:04:12.064862+08:00
CKPT (ospid: 605951) waits for event 'enq: CF - contention' for 63 secs.
2020-05-12T05:04:12.064910+08:00
CKPT (ospid: 605951) is hung in an acceptable location (cfio 0x11.00).
2020-05-12T05:05:18.151878+08:00
NOTE: ASMB0 registering with ASM instance as Flex client 0x10008 (reg:2344414256) (reconnect)
2020-05-12T05:05:25.774637+08:00
CKPT (ospid: 605951) waits for event 'enq: CF - contention' for 137 secs.
2020-05-12T05:05:25.774701+08:00
CKPT (ospid: 605951) is hung in an acceptable location (inwait 0x201.00).
2020-05-12T05:06:10.637714+08:00
ERROR: terminating instance because ASMB is stuck for 242 seconds
GEN0 (ospid: 605758): terminating the instance due to error 15082
2020-05-12T05:06:10.646458+08:00
DWPUB(3):opiodr aborting process unknown ospid (220276) as a result of ORA-1092
2020-05-12T05:06:10.646808+08:00
DWPUB(3):opiodr aborting process unknown ospid (330270) as a result of ORA-1092
2020-05-12T05:06:10.647256+08:00
DWPUB(3):opiodr aborting process unknown ospid (371195) as a result of ORA-1092
2020-05-12T05:06:10.651213+08:00
DWPUB(3):opiodr aborting process unknown ospid (220280) as a result of ORA-1092
2020-05-12T05:06:11.233314+08:00
System state dump requested by (instance=4, osid=605758 (GEN0)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/dwdb/dwdb4/trace/dwdb4_diag_605768_20200512050611.trc
2020-05-12T05:06:16.690258+08:00
Instance terminated by GEN0, pid = 605758

从上述日志可以看出,节点4是先宕机的,然后节点2接着宕机,节点3在宕机,最后是节点1最后宕机。节点1最后认为节点234都已经Dead。

List of instances (total 1) :
 1
Dead instances (total 3) :
 2 3 4
My inst 1

继续分析节点1,在宕机之前,节点1后台一直在报错。

ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2

配合节点1 oswatch的内存图,可以发现问题期间内存free值非常低。

进一步分析空闲内存低的时候top情况,发现系统开启大量的并行进程。结合当时的addm报告,可以发现应用当时create table的时候开了很多并行。

内存不足导致ORA-错误,通过mos文档。
“ORA-27301: OS failure message: No buffer space available” occurs on OPC RAC (Doc ID 2397062.1)

需要设置参数vm.min_free_kbytes。
当请求分配内存的时候,如果有足够的内存,则可以成功分配,当没有足够内存的时候,操作就会阻塞。他需要等待系统先去释放内存,再分配内存。而对于系统级别的一些原子性的请求,它是不能被阻塞的,如果分配不到内存的话,就会出现失败。内核为了避免原子请求失败,必须设置一块保留的内存。而这个就是通过这个参数来设置的。
节点4的oswwatch内存图

这里可以看到节点4的内存也是free持续变低。


从top进程中可以看到,节点4的情况和节点1不一样,osysmond进程占用内存逐步增加,内存free越来越少。通过mos查询,可以发现osysmond进程在12c版本存在内存泄露的情况,Bug 18701017 – Memory Leak with osysmond.bin (Doc ID 18701017.8)

优化建议
1.建议所有节点设置参数vm.min_free_kbytes,必须设置一块保留的内存,该参数可以让内核避免原子请求失败。具体值需要主机工程师评估。
2.节点1建表语句开启并行太高,建议将单个语句并行个数控制在160以内。

3.所有集群节点关闭System Monitor Service(osysmond)。osysmond会将每个节点的资源使用情况发送给cluster logger service,后者将会把所有节点的信息都接收并保存到CHM的资料库。由于我们已经安装oswatch,所以这个可以关闭。

On each node, as root user:
# /bin/crsctl stop res ora.crf -init
# /bin/crsctl modify res ora.crf -attr ENABLED=0 -init

一次ORA-12547: TNS:lost contact问题分析

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:一次ORA-12547: TNS:LOST CONTACT问题分析

今天无意中连一套数据库,连接的时候直接报ORA-12547: TNS:lost contact

这种问题也是很奇怪的,我随手就做了一个strace进行分析了一下。

[oracle@itwgbx2 admin]$ strace -f -o /tmp/trace.1.log $ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 29 20:30:17 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12547: TNS:lost contact

通过strace生成的trace文件进一步分析,如下所示:

可以看到这里产生了一个trace文件,打开trace文件,可以发现这里报”opiino: Attach failed! error=-1 “错误。

查看MOS文档”opiino: Attach failed! error=-1 ifvp=0″ written in Database UDUMP file (Doc ID 399727.1),里面说明了原因:
1.这个错误是由数据库在客户端进程的OPI / OCI(两次任务)上异常断开引起的。如果在数据库可以完成两次任务通信功能之前(异常)终止了客户端,则会发生这种情况。
2.如果Oracle二进制文件位于使用NFS挂载,使用了noac参数/选项,则也可能引发此错误。
3.另一个可能的原因,以及可能导致连接大量增加或断开的原因是,是10g引入的INBOUND_CONNECT_TIMEOUT参数,设置的比默认值还要低。另一个错误可能在数据库警报日志中报告的异常断开,特别是与11g中经常出现ORA-609 “opiodr aborting process unknown ospid。这两种情况非常类似,都是从“opi”数据库层生成的错误最终导致客户端异常。
4.另一个潜在(但罕见)的原因是,用于客户端访问的监听版本比正在访问的数据库的版本低。例如,使用9.2.x监听器访问未经认证或支持的10.x数据库,这将导致连接异常
上述几种错误的形式确实很多需要去验证,我基本上确认了2,3,4在我的系统中不可能,难道是1这种情况。1这种情况需要收集更多的信息来确认。
受影响的客户端和数据库之间的网络是否处于稳定状态?
连接活动是否突然增加并且异常增加,可能会使数据库连接数爆满?
客户端是否异常终止(例如机器故障)?
我又仔细的check了一遍,确认不存在上述问题。就在不知道如何在查下去的时候,我手动又把刚刚的strace文件又做了一遍grep,这次grep发现了一个新的方向。
[oracle@itwgbx2 tmp]$ cat trace.1.log | grep -i TNS-
26554 write(7, “TNS-12649: Unknown encryption or”…, 58
26553 write(8, “TNS-12547: TNS:lost contact\n”, 28
26553 write(8, “TNS-00517: Lost contact\n”, 24
可以看到,这里出现了一个错误”TNS-12649: Unknown encryption or”。这肯定是监听加密有问题。我查看了一下sqlnet.ora文件,确实上面设置了一些监听加密的参数,我们这些加密的参数全部取消掉,再次登录就恢复了正常。这波真是山穷水复疑无路,柳暗花明又一村。

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)