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

20130723 上海OOW第二日

今天在OOW的ACOUG专场分享了Think Different的主题。
今天一天在会场非常的充实。到了会场后先去听了今天的KeyNotes,其中Steve Miranda的云计算的主题以及Andy Mendelsohn的12c数据库对云计算的支持,都是本次大会的重点话题。Oracle 12c中的C指的就是CLOUD,而12c最大的亮点PLUGABLE DB就是Oracle在DB层对云架构的支持。
如果说去年OOW大会只是给出了12c PLUGABLE DATABASE的概念,那么今年OOW上Oracle已经想好要如何用PDB来支持云服务了,甚至我怀疑Oracle来云服务的收费细则都已经定义好了,青铜、白银和黄金三档,每档对应的架构和服务都已经定义清楚,而在各个档之间进行升级也同样进行说明。看来Oracle已经准备利用12c来打造数据库服务云了。只不过以Oracle以往收费的标准,不知道一些不想自己搭建环境、维护数据库的中小企业,能否接受正版云服务的价格。
中午OOW的午饭一如既往的不给力,这种面包、三明治的配置更适合老外,估计大部分国内参会者,这三天午饭会比较郁闷。
下午全程参加了展览大厅的ACOUG活动,首先是Eygle两个优化的主题,然后是Kamus三个关于升级的主题,最后是我的两个主题,一个是ODA,另一个是Think Different。可惜的是,每个主题的时间只有二十分钟,而我们任何一个主题正常时间都应该超过1小时。因此,最终Eygle只讲了一个主题就超过了两个主题的时间,而Kamus最后紧赶慢赶终于完成了三个主题,但是时间方面超了不少。根据他们两个的前车之鉴,我直接选择了现场绝大部分人都想听的Think Different,而舍弃了ODA的主题。其实舍弃ODA也比较可惜,在OOW的现场展示了ODA的最新版本,而我在中午的时候还把最新版的ODA的内容添加到了PPT里面。
剩下一些零碎的时间基本上都在公司的展台帮忙,除了解答问题,当背景合影外,还碰到了不少新、老朋友。

Posted in NEWS | Tagged , , | Leave a comment

20130722 上海OOW开幕

期待已久的ORACLE OPEN WORLD终于开幕了。
有了以往OOW的经验,没有一大早就跑去排队注册,而是到了下午才去签到注册地方,几分钟搞定了注册的过程。
今天一早听说马克赫德的飞机由于机械故障,导致中途返航,导致他将无法现场出席本次上海OOW大会,他的演讲将改为远程的方式。我在去年10月不但现场听过他的演讲,而且现场看过Larry的演讲,因此倒也没有什么遗憾,而对于大部分技术人员而言,在现场的时候也会盯着大屏幕,是不是现场真人演讲也没有本质的区别。估计影响最大的应该是媒体的朋友,缺少现场采访对象,可能会使本次的OOW之行大打折扣。
下午并没有去听KeyNotes,而是跑到了公司展台帮忙。公司的几个同事基本已经把展品布置好了,我们为大家准备了一些礼物,尤其是一张Oracle 12c的架构图,经过我们多次设计修改,是公司很多人集体智慧的结果,希望可以对技术爱好者有所帮助。

Posted in NEWS | Tagged , , | Leave a comment

20130721 OOW媒体发布会

今天抵达了上海,开始开启OOW模式。
这次是第三次参加OOW,和以往不同,本次公司作为参展商参加了OOW,在一层的展览大厅有专门的展台,而且本次公司的6个ACE都到达现场,准备一起参加这次Oracle的盛会。
今天下午原本想去会场先进行注册,但是听说会场还没有开门。本来今天应该没有相关的活动,结果Eygle作为微博特使有一个媒体发布会,被Eygle拉了过去,顺便把Kamus也一起叫上。
这个媒体发布会主要介绍了本次OOW的重点方向,包括软硬件集成的系统、云计算、客户体验和JAVA。另外,本次会议的规模在国内也是空前的,参会人员达到了18000人,而国内其他IT上千人的会议相比,绝大算得上是巨无霸了。

Posted in NEWS | Tagged , , | Leave a comment

密码延迟验证导致的系统HANG住

又是一个11g新特性导致的问题。
这个新特性很早之前就研究过,也在其他客户处碰到过类似的问题。从11g开始,如果一个用户使用不正确的密码尝试登录数据库,那么随着登录失败次数的增加,每次登录验证前延迟等待的时间也会增加:

SQL> SET TIME ON
18:30:54 SQL> 
18:30:58 SQL> conn test/test
Connected.
18:31:25 SQL> 
18:31:25 SQL> conn test/a
conn test/a
conn test/a
conn test/a
conn test/a
conn test/a
conn test/a
conn test/test
conn test/a
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected TO ORACLE.
18:31:26 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
18:31:26 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
18:31:26 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
18:31:27 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
18:31:29 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
18:31:32 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
18:31:36 SQL> Connected.
18:31:36 SQL> ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected TO ORACLE.
18:31:36 SQL>

可以看到,从第三次密码错误的登录开始,每次延迟时间开始变成2秒、3秒并一次递增。既是这时提供正确的密码登录,会话也会延迟N秒,然后进行验证。不过一旦验证成功,会将失败计数清零,后续的错误登录会重新计数。
不过这只是单一会话尝试失败登录的情况,如果同时存在两个会话,则很快延迟验证时间就会达到10秒、20秒的级别。如果同时大量的连接采用错误的密码,基本上这个用户的登录就会被完全HANG住。
客户的数据库就出现了类似的情况,数据库版本为11.2.0.3 RAC,在数据库中观察,三个节点每个节点的会话数都接近SESSIONS参数设置的上线3000,而后台高级日志已经出现了ORA-20错误。由于客户系统的关键用户只有一个,因此几乎所有的会话都无法正常的登录到数据库中。而在数据库上发现,大量的会话用户名、EVENT以及PROGRAM都信息都是NULL,这说明这些会话还没有完成验证成功的登录到数据库中。而当前主机的CPU资源使用并不高,那些已经连接到数据库中的进程也可以正常的工作。尝试使用SYSTEM等其他用户发现可以迅速的登录数据库。所有这一切都已经说明,当前有一个或多个中间件服务器在使用错误的密码连接数据库,由于密码延迟验证的策略,导致所有后续的连接都被HANG住。
任何一个新特性带来性能或功能上的提高的同时,也会引入相关的bug,显然这个安全性上的考虑,有时候也会带来验证的性能问题,甚至成为用来攻击数据库的一种手段。
之前几次并没有给出彻底屏蔽密码延迟验证的手段,而Oracle最强大之处就在于几乎所有的功能和特性都有对应的开关,通过设置EVENTS 28401可以屏蔽密码延迟验证:

SQL> ALTER SYSTEM SET EVENT =28401 TRACE NAME CONTEXT FOREVER, LEVEL 1’ SCOPE = SPFILE;

设置该事件后重启数据库即可。

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