落落的专栏 专注SQL调优 性能调优

原创 徒弟涨工资排行榜

这个是徒弟张工资了，和我聊天，

原创 SQL改写的书终于出版了

众所周知，SQL 优化很多 SQL 都是写法上有问题，目前市面上没有这种书籍，为了造福广大Oracle使用者教主和我特意编写 SQL 改写 优化的书籍 ，大家可以到这里购买http://product.china-pub.com/4455744#mlSQL水平比较菜的兄弟们也可以购买运维同志们也可以购买

原创 SQL优化新书《SQL优化核心思想》终于出版了

耗时三年，SQL优化大作终于出版了，有想提升SQL优化水平的同学，可以买本看看本书共10章内容：第一章介绍SQL优化的基础知识；第二章讲解统计信息相关内容；第三章讲解执行计划，快速找出SQL性能瓶颈；第四章讲解常见的访问路径；第五章讲解表的各种连接方式；第六章介绍单表访问以及索引扫描的成本计算，引出优化思想。第七章讲解查询变换；第八章讲解优化技巧；第九章分享经典案例；第十章介绍全自动SQL审核...

原创 PostGreSQL/openGauss表膨胀处理

最后搞个了crontab，每隔一段时间检查n_dead_tup，当n_dead_tup大于某个阈值，检查表上有没有锁，没有锁就人工回收空间，自此之后，每次月结跑批就很稳定了。在PG/OG数据库中，命令vacuum full，插件pg_repack用于处理表膨胀，但是别高兴得太早，如果有长事务，vacuum full执行完之后空间还是无法回收。为什么要先锁住原表呢？我做实验的表比较小，如果表特别大，几十GB，上百GB就不好处理了，所以大表最好分区，分区之后可以按照分区单独处理表膨胀。

原创 openGauss关联列数据类型不一致引起谓词传递失败

视图dba_tab_columns的定义非常复杂，这里就不贴了，视图dba_tab_columns的table_name没有做类型转换，table_name同样来自pg_class.relname。这是因为dba_tab_columns过滤条件有where table_name = 'psph_t'，并且两个表关联条件是dct.table_name = dtcs.table_name。...........................省略.............................

原创 openGauss谓词推入失败案例二

Oracle一般都是安装在INTEL志强2代，志强3代CPU上，国产库一般采购的是ARM或者海光的X86处理器，ARM，海光CPU本身的性能就比INTEL志强二代，三代单核慢接近1倍，6.针对案例一以及本案例，其实笔者改写的SQL有点问题，应该将标量子查询放最外面，而笔者放在了最里面，如果前端某个用户选了中间第几千页，标量执行的次数就多了，3.如果没有复杂的包，存储过程，将Oracle迁移至国产库优化工作量是可控的，因为问题都是共性的，一旦有非常复杂的包，存储过程要迁移，这个工作量就很大了。

原创 openGauss谓词推入失败案例一

A的名字是VW_RME_EQP，以VM开头或者结尾大概率是视图，E的名字是VW_OPTROAD_PIONTRES，E也大概率是视图。这条SQL是典型的分页语句，SQL里面没有聚合函数，没有分析函数，最多返回20行，正常情况下应该秒杀。

原创 MogDB&openGauss查询重写规则uniquecheck

在MogDB&openGauss中，参数rewrite_rule用于控制查询重写，本文介绍查询重写规则uniquecheck。子查询test02 t2没有被提升(Oracle的说法是没有被展开)，走了filter，被扫描了4000多万次。设置rewrite_rule=uniquecheck之后，子查询被提升了，t1和t2走了hash join。如果子查询JOIN列(t2.object_id)不唯一，SQL会报错。如果子查询有agg函数，无需设置查询重写参数，可以自动提升。整个SQL耗时10秒，性能较好。

原创 MogDB&openGauss查询重写规则magicset

从执行计划上可以看到test01 t3是大表，有4400W行数据，它走了全表扫描，t2是小表，并且t2还有过滤条件，过滤完之后只有36行数据。disable_pullup_expr_sublink表示禁止子查询提升，效果与添加hint /*+ no_expand */ 一样。如果不想设置rewrite_rule=magicset，也可以对子查询添加/*+ no_expand */达到优化目的。为什么t2会被访问2次呢？从执行计划上可以看到，t2被访问了2次，但是整个SQL语句中，t2只出现了一次。

原创 MogDB&openGauss查询重写规则lazyagg

从执行计划中看到，子查询先进行了GROUP BY，再与test02进行关联，整个SQL GROUP BY了2次。当子查询中有GROUP BY，子查询中的表很大，子查询与外面的表(比较小/过滤完之后数据量少)进行关联之后还有GROUP BY，这个时候就可以开启lazyagg特性，加快SQL性能。从执行计划中看到，子查询中的聚合运算被消除了，子查询中的表test01与test02先做了关联，关联之后再进行GROUP BY，整个SQL只做了1次GROUP BY。2.子查询与外面的表关联之后还有GROUP BY。

原创 MogDB&openGauss中的Bitmap Index Scan

从上面的例子看到，当where条件有limit，不要走Bitmap Index Scan，limit无法刹车。没有force_bitmapor参数，当where条件中有or，想要强制走BitmapOr，禁止全表扫描就行。4. BitmapOr 要注意观察最终返回的数据量，返回数据量少性能较高，返回数据量大，走全表扫描。禁止indexscan，禁止走全表扫描，这个时候只能走Bitmap Index Scan。只有在返回少量数据的情况下BitmapOr性能才高，返回的数据量大，全表扫描性能更高。

原创 MogDB&openGauss导出TYPE对象的DDL

可以先使用gs_dump导出所有对象DDL，再使用shell过滤CREATE TYPTE。在MogDB/openGauss中，可以使用gs_dump导出数据库中所有对象。目前 dbms_metadata.get_ddl 还不支持导出TYPE对象。虽然 dbms_metadata.get_ddl不支持，但是还是有其他方法。

原创 CPU对数据库的性能影响

设置了参数alter system set "_serial_direct_read"=never;intel e5-2667 v4 1.58秒，这颗CPU和E5-2697 v3都是垃圾佬比较关注的CPU。为了避免物理IO的影响，多次反复运行，确保test01全部缓存在buffer cache中。我拿消费级CPU对比服务器CPU是有问题的，毕竟消费级CPU主频比服务器CPU高接近1倍了。从上面的测试结果可以看到，CPU对数据库的影响非常巨大，特别是有跑批业务的情况。最近做了个CPU性能测试，测试语句如下。

原创 利用业务逻辑+OB分布式特性优化SQL

Oceanbase优化经典案例：利用业务逻辑+OB分布式特性优化SQL

原创 SELECT后面有自定义函数的优化方法

SELECT后面有自定义函数的优化方法

原创 利用ORDERED_PREDICATES优化多个自定函数作为WHERE过滤条件

利用ORDERED_PREDICATES优化多个自定函数作为WHERE过滤条件

原创 where条件中有权限校验的自定义函数优化方法

where条件中有权限校验的自定义函数优化方法

原创 Oracle19c中SQL分开跑很快UNION ALL之后变慢分析

SQL分开跑很快，UNION ALL之后变慢

原创 SQL优化案例之exists中套or not exists

一个SQL带你了解SQL等价改写各种姿势

原创 SQL优化案例之where exists(col1=xxx or col2=xxx)等价改写

SQL优化案例之where exists(col1=xxx or col2=xxx)等价改写

原创 今年一直搞Oracle EBS优化，脑壳痛

搞了10几年的性能优化,OLTP系统,OLAP系统,Oracle,MYSQL,PG,Greenplum,Oceanbase,hive,达梦等等各种数据库优化项目做过太多太多...唯独EBS系统没有单独做过优化，一直都很遗憾。虽然之前做网络培训的时候教了几个EBS DBA徒弟，他们也找我优化过EBS的SQL，但是都是零零散散的。今年开春，公司接了一个EBS优化项目(版本ebs11i)，这也算公司是第一个真正意义的EBS优化项目。早期是3个DBA和3个EBS顾问以及项目经理在客户现场，搞了2个多月，进展

原创 利用分析函数减少对表访问次数

最近在给一银行客户优化数据仓库，发现了很多烂SQL下面这条SQL有很好的教学作用，现拿来与大家分享SQL语句如下：select /*+ parallel(16) */ count(*) from (SELECT HM, DZ, YB, ZZDH, ZJLX, ZJHM, JGBM, Z

原创 PostgreSQL对or exists产生的filter优化二

PostgreSQL会对or exists产生的filter进行优化，上一篇文章没有测试exists中有大表的情况，今天来测试一下exists中有大表的情况注意：测试期间没有对表添加索引create table a as select * from dba_objects;create table b as select * from a;create table c as select * from a;create table d as select * from a;insert i

原创 PostgreSQL对or exists产生的filter优化一

在Oracle数据库中，where条件中有or exists子查询会走Filter，这种情况一般都需要改写SQL

原创 利用Python将Oracle表中的数据抽到PostgreSQL

import cx_Oracleimport psycopg2import osimport timefrom io import StringIOimport pandas as pd#说明：本脚本用于将Oracle数据迁移到PG#注意：源表与目标表字段数量必须一致os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' #设置字符集为UTF8，防止中文乱码source_db = cx_Oracle.connect.

原创 对自定义函数开启parallel_enable属性使函数可以并行来提升SQL查询性能

***人社系统最近做了数据迁移，采用国产的Zdata一体机替换了老旧的小型机，数据库也从11g升级为了19c之前整个系统被拆分为5套子系统，这次升级将5套子系统做了整合，采用pdb的方式将5套子系统统一存放在Zdata一体机中完成数据迁移之后，在没有针对SQL进行专门的优化的前提下，从各项指标上看，系统的整体性能提升了15-20倍虽然整个系统的性能有了巨大提升，但是我们觉得还不够，我们的目标是将性能提升100倍前几天对系统中一个老大难的SQL做了等价改写，SQL从每次执行5到10分钟降低为了15秒.

原创 通过一个例子详解 MySQL record lock(记录锁) 的加锁方式

本文基于MySQL8.0.19，下面通过一个例子讲解 MySQL record lock(记录锁) 的加锁方式SESSION1:mysql> SELECT VERSION();+-----------+| VERSION() |+-----------+| 8.0.19 |+-----------+1 row in set (0.01 sec)mysql> CREATE TABLE t(id INT UNSIGNED PRIMARY KEY ,NAME VARCH

原创 都2020年了还能看见奇葩的SQL写法

原创 A.COLUMN LIKE B.COLUMN% 关联的优化方法

现在有个SQL要跑10秒：SQL> select a0.id, 2 a1.room_no, 3 a1.user_name, 4 a1.user_no, 5 row_number() over(partition by a0.id order by a1.room_enter_time desc) as fn 6 from vid_attachment a0 7 inner join vid_room

原创 MySQL8.0.19 MGR MySQL router MySQL connector failover 组合实现高可用

在MySQL5.7之前，本人一直是很排斥MySQL的，因为MySQL的半同步并不能100%保证主从数据一致性，MHA架构也不能100%保证主从数据一致性，一个连数据一致性都不能保证的数据库我肯定是不会像学Oracle那样化大量时间去学习。MySQL5.7 MGR技术的出现，MySQL8.0对分析函数的支持，对内连接等值访问HASH JOIN 算法的支持等等新特征，让我看到MySQL要脱胎换骨了，这也终于让我有兴趣学习MySQL了，今天先配置MySQL MGR 3节点单主模式的集群，然后利用MySQL rou

原创 利用Python多进程并行执行加快MySQL批量UPDATE执行速度

现在有个表tmysql> desc t;+----------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+--------------+------+-----+---------+----------------+| owner

原创 利用Python解析MySQL BINLOG从而回滚UPDATE和DELETE误操作

MySQL没有Oracle的闪回(flashback)功能，如果不小心执行了UPDATE或者DELETE误操作，想要回滚相比Oracle还是挺麻烦的可以利用mysqlbinlog工具解析binlog，从而拼接出UPDATE和DELETE的回滚语句，人工拼接比较麻烦，所以利用Python来拼接import ioimport pymysqldef processUPDATE(db_name,table_name): conn = pymysql.connect("192.168.56.10",

原创 Oracle中TX锁(行锁)监控，抓TX锁的源头

DBA小伙子，看到这个文章是不是很开心，解决了你一个大麻烦session 1: update emp_bak set ename='沙雕' where empno=7369;session 2: update emp_bak set ename='大长腿' where empno=7369;session 3: update emp_bak set ename='矮丑穷' where empno=7369;运行下面脚本可以抓到哪个SID,哪个SQL_ID,跑的SQL_TEXT锁住了哪个SID

原创 利用Python监控MySQL当前跑的TOP SESSION

利用ps命令抓出MySQL PID，根据PID再监控每个线程CPU使用率以及跑的SQL语句import pymysqlimport osimport timeos.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'command="ps -ef | grep 3306 | grep -v grep | awk '{print $2}'"with os.popen(command, "r") as pid: pid = str(int(pi

原创 利用Python监控Oracle当前跑的TOP SESSION

因为要使用Python经常连接到Oracle获取SQL_ID以及SQL语句，会话频繁连接，断开会引起Oracle服务器性能严重下降所以利用Python监控Oracle的时候，要先在Oracle配置连接池(DRCP)begin dbms_connection_pool.configure_pool(pool_name => 'sys_default_connection_pool', mi

原创 利用Python发送QQ邮件

发送QQ邮件之前，要先开启QQ邮箱IMAP/SMTP服务，并且记住授权码import smtplibfrom email.mime.text import MIMETextmail_sender='你的qq号@qq.com' #发送方邮箱mail_code='你的授权码' #授权码mail_receivers='你的qq号@qq.com' #邮件接收方subject='测试Python发邮件' #主题content='测试Pyt

原创 利用Python将EXCEL，CSV，TSV导入Oracle或者MySQL

虽然可以用Kettle等ETL工具将EXCEL，CSV，TSV等格式的文件导入Oracle或者MySQL，但是还是觉得写点代码心里踏实些import cx_Oracleimport pymysqlimport pandasimport osimport timeos.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'target_db_type='MySQL'

原创 利用Python将数据库查询结果导出为EXCEL，CSV，TSV格式(性能比Kettle高接近1倍)

虽然可以用Kettle等ETL工具将数据库查询结果导出为CSV格式，但是感觉很麻烦，我们还是用Python来做吧import cx_Oracle #导入连接Oracle模块import os #导入os模块import csv #

原创 在Oracle和MySQL中利用SQL查询出今年日历

Oracle写法：select case when rank() over(partition by month order by week) = 1 then month else ' ' end month, max(一) 一, max(二) 二, max(三) 三, max(四) 四, max(五) 五, max(六) 六,

原创 MySQL中的树形查询

树形查询本质就是递归算法(自己调用自己)，Oracle很早就支持了树形查询MySQL中可以使用 WITH RECURSIVE 来实现树形查询比如在Oracle中scott账户下，找出员工编号为7369所有的上级：SQL> select empno, ename, job, mgr, level 2 from emp 3 start with empno = 7369 4 connect by empno = prior mgr; EMPNO ENAME