Fake SQL Tuning, v$session, etc...

随便起了个名字，因为不知道叫啥好。主要是想把最近遇到的一个事情简单记录下，仅此而已。为啥叫Fake SQL Tuning, 自然不是真正地谈SQL优化，因为我也说不出来啥玩意，毕竟SQL优化不是简单的一两句话就可以说出个所以然的。

最近遇到个问题，我负责维护的一个产品的用户说他们有一个process老是跑不完，卡在那里。这个产品前端用PB做，中间部分计算引擎用C++来写，还有一大部分的东东是放在数据库里面，通过PL/SQL来运行。跑一个Process就是在PB界面里面调用C++ DLL提供的接口，然后中间一部分过程又要跑到数据库里面来计算。卡的地方恰恰是在数据库里面执行的那一部分。

1st Round:

最开始，通过客户提供的AWR报表，定位到最time-consuming的SQL，因此就想当然地把这个SQL改改，然后丢给用户让他再试一下。该SQL还是蛮复杂的，而且它是在PL/SQL的运行过程中动态“拼接”而成，不是个相对静态的SQL，为了便于“有针对性地优化”，我把这个SQL显示地“hardcode"出来，而不是根据程序逻辑判断来动态拼接起来。我做的改动其实就是对SQL进行简单地改写，加了些hint, 改变表连接方式，由原来的Nested-Loop改成了Hash Join，(use_hash, blahhhhhh....)。 因为没有跟客户相当的环境，因此也没有怎么测试，而且主观态度上也没有想去解决这个问题，因为手头上还有其他乱七八糟的事情要做。

结果，很快用户给反馈，说现在跑process不是跑不完的问题，而是跑着跑着就跑出了'unable to extend temp segment' 云云错误！！ 我晕，居然把临时表空间给爆掉了！ 我当时也没有细想，就跟那个客户说，你是不是临时表空间太小了，没有autoextend 啊。 给出了一些不痛不痒的建议，比如增大pga的大小啊之类，其实我也参考了Roger Schrag 写的一篇文章What You Can Do When Your Database Runs out of Temp Space。这篇文章提到了1652 事件，后来我开启了这个事件，发现导致临时表空间爆满的SQL就是我改的那条SQL。因为我用hint促使了CBO选择了hash join而不是nested-loop join，因此是会耗掉更多的内存，数据量太大，肯定是multi-pass的，造成临时表空间激增也是可以理解的。

2nd Round:

没有实际的运行环境来进行“盲调”看来是压根就行不通的，因此向客户要了他们数据库的dump文件，并导入等我的虚拟机里面的一个数据库里面。杯具的是，我的虚拟机硬盘30G的空间几乎要爆满了，导完dump之后，也就剩下5G左右剩余空间！

导完之后，我就开始run process, 果然没过多久，process就失败了，查看Log给出的错误信息就是临时表空间无法增长，我一看磁盘使用情况，god..剩下不到20M的空间！！ 临时表空间果然增长速度惊人！ 因为有限的磁盘空间，我就把当前临时表空间给drop掉，重新创建一个临时表空间，大小设置为4G，并且关闭自动增长的属性(autoextend off). 我也把之前改的SQL给改了回来，我要看看究竟process卡在什么地方。于是，我就在launch process之后，打开一个session,用来查询v$session的情况。 发现v$session真是包含了超多信息...

SQL>descv$session

我比较关注其中的几列，像COMMAND, SQL_ID, WAIT_CLASS，因为我要知道process当前的数据库操作是啥(command), 运行的哪条SQL（SQL_ID), 在等待什么资源(WAIT_CLASS). 关于command列的值，可以参见oracle 文档。 比较常见的command 数值有...

3 -- SELECT

47 -- PL/SQL Execute

还有 6 -- Update，因为在跑process的时候，大部分时间v$session.comomand都是显示出要么是3，要么是6。

刚开始发现process运行到一条SQL语句的时候就卡在那里了，等了半天还是没有动静，一直在run啊run的，通过select * from table(dbms_xplan.display_cursor(...))查询了下该SQL的执行计划，再结合等待事件为"db file sequential read", 在通过如下SQL语句定位到改SQL再等待哪个segmenet，

file# 和 block# 可以从v$session中的字段P1和P2获得。最后发现等待资源是一个索引段，因此初步想法是给改SQL加上full hint，让优化器选择全表扫描而不是索引扫描操作。再做了上述改动之后，重新启动Process，但是发现并没有多大效果！！！

后来仔细瞅了下这条SQL语句，发现这个SQL语句有两个子查询基本上是一模一样的，而且该SQL语句的bottleneck（等待的索引段就是这个子查询中涉及到的表）就存在这个子查询中，因此想到通过WITH字句将该SQL进行重写，抽出公共部分放到WITH字句里面。做了上述改动，重新launch其中的一个process,发现还是蛮有效果的，一个process很快就跑完了！ 由于时间很晚，我赶着下班了，所以并没有把所有的process都跑了，就欣欣然就回信给客户，云云怎么一番修改。

3rd Round:

Very frustrated! 第二天回到office, 查看mail, 发现客户说没有任何效果，那个耗时的process依旧没有任何能运行完的征兆！我了个擦擦....我又重新跑了那个process, 发现确实一直卡着...GOD！ 这次我一直监视着v$session中SQL_ID的变化，发现之前的那天SQL确实不像之前那么耗时间，但是另外一条SQL come out in the way! 而且这次不是SELECT语句，而是一条UPDATE语句！这条update语句涉及到一张大表和一个GTT(Global Temporary Table)的关联update操作。由于是涉及到临时表，因此在其他session没法查询到该表的数据信息啥的。仔细看了下这条SQL，发现没有什么可以“简化”的余地，不知道如何下手。等待事件显示是对其中那个非临时表的segment的sequential访问。虽然执行计划显示是全表扫描，但是等待事件并不是'db file scattered file'. 一般理解好像是db file sequential read 是对索引段的访问，但是这里看到对普通表的segment访问也会出现这种情况！

后来，实在不晓得该如何去更改这条update语句，我就突发奇想，反正性能不好，我就索性一不做二不休，把代码改成用PL/SQL来一条一条更新数据！理论上这样做肯定不如用SQL来的快，不用涉及到PL/SQL 与 SQL context的切换操作。不过考虑到我已经黔驴技穷了，不如活马当死马医了。 这样一番更改之后，重新launch process试一下。

惊喜的是，几个process一一通过了，之前一直跑不出的Process现在只需要3小时48分钟！真是诡异呀！

Sumup:

这件事情给我的最大感触就是，理论跟现实还是有差距的！

在SQL“调优”或者是“伪调优” 的时候，要充分运行v$session提供的信息，及时定位到问题所在。 通过dbms_xplan.display_cursor来查看执行计划，找出可能存在的问题。还要“敢想敢做”，不断尝试。