数据泵导致表部分列

看到群里有人提出一个需求,一张表数据量很大,只想导出其中一部分列。

无论是老版本exp还是数据泵expdp,Oracle都提供了QUERY的功能,这使得查询表中部分记录的功能可以实现,但是QUERY只能过滤行,而不能过滤列,Oracle数据泵会读取表中全部列的。

在12c中,Oracle为数据泵提供了VIEW功能,使得导出的时候可以根据视图的定义来导出表中的数据:

SQL> select banner from v$version;

BANNER

——————————————————————————–

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

SQL> CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES;

表已创建。

SQL> create view v_tables as select owner, table_name, tablespace_name from t_tables where owner not in (‘SYS’);

视图已创建。

利用数据泵的VIEWS_AS_TABLES参数可以直接导出视图对应的表数据:

C:\Users\yangt>expdp c##u1 directory=d_output dumpfile=t_tab_view views_as_tables=v_tables

Export: Release 18.0.0.0.0 – Production on 星期二 2月 26 13:37:31 2019

Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

口令:

连接到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

警告: 连接到容器数据库的根或种子时通常不需要 Oracle Data Pump 操作。

启动 “C##U1”.”SYS_EXPORT_TABLE_01″:  c##u1/******** directory=d_output dumpfile=t_tab_view views_as_tables=v_tables

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

. . 导出了 “C##U1″.”V_TABLES”                          8.289 KB      71 行

已成功加载/卸载了主表 “C##U1”.”SYS_EXPORT_TABLE_01″

******************************************************************************

C##U1.SYS_EXPORT_TABLE_01 的转储文件集为:

  D:\TEMP\T_TAB_VIEW.DMP

作业 “C##U1”.”SYS_EXPORT_TABLE_01″ 已于 星期二 2月 26 13:37:40 2019 elapsed 0 00:00:06 成功完成

虽然是通过视图导出的,但是数据泵把它当作一个表来处理,因此这个dump文件是可以直接导入到数据库中变成表的:

SQL> drop view v_tables;

视图已删除。

完成导入操作:

C:\Users\yangt>impdp c##u1 directory=d_output dumpfile=t_tab_view full=y

Import: Release 18.0.0.0.0 – Production on 星期二 2月 26 14:33:02 2019

Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

口令:

连接到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

警告: 连接到容器数据库的根或种子时通常不需要 Oracle Data Pump 操作。

已成功加载/卸载了主表 “C##U1”.”SYS_IMPORT_FULL_01″

启动 “C##U1”.”SYS_IMPORT_FULL_01″:  c##u1/******** directory=d_output dumpfile=t_tab_view full=y

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

. . 导入了 “C##U1″.”V_TABLES”                          8.289 KB      71 行

作业 “C##U1”.”SYS_IMPORT_FULL_01″ 已于 星期二 2月 26 14:33:07 2019 elapsed 0 00:00:02 成功完成

查询表数据:

SQL> select count(*) from v_tables;

  COUNT(*)

———-

        71

SQL> select table_name, tablespace_name from user_tables where table_name = ‘V_TABLES’;

TABLE_NAME                     TABLESPACE_NAME

—————————— ——————————

V_TABLES                       USERS

这个功能是12c的特新特,如果是12c之前的版本,可以考虑使用ORACLE_DATAPUMP类型的外部表来实现:

SQL> create table t_external_tables (owner, table_name, tablespace_name) organization external

  2  (type oracle_datapump

  3  default directory d_output

  4  location (‘external_table.dp’))

  5  as select owner, table_name, tablespace_name from t_tables where owner not in (‘SYS’);

表已创建。

通过将外部表导出的external_table.dp文件拷贝到目标环境对应的目录下,根据上面的定义重建外部表,注意目标环境创建过程由于已经拷贝了导出的数据,因此语法截至到location语句就结束了,不要再包含as select部分。

SQL> create table t_target_ext_tab (owner varchar2(30), table_name varchar2(128), tablespace_name varchar2(30))

  2  organization external

  3  (type oracle_datapump

  4  default directory d_output

  5  location (‘external_table.dp’));

表已创建。

SQL> select count(*) from t_target_ext_tab;

  COUNT(*)

———-

        71

SQL> col table_name for a30

SQL> select * from t_target_ext_tab where owner = ‘SYSTEM’;

OWNER                          TABLE_NAME                     TABLESPACE_NAME

—————————— —————————— ——————————

SYSTEM                         OL$

SYSTEM                         OL$HINTS

SYSTEM                         OL$NODES

SYSTEM                         HELP                           SYSTEM

所以对于11g和10g的版本,也可以不通过新建表,然后拷贝数据的方式来实现数据的导出和转移。但是如果是9i及以前的版本,那么要不然通过建表然后exp导出的方式,要不然自己写程序实现数据的导出吧。

Posted in ORACLE | Tagged , , | Leave a comment

12c新增并行索引扫描

Oracle在12c之前对于索引范围扫描是没有办法并行执行的,从12.1开始,Oracle可以并行的执行索引扫描。

首先创建测试环境:

SQL> create table t_para_ind (id number, name varchar2(30), created date);

 

Table created.

 

SQL> insert into t_para_ind select id, object_name, created from t_big;

 

6735106 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> create index ind_para_created on t_para_ind (created);

 

Index created.

 

SQL> select banner from v$version;

 

BANNER

——————————————————————————–

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

    

强制执行计划采用索引扫描:

SQL> select /*+ index(t) */ count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.61

 

————————————————————————————–

| Id  | Operation                            | Name             | Rows  | Cost (%CPU)|

————————————————————————————–

|   0 | SELECT STATEMENT                     |                  |     1 | 63096   (1)|

|   1 |  SORT AGGREGATE                      |                  |     1 |            |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_PARA_IND       |  3365K| 63096   (1)|

|*  3 |    INDEX RANGE SCAN                  | IND_PARA_CREATED |  3672K|  9850   (2)|

————————————————————————————–

 

下面设置语句级并行执行,首先将优化器参数设置为11.2.0.4版本:

SQL> select /*+ index(t) parallel(2) opt_param(‘optimizer_features_enable’, ‘11.2.0.4’) */ count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.64

 

——————————————————————————

| Id  | Operation                    | Name             | Rows  | Cost (%CPU)|

——————————————————————————

|   0 | SELECT STATEMENT             |                  |     1 | 63096   (1)|

|   1 |  SORT AGGREGATE              |                  |     1 |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| T_PARA_IND       |  3365K| 63096   (1)|

|*  3 |    INDEX RANGE SCAN          | IND_PARA_CREATED |  3672K|  9850   (2)|

——————————————————————————

对于11.2.0.4的优化器版本,即使设置了并行提示,Oracle也会忽略并行,而采用串行索引范围扫描执行计划。

即使都是索引串行扫描,11.2和12c中还是有一点小差异的。在12c中,Oracle引入了批量ROWID提取的新特性,在执行计划中由关键字BATCHED标识。可以看到11.2和12c中执行效率也有很小的差异,而这个性能提升就是这个批量处理新特性带来的。

Oracle无法采用并行执行的原因是由索引的存储结构决定的,当执行索引访问时,Oracle首先定位到Btree索引的根节点,通过与根节点中存储的键值前缀进行比较,定位到枝叶节点,重复比较的过程,最终定位到叶子节点。在叶子节点上Oracle找到了第一条满足条件的键值,然后Oracle会根据叶节点上的链表扫描下一个叶节点,不断重复这个过程,直到不满足查询限制条件的记录出现。

也就是说Oracle需要根据顺序访问一条链表,只有找到第一个索引块才知道下一个要访问的索引块在哪里,因此这个过程没有办法拆分到多个进程同时执行,这就是为什么索引范围扫描一直无法并行的原因。

Oracle在12c中使得索引扫描可以并行执行,下面看看Oracle是如何实现的:

SQL> select /*+ index(t) parallel(2) */ count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.38

 

—————————————————————————————-

|Id |Operation                               |Name            |  TQ |IN-OUT|PQ Distrib |

—————————————————————————————-

|  0|SELECT STATEMENT                        |                |     |      |           |

|  1| SORT AGGREGATE                         |                |     |      |           |

|  2|  PX COORDINATOR                        |                |     |      |           |

|  3|   PX SEND QC (RANDOM)                  |:TQ10001        |Q1,01| P->S |QC (RAND)  |

|  4|    SORT AGGREGATE                      |                |Q1,01| PCWP |           |

|  5|     TABLE ACCESS BY INDEX ROWID BATCHED|T_PARA_IND      |Q1,01| PCWP |           |

|  6|      PX RECEIVE                        |                |Q1,01| PCWP |           |

|  7|       PX SEND HASH (BLOCK ADDRESS)     |:TQ10000        |Q1,00| S->P |HASH (BLOCK|

|  8|        PX SELECTOR                     |                |Q1,00| SCWC |           |

|* 9|         INDEX RANGE SCAN               |IND_PARA_CREATED|Q1,00| SCWP |           |

—————————————————————————————-

为了输出格式的可读性,把执行计划中和当前关系不大的列去掉了。

可以看到Oracle确实采用了并行的执行计划,而且执行时间也比串行执行快。

如果仔细观察IN-OUT列,就会发现Oracle的并行执行实际上从第6步才开始,第7步是串行到并行的过程,而第7步之前的第9步和第8步都是串行执行。

也就是说Oracle把索引范围扫描的过程分成了两部分,一部分是之前讨论的索引范围扫描部分,而另一部分是索引扫描后根据rowid的读取表中记录的过程。对于前者,即使是在12c中,Oracle仍然采用串行的扫描方式执行,而对于后者,Oracle将其并行化。这也是性能提升的由来。

SQL> select /*+ index(t) parallel(4) */ count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.56

 

—————————————————————————————-

|Id |Operation                               |Name            |  TQ |IN-OUT|PQ Distrib |

—————————————————————————————-

|  0|SELECT STATEMENT                        |                |     |      |           |

|  1| SORT AGGREGATE                         |                |     |      |           |

|  2|  PX COORDINATOR                        |                |     |      |           |

|  3|   PX SEND QC (RANDOM)                  |:TQ10001        |Q1,01| P->S |QC (RAND)  |

|  4|    SORT AGGREGATE                      |                |Q1,01| PCWP |           |

|  5|     TABLE ACCESS BY INDEX ROWID BATCHED|T_PARA_IND      |Q1,01| PCWP |           |

|  6|      PX RECEIVE                        |                |Q1,01| PCWP |           |

|  7|       PX SEND HASH (BLOCK ADDRESS)     |:TQ10000        |Q1,00| S->P |HASH (BLOCK|

|  8|        PX SELECTOR                     |                |Q1,00| SCWC |           |

|* 9|         INDEX RANGE SCAN               |IND_PARA_CREATED|Q1,00| SCWP |           |

—————————————————————————————-

但是这种方式的分拆并不是真正意义的并行,因为其中的一部分是无法并行的,所以当我们进一步加大并行度的时候,执行时间反而变长了。

SQL> select /*+ index(t) parallel(4) */ count(*) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.17

 

——————————————————————-

| Id  | Operation         | Name             | Rows  | Cost (%CPU)|

——————————————————————-

|   0 | SELECT STATEMENT  |                  |     1 |  9850   (2)|

|   1 |  SORT AGGREGATE   |                  |     1 |            |

|*  2 |   INDEX RANGE SCAN| IND_PARA_CREATED |  3365K|  9850   (2)|

——————————————————————-

而如果我们修改SQL语句,把原本的COUNT(NAME)改为COUNT(*),这时由于执行计划中回表部分不再需要,执行计划只剩下不能并行的索引扫描部分,因此执行计划又恢复了串行执行。这又一次证实了12c的并行索引扫描只是部分并行,其提升总体扫描效率的能力是有限的。

下面看看全表扫描的并行执行:

SQL> select count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.24

 

————————————————————–

| Id  | Operation          | Name       | Rows  | Cost (%CPU)|

————————————————————–

|   0 | SELECT STATEMENT   |            |     1 | 11519   (2)|

|   1 |  SORT AGGREGATE    |            |     1 |            |

|*  2 |   TABLE ACCESS FULL| T_PARA_IND |  3073K| 11519   (2)|

————————————————————–

串行全表扫描时,执行时间为0.24秒。

SQL> select /*+ parallel(2) */ count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.15

 

————————————————————————–

| Id  | Operation              | Name       |    TQ  |IN-OUT| PQ Distrib |

————————————————————————–

|   0 | SELECT STATEMENT       |            |        |      |            |

|   1 |  SORT AGGREGATE        |            |        |      |            |

|   2 |   PX COORDINATOR       |            |        |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000   |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |            |  Q1,00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |            |  Q1,00 | PCWC |            |

|*  6 |       TABLE ACCESS FULL| T_PARA_IND |  Q1,00 | PCWP |            |

————————————————————————–

开启2路并行时,执行时间为0.15秒。

SQL> select /*+ parallel(4) */ count(name) from t_para_ind t where created >= to_date(‘201701’, ‘yyyymm’);

 

Elapsed: 00:00:00.08

 

————————————————————————–

| Id  | Operation              | Name       |    TQ  |IN-OUT| PQ Distrib |

————————————————————————–

|   0 | SELECT STATEMENT       |            |        |      |            |

|   1 |  SORT AGGREGATE        |            |        |      |            |

|   2 |   PX COORDINATOR       |            |        |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000   |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |            |  Q1,00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |            |  Q1,00 | PCWC |            |

|*  6 |       TABLE ACCESS FULL| T_PARA_IND |  Q1,00 | PCWP |            |

————————————————————————–

开启4路并行时,执行时间降低到了0.08秒。显然全表扫描才是真正的并行,在合理的数据量和资源消耗范围内,其执行时间是随着并行度增大而等比降低的。

 

 

Posted in ORACLE | Tagged , | Leave a comment

Undo构造一致性读的例外情况

嘉年华听了恩墨学院的一个主题:《重现ORA-01555 细说Oracle 12c Undo数据管理》,吕老师介绍了UNDO的概念以及ORA-1555的产生,并介绍了12c以来Oracle的UNDO相关的新特性。

其中介绍了Oracle如何使用UNDO来实现多版本一致性读,使用了OPEN CURSOR的方式非常巧妙地在很少量数据的情况下构造出可重现的案例。不过这个案例存在一点小的瑕疵,因为如果一不小心,很可能会导致结果与预期不符,这是因为这里有一个例外存在。

我们先来模拟一下UNDO构造一致性读的情况,对于Oracle而言,默认的隔离级别是READ COMMIT,也就是说一个会话只能看到其他会话已经提交的修改,未提交的修改或者在当前会话查询发起之后提交的修改都是不可见的。

再介绍一下OPEN CURSOR,Oracle中当一个游标被打开,其结果集就已经确定了,也就是说这个游标会根据OPEN CURSOR这个时间点对应的SCN来构造一致性查询。但是OPEN CURSOR时,对应的SQL并不会被执行,在后续FETCH的时候(对于SQLPLUS而言PRINT命令会触发FETCH),SQL才真正被执行。使用这种办法可以模拟一个大的查询,OPEN CURSOR相当于大的查询的开始时间,其早于其他会话的修改提交时间,而FETCH的时间相当于大查询读取到这条记录的时间,而该时间晚于其他会话提交的时间:

 

SQL> SET SQLP ‘SQL1> ‘

SQL1> CREATE TABLE T_UNDO (ID NUMBER, NAME VARCHAR2(30));

 

Table created.

 

SQL1> INSERT INTO T_UNDO SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

 

96920 rows created.

 

SQL1> COMMIT;

 

Commit complete.

 

SQL1> CREATE INDEX IND_UNDO_ID ON T_UNDO(ID);        

 

Index created.

 

SQL1> SELECT NAME FROM T_UNDO WHERE ID = 1119;

 

NAME

————————————————————

I_EXTERNAL_LOCATION1$

 

SQL1> VAR C REFCURSOR

SQL1> EXEC OPEN :C FOR SELECT NAME FROM T_UNDO WHERE ID = 1119;

 

PL/SQL procedure successfully completed.

 

在第一个会话已经构造了一个查询,下面在会话2对这条ID为1119的记录进行修改并提交:

 

SQL> SET SQLP ‘SQL2> ‘

SQL2> UPDATE T_UNDO SET NAME = ‘UPDATED’ WHERE ID = 1119;

 

1 row updated.

 

SQL2> COMMIT;

 

Commit complete.

 

在会话3上执行查询,这时会看到会话2修改提交后的结果:

 

SQL> SET SQLP ‘SQL3> ‘

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1119;

 

NAME

————————————————————

UPDATED

 

回到会话1,对CURSOR变量执行PRINT,检查得到的结果:

 

SQL1> PRINT :C 

 

NAME

————————————————————

I_EXTERNAL_LOCATION1$

 

到目前为止,所有都是预期之内的结果,Oracle会利用UNDO来存储UPDATE的前镜像,当查询发现需要访问的数据块SCN大于会话发起的SCN,而需要通过UNDO中存储的前镜像来构造一致性读,找到会话需要读取的修改前的数据。

那么例外来自哪里呢,在这个例子中,我们给ID列上创建了一个索引,如果这不是一个普通的索引,而是一个主键,那么效果如何呢:

 

SQL1> DROP INDEX IND_UNDO_ID;

 

Index dropped.

 

SQL1> ALTER TABLE T_UNDO ADD PRIMARY KEY (ID);

 

Table altered.

 

SQL1> SELECT NAME FROM T_UNDO WHERE ID = 1118;

 

NAME

————————————————————

EXTERNAL_LOCATION$

 

SQL1> EXEC OPEN :C FOR SELECT NAME FROM T_UNDO WHERE ID = 1118;

 

PL/SQL procedure successfully completed.

 

会话2修改ID为1118的记录:

 

SQL2> UPDATE T_UNDO SET NAME = ‘UPDATED WITH PK’ WHERE ID = 1118;

 

1 row updated.

 

SQL2> COMMIT;

 

Commit complete.

 

会话3检查确认修改结果:

 

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1118;

 

NAME

—————

UPDATED WITH PK

 

再次回到会话1,PRINT游标变量:

 

SQL1> PRINT :C

 

NAME

————————————————————

UPDATED WITH PK

 

可以看到例外产生了,一致性读的结果被破坏了,居然可以查询到发生在游标打开之后提交的修改。

导致这个例外的原因来自于一个隐含函数_row_cr:

 

SQL> SELECT KSPPINM NAME, KSPPSTVL VALUE, KSPPDESC DESCRIPTION

  2  FROM SYS.X$KSPPI X, SYS.X$KSPPCV Y

  3  WHERE X.INDX = Y.INDX

  4  AND KSPPINM = ‘_row_cr’;

 

NAME            VALUE           DESCRIPTION

————— ————— ————————————————–

_row_cr         TRUE            enable row cr for all sql

 

Oracle11g以后,这个隐含参数默认值修改为TRUE,这使得Oracle对于基于主键的访问不再采用默认的一致性读方案。当然Oracle做出这种修改的目的是为了提高性能,而且仅对于单行访问生效,而大部分情况下单行访问的效率非常高,因此对于一致性破坏的影响并不明显。到18C为止,该参数仍然为TRUE。

如果关闭该参数:

 

SQL1> ALTER SYSTEM SET “_row_cr” = FALSE;

 

System altered.

 

SQL1> SELECT NAME FROM T_UNDO WHERE ID = 1117;

 

NAME

————————————————————

I_EXTERNAL_TAB1$

 

SQL1> EXEC OPEN :C FOR SELECT NAME FROM T_UNDO WHERE ID = 1117;

 

PL/SQL procedure successfully completed.

 

会话2进行修改:

 

SQL2> UPDATE T_UNDO SET NAME = ‘UPDATED NO ROW CR’ WHERE ID = 1117;

 

1 row updated.

 

SQL2> COMMIT;

 

Commit complete.

 

检查结果:

 

SQL3> SELECT NAME FROM T_UNDO WHERE ID = 1117;

 

NAME

——————

UPDATED NO ROW CR

 

回到会话1检查结果:

 

SQL1> PRINT :C

 

NAME

————————————————————

I_EXTERNAL_TAB1$

 

Oracle恢复默认的读一致性隔离级别。

虽然Oracle认为这种优化只是针对主键或唯一索引等行级访问生效,造成数据一致性破坏的可能性很小,但是建议对于一致性要求较高的行业尤其是金融相关行业还是将该特性关闭,避免因此造成的一致性问题。

 

 

Posted in ORACLE | Tagged , , | Leave a comment

全局事务锁等待分析

某客户数据库出现了严重的性能问题,导致应用出现大范围超时以及会话激增等问题,多次尝试kill session都无法彻底解决问题,重启后系统恢复正常。

拿到故障时刻的AWR报告,可以发现问题时刻,数据库的主要等待为:Global transaction acquire instance locks和enq: TX – row lock contention。

 

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
Global transaction acquire instance locks 5,342 5,343 1000 74.09 Configuration
enq: TX – row lock contention 5 1,437 287308 19.92 Application
DB CPU 331 4.59
direct path read 37,708 72 2 0.99 User I/O
log file sync 7,817 12 2 0.16 Commit

 

其中TX – row lock contention等待十分常见,这个等待事件造成应用的阻塞也很容易理解,但是Global transaction acquire instance locks并不是常见等待,从字面上理解,是全局事务在尝试获取实例锁。这个等待在等待时间占比上,消耗了将近75%的DBTIME。

当然数据库中TOP 5中最严重的等待不一定是问题的根源,

分析问题时刻的ASH信息,在问题时刻,最先出现的是全局事务获取锁的等待,随后开始出现行锁等待:

 

SQL> select to_char(sample_time, ‘hh24miss’), session_id, event, blocking_session

2  from dba_hist_active_sess_history

3  where sample_time >= to_date(‘201810180652’, ‘yyyymmddhh24mi’)

4  and sample_time <= to_date(‘201810180700’, ‘yyyymmddhh24mi’)

5  and instance_number = 1

6  order by 1, 2;

 

TO_CHA SESSION_ID EVENT                                      BLOCKING_SESSION

—— ———- —————————————— —————-

065204         69 Global transaction acquire instance locks

065214         69 Global transaction acquire instance locks

065224         69 Global transaction acquire instance locks

065224        535

065234         69 Global transaction acquire instance locks

065234        232

065234        535

065234        763 Global transaction acquire instance locks

065244         69 Global transaction acquire instance locks

065244        535 direct path read

065244        695 enq: TX – row lock contention                           763

065244        763 Global transaction acquire instance locks

065254         69 Global transaction acquire instance locks

065254        535

065254        695 enq: TX – row lock contention                           763

065254        763 Global transaction acquire instance locks

065304        136 Global transaction acquire instance locks

065304        695 enq: TX – row lock contention                           763

065304        763 Global transaction acquire instance locks

065314        136 Global transaction acquire instance locks

065314        695 enq: TX – row lock contention                           763

065314        763 Global transaction acquire instance locks

065324         69 Global transaction acquire instance locks

065324        136 Global transaction acquire instance locks

065324        695 enq: TX – row lock contention                           763

065324        763 Global transaction acquire instance locks

065334         69 Global transaction acquire instance locks

065334        136 Global transaction acquire instance locks

065334        695 enq: TX – row lock contention                           763

065344         69 Global transaction acquire instance locks

065344        136 Global transaction acquire instance locks

065344        434 enq: TX – row lock contention                           763

065344        695 enq: TX – row lock contention                           763

065354         69 Global transaction acquire instance locks

065354        136 Global transaction acquire instance locks

065354        434 enq: TX – row lock contention                           763

065354        695 enq: TX – row lock contention                           763

065404         69 Global transaction acquire instance locks

065404        136 Global transaction acquire instance locks

065404        434 enq: TX – row lock contention                           763

065404        695 enq: TX – row lock contention                           763

.

.

.

065944        302 enq: TX – row lock contention                           763

065944        336 enq: TX – row lock contention                           763

065944        434 enq: TX – row lock contention                           763

065944        695 enq: TX – row lock contention                           763

065954         71 Global transaction acquire instance locks

065954        302 enq: TX – row lock contention                           763

065954        336 enq: TX – row lock contention                           763

065954        434 enq: TX – row lock contention                           763

065954        695 enq: TX – row lock contention                           763

 

216 rows selected.

 

首先出现问题的是会话763,其处于Global transaction acquire instance locks等待中,该会话并未显示被其他会话阻塞。之后开始出现了行锁等待,这些等待enq: TX – row lock contention的会话,其阻塞对象都是会话763。

显然在本次故障中,虽然最终导致大范围业务挂起的是enq: TX – row lock contention等待,但是最终问题的根源是Global transaction acquire instance locks等待。

几乎与此同时,后台告警日志出现大量的报错:

 

Thu Oct 18 06:53:33 2018

opiodr aborting process unknown ospid (26428) as a result of ORA-24756

Thu Oct 18 06:53:36 2018

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pmon_30688.trc:

ORA-24756: transaction does not exist

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pmon_30688.trc:

ORA-24756: transaction does not exist

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pmon_30688.trc:

ORA-24756: transaction does not exist

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pmon_30688.trc:

ORA-24756: transaction does not exist

 

数据库中出现该等待的时间点06:52,再考虑到等待在报错前会经历一个超时,因此数据库中的等待与告警日志中的ORA-24756错误有密切的关系。

以ORA-24756作为关键字查询MOS,可以找到与当前现象非常接近的文章:PMON cleanup fails with ORA-24756: transaction does not exist (文档 ID 2299927.1)。

文档中描述的报错现象与当前问题一致,且数据库版本也很接近。Oracle认为这个错误是由于PMON进程在清理不存在的事务时出现了卡住的现象。Oracle给出了几个比较类似的bug,但是这些bug的状态不是不可重现就是已中止,因此类似的问题并没有明确的结论:

Bug 20676168 – PMON GOT CONTINUOUS ORA-24756 DURING SHUTDOWN <<<<<<<<<< Closed, Could Not Reproduce

Bug 16317766 – EXADATA : ORA-24756: TRANSACTION DOES NOT EXIST <<<<<<<<<<<< Suspended, Req’d Info not Avail

Bug 9757979 – PMON CLEANUP FAILS WITH ORA-24756: TRANSACTION DOES NOT EXIST <<<<<<<<<<< Closed, Could Not Reproduce

 

无论是Global transaction acquire instance locks等待还是后台alert日志中不断出现的ORA-24756错误,问题都指向Oracle的锁和全局事务处理。

而从11g以后,RAC的全局事务的处理由后台进程GTXn来自动维护。该进程是否启动受初始化参数global_txn_processes的控制,该参数默认值为1,也就是在数据库启动的时候会默认启动GTXn进程。

询问客户近期是否进行过应用和数据库的调整,可以确认的是客户的应用程序并未发生任何变化,在之前也未出现过类似的问题,不过在出现问题之前,数据库做过主库和Data Guard备库之间的切换演练,而切换演练完成后,恢复正常业务时,就出现了这次故障。显然这次切换演练的操作是被怀疑的重点。

在详细检查了alert告警日志后发现,在DATA GUARD演练后切换回主库时,GTXn进程未启动:

 

Thu Oct 18 02:36:18 2018

alter database commit to switchover to physical standby with session shutdown

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 4366] (orcl1)

.

.

.

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete – Database shutdown required

Completed: alter database commit to switchover to physical standby with session shutdown

Thu Oct 18 02:45:27 2018

.

.

.

RFS[8]: Assigned to RFS process 3216

RFS[8]: Identified database type as ‘physical standby’: Client is ARCH pid 11990

Thu Oct 18 03:11:13 2018

alter database open

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

This instance was first to open

Beginning standby crash recovery.

Serial Media Recovery started

.

.

.

Thu Oct 18 03:11:13 2018

SMON: enabling cache recovery

Dictionary check beginning

Dictionary check complete

Database Characterset is ZHS16GBK

No Resource Manager plan active

Starting background process GTX0

Thu Oct 18 03:11:14 2018

GTX0 started with pid=51, OS id=5041

replication_dependency_tracking turned off (no async multimaster replication found)

Physical standby database opened for read only access.

Completed: alter database open

.

.

.

Thu Oct 18 04:57:19 2018

alter database commit to switchover to primary with session shutdown

ALTER DATABASE SWITCHOVER TO PRIMARY (orcl1)

Maximum wait for role transition is 15 minutes.

Switchover: Media recovery is still active

Role Change: Canceling MRP – no more redo to apply

.

.

.

Switchover: Complete – Database mounted as primary

Completed: alter database commit to switchover to primary with session shutdown

Thu Oct 18 04:57:39 2018

alter database open

This instance was first to open

Picked broadcast on commit scheme to generate SCNs

.

.

.

Thu Oct 18 04:57:44 2018

QMNC started with pid=41, OS id=22585

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open

.

.

.

Thu Oct 18 06:53:33 2018

opiodr aborting process unknown ospid (26428) as a result of ORA-24756

Thu Oct 18 06:53:36 2018

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pmon_30688.trc:

ORA-24756: transaction does not exist

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pmon_30688.trc:

.

.

.

Starting background process GTX0

Thu Oct 18 09:51:20 2018

GTX0 started with pid=36, OS id=32470

Starting background process RCBG

Thu Oct 18 09:51:20 2018

 

从上面节选的部分alert日志可以看到,在2点36分,主库执行了SWITCHOVER操作,开始切换演练。

在3点11分,数据库打开,alert中提示,当前数据库为物理备库,为只读打开模式,在随后的日志中加粗的部分,显示此时后台进程GTX0进程启动。

在4点57分,数据库再次执行SWITCHOVER切换,此时数据库并未完全SHUTDOWN,而是直接转换为MOUNT模式,并于4点57分44秒完成了数据库的OPEN过程。但是这个时间段的alert中并未发现GXTn进程的启动。

而在alert中发现GTX0进程再次出现,是故障之后实例1重启时刻的9点51分。

显然,问题已经非常明确,在数据库由物理备库切换为主库的过程中,GTXn进程没有启动,这导致了Oracle无法处理分布式事务的锁问题,因此前台会话出现Global transaction acquire instance locks等待,而后台PMON也无法正常的清理会话和事务。

由于实例2在SWITCHOVER切换过程中是被SHUTDOWN后重新启动的,因此实例2上的GTXn进程是正常启动的,这也是应用切换到实例2上不在出现故障的原因。

如果要避免类似的问题产生,一方面在进行DATA GUARD的切换演练后,在主库切换到PRIMARY模式后,再执行一次数据库重启的操作,确保所有必要的进程都会启动。另一方面,对于应用程序,如果确实需要修改其他数据库中的表,应该通过建立其他数据库连接的方式来实现,避免在数据库中通过数据库链的方式直接更新远端表。

 

Posted in ORACLE | Tagged , , | Leave a comment

利用分析函数改写范围判断自关联查询

最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 363,741 363,740.78 8 .42
CPU Time (ms) 362,770 362,770.00 8 .81
Executions 1    
Buffer Gets 756 756.00 0.00
Disk Reads 0 0.00 0.00
Parse Calls 1 1.00 0.01
Rows 50,825 50,825.00  
User I/O Wait Time (ms) 0  
Cluster Wait Time (ms) 0    
Application Wait Time (ms) 0    
Concurrency Wait Time (ms) 0    
Invalidations 0    
Version Count 1    
Sharable Mem(KB) 28    

 

从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:

 

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
0 SELECT STATEMENT       1226 (100)  
1    SORT ORDER BY   49379 3375K 3888K 1226 (2) 00:00:05
2      HASH JOIN ANTI   49379 3375K 2272K 401 (3) 00:00:02
3        TABLE ACCESS FULL T_NUM 49379 1687K   88 (4) 00:00:01
4        TABLE ACCESS FULL T_NUM 49379 1687K   88 (4) 00:00:01

 

从执行计划看,Oracle选择了HASH JOIN ANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。

将原SQL进行简单脱密改写后, SQL文本类似如下:

 

SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

FROM T_NUM A

WHERE NOT EXISTS (

SELECT 1

FROM T_NUM B

WHERE B.BEGIN <= A.BEGIN

AND B.END >= A.END

AND B.ROWID != A.ROWID

AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

 

如果分析SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。

简单一点说,表中的记录表示的是由BEGIN开始到END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段BEGIN的长度的比较。

显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:

 

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;

 

LENGTH(BEGIN)   COUNT(*)

————- ———-

12      22096

11       9011

13       8999

14       8186

16         49

9         45

8         41

7         27

 

大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,设置关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。

再来看一下具体的SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。

那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。

SQL改写结果如下:

 

SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

FROM (

SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

FROM

(

SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

FROM T_NUM

)

)

WHERE RN = 1

AND CN = 1;

 

简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。

改写后,这个SQL避免对自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

2  FROM T_NUM A

3  WHERE NOT EXISTS (

4     SELECT 1

5     FROM T_NUM B

6     WHERE B.BEGIN <= A.BEGIN

7     AND B.END >= A.END

8     AND B.ROWID != A.ROWID

9     AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))

10  ;

 

48344 rows selected.

 

Elapsed: 00:00:57.68

 

Execution Plan

———————————————————-

Plan hash value: 2540751655

 

————————————————————————————

| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

————————————————————————————

|   0 | SELECT STATEMENT   |       | 48454 |  1703K|       |   275   (1)| 00:00:04 |

|*  1 |  HASH JOIN ANTI    |       | 48454 |  1703K|  1424K|   275   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL| T_NUM | 48454 |   851K|       |    68   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T_NUM | 48454 |   851K|       |    68   (0)| 00:00:01 |

————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))

filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND

“B”.ROWID<>”A”.ROWID)

 

 

Statistics

———————————————————-

0  recursive calls

0  db block gets

404  consistent gets

0  physical reads

0  redo size

2315794  bytes sent via SQL*Net to client

35966  bytes received via SQL*Net from client

3224  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

48344  rows processed

 

SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

2  FROM (

3     SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

4             ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

5     FROM

6     (

7             SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

8             FROM T_NUM

9     )

10  )

11  WHERE RN = 1

12  AND CN = 1;

 

48344 rows selected.

 

Elapsed: 00:00:00.72

 

Execution Plan

———————————————————-

Plan hash value: 1546715670

 

——————————————————————————————

| Id  | Operation                | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT         |       | 48454 |  2460K|       |   800   (1)| 00:00:10 |

|*  1 |  VIEW                    |       | 48454 |  2460K|       |   800   (1)| 00:00:10 |

|*  2 |   WINDOW SORT PUSHED RANK|       | 48454 |  1845K|  2480K|   800   (1)| 00:00:10 |

|   3 |    WINDOW BUFFER         |       | 48454 |  1845K|       |   800   (1)| 00:00:10 |

|   4 |     VIEW                 |       | 48454 |  1845K|       |   311   (1)| 00:00:04 |

|   5 |      WINDOW SORT         |       | 48454 |   662K|  1152K|   311   (1)| 00:00:04 |

|   6 |       TABLE ACCESS FULL  | T_NUM | 48454 |   662K|       |    68   (0)| 00:00:01 |

——————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – filter(“RN”=1 AND “CN”=1)

2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”

ORDER BY “BEGIN”)<=1)

 

 

Statistics

———————————————————-

0  recursive calls

0  db block gets

202  consistent gets

0  physical reads

0  redo size

1493879  bytes sent via SQL*Net to client

35966  bytes received via SQL*Net from client

3224  SQL*Net roundtrips to/from client

3  sorts (memory)

0  sorts (disk)

48344  rows processed

 

原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。

 

Posted in ORACLE | Tagged , | Leave a comment

UPDATE语句是否带有WHERE语句对更新重启动的影响

最近看到一个比较有意思的例子,是和更新重启动有关。

更新重启动是Oracle中一个隐藏的知识点,当Oracle在读取数据时,会构造读一致性,当读取的内容发生了修改,Oracle会通过UNDO信息来还原数据的前镜像,把数据还原到查询发生的时刻,通过构造一致性的结果来实现读取数据的一致性和隔离性。

Oracle实现的读一致性也被称为多版本读一致性,每个会话都会构造自己的一致性查询版本。但是对于写操作而言,这种方式是不可能的,因为最终的数据只有一份,如要要修改数据,就只能修改唯一的这份数据,所以对读操作,访问的是一致性版本,而对于写操作,修改的永远是当前版本。

既然写操作只能修改当前版本,当写操作执行的过程中,发现要修改的记录发生了变化,破坏了更新发起时刻的一致性,这时就触发了更新重启动,也就是说更新操作会放弃之前的修改,然后重新发起一次新的更新操作。

ITPUB上有一个有意思的案例,展示的就是更新重启动现象,链接如下:http://www.itpub.net/forum.php?mod=viewthread&tid=2102897

这里来重现一下更新重启动,建立一张简单测试用表,在会话一插入初始数据并进行更新操作:

SQL> SET SQLP ‘SQL1> ‘
SQL1> CREATE TABLE T_UPDATE (ID NUMBER);

Table created.

SQL1> INSERT INTO T_UPDATE SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 4;

3 rows created.

SQL1> COMMIT;

Commit complete.

SQL1> SELECT * FROM T_UPDATE;

ID
———-
1
2
3

SQL1> UPDATE T_UPDATE SET ID = 4 WHERE ID > 2;

1 row updated.

在会话2发起UPDATE语句,更新ID大于0的记录,这时UPDATE操作由于会话1更新了ID为3的记录且未提交,会处于挂起状态:

SQL> SET SQLP ‘SQL2> ‘
SQL2> UPDATE T_UPDATE SET ID = ID + 0.1 WHERE ID > 0;

然后在会话3插入一条新的记录,并提交:

SQL> SET SQLP ‘SQL3> ‘
SQL3> INSERT INTO T_UPDATE VALUES (0.1);

1 row created.

SQL3> COMMIT;

Commit complete.

这时回到会话1,进行提交:

SQL1> COMMIT;

Commit complete.

检查会话2,发现更新重启动发生,UPDATE操作更新了4条记录:

4 rows updated.

SQL2> SELECT * FROM T_UPDATE;

ID
———-
1.1
2.1
4.1
.2

会话2中不仅更新了之前存在的三条记录,连更新语句发起之后插入并提交的第四条记录也一起进行了更新,这说明更新重启动发生在第四条记录插入之后。

这个案例很好的展示了更新重启动现象,不过这个帖子并没有完,除了这个更新重启动的案例外,还展示了一个另外一个案例,操作和这个案例仅有很小的差别,但是结果却大不相同。

首先把数据恢复到初始状态,在会话2回滚之前的更新:

SQL2> ROLLBACK;

Rollback complete.

在会话1,恢复初始的数据情况,然后开始更新数据:

SQL1> DELETE T_UPDATE WHERE ID < 1;

1 row deleted.

SQL1> UPDATE T_UPDATE SET ID = 3 WHERE ID = 4;

1 row updated.

SQL1> COMMIT;

Commit complete.

SQL1> SELECT * FROM T_UPDATE;

ID
———-
1
2
3

SQL1> UPDATE T_UPDATE SET ID = 4 WHERE ID > 2;

1 row updated.

在会话2,对表中所有的记录的ID执行UPDATE操作,和上一个案例的唯一差别是,这里没有使用WHERE语句:

SQL2> UPDATE T_UPDATE SET ID = ID + 0.1;

同样在会话3插入数据并提交:

SQL3> INSERT INTO T_UPDATE VALUES (0.1);

1 row created.

SQL3> COMMIT;

Commit complete.

回到会话1,进行提交:

SQL1> COMMIT;

 

Commit complete.

发现会话2仅更新了3条记录:

 

3 rows updated.

 

SQL2> SELECT * FROM T_UPDATE;

ID
———-
1.1
2.1
4.1
.1

可以清晰的看到,最后一条插入的记录并未被更新,说明更新重启动并未被触发。

为什么带有WHERE条件的更新操作触发了更新重启动,而不带WHERE条件的更新未触发重启动呢,我是这样理解的:当UPDATE操作包含了WHERE条件,那么这个查询的结果要满足WHERE定义的查询一致性,当更新发现一致性不满足的情况下,就会触发更新重启动。而对于不包含WHERE条件或包含WHERE条件但是该条件与表查询无关的情况,这时Oracle的目标只是将表中所有的数据进行一次更新,并不需要考虑一致性的问题。因此,这个不加WHERE条件的UPDATE,感觉其实现方式上类似这种通过ORA_ROWSCN限定来实现:

SQL2> ROLLBACK;

 

Rollback complete.

回到会话1恢复数据,并检查ORA_ROWSCN的值:

SQL1> DELETE T_UPDATE WHERE ID < 1;

1 row deleted.

SQL1> UPDATE T_UPDATE SET ID = 3 WHERE ID = 4;

1 row updated.

SQL1> COMMIT;

Commit complete.

SQL1> SELECT ID, ORA_ROWSCN FROM T_UPDATE;
ID ORA_ROWSCN
———- ———-
1  231512964
2  231512964
3  231512964

SQL1> UPDATE T_UPDATE SET ID = 4 WHERE ID > 2;

1 row updated.

在会话2,发起包含制定ORA_ROWSCN限制条件的更新:

SQL2> UPDATE T_UPDATE SET ID = ID + 0.1 WHERE ORA_ROWSCN = 231512964;

会话3插入并提交:

SQL3> INSERT INTO T_UPDATE VALUES (0.1);

1 row created.

SQL3> COMMIT;

Commit complete.

回到会话1,进行提交:

SQL1> COMMIT;

Commit complete.

检查会话2:

3 rows updated.

SQL2> SELECT * FROM T_UPDATE;

ID
———-
1.1
2.1
4.1
.1

采用ORA_ROWSCN的效果与直接UPDATE不带任何WHERE条件是一样的,说明更新只关注UPDATE语句发出时刻的数据,不再考虑整体更新结果的一致性问题。

关于更新重启动的更详细的描述,建议参考ASKTOM上的回复:https://asktom.oracle.com/pls/apex/asktom.search?tag=write-consistency

 

 

Posted in ORACLE | Tagged , , | Leave a comment

SQL解决刑侦推理问题

前些天看到一个朋友在朋友圈里面贴了个图,上面是2018年刑侦科推理试题,看了一下题目,这些题目都是彼此依赖,很难找到一个题目作为入手点可以进一步分析,因此可能需要用纸笔配合大量的假设和试错才能继续完成。

原题如下:


    不过这种问题其实是SQL擅长的领域,于是一时手痒,写了一个SQL语句:

SQL> WITH T AS

  2  (SELECT ASCII(‘A’) R FROM DUAL

  3  UNION ALL

  4  SELECT ASCII(‘B’) FROM DUAL

  5  UNION ALL

  6  SELECT ASCII(‘C’) FROM DUAL

  7  UNION ALL

  8  SELECT ASCII(‘D’) FROM DUAL),

  9  RESULT AS

 10  (SELECT T1.R R1, T2.R R2, T3.R R3, T4.R R4, T5.R R5, T6.R R6, T7.R R7, T8.R R8, T9.R R9, T10.R R10,

 11     DECODE(T1.R, ASCII(‘A’), 1, 0) + DECODE(T2.R, ASCII(‘A’), 1, 0) + DECODE(T3.R, ASCII(‘A’), 1, 0) + DECODE(T4.R, ASCII(‘A’), 1, 0)

 12             + DECODE(T5.R, ASCII(‘A’), 1, 0) + DECODE(T6.R, ASCII(‘A’), 1, 0) + DECODE(T7.R, ASCII(‘A’), 1, 0)

 13             + DECODE(T8.R, ASCII(‘A’), 1, 0) + DECODE(T9.R, ASCII(‘A’), 1, 0) + DECODE(T10.R, ASCII(‘A’), 1, 0) A,

 14     DECODE(T1.R, ASCII(‘B’), 1, 0) + DECODE(T2.R, ASCII(‘B’), 1, 0) + DECODE(T3.R, ASCII(‘B’), 1, 0) + DECODE(T4.R, ASCII(‘B’), 1, 0)

 15             + DECODE(T5.R, ASCII(‘B’), 1, 0) + DECODE(T6.R, ASCII(‘B’), 1, 0) + DECODE(T7.R, ASCII(‘B’), 1, 0)

 16             + DECODE(T8.R, ASCII(‘B’), 1, 0) + DECODE(T9.R, ASCII(‘B’), 1, 0) + DECODE(T10.R, ASCII(‘B’), 1, 0) B,

 17     DECODE(T1.R, ‘C’, 1, 0) + DECODE(T2.R, ASCII(‘C’), 1, 0) + DECODE(T3.R, ASCII(‘C’), 1, 0) + DECODE(T4.R, ASCII(‘C’), 1, 0)

 18             + DECODE(T5.R, ASCII(‘C’), 1, 0) + DECODE(T6.R, ASCII(‘C’), 1, 0) + DECODE(T7.R, ASCII(‘C’), 1, 0)

 19             + DECODE(T8.R, ASCII(‘C’), 1, 0) + DECODE(T9.R, ASCII(‘C’), 1, 0) + DECODE(T10.R, ASCII(‘C’), 1, 0) C,

 20     DECODE(T1.R, ASCII(‘D’), 1, 0) + DECODE(T2.R, ASCII(‘D’), 1, 0) + DECODE(T3.R, ASCII(‘D’), 1, 0) + DECODE(T4.R, ASCII(‘D’), 1, 0)

 21             + DECODE(T5.R, ASCII(‘D’), 1, 0) + DECODE(T6.R, ASCII(‘D’), 1, 0) + DECODE(T7.R, ASCII(‘D’), 1, 0)

 22             + DECODE(T8.R, ASCII(‘D’), 1, 0) + DECODE(T9.R, ASCII(‘D’), 1, 0) + DECODE(T10.R, ASCII(‘D’), 1, 0) D

 23  FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9, T T10

 24  WHERE DECODE(CHR(T2.R), ‘A’, ‘C’, ‘B’, ‘D’, ‘C’, ‘A’, ‘B’) = CHR(T5.R)

 25  AND (CASE CHR(T3.R) WHEN ‘A’ THEN LEAST(T2.R, T4.R, T6.R) – GREATEST(T2.R, T4.R, T6.R)

 26     WHEN ‘B’ THEN LEAST(T2.R, T3.R, T4.R) – GREATEST(T2.R, T3.R, T4.R)

 27     WHEN ‘C’ THEN LEAST(T3.R, T4.R, T6.R) – GREATEST(T3.R, T4.R, T6.R)

 28     ELSE LEAST(T2.R, T3.R, T6.R) – GREATEST(T2.R, T3.R, T6.R) END) = 0

 29  AND (CASE CHR(T3.R) WHEN ‘A’ THEN T2.R – T3.R

 30     WHEN ‘B’ THEN T2.R – T6.R

 31     WHEN ‘C’ THEN T3.R – T2.R

 32     ELSE T2.R – T4.R END) != 0

 33  AND (CASE CHR(T4.R) WHEN ‘A’ THEN T1.R – T5.R

 34     WHEN ‘B’ THEN T2.R – T7.R

 35     WHEN ‘C’ THEN T1.R – T9.R

 36     ELSE T6.R – T10.R END) = 0

 37  AND (CASE CHR(T5.R) WHEN ‘A’ THEN T8.R – ASCII(‘A’)

 38     WHEN ‘B’ THEN T4.R – ASCII(‘B’)

 39     WHEN ‘C’ THEN T9.R – ASCII(‘C’)

 40     ELSE T10.R – ASCII(‘D’) END) = 0

 41  AND (CASE CHR(T6.R) WHEN ‘A’ THEN LEAST(T2.R, T4.R, T8.R) – GREATEST(T2.R, T4.R, T8.R)

 42     WHEN ‘B’ THEN LEAST(T1.R, T6.R, T8.R) – GREATEST(T1.R, T6.R, T8.R)

 43     WHEN ‘C’ THEN LEAST(T3.R, T10.R, T8.R) – GREATEST(T3.R, T10.R, T8.R)

 44     ELSE LEAST(T5.R, T9.R, T8.R) – GREATEST(T5.R, T9.R, T8.R) END) = 0

 45  AND (CASE CHR(T8.R) WHEN ‘A’ THEN ABS(T7.R – T1.R)

 46     WHEN ‘B’ THEN ABS(T5.R – T1.R)

 47     WHEN ‘C’ THEN ABS(T2.R – T1.R)

 48     ELSE ABS(T10.R – T1.R) END) != 1

 49  AND CASE CHR(T9.R) WHEN ‘A’ THEN T1.R – T6.R + T5.R – T6.R ELSE 1 END != 0

 50  AND CASE CHR(T9.R) WHEN ‘A’ THEN (T1.R – T6.R) * (T5.R – T6.R) ELSE 0 END = 0

 51  AND CASE CHR(T9.R) WHEN ‘B’ THEN T1.R – T6.R + T5.R – T10.R ELSE 1 END != 0

 52  AND CASE CHR(T9.R) WHEN ‘B’ THEN (T1.R – T6.R) * (T5.R – T10.R) ELSE 0 END = 0

 53  AND CASE CHR(T9.R) WHEN ‘C’ THEN T1.R – T6.R + T5.R – T2.R ELSE 1 END != 0

 54  AND CASE CHR(T9.R) WHEN ‘C’ THEN (T1.R – T6.R) * (T5.R – T2.R) ELSE 0 END = 0

 55  AND CASE CHR(T9.R) WHEN ‘D’ THEN T1.R – T6.R + T5.R – T9.R ELSE 1 END != 0

 56  AND CASE CHR(T9.R) WHEN ‘D’ THEN (T1.R – T6.R) * (T5.R – T9.R) ELSE 0 END = 0)

 57  SELECT CHR(R1) R1, CHR(R2) R2, CHR(R3) R3, CHR(R4) R4, CHR(R5) R5,

 58     CHR(R6) R6, CHR(R7) R7, CHR(R8) R8, CHR(R9) R9, CHR(R10) R10

 59  FROM RESULT

 60  WHERE (CASE CHR(R7) WHEN ‘A’ THEN C

 61     WHEN ‘B’ THEN B

 62     WHEN ‘C’ THEN A

 63     ELSE D END) = LEAST(A, B, C, D)

 64  AND (CASE CHR(R10) WHEN ‘A’ THEN 3

 65     WHEN ‘B’ THEN 2

 66     WHEN ‘C’ THEN 4

 67     ELSE 1 END) = (GREATEST(A, B, C, D) – LEAST(A, B, C, D));

 

R1      R2      R3      R4      R5      R6      R7      R8      R9      R10

——- ——- ——- ——- ——- ——- ——- ——- ——- ——-

B       C       A       C       A       C       D       A       B       A

 

主要是通过WITH构造ABCD四个选项,然后把十道题的所有选项可能性穷举生成,然后按照题意通过WHERE语句来进行条件限制,除了第三题和第九题都是一道题对应一个WHERE 语句,另外第七题和第十题无法在第一层查询中获取,需要在第二层中过滤。

虽然用了一点技巧,但是总的来说没啥技术含量,这里就不逐一解释了。

Posted in ORACLE | Tagged , , | Leave a comment

20180517 Oracle Code Singapore

接到Oracle的邀请参加了2018517日在新加坡举办的Oracle Code会议。我的演讲主题是How to write an efficient SQL

开发相关的技术变化非常之快,甚至让人有应接不暇的感觉,不过开发技术中也有少量技术一直历久弥新,SQL就是之一,自诞生以来40多年一直发挥着重要的作用。

调查显示,SQL是目前第二大编程语言,有50%的开发者都在使用SQL。虽然使用非常广泛,但是SQL的质量水平却并不令人满意。

          根据经验80%的数据库问题是由于SQL引起的,而80%SQL问题来自于20%SQL语句,在一些高并发高负载的系统中,由于一条SQL的性能问题导致数据库整体出现异常的情况屡见不鲜,这也是我本次选择这个主题的原因,希望帮助更多的开发人员可以书写出高效的SQL语句。         

 

         本次主题包括四方面的内容:合理的使用新特性;数据集整体处理;设计SQL执行计划;严格过滤数据。

          

         合理的使用新特性,可以避免重复访问数据,合并简化执行操作过程,缓存中执行间结果,减少自关联,高效灵活的处理一些复杂问题。因此,充分理解新特性的功能以及其适用场景,是书写高效SQL语句的基础。

         

         SQL本身是描述性语言,大部分情况下使用SQL的时候并不需要特别关注每行数据如何去处理,将数据整理处理作为思路,会发现SQL的性能会更好,而且很多时候SQL的写法也会更加简洁。

  

         好的性能是设计出来的,因此如果想写出高效的SQL语句,要从一开始就考虑好这个SQL的执行计划,驱动表是谁,采用何种JOIN方式连接到被驱动表。设计思路是一方面,另一方面是保证执行计划符合设计思路,这时候就需要用到提示的功能。熟悉提示的功能,可以更好的控制SQL的执行路径,绕过bug或性能问题,强制SQL按照设计思路去执行。

  

         过滤不必要的数据对于提升SQL的性能非常重要,对于一个计算10000以内质数的SQL,未经优化前需要112秒的执行时间,消耗17万逻辑读。

          

         通过严格的数据过滤,在运行的第一步消除不必要的数据,最终优化后的SQL只需要0.05秒,消耗461的逻辑读,执行时间和逻辑读都有几百倍的提升。

  

         想要具有书写高效SQL的能力,除了之前介绍的四点之外,还要下面三个因素:多些多练:熟能生巧;深思熟虑:算法为王;坚持不懈:优化无止境。

         最后附上本次演讲的PPT:How to write an efficient SQL

Posted in NEWS | Tagged , | Leave a comment

非空字段空值的产生

上一篇讨论了非空字段中如果存在空值对于查询的影响,这里描述一下导致问题的原因。
非空字段空值对查询的影响:http://yangtingkun.net/?p=1481
书接上文,其实CBO的判断本身是没有问题的,问题在于,为什么一个空值会存在非空约束的字段中。
重新看一下问题:
SQL> select * from t_def;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select * from t_def where type is null;
no rows selected
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select dump(type) from t_def;
DUMP(TYPE)
--------------------------------------------
NULL
SQL> select nvl(type, 'is null') from t_def;
NVL(TYPE
--------
is null
SQL> select dbms_metadata.get_ddl('TABLE', 'T_DEF') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_DEF')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."T_DEF"
( "ID" NUMBER,
"NAME" VARCHAR2(8) DEFAULT 'a',
"TYPE" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

之前提到,由于TYPE列具有非空约束,导致CBO给出的执行计划返回了错误的结果,但是问题的根源在于,为Oracle会允许空值插入到非空约束字段中:
SQL> insert into t_def (id, name) values (1, 'a');
insert into t_def (id, name) values (1, 'a')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."T_DEF"."TYPE")

那么是什么情况导致了错误的数据绕过了Oracle的检查呢。检查表的定义,发现一个特别之处,TYPE列的默认值本身就是NULL,是不是这个导致了Oracle的数据问题呢:
SQL> CREATE TABLE T_TEST (ID NUMBER, NAME VARCHAR2(30) DEFAULT '' NOT NULL);
表已创建。
SQL> INSERT INTO T_TEST (ID) VALUES (1);
INSERT INTO T_TEST (ID) VALUES (1)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("TEST"."T_TEST"."NAME")

显然问题没有那么简单,虽然默认值人为设置为NULL并不常见,但是对于哪些具有NOT NULL约束且没有指定默认值的列,都相当于默认值为NULL。显然不太可能是常规问题导致的bug,Oracle经过这么多年这么多版本的磨练,应该不会在11g还出现这种问题,而且这个问题还是第一次碰到。综上所述,推断问题可能是11g新特性所引入的bug。
分析到这里,问题的答案也呼之欲出了,没错,导致问题的就是11g新增的快速添加非空默认值的功能,这个诡异的问题可以通过下面的三步简单的重新:
SQL> create table t_def (id number, name varchar2(30) default '' not null);
Table created.
SQL> insert into t_def values (1, 'a');
1 row created.
SQL> alter table t_def add type varchar2(8) default '' not null;
Table altered.
SQL> select * from t_def;
ID NAME TYPE
---------- ------------------------------ --------
1 a

Oracle确实允许NOT NULL列的默认值为NULL,如果不指定默认值那么就相当于默认值为NULL,但是对于11g新增的新特性而言,DEFAULT为NULL是要禁止的,否则就会导致现有记录的NOT NULL字段出现NULL值。
而且由于指定的DEFAULT是NULL,ECOL$中居然没有记录任何信息:
SQL> select * from sys.ecol$;
no rows selected

看来任何新特性都难以避免BUG的产生,没想到一个增加非空默认值的新特性也会引发BUG。

Posted in BUG, ORACLE | Tagged , , | Leave a comment

非空字段空值对查询的影响

BLOG有三年没有更新了,其实这篇文档也是三年前写的,不过当时没有贴出来,今天看到有人问起,于是分享出来。

这是客户数据库中碰到的一个有意思的问题。
数据库版本为11.2.0.3,客户的DSG同步软件在同步某张表时出现了错误,报错无非插入空值ORA-1400,本来这是一个很简单的问题,但是诊断发现问题比较有意思:
SQL> select * from t_def;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select * from t_def where type is null;
no rows selected
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- ------------------------------ --------
1 a
SQL> select dump(type) from t_def;
DUMP(TYPE)
--------------------------------------------
NULL
SQL> select nvl(type, 'is null') from t_def;
NVL(TYPE
--------
is null

第一个查询不能反映任何问题,而第二个查询和第三个查询明确的告诉我们,TYPE列的值不是NULL。但是第四个查询和第五个查询又确定告之我们,TYPE列是NULL。到底TYPE列的值是NULL还是NOT NULL呢,这是一个问题。
Oracle给了我们两个不同的答案,但是可以坚信的是,一条记录的字段不可能同时具有这两种属性,要不然是NULL值,要不然就是NOT NULL,它不可能既是NULL又是NOT NULL,这不科学。什么?薛定谔的猫?谁说的?我的刀呢?!
电子的波粒二象性问题本质是波动和粒子性不想人们一开始认为的那样是完全对立的,事实上在电子的尺度上,本身二者就是共存的,所以你用检查波动的方法去观察电子,电子就以波的形式出现,而以粒子方式去观察电子,电子就以粒子的方式呈现。因此,波粒二象性本质并不是对立的。
薛定谔的猫的本质也是相同的,当然还要涉及到意识、观测、坍塌等一系列复杂的问题。建议不了解量子力学的同学们直接跳过以上两段内容。而如果你对量子力学有所研究,你就知道我完全是做为一个门外汉在胡扯,请无视上面两段内容。
好了,扯淡完毕,回到Oracle的问题上,我个人不相信量子尺度上的不确定性会影响到Oracle的查询结果,因此我们需要找到问题出在哪里。
简单分析一下,通过WHERE条件判断得到的字段空值与否与通过函数得到的结果是相反的。在宏观的维度上看,那么总有一个结果是错误的。既然Oracle本身已经不可信了,那么我们来看看数据本身到底是如何的。
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t_def;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 173109
SQL> alter system dump datafile 4 block 173109;
System altered.

下面看一下数据块中的内容:
Block header dump: 0x0102a435
Object id on Block? Y
seg/obj: 0x145df csc: 0x00.23b6097 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x102a430 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.01b.00001afc 0x00c026de.11be.10 --U- 1 fsc 0x0000.023b6098
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0102a435
data_block_dump,data header at 0x7fe7fdc97264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7fe7fdc97264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f90
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f90
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 173109 maxblk 173109

显然,数据块中只有两个列的值,也就是真实的情况是TYPE列是NULL。在WHERE条件和函数的PK中,函数取得了最终的胜利。
但是对我们而言,问题才刚刚开始,为什么明明是NULL值,但是通过IS NULL和IS NOT NULL的查询条件进行过滤,得到的是与真实条件相反的结果。
Oracle的执行语句不会这么笨,连最基本的查询条件都判断错吧。事实上,导致查询结果相左的一个原因恰恰是Oracle的CBO太智能了,我们看一下表结构和执行计划就真相大白了:
SQL> select dbms_metadata.get_ddl('TABLE', 'T_DEF') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_DEF')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."T_DEF"
( "ID" NUMBER,
"NAME" VARCHAR2(8) DEFAULT 'a',
"TYPE" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL> set autot on exp
SQL> select * from t_def where type is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 177263571
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_DEF | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):,F
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- -------- --------
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 1057129282
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_DEF | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

现在我们可以放心大胆的宣布,这个问题和猫没有关系。从表的定义可以看到,TYPE列的属性是NOT NULL的,而目前记录中TYPE的值是NULL,这就是问题的关键。
从10g开始,CBO变得更聪明,但一个查询条件明显的违反表中的约束条件时,CBO会在执行计划的最上层增加一个FILTER,而FILTER的条件是恒为假的条件NULL IS NOT NULL,导致的结果是Oracle根本不需要真正执行这个语句,就直接返回0条记录,因为表中定义的限制条件是TYPE非空,Oracle并不会去执行这个查询语句,而是直接返回了0条记录。
而对于TYPE IS NOT NULL的查询而言,由于查询条件满足约束的条件,因此Oracle在全表扫描后省略掉了原本应该做的TYPE IS NOT NULL的过滤,而直接将结果返回给用户,造成了TYPE IS NOT NULL条件返回的结果是NULL的情况。
简单的说,导致这个问题的原因是由于错误的数据存储于表中,而这导致了CBO在判断时出现了错误,导致和预期相反的结果返回。

Posted in BUG, ORACLE | Tagged , , , | 2 Comments