Oracle数据库中最让人匪夷所思的十大问题盘点
数据的世界无奇不有，常常会遇到一些超出常识之外的故障的发生。这就要求广大的DBA要深入了解数据库的内部机制，面对一些奇葩的故障或者问题能够拨开迷雾找到真相。今天我们一起来盘点一下Oracle数据库中，都有过哪些让人匪夷所思的问题。
No 1. Select 语句也会导致系统hang住吗
我们都知道在 Oracle 数据库里是“读不阻塞写，写不阻塞读”，那么是否可以认为在正常情况下，select 操作是怎样都能执行，始终不会被 hang 住的呢？
答案是否定的。
崔华老师分享过这样一个案例。通过10049事件分析了 sql 硬解析时在相关表对象上 library cache lock 的持有情况。发现以下规律：当以 exclusive 模式（比如添加主键的操作）在某对象上持有 library cache lock，那么后续的以硬解析方式执行的针对该表的所有sql（包括 select 语句）都将被 hang 住。
但有一些以exclusive模式持有library cache lock的操作，不一定会对select造成阻塞，只有select的时间点要恰好是Oracle以X模式持有library cache lock才会。
建议阅读：
Oracle数据库里SELECT操作Hang解析 作者：崔华
No 2. SQL增加DISTINCT后查询效率反而提高？
在SQL中，只要增加了DISTINCT关键字，Oracle就会对随后跟着的所有字段进行排序去重。因此，不正确地使用DISTINCT很可能带来性能方面的负面影响。
不过有次碰到了一个有趣的现象：开发人员在测试一个比较复杂的SQL时发现如果SQL中加上了DISTINCT，则查询大概要花费4分钟左右；而如果不加DISTINCT，则查询执行了10多分钟仍然没有返回结果。
事实上，在多表操作中，当连接的表数据量很大，但SELECT的最终结果并不是很多，且SELECT列数也不是很多的时候，加上DISTINCT后，增加的排序的代价要小于SEMIJOIN连接的代价。这就是增加一个DISTINCT操作，查询效率反而提高的真正原因。
因此优化时没有什么东西是一成不变的，几乎任何事情都有可能发生，不要被一些所谓规则限制住。
建议阅读：
SQL增加DISTINCT后查询效率反而提高 作者：杨廷琨
No 3. 全表扫描会产生大量 db file sequential read 等待吗？
我们知道Oracle在进行全表扫的时候是多块读的方式。但我们曾遇到这样的情况，开发人员在进行新系统上线前的数据校验测试时，发现一条手工执行的 SQL 执行了超过1小时还没有返回结果。SQL很简单，走全表扫描。假设单进程全表扫描表，每秒扫描 50MB 大小（这实际上是一个很保守的扫描速度了），那么只需要245秒就可以完成扫描。
之后我们查看会话的等待事件发现，99%以上的等待时间是 db file sequential read？！
那么 SQL 执行计划为全表扫描（或索引快速全扫描）的时候，在运行时会有哪些情况实际上是单块读？
- db_file_multiblock_read_count 参数设置为1
- 表或索引的大部分块在 buffer cache 中，少量不连续的块在磁盘上。
- 一些特殊的块，比如段头
- 行链接的块
- LOB 列的索引块和 cache 的 LOB 块（虽然10046事件看不到 lob 索引和 cache 的 lob 的读等待，但客观上是存在的。）
- 事务过大，导致undo块的读取
在案例中，就属于最后一种情况。因此建议在生产系统上，特别是 OLTP 类型的系统上，尽量避免大事务。
建议阅读：
常识之外：全表扫描为何产生大量 db file sequential read 单块读？ 作者：熊军
No 4. 一个进程可以自成死锁么
世界之大，无奇不有。原来数据的世界里，真有这种跟自己打起来的事情发生。在执行一个存储过程的时候，遇到如下报错。
之后查看Blocker是session 362，Waiter也是session 362，典型的自己锁死了自己。
那么这究竟是一个什么样的神奇的存储过程把自己锁死了呢？
select sid from v$mystat where rownum<2; SID ———- 362 SQL> create table t1 (id varchar2(10), amount number(10)); Table created SQL> insert into t1 values('cuihua',100); 1 row inserted SQL> commit; Commit complete SQL> select * from t1; ID AMOUNT ———- ———– cuihua 100 SQL> create procedure p_autonomous is 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 begin 4 update t1 set amount=102 5 where id='cuihua'; 6 commit; 7 end; 8 / Procedure created SQL> create procedure p_test is 2 begin 3 update t1 set amount=101 where id='cuihua'; 4 p_autonomous; 5 commit; 6 end; 7 / Procedure created
建议阅读：
自相矛盾：一个进程可以自成死锁么？ 作者：崔华
No 5. 数据库中的空格，远比你想象中更重要
你即将进入危险地带，请做好心理准备。
以下是由空格引发的两次数据库的血案。
事件1：
Oracle 11204的RAC数据库，在某一时刻突然出现节点重启，严重影响业务。经工程师检测，在重启之前数据库依次遇到以下问题：
1、告警日志中出现ORA-27504的错误，并明确显示请求的IP地址不存在，需要检查ifconfig的输出。
2、IPC超时
3、出现实例驱逐，然由于是两节点的RAC数据库，互相访问不到彼此的心跳，等待节点2重启。
分析原因是由于节点2的IP地址被篡改，而导致心跳异常。
最终发现，引发故障的操作如下：
执行ifconfig –a6来检查IPV6的地址，但是命令敲错 执行了ifconfig –a 6，在a和6之间多了一个空格 导致主机所有的IP地址被设置成0.0.0.0
事件2：
通过sqlplus 连接数据库，若SQL 语句以/*方式的注释开头，注释与后面的内容之间的空格很可能会决定数据库的生死。
1、有些2B的结果
这是怎么回事？
对于第二个语句而言，注释并没有对语句产生任何的影响；而对于第三个语句，实际上 Oracle 并没有把这个语句作为包含注释的语句看待，实际上 sqlplus 运行的是/，也就是将缓存中的语句再运行一次，而完全忽略了/之后的内容。
2、小问题也是大隐患
如果上一条是 SELECT，则显然对系统影响最小（事实上这个影响也不小，因为当前需要执行的 SQL 被跳过了，这可能影响这个 SQL 脚本的逻辑），而如果是 DELETE 语句，如上所示，那么表中数据就会被多删除一次。
也许有人会说，删除也无所谓，可以进行回滚，并没有数据的损失。事实上，对于 SHELL 脚本方式或者编写好的 SQL 脚本而言，是没有办法对其进行控制的。
即使不在脚本中运行，有些情况下也是没有机会回滚的，比如：
这种想要恢复就只能通过闪回了。而如果重复执行的是 DDL，那么连闪回的机会都没有了。
再小的问题，都是大大的隐患。不要忘记墨菲定律，可能发生故障的地方，终究会有人掉进坑里。
No 6. 你知道的临时表空间的占用最大可以达到多少
在我们客户的系统中，有一次临时表空间的占用达到了600G。首先我们查询了v$sort_usage，发现有几百个会话在执行相同的操作，SQL ID都是一样的，每个占用的临时段的大小将近1G。
我们选择了一个占用了接近1GB的会话，查询v$open_cursor，查看其打开的游标中是否有大数据量排序的SQL：
通过count(*)出来的结果居然有1200多万条数据，一个前台应用，不知道取1200多万条数据干嘛。但是从rows_processed/executions只有几万的结果来看，应用在取了几万条数据之后，由于某些原因（最大的可能就是不能再处理更多的数据），不再继续取数据，但是游标也一直没有关闭。
在基于对临时表空间分析的基础上，我们认为，
临时段的占用大体可以分为三类占用：
- SQL语句排序、HASH JOIN占用
- 临时表占用
- 临时LOB对象占用
临时表空间的异常占用，一种缓步增长的，另一种情况：一下撑满的通常是一个极大数据量的排序或极大的索引的创建。缓步增长的情况，跟系统的内存被逐渐占用类似，存在“泄露”。比如排序的SQL游标没有关闭，比如本文的案例；比如会话级临时表产生了数据后一直没有清除；临时LOB对象没有清理或泄露。前两种比较好去分析处理，但是临时LOB的泄露问题就复杂很多。
建议阅读：
天呐，临时表空间占用了600G？！ 作者：熊军
No 7. Shutdown immediate 会导致数据库无法启动吗
我们知道，以immediate的方式停库之后，整个Oracle数据库的文件都是处于一致的状态，重新启动数据库实例后按理说是不需要再进行实例恢复的。
然而就在不久前，我们却遇到了这样一件奇怪的事情。客户通过shutdown immediate停库维护后，启动数据库无法报错，此时发现数据库无法open，期间尝试了各种数据库手段，均失败告终。
这个案例在我们工程师的努力下，得到了及时处理，并没有数据丢失。然而，最终也没有明确得出结论，为什么会出现这样的问题。
根据我们的经验，我们做出如下推测，仅供参考：
1、shutdown immediate之后，数据库写入到操作系统cache，还未完全写入到disk上时，此时数据库主机被强行重启；由于操作系统cache丢失，导致数据库出现了不一致的情况(本文环境是Linux文件系统)。 2、其他程序或软件破坏了Oracle数据库文件的一致性（实际上，经过了解该环境部署了Rose HA软件；而且客户在操作时，据说并没有停止rose ha软件）。
最后要说明一点的是，由于在案例中的数据库版本是9i的，很可能是因为版本上的bug，但由于Oracle已经不对这些旧的版本提供补丁和服务，因此强烈建议生产环境尽量升级到较高的版本。
建议阅读：
我明明 immediate 关库的，怎么就打不开了？！ 作者：李真旭
No 8. 官方文档也出错，原来所有的跨平台迁移都可以通过XTTS的方式实现
自从2015年初进行了xtts增量的U2L迁移测试之后，国内很多人都开始利用这种方案进行数据库跨平台迁移了，基本上都是利用Oracle 封装的perl脚本。其中Oracle MOS文档 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID 1389592.1) 明确提到目标端环境必须是Linux,
这里该文档中的一段原话：
The source system may be any platform provided the prerequisites referenced and listed below for both platform and database are met. The destination system must be Linux, either 64-bit Oracle Linux or RedHat Linux, as long as it is a certified version. The typical use case is expected to be migrating data from a big endian platform, such as IBM AIX, HP-UX, or Solaris SPARC, to 64-bit Oracle Linux, such as Oracle Exadata Database Machine running Oracle Linux.
其实这里很容易让人产生误解，这里Oracle并非说不支持其他平台，而是说Oracle 提供的封装perl脚本不支持而已。但是手工进行xtts操作，完全是ok的；经过我们工程师的测试也是可行。
建议阅读：
我们都被骗了，所有的跨平台迁移都可以通过XTTS实现 作者：李真旭
No 9. 脑洞大开，SQL也可以解脑经急转弯吗？
有一类人，他们狂热追求技术，见猎心喜，遇难而技痒，他们把SQL当做艺术，把旁人眼中的枯燥演绎成经典，云和恩墨专家团队中的杨廷琨、罗海雄就都是这样的SQL专家。
分享几个比较有趣的案例：
1、用SQL解海盗分金的问题
看到这个问题，你脑海中的答案是什么样的？
这样的吗？
还是这样的？
这都不够酷，来看看杨长老是答案，是这样的：
看到人与人之间的差距了吗？你是不是有点怀疑我们吃的是米饭，杨长老平时吃的是芯片呢？（小编想约个饭调研一下的 ：）
建议阅读：
神马？SQL竟然可以解脑筋急转弯的题目？ 作者：盖国强
No 10. PDB难道不能以shutdown abort的方式关闭？
我们一直说，新特性就像阿甘的巧克力，不知道是惊喜还是坑。在12c中，提出了多租户的概念，允许用户将多套系统整合到一起方便管理并降低成本，同时具有快速线性扩展等优势。然而在12.1的版本中，依然存在一些坑。
我们之前分享过一篇文章，