0

Oracle数据库中最让人匪夷所思的十大问题盘点

孙雪 2017-07-16
71

数据的世界无奇不有,常常会遇到一些超出常识之外的故障的发生。这就要求广大的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的版本中,依然存在一些坑。


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

回答了这四个问题,少踩12c 多租户的好多坑  作者:盖国强


里面提到了一个问题,多租户环境下,PDB能不能以abort的方式关闭?

答案是不能,在Oracle 12.1.0.1版本中,Oracle根本就不支持PDB的异常关闭,所以Crash Instance,就只能Crash CDB的Instance了。


这个问题在12.2中得到了修复。但这一bug的确对于生产系统来说是太大的隐患。


这些奇葩的故障你都遇到过吗?你还有没有更刺激的运维经历,欢迎留言或投稿分享(xue.sun@enmotech.com)。


世界之大无奇不有,打破思维定式,才能创造新的奇迹。以上作者解决问题的思路是否让你耳目一新?微信回复作者名字,可立即获得该作者所有的文章列表,欢迎体验!

加入"云和恩墨大讲堂",参与讨论学习

搜索 盖国强(Eygle) :eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


关注微信,获得后续精彩分享

近期文章

MySQL大表优化方案(最全面)

RAC 节点参数不一致引发的悲剧

MySQL - 8种常见的SQL错误用法

RAC 如何安装新主机识别老存储恢复数据库

Oracle 子查询优化系列精讲

我明明 immediate 关库的,怎么就打不开了

我们都被骗了,所有的跨平台迁移都可以通过XTTS实现

Linux环境下合理配置大内存页

资源下载

(OraNews)回复关键字获取

2016DTCC, 2016数据库大会PPT;

DBALife,"DBA的一天"精品海报大图;

12cArch,“Oracle 12c体系结构”精品海报;

DBA01,《Oracle DBA手记》第一本下载;

YunHe“云和恩墨大讲堂”案例文档下载;

最后修改时间:2020-05-08 00:18:29
「喜欢文章,快来给作者赞赏墨值吧」
文章转载自孙雪,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部