Skip to content

Data access tuning

Oracle Truncate table原理剖析三:偷天换日也要找到你

如果说hellodba发明的truncate的手段恢复为移花接木,那么以下的手段可以称之为偷天换日,在此感谢伟翔同学的信息提供以及建议。

对于Truncate恢复,总结以下步骤:

1.遍历所有数据文件的数据块,寻找offset 1是23的(16进制),23代表段头块,同时还要和相应的Data Object Id相同的,这个需要检索offset 272的位置。(直接sqlplus就可以实现了)
2.找到了段头块,我们就可以通过offset 5192寻找到我们的L2块。
3.找到了L2块,我们就可以通过offset 116找到所有的L1块。找到了L1块,我们就等于找到了数据块。
4.至此,我们就可以开始反向的构造段头块。
5.修改段头块、L2块和第一个L1块的Data Object Id.,同时在修改数据字典。
6.修改段头块的高水位信息。当然这里的高水位块的辨别,一定会是在最后一个Extents上,你可以设置到最后一个Extnets的最后一个块,这个信息的准备性其实无所谓,全表扫描的时候它一定会扫描这个块下面所有的块。
7.修改段头上的Extents信息。

而在这些之前对segment header 结构的熟悉是必须的。

测试truncate恢复过程如下:

首先恢复段头,L1,L2等object_data_id为原object_data_id

BBED> set block 130
        BLOCK#          130

BBED> modify /x ca offset 272
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:  272 to  291           Dba:0x00000000
------------------------------------------------------------------------
 ca270100 00000010 80004001 08000000 00000000

BBED> set block 129
        BLOCK#          129

BBED> modify /x ca offset 104
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 129              Offsets:  104 to  123           Dba:0x00000000
------------------------------------------------------------------------
 ca270100 01000000 00000000 80004001 05000100

BBED> set block 128
        BLOCK#          128

BBED> modify /x ca offset 192
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 128              Offsets:  192 to  211           Dba:0x00000000
------------------------------------------------------------------------
 ca270100 e6151400 00000000 80004001 08000000

BBED> sum apply
Check value for File 0, Block 128:
current = 0x35a9, required = 0x35a9

还需要修改数据字典,否则会报错。

SQL> select * from a1.a;
select * from a1.a
                 *
ERROR at line 1:
ORA-08103: object no longer exists

SQL> update OBJ$ set DATAOBJ#= 75722 where OBJ#= 75722;
1 row updated.

SQL> commit;
Commit complete.

SQL> update TAB$ set DATAOBJ#=75722 where OBJ#=75722;
1 row updated.

SQL> commit;
Commit complete.

SQL> update  SEG$ set HWMINCR=75722 where FILE#=5 and BLOCK#=130; -- 这里的130为段头
1 row updated.

SQL> commit;
Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             511708504 bytes
Database Buffers          322961408 bytes
Redo Buffers                2379776 bytes
Database mounted.
Database opened.

SQL> select * from a1.a;
no rows selected

2.段头高水位信息恢复

修改了这些东西后,我们会发现数据还是没有。我们还需要修改一些信息。一个很重要的信息就是段头上的高水位信息。在Truncate之前,段头上会记载。前面2节内容在解析L1块的时候说明过:
可以看到223后面直接就是225,直接此处跳空,这是因为我们的224是L1位图块,后面紧跟着我们刚刚说的225,226,227,228,229,230,231,232,233。但是问题是,这里看不到后面的234到239?这是因为234到239还是空闲没有格式化过的块,但是它已经被L1锁定了。
所以我们现在的高水位的块是234,一般做全表扫描的查询就会查高水位以下(234)的块。我们来看下我们现在的高水位。

BBED> dump /v offset 48 count 16
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:   48 to   63  Dba:0x00000000
-------------------------------------------------------
 00000000 03000000 08000000 83004001 l ..............@.

BBED> dump /v offset 92 count 16
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:   92 to  107  Dba:0x00000000
-------------------------------------------------------
 00000000 03000000 08000000 83004001 l ..............@.

SQL> select dbms_utility.data_block_address_file(to_number('01400083','xxxxxxxx')) as fileno,dbms_utility.data_block_address_block(to_number('01400083','xxxxxxxx')) as blockno from dual;

    FILENO    BLOCKNO
---------- ----------
         5        131

注意看这里的高水位是83004001,转换成文件号和块号,刚好是文件5块131。而前面的00000000,03000000,则代表着是扩展0,block 3,代表着高水位的位置。刚好是第一个extent的第三个块。128是L1,129是L2,130是段头块。而131则是第一个可以使用的数据块。所以这里记录了extent 0,block为3则代表了文件5的131号块。而08000000则代表了extent的大小,我们每个extents是由8个块组成的。
那在truncate之前,我们的高水位的块是文件5块234,我们从块128开始,每8个块是一个extent,234是第14个extent的第三个块。后面的6个块是没有插入数据的空块。这个在前面我dump 最后一个L1块得知。回顾一下,这里下面的块状态显示11111111 11000000。

BBED> dump /v offset 396 count 50
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 224     Offsets:  396 to  445  Dba:0x00000000
-------------------------------------------------------
 11111111 11000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 0000                                l ..

所以我们这个地方要把高水位从00000000 03000000 08000000 83004001修改成0d000000 02000000 08000000 ea004001。0d000000代表13,表明是第十四个扩展,02000000代表02,表明是第三个块开始,而08000000还是一样代表着这个扩展是8个块的大小,而ea004001则代表着文件5块234。

BBED> modify /x 0d offset 48
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:   48 to   63           Dba:0x00000000
------------------------------------------------------------------------
 0d000000 03000000 08000000 83004001

BBED> modify /x 02 offset 52
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:   52 to   67           Dba:0x00000000
------------------------------------------------------------------------
 02000000 08000000 83004001 00000000

BBED> modify /x ea offset 60
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:   60 to   75           Dba:0x00000000
------------------------------------------------------------------------
 ea004001 00000000 00000000 00000000

BBED> dump /v offset 48 count 16
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:   48 to   63  Dba:0x00000000
-------------------------------------------------------
 0d000000 02000000 08000000 ea004001 l ............
BBED> modify /x 0d offset 92
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:   92 to  107           Dba:0x00000000
------------------------------------------------------------------------
 0d000000 03000000 08000000 83004001

BBED> modify /x 02 offset 96
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:   96 to  111           Dba:0x00000000
------------------------------------------------------------------------
 02000000 08000000 83004001 00000000

BBED>  modify /x ea offset 104
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:  104 to  119           Dba:0x00000000
------------------------------------------------------------------------
 ea004001 00000000 00000000 00000000

BBED> dump /v offset 92 count 16
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:   92 to  107  Dba:0x00000000
-------------------------------------------------------
 0d000000 02000000 08000000 ea004001 l ............
BBED> sum apply
Check value for File 0, Block 130:
current = 0x8c54, required = 0x8c54

修改完成之后,刷新buffer cache,然后重新查询。

SQL> alter system flush buffer_cache;
System altered.
SQL> select count(1) from a1.a;
  COUNT(1)
----------
       142

3.Extents信息恢复
可以看到数据量不对,这是因为我们Truncate之后,在段头上只剩下了一个Extent的信息。而我们的Extents是有14个的,这需要我们在修改如下几个地方。Offset 264代表着我们Extents的数量,,这里修改成0e代表了14个extents。

BBED> modify /x 0e offset 264
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130              Offsets:  264 to  363           Dba:0x00000000
------------------------------------------------------------------------
 0e000000 00000000 ca270100 00000010 80004001 08000000 88004001 08000000
 90004001 08000000 98004001 08000000 a0004001 08000000 a8004001 08000000
 b0004001 08000000 b8004001 08000000 c0004001 08000000 c8004001 08000000
 d0004001

修改完Extents的数量之后,还需要添加对应的Extents Map的信息。因为我们的Extents Map信息也被删除了。从我们的offset 280开始。

BBED> dump /v offset 280 count 100
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:  280 to  379  Dba:0x00000000
-------------------------------------------------------
 80004001 08000000 00000000 00000000 l ..@.............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000                            l ....

80004001 08000000这个Extents是我们第一个Extents。代表了文件5的块128。我们可以依次类推下列的信息出来。而08000000则代表有8个数据块构成一个Extents。

modify /x 8800 offset 288  modify /x 4001 offset 290  modify /x 08 offset 292
modify /x 9000 offset 296  modify /x 4001 offset 298  modify /x 08 offset 300
modify /x 9800 offset 304  modify /x 4001 offset 306  modify /x 08 offset 308
modify /x a000 offset 312  modify /x 4001 offset 314  modify /x 08 offset 316
modify /x a800 offset 320  modify /x 4001 offset 322  modify /x 08 offset 324
modify /x b000 offset 328  modify /x 4001 offset 330  modify /x 08 offset 332
modify /x b800 offset 336  modify /x 4001 offset 338  modify /x 08 offset 340
modify /x c000 offset 344  modify /x 4001 offset 346  modify /x 08 offset 348
modify /x c800 offset 352  modify /x 4001 offset 354  modify /x 08 offset 356
modify /x d000 offset 360  modify /x 4001 offset 362  modify /x 08 offset 364
modify /x d800 offset 368  modify /x 4001 offset 370  modify /x 08 offset 372
modify /x e000 offset 376  modify /x 4001 offset 378  modify /x 08 offset 380
modify /x e800 offset 384  modify /x 4001 offset 386  modify /x 08 offset 388

BBED> dump /v offset 280 count 128
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:  280 to  407  Dba:0x00000000
-------------------------------------------------------
 80004001 08000000 88004001 08000000 l ..@.......@.....
 90004001 08000000 98004001 08000000 l ..@.......@.....
 a0004001 08000000 a8004001 08000000 l ........
 b0004001 08000000 b8004001 08000000 l ........
 c0004001 08000000 c8004001 08000000 l ........
 d0004001 08000000 d8004001 08000000 l ........
 e0004001 08000000 e8004001 08000000 l ........
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

当然这些Extents Map修改完成之后,我们还需要在添加Auxillary Map。从Offset 2736开始。

BBED> dump /v offset 2736 count 200
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets: 2736 to 2935  Dba:0x00000000
-------------------------------------------------------
80004001 83004001 00000000 00000000 l ..@...@.........
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ...............

这里的80004001 83004001,代表这Extents中,L1块的地址和Data Block的地址,可以看到在Extents 1上面,它的L1块是128,而数据块是从131开始的。因为我们的129是L2,130是段头。所以下面我们构造其他的数据的时候,我们也要遵循这个规律。我们的Extents 2,它的L1还是128块,但是它的数据块却是从136开始的。而Extents 3,它的L1就是第二个L1块,也就是144,而它的数据块的开始则是从145开始的。依次类推下去。结果如下:

modify /x 8000 offset 2744  modify /x 4001 offset 2746
modify /x 8800 offset 2748  modify /x 4001 offset 2750
modify /x 9000 offset 2752  modify /x 9000 offset 2754
modify /x 9100 offset 2756  modify /x 4001 offset 2758
modify /x 9000 offset 2760  modify /x 4001 offset 2762
modify /x 9800 offset 2764  modify /x 4001 offset 2766
modify /x a000 offset 2768  modify /x 4001 offset 2770
modify /x a100 offset 2772  modify /x 4001 offset 2774
modify /x a000 offset 2776  modify /x 4001 offset 2778
modify /x a800 offset 2780  modify /x 4001 offset 2782
modify /x b000 offset 2784  modify /x 4001 offset 2786
modify /x b100 offset 2788  modify /x 4001 offset 2790
modify /x b000 offset 2792  modify /x 4001 offset 2794
modify /x b800 offset 2796  modify /x 4001 offset 2798
modify /x c000 offset 2800  modify /x 4001 offset 2802
modify /x c100 offset 2804  modify /x 4001 offset 2806
modify /x c000 offset 2808  modify /x 4001 offset 2810
modify /x c800 offset 2812  modify /x 4001 offset 2814
modify /x d000 offset 2816  modify /x 4001 offset 2818
modify /x d100 offset 2820  modify /x 4001 offset 2822
modify /x d000 offset 2824  modify /x 4001 offset 2826
modify /x d800 offset 2828  modify /x 4001 offset 2830
modify /x e000 offset 2832  modify /x 4001 offset 2834
modify /x e100 offset 2836  modify /x 4001 offset 2838
modify /x e000 offset 2840  modify /x 4001 offset 2842
modify /x e800 offset 2844  modify /x 4001 offset 2846

BBED> dump /v offset 2736 count 128
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets: 2736 to 2863  Dba:0x00000000
-------------------------------------------------------
 80004001 83004001 80004001 88004001 l ..@...@...@...@.
 90009000 91004001 90004001 98004001 l ......@...@...@.
 a0004001 a1004001 a0004001 a8004001 l
 b0004001 b1004001 b0004001 b8004001 l
 c0004001 c1004001 c0004001 c8004001 l
 d0004001 d1004001 d0004001 d8004001 l
 e0004001 e1004001 e0004001 e8004001 l
 00000000 00000000 00000000 00000000 l ................

修改完成这些后,我们就能够查到我们全部的数据了。

SQL> alter system flush buffer_cache;
System altered.

SQL> select count(1) from a1.a;
  COUNT(1)
----------
      2775

此时切勿执行一些其他的操作,应该尽快的使用CTAS的方式将这个表进行备份或者是导出。因为段头块L2和L1的信息还一些是没有修改的。

SQL> create table a2.a as select * from a1.a;
Table created.

SQL> drop table a1.a;
Table dropped

至此,Truncate恢复完成。仔细的研究才会发现,其实最重要的是摸清楚整个段的构造情况,只要你对整个段的构造情况,了若指掌,基本上恢复是很简单的。

Oracle Truncate table原理剖析二:从truncate结果看待恢复

总结一下前面,首先是段头块,它指向了L2块,L2块指向了L1块,而L1块则指向了我们真实的物理数据块。那么做了truncate操作,它到底做了什么鬼?这里需要测试一下,查看如下测试引入:

SQL> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where OBJECT_NAME='A';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
A                                   75722          75722

我们先来看一下对象,当我们创建对象的之后,OBJECT_ID和data_object_id都会是一样的,但是当我们发生truncate之后,我们的object_id不会变,而data_object_id则会变掉。

SQL> truncate table a;
Table truncated.

SQL> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where OBJECT_NAME='A';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
A                                   75722          75727

这里可以看到Truncate表之后,data_object_id从75722变成了75727。分别看一下段头块,L2位图块,L1位图块,数据块,这个ID是否有变化。分别从检查块130,129,224,225

BBED> set block 130
        BLOCK#          130

BBED> dump /v offset 272 count 20
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 130     Offsets:  272 to  291  Dba:0x00000000
-------------------------------------------------------
 cf270100 00000010 80004001 08000000 l ......@.....
 00000000                            l .....

BBED> set block 129
BLOCK#          129

BBED> dump /v offset 104 count 20
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 129     Offsets:  104 to  123  Dba:0x00000000
-------------------------------------------------------
 cf270100 01000000 00000000 80004001 l ..........@.
 05000100                            l ....

BBED> set block 128
        BLOCK#          128

BBED>  dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 128     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 cf270100 e6151400 0000              l ..

BBED> set block 144
        BLOCK#          144

BBED> dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 144     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 ca270100 de300f00 0000              l ..

BBED> set block 160
        BLOCK#          160

BBED> dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 160     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 ca270100 de300f00 0000              l ..

BBED> set block 176
        BLOCK#          176

BBED> dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 176     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 ca270100 de300f00 0000              l ..

BBED> set block 192
        BLOCK#          192

BBED> dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 192     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 ca270100 de300f00 0000              l ..

 <16 bytes per line>

BBED> set block 208
        BLOCK#          208

BBED> dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 208     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 ca270100 de300f00 0000              l ..

 <16 bytes per line>

BBED> set block 224
        BLOCK#          224

BBED> dump /v offset 192 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 224     Offsets:  192 to  201  Dba:0x00000000
-------------------------------------------------------
 ca270100 de300f00 0000              l ..

BBED> set block 225
BLOCK#          225

BBED> dump /v offset 24 count 10
 File: /oracle/app/oracle/oradata/ora11/a1.dbf (0)
 Block: 225     Offsets:   24 to   33  Dba:0x00000000
-------------------------------------------------------
 ca270100 90121400 0000              l ......

通过对各个块的dump,发现段头和L2位图块的data_obj_id已经发生了改变,从ca270100变成了cf270100,而只有第一个L1发生了变化,数据块则没有发生改变。其实到了这里,MDATA如何快速恢复truncate数据的原理非常清楚了.

那么如果从手工修复的角度来看的话,要处理的东西就比较多了,这方面可以参考第三节的内容。
也可以参考hellodba的《移花接木————利用Oracle表扫描机制恢复被Truncate的数据》

SPM固定执行计划以及踩bug事一件

原有2个sql语句有多个表连接,执行计划一直在走错误的执行计划.表级统计信息以及索引规划都已经是最新(这里统计信息有狗血不做描述),只是SQL里还有六个绑定变量以及字段的柱状图影响了执行计划,在这个优化里没有删除柱状图和对绑定变量的影响进行处理(星形连接不建议使用绑定变量),现场环境微妙最终选择通过sql profile以及spm对这2个sql的执行计划进行固定处理.先用sqlprofile固定后让sql重新解析后发现未能生效,逐用spm的方式固定.

这里以其中一个sql_id为bwwnw7r1gzhdf的语句为例,这是收集到对应1个小时内的sqlrpt,其中plan_hash_value为711942702执行计划为正确的执行计划,从报告中可以看到这个sql选择了错误的执行计划,并且从中也可以看到sql有多个执行计划.当中执行计划正确与否的判断方式就不做描述.
 

SQL ID: bwwnw7r1gzhdf

# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
1 3052678239 13,512,877 10 25060 25060
2 3392573872 0 0 25060 25060
3 4134955434 0 0 25060 25060
4 1564064893 0 0 25060 25060
5 2504448979 0 0 25060 25060
6 147966509 0 0 25060 25060
7 711942702 0 0 25060 25060

 
通过coe_xfr_sql_profile.sql脚本对bwwnw7r1gzhdf的sql进行固定711942702,生成sql profile的名字为coe_bwwnw7r1gzhdf_711942702.
(该部分可以参考
1.Using Sqltxplain to create a ‘SQL Profile’ to consistently reproduce a good plan (文档 ID 1487302.1)
2.Automatic SQL Tuning and SQL Profiles (文档 ID 271196.1)
3.Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (文档 ID 1955195.1))

让sql从新解析后从v$sql视图中的sql profile字段没有看到生效的迹象,原因是在脚本coe_xfr_sql_profile.sql中对创建的sqlprofile默认的生效是false的,所以创建出来的profile不会失效,监控中的执行计划未变(现场我对此处的profile drop).

 

SQL>  select name,created,status from dba_sql_profiles;

NAME                           CREATED                        STATUS
------------------------------ ------------------------------ --------
coe_bwwnw7r1gzhdf_711942702    26-JUN-15 02.09.30.000000 PM   ENABLED
coe_g87an0j5djjpm_334801256    26-JUN-15 11.30.25.000000 AM   ENABLED

SQL>  select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='bwwnw7r1gzhdf' and sql_profile is not null;

no rows

SQL>  select sql_profile,EXECUTIONS,PLAN_HASH_VALUE,parse_calls,ELAPSED_TIME/1000000,
ELAPSED_TIME/1000000/EXECUTIONS,LAST_LOAD_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id='bwwnw7r1gzhdf' order by LAST_LOAD_TIME desc;
...

逐对profile进行disable并drop

=====disable profile==============
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =&gt; 'coe_bwwnw7r1gzhdf_711942702',
attribute_name =&gt; 'STATUS',
value =&gt; 'DISABLED');
END;
/

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =&gt; 'coe_g87an0j5djjpm_334801256',
attribute_name =&gt; 'STATUS',
value =&gt; 'ENABLED');
END;
/

=====drop profile=================
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_bwwnw7r1gzhdf_711942702');
end;
/

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_g87an0j5djjpm_334801256');
end;
/

由于已经存在了正确的执行计划,所以通过DBMS_SPM直接创建baseline,并通过DBMS_SPM包对该sql的baseline的enable,accept,fixed三个属性指定为yes.

该部分可以参考:
Plan Stability Features (Including SQL Plan Management (SPM)) (文档 ID 1359841.1)

为sql创建baseline

variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702) ;

验证该baseline已经生成

SQL> set linesize 200
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
From Dba_Sql_Plan_Baselines
Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;


SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES NO              69 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2

为sqlbaseline的fixed属性改为yes

variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702,fixed => 'yes') ;
验证修改完成
SQL> set linesize 200
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
  2  From Dba_Sql_Plan_Baselines
  3  Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES YES            574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2

最终验证生效

SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
  2  From Dba_Sql_Plan_Baselines
  3  Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES YES            574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2
SQL_2e1c8025edb165b3           SQL_PLAN_2w7404rqv2tdm56eb6fa8 MANUAL-LOAD    YES YES YES            311 SELECT 1 " ", D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(MAX(H.EVENTTIME), 'YYY

SPM主要和2个参数有关,一个是baseline生效(optimizer_user_sql_plan_baselines,前提是accept属性要为yes,否则会产生干扰),一个是捕获sql语句生成baseline(optimizer_cature_sql_plan_baselines).在数据库中我一般不开启捕获,但是开启baseline生效.
开启的语法:

alter system set optimizer_user_sql_plan_baselines=true scope=both;
alter system set optimizer_cature_sql_plan_baselines=true scope=both;

关闭的语法:

alter system set optimizer_user_sql_plan_baselines=false scope=both;
alter system set optimizer_cature_sql_plan_baselines=false scope=both;

开启捕获的情况在一些11g版本中会触发该bug
Bug 9910484 – SQL Plan Management Capture uses excessive space in SYSAUX (文档 ID 9910484.8)
此bug会造成sysaux的表空间暴增,主要为sqllob$data,我遇见的是在一天内从2g增长到4g.关闭了捕获后,该现象消失.
删除掉不必要的baseline后可以通过shrink的方式回收sysaux的空间,具体可以参考
Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (文档 ID 1499542.1)

Cardinality Feedback与_optimizer_use_feedback的使用建议

该参数与Cardinality Feedback特性有关,优化器可以估算基数不正确的原因有很多,如缺少的统计信息,不准确的统计数据,或复杂的谓词,基数统计反馈有助于优化器生成更合理的执行计划.对于此特性我不作科普了,比较详细的资料可以参考以下文档:

1.Tuning-by-Cardinality-Feedback.pdf
2.Statistics (Cardinality) Feedback – Frequently Asked Questions (文档 ID 1344937.1)

该特性其实从10g版本就已经开始引入,只是它的应用受限于optimizer_dynamic_sampling参数,在11g以前的10g版本中,只有在optimizer_dynamic_sampling=4以上的情况下才会被用到该特性.这个在10g版本中可以通过HINT语法
/*+ dynamic_sampling (customer 4) */或者在session/system中将optimizer_dynamic_sampling设置为4进行测试.

判断SQL是否使用了CF可以通过sql的执行计划中的note提示或者视图V$SQL_SHARED_CURSOR的USE_FEEDBACK_STATS字段进行.

执行计划中的note提示

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - filter(ROWNUM>0)
 8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
- Cardinality Feedback used for this statement

V$SQL_SHARED_CURSOR


column use_feedback_stats format a18
column sql_text format a80

select c.child_number, c.use_feedback_stats , s.sql_text from v$sql_shared_cursor c,v$sql s
where s.sql_id=c.sql_id and c.sql_id = 'an4zdfz0h7513'
and s.child_number= c.child_number;

CHILD_NUMBER USE_FEEDBACK_STATS SQL_TEXT
------------ ------------------ ------------------------------------------------------------
           0 Y                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t
           1 N                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t

对使用了基数统计回馈特性的sql,可以在10053的trace文件中发现sql被转化成带了hint opt_estimate的语法.
CF特性的功能是非常不错的,oracle在使用了cf反馈评估后更准确的统计数据后会根据此数据生成对于的plan,在再次的执行中还会持续评估统计信息是否准确,以此重复解析,参考如下:

When a cursor is found to be a candidate for Statistics Feedback it will be hard parsed again using the new estimates. The child cursor will be marked as not being shareable and USE_FEEDBACK_STATS set to ‘Y’ in V$SQL_SHARED_CURSOR.
Note: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

At the next execution, as a result of the cursor being marked as not shareable, a hard parse will again be performed and a new child created with the optimizer having used the new estimates for creating an optimizer plan.

但是由于cf的评估结果数据只存在内存中(重启之后就需要重新来过),在session之间是不可共用的,并且由于在11g中存在了过多的bug,常见的问题就是在第二次执行sql时候性能下降较多.因此我在11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭.

部分bug list

feedback bug

关闭CF特性的方法即是将_optimizer_use_feedback设置为false.

alter session set "_optimizer_use_feedback" = false;
或者
alter system set "_optimizer_use_feedback" = false;

skip_unusable_indexes参数使用建议

​SKIP_UNUSABLE_INDEXES的使用与索引失效是相关的,该参数10g开始引入,11g默认为TRUE.
当为TRUE时候,如果数据库中存在usable状态的索引,则会自动忽略该索引生成新的执行计划(不走该索引,也不提示该索引的异常);当为False时候,则会报错.我所运维的数据库在一些关键系统中,会将此参数设成False,让系统及时发现索引的异常以便及时去介入修复.
环境各有所异,设置值也可依据实际情况设置.如果sql使用了hint或者涉及到唯一索引的对应DML,该参数会失效.

该参数的一些使用场景可以参考如下的测试:

创建测试表和索引

SQL> conn test/test
已连接。
SQL> drop table a;
表已删除。
SQL> create table a(id number);
表已创建。
SQL> create unique index idx_a_id on a(id);
索引已创建。
SQL> declare
  2  begin
  3  for a in 1..1000 loop
  4  insert into a(id) values(a);
  5  end loop;
  6  end;
  7  /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
skip_unusable_indexes                boolean     TRUE
SQL> select * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_A_ID |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=1)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
        124  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

修改skip_unusable_indexes为false

SQL> alter system set skip_unusable_indexes=false scope=memory;
系统已更改。
将索引修改为不可用
SQL> alter index idx_a_id unusable;
索引已更改。
出现错误提示索引不可用
SQL> select * from a where id=1;
select * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态

将skip_unusable_indexes修改为true

SQL> alter system set skip_unusable_indexes=true scope=memory;
系统已更改。

对于查询操作此时该sql能够正常运行,但是此时进行的是全表扫描

SQL> select * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    52 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| A    |     4 |    52 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用hint强制使用索引,此时会提示索引无效

SQL> select /*+index(a)*/ * from a where id=1;
select /*+index(a)*/ * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态
--插入操作会出错
SQL> insert into a values(1002);
insert into a values(1002)
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态
SQL> delete from a where id=1;
delete from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态

SQL>

解决方法,重建索引

SQL> alter index test.idx_a_id rebuild;
索引已更改。
SQL> select /*+index(a)*/ * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_A_ID |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=1)

统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> drop index test.idx_a_id;
索引已删除。
SQL> create index test.idx_a_id on a(id);
索引已创建。
SQL> alter index test.idx_a_id unusable;
索引已更改。
SQL> insert into a values(1002);
已创建 1 行。
SQL> commit;

测试证明SKIP_UNUSABLE_INDEXES对于使用hint强制使用索引的语句和唯一索引的插入、删除语句却不能生效。

该测试摘自互联网,同时做了一些修改.

Oracle full table scan 的跟踪监视

最近一个客户的数据库反应不定时的性能问题,经过跟踪发现是一系列的全表扫相关SQL,其中还碰到了比较诡异的全表扫都是顺序读的情况.和周亮以前也讨论过此类情况只是当时这方面的事情并没有太多的眉目,这次跟踪发现了是和undo一致性读有关,还存在大量的row chains.当然这里不讨论这个case,这个case可能会在新书中描述.这个文章不描述具体的原理,只是作为一个对数据库全表扫相关信息收集诊断建议.

在跟踪某一特定的事件时候,有时候没有监控系统或者其他相关捕获信息的手段,则诊断起来比较耗时间,比如跟踪系统中全表扫的情况,默认情况下是只能获取当时时刻具体的情况,这里介绍通过AWR记录中来获取相关的信息,由于AWR从v$sql里面获取的sql是有条件限制的,因此该方法并不能补全数据库中所有的全表扫相关sql(其他情况也是),因为awr收集sql的情况是以statistics_level 的设置为依赖,typical(top 30)或者all(top 100)收集的top sql信息不同.

 

1.周期的全表扫信息

col c1 heading "Day|Hour" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
COUNT(1) c2
FROM dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
WHERE p.object_owner <> 'SYS'
AND p.object_owner = o.owner
AND p.object_name = o.segment_name
AND o.blocks > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Full-table scans Per Snapshot Period
Begin
 Interval FTS
 time Count
 -------------------- --------
 04-10-18 11 4
 04-10-21 17 1
 04-10-21 23 2
 04-10-22 15 2
 04-10-22 16 2
 04-10-22 23 2
 04-10-24 00 2

2.每小时的全表扫信息

col c1 heading "Day|Hour" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Table Full-table scans
Averages per Hour

Day                       FTS
Hour                    Count
-------------------- --------
00                          4
10                          2
11                          4
12                         23
13                         16
14                          6
15                         17
16                         10
17                         17
18                         21
19                          1
23                          6

3.基于星期的全表扫信息

col c1 heading "Week|Day" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'day') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'day')
ORDER BY 1;

查询结果类似如下:

Week                      FTS
Day                     Count
-------------------- --------
sunday                      2
monday                     19
tuesday                    31
wednesday                  34
thursday                   27
friday                     15
Saturday                    2

以上脚本作者为Donald K. Burleson.
我对此脚本做了些变更,除了生成html格式外额外加入索引类的信息,变更的脚本未在blog上发布.

索引块上递归事务专用的itl slot争用的识别判断

承接《Oracle10g版本后enq: TX – allocate ITL entry等待事件的根源以及解决思路》

由于itl争用主要是initrans不足(10g后今本不受此影响maxtrans失效),块空间不足原因引起,但是特殊的情况下的索引递归事务引起的itl争用的识别也是需要掌握的技术,虽然大量的递归情况较少见,但如何区分相比前面的2种情况就相对复杂点。主要的思路是根据索引块的itl信息来识别,因为上一篇中讲到在索引的枝节点上,有且只有一个ITL slot,它是用于当发生节点分裂的递归事务(Recursive Transaction)。在叶子节点上,第一条ITL Slot也是用于分裂的递归事务的。只要根据相关索引块的负责递归事务的itl事务槽的使用情况就可以判断争用的情况。

思路如下:
1.找出被阻塞的事务
2.根据阻塞的事务找到相关的回滚块以及相关事务起始回滚编号
3.根据回滚块的内容识别相关的索引块
4.dump出相关的索引块识别对应的itl争用情况

例子:

1.测试阻塞

session 1 更新61080块的100行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=100;

1 row updated.

commit;

session 2 更新61080块的200行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=200;

1 row updated.

commit;

session 3 更新61080块的300行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=300;

1 row updated.

session 4 更新61080块的400行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=400;

1 row updated.

session 5 更新61080块的500行hang住

2.找出被阻塞的事务以及相关回滚段信息

确认itl阻塞:

SQL> select s.sid, s.event, s.row_wait_obj#
  2  from v$session s where s.sid=151;

       SID EVENT                          ROW_WAIT_OBJ#
---------- ------------------------------ -------------
       148 enq: TX - allocate ITL entry              -1

确认对应的阻塞事务的回滚相关信息,可以发现阻塞事务对应的回滚在2号数据文件的2109数据块中,起始的回滚记录是0x2e(46的16进制)

SQL> select l.sid req_session, s.sid lock_session, l.lmode, l.request, t.xidusn, t.xidslot, t.start_ubafil, t.start_ubablk, t.start_ubarec
  2  from v$lock l, v$transaction t, v$session s
  3  where l.type = 'TX'
  4  and trunc(id1/power(2,16)) = t.xidusn
  5  and l.id2 = t.xidsqn
  6  and id1 - power(2,16)*trunc(id1/power(2,16)) = t.xidslot
  7  and t.addr = s.taddr
  8  and l.request = 4;

REQ_SESSION LOCK_SESSION      LMODE    REQUEST     XIDUSN    XIDSLOT START_UBAFIL START_UBABLK START_UBAREC
----------- ------------ ---------- ---------- ---------- ---------- ------------ ------------ ------------
        148          159          0          4          5         26            2         2109           46

dump出2号数据文件的2109号数据块

SQL> alter system dump datafile 2 block 2109;

System altered.

确认相关对象的object_id

SQL> select object_name,object_id,data_object_id from dba_objects where object_name in ('LUDA','IDX_TEST') and owner='SYS';

OBJECT_NAME                               OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ---------- --------------
IDX_TEST                                      51980          51980
LUDA                                          51978          51978

分析2号数据文件的2109号数据块dump文件,从Rec #0x2e部分开始到结束只有0x2e此条与对象号51980,51978相关回滚记录

*-----------------------------
* Rec #0x2e  slt: 0x1a  objn: 51978(0x0000cb0a)  objd: 51978  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x0080083d.014b.2d ctl max scn: 0x0000.000bb362 prv tx scn: 0x0000.000bb37b
txn start scn: scn: 0x0000.000bcdc1 logon user: 0
 prev brb: 8388917 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0002.010.0000013b uba: 0x00800051.0100.3c
                      flg: C---    lkc:  0     scn: 0x0000.000bcd90
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040ee98  hdba: 0x0040ee91
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 400(0x190) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
Vector content:
col  0: [ 3]  c2 31 06

End dump data blocks tsn: 1 file#: 2 minblk 2109 maxblk 2109

从bdba地址分析可以获得当前更新对象对luda,前面更新提交的1条语句的部分就是luda的61080号块。

SQL> select dbms_utility.data_block_address_file(TO_NUMBER('0040ee98', 'XXXXXXXX')) file_id,
  2  dbms_utility.data_block_address_block(TO_NUMBER('0040ee98', 'XXXXXXXX')) block_id from dual;

   FILE_ID   BLOCK_ID
---------- ----------
         1      61080

在回滚信息中没有发现索引块类型,事务对象只有objno为51978的luda表,类似的索引对象也是用此方法分析。

Oracle10g版本后enq: TX – allocate ITL entry等待事件的根源以及解决思路

客户昨日一套核心系统有进程挂住,top等待事件为enq: TX – allocate ITL entry,判断为itl争用的原因,这方面以前做了不少测试并没有整理,这里顺便做个整理作为诊断手段用。该内容主要分为主要的三个部分:

一.itl的解释以及原理
二.数据块上的initrans不能扩展分配slot导致的itl争用测试
三.索引块上递归事务专用的itl slot争用的识别判断

 

一.ITL原理解释

ITL(Interested Transaction List)是Oracle数据块内部的一个组成部分,用来记录该块所有发生的事务,一个itl可以看作是一个记录,在一个时间,可以记录一个事务(包括提交或者未提交事务)。当然,如果这个事务已经提交,那么这个itl的位置就可以被反复使用了,因为itl类似记录,所以,有的时候也叫itl槽位。Oracle的每个数据块中都有一个或者多个事务槽,每一个对数据块的并发访问事务都会占用一个事务槽。 表和索引的事务槽ini_trans是1、max_trans是255,在oracle10g中,不能修改max_trans这个参数,因为oracle10g忽略了这个参数。如果一个事务一直没有提交,那么,这个事务将一直占用一个itl槽位,itl里面记录了事务信息,回滚段的嵌入口,事务类型等等。如果这个事务已经提交,那么,itl槽位中还保存的有这个事务提交时候的SCN号。如dump一个块,就可以看到itl信息:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.002.0000158e  0x0080104d.00a1.6e  --U-  734  fsc 0x0000.6c9deff0
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

如果在并发量特别大的系统中,最好分配足够的itl个数(10g之前的版本),其实它并浪费不了太多的空间,或者,设置足够的pctfree,保证itl能扩展,但是pctfree有可能是被行数据给消耗掉的,如update可能一下占满块空间,所以,也有可能导致块内部的空间不够而导致itl等待,所以在通常情况下,10g版本后引起itl等待的原因往往是因为块的空间不足导致,并不是tran事务槽数量不足,在正常情况下2k的数据块最多可以拥有41个itl,4k数据块最多拥有83,8k最多用友169个itl(以itl 24byte为单位)。INITRANS不足的问题不会出现在索引数据块上,当发现没有足够空间分配ITL slot时,无论是枝点块还是叶子块,数据块会发生分裂(Index Block Split)。

有一种特殊情况也会引起ITL的等待,就是在索引上的递归事务itl争用,这种情况比较特殊。在索引的枝节点上,有且只有一个ITL slot,它是用于当发生节点分裂的递归事务(Recursive Transaction)。在叶子节点上,第一条ITL Slot也是用于分裂的递归事务的。在一个用户事务中,如果发生多次分裂,每一次分裂都是由一个单独的递归事务控制的,如果下层节点分裂导致其父节点分裂,它们的分裂则由同一个递归事务控制。当2个事务同时需要分裂一个枝节点或者叶子节点时,或者枝节点下的2个子节点分别被2个事务分裂,就会造成这种ITL等待。

 

2.数据块上的initrans不能扩展分配slot导致的itl争用测试

我们做个测试关于如果数据块没有空间留给itl slot扩展时候的测试,创建表luda,指定pctfree为0,同时指定initrans为1然后填满相关数据块,再对块满的数据进行更新模拟出itl的等待。
创建表luda,并指定pctfree为0,initrans为1

SQL> create table luda(a int) pctfree 0 initrans 1;

Table created.

 

插入大量数据

SQL> begin
2 for i in 1..20000 loop
3 insert into luda values(i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit ;

Commit complete.

 

SQL> select f,b,count(*) from (select dbms_rowid.rowid_relative_fno(rowid) f,dbms_rowid.rowid_block_number(rowid) b
2 from luda) group by f,b order by 3;

F B COUNT(*)
---------- ---------- ----------
1 61101 200
1 61093 734
1 61089 734
1 61095 734
1 61085 734
1 61099 734
1 61074 734
1 61077 734
1 61080 734
1 61092 734
1 61100 734
1 61083 734
1 61091 734
1 61097 734
1 61098 734
1 61075 734
1 61076 734
1 61078 734
1 61081 734
1 61084 734
1 61087 734
1 61096 734
1 61079 734
1 61094 734
1 61088 734
1 61090 734
1 61082 734
1 61086 734

 

插入20018条数据后可以发现该表有26个数据块,填满了除了61101块意外的其他数据块。
接着导出已经填满的数据块61074.

SQL> alter system dump datafile 1 block 61074;

System altered.

 

Block header dump: 0x0040ee92
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bb6a1 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.020.0000013b 0x0080078c.013c.3a --U- 734 fsc 0x0000.000bb765
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--可以发现initrans为1的情况下默认是有2个事务槽,itc=2
data_block_dump,data header at 0xd7fe45c
===============
tsiz: 0x1fa0
hsiz: 0x5ce
pbl: 0x0d7fe45c
bdba: 0x0040ee92
76543210
flag=--------
ntab=1
nrow=734
frre=-1
fsbo=0x5ce
fseo=0xbf8

avsp=0x4
tosp=0x4
0xe:pti[0] nrow=734 offs=0

 

块满的情况测试slot的分配,根据前面的查询结果我们知道单个块的存储行数为734行,也可以通过dump中的nrow=734得知,所以我们在这部测试中依次更新第100,200,300行的数据。

session 1 更新第100行的数据:

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61074
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=100;

1 row updated.

 

session 2更新第200行的数据:

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61074
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=200;

1 row updated.

 

session 3更新第300行的数据,session3的sid为158,并且在执行过程中session 3 hang住:

SQL> select sid from v$mystat where rownum=1;

SID
----------
158

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61074
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=300;
--此时进程hang住
 

alter system dump datafile 1 block 61074;

 

Block header dump: 0x0040ee92
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bb97e itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.014.00000159 0x008006bb.01f1.12 ---- 1 fsc 0x0000.00000000
0x02 0x0005.00e.0000013c 0x0080083c.014b.20 ---- 1 fsc 0x0000.00000000
--通过此时的dump我们也可以发现原先为被占用的2个事务槽已经被占用而且事务未提交。
data_block_dump,data header at 0xd77645c
===============
tsiz: 0x1fa0
hsiz: 0x5ce
pbl: 0x0d77645c
bdba: 0x0040ee92
76543210
flag=--------
ntab=1
nrow=734
frre=-1
fsbo=0x5ce
fseo=0xbf8
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=734 offs=0

 

 
--查询158进程的等待事件为itl的相关等待事件enq: TX - allocate ITL entry
SQL> select sid,event from v$session where sid=158;

SID EVENT
---------- ----------------------------------------------------------------
158 enq: TX - allocate ITL entry

从以上验证了空间不足的情况下会导致itl无法分配引起enq: TX – allocate ITL entry等待事件的产生。

接下来测试块不满的情况,在表luda中目前有数据的块为26个,其中块号为61101的块只有200条数据,只占用该块30%的空间,为了测试需要对61101号块采用4个session分别对第10,20,30,40行进行更新:

session 1:

 

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=10;

1 row updated.

 

 

session2:

 

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=20;

1 row updated.

 

session3:

 

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=30;

1 row updated.

 

session4:

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=40;

1 row updated.

 

以上4个session都没有遇到阻塞,导出61101号块可以发现该块有4个itl slot,自动扩展了2个slot,验证了在空间足够的情况下itl slot会自动扩展。

SQL> alter system dump datafile 1 block 61101;

System altered.

 

--

Block header dump: 0x0040eead
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bc003 itc: 4 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.014.00000159 0x008006bb.01f1.13 ---- 1 fsc 0x0000.00000000
0x02 0x0009.016.0000013b 0x0080075c.0143.37 ---- 1 fsc 0x0000.00000000
0x03 0x0005.00e.0000013c 0x0080083c.014b.21 ---- 1 fsc 0x0000.00000000
0x04 0x0002.026.00000139 0x00800130.00fc.09 ---- 1 fsc 0x0000.00000000
--itc=4,在61101块上存在4条未提交的事务,分别是我们刚才执行的sesson1-4.

data_block_dump,data header at 0xe17048c
===============
tsiz: 0x1f70
hsiz: 0x1a2
pbl: 0x0e17048c
bdba: 0x0040eead
76543210
flag=--------
ntab=1
nrow=200
frre=-1
fsbo=0x1a2
fseo=0x1957
avsp=0x16c6
tosp=0x16c6
0xe:pti[0] nrow=200 offs=0

 

以上测试说明了在Oracle10g以来,itl的争用事件产生在数据块上主要是受块空间的影响,如果大量事务在相关block上执行操作但是当块无法满足分配事务槽,则会产生itl相关争用。
下一节主要是区分是索引块还是表数据块的争用. http://www.ludatou.com/?p=1920

在Oracle监控中如何捕获执行计划变更的语句

在通常的数据库运维中,经常担心数据库一些重要的SQL的性能突然变差,导致整个数据库性能糟糕响应缓慢,甚至让数据库瘫痪的情况时有出现,往往这个时候要在事后通过awrsqrpt.sql脚本从top sql中去抓取分析执行变化的SQL语句,今天脑袋一闪想有没有办法有脚本可以自己去对比呢?回家就开始敲命令测试,测试的平台为11gr1版本,通过dba_hist_sqlstats数据字典中抓去sql id数据进行对比,通过对比获取相关的SQL_ID,以及此SQL的最优执行计划是什么。通过该sql可以设定一个固定的执行时间,通过分别查出来的数据进行监控,对执行计划产生误差的语句及时的通过SQL_PROFILE或者SPM(11g)调整成最优执行计划。

具体语句如下:

spool sql_have_logs_plan.txt
set lines 220 pages 9999 trimspool on
set numformat 999,999,999
column plan_hash_value format 99999999999999
column min_snap format 999999
column max_snap format 999999
column min_avg_ela format 999,999,999,999,999
column avg_ela format 999,999,999,999,999
column ela_gain format 999,999,999,999,999
select sql_id,
       min(min_snap_id) min_snap,
       max(max_snap_id) max_snap,
       max(decode(rw_num,1,plan_hash_value)) plan_hash_value,
       max(decode(rw_num,1,avg_ela)) min_avg_ela,
       avg(avg_ela) avg_ela,
       avg(avg_ela) - max(decode(rw_num,1,avg_ela)) ela_gain,
       -- max(decode(rw_num,1,avg_buffer_gets)) min_avg_buf_gets,
       -- avg(avg_buffer_gets) avg_buf_gets,
       max(decode(rw_num,1,sum_exec))-1 min_exec,
       avg(sum_exec)-1 avg_exec
from (
  select sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec,
         row_number() over (partition by sql_id order by avg_ela) rw_num , min_snap_id, max_snap_id
  from
  (
    select sql_id, plan_hash_value , sum(BUFFER_GETS_DELTA)/(sum(executions_delta)+1) avg_buffer_gets,
    sum(elapsed_time_delta)/(sum(executions_delta)+1) avg_ela, sum(executions_delta)+1 sum_exec,
    min(snap_id) min_snap_id, max(snap_id) max_snap_id
    from dba_hist_sqlstat a
    where exists  (
       select sql_id from dba_hist_sqlstat b where a.sql_id = b.sql_id
         and  a.plan_hash_value != b.plan_hash_value
         and  b.plan_hash_value > 0)
    and plan_hash_value > 0
    group by sql_id, plan_hash_value
    order by sql_id, avg_ela
  )
  order by sql_id, avg_ela
  )
group by sql_id
having max(decode(rw_num,1,sum_exec)) > 1
order by 7 desc
/
spool off
clear columns
set numformat 9999999999

具体的执行结果参考如下:

SQL> spool sql_with_more_than_1plan.txt
set lines 220 pages 9999 trimspool on
SQL> SQL> set numformat 999,999,999
SQL> column plan_hash_value format 99999999999999
SQL> column min_snap format 999999
SQL> column max_snap format 999999
SQL> column min_avg_ela format 999,999,999,999,999
SQL> column avg_ela format 999,999,999,999,999
SQL> column ela_gain format 999,999,999,999,999
SQL> select sql_id,
2 min(min_snap_id) min_snap,
3 max(max_snap_id) max_snap,
4 max(decode(rw_num,1,plan_hash_value)) plan_hash_value,
5 max(decode(rw_num,1,avg_ela)) min_avg_ela,
6 avg(avg_ela) avg_ela,
7 avg(avg_ela) - max(decode(rw_num,1,avg_ela)) ela_gain,
8 -- max(decode(rw_num,1,avg_buffer_gets)) min_avg_buf_gets,
9 -- avg(avg_buffer_gets) avg_buf_gets,
10 max(decode(rw_num,1,sum_exec))-1 min_exec,
11 avg(sum_exec)-1 avg_exec
12 from (
13 select sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec,
14 row_number() over (partition by sql_id order by avg_ela) rw_num , min_snap_id, max_snap_id
15 from
16 (
17 select sql_id, plan_hash_value , sum(BUFFER_GETS_DELTA)/(sum(executions_delta)+1) avg_buffer_gets,
18 sum(elapsed_time_delta)/(sum(executions_delta)+1) avg_ela, sum(executions_delta)+1 sum_exec,
19 min(snap_id) min_snap_id, max(snap_id) max_snap_id
20 from dba_hist_sqlstat a
21 where exists (
22 select sql_id from dba_hist_sqlstat b where a.sql_id = b.sql_id
23 and a.plan_hash_value != b.plan_hash_value
24 and b.plan_hash_value > 0)
25 and plan_hash_value > 0
26 group by sql_id, plan_hash_value
27 order by sql_id, avg_ela
28 )
29 order by sql_id, avg_ela
30 )
31 group by sql_id
32 having max(decode(rw_num,1,sum_exec)) > 1
33 order by 7 desc
34 /
spool off
clear columns
set numformat 9999999999
SQL_ID MIN_SNAP MAX_SNAP PLAN_HASH_VALUE MIN_AVG_ELA AVG_ELA ELA_GAIN MIN_EXEC AVG_EXEC
------------- -------- -------- --------------- -------------------- -------------------- -------------------- ------------ ------------
gfjvxb25b773h 192 204 3801470479 6,368 171,152 164,784 1 1
7asddw0r2wykx 192 211 3660107835 10,311 16,448 6,137 57 30
7ng34ruy5awxq 192 209 2606284882 117 320 204 426 1,371
g278wv0zzpn5j 195 211 3142739690 0 189 189 72 63
1gu8t96d0bdmu 192 209 2035254952 67 154 87 826 1,134
bsa0wjtftg3uw 193 211 2020579421 0 18 18 224 191
0kugqg48477gf 201 202 3321035584 0 0 0 19 27

7 rows selected.

通过以上查询我们发现了7个结果,其中MIN_SNAP和MAX_SNAP代表该sql_id所代表最早出现时候的快照和最后一次出现的快照;PLAN_HASH_VALUE代表着最佳的执行计划;ELA_GAIN字段代表如果使用最佳的执行计划,与平均执行时间能够提速多少时间。

在查出结果后,我们可以通过sql profile(参考老刘的《验证sql_profile的执行计划》),也可以通过11g的dbms_spm.load_plans_from_cursor_cache更改相关Sql_id的sql的执行计划,这里以11g为例子做如下变更,对上面查询到的7asddw0r2wykx语句执行计划改为最优:

SQL> variable v_sqlid number
SQL> begin
2 :v_sqlid :=
3 dbms_spm.load_plans_from_cursor_cache(
4 sql_id=>'&sql_id',
5 plan_hash_value=>&plan_hash,
6 fixed=>'YES');
7 end;
8 /
Enter value for sql_id: 7asddw0r2wykx
old 4: sql_id=>'&sql_id',
new 4: sql_id=>'7asddw0r2wykx',
Enter value for plan_hash: 3660107835
old 5: plan_hash_value=>&plan_hash,
new 5: plan_hash_value=>3660107835,

PL/SQL procedure successfully completed.