Author Archives: yangtingkun

数据泵导致表部分列

看到群里有人提出一个需求,一张表数据量很大,只想导出其中一部分列。 无论是老版本exp还是数据泵expdp,Oracle都提供了QUERY的功能,这使得查询表中部分记录的功能可以实现,但是QUERY只能过滤行,而不能过滤列,Oracle数据泵会读取表中全部列的。 在12c中,Oracle为数据泵提供了VIEW功能,使得导出的时候可以根据视图的定义来导出表中的数据: SQL> select banner from v$version; BANNER ——————————————————————————– Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production SQL> CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES; 表已创建。 SQL> create view v_tables as select owner, table_name, tablespace_name from t_tables … Continue reading

Posted in ORACLE | Tagged , , | Leave a comment

12c新增并行索引扫描

Oracle在12c之前对于索引范围扫描是没有办法并行执行的,从12.1开始,Oracle可以并行的执行索引扫描。 首先创建测试环境: SQL> create table t_para_ind (id number, name varchar2(30), created date);   Table created.   SQL> insert into t_para_ind select id, object_name, created from t_big;   6735106 rows created.   SQL> commit;   Commit complete.   SQL> create index … Continue reading

Posted in ORACLE | Tagged , | Leave a comment

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; … Continue reading

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 … Continue reading

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 … Continue reading

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> … Continue reading

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   … Continue reading

Posted in ORACLE | Tagged , , | Leave a comment

20180517 Oracle Code Singapore

接到Oracle的邀请参加了2018年5月17日在新加坡举办的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,未经优化前需要1分12秒的执行时间,消耗17万逻辑读。       … Continue reading

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 … Continue reading

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 … Continue reading

Posted in BUG, ORACLE | Tagged , , , | 2 Comments