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

20130725 上海OOW第四日

今天终于有空听了一下技术主题。
展台的奖品已经基本上发完了,而且今天是最后一天,人相对少一些,因此上午抽出一些时间去听了一个技术的专题。
这个主题主要介绍了12c性能方面的特性。开篇Oracle先介绍了在各个场景下TPCC的记录都是Oracle保持的,并展示了那张引来无数关注的TPMC测试结果:SUN平台性能超过HP平台的7倍。
主题中的另一个亮点是介绍了Oracle自己用来承载邮件、日历、联系人、聊天、文档以及网络会议的Oracle集群,由9台X2-2组成,一共2376核,2PB数据量,7T内层和48T闪存。这个应该是实际上最大的Oracle集群部署在奥斯汀,通过DG技术将备用环境部署在犹他数据中心。这个环境中充分利用了Oracle各种软件功能:RAC、Streams、Active Data Guard、Secure Backup、RMAN、Flashback Database、ASM、Partition等。数据库利用SecureFiles的压缩功能节省了2倍的空间。
最后终于介绍了12c中一些与性能有关的特性:内存中并行执行、自适应执行计划、通过WITH将PL/SQL内嵌到SQL中、内存中的全局临时表、内存中的LOB查询和更新、并行加载和移动LOB、基于内存队列的快速审计、自动数据优化、高级压缩等。

Posted in NEWS | Tagged , , | Leave a comment

20130724 上海OOW第三日

又在OOW的展览大厅打了一回酱油,和Jackie Han、Kamus一起介绍ACE Program。
今天上午本打算一早去听技术的专题,没想到有客户数据库出现了异常,远端诊断和分析用了不少时间,到了OOW现场时,KeyNotes已经结束了。又赶上公司的展台事情比较多,因此一直在展台帮忙。
OOW的午饭从1点开始,而我们的专题从1:20开始,怕吃饭的人多来不及,于是打算等专题结束后再去,没有想到后来提问的人还很多,专题一直持续到了2点以后。跑到楼上发现午饭也结束了。
公司的展台仍然是络绎不绝,连Oracle数据库研发的老大Andy都来到了我们的展台,可惜当时正在给一个咨询问题的朋友讲解12c的架构,完全没有注意到。知道最后看到Eygle的微博才发现,也算是后知后觉了。我们的12c架构图非常受欢迎,从北京带来的近900张图,在今天下午就已经发送一空了。看来明天上午已经没啥礼品可送了。
晚上Oracle的答谢晚宴,感觉和旧金山的金银岛音乐节异曲同工,都是我很不感冒的。转了一圈就闪人了。

Posted in NEWS | Tagged , , | Leave a comment