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只有几万的结果来看,应用在取了几万条数据之后,由于某些原因(最大的可能就是不能再处理更多的数据),不再继续取数据,但是游标也一直没有关闭。

在基于对临时表空间分析的基础上,我们认为,

临时段的占用大体可以分为三类占用:

  1. SQL语句排序、HASH JOIN占用
  2. 临时表占用
  3. 临时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竟然可以解脑筋急转弯的题目? 作者:盖国强

2、我们一起画元宵

3、玩转扑克牌游戏?套路不如SQL

No 10. PDB难道不能以shutdown abort的方式关闭?

我们一直说,新特性就像阿甘的巧克力,不知道是惊喜还是坑。在12c中,提出了多租户的概念,允许用户将多套系统整合到一起方便管理并降低成本,同时具有快速线性扩展等优势。然而在12.1的版本中,依然存在一些坑。

我们之前分享过一篇文章,

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-07-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏GopherCoder

专栏:008:MySQLdb及其银行模拟转账

1504
来自专栏idba

order by 主键id导致全表扫描的问题

一 简介 在检查某业务数据库的slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where条件字段存在正确的组合索引,正确的情况下优化器应...

662
来自专栏数据和云

以12c Identity类型示范自我探索式学习方法

作者简介: ? Oracle ACE总监,ACOUG联合创始人,云和恩墨的联合创始人,致力于通过不断的技术探索,帮助中国用户理解和接触新技术,推广数据库技术应...

3994
来自专栏PHP技术

MYSQL性能优化分享(分库分表)

MYSQL性能优化之分库分表与不停机修改mysql表结构,需要的朋友可以参考下 1、分库分表 很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严...

3265
来自专栏张善友的专栏

PostgreSQL 与 MySQL 相比,优势何在?

一、 PostgreSQL 的稳定性极强, Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多 MySQL 用户都遇到过Serve...

2276
来自专栏数据和云

实践实战:在PoC中的Oracle 12c优化器参数推荐

最近,Oracle数据库优化器的产品经理 Nigel Bayliss 发布了一篇文档,介绍:Setting up the Oracle Optimizer fo...

814
来自专栏Albert陈凯

2018-08-05 没有测试用例的代码,根本不应该跑在服务器上

在实际测试中,一个单元可以小到一个方法,也可以大到包含多个类。从定义上讲,单元测试和集成测试是有严格的区分的,但是在实际开发中它们可能并没有那么严格的界限。如果...

655
来自专栏xingoo, 一个梦想做发明家的程序员

循序渐进,了解Hive是什么!

一直想抽个时间整理下最近的所学,断断续续接触hive也有半个多月了,大体上了解了很多Hive相关的知识。那么,一般对陌生事物的认知都会经历下面几个阶段: ...

1935
来自专栏跟着阿笨一起玩NET

分表处理设计思想和实现[转载]

分表是个目前算是比较炒的比较流行的概念,特别是在大负载的情况下,分表是一个良好分散数据库压力的好方法。

471
来自专栏杨建荣的学习笔记

一次归档报错的处理和分析(r7笔记第60天)

昨天在睡觉前接到了一条报警短信,本来已经疲倦的身轻如燕,但是看到报警,还是警觉了起来 ZABBIX-监控系统: --------------------...

2894

扫描关注云+社区