由于bitmap造成的insert相互阻塞

Posted on 2015-05-02 by royalwzy

CREATE TABLE t
(
NAME VARCHAR2(20)
);

CREATE BITMAP INDEX idx_b_name ON t(NAME);

INSERT INTO t VALUES(‘U’);
INSERT INTO t VALUES(‘C’);

select sid from v$mystat where rownum<2; –198

select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000000001E2E9600 198 TM 77149 0 3 0 0
3 000007FFB9CE8488 198 TX 65561 1133 6 0 0
–这个时候登录第二个session
select sid from v$mystat where rownum<2;
200

INSERT INTO t VALUES(‘U’);
select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;

1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 0000000026980C10 198 TM 77149 0 3 0 0
4 000007FFB9CE8488 198 TX 196617 1336 6 0 0

这个时候在SESSION2中
INSERT INTO t VALUES(‘E’);

select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;

1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 000000002697E370 200 TM 77149 0 3 0 0
4 000000002697E370 198 TM 77149 0 3 0 0
5 000007FFB9CC9678 200 TX 65568 1131 6 0 0
6 000007FFB9CE8488 198 TX 196617 1336 6 0 0
SEssion不阻塞

这个时候继续在session2 做插入

INSERT INTO t VALUES(‘U’);
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 000007FFBDC56658 200 TX 196617 1336 0 4 0
4 0000000026980C10 200 TM 77149 0 3 0 0
5 0000000026980C10 198 TM 77149 0 3 0 0
6 000007FFB9CC9678 200 TX 65568 1131 6 0 0
7 000007FFB9CE8488 198 TX 196617 1336 6 0 1

这个时候Insert发生了阻塞 session2在等seesion1释放资源

在这个时候发生一个insert 阻碍了 INSERT 的操作造成它的原因就是 BITMAP
SELECT OBJECT_ID FROM User_Objects WHERE object_name=’IDX_B_NAME’ ; –77150

alter session set events ‘immediate trace name treedump level INDEX_OBJECT_ID’;

alter session set events ‘immediate trace name treedump level 52563’

Bitmap深入研究(一)

Posted on 2015-04-03 by royalwzy

环境：
ORACLE:
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Prod
PL/SQL Release 10.2.0.2.0 – Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 – Production
NLSRTL Version 10.2.0.2.0 – Production

OS：

LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description:    Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)
Release:        5.4
Codename:       Carthage
研究Bitmap索引的存储和特性

SQL> conn scott/scott
Connected.
SQL> CREATE TABLE t –创建一张测试表
2 (
3 NAME VARCHAR2(20)
4 );
Table created.

SQL> CREATE BITMAP INDEX idx_b_name ON t(NAME); –创建一个测试的索引

Index created.
SQL> select sid from v$mystat where rownum<2; –查看自己的sid

SID
———-
159

–查看trace的文件所在
select a.value||b.symbol||c.instance_name||’_ora_’||d.spid||’.trc’ trace_file from
(select value from v$parameter where name=’user_dump_dest’) a,
(select substr(value,-6,1) symbol from v$parameter where name=’user_dump_dest’) b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr=p.addr
and s.sid=m.sid and m.statistic#=0) d;

TRACE_FILE
——————————————————————————–
/u01/app/oracle/admin/orcl/udump/orcl_ora_9707.trc
查看文件内容

*** ACTION NAME:() 2015-03-03 08:25:59.728
*** MODULE NAME:(SQL*Plus) 2015-03-03 08:25:59.728
*** SERVICE NAME:(SYS$USERS) 2015-03-03 08:25:59.728
*** SESSION ID:(159.7) 2015-03-03 08:25:59.728
—– begin tree dump 这个是空的时候
leaf: 0x100018c 16777612 (0: nrow: 0 rrow: 0)
—– end tree dump
*** 2015-03-03 08:28:08.940
—– begin tree dump insert into t values(‘U’); commit;
leaf: 0x100018c 16777612 (0: nrow: 1 rrow: 1)
—– end tree dump
*** 2015-03-03 08:29:20.359
—– begin tree dump insert into t values(‘c’); commit;
leaf: 0x100018c 16777612 (0: nrow: 2 rrow: 2)
—– end tree dump
*** 2015-03-03 08:30:09.053
—– begin tree dump 又insert into t values(‘U’); commit;
leaf: 0x100018c 16777612 (0: nrow: 2 rrow: 2)
—– end tree dump
*** 2015-03-03 08:38:07.763
—– begin tree dump delete from t where name=’c’; commit;
leaf: 0x100018c 16777612 (0: nrow: 2 rrow: 1)
—– end tree dump
*** 2015-03-03 08:40:35.384
—– begin tree dump –insert into t select OBJECT_TYPE from user_objects;
leaf: 0x100018c 16777612 (0: nrow: 3 rrow: 3)
—– end tree dump

—– begin tree dump insert into t SELECT object_type FROM dba_objects; / commit;
branch: 0x100018c 16777612 (0: nrow: 6, level: 1)
leaf: 0x1000190 16777616 (-1: nrow: 11 rrow: 11)
leaf: 0x100018f 16777615 (0: nrow: 2 rrow: 2)
leaf: 0x100018d 16777613 (1: nrow: 17 rrow: 17)
leaf: 0x100028c 16777868 (2: nrow: 3 rrow: 3)
leaf: 0x100018e 16777614 (3: nrow: 2 rrow: 2)
leaf: 0x100028d 16777869 (4: nrow: 11 rrow: 11)
—– end tree dump 这个时候可以发现整个树的结构都发生了变化不再是单纯只有一个叶子节点了

仔细观察最后一次dump出来的结构我们可以得出以下结论
1.原来的叶子节点发生了分裂而不是迁移。（b-tree也是一样）根节点永远不会变
2.叶子节点从-1开始的顺序不代表在数据文件上也是连续的块

观察倒数第二个的nrow=3 我们去验证是否是这样
SQL> select DISTINCT name from t;

NAME
——————–
U
INDEX
TABLE

第一列中的
1.branch表示分支节点（包括根节点），
2.leaf则表示叶子节点
第二列表示十六进制表示的节点的地址；
第三列表示十进制表示的节点的地址；
第四列表示相对于前一个节点的位置，根节点从0开始计算，其他分支节点和叶子节点从-1开始计算；
第五列的nrow表示当前节点中所含有的索引条目的数量。比如我们可以看到含有的nrow为2，表示节点中含有2个索引条目，
分别指向2个分支节点
第六列中的
1.rrow表示有效的索引条目（因为索引条目如果被删除，不会立即被清除出索引块中。
所以nrow减rrow的数量就表示已经被删除的索引条目数量）的数量，
2.level表示分支节点的层级，对于叶子节点来说level都是0

以种方式以树状形式转储整个索引。同时，我们可以转储一个索引节点来看看其中存放了些什么

从上面我们可以看到索引块（根）的地址为：16777612 .我们先将其转换为文件号以及数据块号

select dbms_utility.data_block_address_file(16777615) “file”,dbms_utility.data_block_address_block(16777615) “BLOCK” from dual;

FILE BLOCK
—————
4 396

— 注：反推的话可以使用 select dbms_utility.make_data_block_address(4,396) from dual;
我们首先看一下根节点然后再看一下叶子节点

SQL> alter system dump datafile 4 block 396;

System altered.
–内容如下

*** 2015-03-03 09:03:48.908
Start dump data blocks tsn: 4 file#: 4 minblk 396 maxblk 396
buffer tsn: 4 rdba: 0x0100018c (4/396)
scn: 0x0000.000739b7 seq: 0x02 flg: 0x04 tail: 0x39b70602
frmt: 0x02 chkval: 0x3905 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DECE400 to 0x0DED0400
DECE400 0000A206 0100018C 000739B7 04020000 [………9……]
DECE410 00003905 00000002 0000CD53 000739B7 [.9……S….9..]
DECE420 00000000 00320001 01000189 002C0006 [……2…….,.]
DECE430 00000102 00800B8F 00020136 00008000 [……..6…….]
DECE440 000739B7 00000000 00000000 04800001 [.9…………..]
DECE450 00000001 00260005 1F171F3D 01000190 [……&.=…….]
DECE460 00000004 00001F78 1F6C1F4B 1F5D1F44 [….x…K.l.D.].]
。。。
。。。
省略部分内容
。。。
。。。
Block header dump: 0x0100018c
Object id on Block? Y
seg/obj: 0xcd53 csc: 0x00.739b7 itc: 1 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1000189 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.02c.00000102 0x00800b8f.0136.02 C— 0 scn 0x0000.000739b7

Branch block dump
=================
header address 233628748=0xdece44c
kdxcolev 1
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 1
kdxconro 5
kdxcofbo 38=0x26
kdxcofeo 7997=0x1f3d
kdxcoavs 7959
kdxbrlmc 16777616=0x1000190
kdxbrsno 4
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8011] dba: 16777615=0x100018f
col 0; len 10; (10): 4a 41 56 41 20 43 4c 41 53 53
col 1; len 1; (1): 01
col 2; TERM
row#1[8044] dba: 16777613=0x100018d
col 0; len 6; (6): 4a 41 56 41 20 44
col 1; TERM
row#2[8004] dba: 16777868=0x100028c
col 0; len 1; (1): 53
col 1; TERM
row#3[8029] dba: 16777614=0x100018e
col 0; len 7; (7): 53 59 4e 4f 4e 59 4d
col 1; len 1; (1): 01
col 2; TERM
row#4[7997] dba: 16777869=0x100028d
col 0; len 1; (1): 54
col 1; TERM
—– end of branch block dump —–
End dump data blocks tsn: 4 file#: 4 minblk 396 maxblk 396

–kdxcolev表示索引层级号，这里由于我们转储的是根节点所以其层级号为1（当非叶子节点时层级也有可能为2 3 4递增）对叶子节点来说该值为0
–kdxcolok表示该索引上是否正在发生修改块结构的事务；
–kdxcoopc表示内部操作代码；
–kdxconco表示索引条目中列的数量；
–kdxcosdc表示索引结构发生变化的数量，当你修改表里的某个索引键值时，该值增加；
–kdxconro表示当前索引节点中索引条目的数量，但是注意，不包括kdxbrlmc指针；
–kdxcofbo表示当前索引节点中可用空间的起始点相对当前块的位移量；
–kdxcofeo表示当前索引节点中可用空间的最尾端的相对当前块的位移量；
–kdxcoavs表示当前索引块中的可用空间总量，也就是用kdxcofeo减去kdxcofbo得到的。
–kdxbrlmc表示分支节点的地址，该分支节点存放了索引键值小于row#0（在转储文档后半部分显示）所含有的最小值的所有节点信息；
–kdxbrsno表示最后一个被修改的索引条目号，这里看到是0，表示该索引是新建的索引；
–kdxbrbksz表示可用数据块的空间大小。实际从这里已经可以看到，即便是PCTFREE设置为0，也不能用足8192字节。
接下去看到的是row#0的内容
这部分内容就是在根节点中所记录的索引条目，总共是5个[0-4]条目。再加上kdxbrlmc所指向的第一个分支节点，
我们知道该根节点中总共存放了6个分支节点的索引条目也对应了树形结构里nrow的值

每个索引条目都指向一个分支节点。
其中col 1表示所链接的分支节点的地址，该值经过一定的转换以后实际就是row#所在行的dba的值。
如果根节点下没有其他的分支节点，则col 1为TERM 比如上面的dba: 16777613=0x100018d的col 1; TERM

col 0表示该分支节点所链接的最小键值。其转换方式非常复杂是要从十六进制转换为十进制
我们可以做下试验

row#0[8011] dba: 16777615=0x100018f
col 0; len 10; (10): 4a 41 56 41 20 43 4c 41 53 53
SELECT to_number(‘4a’,’XX’) FROM dual; –74
SELECT to_number(’41’,’XX’) FROM dual;–65
SELECT to_number(’56’,’XX’) FROM dual;–86
SELECT to_number(’41’,’XX’) FROM dual;–65
SELECT to_number(’20’,’XX’) FROM dual;–32
SELECT to_number(’43’,’XX’) FROM dual;–67
SELECT to_number(‘4c’,’XX’) FROM dual;–76
SELECT to_number(’41’,’XX’) FROM dual;–65
SELECT to_number(’53’,’XX’) FROM dual;–83
SELECT to_number(’53’,’XX’) FROM dual;–83
索引键值是char类型的，所以对每个值都运用chr函数就可以得到被索引键值为:
SELECT CHR(74) FROM dual; –J
SELECT CHR(65) FROM dual; –A
SELECT CHR(86) FROM dual; –V
SELECT CHR(65) FROM dual; –A
SELECT CHR(32) FROM dual; — (空格)
SELECT CHR(67) FROM dual; –C
SELECT CHR(76) FROM dual; –L
SELECT CHR(65) FROM dual; –A
SELECT CHR(83) FROM dual; –S
SELECT CHR(83) FROM dual; –S
–我擦竟然是JAVA CLASS ， JAVA CLASS就是dba为16777615的索引块所链接的最小键值

然后再dump一下看到结果就是我们要的10进制还没转成16进制的值
select dump(‘JAVA CLASS’) from dual;
Typ=96 Len=10: 74,65,86,65,32,67,76,65,83,83

那么我们上面dump出来了根节点接下来继续dump叶子节点我们就dump刚刚的16777615这个块吧。

select dbms_utility.data_block_address_file(16777615) “file”,dbms_utility.data_block_address_block(16777615) “BLOCK” from dual;
file BLOCK
———- ———-
4 399

alter system dump datafile 4 block 399;
文件如下：
*** 2015-03-03 10:26:29.354
Start dump data blocks tsn: 4 file#: 4 minblk 399 maxblk 399
buffer tsn: 4 rdba: 0x0100018f (4/399)
scn: 0x0000.00073fd7 seq: 0x01 flg: 0x04 tail: 0x3fd70601
frmt: 0x02 chkval: 0x0703 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DECE400 to 0x0DED0400
DECE400 0000A206 0100018F 00073FD7 04010000 [………?……]
DECE410 00000703 00000002 0000CD53 00073FD7 [……..S….?..]
DECE420 00000000 00320002 01000189 00080004 [……2………]
…..
Block header dump: 0x0100018f
Object id on Block? Y
seg/obj: 0xcd53 csc: 0x00.73fd7 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1000189 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0004.008.000000d0 0x00800041.009e.01 CB–    0 scn 0x0000.000739b2
0x02   0x0001.009.000000db 0x008002da.00be.09 C—    0 scn 0x0000.000739ba

Leaf block dump
===============
header address 233628772=0xdece464
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 1
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 2853=0xb25
kdxcoavs 3233
kdxlespl 0
kdxlende 0
kdxlenxt 16777613=0x100018d
kdxleprv 16777616=0x1000190
kdxledsz 0
kdxlebksz 8032
row#0[3667] flag: ——, lock: 0, len=3945
col 0; len 10; (10): 4a 41 56 41 20 43 4c 41 53 53
col 1; len 6; (6): 01 00 01 db 00 b0
….
row#1[2853] flag: ——, lock: 0, len=814
col 0; len 10; (10): 4a 41 56 41 20 43 4c 41 53 53
col 1; len 6; (6): 01 00 02 3e 01 00
col 2; len 6; (6): 01 00 02 80 01 4f
col 3; len 785; (785):
cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55
55 55 ce 55 55 55 55 55 55 05 ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55
55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55
55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce
55 55 55 55 55 55 05 ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55
55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55
55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce 55 55 55
55 55 55 05 ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55
55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55
c9 55 05 ff 2d 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55
55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf
55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce 55 55 55 55 55 55 05
ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55
55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55
55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce 55 55 55 55 55 55 05 ff 18 55
—– end of leaf block dump —–
End dump data blocks tsn: 4 file#: 4 minblk 399 maxblk 399

发现内容与根节点完全类似，只不过该索引块中所包含的索引条目（指向叶子节点）的数量只有两个

我们再拿其中一个叶子来分析一下
row#1[2853] flag: ——, lock: 0, len=814
col 0; len 10; (10): 4a 41 56 41 20 43 4c 41 53 53
col 1; len 6; (6): 01 00 02 3e 01 00
col 2; len 6; (6): 01 00 02 80 01 4f
col 3; len 785; (785):
cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55

其中flag表示标记，比如删除标记等；
而lock表示锁定信息。col 0表示索引键值，其算法与我们在前面介绍分支节点时所说的算法一致。

继续转化进制后使用chr
4a 41 56 41 20 43 4c 41 53 53
74 65 86 65 32 67 76 65 83 83
J A V A C L A S S

col 1
在b-tree的时候表示rowid
在bitmap的时候表示rowid的起始位置
01 00 01 db 00 b0
010001db00b0

SELECT to_number(‘010001db00b0′,’xxxxxxxxxxxx’) FROM dual;
select dbms_utility.data_block_address_file(to_number(‘010001DB’,’xxxxxxxxxxxx’)) “file”,
dbms_utility.data_block_address_block(to_number(‘010001DB’,’xxxxxxxxxxxx’)) “BLOCK” from dual;

4 475
col 2
在b-tree的时候没有
在bitmap中表示rowid的结束位置
01000280014f

col3表示位图编码在b-tree里一样也没有
select to_number(‘0180001c’,’xxxxxxxxxxxx’) from dual;

中间的ca和开头的cf，不是索引的bitmap，而是类似数据头之类的东西，具体数值代表什么我还不清楚，不过似乎每64位就会出现一个数据头
SELECT
sys.pkg_number_trans.f_hex_to_bin(’55’) as c2,
from dual;

1010101

其中不足8位的前面用0补齐
–连接起来全是二进制的数据也就代表了是不是JAVA CLASS

SELECT SYS.pkg_number_trans.f_oct_to_hex(‘01000280014f’) FROM dual;

在数据很少的时候单一块的时候很容易验证但是数据多了之后怎么在块与块之间进行

我们来做一个单一的吧
DROP TABLE t1 PURGE;
CREATE TABLE t1
(
ID NUMBER
);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(1);
COMMIT;
select dbms_rowid.rowid_relative_fno(rowid)file_id, dbms_rowid.rowid_block_number(rowid)block_id,
dbms_rowid.rowid_row_number(rowid) row# from t1;

FILE_ID   BLOCK_ID       ROW#
———- ———- ———-
4        664          0
4        664          1
4        664          2
4        664          3
4        664          4
4        664          5
4        664          6

在同一个数据块上
alter session set events ‘10608 trace name context forever, level 10608’;

CREATE BITMAP INDEX IDX_B_T1 ON T1(ID);

alter session set events ‘10608 trace name context off’;

10608事件用来跟踪创建bitmap索引的过程

内容如下：
qerbiARwo: bitmap size is 8168
qerbiIPI default pctfree=10
qerbiIPI length=0
qerbiAllocate pfree=127 space=8168
qerbiStart first start
qerbiRop: rid=01000298.0000, new=Y , key: (2): c1 02 –表示这是一个新值产生一个的新的位图表示
qerbiCmpSz notfound pctfree=10
qerbiCmpSz adjblksize=7351 length=0
qerbiRop keysize=4 maxbm=3529
kdibcoinit(b7ddb044): srid=01000298.0000
qerbiRop: rid=01000298.0001, new=Y , key: (2): c1 03
kdibcoinit(b7ddafc8): srid=01000298.0001
qerbiRop: rid=01000298.0002, new=Y , key: (2): c1 04
kdibcoinit(b7ddaf4c): srid=01000298.0002
qerbiRop: rid=01000298.0003, new=Y , key: (2): c1 05
kdibcoinit(b7ddaed0): srid=01000298.0003
qerbiRop: rid=01000298.0004, new=N, key: (2): c1 04 –不是新值
qerbiRop: rid=01000298.0005, new=N, key: (2): c1 03
qerbiRop: rid=01000298.0006, new=N, key: (2): c1 02
kdibcoend(b7ddb044): erid=01000298.0007status=3
qerbiCon: key: (2): c1 02
srid=01000298.0 erid=01000298.7 bitmap: (2): c8 41
kdibcoend(b7ddafc8): erid=01000298.0007status=3
qerbiCon: key: (2): c1 03
srid=01000298.0 erid=01000298.7 bitmap: (2): c8 22
kdibcoend(b7ddaf4c): erid=01000298.0007status=3
qerbiCon: key: (2): c1 04
srid=01000298.0 erid=01000298.7 bitmap: (2): c8 14
kdibcoend(b7ddaed0): erid=01000298.0007status=3
qerbiCon: key: (2): c1 05
srid=01000298.0 erid=01000298.7 bitmap: (1): 03
qerbiFreeMemory: Work heap is used.

上面是创建bitmap索引的过程。我们先把被索引的列的值换算成十六进制：

select dump(4),dump(3),dump(2),dump(1) from dual;
Typ=96 Len=2: 85,99 Typ=96 Len=3: 74,109,104
1 Typ=2 Len=2: 193,5 Typ=2 Len=2: 193,4 Typ=2 Len=2: 193,3 Typ=2 Len=2: 193,2
5 4 3 2的16进制是05 04 03 02 同时对应了上面的生成key部分的值
qerbiCon: key: (2): c1 02
srid=01000298.0 erid=01000298.7 bitmap: (2): c8 41 从上面的推论我们得出02表示1
start rowid和end rowid的格式分两部分，中间用点隔开，
点左边的表示文件号（从左边第一个字节开始的4个字节表示）和数据块号（从左边第五个字节开始的4个字节表示），
点右边表示数据块里的行号。这里的显示可以看到，
这几条记录都位于相同的数据块里。这里的0100表示文件号
select dbms_utility.data_block_address_file(to_number(‘01000298′,’xxxxxxxxxxxx’)) “file”,
dbms_utility.data_block_address_block(to_number(‘01000298′,’xxxxxxxxxxxx’)) “BLOCK” from dual;

4 664
根据我们刚刚的插入按照预想的应该是：1000001

得到结果果然是：
SELECT
sys.pkg_number_trans.f_hex_to_bin(’41’) as c2
from dual;
1000001

Oracle SPA介绍

Posted on 2015-02-27 by royalwzy

Sql Performance Analyzer (SPA)
这是oracle 11g的新特性可对一部分sql结果进行性能分析适用于数据升级迁移做前后的比较
分析的结果会永远存在的数据库内部。和数据库重演不同的是spa只能分析sql语句且只有查询语句
步骤如下：
1.在生产库捕获具体语句
2.把捕获的语句打包传输到测试环境
3.创建SPA任务在测试环境
4.生成改变之前的执行信息
5.做改变（升级或者打补丁或者改参数或者等等。。）
6.生成改变之后的执行信息
7.对于两份信息（可生成报告文件）

CREATE USER ucjmh IDENTIFIED BY ucjmh;
GRANT DBA TO ucjmh;
conn ucjmh/ucjmh –创建一个用户
–建几张表
CREATE TABLE uc_objects AS SELECT * FROM dba_objects;
CREATE TABLE uc_tables AS SELECT * FROM dba_tables;
CREATE TABLE uc_users AS SELECT * FROM dba_users;
–做一些查询
select /*UCJMH*/ * from uc_objects;
select /*UCJMH*/ * from uc_tables;
select /*UCJMH*/ * from uc_users;
–生成一个sqlset
begin
sys.dbms_sqltune.create_sqlset(
sqlset_name => ‘uc_sts’,
sqlset_owner => ‘UCJMH’);
end;
/

PL/SQL procedure successfully completed.

–查看一下是否生成成功
SQL> select name from user_sqlset;

NAME
——————————
uc_sts

–把需要的sql放到这个sqlset里
declare
stscur dbms_sqltune.sqlset_cursor;
begin
open stscur for
select value(P) from table(
dbms_sqltune.select_cursor_cache(
‘sql_text like ”select /*UCJMH*/%”’,
null,null,null,null,null,null,’ALL’)) P;
dbms_sqltune.load_sqlset(
sqlset_name =>’uc_sts’, –这里是区分大小写的如果你上面写的小写不要想当然认为ORACLE会内部转换成大写
populate_cursor => stscur,
sqlset_owner =>’UCJMH’);
end;

PL/SQL procedure successfully completed

SQL> select sql_text from user_sqlset_statements where sqlset_name=’uc_sts’;

SQL_TEXT
——————————————————————————–
select /*UCJMH*/ * from uc_objects
select /*UCJMH*/ * from uc_users
select /*UCJMH*/ * from uc_tables

–或者
SQL> select sql_Text from table(dbms_sqltune.select_sqlset(‘uc_sts’));

SQL_TEXT
——————————————————————————–
select /*UCJMH*/ * from uc_objects
select /*UCJMH*/ * from uc_users
select /*UCJMH*/ * from uc_tables

创建暂存表保存STS：
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(‘UC_STS_TAB’,’UCJMH’);

SQL> select count(*) from uc_sts_tab;

COUNT(*)
———-
0
–上面的步骤只是创建了一个用来暂存set的表但是并没有真的已经把sql放进去
–把STS加载到暂存表里：
exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET(‘uc_sts’,’UCJMH’,’UC_STS_TAB’,’UCJMH’);

PL/SQL procedure successfully completed.

SQL> select count(*) from uc_sts_tab;

COUNT(*)
———-
6

–不是应该是3条sql吗问什么是6行记录呢？仔细看看可以发现每个sql_id占两行一行是sql 一行是统计信息和执行记划等信息

把暂存表导出生产环境导入测试环境（我这里测试环境没有东西我直接把整个用户导过去如果你们其它表已经存在了可以只导一张暂存表）

create or replace directory dmp as ‘/home/oracle/dmp’;
EXIT
expdp ucjmh/ucjmh directory=dmp dumpfile=uc.dmp
export ORACLE_SID=emrep
sqlplus / AS SYSDBA
create or replace directory dmp as ‘/home/oracle/dmp’;
EXIT
impdp system/oracle directory=dmp dumpfile=uc.dmp

把STS从暂存表里放入数据字典里：
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(‘uc_sts’,’UCJMH’,TRUE,’UC_STS_TAB’,’UCJMH’); –true表示如果有的话是否替换

PL/SQL procedure successfully completed.

–创建一个spa任务
SQL> var tname varchar2(20);
SQL> exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => ‘uc_sts’, task_name => ‘MYSPA’);

PL/SQL procedure successfully completed.

–执行任务从而构建before change data
SQL> exec dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘TEST EXECUTE’, execution_name => ‘before’);

PL/SQL procedure successfully completed.

–生成before change的报表
SQL> set long 99999
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname, type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 1
Current Execution : before
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:27:01

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:27:01
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
3 5d0v1n7gt8j2y .014787 .016058 .015664 1013 284
5 g9shuu1cxbqqz .006716 .001369 .001333 76 23
4 71tf6tnk513p5 .002394 .000084 .000111 3 3

——————————————————————————-

–做你要做的变化，比如修改参数、升级硬件、升级操作系统等等。
SQL> exec dbms_stats.gather_schema_stats(USER);

PL/SQL procedure successfully completed.
构建after change data
SQL> EXEC dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘TEST EXECUTE’, execution_name => ‘after’);

PL/SQL procedure successfully completed.

生成after change的报表
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname,type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : after
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:30:29

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:30:29
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
7 5d0v1n7gt8j2y .000147 .017201 .016886 1012 284
9 g9shuu1cxbqqz .000244 .001269 .001444 75 23
8 71tf6tnk513p5 .000423 .000064 0 2 3

——————————————————————————-

–比较before和after
EXEC dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘COMPARE PERFORMANCE’);

–生成分析报表
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname, type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : after
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:30:29

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
7 5d0v1n7gt8j2y .000147 .017201 .016886 1012 284
9 g9shuu1cxbqqz .000244 .001269 .001444 75 23
8 71tf6tnk513p5 .000423 .000064 0 2 3

——————————————————————————-

删除任务：
select * from DBA_SQLSET_REFERENCES where SQLSET_NAME=’uc_sts’;
exec dbms_sqltune.drop_tuning_task(‘MYSPA’);

删除暂存表和STS：
drop table hr.hsj_sts_tab purge;
exec dbms_sqltune.drop_sqlset(‘uc_sts’);

性能调优工具05–SQL Profiles

Posted on 2014-08-15 by royalwzy

SQL Profiles

SQL Profiles介绍:
1. SQL Profiles是在10g中被引进的新特性,主要是通过DBMS_SQLTUNE包或者EM来管理,它是AST(Automatic SQL Tuning)过程的一部分;也在10g版本中使用较多,11g中更多使用SPM;
2. Automatic SQL Tuning:
  1. 因为缺少必要的信息,查询优化器对sql语句可能会产生不准确的评估(比如返回的记录数等),从而导致生成一个较差的执行计划;传统的处理方法是手动添加hints来改变执行计划,但是对于打包的应用程序,无法修改相应的sql语句,所以只能等应用程序提供商来解决这个问题;
  2. Automatic SQL Tuning使用SQL Profile的特性解决了这个问题;Automatic Tuning Optimizer会为每条sql语句创建一个包含辅助信息(主要通过动态采用和部分执行技术收集附加信息)的SQL Profile;查询优化器可以计算出更精确的cardinality/selectivity/cost的评估,从而生成更优的执行计划;
  3. 在产生SQL Profile期间,Automatic Tuning Optimizer也会为了与优化器参数匹配从而执行SQL语句的历史信息,比如把optimizer_mode参数从all_rows修改到first_rows;
3. SQL Profile:
  1. SQL Profile是存储在数据字典中的一系列的信息(主要是包括在AST过程中针对较差执行计划的一些修正信息),它可以使查询优化器针对某条sql生成更优的执行计划;
  2. SQL Profile并不包含独立的执行计划,只是优化器在选择执行计划时除了参考数据库配置/绑定变量值/优化器统计信息/数据集等信息之外还会参考SQL Profile中的附加信息;
  3. 一段时间后,SQL Profile的内容可能会过期,需要运行AST重新生成SQL Profile;
  4. tips:SQL Profile并不会像Stored Outlines一样固定SQL语句的执行计划;即便拥有相同SQL Profile,由于表的增长或者索引的创建删除,执行计划也会改变;
4. 如何控制SQL Profile的范围:
  1. SQL Profile的范围由category属性控制,它决定了哪些用户会话可以应用这些profile;
  2. 可以通过dba_sql_profiles字典来查看(SELECT NAME, category FROM dba_sql_profiles);
  3. 默认情况下,所有的profiles都属于DEFAULT分类,表示所有的初始化参数sqltune_category=DEFAULT(这也是默认值)的用户会话都可使用这些profile;
  4. 可以通过修改sqltune_category参数来控制profile使用的范围,从而可以在发布之前进行测试;
5. 可以使用SQL Profile的语句:
  1. SELECT语句;
  2. UDPATE语句;
  3. INSERT INTO SELECT语句;
  4. DELETE语句;
  5. CTAS语句;
  6. MERGE语句(包含UPDATE或者INSERT操作);
6. Sql Profiles是对OUTLINES的增强,并且具有自己的特点:
  1. SQL Profiles更容易生成,更改和控制;
  2. SQL Profiles对SQL语句的支持更广泛;
7. 使用SQL Profiles的目的:
  1. 稳定或者”锁定”SQL的执行计划;
  2. 在不能修改应用程序中SQL的情况下,使SQL按照指定的执行计划运行;
管理SQL Profiles:
1. 首先用户需要有CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, ALTER ANY SQL_PROFILE权限;
2. 从STA优化结果中接受一个Profile:DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name,object_id,name,description,category,task_owner,replace,force_match,profile_type),例子<<如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理>>;
3. 修改Profile:DBMS_SQLTUNE.ALTER_SQL_PROFILE (name,attribute_name,value);
4. 删除Profile:DBMS_SQLTUNE.DROP_SQL_PROFILE (name,ignore);
5. SQL Profile的迁移,例子<<How to Move SQL Profiles from One Database to Another>>;
6. 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles,这种方法创建的SQL Profiles和Outlines一样,能够稳定SQL的执行计划,例子;
  1. 稳定现有的SQL的执行计划;
  2. 在不能修改SQL的情况下改变并固定SQL的执行计划,即使原来的SQL使用了hints;

———————- 如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理 ———————-

— 1.实验环境;

SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

— 2.创建测试表;

conn / as sysdba
CREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE rownum <= 50000;
CREATE TABLE t2 AS SELECT * FROM dba_objects;
CREATE INDEX idx_t2_oid ON t2(object_id);

BEGIN
dbms_stats.gather_table_stats(USER, ‘T1’, cascade => TRUE, method_opt => ‘for all columns size 1’);
dbms_stats.gather_table_stats(USER, ‘T2’, cascade => TRUE, method_opt => ‘for all columns size 1’);
END;
/

— 3.查看SQL的执行计划和统计信息,并选择不同的访问路径;

1.查看SQL的执行计划;

1.从执行结果发现:这条sql在两个表上都是全表扫描,在T1上因为有like条件,所以很正常;在T2表上应该有其它的访问路径;

2.在执行计划ID=2那一行,CBO评估表T1 LIKE ‘%T1%’的返回结果为2500行,即T1表总记录的5%(类似于LIKE等谓词的默认选择率为5%);如果采用两个表采用INDEX RANGE SCAN + NESTED LOOPS连接的话,CBO估算的成本会高于FULL TABLE SCAN + HASH JOIN;

SQL> SET AUTOT ON;
SQL> SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

……

26 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1838229974

—————————————————————————
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      | 2500 |    97K|   212   (3)| 00:00:03 |
|* 1 | HASH JOIN         |      | 2500 |    97K|   212   (3)| 00:00:03 |
|* 2 |   TABLE ACCESS FULL| T1   | 2500 | 72500 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

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

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

Statistics
———————————————————-
304 recursive calls
0 db block gets
974 consistent gets
0 physical reads
0 redo size
1402 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
26 rows processed

2.加hints改变sql的访问路径查看成本;

1.从执行计划发现:CBO评估的成本为5061,远远高于原来的212;

2.从统计信息发现:加了hint之后的逻辑读只有288,远远低于原来的974;

3.说明由于ORACLE过高的估计了T1表返回的结果集行数,进而过高的估计了NEST LOOP的成本,导致没有选择最优的执行计划;

4.tips:也可以使用EXPLAIN PLAN FOR然后SELECT * FROM TABLE(DBMS_XPLAN.display())的方法查看执行计划;

SQL> SET AUTO ON;

SQL> SELECT /*+ use_nl(t1 t2) index(t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

26 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1164754026

——————————————————————————–
———-

——————————————————————————–
———-

| 0 | SELECT STATEMENT | | 2500 | 97K| 5061 (1)|
00:01:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|
00:00:01 |

| 2 | NESTED LOOPS | | 2500 | 97K| 5061 (1)|
00:01:01 |

|* 3 | TABLE ACCESS FULL | T1 | 2500 | 72500 | 55 (4)|
00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_T2_OID | 1 | | 1 (0)|
00:00:01 |

——————————————————————————–
———-

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

3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Statistics
———————————————————-
1 recursive calls
0 db block gets
288 consistent gets
0 physical reads
0 redo size
1402 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed

— 4.使用STA调优这条sql;

1.查看这条sql的sql_id为’cy6x06rhrt54x’;

可以从v$sql或者dba_hist_sqltext视图中拿到sql_id;

2.可以使用工具包sqltrpt.sql来调优;

SQL> @?/rdbms/admin/sqltrpt.sql

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name                  : TASK_62
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 07/26/2014 10:45:03
Completed at                      : 07/26/2014 10:45:03
Number of SQL Profile Findings    : 1

——————————————————————————-
Schema Name: SYS

SQL ID : cy6x06rhrt54x
SQL Text : SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE
‘%T1%’ AND t1.object_id = t2.object_id

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 49.32%)

——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, replace
=> TRUE);

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original With Adjusted Cost
——————————
Plan hash value: 1838229974

—————————————————————————
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    26 | 1040 |   212   (3)| 00:00:03 |
|* 1 | HASH JOIN         |      |    26 | 1040 |   212   (3)| 00:00:03 |
|* 2 |   TABLE ACCESS FULL| T1   |    26 |   754 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

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

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

2- Using SQL Profile
——————–
Plan hash value: 1164754026

——————————————————————————–
———-
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)|
Time     |
——————————————————————————–
———-
|   0 | SELECT STATEMENT            |            |    26 | 1040 |   107   (2)|

00:00:02 |
|   1 | TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |
|   2 |   NESTED LOOPS              |            |    26 | 1040 |   107   (2)|
00:00:02 |
|* 3 |    TABLE ACCESS FULL        | T1         |    26 |   754 |    55   (4)|
00:00:01 |
|* 4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |
——————————————————————————–
———-

Predicate Information (identified by operation id):

—————————————————

3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

3.从结果发现STA找到了最优的执行计划,T1表上的条件过滤后的结果集记录数为26,非常准确,所以可以选择接受这个SQL Profile;

SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, REPLACE=> TRUE);

PL/SQL procedure successfully completed.

— 5.查看SQL Profile做了什么;

1.查看SQL Profile信息;

SQL> SELECT NAME, CATEGORY, signature, sql_text FROM dba_sql_profiles;

NAME CATEGORY SIGNATURE
—————————— —————————— ———-
SQL_TEXT
——————————————————————————–
SYS_SQLPROF_0151dc24f6338000 DEFAULT 8.9755E+18
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
_id = t2.object_id

2.在sys.sqlprof$attr数据字典(11g中为SQLOBJ$,SQLOBJ$AUXDATA,SQLOBJ$DATA字典)中,有signature和attr_val两列重要信息;

1.在10g之后的v$sql视图中有两列exact_matching_signature,force_matching_signature信息,用来表示sql的特征,可以用于查找没有使用绑定变量的sql语句;

2.对于dbms_sqltune.accept_sql_profile过程中,如果force_match=>true的话表示dba_sql_profile中的signature由sql的force_matching_signature而来,否则就是exact_matching_signature;

3.这也验证了SQL Profile比outlines好的一点,因为它也支持没有使用绑定变量的SQL;

4.attr_val实际上就是一些hints,和outline本质是一样的,只是SQL Profile中的hints没有指定sql使用的索引和表连接的方式及顺序;这里仅仅提供了一些辅助信息,即评估了表返回记录的行数,与原始的评估返回的行数的放大/缩小倍数;

5.计算0.0104*2500=26,就告诉CBO,T1表经过谓词过滤返回行数应该为评估的0.0104倍;

6.这里也验证了SQL Profile并不会锁定SQL的执行计划,只是提供更多,更准确的统计信息给CBO参考;

SQL> SELECT * FROM sys.sqlprof$attr;

SIGNATURE CATEGORY ATTR# ATTR_VAL
———– ——– ———- ——————————————————————————–

8.9755E+18 DEFAULT 1 OPT_ESTIMATE(@”SEL$1″, TABLE, “T1″@”SEL$1”, SCALE_ROWS=0.0104)

3.修改谓词条件?如果执行sql:SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%’ AND t1.object_id = t2.object_id;

1.在10.2.0.1版本中:则还是会走没有使用SQL Profile的执行计划;

2.也许在其它的版本中会使用SQL Profile,但是评估的T1表返回的记录数应该和LIKE ‘%T1%’一样,以为这个SQL Profile是为这个条件生成的;

4.修改表的统计信息,进一步验证;

1.将T1表的统计信息修改为500W,则Oracle会评估返回500W*5%*0.0104=2600行记录;执行计划变为FULL TABLE SCAN + HASH JOIN;

2.虽然SQL Profile起了所用,但是并没有锁定执行计划;

SQL> EXEC dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows=>5000000);

SQL> EXPLAIN PLAN FOR SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1838229974

—————————————————————————
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      | 2600 |   101K|   401 (49)| 00:00:05 |
|* 1 | HASH JOIN         |      | 2600 |   101K|   401 (49)| 00:00:05 |
|* 2 |   TABLE ACCESS FULL| T1   | 2600 | 75400 |   245 (79)| 00:00:03 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

Note
—–
– SQL profile “SYS_SQLPROF_0151dc24f6338000” used for this statement

———————- 如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理 ———————-

———————- How to Move SQL Profiles from One Database to Another ———————-

— 1.实验环境;

1.源端数据库信息;

SQL> SELECT * FROM v$version;

2.目标端数据库信息;

SQL> SELECT * FROM v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

— 2.准备测试数据,在源端和目标端分别创建测试表,结果都是走全表扫描;

SQL> conn / as sysdba
SQL> ALTER USER hr IDENTIFIED BY oracle ACCOUNT UNLOCK;
SQL> conn hr/oracle
SQL> CREATE TABLE emp AS SELECT * FROM employees;
SQL> ALTER TABLE emp ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID) USING INDEX;
SQL> SET AUTOT ON
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 3956160932

————————————————————————–
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT |      |     4 |   100 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP |     4 |   100 |     3   (0)| 00:00:01 |
————————————————————————–

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

1 – filter(“EMPLOYEE_ID”=198)

Statistics
———————————————————-
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
482 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

— 3.在源端使用STA进行SQL调优,然后并使用给出的建议生成一个SQL Profile;

DECLARE
my_sta_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198’;
my_sta_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
user_name => ‘HR’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task’);
dbms_sqltune.execute_tuning_task(task_name => my_sta_name);
dbms_sqltune.accept_sql_profile(task_name => my_sta_name, NAME => ‘my_sql_profile’);
END;
/

— 4.在源端查看相应的改变;

1.查看生成的profile信息;

SELECT * FROM dba_sql_profiles WHERE name = ‘my_sql_profile’;

2.查看sql的执行计划改变;

SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 2864363232

——————————————————————————–
———

——————————————————————————–
———

| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 0
0:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 9 | 1 (0)| 0
0:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP_ID | 1 | | 0 (0)| 0
0:00:01 |

——————————————————————————–
———

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

2 – access(“EMPLOYEE_ID”=198)

Note
—–
– SQL profile “my_sql_profile” used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
482 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

— 5.在源端打包SQL Profiles并导出;

1.打包SQL Profiles(表名和schema名严格区分大小写);

BEGIN
dbms_sqltune.create_stgtab_sqlprof(table_name => ‘TB_SQLP_STAGE’, schema_name => ‘HR’);
dbms_sqltune.pack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
staging_table_name => ‘TB_SQLP_STAGE’,
staging_schema_owner => ‘HR’);
END;
/

2.导出对应的stage table;

[oracle@singleton10g ~]$ expdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE;

Export: Release 10.2.0.1.0 – Production on Friday, 25 July, 2014 11:49:29

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “HR”.”SYS_EXPORT_TABLE_01″: hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HR”.”TB_SQLP_STAGE” 9.257 KB 1 rows
Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ORCL/dpdump/sqlp.dmp

Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 11:49:33

3.把dump文件放到目标端;

— 6.在目标端导入并解包SQL Profiles;

1.导入对应的stage table;

[oracle@singleton11g ~]$ impdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE

Import: Release 11.2.0.3.0 – Production on Fri Jul 25 11:52:37 2014

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “HR”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_IMPORT_TABLE_01″: hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “HR”.”TB_SQLP_STAGE” 9.257 KB 1 rows
Job “HR”.”SYS_IMPORT_TABLE_01″ successfully completed at 11:53:05

2.解包SQL Profiles(对应的SQL Profile被添加到了数据字典中,可以通过dba_sql_profiles查看);

BEGIN
dbms_sqltune.unpack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
REPLACE => TRUE,
staging_table_name => ‘TB_SQLP_STAGE’,
staging_schema_owner => ‘HR’);
END;
/

— 7.在目标端查看对应的执行计划;

SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 2864363232

——————————————————————————–
———

——————————————————————————–
———

| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 0
0:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 9 | 1 (0)| 0
0:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP_ID | 1 | | 0 (0)| 0
0:00:01 |

——————————————————————————–
———

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

2 – access(“EMPLOYEE_ID”=198)

Note
—–
– SQL profile “my_sql_profile” used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
497 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

———————- How to Move SQL Profiles from One Database to Another ———————-

———————- 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles ———————-

— 1.实验环境;

SQL> SELECT * FROM v$version;

— 2.相关介绍;

1.存储过程:在帮助文档中并没有介绍,但是可以通过查看包体的信息;

SQL> desc dbms_sqltune

PROCEDURE IMPORT_SQL_PROFILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SQL_TEXT                       CLOB                    IN
PROFILE                        SQLPROF_ATTR            IN
NAME                           VARCHAR2                IN     DEFAULT
DESCRIPTION                    VARCHAR2                IN     DEFAULT
CATEGORY                       VARCHAR2                IN     DEFAULT
VALIDATE                       BOOLEAN                 IN     DEFAULT
REPLACE                        BOOLEAN                 IN     DEFAULT
FORCE_MATCH                    BOOLEAN                 IN     DEFAULT

2.SYS.SQLPROF_ATTR是一个VARCHAR2的集合类型;

SELECT * FROM dba_source WHERE owner = ‘SYS’ AND NAME = ‘SQLPROF_ATTR’;

TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)

— 3.还原环境;

BEGIN
dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_0151dc24f6338000’);
END;
/
BEGIN
dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows => 5000);
END;
/

— 4.稳定现有的SQL的执行计划;

1.查看sql的信息;

SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql;

SQL_ID PLAN_HASH_VALUE
————- —————
SQL_TEXT
——————————————————————————–
cy6x06rhrt54x 1838229974
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
_id = t2.object_id

2.10g开始,v$sql_plan中包括了SQL语句的outlines数据,即稳定执行计划的hints;

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(‘cy6x06rhrt54x’,NULL,’outline’));

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID cy6x06rhrt54x, child number 0
————————————-
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND
t1.object_id = t2.object_id

Plan hash value: 1838229974

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 212 (100)| |

PLAN_TABLE_OUTPUT
——————————————————————————–
|* 1 | HASH JOIN         |      | 2500 |    97K|   212   (3)| 00:00:03 |
|* 2 |   TABLE ACCESS FULL| T1   | 2500 | 72500 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “T1″@”SEL$1″)
FULL(@”SEL$1” “T2″@”SEL$1″)
LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)
USE_HASH(@”SEL$1” “T2″@”SEL$1”)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

3.SQLT工具中的coe_xfr_sql_profile.sql脚本:这个脚本可以用于从shared pool,awr中提取指定的SQL ID的Outline Data并创建SQL Profile;

1.脚本需要输入sql的sql_id和plan_hash_value,然后会生成一个coe_xfr_sql_profile_${sql_id}_${plan_hash_value}.sql的脚本;

2.脚本中的force_match参数默认是FALSE,可以修改为TRUE,也可以修改脚本其它的内容;

SQL>@/home/oracle/coe_xfr_sql_profile.sql cy6x06rhrt54x 1838229974

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1838229974 .087
1164754026

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : “cy6x06rhrt54x”
PLAN_HASH_VALUE: “1838229974”

SQL>BEGIN
2    IF :sql_text IS NULL THEN
3      RAISE_APPLICATION_ERROR(-20100, ‘SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).’);
4    END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2    IF :other_xml IS NULL THEN
3      RAISE_APPLICATION_ERROR(-20101, ‘PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).’);
4    END IF;
5 END;
6 /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql
on TARGET system in order to create a custom SQL Profile
with plan 1838229974 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>@coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql

SQL>DECLARE
2 sql_txt CLOB;
3 h       SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 — SQL Text pieces below do not have to be of same length.
12 — So if you edit SQL Text (i.e. removing temporary Hints),
13 — there is no need to edit or re-align unmodified pieces.
14 wa(q'[SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1]’);
15 wa(q'[%’ AND t1.object_id = t2.object_id
16   ]’);
17 DBMS_LOB.CLOSE(sql_txt);
18 h := SYS.SQLPROF_ATTR(
19 q'[BEGIN_OUTLINE_DATA]’,
20 q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
21 q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
22 q'[ALL_ROWS]’,
23 q'[OUTLINE_LEAF(@”SEL$1″)]’,
24 q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
25 q'[FULL(@”SEL$1” “T2″@”SEL$1″)]’,
26 q'[LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)]’,
27 q'[USE_HASH(@”SEL$1” “T2″@”SEL$1″)]’,
28 q'[END_OUTLINE_DATA]’);
29 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
30 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
31 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
32 sql_text    => sql_txt,
33 profile     => h,
34 name        => ‘coe_cy6x06rhrt54x_1838229974’,
35 description => ‘coe cy6x06rhrt54x 1838229974 ‘||:signature||’ ‘||:signaturef||”,
36 category    => ‘DEFAULT’,
37 validate    => TRUE,
38 replace     => TRUE,
39  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
40 DBMS_LOB.FREETEMPORARY(sql_txt);
41 END;
42 /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

SIGNATURE
———————
8975541025552400288

SIGNATUREF
———————
3960696072677096522

… manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_cy6x06rhrt54x_1838229974 completed

— 5.在不能修改SQL的情况下改变并固定SQL的执行计划,即使原来的SQL使用了hints;

1.一般遇到sql不能修改,或者sql使用了错误hints的情况下有三种办法修改sql的执行计划;

1.调整统计信息:不可靠,使用比较复杂,而且统计信息会自动收集;

2.使用outline:比较复杂;

3.使用SQL Profiles:本质和OUTLINE是一样的;

2.一般的工作流;

1.取得原始的SQL文本,包括sql_id;

2.构造一个与原始SQL在逻辑上,结构上完全相同的SQL(逻辑和结构相同即可,即sql解析的用户,引用对象的schema,甚至是谓词条件可以不同);

3.执行我们构造的SQL,生成Outline Data;

4.使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile;

3.构建SQL;

1.FULL TABLE SCAN + HASH JOIN的方式:SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;
2.INDEX RANGE SCAN + NESTED LOOP的方式:SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

3.查看sql_id和plan_hash_value;

SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE ‘%sql_profile%’ AND sql_id IN (‘8cukn1u7dxssh’,’6csfwp8k7qu49′)
2 ;

SQL_ID PLAN_HASH_VALUE
————- —————
SQL_TEXT
——————————————————————————–
8cukn1u7dxssh 1838229974
SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM
t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id

6csfwp8k7qu49 1164754026
SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */ t1.*, t2.owner FROM t1, t2 WH
ERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id

4.分别使用coe_xfr_sql_profile.sql脚本生产两个sql的sql_profile创建脚本;

SQL>@/home/oracle/coe_xfr_sql_profile.sql 8cukn1u7dxssh 1838229974 — coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql
SQL>@/home/oracle/coe_xfr_sql_profile.sql 6csfwp8k7qu49 1164754026 — coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql

5.然后把coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql中构建SYS.SQLPROF_ATTR的部分和force_match => TRUE地方替换掉coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql脚本中相应内容;

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]’,
q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
q'[ALL_ROWS]’,
q'[OUTLINE_LEAF(@”SEL$1″)]’,
q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
q'[INDEX(@”SEL$1” “T2″@”SEL$1” (“T2″.”OBJECT_ID”))]’,
q'[LEADING(@”SEL$1″ “T1″@”SEL$1” “T2″@”SEL$1″)]’,
q'[USE_NL(@”SEL$1” “T2″@”SEL$1”)]’,
q'[END_OUTLINE_DATA]’);

6.然后执行,并查看执行计划;

SQL>@/home/oracle/coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql

SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1164754026

——————————————————————————–
———-

——————————————————————————–
———-

PLAN_TABLE_OUTPUT
——————————————————————————–
| 0 | SELECT STATEMENT | | 250 | 10000 | 554 (1)|
00:00:07 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|
00:00:01 |

| 2 | NESTED LOOPS | | 250 | 10000 | 554 (1)|
00:00:07 |

|* 3 | TABLE ACCESS FULL | T1 | 250 | 7250 | 53 (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–

|* 4 | INDEX RANGE SCAN | IDX_T2_OID | 1 | | 1 (0)|
00:00:01 |

——————————————————————————–
———-

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

PLAN_TABLE_OUTPUT
——————————————————————————–
3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note
—–
– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement

— 7.改变条件,再次查看执行计划,发现SQL的执行计划被固定了;

SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%‘ AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1164754026

——————————————————————————–
———-

——————————————————————————–
———-

PLAN_TABLE_OUTPUT
——————————————————————————–
| 0 | SELECT STATEMENT | | 250 | 10000 | 554 (1)|
00:00:07 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|
00:00:01 |

| 2 | NESTED LOOPS | | 250 | 10000 | 554 (1)|
00:00:07 |

|* 3 | TABLE ACCESS FULL | T1 | 250 | 7250 | 53 (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–

|* 4 | INDEX RANGE SCAN | IDX_T2_OID | 1 | | 1 (0)|
00:00:01 |

——————————————————————————–
———-

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

PLAN_TABLE_OUTPUT
——————————————————————————–
3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T2%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note
—–
– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement

———————- 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles ———————-

性能调优工具04–SQLT Diagnostic Tool

Posted on 2014-07-15 by royalwzy

SQLT Diagnostic Tool

1.SQLT介绍:SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

2.安全模式:SQLT在安装的过程中会创建两个用户和一个角色,这些用户和角色的名字都是固定的;

1.SQLTXPLAIN用户:用来管理SQLT repository,SQLT的使用者每次使用SQLT提供的主要方法时都要提供SQLTXPLAIN的密码;SQLTXPLAIN用户被赋予了CREATE SESSION,CREATE TABLE系统权限;
2.SQLTXADMIN用户:用来管理SQLT包含的PL/SQL程序包以及视图,SQLTXADMIN用户处于锁定状态并且由一个随机产生的密码保护;SQLTXADMIN用户被赋予了ADMINISTER SQL MANAGEMENT OBJECT,ADMINISTER SQL TUNING SET,ADVISOR,ALTER SESSION,ANALYZE ANY,SELECT ANY DICTIONARY,SELECT_CATALOG_ROLE系统权限;
3.SQLT_USER_ROLE角色:所有SQLT的使用者在使用SQLT提供的主要方法之前必须被赋予这个角色;SQLT_USER_ROLE角色被赋予了ADVISOR,SELECT_CATALOG_ROLE系统权限;

4.TIPS:12c在缺省情况下SYS用户不能作为SQLT的用户,因为PL/SQL安全模型改变的原因,需要单独对SQLTADMIN授权(GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN);

3.安装:

1.下载并解压到相应的目录;(可以查看目录下的sqlt_instructions.html安装文档)

2.安装:conn / as sysdba; SQL> @/tools/sqlt/install/sqcreate.sql;需要提供以下信息:

1.连接标识符(可选,当安装在一个PDB上时是必须的):一般不输入直接回车;

2.SQLTXPLAIN密码;
3.SQLTXPLAIN默认表空间:它必须具有50MB以上的可用空间;
4.SQLTXPLAIN临时表空间;
5.应用程序用户(可选):指定发出要分析SQL语句的用户(EBS中为APPS,Siebel中为SIEBEL,PeopleSoft中为SYSADM),也可以安装之后再添加用户,必须授予其SQLT_USER_ROLE角色;
6.授权的Oracle Pack(T,D,N):T表示Oracle Tuning,D表示Oracle Diagnostic,或N表示None;如果选择T或D,SQLT可以在它生成的诊断文件中包含授权的内容;默认值为T,如果选择N,SQLT将只安装限定的功能;

3.卸载:conn / as sysdba; SQL> @/path/sqlt/install/sqdrop.sql;

4.升级:只需要执行安装的过程即可,如果升级失败,则需要执行卸载然后安装;

5.tips:

1.如果安装时没有设置连接标示符,那么可以之后自己设置:EXEC sqltxadmin.sqlt$a.set_sess_param(‘connect_identifier’, ‘@network_name’);

2.如果安装时没有指定添加用户,那么可以CREATE USER sqlt;GRANT sqlt_user_role TO sqlt;

4.主要方法:

1.XTRACT方法:

1.如果知道待分析SQL的SQL_ID或HASH_VALUE,可以使用此方法,否则使用XECUTE;可以在AWR report中找到SQL_ID,在SQL trace中找到HASH_VALUE(在SQL文本上面,通过”hv=”标记进行标识);
2.如果对SQL进行硬分析时将参数STATISTICS_LEVEL设置为ALL,将可以得到重要的性能统计信息(如每步操作的实际行数);也可以通过在SQL中包括以下CBO提示来生成同样有用的性能统计信息:/*+ GATHER_PLAN_STATISTICS */;在11g中,您可以在SQL中包含以下CBO提示以获得增强的诊断信息:/*+ GATHER_PLAN_STATISTICS MONITOR */;
3.使用方法:SQL> @/path/sqlt/run/sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];

2.XECUTE方法:

1.与XTRACT方法相比,此方法提供的信息更为详细;正如名称XECUTE所指示的,它将执行正在分析的SQL,然后生成一组诊断文件;

2.它的主要缺点是如果正在分析的SQL需要很长时间来执行,那么该方法也要花费很长的时间;根据经验法则,仅当SQL执行少于1小时的情况下,才考虑使用此方法,否则请使用XTRACT;
3.使用此XECUTE方法之前,必须创建一个包含SQL文本的文本文件;如果SQL包括绑定变量,则您的文件必须包含绑定变量声明和赋值;以sqlt/input/sample/script1.sql为例;您的SQL应该包含标记/* ^^unique_id */;

4.使用方法:SQL> @/path/sqlt/run/sqltxecute.sql /path/scriptname [sqltxplain_password];

3.XTRXEC方法:

1.该方法合并了XTRACT和XECUTE的功能;实际上,XTRXEC连续执行了这两种方法;针对所请求SQL语句找到的开销较大的计划,XTRACT阶段将生成一个包含提取的SQL以及绑定声明和赋值的脚本;然后,XTRXEC使用第一阶段创建的脚本执行XECUTE阶段;
2.SQLT根据在内存中生成开销最大的执行计划时窥视到的值,创建脚本的绑定变量的以供XTRACT使用;判断计划的开销大小的标准是基于这个计划的平均执行时间;
3.如果XTRXEC仅执行了第一个阶段(XTRACT)后就输出错误,您可能需要检查在第二阶段(XECUTE)使用的脚本并相应调整绑定变量;使用不常用数据类型时尤其需要进行调整;

4.使用方法:SQL> @/path/sqlt/run/sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];

4.XTRSBY方法:

1.如果需要分析在DataGuard或备用只读数据库上执行的SQL,请使用该方法;您需要知道要分析的SQL的SQL_ID或HASH_VALUE;
2.在主库上创建一个到备库的database link,连接到的用户需要有访问数据字典的权限,通常都是使用有DBA权限的用户;
3.如果对只读数据库中的SQL进行硬分析时将参数STATISTICS_LEVEL设置为ALL,将可以得到重要的性能统计信息(如每个执行计划操作的实际行数);您也可以通过在SQL中包括以下CBO提示来生成同样有用的性能统计信息:/*+ GATHER_PLAN_STATISTICS */;在11g中,您可以在SQL中包含以下CBO提示以获得改进的诊断信息:/*+ GATHER_PLAN_STATISTICS MONITOR */;

4.使用方法:SQL> @/path/sqlt/run/sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK];

5.XPLAIN方法:

1.该方法是基于EXPLAIN PLAN FOR命令执行的,因此它将无视您的SQL语句引用的绑定变量;仅当无法使用XTRACT或XECUTE时才使用该方法;
2.使用此XPLAIN方法之前,必须创建一个包含SQL文本的文本文件;如果SQL包括绑定变量,您有两个选择:保持SQL文本”不变”,或谨慎使用相同数据类型的字面值替换该绑定;以sqlt/input/sample/sql1.sql为例;

3.使用方法:SQL> @/path/sqlt/run/sqltxplain.sql /path/scriptname [sqltxplain_password];

6.XPREXT方法:

1.假如您想使用XTRACT同时希望禁用一些SQLT的特性使之执行更快,请使用这个方法;脚本sqlt/run/sqltcommon11.sql显示了哪些特性被禁用;

2.使用方法:SQL> @/path/sqlt/run/sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];

7.XPREXC方法:

1.假如您想使用XECUTE同时希望禁用一些SQLT的特性使之执行更快,请使用这个方法;脚本sqlt/run/sqltcommon11.sql显示了哪些特性被禁用;

2.使用方法:SQL> @/path/sqlt/run/sqltxprexc.sql /path/scriptname [sqltxplain_password];

8.TIPS:

1.XTRACT,XECUTE,XTRXEC,XTRSBY,XPREXT和XPREXC处理绑定变量和会做bind peeking(绑定变量窥视),但是XPLAIN不会;这是因为XPLAIN是基于EXPLAIN PLAN FOR命令执行的,该命令不做bind peeking;因此,如果可能请避免使用XPLAIN;

2.除了XPLAIN的bind peeking限制外,所有这7种主要方法都可以提供足够的诊断详细信息,对性能较差或产生错误结果集的SQL进行初步评估;如果该SQL仍位于内存中或者AWR中,请使用XTRACT或XTRXEC,其他情况请使用XECUTE;

3.对于DataGuard或备用只读数据库,请使用XTRSBY;

4.仅当其他方法都不可行时,再考虑使用XPLAIN;

5.XPREXT和XPREXC是类似于XTRACT和XECUTE,但为了提高SQLT的性能它们禁了一些SQLT的特性;

5.PROFILE方法:

1.当性能较差的SQL语句正好有已知的更好的执行计划时,在10g使用该PROFILE方法可以提供一个快速修复;

2.这个更好的执行计划可以位于相同或不同系统中的内存中,或者位于相同或不同系统的AWR中;换句话说,如果有更好的计划,该方法允许使用自定义SQL Profile “固定”该计划;使用该方法之前,必须对您要为其提取和固定此计划的SQL使用主要方法中的任何一个;

3.在11g或更高的版本你可以使用SQL Plan Management(SPM)来代替这个方法;

6.可以查看生成文件中的html报告;

7.相关文档:SQLT Diagnostic Tool (Doc ID 215187.1),SQLT 使用指南 (Doc ID 1677588.1(CHN),Doc ID 1614107.1(EN));

性能调优工具03–OUTLINE技术暂时锁定SQL的执行计划

Posted on 2014-07-15 by royalwzy

OUTLINE技术暂时锁定SQL的执行计划

Oracle的outline技术和hint技术可以在特殊情况下保证执行计划的稳定,使用outline技术锁定执行计划的场景:
1. 短时间内无法完成sql的优化任务;
2. 在CBO模式下,当统计信息出现问题时,导致执行计划出现变化;
3. 由于数据库的bug导致sql的执行计划出现异常;
4. 使用第三方的系统,sql语句无法直接修改时;
OUTLINE相关的参数:
1. CREATE_STORED_OUTLINES:会话中执行的sql语句是否自动创建并存储为OUTLINE,初始化参数;
  1. true:开启自动创建outline的功能,系统会自动指定名称并存储在DEFAULT类别下,如果在DEFAULT类别下已经存在的话不会重复创建;
  2. false:默认值,不开启自动创建功能,推荐不要修改此参数;
  3. category_name:跟true功能一样,只是默认存在category_name类别下;
2. USE_STORED_OUTLINES:使用公有OUTLINE产生执行计划,不是初始化参数;
  1. true:使优化器使用DEFAULT类别的OUTLINE产生执行计划;
  2. false:默认值,不使用outline;
  3. category_name:是优化器使用category_name类别的outline产生执行计划;
3. USE_PRIVATE_OUTLINES:使用私有OUTLINE产生执行计划,不是初始化参数;
  1. 参数的含义与USE_STORED_OUTLINES一致;
  2. 限制:只有USE_STORED_OULINES关闭的时候才能打开此参数;
4. 都可以使用ALTER SYSTEM/ALTER SESSION语法来修改;
创建OUTLINE的语法解析:
1. 创建语法:CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE outln_name [FROM PUBLIC|PRIVATE source_outline] [FOR CATEGORY category_name] [ON statement];
2. PUBLIC:默认值,创建一个公有的OUTLINE;
3. PRIVATE:在当前的session创建一个私有的OUTLINE,存在当前的SCHEMA下;要创建私有的OUTLINE,必须要在当前SCHEMA下使用DMBS_OUTLN_EDIT.CREATE_EDIT_TABLES过程创建一个表来存储信息;
4. FROM子句:从一个现有的outline中创建一个新的outine,如果指定FROM子句就不用指定ON子句了;
5. FOR CATEGORY子句:指定OUTLINE所属的分类,如果不指定就默认属于DEFAULT分类,如果指定的分类不存在,系统自动创建该分类;
6. ON子句:要创建OUTLINE的sql语句;
  1. 当有FROM子句时不用指定;
  2. 不能是多路插入语句;
  3. 不能是远程服务器的DML操作;
7. 修改OUTLINE:ALTER OUTLINE outln_name;
8. 删除OUTLINE:DROP OUTLINE outln_name;
相关的包:
1. DBMS_OUTLN:
  1. CLEAR_USED:清除outline的USED标识;
  2. CREATE_OUTLINE:使用shared pool中的sql生成一个outline;
  3. DROP_BY_CAT:删除某一个分类下的所有outline;
  4. DROP_UNUSED:删除从来没有被使用过的outline;
  5. UPDATE_BY_CAT:修改一个outline的分类;
  6. UPDATE_SIGNATURES:修改outline的signature到当前的版本;
2. DBMS_OUTLN_EDIT:
  1. CHANGE_JOIN_POS:修改执行计划中步骤的顺序;
  2. CREATE_EDIT_TABLES:在当前schema下创建一个表用来保存私有的outline;
  3. DROP_EDIT_TABLES:删除表;
相关的视图:
1. DBA_OUTLINE:所有的outline信息,其中USED表示此outline是否被使用过,SIGNATURE是sql语句的唯一标示符;
2. DBA_OUTLINE_HINTS:所有outline的执行计划;
3. 与OUTLINE相关的数据都保存了OUTLN用户下的对象中了:
  1. ALTER USER outln IDENTIFIED BY outln ACCOUNT UNLOCK;
  2. outln.ol$:outline的信息;
  3. outln.ol$hints:保存执行计划;
  4. outln.ol$nodes:OUTLINE节点信息;
创建outline;
使用outline;
清除outline:
1. 可以使用DMBS_OUTLN包来实现删除某个分类的outline;
2. 使用drop outline语法删除单个个outline;
把一个环境中的outline应用于其它环境:使用expdp导出当前环境中outln schema,然后impdp导入到其它环境即可;
如果想要创建一个使用变量的outline,可以在创建outline时使用绑定变量,例子;
关于HINTS:
1. 最后才使用的方法;
2. hints是硬编码;
3. Hints apply to only the statement block in which they appear:只能用在第一个sql上,如果有子查询,要单独使用hints;
4. 建议使用表的别名;
5. HINTS写错的话,会当成注释;

————————– 创建OUTLINE ————————–

— 1.在hr用户下创建测试表;

CREATE TABLE tbobjects AS SELECT * FROM dba_objects;

— 2.创建一个outline,并属于CATE分类;

CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = 100;

— 3.查看outln.ol$hints中保存的执行计划;

SELECT hint_text FROM outln.ol$hints
WHERE ol_name = ‘OL_OBJECTS’
ORDER BY HINT#;

— 4.查看真正的执行计划;

————————– 创建OUTLINE ————————–

————————– 使用OUTLINE ————————–

— 1.在表上创建索引,来改变执行计划;

CREATE INDEX IDX_TBOBJECTS_ID ON TBOBJECTS (object_id);

— 2.查看当前实际的执行计划;

— 3.查看outline是否被使用过;

SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;

— 4.强制会话使用outline中保存的执行计划,要首先设置当前会话的CATEGORY为CATE:ALTER SESSION SET USE_STORED_OUTLINES = CATE;

— 5.查看outline是否被使用;

SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;

— 6..消除outline对sql执行计划的影响;

方法1.ALTER SESSION SET USE_STORED_OUTLINE = FALSE;

方法2.ALTER OUTLINE ol_objects DISABLE;

————————– 使用OUTLINE ————————–

————————– 创建OUTLINE时使用绑定变量 ————————–

— 1.创建一个outline

CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = :object_id;

— 2.设置当前会话使用此outline

ALTER SESSION SET USE_STORED_OUTLINES = CATE;

— 3.使用绑定变量调用;

variable object_id NUMBER;

exec :object_id := 100;

SELECT * FROM tbobjects WHERE object_id = :object_id;

— 4.查看outline是否被使用;

SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;

————————– 创建OUTLINE时使用绑定变量 ————————–

调优实践04–利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句

Posted on 2014-06-15 by royalwzy

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句
1.10g之后的v$sql视图增加了两列:
1.EXACT_MATCHING_SIGNATURE:Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
2.FORCE_MATCHING_SIGNATURE:Signature used when the CURSOR_SHARING parameter is set to FORCE,也就是SQL语句在CURSOR_SHARING=FORCE模式下运行的签名值;
2.控制共享游标的参数:CURSOR_SHARING
1.FORCE:在不改变语义的情况下都使用游标共享,可能使用的并不是最优的执行计划,强制绑定变量,并在左边的列进行to_char转换;
2.EXACT:默认值,只有在SQL语句完全一致的情况下才共享游标;
3.SIMILAR:在不改变语义或者不降低性能的情况下,尽量采用游标共享,一般用于第三方的系统,无法修改源码的情况;
3.执行计划相关内容:
1.v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
2.查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
4.在CURSOR_SHARING=EXACT模式下查看使用字面值的sql语句;
4.1创建测试表;
CREATE TABLE tb_sqltuning AS SELECT * FROM dba_objects;
ALTER TABLE TB_SQLTUNING ADD CONSTRAINT PK_sqltuning_sqlid PRIMARY KEY (OBJECT_ID);
4.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
4.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
4.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
4.5结论;
每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;(plan_hash_value相同更说明了,不同的sql产生的执行计划是相同的,需要优化)
5.在CURSOR_SHARING=EXACT模式下查看使用绑定变量的sql语句;
5.1清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
5.2执行测试sql;
VARIABLE id NUMBER;
EXEC :id := 1;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 2;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 3;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 4;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 5;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 6;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
5.3查看sql信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_BIND%’ AND
sql_text NOT LIKE ‘%LIKE%’;
5.4结论;
只进行一次硬结析,之后的语句使用相同的执行计划,它们的exact_matching_signature和force_matching_signature都相同;
硬结析了三次是应为11g里面SPM(Sql Plan Management)的特性,它可以自动学习并选择最优的执行计划,最大的优点是通过避免执行计划的退化,从而保证系统性能的稳定;10g的话第一次产生执行计划就固定了,很可能不是最优的执行计划;
1.第一次执行SQL的时候跟原来一样,什么也不做,只是把执行计划保存在Library Cache中;
2.第二次执行SQL的时候会产生Plan History,而且Plan Baseline为空,把这次的执行计划放到Plan Baseline中,并且固定执行计划;
3.第三次执行SQL的时候,会把产生的执行计划放入到Plan History中,但是不会进入Plan Baseline;
6.修改参数,在CURSOR_SHARING=SIMILAR模式下查看使用字面值的sql语句;
6.1修改参数;
ALTER SYSTEM SET cursor_sharing = SIMILAR
6.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
6.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
6.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
6.5结论;
跟在EXACT模式下使用绑定变量的结果一致:每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;
7.How to Find Literal SQL in Shared Pool;
DECLARE
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;

CURSOR my_statement IS
SELECT address FROM v$sql GROUP BY address;
CURSOR getsqlcode IS
SELECT substr(sql_text, 1, 60) FROM v$sql WHERE address = b_myadr;
CURSOR kglcur IS
SELECT kglhdadr
FROM x$kglcursor
WHERE kglhdpar = b_myadr AND
kglhdpar != kglhdadr AND
kglobt09 = 0;
CURSOR isthisliteral IS
SELECT kkscbndt FROM x$kksbv WHERE kglhdadr = b_myadr1;
BEGIN
dbms_output.enable(10000000);
OPEN my_statement;
LOOP
FETCH my_statement
INTO b_myadr;
OPEN kglcur;
FETCH kglcur
INTO b_myadr1;
IF kglcur%FOUND THEN
OPEN isthisliteral;
FETCH isthisliteral
INTO b_anybind;
IF isthisliteral%NOTFOUND THEN
OPEN getsqlcode;
FETCH getsqlcode
INTO qstring;
dbms_output.put_line(‘Literal:’ || qstring || ‘ address: ‘ || b_myadr);
CLOSE getsqlcode;
END IF;
CLOSE isthisliteral;
END IF;
CLOSE kglcur;
EXIT WHEN my_statement%NOTFOUND;
END LOOP;
CLOSE my_statement;
END;

调优实践03–使用SQL_TRACE和10046事件跟踪SQL的执行

Posted on 2014-06-15 by royalwzy

使用SQL_TRACE和10046事件跟踪SQL的执行

当在数据库中启动SQL_TRACE或者设置10046事件之后,Oracle将会启动内核跟踪程序,持续记录会话的相关信息,并写入到相应的trace文件中;跟踪记录的内容包括SQL的解析过程,执行计划,绑定变量的使用和会话中发生的等待事件等;
SQL_TRACE和10046事件介绍:
1. SQL_TRACE:
  1. 是一个静态的初始化参数,可以设置为TRUE/FALSE,用于开启/关闭SQL TRACE工具默认为FALSE;
  2. 设置SQL_TRACE=TRUE的话可以收集信息用于性能优化(DBMS_SYSTEM包可以实现相同的功能),但是对数据库会产生严重的性能问题,生产环境一定不要打开此参数,如果一定要在全局打开,要做到以下几点:
    1. 保证25%的CPU idle;
    2. 为USER_DUMP_DEST分配足够的空间:ALTER SYSTEM SET max_dump_file_size=UNLIMITED;
    3. 条带化磁盘,减轻IO负担;
    4. 设置timed_statistics打开:ALTER SYSTEM SET timed_statistics=TRUE;
  3. 打开SQL_TRACE功能:
    1. 全局打开:ALTER SYSTEM SET SQL_TRACE=TRUE SCOPE=SPFILE;重启数据库服务,会跟踪所有进程的活动,包括用户进程和后台进程;可以通过跟踪文件的实时变化来分析各个进程之间的协作关系;
    2. Session级别打开:ALTER SESSION SET SQL_TRACE=TRUE/FALSE;通过跟踪当前进程,来发现后台数据库的递归活动,用于研究SQL执行和发现后台错误等;
    3. 如果要跟踪其它用户的进程,可以通过DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成,所需要的sid和serial#参数可以通过v$session视图查看得到;
    4. 如果要针对其它用户的参数进行设置,可以通过DBMS_SYSTEM.SET_INI_PARAM_IN_SESSION过程或者DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION过程来完成;
  4. 其它方式:EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE/FALSE);
2. 10046事件:
  1. 是Oracle提供的内部事件,是对SQL_TRACE的增强;
  2. 10046分为四个级别:
    1. level 1:启用标准的sql_trace功能,等于sql_trace(包含了SQL语句,响应事件,服务时间,处理的行数,物理读和写的数目,执行计划以及其它一些额外信息);
    2. level 4:level 1加上绑定值;
    3. level 8:level 1加上等待事件;
    4. level 12:level 1 + level 4 + level 8;
  3. 设置10046事件:
    1. 全局开启:在spfile中添加events=”10046 trace name context forever, level 12″:ALTER SYSTEM SET EVENTS ‘10046 trace name context forever, level 12’;
    2. 全局关闭:ALTER SYSTEM SET EVENTS ‘10046 trace name context off’;
    3. 针对当前session的开启:ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;
    4. 针对当前session的关闭:ALTER SESSION SET EVENTS ‘10046 trace name context off’;
    5. 针对其它用户session的开启:使用DBMS_SYSTEM.SET_EV(si in integer, se in integer, ev in integer, le in integer, nm in varchar):EXEC DBMS_SYSTEM.SET_EV(38,25,10046,12,’HR’);
      1. si:sid;
      2. se:serial#;
      3. ev:event number;
      4. le:level;
      5. nm:username;
      6. 可以通过v$session视图查询:SELECT sid, serial#, username FROM v$session WHERE username IS NOT NULL;
    6. 针对其它用户session的关闭:EXEC DBMS_SYSTEM.SET_EV(38,25,10046,0,’HR’);
3. 也可以使用oradebug工具或者DBMS_MONITOR包;
4. 获取跟踪文件的脚本;
5. 获取当前session设置的参数的脚本;
实例分析步骤:
1. 隐式转换与索引失效:
  1. 问题描述:反应前端程序某个功能非常慢;
  2. 首先检查并跟踪数据库进程:SELECT sid, serial#, username FROM v$session WHERE username IS NOT NULL AND username <> ‘SYS’;
  3. 然后对这几个进程开启sql trace:EXEC dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);等待一段时间后关闭:EXEC dbms_system.set_sql_trace_in_session(sid, serial#, FALSE);
  4. 针对产生的trace文件使用tkprof工具进行格式化,然后查看内容;
  5. 一般发生索引失效或者是隐式转换的话就会发现返回少量的数据但是却产生特别多的物理读(也有可能是汇总操作);
  6. 可以查看关键列的索引和索引列的类型:SELECT index_name, table_name, column_name FROM user_ind_columns WHERE table_name = upper(‘tb_name’);DESC tb_name;
  7. 然后可以针对发现的问题做相应的处理,比如创建索引,手动处理隐式转换等问题;
2. 对数据库进行操作时(如drop table/user)发生错误:
  1. 问题描述:对数据库操作后发生了ORA错误;
  2. 首先在当前的会话开启一个标识符:ALTER SESSION SET TRACEFILE_IDENTIFIER='<TRACEFILE_IDENTIFIER>’;
  3. 然后打开sql trace功能:ALTER SESSION SET SQL_TRACE=TRUE;
  4. 重现错误,即再执行当前操作;
  5. 关闭sql trace功能;
  6. 然后查看trace文件,找到具体的错误并解决;
10046查看等待事件的例子;
db_file_multiblock_read_count:
1. 表示全表扫描时,ORACLE一次I/O可以读取的数据库的数据块数,Oracle的一次I/O操作不能跨extent;
2. 最大值为((max OS I/O size)/db_block_size),一般操作系统一次I/O最大读取1M,db_block_size=8k,所以这个参数最大为128;
3. 这个参数也会受到SSTIOMAX的参数影响,这是一个内核参数,不能被修改,同时db_file_multiblock_read_count也不能超过db_block_buffer/4;
4. 在OLTP系统中一般设置为4-16,在DSS系统中可以根据需要设置更大的值;
5. 增大db_file_multiblock_read_count参数会使全表扫面的成本降低,但是在CBO下,Oracle会更倾向于使用全表扫面而不是索引扫描,db_file_multiblock_read_count与执行计划选择的例子;
为什么要使用10046事件:
1. 10046事件可以帮助我们解析SQL语句的运行状态(包括Parse/Fetch/Execute三个阶段中遇到的等待事件,消耗的物理和逻辑读,CPU时间,执行计划等等);
2. 即10046为我们揭示了SQL语句的运行情况,对于以点入手的SQL调优是很好的辅助工具,特别是在10g之前没有ASH的情况下;但整体系统调优不是10046所擅长的,需要用到AWR;
3. 10046还能帮助我们分析一些DDL维护命令的内部工作原理,比如RMAN,expdp/impdp等工具的缓慢问题等;
10046事件和10053事件的区别:
1. 10053事件是最常用的Oracle优化器optimizer跟踪trace,10053可以作为我们解释优化器为什么选择某个执行计划,但并不告诉我们这个执行计划到底运行地如何;
2. 10046并不解释optimizer优化器的工作,但它同样说明了在SQL解析parse阶段所遇到的等待事件和所消耗的CPU等资源,以及Execute执行和Fetch阶段的各项指标;
3. 简而言之10046告诉我们SQL(执行计划)运行地如何,10053告诉我们优化器为什么为这个SQL选择某个执行计划;
10046跟踪文件的阅读;

———————————– 获取跟踪文件的脚本 ———————————–

— 1.解析阶段;

PARSING IN CURSOR #11328540 len=56 dep=0 uid=84 oct=3 lid=84 tim=1396508012619901 hv=3963517665 ad=’4464298c’ sqlid=’25vmrurq3wyr1′
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=20
END OF STMT
PARSE #11328540:c=6999,e=6974,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2271004725,tim=1396508012619893

PARSING

1.#11328540:表示游标号,非常重要,后面的PARSE,EXEC,WAIT,FETCH,STAT,CLOSE阶段都需要使用这个游标号和前面的sql关联起来;

2.这个过程之后可能会产生很多的递归sql,一般是用来查询一些数据字典的,所消耗的资源和时间都非常的少;

3.len=56:表示sql的长度;

4.dep=0:表示Recursive Depth,即sql递归的深度;如果为0表示不是递归sql,如果大于0表示递归sql;

5.uid=84:表示解析这个游标的用户的UID,如果是0表示是sys;通过dba_users/user$查看;

6.oct=3:表示Oracle Command Type,即Oracle中的命令类型,与v$sql中的command_type列对应,可以通过查询v$sqlcommand视图查看具体的定义;

7.lid=84:表示Privilege User Id,即权限用户ID;

8.tim:表示timestamp时间戳,9i之后单位是ms,用来判断trace中两个点的时间差;来自v$timer视图,一个Oracle的内部时钟;

9.hv:表示sql的hash value,10g之前没有sqlid就使用hash value来定位一个sql;

10.ad:表示sqltext的地址,来自于v$sql的address列;

11.sqlid:表示对应的sql id;

12.err:如果有错误的话,代表错误代码,可以通过oerr ora xxx;

PARSE:是sql运行的第一个阶段,解析SQL语句;

1.c:表示cpu time,即消耗cpu的时间,9i之后单位是ms;

2.e:表示elapsed time,即消耗的自然时间,9i之后单位是ms;

3.p:表示physcial read,即物理的数目;

4.cr:表示consist read,即一致性读引起的buffer get数目;

5.cu:表示current read,即当前读取引起的buffer get数目;

6.mis:表示读取library cache的miss的数目,如果=0的话表示使用软解析或者更好的方式;如果大于0表示发生了硬结析;

7.r:表示rows,即处理的行数;

8.dep:表示Recursive Depth,即sql递归的深度;如果为0表示不是递归sql,如果大于0表示递归sql;

9.og:表示optimizer_mode,对应关系为:0-游标不可见/优化器环境未合理创建;1-ALL_ROWS;2-FIRST_ROWS;3-RULE;4-CHOOSE;

EXEC:sql运行的第二个阶段,执行sql语句;

FETCH:从游标中fetch数据行;

UNMAP:当游标使用临时表时,若游标关闭则使用UNMAP释放临时表相关的资源;

— 2.执行阶段;
EXEC #11328540:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2271004725,tim=1396508012620426

— 3.相关的等待;

WAIT #11328540: nam=’db file sequential read’ ela= 25707 file#=5 block#=243 blocks=1 obj#=76349 tim=1396508012647378

1.Nam:等待针对的事件名字,它的P1/P2/P3可以参考视图V$EVENT_NAME,也可以从V$SESSION/ASH中观察到等待事件;
2.ela:本操作的耗时,单位是ms;
3.p1,p2,p3:针对该事件的三个描述参数,见V$EVENT_NAME;

4.obj#:相关的对象id;

— 4.获取数据;

FETCH #11328540:c=4000,e=27513,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2271004725,tim=1396508012648208

— 5.相关的统计;

STAT #11328540 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=1 pw=0 time=27520 us)’
STAT #11328540 id=2 cnt=2 pid=1 pos=1 obj=76349 op=’INDEX RANGE SCAN EMP_DEPARTMENT_IX (cr=1 pr=1 pw=0 time=27485 us cost=1 size=6 car
d=2)’

STAT:相关行反应解释执行计划的统计信息
1.id:执行计划的行数,从1开始;
2.cnt:该数据源的行数;
3.pid:该数据源的父ID;
4.pos:在执行计划中的位置;
5.obj:对应数据源的object id;
6.op:数据源的访问操作,例如FULL TABLE SCAN;

7.cr:代表一致性读的数量
8.pr:代表物理读的数量
9.pw:代表物理写的数量
10.time:单位为ms,本步骤的耗时间;
11.cost:本操作的优化器成本;
12.size:评估的数据源大小,单位为字节;
13.card:评估的优化器基数Cardinality;

— 6.关闭游标;

CLOSE #11328540:c=0,e=21,dep=0,type=0,tim=1396508016737280

CLOSE:关闭游标;

type:关闭游标的操作类型;

0-该游标从未被缓存且执行次数小于3次,也叫hard close;

1-该游标从未被缓存但执行次数至少3次,若在session cached cursor中有free slot,则将该游标放入session cached cursor;

2-该游标从未被缓存但执行次数至少3次,该游标置入session cached cursor的条件是讲老的缓存age out掉;

3-该游标已经在缓存里;

— 7.其它,如果有绑定变量的话;

BINDS:
1.kkscoacd:是绑定变量相关的描述符;
2.Bind#0:说明是第0个变量;
3.oacdty:data type,96是ANSI fixed char;
4.oacflg:代表绑定选项的特殊标志位;
5.size:为该内存chunk分配的内存大小;
6.mxl:绑定变量的最大长度;
7.pre:precision;
8.scl:Scale;
9.kxsbbbfp:buffer point;
10.bln:bind buffer length;
11.avl:实际的值的长度;
12.flg:代表绑定状态;
13.value:实际的绑定值;

———————————– 获取跟踪文件的脚本 ———————————–

SELECT a.VALUE || b.symbol || c.instance_name || ‘_ora_’ || d.spid || ‘.trc’ trace_file
FROM (SELECT VALUE FROM v$parameter WHERE NAME = ‘user_dump_dest’) a,
(SELECT substr(VALUE, -6, 1) symbol FROM v$parameter WHERE NAME = ‘user_dump_dest’) b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND
s.sid = m.sid AND
m.statistic# = 0) d;

11g later:SELECT VALUE FROM v$diag_info WHERE NAME = ‘Default Trace File’;

———————————– 获取跟踪文件的脚本 ———————————–

———————————– 获取当前session设置的参数的脚本 ———————————–

DECLARE
event_level NUMBER;
BEGIN
FOR event_number IN 10000 .. 10999 LOOP
sys.dbms_system.read_ev(event_number, event_level);
IF (event_level > 0) THEN
sys.dbms_output.put_line(‘Event ‘ || to_char(event_number) || ‘ is set at level ‘ || to_char(event_level));
END IF;
END LOOP;
END;

———————————– 获取当前session设置的参数的脚本 ———————————–

———————————– 10046查看等待事件的例子 ———————————–

— 1.查看系统参数;

show parameter db_block_size; — 8192;

show parameter db_file_multiblock_read_count; — 128;

SELECT tablespace_name, block_size, initial_extent / block_size, next_extent / block_size
FROM dba_tablespaces
WHERE tablespace_name IN (‘SYSTEM’, ‘TBS32B’);

SYSTEM 8192 8

TBS32B 8192 32 32

— 2.创建一个测试表;

sqlplus / as sysdba

CREATE TABLE TB10046 AS SELECT * FROM dba_objects;

SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name = ‘TB10046’;

   FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1      93784          8
1      93792          8
1      93800          8
1      93808          8
1      93816          8
1     104832          8
1     104840          8
1     104848          8
1     104856          8
1     104864          8
1     104872          8

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1     104880          8
1     104888          8
1     104896          8
1     104904          8
1     104912          8
1     104960        128
1     105088        128
1     105216        128
1     105344        128
1     105472        128
1     105600        128

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1     105728        128
1     105856        128

24 rows selected.

— 3.生成trace文件;

ALTER SESSION SET TRACEFILE_IDENTIFIER=’t1′;

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;

SELECT event, total_waits FROM V$system_EVENT WHERE EVENT = ‘db file scattered read’; — 146913315

SELECT COUNT(*) FROM TB10046; — 发生全表扫描;

SELECT event, total_waits FROM V$system_EVENT WHERE EVENT = ‘db file scattered read’; — 146913412

— 4.查看trace文件,发现一次读取8个blocks,因为扫描不能跨extent,此时一个extent中是8个块;(trace中的file#,block#和blocks是与等待事件中参数一一对应的:SELECT NAME, parameter1, parameter2, parameter3 FROM v$event_name WHERE NAME = ‘db file scattered read’;)

WAIT #1: nam=’db file scattered read’ ela= 82 file#=1 block#=104961 blocks=8 obj#=95923 tim=1389859838564653
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=104986 blocks=8 obj#=95923 tim=1389859838565112
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105009 blocks=8 obj#=95923 tim=1389859838565556
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105017 blocks=8 obj#=95923 tim=1389859838565951
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105059 blocks=8 obj#=95923 tim=1389859838566301
WAIT #1: nam=’db file scattered read’ ela= 77 file#=1 block#=105070 blocks=8 obj#=95923 tim=1389859838566646
WAIT #1: nam=’db file scattered read’ ela= 62 file#=1 block#=105106 blocks=8 obj#=95923 tim=1389859838567210
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105118 blocks=8 obj#=95923 tim=1389859838567565
WAIT #1: nam=’db file scattered read’ ela= 71 file#=1 block#=105163 blocks=8 obj#=95923 tim=1389859838567961
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105185 blocks=8 obj#=95923 tim=1389859838568283
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105200 blocks=8 obj#=95923 tim=1389859838568635
WAIT #1: nam=’db file scattered read’ ela= 68 file#=1 block#=105226 blocks=8 obj#=95923 tim=1389859838568997
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105236 blocks=8 obj#=95923 tim=1389859838569331
WAIT #1: nam=’db file scattered read’ ela= 67 file#=1 block#=105264 blocks=8 obj#=95923 tim=1389859838569659
WAIT #1: nam=’db file scattered read’ ela= 67 file#=1 block#=105291 blocks=8 obj#=95923 tim=1389859838569972
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105303 blocks=8 obj#=95923 tim=1389859838570278
WAIT #1: nam=’db file scattered read’ ela= 79 file#=1 block#=105325 blocks=8 obj#=95923 tim=1389859838570640
WAIT #1: nam=’db file scattered read’ ela= 63 file#=1 block#=105371 blocks=8 obj#=95923 tim=1389859838571135
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105425 blocks=8 obj#=95923 tim=1389859838571486
WAIT #1: nam=’db file scattered read’ ela= 69 file#=1 block#=105474 blocks=8 obj#=95923 tim=1389859838571855
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105516 blocks=8 obj#=95923 tim=1389859838572204
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105528 blocks=8 obj#=95923 tim=1389859838572530
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105601 blocks=8 obj#=95923 tim=1389859838572887
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105617 blocks=8 obj#=95923 tim=1389859838573215
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105644 blocks=8 obj#=95923 tim=1389859838573561
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105676 blocks=8 obj#=95923 tim=1389859838573917
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105700 blocks=8 obj#=95923 tim=1389859838574251
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105712 blocks=8 obj#=95923 tim=1389859838574650
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105759 blocks=8 obj#=95923 tim=1389859838575214
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105779 blocks=8 obj#=95923 tim=1389859838575545
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105795 blocks=8 obj#=95923 tim=1389859838575886
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105820 blocks=8 obj#=95923 tim=1389859838576234
WAIT #1: nam=’db file scattered read’ ela= 63 file#=1 block#=105828 blocks=8 obj#=95923 tim=1389859838576565
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105842 blocks=8 obj#=95923 tim=1389859838576902
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105871 blocks=8 obj#=95923 tim=1389859838577386
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105879 blocks=8 obj#=95923 tim=1389859838577723

— 5.创建一个256k大小extent的表空间,即一个extent可以存放32个块;

CREATE TABLESPACE TBS32B DATAFILE ‘/u01/app/oracle/oradata/ORCL/tbs32b.dbf’ SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

ALTER TABLE TB10046 MOVE TABLESPACE TBS32B;

SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name = ‘TB10046’;

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        128         32
8        160         32
8        192         32
8        224         32
8        256         32
8        288         32
8        320         32
8        352         32
8        384         32
8        416         32
8        448         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        480         32
8        512         32
8        544         32
8        576         32
8        608         32
8        640         32
8        672         32
8        704         32
8        736         32
8        768         32
8        800         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        832         32
8        864         32
8        896         32
8        928         32
8        960         32
8        992         32
8       1024         32
8       1056         32
8       1088         32
8       1120         32
8       1152         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8       1184         32
8       1216         32

35 rows selected.

ALTER SYSTEM FLUSH BUFFER_CACHE;

ALTER SESSION SET TRACEFILE_IDENTIFIER=’t2′;

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;

SELECT COUNT(*) FROM TB10046; — 发生全表扫描;

— 6.查看trace文件,发现一次读取32个blocks;

WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=132 blocks=28 obj#=95923 tim=1389860294615193
WAIT #1: nam=’db file scattered read’ ela= 118 file#=8 block#=162 blocks=30 obj#=95923 tim=1389860294616423
WAIT #1: nam=’db file scattered read’ ela= 109 file#=8 block#=194 blocks=30 obj#=95923 tim=1389860294617528
WAIT #1: nam=’db file scattered read’ ela= 111 file#=8 block#=225 blocks=31 obj#=95923 tim=1389860294618635
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=256 blocks=32 obj#=95923 tim=1389860294619776
WAIT #1: nam=’db file scattered read’ ela= 133 file#=8 block#=289 blocks=31 obj#=95923 tim=1389860294620942
WAIT #1: nam=’db file scattered read’ ela= 142 file#=8 block#=320 blocks=32 obj#=95923 tim=1389860294622095
WAIT #1: nam=’db file scattered read’ ela= 115 file#=8 block#=353 blocks=31 obj#=95923 tim=1389860294623268
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=384 blocks=32 obj#=95923 tim=1389860294624399
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=417 blocks=31 obj#=95923 tim=1389860294625493
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=448 blocks=32 obj#=95923 tim=1389860294626569
WAIT #1: nam=’db file scattered read’ ela= 112 file#=8 block#=481 blocks=31 obj#=95923 tim=1389860294627654
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=512 blocks=32 obj#=95923 tim=1389860294628730
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=545 blocks=31 obj#=95923 tim=1389860294629788
WAIT #1: nam=’db file scattered read’ ela= 139 file#=8 block#=576 blocks=32 obj#=95923 tim=1389860294631207
WAIT #1: nam=’db file scattered read’ ela= 123 file#=8 block#=609 blocks=31 obj#=95923 tim=1389860294632340
WAIT #1: nam=’db file scattered read’ ela= 191 file#=8 block#=640 blocks=32 obj#=95923 tim=1389860294633470
WAIT #1: nam=’db file scattered read’ ela= 193 file#=8 block#=673 blocks=31 obj#=95923 tim=1389860294634671
WAIT #1: nam=’db file scattered read’ ela= 131 file#=8 block#=704 blocks=32 obj#=95923 tim=1389860294635781
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=737 blocks=31 obj#=95923 tim=1389860294636852
WAIT #1: nam=’db file scattered read’ ela= 125 file#=8 block#=768 blocks=32 obj#=95923 tim=1389860294637924
WAIT #1: nam=’db file scattered read’ ela= 126 file#=8 block#=801 blocks=31 obj#=95923 tim=1389860294638974
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=832 blocks=32 obj#=95923 tim=1389860294640007
WAIT #1: nam=’db file scattered read’ ela= 123 file#=8 block#=865 blocks=31 obj#=95923 tim=1389860294641040
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=896 blocks=32 obj#=95923 tim=1389860294642112
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=929 blocks=31 obj#=95923 tim=1389860294643170
WAIT #1: nam=’db file scattered read’ ela= 135 file#=8 block#=960 blocks=32 obj#=95923 tim=1389860294644287
WAIT #1: nam=’db file scattered read’ ela= 183 file#=8 block#=993 blocks=31 obj#=95923 tim=1389860294645459
WAIT #1: nam=’db file scattered read’ ela= 131 file#=8 block#=1024 blocks=32 obj#=95923 tim=1389860294646588
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=1057 blocks=31 obj#=95923 tim=1389860294647669
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=1088 blocks=32 obj#=95923 tim=1389860294648735
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=1121 blocks=31 obj#=95923 tim=1389860294649806
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=1152 blocks=32 obj#=95923 tim=1389860294650864
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=1185 blocks=31 obj#=95923 tim=1389860294651919

———————————– 10046查看等待事件的例子 ———————————–

———————————– db_file_multiblock_read_count与执行计划选择的例子 ———————————–

— 1.准备工作;

SELECT owner, count(*) FROM TB10046 GROUP BY owner ORDER BY 2;

OWNER                            COUNT(*)
—————————— ———-
OWBSYS                                  2
APPQOSSYS                               3
SCOTT                                   6
ORACLE_OCM                              8
SI_INFORMTN_SCHEMA                      8
BI                                      8
OUTLN                                   9
ORDPLUGINS                             10
OWBSYS_AUDIT                           12
FLOWS_FILES                            12
PM                                     27

OWNER                            COUNT(*)
—————————— ———-
HR                                     34
IX                                     55
DBSNMP                                 65
OE                                    127
ORDDATA                               248
SH                                    306
EXFSYS                                310
WMSYS                                 316
CTXSYS                                366
SYSTEM                                529
OLAPSYS                               719

OWNER                            COUNT(*)
—————————— ———-
XDB                                   844
MDSYS                                1509
PV                                   2161
APEX_030200                          2406
ORDSYS                               2532
SYSMAN                               3491
PUBLIC                              27702
SYS                                 31132

30 rows selected.

CREATE INDEX IDX_TB10046_HR ON TB10046(owner);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘TB10046’, cascade=>TRUE);

— 2.设置db_file_multiblock_read_count参数为8,查看表的执行计划;

ALTER SYSTEM SET db_file_multiblock_read_count=8;

SET AUTOT TRACE EXP;

SQL> SELECT * FROM tb10046 WHERE OWNER=’SYSMAN’;
Execution Plan
———————————————————-
Plan hash value: 3379381082

——————————————————————————–
————–

——————————————————————————–
————–

| 0 | SELECT STATEMENT | | 3268 | 315K| 95 (
0)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB10046 | 3268 | 315K| 95 (
0)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | IDX_TB10046_HR | 3268 | | 8 (
0)| 00:00:01 |

——————————————————————————–
————–

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

2 – access(“OWNER”=’SYSMAN’)

— 3.设置db_file_multiblock_read_count参数为128,查看表的执行计划;

ALTER SYSTEM SET db_file_multiblock_read_count=;128

SQL> SELECT * FROM tb10046 WHERE OWNER=’SYSMAN’;

Execution Plan
———————————————————-
Plan hash value: 3237706262

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 3268 | 315K| 99 (1)| 00:00:03 |

|* 1 | TABLE ACCESS FULL| TB10046 | 3268 | 315K| 99 (1)| 00:00:03 |

—————————————————————————–

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

1 – filter(“OWNER”=’SYSMAN’)

———————————– db_file_multiblock_read_count与执行计划选择的例子 ———————————–

性能调优工具02–Sql Access Advisor工具的使用

Posted on 2014-06-01 by royalwzy

SAA(Sql Access Advisor)工具的使用

SAA可以做什么:
1. 在给定的负载上推荐一些物化视图,物化视图日志和索引;
2. 推荐的索引包括Bitmap索引,基于函数的索引和B-Tree索引;
3. 推荐如何创建物化视图,以至于可以实现快速刷新和查询重写;
SAA的使用模型:
1. SQL的来源:
  1. SQL Cache:即当前v$sql视图中记录的sql语句;
  2. User-defined:自定义一个表,记录执行的sql语句和执行sql语句的用户,然后把这个表传给SAA;
  3. STS:使用Sql Tuning Set,主要是从负载中获得;
2. 给出的建议：
  1. Simultaneously considers index solutions, materialized view solutions, or combinations of both;
  2. Considers storage for creation and maintenance costs;
  3. Does not generate drop recommendations for partial workloads;
  4. Optimizes materialized views for maximum query rewrite usage and fast refresh;
  5. Recommends materialized view logs for fast refresh;
  6. Combines similar indexes into a single index;
  7. Generates recommendations that support multiple workload queries;
3. 架构图;
4. 推荐的两种方式:Comprehensive和Limited;
  1. Add new index on table or materialized view;
  2. Drop an unused index;
  3. Modify an existing index by changing the index type;
  4. Modify an existing index by adding columns at the end;
  5. Add a new materialized view;
  6. Drop an unused materialized view;
  7. Add a new materialized view log;
  8. Modify an existing materialized view log to add new columns or clauses;
  9. Comprehensive可以分析1-8所有的推荐;
  10. Limited只能分析1,4,5,7,8推荐;
使用SAA需要的权限:
1. ADVISOR的系统权限;
2. 目标表的SELECT权限,而且这个权限不能从一个角色中获得;
使用SAA工具:
1. 执行负载;
2. 使用EM中图形化界面;
3. 使用脚本调用DMBS_ADVISOR包;
使用DMBS_ADVISOR包:
1. 步骤:
  1. 创建一个任务,并定义参数;
  2. 定义负载;
  3. 生成一些建议;
  4. 查看并应用建议;
2. SAA的工作流;
3. 创建一个任务:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name);
4. 设置SAA的参数:
  1. 设置任务的参数:DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, parameter, value);
  2. 设置负载的参数:DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(:workload_name, parameter, value);
5. 创建模板:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name, template=>:template_name, is_template=>’TRUE’);
6. 创建一个负载:DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
  1. SQL Tuning Sets:DBMS_ADVISOR.IMPORT_SQLWKLD_STS();
  2. 用户自定义负载:DBMS_ADVISOR.IMPORT_SQLWKLD_USER();
  3. SQL Cache:DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE();
  4. 单条的SQL语句:DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT();
  5. 某个用户下的SQL语句:DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA();
7. 删除负载:DBMS_ADVISOR.DELETE_SQLWKLD(:workload_name);
8. 任务与负载关联:DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
9. 生成建议并把建议保存在SAA的资源库:DBMS_ADVISOR.EXECUTE_TASK(:task_name);
  1. 相应的视图为:dba/user_advisor_recommendations;
  2. 或者使用dbms_advisor.get_task_script生成相应的脚本;
10. SAA的主要建议有:
  1. 创建/删除物化视图;
  2. 创建/删除物化视图日志;
  3. 创建/删除索引;
  4. 收集统计信息;
11. 生成SQL脚本:
  1. 创建DIRECTORY;
  2. 授权给用户;
  3. 生成脚本;
如果只是想调优一条SQL语句的话,可以执行快速调优,使用DBMS_ADVISOR.QUICK_TUNE()过程;
对于物化视图的优化:
1. DBMS_MVIEW.EXPLAIN_MVIEW:查看使用/不使用物化视图的原因;
2. DBMS_MVIEW.EXPLAIN_REWRITE:为没有么有使用查询重写,如果使用了,使用的哪个物化视图;
3. DBMS_ADVISOR.TUNE_MVIEW:生成快速刷新物化视图和生成查询重写的建议;

——————————– 使用SAA工具 ——————————–

— 1.执行负载;

CONNECT / AS SYSDBA;
ALTER USER sh IDENTIFIED BY oracle ACCOUNT UNLOCK;
GRANT DBA TO sh;
ALTER SYSTEM FLUSH shared_pool;
ALTER SYSTEM FLUSH buffer_cache;
CONNECT sh/oracle;
SET autotrace traceonly stat;

SELECT c.cust_last_name, SUM(s.amount_sold) AS dollars, SUM(s.quantity_sold) AS quantity
FROM sales s, customers c, products p
WHERE c.cust_id = s.cust_id AND
s.prod_id = p.prod_id AND
c.cust_state_province IN (‘Dublin’, ‘Galway’)
GROUP BY c.cust_last_name;

SELECT c.cust_id, SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;

SELECT SUM(unit_cost) FROM costs GROUP BY prod_id;
SELECT * FROM customers WHERE cust_postal_code = ‘83786’;

— 2.使用EM中的SAA工具;

1.Databaes Instance->Advisor Central->SQL Access Advisor;

2.选择SQL语句的来源:选择从内存中的SQL语句;

3.选择推荐的选项:生成索引和物化视图,并使用综合模式;

4.系统创建了一个作业,指定立即执行;

5.查看作业已完成;

6.然后查看和采用相应的建议;

— 3.使用脚本调用DBMS_ADVISOR包;

1.定义变量;

VARIABLE v_task_id NUMBER;
VARIABLE v_task_name VARCHAR2(255);
VARIABLE v_wkld_name VARCHAR2(255);

2.指定任务的名称和负载的名称;
EXECUTE :v_task_name := ‘my_task’;
EXECUTE :v_wkld_name := ‘my_sql_wkld’;

3.删除之前的任务和负载;
BEGIN
dbms_advisor.delete_sqlwkld_ref(:v_task_name, :v_wkld_name);
dbms_advisor.delete_sqlwkld(:v_wkld_name);
dbms_advisor.delete_task(:v_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

4.创建一个任务;
EXECUTE DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :v_task_id, :v_task_name);

5.查看当天前的优化任务;
SELECT * FROM user_advisor_tasks;

6.设置任务的参数,生成所有建议,并使用综合模式;
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘EXECUTION_TYPE’, ‘FULL’);
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘MODE’, ‘COMPREHENSIVE’);

7.创建一个负载;

EXECUTE dbms_advisor.create_sqlwkld(:v_wkld_name);

8.把任务与负载关联;
EXECUTE dbms_advisor.add_sqlwkld_ref(:v_task_name, :v_wkld_name);

9.查看任务与负载的映射关系;

SELECT * FROM user_advisor_sqla_wk_map;

10.导入负载,使用SQL Cache的模式;
VARIABLE v_saved_stmts NUMBER;
VARIABLE v_failed_stmts NUMBER;
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(:v_wkld_name, ‘APPEND’, 2, :v_saved_stmts, :v_failed_stmts);

11.查看负载中的SQL语句;
SELECT * FROM user_advisor_sqlw_stmts;

12.执行任务,生成优化建议;
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:v_task_name);

13.查看优化建议;
SELECT * FROM user_advisor_recommendations;

14.生成优化建议的脚本;

直接查看:SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;

或者创建目录,生成建议的脚本;

CONNECT / AS SYSDBA;
DROP DIRECTORY ADVISOR_RESULTS;
CREATE OR REPLACE DIRECTORY ADVISOR_RESULTS AS ‘/home/oracle’;
GRANT READ, WRITE ON DIRECTORY ADVISOR_RESULTS TO sh;

CONNECT SH/ORACLE
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name), ‘ADVISOR_RESULTS’, ‘advscript.sql’);

——————————– 使用SAA工具 ——————————–

——————————– SAA的快速调优 ——————————–

1.定义变量;

CONNECT sh/oracle;
VARIABLE v_task_name VARCHAR2(30);
EXECUTE :v_task_name := ‘quick_task’;

2.执行快速调优,只能执行一条SQL语句,默认是在当前用户下执行;
EXECUTE dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, :v_task_name, ‘SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=10’);

3.查看优化建议结果;

SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;

——————————– SAA的快速调优 ——————————–

性能调优工具01–Sql Tuning Advisor工具的使用

Posted on 2014-06-01 by royalwzy

STA(Sql Tuning Advisor)工具的使用

STA工具其实就是DBMS_SQLTUNE包,要使用STA的话,必须是在CBO模式下使用;
执行DBMS_SQLTUNE包,需要有advisor角色权限;
DBMS_SQLTUNE包介绍:
1. DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text, bind_list, user_name, scope, time_limit, task_name, DESCRIPTION) RETURN VARCHAR2;
  1. sql_text:需要优化的sql语句;
  2. bind_list:绑定的变量列表,默认为NULL;
  3. user_name:要优化的sql语句通过哪个用户执行,默认为NULL;
  4. scope:优化范围,有两个取值,limited-分析时不使用推荐的SQL Profile,大约每个语句1s时间;comprehensive-分析时使用推荐的SQL Profile,可能花费的时间较长,默认值;
  5. time_limit:优化过程的时间限制,默认为TIME_LIMIT_DEFAULT;
  6. task_name:优化任务的名称,默认为NULL;
  7. decription:描述信息,默认为NULL;
2. DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
  1. task_name:要执行的优化任务的名称;
3. DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name, type, level, section, object_id, result_limit);
  1. task_name:要查看的优化任务的名称;
  2. type:优化报告的类型,TEXT,HTML或者XML,默认是TEXT;
  3. level:格式化的级别,TYPICAL, BASIC, ALL.默认是TYPICAL;
  4. section:优化报告的部分,FINDING,PLAN,INFORMATION,ERROR或者ALL,默认是ALL;
  5. 调用方法:SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘task_name’) from dual;
4. DBMS_SQLTUNE.DROP_TUNING_TASK(task_name);
  1. task_name:要删除的优化任务的名称;
5. DBMS_SQLTUNE.RESET_TUNING_TASK(task_name):重置优化任务的结果;
  1. task_name:优化任务的名称;
  2. 执行过之后就可以重新执行EXECUTE_TUNING_TASK过程了;
一般的步骤:
1. 创建优化任务;
2. 执行优化任务;
3. 显示优化结果;
4. 根据建议来做相应的优化;
5. 删除优化任务;
创建测试用户:SQLTUNE;
创建测试表;
执行查询语句:SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name;
1. 需要的时间;
2. 执行计划;
3. 统计信息;
创建优化任务;
执行优化任务,并查看优化任务的执行进度;
查询优化结果;
1. 关于这次优化任务的基本信息:如任务名称,执行时间,范围,涉及到的语句,发现问题的类型及个数的信息等等;
2. 发现的问题:两个表没有收集统计信息;
3. 发现的问题:两个表没有索引;
4. 按照优化建议修改前后的执行计划的对比;
按照建议做相应的修改,然后执行语句验证优化的结果;
删除优化任务:
如果需要对多条语句进行优化时,应该使用STS(Sql Tuning Set);
1. STS可以从多种数据源(Cursor Cache, AWR, STS)获取SQL;
2. 创建一个STS:DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ‘sts_test’);
3. 使用Cursor Cache加载STS:DBMS_SQLTUNE.LOAD_SQLSET();
4. 查看STS的内容:SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘sts_test’));
5. 使用STS创建一个优化任务:DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name=>’sts_test’, task_name=>’sta_test’);
使用EM,在EM->Performance->Advisor Central->SQL Advisors->SQL Tuning Advisor;
单条sql的调优可以使用sqltrpt.sql(执行SQL>@?/rdbms/admin/sqltrpt.sql sqlid即可)脚本传入sqlid即可;对于多条sql语句可以使用dbmssqlt.sql脚本,这个脚本给出了dbms_sqltune包的定义和相关的例子;

— 创建测试用户SQLTUNE;

CREATE USER SQLTUNE IDENTIFIED BY “oracle”;
GRANT CONNECT, RESOURCE, ADVISOR TO SQLTUNE;
CONN SQLTUNE/ORACLE;

— 创建测试表;

CREATE TABLE bigtable AS SELECT rownum id, t.* FROM sys.all_objects t;
CREATE TABLE smalltable AS SELECT rownum id, t.* FROM sys.all_objects t;
INSERT INTO bigtable SELECT * FROM bigtable;
INSERT INTO bigtable SELECT * FROM bigtable;
INSERT INTO bigtable SELECT * FROM bigtable;
COMMIT;

— 创建优化任务;

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, user_name => ‘SQLTUNE’, task_name => ‘sta_test’);
END;

— 执行优化任务;

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘sta_test’);

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =’sta_test’;

— 查看优化结果;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘sta_test’) from dual;

— 执行优化建议;

CREATE INDEX IDX_BIGTABLE_OBJECTNAME ON BIGTABLE(‘OBJECT_NAME’);
CREATE INDEX IDX_SMALLTABLE_OBJECTNAME ON SMALLTABLE(‘OBJECT_NAME’);
EXEC dbms_stats.gather_table_stats(USER, ‘BIGTABLE’, CASCADE=>TRUE);
EXEC dbms_stats.gather_table_stats(USER, ‘SMALLTABLE’, CASCADE=>TRUE);

— 删除优化任务;

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(‘sta_test’);

— 创建SQL Tuning Set创建优化任务;

— create a STS
BEGIN
dbms_sqltune.create_sqlset(sqlset_name => ‘my_sts’);
END;
/

— load STS using cursor cache
DECLARE
l_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN l_cur FOR
SELECT VALUE(t)
FROM TABLE(dbms_sqltune.select_cursor_cache(‘sql_id in (”588rxmp05xt7g”,”7rucbfq8vcr7d”)’)) t;

dbms_sqltune.load_sqlset(sqlset_name => ‘sts_test’, populate_cursor => l_cur);
END;
/

— display contents of STS
SELECT * FROM TABLE(dbms_sqltune.select_sqlset(‘sts_test’));

— drop a sql tuning task
BEGIN
dbms_sqltune.drop_tuning_task(task_name => ‘my_sql_tuning_task’);
END;
/

— create a sql tuning task by using STS
DECLARE
l_task_name VARCHAR2(30);
l_sqltext CLOB;
BEGIN
l_task_name := dbms_sqltune.create_tuning_task(sqlset_name => ‘sts_test’, task_name => ‘my_sql_tuning_task’);

END;
/

调优实践02–ALTER SYSTEM/SESSION SET EVENTS转储

Posted on 2014-05-15 by royalwzy

ALTER SYSTEM/SESSION SET EVENTS

语法:alter system|session set events ‘[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : ……’; 通过[:]符号,可以连续设置多个事件,也可以通过连续使用alter session set events来设置多个事件;
参数说明:
1. eventnumber:指触发dump的事件号,事件号可以是Oracle错误号(出现相应错误时跟踪指定的事件)或oralce内部事件号(内部事件号在10000到10999之间),不能与immediate关键字同用;
2. immediate:关键字表示命令发出后,立即将指定的结构dump到跟踪文件中,这个关键字只用在alter session语句中,并且不能与eventnumber/forever关键字同用;
3. eventname:指事件名称,即要进行dump的实际结构名;若eventname为context,则指根据内部事件号进行跟踪;
4. forever:关键字表示事件在实例或会话的周期内保持有效状态,不能与immediate同用;
5. level:为事件级别关键字,但在dump错误栈(errorstack)时不存在级别;
6. levelnumber:表示事件级别号,一般从1到10,1表示只dump结构头部信息,10表示dump结构的所有信息;
常用的事件名称和级别:
1. Memory Dumps:
  1. Global Area:ALTER SESSION SET EVENTS ‘immediate trace name global_area level n’;
    1. 1 包含PGA
    2. 2 包含SGA
    3. 4 包含UGA
    4. 8 包含indrect memory
  2. Library Cache:ALTER SESSION SET EVENTS ‘immediate trace name library_cache level n’;
    1. 1 library cache统计信息
    2. 2 包含hash table histogram
    3. 3 包含object handle
    4. 4 包含object结构
  3. Row Cache:ALTER SESSION SET EVENTS ‘immediate trace name row_cache level n’;
    1. 1 row cache统计信息
    2. 2 包含hash table histogram
    3. 8 包含object结构
  4. Buffers:ALTER SESSION SET EVENTS ‘immediate trace name buffers level n’;
    1. 1 buffer header
    2. 2 level 1 + block header
    3. 3 level 2 + block contents
    4. 4 level 1 + hash chain
    5. 5 level 2 + hash chain
    6. 6 level 3 + hash chain
    7. 8 level 4 + users/waiters
    8. 9 level 5 + users/waiters
    9. 10 level 6 + users/waiters
  5. Buffer:ALTER SESSION SET EVENTS ‘immediate trace name buffer level n’;
    1. n为某个指定block的rdba,该命令可以转储某个block在buffer中的所有版本;
  6. Heap:ALTER SESSION SET EVENTS ‘immediate trace name heapdump level n’;
    1. 1 PGA摘要
    2. 2 SGA摘要
    3. 4 UGA摘要
    4. 8 Current call(CGA)摘要
    5. 16 User call(CGA)摘要
    6. 32 Large call(LGA)摘要
    7. 1025 PGA内容
    8. 2050 SGA内容
    9. 4100 UGA内容
    10. 8200 Current call内容
    11. 16400 User call内容
    12. 32800 Large call内容
  7. Sub Heap:
    1. ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n’;(oracle 9.0.1版本之前)
      1. 若n为subheap的地址，转储的是subheap的摘要信息
      2. 若n为subheap的地址+1，转储的则是subheap的内容
    2. ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n, addr m’;(oracle 9.2.0版本之后)
      1. 其中m为subheap的地址
      2. n为1转储subheap的摘要，n为2转储subheap的内容
  8. Process State:ALTER SESSION SET EVENTS ‘immediate trace name processstate level n’;
  9. System State:ALTER SESSION SET EVENTS ‘immediate trace name systemstate level n’;
  10. Error State:ALTER SESSION SET EVENTS ‘immediate trace name errorstack level n’;
    1. 0 Error stack
    2. 1 level 0 + function call stack
    3. 2 level 1 + process state
    4. 3 level 2 + context area
  11. Hang Analysis:ALTER SESSION SET EVENTS ‘immediate trace name hanganalyze level n’;
  12. Work Area:ALTER SESSION SET EVENTS ‘immediate trace name workareatab_dump level n’;
    1. 1 SGA信息
    2. 2 Workarea Table摘要信息
    3. 3 Workarea Table详细信息
  13. Latches:ALTER SESSION SET EVENTS ‘immediate trace name latches level n’;
    1. 1 latch信息
    2. 2 统计信息
  14. Events:ALTER SESSION SET EVENTS ‘immediate trace name events level n’;
    1. 1 session
    2. 2 process
    3. 3 system
  15. Locks:ALTER SESSION SET EVENTS ‘immediate trace name locks level n’;
  16. Shared Server Process:ALTER SESSION SET EVENTS ‘immediate trace name shared_server_state level n’;
    1. n取值为1~14
  17. Background Messages:ALTER SESSION SET EVENTS ‘immediate trace name bg_messages level n’;
    1. n为pid+1
2. File Dumps:
  1. Block:
    1. ALTER SESSION SET EVENTS ‘immediate trace name blockdump level n’;(oracle 7之前)
      1. n为block的rdba
    2. ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;(oracle8以后)
    3. ALTER SYSTEM DUMP DATAFILE file# BLOCK MIN minimum_block# BLOCK MAX maximum_block#;
  2. Tree Dump:ALTER SESSION SET EVENTS ‘immediate trace name treedump level n’;
    1. n为object_id
  3. Undo Segment Header:ALTER SYSTEM DUMP UNDO_HEADER ‘segment_name’;
  4. Undo for a Transaction:ALTER SYSTEM DUMP UNDO BLOCK ‘segment_name’ XID xidusn xidslot xidsqn;
  5. File Header:ALTER SESSION SET EVENTS ‘immediate trace name file_hdrs level n’;
    1. 1 控制文件中的文件头信息
    2. 2 level 1 + 文件头信息
    3. 3 level 2 + 数据文件头信息
    4. 10 level 3
  6. Control file:ALTER SESSION SET EVENTS ‘immediate trace name controlf level n’;
    1. 1 文件头信息
    2. 2 level 1 + 数据库信息 + 检查点信息
    3. 3 level 2 + 可重用节信息
    4. 10 level 3
  7. Redo log Header:ALTER SESSION SET EVENTS ‘immediate trace name redohdr level n’;
    1. 1 控制文件中的redo log信息
    2. 2 level 1 + 文件头信息
    3. 3 level 2 + 日志文件头信息
    4. 10 level 3
  8. Redo log
    1. ALTER SYSTEM DUMP LOGFILE ‘FileName’;
    2. ALTER SYSTEM DUMP LOGFILE ‘FileName’
      1. SCN MIN MinimumSCN
      2. SCN MAX MaximumSCN
      3. TIME MIN MinimumTime
      4. TIME MAX MaximumTime
      5. LAYER Layer
      6. OPCODE Opcode
      7. DBA MIN FileNumber . BlockNumber
      8. DBA MAX FileNumber . BlockNumber
      9. RBA MIN LogFileSequenceNumber . BlockNumber
      10. RBA MAX LogFileSequenceNumber . BlockNumber;
    3. 其中time = (((((yyyy – 1988)) * 12 + mm – 1) * 31 + dd – 1) * 24 + hh) * 60 + mi) * 60 + ss;
  9. Loghist:ALTER SESSION SET EVENTS ‘immediate trace name loghist level n’;
    1. 1 dump控制文件中最早和最迟的日志历史项
    2. >1 dump 2^n个日志历史项
事件编号和对应的描述;
获得对应trace文件的脚本;
例子:
1. alter session set events ‘10046 trace name context forever,level 12’;
2. alter session set events ‘immediate trace name controlf level 12’;

———————————- 获得对应trace文件的脚本 ———————————-

———————————- 事件编号和对应的描述 ———————————-
10000 Controlfile debug event, name ‘control_file’
10001 Controlfile crash event1
10002 Controlfile crash event2
10003 Controlfile crash event3
10004 Controlfile crash event4
10005 Trace latch operations for debugging
10006 Testing – block recovery forced
10007 Log switch debug crash after new log select, thread %s
10008 Log switch debug crash after new log header write, thread %s
10009 Log switch debug crash after old log header write, thread %s
10010 Begin Transaction
10011 End Transaction
10012 Abort Transaction
10013 Instance Recovery
10014 Roll Back to Save Point
10015 Undo Segment Recovery
10016 Undo Segment extend
10017 Undo Segment Wrap
10018 Data Segment Create
10019 Data Segment Recovery
10020 Partial link restored to linked list (KSG)
10021 Latch cleanup for state objects (KSS)
10022 Trace ktsgsp
10023 Create Save Undo Segment
10024 Write to Save Undo
10025 Extend Save Undo Segment
10026 Apply Save Undo
10027 Latch cleanup for enqueue locks (KSQ)
10028 Latch cleanup for enqueue resources (KSQ)
10029 Session logon (KSU)
10030 Session logoff (KSU)
10031 Sort debug event (S*)
10032 Sort statistics (SOR*)
10033 Sort run information (SRD*/SRS*)
10034 Access path analysis (APA*)
10035 Parse SQL statement (OPIPRS)
10036 Create remote row source (QKANET)
10037 Allocate remote row source (QKARWS)
10038 Dump row source tree (QBADRV)
10039 Type checking (OPITCA)
10040 Dirty cache list
10041 Dump undo records skipped
10042 Trap error during undo application
10043 Check consistency of owner/waiter/converter lists in KSQ
10044 Free list undo operations
10045 Free list update operations – ktsrsp, ktsunl
10046 Enable SQL statement timing
10047 Trace switching of sessions
10048 Undo segment shrink
10049 Protect library cache memory heaps
10050 Sniper trace
10051 Trace OPI calls
10052 Don’t clean up obj$
10053 CBO Enable optimizer trace
10054 Trace UNDO handling in MLS
10055 Trace UNDO handing
10056 Dump analyze stats (kdg)
10057 Suppress file names in error messages
10058 Use table scan cost in tab$.spare1
10059 Simulate error in logfile create/clear
10060 CBO Enable predicate dump
10061 Disable SMON from cleaning temp segment
10062 Disable usage of OS Roles in osds
10063 Disable usage of DBA and OPER privileges in osds
10064 Thread enable debug crash level %s, thread %s
10065 Limit library cache dump information for state object dump
10066 Simulate failure to verify file
10067 Force redo log checksum errors – block number
10068 Force redo log checksum errors – file number
10069 Trusted Oracle test event
10070 Force datafile checksum errors – block number
10071 Force datafile checksum errors – file number
10072 Protect latch recovery memory
10073 Have PMON dump info before latch cleanup
10074 Default trace function mask for kst
10075 CBO Disable outer-join to regular join conversion
10076 CBO Enable cartesian product join costing
10077 CBO Disable view-merging optimization for outer-joins
10078 CBO Disable constant predicate elimination optimization
10079 Trace data sent/received via SQL*Net
10080 Dump a block on a segment list which cannot be exchanged
10081 Segment High Water Mark has been advanced
10082 Free list head block is the same as the last block
10083 A brand new block has been requested from space management
10084 Free list becomes empty
10085 Free lists have been merged
10086 CBO Enable error if kko and qka disagree on oby sort
10087 Disable repair of media corrupt data blocks
10088 CBO Disable new NOT IN optimization
10089 CBO Disable index sorting
10090 Invoke other events before crash recovery
10091 CBO Disable constant predicate merging
10092 CBO Disable hash join
10093 CBO Enable force hash joins
10094 Before resizing a data file
10095 Dump debugger commands to trace file
10096 After the cross instance call when resizing a data file
10097 After generating redo when resizing a data file
10098 After the OS has increased the size of a data file
10099 After updating the file header with the new file size
10100 After the OS has decreased the size of a data file
10101 Atomic redo write recovery
10102 Switch off anti-joins
10103 CBO Disable hash join swapping
10104 Dump hash join statistics to trace file
10105 CBO Enable constant pred trans and MPs w WHERE-clause
10106 CBO Disable evaluating correlation pred last for NOT IN
10107 CBO Always use bitmap index
10108 CBO Don’t use bitmap index
10109 CBO Disable move of negated predicates
10110 CBO Try index rowid range scans
10111 Bitmap index creation switch
10112 Bitmap index creation switch
10113 Bitmap index creation switch
10114 Bitmap index creation switch
10115 CBO Bitmap optimization use maximal expression
10116 CBO Bitmap optimization switch
10117 CBO Disable new parallel cost model
10118 CBO Enable hash join costing
10119 QKA Disable GBY sort elimination
10120 Generate relative file # different from absolute
10121 CBO Don’t sort bitmap chains
10122 Disable transformation of count(col) to count(*)
10123 QKA Disable Bitmap And-EQuals
10124 Force creation of segmented arrays by kscsAllocate
10125 Disable remote sort elimination
10126 Debug oracle java xa
10127 Disable remote query block operation
10128 Dump Partition Pruning Information
10129 Alter histogram lookup for remote queries
10130 Sort disable readaheads
10131 Use v$sql_plan code path for explain plan
10132 Dump plan after compilation
10133 Testing for SQL Memory Management
10134 Tracing for SQL Memory Management for session
10135 CBO do not count 0 rows partitions
10136 CBO turn off fix for bug 1089848
10137 CBO turn off fix for bug 1344111
10138 CBO turn off fix for bug 1577003
10139 CBO turn off fix for bug 1386119
10140 CBO turn off fix for bug 1332980
10141 CBO disable additional keys for inlist in bitmap optimization
10142 CBO enable dynamic selectivity estimation
10143 CBO force dynamic selectivity estimation (if enabled)
10145 Test auditing network errors
10146 Enable Oracle TRACE collection
10148 Use pre-7.3.3 random generator
10149 Allow the creation of constraints with illegal date constants
10150 Import exceptions
10151 Force duplicate dependency removal
10152 CBO don’t consider function costs in plans
10153 Switch to use public synonym if private one does not translate
10154 Switch to disallow synonyms in DDL statements
10155 CBO disable generation of transitive OR-chains
10156 CBO disable index fast full scan
10157 CBO disable index access path for in-list
10158 CBO preserve predicate order in post-filters
10159 CBO disable order-by sort pushdown into domain indexes
10160 CBO disable use of join index
10161 CBO recursive semi-join on/off-switch
10162 CBO join-back elimination on/off-switch
10163 CBO join-back elimination on/off-switch
10164 CBO disable subquery-adjusted cardinality fix
10165 Mark session to be aborted during shutdown normal
10166 Trace long operation statistics updates
10167 CBO use old index MIN/MAX optimization
10168 CBO disable single-table predicate predicate generation
10169 CBO disable histograms for multi partitions
10170 CBO use old bitmap costing
10171 CBO disable transitive join predicates
10172 CBO force hash join back
10173 CBO no constraint-based join-back elimination
10174 View join-back elimination switch
10175 CBO star transformation switch
10176 CBO colocated join switch
10177 CBO colocated join switch
10178 CBO turn off hash cluster filtering through memcmp
10179 CBO turn off transitive predicate replacement
10180 Temp table transformation print error messages
10181 CBO disable multi-column in-list processing
10182 CBO disable generation of implied predicates
10183 CBO disable cost rounding
10184 CBO disable OR-exp if long inlist on bitmap column
10185 CBO force index joins
10186 CBO disable index join
10187 CBO additional index join switch
10188 “CBO additional index join switch
10189 CBO turn off FFS null fix
10190 Analyze use old frequency histogram collection and density
10191 Avoid conversion of in-lists back to OR-expanded form
10192 Nopushdown when number of groups exceed number of rows
10193 Force repeatable sampling with specified seed
10194 CBO disable new LIKE selectivity heuristic
10195 CBO don’t use check constraints for transitive predicates
10196 CBO disable index skip scan
10197 CBO force index skip scan
10198 Check undo record
10199 Set parameter in session
10200 Consistent read buffer status
10201 Consistent read undo application
10202 Consistent read block header
10203 Block cleanout
10204 Signal recursive extend
10205 Row cache debugging
10206 Transaction table consistent read
10207 Consistent read transactions’ status report
10208 Consistent read loop check
10209 Enable simulated error on controlfile
10210 Check data block integrity
10211 Check index block integrity
10212 Check cluster integrity
10213 Crash after controlfile write
10214 Simulate write errors on controlfile
10215 Simulate read errors on controlfile
10216 Dump controlfile header
10217 Debug sequence numbers
10218 Dump uba of applied undo
10219 Monitor multi-pass row locking
10220 Show updates to the transaction table
10221 Show changes done with undo
10222 Row cache
10223 Transaction layer – turn on verification codes
10224 Index block split/delete trace
10225 Free/used extent row cache
10226 Trace CR applications of undo for data operations
10227 Verify (multi-piece) row structure
10228 Trace application of redo by kcocbk
10229 Simulate I/O error against datafiles
10230 Check redo generation by copying before applying
10231 Skip corrupted blocks on _table_scans_
10232 Dump corrupted blocks symbolically when kcbgotten
10233 Skip corrupted blocks on index operations
10234 Trigger event after calling kcrapc to do redo N times
10235 Check memory manager internal structures
10236 Library cache manager
10237 Simulate ^C (for testing purposes)
10238 Instantiation manager
10239 Multi-instance library cache manager
10240 Dump dba’s of blocks that we wait for
10241 Remote SQL execution tracing/validation
10242 Suppress OER 2063 (for testing distrib w/o different error log)
10243 Simulated error for test %s of K2GTAB latch cleanup
10244 Make tranids in error msgs print as 0.0.0 (for testing)
10245 Simulate lock conflict error for testing PMON
10246 Print trace of PMON actions to trace file
10247 Turn on scgcmn tracing. (VMS ONLY)
10248 Turn on tracing for dispatchers
10249 Turn on tracing for multi-stated servers
10250 Trace all allocate and free calls to the topmost SGA heap
10251 Check consistency of transaction table and undo block
10252 Simulate write error to data file header
10253 Simulate write error to redo log
10254 Trace cross-instance calls
10255 Pl/sql parse checking
10256 Turn off shared server load balancing
10257 Trace shared server load balancing
10258 Force shared servers to be chosen round-robin
10259 Get error message text from remote using explicit call
10260 Trace calls to SMPRSET (VMS ONLY)
10261 Limit the size of the PGA heap
10262 Don’t check for memory leaks
10263 Don’t free empty PGA heap extents
10264 Collect statistics on context area usage (x$ksmcx)
10265 Keep random system generated output out of error messages
10266 Trace OSD stack usage
10267 Inhibit KSEDMP for testing
10268 Don’t do forward coalesce when deleting extents
10269 Don’t do coalesces of free space in SMON
10270 Debug shared cursors
10271 Distributed transaction after COLLECT
10272 Distributed transaction before PREPARE
10273 Distributed transaction after PREPARE
10274 Distributed transaction before COMMIT
10275 Distributed transaction after COMMIT
10276 Distributed transaction before FORGET
10277 Cursor sharing (or not) related event (used for testing)
10278 Internal testing
10279 Simulate block corruption in kdb4chk
10280 Internal testing – segmentation fault during crash recovery
10281 Maximum time to wait for process creation
10282 Inhibit signalling of other backgrounds when one dies
10283 Simulate asynch I/O never completing
10284 Simulate zero/infinite asynch I/O buffering
10285 Simulate controlfile header corruption
10286 Simulate controlfile open error
10287 Simulate archiver error
10288 Do not check block type in ktrget
10289 Do block dumps to trace file in hex rather than fromatted
10290 Kdnchk – checkvalid event – not for general purpose use.
10291 Die in tbsdrv to test controlfile undo
10292 Dump uet entries on a 1561 from dtsdrv
10293 Dump debugging information when doing block recovery
10294 Enable PERSISTENT DLM operations on non-compliant systems
10295 Die after file header update durning cf xact
10296 Disable ORA-379
10297 Customize dictionary object number cache
10298 Ksfd i/o tracing
10299 Trace prefetch tracking decisions made by CKPT
10300 Disable undo compatibility check at database open
10301 Enable LCK timeout table consistency check
10302 Trace create or drop internal trigger
10303 Trace loading of library cache for internal triggers
10304 Trace replication trigger
10305 Trace updatable materialized view trigger
10306 Trace materialized view log trigger
10307 Trace RepCat execution
10308 Replication testing event
10309 Trigger Debug event
10310 Trace synchronous change table trigger
10311 Disable Flashback Table Timestamp checking
10312 Allow disable to log rows into the mapping table
10319 Trace PGA statistics maintenance
10320 Enable data layer (kdtgrs) tracing of space management calls
10321 Datafile header verification debug failure.
10323 Before committing an add datafile command
10324 Enable better checking of redo logs errors
10325 Trace control file record section expand and shrink operations
10326 Clear logfile debug crash at %s, log %s
10327 Simulate ORA-00235 error for testing
10328 Disable first-to-mount split-brain error, for testing
10329 Simulate lost write, test detection by two-pass recovery
10330 Clear MTTR statistics in checkpoint progress record
10331 Simulate resilvering during recovery
10332 Force ALTER SYSTEM QUIESCE RESTRICTED command to fail
10336 Do remote object transfer using remote SQL
10337 Enable padding owner name in slave sql
10340 Buffer queues sanity check for corrupted buffers
10341 Simulate out of PGA memory in DBWR during object reuse
10342 Raise unknown exception in ACQ_ADD when checkpointing
10343 Raise an out of memory exception-OER 4031 in ACQ_ADD
10344 Simulate kghxal returning 0 in ACQ_ADD but no exception
10345 Validate queue when linking or unlinking a buffer
10346 Check that all buffers for checkpoint have been written
10347 Dump active checkpoint entries and checkpoint buffers
10348 Test abnormal termination of process initiating file checkpoint
10349 Do not allow ckpt to complete
10350 Simulate more than one object & tsn id in object reuse
10351 Size of slots
10352 Report direct path statistics
10353 Number of slots
10354 Turn on direct read path for parallel query
10355 Turn on direct read path for scans
10356 Turn on hint usage for direct read
10357 Turn on debug information for direct path
10359 Turn off updates to control file for direct writes
10360 Enable dbwr consistency checking
10365 Turn on debug information for adaptive direct reads
10370 Parallel query server kill event
10371 Disable TQ hint
10372 Parallel query server kill event proc
10373 Parallel query server kill event
10374 Parallel query server interrupt (validate lock value)
10375 Turn on checks for statistics rollups
10376 Turn on table queue statistics
10377 Turn off load balancing
10378 Force hard process/range affinity
10379 Direct read for rowid range scans (unimplemented)
10380 Kxfp latch cleanup testing event
10381 Kxfp latch cleanup testing event
10382 Parallel query server interrupt (reset)
10383 Auto parallelization testing event
10384 Parallel dataflow scheduler tracing
10385 Parallel table scan range sampling method
10386 Parallel SQL hash and range statistics
10387 Parallel query server interrupt (normal)
10388 Parallel query server interrupt (failure)
10389 Parallel query server interrupt (cleanup)
10390 Trace parallel query slave execution
10391 Trace PX granule allocation/assignment
10392 Parallel query debugging bits
10393 Print parallel query statistics
10394 Generate a fake load to test adaptive and load balancing
10395 Adjust sample size for range table queues
10396 Circumvent range table queues for queries
10397 Suppress verbose parallel coordinator error reporting
10398 Enable timeouts in parallel query threads
10399 Trace buffer allocation
10400 Turn on system state dumps for shutdown debugging
10401 Turn on IPC (ksxp) debugging
10402 Turn on IPC (skgxp) debugging
10403 Fake CPU number for default degree of parallelism
10404 Crash dbwr after write
10405 Emulate broken mirrors
10406 Enable datetime TIMESTAMP, INTERVAL datatype creation
10407 Enable datetime TIME datatype creation
10408 Disable OLAP builtin window function usage
10410 Trigger simulated communications errors in KSXP
10411 Simulate errors in IMR
10412 Trigger simulated errors in CGS/CM interface
10425 Enable global enqueue service open event trace
10426 Enable global enqueue service convert event trace
10427 Enable global enqueue service traffic controller event trace
10428 Enable tracing of global enqueue service distributed resource
10429 Enable tracing of global enqueue service IPC calls
10430 Enable tracing of global enqueue service AST calls
10431 Enable verification messages on pi consistency
10432 Enable tracing of global cache service fusion calls
10433 Global enqueue service testing event
10434 Enable tracing of global enqueue service muliple LMS
10435 Enable tracing of global enqueue service deadlock detetction
10450 Signal ctrl-c in kdddca (drop column) after n rows
10500 Turn on traces for SMON
10510 Turn off SMON check to offline pending offline rollback segment
10511 Turn off SMON check to cleanup undo dictionary
10512 Turn off SMON check to shrink rollback segments
10515 Turn on event to use physical cleanout
10550 Signal error during create as select/create index after n rows
10560 Block type ‘%s’
10561 Block type ‘%s’, data object# %s
10562 Error occurred while applying redo to data block (file# %s, block# %s)
10563 Test recovery had to corrupt data block (file# %s, block# %s) in order to proceed
10564 Tablespace %s
10565 Another test recovery session is active
10566 Test recovery has used all the memory it can use
10567 Redo is inconsistent with data block (file# %s, block# %s)
10568 Failed to allocate recovery state object: out of SGA memory
10570 Test recovery complete
10571 Test recovery canceled
10572 Test recovery canceled due to errors
10573 Test recovery tested redo from change %s to %s
10574 Test recovery did not corrupt any data block
10575 Give up restoring recovered datafiles to consistent state: out of memory
10576 Give up restoring recovered datafiles to consistent state: some error occurred
10577 Can not invoke test recovery for managed standby database recovery
10578 Can not allow corruption for managed standby database recovery
10579 Can not modify control file during test recovery
10580 Can not modify datafile header during test recovery
10581 Can not modify redo log header during test recovery
10582 The control file is not a backup control file
10583 Can not recovery file %s renamed as missing during test recovery
10584 Can not invoke parallel recovery for test recovery
10585 Test recovery can not apply redo that may modify control file
10586 Test recovery had to corrupt 1 data block in order to proceed
10587 Invalid count for ALLOW n CORRUPTION option
10588 Can only allow 1 corruption for normal media/standby recovery
10589 Test recovery had to corrupt %s data blocks in order to proceed
10590 Kga (argus debugger) test flags
10591 Kga (argus debugger) test flags
10592 Kga (argus debugger) test flags
10593 Kga (argus debugger) test flags
10594 Kga (argus debugger) test flags
10595 Kga (argus debugger) test flags
10596 Kga (argus debugger) test flags
10597 Kga (argus debugger) test flags
10598 Kga (argus debugger) test flags
10599 Kga (argus debugger) test flags
10600 Check cursor frame allocation
10601 Turn on debugging for cursor_sharing (literal replacement)
10602 Cause an access violation (for testing purposes)
10603 Cause an error to occur during truncate (for testing purposes)
10604 Trace parallel create index
10605 Enable parallel create index by default
10606 Trace parallel create index
10607 Trace index rowid partition scan
10608 Trace create bitmap index
10609 Trace for array index insertion
10610 Trace create index pseudo optimizer
10611 Causes migration to fail – testing only
10612 Prints debug information for auto-space managed segments
10613 Prints debug information for auto-space managed segments
10614 Operation not allowed on this segment
10615 Invalid tablespace type for temporary tablespace
10616 Operation not allowed on this tablespace
10617 Cannot create rollback segment in this tablespace
10618 Operation not allowed on this segment
10619 Avoid assertions when possible
10620 Operation not allowed on this segment
10621 Data block does not belong to the segment
10622 Test/trace online index (re)build
10623 Enable Index range scan Prefetch – testing only
10650 Disable cache-callback optimisation
10651 Incorrect file number block number specified
10666 Do not get database enqueue name
10667 Cause sppst to check for valid process ids
10690 Set shadow process core file dump type (Unix only)
10691 Set background process core file type (Unix only)
10700 Alter access violation exception handler
10701 Dump direct loader index keys
10702 Enable histogram data generation
10703 Simulate process death during enqueue get
10704 Print out information about what enqueues are being obtained
10705 Print Out Tracing information for every I/O done by ODSs
10706 Print out information about global enqueue manipulation
10707 Simulate process death for instance registration
10708 Print out Tracing information for skxf multi instance comms
10709 Enable parallel instances in create index by default
10710 Trace bitmap index access
10711 Trace bitmap index merge
10712 Trace bitmap index or
10713 Trace bitmap index and
10714 Trace bitmap index minus
10715 Trace bitmap index conversion to rowids
10716 Trace bitmap index compress/decompress
10717 Trace bitmap index compaction trace for index creation
10718 Event to disable automatic compaction after index creation
10719 Trace bitmap index dml
10720 Trace db scheduling
10721 Internal testing – temp table transformation
10722 Set parameters for CPU frequency calculation (debug)
10723 Internal testing – release buffer for buffer cache shrink
10730 Trace row level security policy predicates
10731 Dump SQL for CURSOR expressions
10740 Disables fix for bug 598861
10750 Test rollback segment blksize guessing for index array insert
10800 Disable Smart Disk scan
10801 Enable Smart Disk trace
10802 Reserved for Smart Disk
10803 Write timing statistics on cluster database recovery scan
10804 Reserved for ksxb
10806 Switch to 7.3 mode when detaching sessions
10807 Disable user id check when switching to a global transaction
10810 Trace snapshot too old
10811 Trace block cleanouts
10812 Trace Consistent Reads
10830 Trace group by sort row source
10841 Default un-inintialized charact set form to SQLCS_IMPLICIT
10850 Enable time manager tracing
10851 Allow Drop command to drop queue tables
10852 Enable dumping of the AQ statistics hash table
10853 Event for AQ statistics latch cleanup testing
10856 Disable AQ propagator from using streaming
10857 Force AQ propagator to use two-phase commit
10858 Crash the AQ propagator at different stages of commit
10859 Disable updates of message retry count
10860 Event for AQ admin disable new name parser
10861 Disable storing extended message properties
10862 Resolve default queue owner to current user in enqueue/dequeue
10900 Extent manager fault insertion event #%s
10902 Disable seghdr conversion for ro operation
10903 Force tablespaces to become locally managed
10904 Allow locally managed tablespaces to have user allocation
10905 Do cache verification (kcbcxx) on extent allocation
10906 Unable to extend segment after insert direct load
10907 Trace extent management events
10908 Trace temp tablespace events
10909 Trace free list events
10924 Import storage parse error ignore event
10925 Trace name context forever
10926 Trace name context forever
10927 Trace name context forever
10928 Trace name context forever
10929 Trace name context forever
10930 Trace name context forever
10931 Trace name context forever
10932 Trace name context forever
10933 Trace name context forever
10934 Reserved. Used only in version 7.x.
10935 Reserved. Used only in version 7.x.
10936 Trace name context forever
10937 Trace name context forever
10938 Trace name context forever
10939 Trace name context forever
10940 Trace name context forever
10941 Trace name context forever
10943 Trace name context forever
10944 Trace name context forever
10945 Trace name context forever
10975 Trace execution of parallel propagation
10976 Internal package related tracing
10977 Trace event for RepAPI
10979 Trace flags for join index implementation
10980 Prevent sharing of parsed query during Materialized View query generation
10981 Dscn computation-related event in replication
10982 Event to turn off CDC-format MV Logs
10983 Event to enable Create_Change_Table debugging
10984 Subquery materialized view-related event
10985 Event for NULL refresh of materialized views
10986 Do not use HASH_AJ in refresh
10987 Event for the support of caching table with object feature
10988 Event to get exclusive lock during materialized view refresh in IAS
10989 Event to internally create statistics MV
10999 Do not get database enqueue name
10999 End Pseudo-error debugging events

———————————- 事件编号和对应的描述 ———————————-

调优实践01–Oracle常见的等待事件及解决方案

Posted on 2014-04-15 by royalwzy

Oracle常见的等待事件及解决方案

等待事件的相关知识:
1. 等待事件主要可以分为两类,即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件;
  1. 空闲等待事件指ORACLE正等待某种工作,在诊断和优化数据库的时候,不用过多注意这部分事件;
  2. 非空闲等待事件专门针对ORACLE的活动,指数据库任务或应用运行过程中发生的等待,这些等待事件是在调整数据库的时候需要关注与研究的;
  3. 在Oracle 10g中的等待事件有872个,11gR2中等待事件1152个;可以通过v$event_name视图来查看等待事件的相关信息;
2. 查看等待事件的总个数:SELECT COUNT(*) FROM v$event_name;
3. 查看等待事件每个分类的分布:SELECT wait_class#, wait_class_id, wait_class, COUNT(*) FROM v$event_name GROUP BY wait_class#, wait_class_id, wait_class ORDER BY wait_class#;
4. 相关的视图:
  1. V$SESSION:连接到数据库的会话信息;
  2. V$SESSION_WAIT:当前会话正在等待的资源,此视图已经与v$session视图合并了;
  3. V$SESSION_WAIT_HISTORY:是对V$SESSION_WAIT的简单增强,记录活动SESSION的最近10次等待;
  4. V$SYSTEM_EVENT:由于V$SESSION记录的是动态信息,和SESSION的生命周期相关,而并不记录历史信息,所以ORACLE提供视图V$SYSTEM_EVENT来记录数据库自启动以来所有等待事件的汇总信息;通过这个视图,用户可以迅速获得数据库运行的总体概况;
  5. V$SQL:查看某个session正在执行的sql语句(SELECT t1.sid, t2.sql_text FROM v$session t1 INNER JOIN v$sql t2 ON t1.sql_id = t2.sql_id;SELECT t1.sid, t2.sql_text FROM v$session t1 INNER JOIN v$sql t2 ON t1.sql_address = t2.address;);
  6. V$ACTIVE_SESSION_HISTORY:是ASH的核心,用以记录活动SESSION的历史等待信息,每秒采样一次,这部分内容记录在内存中,记录一个小时的内容;
  7. DBA_HIST_ACTIVE_SESS_HISTORY:通过这个视图进行ASH历史数据的访问;
5. 可以从v$event_name视图中查看等待事件的类型(SELECT NAME, parameter1, parameter2, parameter3 FROM v$event_name;),v$session中P1-3表示等待的资源是什么;
buffer busy waits:
1. 从本质上讲,这个等待事件的产生仅说明了一个会话在等待一个Buffer(数据块),但是导致这个现象的原因却有很多种,常见的两种是:
  1. 当一个会话试图修改一个数据块,但这个数据块正在被另一个会话修改时;
  2. 当一个会话需要读取一个数据块,但这个数据块正在被另一个会话读取到内存中时;
2. Oracle操作的最小单位是块(Block),即使你要修改一条记录,也需要对这条记录所在的这个数据块做操作;当你对这个数据块做修改时,其他的会话将被阻止对这个数据块上的数据做修改(即使其他用户修改的不是当前用户修改的数据),但是可以以一致性的方式读取这个数据块(使用UNDO构建CR块);当前的用户修改完这个数据块后,将会立即释放掉加在这个数据块上的排他锁,这样另一个会话就可以继续修改它;修改操作是一个非常短暂的时间,这种加锁的机制我们叫Latch;
3. 当一个会话修改一个数据块时,是按照以下步骤来完成的:
  1. 以排他的方式获得这个数据块(Latch);
  2. 修改这个数据块;
  3. 释放Latch;
4. Buffer busy waits等待事件常见于数据库中存在的热快的时候,当多个用户频繁地读取或者修改同样的数据块时,这个等待事件就会产生;如果等待的时间很长,我们在AWR或者statspack报告中就可以看到;
5. 解决办法:
  1. 如果等待处于字段头部,应增加自由列(freelist)的组数,或者增加pctused到pctfree之间的距离;
  2. 如果等待处于回退段(undo)头部块,可以通过增加回滚段(rollback segment)来解决缓冲区的问题;
  3. 如果等待处于回退段(undo)非头部块上,就需要降低驱动一致读取的表中的数据密度,或者增大DB_CACHE_SIZE;
  4. 如果等待处于数据块,可以将数据移到另一数据块以避开这个”热”数据块,增加表中的自由列表或使用LMT表空间;
  5. 如果等待处于索引块,应该重建索引,分割索引或使用反向键索引;
6. 等待事件的参数:
  1. File#:等待访问数据块所在的文件id号;
  2. Blocks:等待访问的数据块号;
db file scattered read:
1. 这是一个用户操作引起的等待事件,当用户发出每次I/O需要读取多个数据块这样的SQL操作时,会产生这个等待事件,最常见的两种情况是:
  1. 全表扫描(FTS: Full Table Scan);
  2. 索引快速扫描(IFFS: index fast full scan);
2. 这个名称中的scattered(离散),可能会导致很多人认为它是以scattered的方式来读取数据块的,其实恰恰相反,当发生这种等待事件时,SQL的操作都是顺序地读取数据块的,比如FTS或者IFFS方式(如果忽略需要读取的数据块已经存在内存中的情况);
3. 这里的scattered指的是读取的数据块在内存中的存放方式,它们被读取到内存中后,是以分散的方式存在在内存中,而不是连续的;
4. 等待事件的参数:
  1. File#: 要读取的数据块所在数据文件的文件号;
  2. Block#: 要读取的起始数据块号;
  3. Blocks:需要读取的数据块数目;
db file sequential read:
1. 当Oracle需要每次I/O只读取单个数据块这样的操作时,会产生这个等待事件,最常见的情况:
  1. 索引的访问(除IFFS外的方式);
  2. 回滚操作;
  3. 以ROWID的方式访问表中的数据;
  4. 重建控制文件;
  5. 对文件头做DUMP等;
2. 这里的sequential也并非指的是Oracle按顺序的方式来访问数据,和db file scattered read一样,它指的是读取的数据块在内存中是以连续的方式存放的;
3. 等待事件的参数:
  1. File#:要读取的数据块锁在数据文件的文件号;
  2. Block#:要读取的起始数据块号;
  3. Blocks:要读取的数据块数目(这里应该等于1);
4. 查找热点快的方法:SELECT owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + &blocks – 1;
buffer latch:
1. 内存中数据块的存放位置是记录在一个hash列表(cache buffer chains)当中的;当一个会话需要访问某个数据块时,它首先要搜索这个hash列表,从列表中获得数据块的地址,然后通过这个地址去访问需要的数据块,这个列表Oracle会使用一个latch来保护它的完整性;当一个会话需要访问这个列表时,需要获取一个Latch,只有这样,才能保证这个列表在这个会话的浏览当中不会发生变化;
2. 产生buffer latch的等待事件的主要原因是:
  1. Buffer chains太长,导致会话搜索这个列表花费的时间太长,使其他的会话处于等待状态;
  2. 同样的数据块被频繁访问,就是我们通常说的热快问题;
3. 解决办法:产生buffer chains太长,我们可以使用多个buffer pool的方式来创建更多的buffer chains,或者使用参数DB_BLOCK_LRU_LATCHES来增加latch的数量,以便于更多的会话可以获得latch,这两种方法可以同时使用;
4. 等待事件的参数:
  1. Latch addr:会话申请的latch在SGA中的虚拟地址,通过以下的SQL语句可以根据这个地址找到它对应的Latch名称(SELECT * FROM v$latch a, v$latchname b WHERE a.addr = <latch addr> AND a.latch# = b.latch#;);
  2. chain#:buffer chains hash列表中的索引值,当这个参数的值等于0xfffffff时,说明当前的会话正在等待一个LRU latch;
control file parallel write:
1. 当数据库中有多个控制文件的拷贝时,Oracle需要保证信息同步地写到各个控制文件当中,这是一个并行的物理操作过程,因为称为控制文件并行写,当发生这样的操作时,就会产生control file parallel write等待事件;
2. 控制文件频繁写入的主要原因:
  1. 日志切换太过频繁,导致控制文件信息相应地需要频繁更新;
  2. 系统I/O出现瓶颈,导致所有I/O出现等待;
3. 解决办法:
  1. 当系统出现日志切换过于频繁的情形时,可以考虑适当地增大日志文件的大小来降低日志切换频率;
  2. 当系统出现大量的control file parallel write 等待事件时,可以通过比如降低控制文件的拷贝数量,将控制文件的拷贝存放在不同的物理磁盘上的方式来缓解I/O争用;
4. 等待事件的参数:
  1. Files:Oracle要写入的控制文件个数;
  2. Blocks:写入控制文件的数据块数目;
  3. Requests:写入控制请求的I/O次数;
control file sequential read:
1. 当数据库需要读取控制文件上的信息时,会出现这个等待事件,因为控制文件的信息是顺序写的,所以读取的时候也是顺序的,因此称为控制文件顺序读;
2. 控制文件频繁读取的主要原因:
  1. 备份控制文件;
  2. RAC环境下不同实例之间控制文件的信息共享;
  3. 读取控制文件的文件头信息;
  4. 读取控制文件其他信息;
3. 等待事件的参数:
  1. File#:要读取信息的控制文件的文件号;
  2. Block#:读取控制文件信息的起始数据块号;
  3. Blocks:需要读取的控制文件数据块数目;
db file parallel read:
1. 这是一个很容易引起误导的等待事件,实际上这个等待事件和并行操作(比如并行查询,并行DML)没有关系;这个事件发生在数据库恢复的时候,当有一些数据块需要恢复的时候,Oracle会以并行的方式把他们从数据文件中读入到内存中进行恢复操作;
2. 等待事件的参数:
  1. Files:操作需要读取的文件个数;
  2. Blocks:操作需要读取的数据块个数;
  3. Requests:操作需要执行的I/O次数;
db file parallel write:
1. 这是一个后台等待事件,它同样和用户的并行操作没有关系,它是由后台进程DBWR产生的,当后台进程DBWR想磁盘上写入脏数据时,会发生这个等待;
2. DBWR会批量地将脏数据并行地写入到磁盘上相应的数据文件中,在这个批次作业完成之前,DBWR将出现这个等待事件;如果仅仅是这一个等待事件,对用户的操作并没有太大的影响,当伴随着出现free buffer waits等待事件时,说明此时内存中可用的空间不足,这时候会影响到用户的操作,比如影响到用户将脏数据块读入到内存中;
3. 当出现db file parallel write等待事件时,可以通过启用操作系统的异步I/O的方式来缓解这个等待;当使用异步I/O时,DBWR不在需要一直等到所有数据块全部写入到磁盘上,它只需要等到这个数据写入到一个百分比之后,就可以继续进行后续的操作;
4. 等待事件的参数:
  1. Requests:操作需要执行的I/O次数;
  2. Timeouts:等待的超时时间;
Db file single write:
1. 这个等待事件通常只发生在一种情况下,就是Oracle更新数据文件头信息时(比如发生Checkpoint);
2. 当这个等待事件很明显时,需要考虑是不是数据库中的数据文件数量太大,导致Oracle需要花较长的时间来做所有文件头的更新操作(checkpoint);
3. 等待事件的参数:
  1. File#:需要更新的数据块所在的数据文件的文件号;
  2. Block#:需要更新的数据块号;
  3. Blocks:需要更新的数据块数目(通常来说应该等于1);
direct path read:
1. 这个等待事件发生在会话将数据块直接读取到PGA当中而不是SGA中的情况,这些被读取的数据通常是这个会话私有的数据,所以不需要放到SGA作为共享数据,因为这样做没有意义;这些数据通常是来自与临时段上的数据,比如一个会话中SQL的排序数据,并行执行过程中间产生的数据,以及Hash Join,merge join产生的排序数据,因为这些数据只对当前的会话的SQL操作有意义,所以不需要放到SGA当中;
2. 当发生direct path read等待事件时,意味着磁盘上有大量的临时数据产生,比如排序,并行执行等操作;或者意味着PGA中空闲空间不足;
3. 等待事件的参数:
  1. Descriptor address:一个指针,指向当前会话正在等待的一个direct read I/O;
  2. First dba:descriptor address中最旧的一个I/O数据块地址;
  3. Block cnt:descriptor address上下文中涉及的有效的buffer数量;
direct path write:
1. 这个等待事件和direct path read正好相反,是会话将一些数据从PGA中直接写入到磁盘文件上,而不经过SGA;
  1. 使用临时表空间排序(内存不足);
  2. 数据的直接加载(使用append方式加载数据);
  3. 并行DML操作;
2. 等待事件的参数:
  1. Descriptor address:一个指针,指向当前会话正在等待的一个direct read I/O;
  2. First dba:descriptor address中最旧的一个I/O数据块地址;
  3. Block cnt:descriptor address上下文中涉及的有效的buffer数量;
enqueue:
1. Enqueue这个词其实是lock的另一种描述语;
2. 当我们在AWR报告中发现长时间的enqueue等待事件时,说明数据库中出现了阻塞和等待,可以关联AWR报告中的enqueue activity部分来确定是哪一种锁定出现了长时间等待;
3. 等待事件的参数:
  1. Name:enqueue的名称和类型;
  2. Mode:enqueue的模式;
4. 查看当前会话等待的enqueue名称和类型:SELECT chr(to_char(bitand(p1, -16777216)) / 16777215) || chr(to_char(bitand(p1, 16711680)) / 65535) “Lock”, to_char(bitand(p1, 65535)) “Mode” FROM v$session_wait WHERE event = ‘enqueue’;
5. 模式代码及解释:
  1. 1-Null mode
  2. 2-Sub-Share
  3. 3-Sub-Exclusive
  4. 4-Share
  5. 5-Share/Sub-Exclusive
  6. 6-Exclusive
6. Enqueue的缩写及解释;
free buffer waits:
1. 当一个会话将数据块从磁盘读到内存中时,它需要到内存中找到空闲的内存空间来存放这些数据块,当内存中没有空闲的空间时,就会产生这个等待;除此之外,还有一种情况就是会话在做一致性读时,需要构造数据块在某个时刻的前映像(image),此时需要申请内存来存放这些新构造的数据块,如果内存中无法找到这样的内存块,也会发生这个等待事件;
2. 当数据库中出现比较严重的free buffer waits等待事件时,可能的原因是:
  1. Data buffer太小,导致内存空间不够;
  2. 内存中的脏数据太多,DBWR无法及时将这些脏数据写到磁盘中以释放空间
3. 解决办法:
  1. 加大db_buffer_cache大小;
  2. 增加dbwr进程数量;
  3. 增加检查点或者物理磁盘的数量;
4. 等待事件的参数:
  1. File#:需要读取的数据块所在的数据文件的文件号;
  2. Block#:需要读取的数据块块号;
latch free:
1. 在10g之前的版本里,latch free等待事件代表了所有的latch等待,在10g以后,一些常用的latch事件已经被独立了出来:所以latch free等待事件在10g以后的版本中并不常见,而是以具体的Latch等待事件出现;
2. latch是一种低级排队机制(它们被准确地称为相互排斥机制),用于保护系统全局区域(SGA)中共享内存结构;latch就像是一种快速地被获取和释放的内存锁;latch用于防止共享内存结构被多个用户同时访问;如果latch不可用,就会记录latch释放失败;大多数latch 问题都与以下操作相关:
  1. 不能使用邦定变量(库缓存latch);
  2. 重复生成问题(重复分配latch);
  3. 缓冲存储器竞争问题(缓冲器存储LRU 链),以及缓冲存储器中的”热”块(缓冲存储器链);
  4. 也有一些latch等待与bug(程序错误)有关;
3. 查看latch相关的等待事件:SELECT NAME FROM v$event_name WHERE NAME LIKE ‘latch%’;
4. 查找事件热点对象的sql语句;
5. latch问题的解决办法:
  1. Library Cache and Shared Pool(未绑定变量—绑定变量,调整shared_pool_size):每当执行SQL或PL/SQL存储过程,包,函数和触发器时,这个Latch即被用到.Parse操作中此Latch也会被频繁使用;
  2. Redo Copy(增大_LOG_SIMULTANEOUS_COPIES参数):重做拷贝Latch用来从PGA向重做日志缓冲区拷贝重做记录;
  3. Redo Allocation(最小化REDO生成,避免不必要提交):此Latch用来分配重做日志缓冲区中的空间,可以用NOLOGGING来减缓竞争;
  4. Row Cache Objects(增大共享池):数据字典竞争,过度parsing;
  5. Cache Buffers Chains(_DB_BLOCK_HASH_BUCKETS应增大或设为质数):”过热”数据块造成了内存缓冲链Latch竞争;
  6. Cache Buffers Lru Chain(调整SQL,设置DB_BLOCK_LRU_LATCHES,或使用多个缓冲区池):扫描全部内存缓冲区块的LRU(最近最少使用)链时要用到内存缓冲区LRU链Latch;太小内存缓冲区,过大的内存缓冲区吞吐量,过多的内存中进行的排序操作,DBWR速度跟不上工作负载等会引起此Latch竞争;
6. 等待事件的参数:
  1. Address:会话等待的latch地址;
  2. Number:latch号,通过这个号,可以从v$latchname视图中找到这个latch的相关的信息(SELECT * FROM v$latchname WHERE latch# = NUMBER;);
  3. Tries:会话尝试获取Latch的次数;
library cache lock:
1. 这个等待时间发生在不同用户在共享中由于并发操作同一个数据库对象导致的资源争用的时候,比如当一个用户正在对一个表做DDL操作时,其他的用户如果要访问这张表,就会发生library cache lock等待事件,它要一直等到DDL操作完成后,才能继续操作;
2. 该事件通常是由于执行多个DDL操作导致的,即在library cache object上添加一个排它锁后,又从另一个会话给它添加一个排它锁,这样在第二个会话就会生成等待;可通过到基表x$kgllk中查找其对应的对象;
3. 查询引起该等待事件的阻塞者的sid,会话用户,锁住的对象的脚本;
4. 等待事件的参数:
  1. Handle address:被加载的对象的地址;
  2. Lock address:锁的地址;
  3. Mode:被加载对象的数据片段;
  4. Namespace:被加载对象在v$db_object_cache视图中namespace名称;
Library cache pin
1. 这个等待事件和library cache lock一样是发生在共享池中并发操作引起的事件;通常来讲,如果Oracle要对一些PL/SQL或者视图这样的对象做重新编译,需要将这些对象pin到共享池中;如果此时这个对象被其他的用户特有,就会产生一个library cache pin的等待;
2. P1,P2可与x$kglpn和x$kglob表相关:
  1. X$KGLOB (Kernel Generic Library Cache Manager Object);
  2. X$KGLPN (Kernel Generic Library Cache Manager Object Pins);
3. 相关锁的查询sql;
4. 等待事件的参数:
  1. Handle address:被加载的对象的地址;
  2. Lock address:锁的地址;
  3. Mode:被加载对象的数据片段;
  4. Namespace:被加载对象在v$db_object_cache视图中namespace名称;
log file parallel write:
1. 后台进程LGWR负责将log buffer当中的数据写到REDO文件中,以重用log buffer的数据;如果每个REDO LOG组里面有2个以上的成员,那么LGWR进程会并行地将REDO信息写入这些文件中;
2. 如果数据库中出现这个等待事件的瓶颈,主要的原因可能是磁盘I/O性能不够或者REDO文件的分布导致了I/O争用,比如同一个组的REDO成员文件放在相同的磁盘上;
3. 等待事件的参数:
  1. Files:操作需要写入的文件个数;
  2. Blocks:操作需要写入的数据块个数;
  3. Requests:操作需要执行的I/O次数;
log buffer space:
1. 当log buffer中没有可用空间来存放新产生的redo log数据时,就会发生log buffer space等待事件;如果数据库中新产生的redo log的数量大于LGWR写入到磁盘中的redo log数量,必须等待LGWR完成写入磁盘的操作,LGWR必须确保redo log写到磁盘成功之后,才能在redo buffer当中重用这部分信息;
2. 如果数据库中出现大量的log buffer space等待事件,可以考虑如下方法:
  1. 增加redo buffer的大小;
  2. 提升磁盘的I/O性能;
log file sequential read:
1. 这个等待事件通常发生在对redo log信息进行读取时,比如在线redo的归档操作,ARCH进程需要读取redo log的信息,由于redo log的信息是顺序写入的,所以在读取时也是按照顺序的方式来读取的;
2. 等待事件的参数:
  1. Log#:发生等待时读取的redo log的sequence号;
  2. Block#:读取的数据块号;
  3. Blocks:读取的数据块个数;
log file single write:
1. 这个等待事件发生在更新redo log文件的文件头时,当为日志组增加新的日志成员时或者redo log的sequence号改变时,LGWR都会更新redo log文件头信息;
2. 等待事件的参数:
  1. Log#:发生等待时读取的redo log的sequence号;
  2. Block#:读取的数据块号;
  3. Blocks:读取的数据块个数;
log file switch(archiving needed):
1. 在归档模式下,这个等待事件发生在在线日志切换(log file switch)时,需要切换的在线日志还没有被归档进程(ARCH)归档完毕的时候;当在线日志文件切换到下一个日志时,需要确保下一个日志文件已经被归档进程归档完毕,否则不允许覆盖那个在线日志信息(否则会导致归档日志信息不完整);
2. 出现这样的等待事件通常是由于某种原因导致ARCH进程死掉,比如ARCH进程尝试向目的地写入一个归档文件,但是没有成功(介质失效或者其他原因),这时ARCH进程就会死掉;如果发生这种情况,在数据库的alert log文件中可以找到相关的错误信息;
log file switch(checkpoint incomplete):
1. 当一个在线日志切换到下一个在线日志时,必须保证要切换到的在线日志上的记录的信息(比如一些脏数据块产生的redo log)被写到磁盘上(checkpoint),这样做的原因是,如果一个在线日志文件的信息被覆盖,而依赖这些redo信息做恢复的数据块尚未被写到磁盘上(checkpoint),此时系统down掉的话,Oracle将没有办法进行实例恢复;
2. 在v$log视图里记录了在线日志的状态,在线日志有三种状态:
  1. Active:这个日志上面保护的信息还没有完成checkpoint;
  2. Inactive:这个日志上面保护的信息已完成checkpoint;
  3. Current:当前的日志;
3. Oracle在做实例恢复时,会使用状态为current和Active的日志进行实例恢复;
4. 如果系统中出现大量的log file switch(checkpoint incomplete)等待事件,原因可能是日志文件太小或者日志组太少,所以解决的方法是,增加日志文件的大小或者增加日志组的数量;
log file sync:
1. 这是一个用户会话行为导致的等待事件,当一个会话发出一个commit命令时,LGWR进程会将这个事务产生的redo log从log buffer里面写到磁盘上,以确保用户提交的信息被安全地记录到数据库中;会话发出的commit指令后,需要等待LGWR将这个事务产生的redo成功写入到磁盘之后,才可以继续进行后续的操作,这个等待事件就叫作log file sync;
2. 当系统中出现大量的log file sync等待事件时,应该检查数据库中是否有用户在做频繁的提交操作;
3. 这种等待事件通常发生在OLTP系统上,OLTP系统中存在很多小的事务,如果这些事务频繁被提交,可能引起大量的log file sync的等待事件;
4. 等待事件的参数:
  1. Buffer#:redo buffer中需要被写入到磁盘中的buffer;
SQL*Net相关的等待事件:
1. SQL*Net break/reset to client:当出现这个等待事件时,说明服务器端在给客户端发送一个断开连接或者重置连接的请求,正在等待客户的响应,通常的原因是服务器到客户端的网络不稳定导致的;
2. SQL*Net break/reset to dblink:这个等待事件和SQL*Net break/reset to client相同;不过它表示的是数据库通过dblink访问另一台数据库时,他们之间建立起一个会话,这个等待事件发生在这个会话之间的通信过程中,同样如果出现这个等待事件,需要检查两台数据库之间的通信问题;
3. SQL*Net message from client:这个等待事件基本上是最常见的一个等待事件;当一个会话建立成功后,客户端会向服务器端发送请求,服务器端处理完客户端请求后,将结果返回给客户端,并继续等待客户端的请求,这时候会产生SQL*Net message from client 等待事件;很显然,这是一个空闲等待,如果客户端不再向服务器端发送请求,服务器端将一直处于这个等待事件状态;
4. SQL*Net message from dblink:这个等待事件和SQL*Net message from client相同,不过它表示的是数据库通过dblink 访问另一个数据库时,他们之间会建立一个会话,这个等待事件发生在这个会话之间的通信过程中;这个等待事件也是一个空闲等待事件;
5. SQL*Net message to client:这个等待事件发生在服务器端向客户端发送消息的时候; 当服务器端向客户端发送消息产生等待时,可能的原因是用户端太繁忙,无法及时接收服务器端送来的消息,也可能是网络问题导致消息无法从服务器端发送到客户端;
6. SQL*Net message to dblink:这个等待事件和SQL*Net message to client相同,不过是发生在数据库服务器和服务器之间的等待事件,产生这个等待的原因可能是远程服务器繁忙,而无法及时接收发送过来的消息,也可能是服务器之间网络问题导致消息无法发送过来;
7. SQL*Net more data from client:服务器端等待用户发出更多的数据以便完成操作,比如一个大的SQL文本,导致一个SQL*Net 数据包无法完成传输,这样服务器端会等待客户端把整个SQL 文本发过来在做处理,这时候就会产生一个SQL*Net more data from client等待事件;
8. SQL*Net more data from dblink:在一个分布式事务中,SQL 分布在不同的数据库中执行,远程数据库执行完毕后将结果通过dblink返给发出SQL的数据库,在等待数据从其他数据库中通过dblink传回的过程中,如果数据在远程数据库上处理时间很久,或者有大量的结果集需要返回,或者网络性能问题都会产生SQL*Net more data from dblink 等待事件,它的意思是本地数据库需要等到所有的数据从远程处理完毕通过dblink传回后,才可以在本机继续执行操作;
9. SQL*Net more data to client:当服务器端有太多的数据需要发给客户端时,可能会产生SQL*Net more data to client等待事件,也可能由于网络问题导致服务器无法及时地将信息或者处理结果发送给客户端,同样会产生这个等待;
10. SQL*Net more data to dblink:这个等待事件和SQL*Net more data to client 等待时间基本相同,只不过等待发生在分布式事务中,即本地数据库需要将更多的数据通过dblink发送给远程数据库;由于发送的数据太多或者网络性能问题,就会出现SQL*Net more data to dblink等待事件;
11. 它们的等待事件参数一致:
  1. Driver id:服务器端和客户端连接使用的协议信息;
  2. #bytes:服务器端通过dblink发送给另一个服务器消息的字节数;

———————- latch free:查找事件热点对象的sql语句 ———————-

&2值是v$session_wait中的P1RAW,x$bh中的字段Hladdr表示该block buffer在哪个cache buffer chain latch上,可以通过v$latch_children定位哪些segment是热点块;

SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
FROM x$bh a, dba_objects b
WHERE (a.obj = b.object_id OR a.obj = b.data_object_id) AND
a.hladdr = &2
UNION
SELECT hladdr, file#, dbablk, tch, obj, NULL
FROM x$bh
WHERE obj IN (SELECT obj
FROM x$bh
WHERE hladdr = &2
MINUS
SELECT object_id
FROM dba_objects
MINUS
SELECT data_object_id FROM dba_objects) AND
hladdr = &2
ORDER BY 4;

———————- latch free:查找事件热点对象的sql语句 ———————-

———————- 查询引起library cache lock等待事件的阻塞者的sid,会话用户,锁住的对象的脚本 ———————-

SELECT b.sid, a.user_name, a.kglnaobj
FROM x$kgllk a, v$session b
WHERE a.kgllkhdl IN (SELECT p1raw
FROM v$session_wait
WHERE wait_time = 0 AND
event = ‘library cache lock’) AND
a.kgllkmod <> 0 AND
b.saddr = a.kgllkuse;

———————- 查询引起library cache lock等待事件的阻塞者的sid,会话用户,锁住的对象的脚本 ———————-

———————- library cache lock等待事件相关锁的查询sql ———————-

— 查询X$KGLOB,可找到相关的object,其SQL语句如下(即把V$SESSION_WAIT中的P1raw与X$KGLOB中的KGLHDADR相关连);
SELECT kglnaown, kglnaobj
FROM x$kglob
WHERE kglhdadr = (SELECT p1raw FROM v$session_wait WHERE event = ‘library cache pin’);
— 查出引起该等待事件的阻塞者的sid;
SELECT sid
FROM x$kglpn, v$session
WHERE kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE wait_time = 0 AND
event LIKE ‘library cache pin%’) AND
kglpnmod <> 0 AND
v$session.saddr = x$kglpn.kglpnuse;
— 查出阻塞者正执行的SQL语句
SELECT sid, sql_text
FROM v$session, v$sqlarea
WHERE v$session.sql_address = v$sqlarea.address AND
sid = < 阻塞者的sid >;

———————- library cache lock等待事件相关锁的查询sql ———————-

———————- Enqueue缩写及解释 ———————-
BL:Buffer Cache management
BR:Backup/Restore
CF:Controlfile transaction
CI:Cross-instance Call Invocation
CU:Bind Enqueue
DF:Datafile
DL:Direct Loader Index Creation
DM:Database Mount
DR:Distributed Recovery Process
DX:Dirstributed Transaction
FP:File Object
FS:File Set
HW:High-water Lock
IN:Instance Number
IR:Instance Recovery
IS:Instance State
IV:Library Cache Invalidation
JI:Enqueue used during AJV snapshot refresh
JQ:Job Queue
KK:Redo Log “Kick”
KO:Multiple Object Checkpoint
L[A-p]:Library Cache Lock
LS:Log start or switch
MM:Mount Definition
MR:Media recovery
N[A-Z]:Library Cache bin
PE:Alter system set parameter =value
PF:Password file
PI:Parallel slaves
PR:Process startup
PS:Parallel slave synchronization
Q[A-Z]:Row Cache
RO:Object Reuse
RT:Redo Thread
RW:Row Wait
SC:System Commit Number
SM:SMON
SN:Sequence Number
SQ:Sequence Number Enqueue
SR:Synchronized replication
SS:Sort segment
ST:Space management transaction
SV:Sequence number Value
TA:Transaction recovery
TC:Thread Checkpoint
TE:Extend Table
TM:DML enqueue
TO:Temporary Table Object Enqueue
TS:Temporary Segement(also TableSpace)
TT:Temporary Table
TX:Transaction
UL:User-defined Locks
UN:User name
US:Undo segment, Serialization
WL:Being Written Redo Log
XA:Instance Attribute Log
XI:Instance Registration Lock

———————- Enqueue缩写及解释 ———————-

SQL调优01–SQL优化介绍

Posted on 2012-01-14 by royalwzy

Introduction to SQL Tuning

SQL效率太低的原因:
1. Stale or missing optimizer statistics:缺失优化统计信息或者信息太旧;
2. Missing access structures:缺少索引,考虑索引的效率;
3. Suboptimal execution plan selection:不是最好的执行计划;
4. Poorly constructed SQL:SQL语句写的不好;
5. 最重要的是表的数据量太大,归档历史数据.小的数据量可以解决一切问题;
6. 解决办法:
  1. 尽量不要用子查询,可以通过关联查询解决;
  2. 不要再表列上面使用函数,导致索引无效;
  3. 如果发生隐式转换也不走索引,因为oracle内部总是转换表的列;
  4. 尽量使用UNION ALL而不用UNION;
  5. 排序,去重复,分组现在默认使用hash去除重复,对CPU消耗很大;
性能监控的解决方案;
Oracle中监控和调优的工具;
调优的工具:
1. Automatic Database Diagnostic Monitor (ADDM);
2. SQL Tuning Advisor;
3. SQL Tuning Sets;
4. SQL Access Advisor;
5. SQL Performance Analyzer;
6. SQL Monitoring;
7. SQL Plan Management:在11g中的工具,可以控制某个sql的执行计划;
SQL调优的任务:
1. 查找高负载的SQL语句;
2. 收集统计信息;
3. 收集系统统计信息;
4. 重建已存在的索引;
5. 维护执行计划;
6. 创建新的索引;
CPU和Wait Time的调优
1. db_time=cpu_time+wait_time;
2. 如果db_time增加,cpu_time和wait_time等比例增加,说明这是一个可扩展的系统,只需增加硬件即可;
3. 如果db_time增加,cpu_time远大于wait_time的增加,说明SQL效率不高,需要SQL的优化;
4. 如果db_time增加,cpu_time远小于wait_time的增加,说明内部有争用或者IO效率太低;
客户系统的常见问题:
1. Bad connection management:可以使用连接池解决;
2. Bad use of cursors and the shared pool:适当调大SGA和PGA,并指定动态管理;
3. Excess of resources consuming SQL statements:sql要反复执行;
4. Use of nonstandard initialization parameters:使用了隐含参数或者参数使用不当;
5. Poor database disk configuration:IO问题;
6. Redo log setup problems:至少使用三组在线日志组,每组设置足够大,保证20分钟切换一次;
7. Excessive serialization:串行化扫描,添加索引,尽量使用单列索引,可控性比较强;
8. Inappropriate full table scans:全表扫描,主要是加索引解决;
9. Large number of space-management or parse-related generated SQL statements:如果使用本地管理表空间的的话一般不会出现递归SQL;
10. Deployment and migration errors:部署时出错,这个是人为原因;
应用的设计:
1. 简化设计;
2. 数据模型:
  1. 主要还是要与业务逻辑相结合;
  2. 可以使用建模工具如Oracle Designer,但是最好是使用详细的文档;
  3. 考虑是OLTP系统还是DW系统;
3. 表设计:
  1. 考虑使用默认值,约束,物化视图,分区表等特性;
  2. 分区列一定要在where条件上,而且最好不要更新;
  3. 不建议使用触发器;,触发器的目的是做check,而不是做DML;
  4. 不建议使用外键,可以使用程序保证数据的完整性;
4. 索引设计:
  1. 索引的列一定要经常出现在WHERE后面;
  2. 在DW中建议使用外键,在OLTP中考虑到性能可以不用外键,加外键的话就一定要加索引;
5. 视图:
  1. 可以使用视图,但是最好不要嵌套视图;
  2. 嵌套视图影响执行计划;
Share Cursors
1. 尽量使用存储过程和函数;
2. cursor_sharing初始化参数尽量不要修改;

SQL调优04–阅读执行计划

Posted on 2012-01-14 by royalwzy

Interpreting Execution Plans

执行计划的解释:
1. SQL语句的执行计划是由语句中行源的执行计划组成;
2. 执行计划是使用父子关系来描述的,像一个树的结构;
如何查看执行计划:
1. PLAN_TABLE:是由EXPLAIN PLAN命令或者SQL/PLUS的autotrace产生的执行计划,是理论上的执行计划;
2. v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
3. v$sql_plan_monitor:11g中的执行计划监控;
4. dba_hist_sql_plan:由AWR报告产生的执行计划;
5. stats$sql_plan:是由Statspack生成的执行计划;
6. SQL Management Base:是由SQL Plan Management Baselines产生的执行计划;
7. SQL tuning set;
8. DBMS_MONITOR产生的trace文件:相当于10046事件;
9. 由10053事件产生的trace文件;
10. 10gR2之后的dump跟踪文件;
查看执行计划的视图:
1. 如果直接查看基表的话,根本无法直接看到执行计划间的关系,自己编写SQL语句查看很麻烦,可以使用DBMS_XPLAN包下面的函数来完成;
2. DBMS_XPLAN.DISPLAY():用来显示plan_table中的执行计划;
3. DBMS_XPLAN.DISPLAY_CURSOR():用来显示v$sql_plan中的执行计划;
4. DBMS_XPLAN.DISPLAY_AWR():用来显示AWR中的执行计划;
5. DBMS_XPLAN.DISPLAY_SQLSET():用来显示SQL tuning set中的执行计划;
6. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE():用来显示SQL Plan Management Baselines中的执行计划;
EXPLAIN TABLE命令:
1. 生成一个最优的执行计划,把它存在PLAN_TABLE中,但是并不实际执行SQL语句;
2. 语法:EXPLAIN PLAN [SET STATEMENT_ID = ‘text’] [INTO plan_table] FOR statement;默认插入到PLAN_TABLE表中;
3. PLAN_TABLE:
  1. 当执行EXPLAN_PLAN命令时自动创建PLAN_TABLE,它是一个同义词,指向sys.plan_table$的临时表;SELECT * FROM dba_synonyms WHERE synonym_name = ‘PLAN_TABLE’;SELECT table_name, TEMPORARY, duration FROM dba_tables WHERE table_name = ‘PLAN_TABLE$’;
  2. 可以根据$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本创建自己的表,因为默认是临时表,只能在当前session查看,导入到自己的表中就可以永久保存;
  3. 优点是SQL语句么有真正执行;缺点是可能不是真正的执行计划,只有使用绑定变量时执行计划不准,其它情况都准确;
  4. 表中的内容是层级结构,可以通过ID和PAREANT_ID列来关联;
4. DBMS_XPLAN.DISPLAY函数语法:DBMS_XPLAN.DISPLAY(table_name, statement_id, format, filter_preds):
  1. table_name:默认是PLAN_TABLE表;
  2. statement_id:默认是空,可以根据这个参数获得指定的语句的执行计划;
  3. format:默认是TYPICAL类型,其他类型查帮助文档,显示的信息多少;
  4. 默认只查看上一条语句的执行计划;
  5. 查看指定statement_id的执行计划;
  6. 查看更多的执行计划的信息;
AUTOTRACE:
1. AUTOTRACE是sql*plus的功能,在oracle7.3版本后出现,也是把记录存放在PLAN_TABLE表中;
2. 需要PLUSTRACE角色从v$视图中检索统计信息,使用$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本创建;
3. 默认情况下,在执行完查询语句后会生成执行计划和统计信息;
4. 相当于执行了一次EXPLAIN PLAN命令然后执行了一次语句,如果使用绑定变量的话可能不是真实的计划;
5. 语法:SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]];
  1. ON:要显示结果和trace信息;
  2. TRACEONLY:不显示结果;
6. 查看当前的设置:show autotrace;
阅读统计信息:
1. recursive calls:递归的调用,读取数据字典,权限,列的信息.第一次执行会很大,以后执行会变小;如果使用存储过的话,这个值一般会很大,属于正常;可以通过清除shared_pool测试:alter system flush shared_pool;
2. db block gets:修改当前状态的数据块的block的块数.只有当DML语句会引起db block gets增加,因为当前块会被更新,SELECT语句的话不会增加,因为可以读取REDO或者构造的CR块;
3. consistent gets:逻辑读的数量(不是BLOCK),表示返回记录的批次数,跟当前的arraysize有关;
  1. arraysize:表示一次返回的记录数,通过show arraysize命令查看;
  2. 粗略是算法是:consistent gets=rows processed/arraysize,记录越多越接近;
  3. 优化时应该关心在相同的arraysize下减小此值,即减小逻辑读;
4. physical reads:物理读,即从硬盘读取的BLOCK的数量,BUFFER CACHE越大这个值越小,可以通过清除BUFFER CACHE测试:alter system flush buffer_cache;
5. redo size:产生的日志的数量,一般DML语句才会产生;
6. bytes sent via SQL*Net to client:服务器发送到客户端的字节数;
7. bytes received via SQL*Net from client:服务器接收到客户端的字节数;
8. SQL*Net roundtrips to/from client:SQL的网络流量的次数,也跟arraysize参数有关;
9. sorts (memory):内存中的排序数量,主要是PGA;
10. sorts (disk):在硬盘的排序,应该避免这个值;
11. rows processed:处理的记录数;
v$sql_plan:
1. v$sql_plan:查看library cahce中真正使用的执行计划;PLAN_TABLE只是理论上的执行计划;
2. 可以通过sql_id列与v$sql表关联,也可以使用address和hash_value的值;
3. 主要的列:
  1. HASH_VALUE:父语句在library cache中的哈希值;
  2. ADDRESS:访问SQL语句的句柄,即内存地址;
  3. CHILD_NUMBER:使用此执行计划的子CURSOR数量;
  4. POSITION:具有相同PARENT_ID的操作的执行顺序;
  5. PARENT_ID:跳出过程的下一个执行的过程ID,这个很抽象,看到执行计划,很容易理解这一点;
  6. ID:每一个步骤的编号;
  7. PLAN_HASH_VALUE:执行计划的哈希值;
4. 查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
5. v$sql_plan_statistics:提供实际执行时的统计信息
  1. 当STATISTICS_LEVEL设置为ALL时才会收集;
  2. 或者语句中指定了GATHER_PLAN_STATISTICS的hint;
  3. v$sql_plan_statistics_all:获得所有的实际执行的统计信息;
6. v$sql_workarea:提供了SQL CURSOR使用的工作区的信息;
AWR:
1. AWR是为了检测和自调整为目的的收集,处理,维护性能统计信息;
2. 统计信息包括:
  1. 对象统计信息;
  2. 时间模型统计信息;
  3. 一些系统和session的统计信息;
  4. ASH(Active Session History)统计信息;
3. 自动生成性能数据的快照;
4. 重要的AWR视图:
  1. V$ACTIVE_SESSION_HISTORY;
  2. V$metric views;
  3. DBA_HIST views:
    1. DBA_HIST_ACTIVE_SESS_HISTORY;
    2. DBA_HIST_BASELINE;
    3. DBA_HIST_DATABASE_INSTANCE;
    4. DBA_HIST_SNAPSHOT;
    5. DBA_HIST_SQL_PLAN;
    6. DBA_HIST_WR_CONTROL;
5. 指定sql_id查看AWR中的sql的执行计划: SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(‘g22czkqq3pxmb’));
6. 从AWR数据生成一个SQL报告:@$ORACLE_HOME/rdbms/admin/awrsqrpt;
SQL Monitoring:11g;
阅读执行计划:
1. 读执行计划的顺序:
  1. 从上往下看,第一个没有儿子节点的节点最先执行;
  2. 执行执行其兄弟节点;
  3. 最后执行父节点;
2. 就是二叉树中的后序遍历的方式:
  1. 前序遍历:对任一子树,先访问根,然后遍历其左子树,最后遍历其右子树;
  2. 中序遍历:对任一子树,先遍历其左子树,然后访问根,最后遍历其右子树;
  3. 后序遍历:对任一子树,先遍历其左子树,然后遍历其右子树,最后访问根;
3. 例子:
  1. 执行的顺序为:356421;
  2. 执行的顺序为:43652871;
  3. 执行顺序为:325410;
4. 查看执行计划的建议:
  1. 要使驱动表保持最好的过滤条件,即驱动表有最小的记录;
  2. 每一步返回的数据尽量最小;
  3. 正确使用视图,只是用一层,尽量不要嵌套;
  4. 避免使用笛卡尔积;
仅仅靠一个执行计划不能说明它是否是最好的,可以借助SQL Tuning Advisor工具;

SQL调优03–执行计划的访问路径

Posted on 2012-01-14 by royalwzy

Optimizer operators

行源操作
1. Unary Operations:一元运算,即单表的查询;
2. Binary Operations:二元运算,两表的连接;
3. N-ary Operations:多元运算;
主要的结构和访问路径:
1. 表:
  1. Full Table Scan;
  2. Rowid Scan:很少使用,多用在内部的某一个步骤;
  3. Sample Table Scan:很少使用;
2. 索引:
  1. Index Scan(Unique);
  2. Index Scan(Range);
  3. Index Scan(Full);
  4. Index Scan(Fast Full);
  5. Index Scan(Skip);
  6. Index Scan(Index Join);
  7. Using Bitmap Indexes;
  8. Combining Bitmap Indexes;
索引的基本概念:
1. B-Tree Indexes:平衡树索引,最常见的索引;
  1. 正常索引;
  2. 基于函数的索引:
    1. 创建函数索引相当于在表上添加一个伪列;
    2. 查看定义;
  3. IOT(Index-Organized Table):将表结构整体放入索引中,而且按照主键进行排序,一定要有主键,非主键的列一定要落在索引条目里;
  4. Bitmap Indexes;
    1. 可以索引空值;
    2. 适当发生转换:TO ROWIDS/FROM ROWIDS/COUNT;
    3. 可以进行的操作:MERGE/AND/OR/MINUS/KEY ITERATION,位运算的速度很快;
    4. 位图索引可以进行SINGLE VALUE/ RANGE SCAN/ FULL SCAN扫描;
    5. 缺点是位图索引不能经常更新,效率很差;
  5. Cluster Indexes;
    1. 如果要做两个表的关联查询则最少查询两个块;
    2. CLUSTER把两个表按照关联的字段把记录存放在同一个块上;这样只用查一个块即可;查找时效率提高一倍;
    3. 用在总是关联查询两个表的情况,一般是不用的;ORACLE内部大量使用;
    4. cluster上的索引不能指定列,必须使用所有的列;
    5. 基于cluster的表没有segment;
2. 索引的属性:
  1. 键压缩;
  2. 反转键值(考点):可以防止索引块争用(buffer busy wait),只支持等式连接,不支持范围扫描;
  3. 顺序/倒序;
3. 索引和NULL值:
  1. NULL值与索引的关系:
    1. 基于单列的唯一索引,可以多次插入NULL值(NULL <> NULL),因为索引并不存储NULL值;
    2. 基于多列的符合索引,尽管全为NULL的值可以多次插入([NULL, NULL] <> [NULL, NULL]),索引也不会存储,但不全为NULL的重复行则不能重复插入,;
  2. NULL值与执行计划:
    1. 如果列的属性允许为NULL,条件为IS NULL的话,肯定走全表扫描,因为索引不保存NULL值;
    2. 如果列的属性允许为NULL,条件为IS NOT NULL的话,会走全索引扫描;
    3. 如果列的属性为NOT NULL,条件为IS [NOT] NULL的话,走索引扫描;
    4. 组合索引的话,如果条件中只出现一列的话跟单列索引一样;
    5. 组合索引的话,如果条件中出现两列,会优先选择走索引;
  3. IS NULL使用索引的办法:
    1. 在NULL的列上创建函数索引:nvl(column_name, -1),查询的时候条件指定函数索引: where nvl(column_name, -1) = -1;
    2. 为NULL的列添加默认值;
4. 索引的管理:
  1. 插入数据后再创建索引,对于DW来言;
  2. 在适当的表和列上加索引;
  3. 注意组合索引的顺序;
  4. 控制索引的数量:每添加一个索引,DML的效率下降3倍,官方推荐最多加7个索引;
  5. 删除不使用的索引;
  6. 为索引指定单独的表空间;
  7. 创建索引时使用并行,NOLOGGING参数;
  8. COALESCING是合并相邻叶子节点,rebuild则可以减少索引树的高度;
5. 检测索引是否被使用了:
  1. 添加对某个索引的监控:ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE;
  2. 查看监视的对象使用情况:SELECT * FROM v$object_usage;默认是没有任何的监视的;
  3. 使用此索引后再查看;
  4. 取消索引监控:ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE;
  5. 缺点:每次只能添加一个索引,而且不记录索引使用的次数;
  6. 不使用索引的原因:
    1. 被检索的列上用了函数;
    2. 数据类型不匹配;发生隐士转换是转化左边的列,而不是右边的列;
    3. 统计信息是否最新;
    4. 列是否是空值;
    5. 索引效率太低;
各种访问路径的原理及使用场景:
1. Full Table Scan:
  1. 会执行Multiblock Reads,参考初始化参数:db_file_multiblock_read_count;
  2. 会读取HWM(High-Water Mark)以下所有被格式化的块;
  3. 过程中可能会过滤某些记录;
  4. 用在要获得大量记录的时候,比索引扫描更快;
  5. 使用的场景:
    1. 没有合适的索引;
    2. 过滤条件不好,甚至是没有过滤条件;
    3. 表太小,记录数很少;
    4. 需要并行扫描,并行扫描一定不走索引,如果确定是全表的话可以考虑并行:SELECT /*+ PARALLEL(d 4) */ * FROM departments d;
    5. 加全表扫描的hint时:SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10;
    6. IS NULL的操作;
2. ROWID Scan:
  1. 根据记录的rowid查询,最快的访问方式,但不经常使用,可能会出现在执行计划的某个步骤中;
  2. 使用的方法:
3. Sample Table Sacns:基本不用,SELECT * FROM departments SAMPLE BLOCK (10) SEED (1);
4. Index Unique Scan:条件中指定了主键列或者唯一键的列就走唯一键扫描;
5. Index Range Sacn:
  1. 过滤的条件上不是主键/唯一索引,就会走索引范围扫描;
  2. 如果对有索引的列排倒序就会有索引倒序扫描;(因为索引本身是排序的,所以执行计划中不会有排序的步骤,按照索引列排序效率会高;)
  3. 走函数索引的例子,也是索引范围扫描的一种;
6. Index Full Sacn vs Index Fast Full Sacn:
  1. 出现的条件:
    1. SELECT与WHERE子句出现的所有的列必须存在索引,而且为非空列,因为索引不存放NULL值;
    2. 返回的数据总行占据索引的10%以上的比例;
  2. Index Full Sacn:
    1. 完全按照索引存储的顺序依次访问整个索引树,当访问到叶子节点时,按照双向链表方式读取相连的节点值;
    2. 使用Single Read,会产生db file sequential reads事件;
    3. 对于索引列上的排序,总是会使用Index Full Scan;
    4. 索引列上is not null的操作,会走全索引扫描;
  3. Index Fast Full Sacn:
    1. 对于索引的分支结构只是简单的获取,然后扫描所有的叶节点,导致索引结构没有访问,获得的数据没有根据索引键的顺序排序,读取效率高.但是如果SQL语句中有排序操作的话,还要额外多做一次排序;
    2. 在使用Index Fast Full Sacn时,使用Multiblock Read,会产生db file scattered reads,db_file_multiblock_read_count参数的设置很重要;
    3. 统计行数,如count(*)的操作总是会使用Index [Fast] Full Scan的;
    4. 会使用大量的内存和CPU资源;
  4. Index [Fast] Full Scan的例子;
7. Index Skip Scan:
  1. 创建了复合索引,但是条件中只有复合索引中的第二列,而且当第一列的distinct值不多时,会发生跳跃扫描;
  2. 创建一个测试表,和一个联合索引,当第一列可选值少而条件中只查找第二列时,发生跳越扫描;
  3. 如果第一列的可选值很多,条件中查找第二列的话,发生全表扫描;
8. Index Join Scan:查询的列都不为空,而且都有索引才会出现联合扫描;
9. AND-EQUAL操作:两列都有索引,分别扫描两列获得记录的rowid,然后再取rowid的交集;
10. Bitmap Index:
  1. Bitmap的单值扫描;
  2. Bitmap的范围扫描;
  3. Bitmap的迭代操作操作;
  4. Bitmap的AND操作;
排序操作:
1. Sort Operator:
  1. AGGREGATE:在group操作用会用到,统计结果;
  2. UNIQUE:评估是否重复;
  3. JOIN:做合并操作;
  4. GROUP BY,ORDER BY:在group by和order by的时候使用;
2. Hash Operator:
  1. GROUP BY:在group by操作时使用;
  2. UNIQUE:跟SORT UNIQUE一样;
3. 10g之后结果默认不排序,如果想要排序后的结果,应该总是使用ORDER BY字句;
Buffer Sort:
1. BUFFER SORT不是一种排序,而是一种临时表的创建方式;
2. BUFFER表示在内存中存放了一张临时表;
3. SORT来修饰BUFFER表示具体再内存的什么地方:在PGA的SQL工作区的排序区;
4. BUFFER SORT的例子:
INLIST ITERATOR:
1. 是由于IN操作引起的,要关注迭代的次数,一次迭代就要有一次访问,如果没有索引可能性能问题会很严重;
2. 可以使用UNION ALL操作代替;
3. INLIST ITERATOR的例子;
视图的操作:
1. Merge View:是将View的定义和外部查询合并,高效的方式;
2. No Merge View:先将View的数据取出来再做外部条件的过滤,效率低;
执行计划中的Count和Count Stopkey:oracle数据库的优化关于rownum操作;
1. 在查询中有时使用到伪列rownum,对使用伪列rownum的查询,优化器要么使用count操作,要么使用count stopkey操作来对rownum计数器进行增量(注意:这里的count操作和count stopkey操作与count函数没有任何关系).如果对rownum伪列应用一个限定条件,如:where rownum<10,则使用count stopkey操作;如果不为Rownum伪列指定限定条件,则是使用count操作;
2. 不在Rownum伪列上使用限定条件:SELECT employee_id, ROWNUM FROM employees;(employee_id是主键)为了完成这个查询,优化器执行一个全索引扫描(主键索引),后跟一个count操作生成每个行的rownum值,count操作不需要等待得到整个记录集,随着从employee表中返回记录,rownum计数器进行增量,从而确定每个记录的rownum;
3. 在rownum伪列上使用一个限定:SELECT employee_id, ROWNUM FROM employees WHERE ROWNUM < 10;为了实施限定条件,优化器用count stopkey操作代替count操作,它将rownum伪列的增量值与限定条件中指定的值进行比较,如果rownum伪列的值大于限定条件中指定的值,则查询不再返回更多的行;
4. 在where子句中不能使用rownum>10这样的操作,只能使用rownum<10这样的操作;
Min/Max and First Row操作:当使用MAX/MIN函数时发生;
连接的方式:
1. 一个连接定义了两个行源的关系,也是合并两个行源间数据的方法;
2. 主要由连接的谓词所控制,定义了对象间的关系;
3. 连接的方法:
  1. Nested Loops:
    1. 对于被连接的数据子集较小的情况,嵌套循环是个较好的选择;
    2. 返回第一条记录最快的方式;
    3. 这种情况下,内表被外表驱动,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(eg:<1w);
    4. 要把返回子集较小的表作为驱动表,而且内标的连接字段上一定要有索引;
    5. 使用USE_NL(table_name1 table_name2)可是强制CBO执行嵌套循环连接;
  2. Sort-Merge Join:
    1. 通常情况下散列连接的效果都比排序合并连接要好,然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接;
    2. 可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接;
    3. Sort Merge join使用的情况:
      1. 用在没有索引;
      2. 数据已经排序的情况;
      3. 不等价关联;
      4. HASH_JOIN_ENABLED=FALSE;
  3. Hash Join:
    1. 散列连接是CBO做大数据集连接时常用的方式,优化器使用两个表中较小的表(行源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行;
    2. 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和,但是在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O的性能;
    3. 也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接,如果使用散列连接HASH_AREA_SIZE初始化参数必须足够的大,如果是10g以后,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可;
4. 连接方式的比较:
  1. Hash join的工作方式是将一个表(通常是小一点的那个表)做hash运算,将列数据存储到hash列表中,从另一个表中抽取记录,做hash运算,到hash列表中找到相应的值,做匹配;
  2. Nested loops工作方式是从一张表中读取数据,访问另一张表(通常是索引)来做匹配,nested loops适用的场合是当一个关联表比较小的时候,效率会更高;
  3. Merge Join 是先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配,因为merge join需要做更多的排序,所以消耗的资源更多,通常来讲,能够使用merge join的地方,hash join都可以发挥更好的性能,Merge Join太消耗PGA;
5. 连接的类型:
  1. [不]等值连接和自然连接;
  2. 外连接:全连接,左外连接,右外连接;(外连接:+号放那边,哪边记录少;)
  3. 半连接:EXISTS子句;
  4. 反连接:NOT IN字句;
多行源的操作
1. FILTER;
2. CONCATENATION;
3. UNION [ALL]
4. INTERSECT;
5. MINUS;

— Full Table Scan;

SELECT * FROM departments WHERE manager_id = 100;

SELECT /*+ PARALLEL(d 4) */ * FROM departments d;

SELECT * FROM departments d WHERE department_id = 10;
SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10;

— ROWID Sacn;

SELECT * FROM departments WHERE ROWID = ‘AAAMiZAAFAAAAA4AAI’;
SELECT * FROM departments WHERE ROWID = (
SELECT rowid FROM departments WHERE manager_id = 100);

— 函数索引的例子;

CREATE INDEX idx_employees_fun_firstname ON employees (upper(first_name));
SELECT * FROM user_tab_cols WHERE table_name = ‘EMPLOYEES’;
SELECT * FROM user_ind_expressions WHERE index_name = ‘IDX_EMPLOYEES_FUN_FIRSTNAME’;

— Index Skip Scan的例子;
CREATE TABLE skip_test AS
SELECT object_id, object_name, decode(object_type, ‘VIEW’, ‘VIEW’, ‘TABLE’) AS object_flag, object_type
FROM dba_objects WHERE ROWNUM <= 3000;
CREATE INDEX idx_skip_test ON skip_test(object_flag, object_id);
EXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
SELECT * FROM skip_test WHERE object_id = 100;
— 如果联合索引第一列的候选值太多,则发生全表扫描;
DROP INDEX idx_skip_test;
CREATE INDEX idx_skip_test ON skip_test(object_type, object_id);
EXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
SELECT * FROM skip_test WHERE object_id=100;

— 位图索引的例子;

CREATE TABLE bitmap_test AS
SELECT ROWNUM rn, MOD(ROWNUM, 4) bit,
CASE MOD(ROWNUM, 2) WHEN 0 THEN ‘M’ ELSE ‘F’ END gender
FROM dual CONNECT BY ROWNUM < 1000;
CREATE BITMAP INDEX bmp_bitmap_bit on bitmap_test (bit);
CREATE BITMAP INDEX bmp_bitmap_gender on bitmap_test (gender);
EXEC dbms_stats.gather_table_stats(USER, ‘bitmap_test’, CASCADE => TRUE);
ALTER SESSION optimizer_mode = ‘FIRST_ROWS_1000’;
SELECT * FROM bitmap_test WHERE bit = 3;
SELECT * FROM bitmap_test WHERE bit > 2;
SELECT * FROM bitmap_test WHERE bit IN (2, 3);
SELECT * FROM bitmap_test WHERE bit IN (2, 3) AND gender = ‘M’;

SQL调优02–CBO优化器的原理

Posted on 2012-01-14 by royalwzy

Intorduction to the CBO Optimizer

介绍CBO优化器的书:Jonathan Lewis写的<<Cost-Based Oracle Fundamentals>>;
结构化查询语言的类型:
1. DML(Data Manipulation Language):INSERT, UPDATE, DELETE, MERGE, SELECT;
2. DDL(Data Definition Language):CREATE, DROP, ALTER, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT;
3. ESS(Environmental Sustainability Statement):DECLARE, CONNECT, OPEN, CLOSE, DESCRIBLE, WHENEVER, PREPARE, EXECUTE, FETCH;
4. TCS(Transactoin Control Statement):COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION;
5. SystemCS(System Control Statement):ALTER SYSTEM;
6. SessionCS(Session Control Statement):ALTER SESSSION, SET ROLE;
SQL语句的实现过程:相同的SQL语句可以使用相同的执行计划;
Cursor
1. PL/SQL中的cursor:记录行的rowid,用来表示结果集;
2. sql解析过程中的cursor:是SHARED POOL中的内存块;
SQL语句的处理过程:
1. Create a cursor:
  1. Cursor是private SQL area的句柄或者名称;
  2. 其中包含了语句执行的信息;
  3. Cursor的结构与它包含SQL语句是互相独立的;
2. Parse the statement:
  1. SQL语句通过用户进程发送到Oracle实例;
  2. 在PGA中检查语法和语义,然后检查权限,分配private SQL area,然后检查是否已经在Library Cache中存在,如果没有的话,会把sql放入到Shared SQL area中,产生硬解析;
  3. 如果SQL的执行计划已经存在的话可以被重用;
3. Describe query results:
  1. 提供了SELECT后面的要查找的列的信息,与动态执行SQL有关;
  2. 9i时使用DBMS_SQL包,之后使用EXECUTE IMMEDIATE来动态执行SQL;
4. Define query output:定义要查找的列的位置,大小,数据类型信息;
5. Bind variables:
  1. 开启内存地址来保存数据的值;
  2. 允许共享sql;
6. Parallelize the statement:
  1. 可以并行的语句:SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE, ALTER;
  2. 11g中自动判断是否需要并行;
7. Execute the statement:执行SQL语句,产生想要的结果;
8. Fetch rows of a query:以表格的形式返回数据;
9. Close the cursor:关闭游标,PGA中指向cursor的指针关闭,但是内存区域还是被占用,之后可以被覆盖;
10. 使用DBMS_SQL包可以看到完整的SQL执行的过程;
SQL语句解析的过程;
需要优化器的理由:它可以选择一种资源消耗最小的方式;
硬解析的操作步骤:
Transformer
1. 优化器首先会把语句分成一个一个的查询块,然后进行转换;
2. 虽然进行了转换操作以提高效率,但是在内存中保存的还是原来的SQL语句;
3. OR转换为UNION ALL操作;
4. IN转换为内连接操作(11g);
5. IN改写为exists;
6. NOT IN改写为外连接+IS NULL,11g中自动转换,10g中需要修改;
7. IN改写为外连接+IS NOT NULL;
8. 视图合并:查询的时候直接查询视图中的基表,非常适合于视图的记录数很大,查询视图的记录数小的情况;
9. 视图不合并:查询时把制图当成基表,这样效率比较低.CBO会自动合并第一层的视图,所以不要使用嵌套视图;
10. Predicate Pushing:把条件推到最查询的最低端;
11. 条件的传递性:employees的department_id列没有索引,department表department_id列是主键,转换过之后就会先走主键扫描;
Cost-Based Optimizer
1. 由Estimator和Plan Generator组成;
2. Estimator决定执行计划的成本消耗的建议;
  1. 它是基于概率论的,理论依据是数据是均匀分布的;
  2. 它的基础数据是定期收集并存放在数据字典的统计信息;
3. Plan Generator:
  1. 产生各种不同的执行计划;
  2. 使用Estimator计算各个执行计划的成本;
  3. 基于成本选择最好的优化建议;
  4. 生成最优的执行计划;
4. OPTIMIZER_MODE的两个参数:ALL_ROWS, FIRST_ROWS_n:
  1. FIRST_ROWS_n:
    1. CBO优先考虑将结果集中的前N条记录以最快的速度返回,而其它的结果集并不需要同时返回;
    2. 可以使用在BBS的分页上:SELECT /*+ first_rows(10) */ FROM tbname;
    3. 这种执行计划对于SQL整体的执行时间不是最快的,但是在返回前N条记录的处理上绝对是最快的;
    4. 使用的排序字段必须有索引,否则CBO会忽略FIRST_ROWS(n),而使用ALL_ROWS;
  2. ALL_ROWS:
    1. CBO考虑最快的速度返回所有的结果集,和FIRST_ROWS_n相反;
    2. 在OLAP系统中使用较多,总体效率高;
Estimator
1. Selectivity:选择度;
  1. Selectivity is the estimated proportion of a row set retrieved by a particular predicate or combination of predicates;选择度是由一个特定的谓词或者组合谓词检索行集的估计比例;
  2. 计算公式:Selectivity=满足条件的记录数/总记录数;
  3. 它的取值在0.0-1.0之间:
    1. High Selectivity:得到大比例的记录数;
    2. Low Selectivity:得到小比例的记录数;
  4. 如何获得Selectivity:
    1. 如果没有统计信息则采用动态采样(Dynamic Sampling);
    2. 如果没有直方图信息则采用行分布;
  5. 存放统计信息的视图:
    1. dba_tables;
    2. dba_tab_statistics(NUM_ROWS, BLOCKS, AVG_ROW_LEN);
    3. dba_tab_col_statistics(NUM_DISTINCT, DENSITY, HIGH/LOW_VALUE);
2. Cardinality:基数;
  1. 通过执行计划期望能检索出来的记录数;
  2. 计算公式:Cardinality=Selectivity*总记录数;
  3. 对于join, filters和sort的成本是重要的指标;
3. 举例:SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;
  1. employees表的job_id列的取值个数为:19;
  2. employees表中的记录数:107;
  3. Selectivity=1/19=0.0526315789473684,即DENSITY的值;
  4. Cardinality=(1/19)*107=5.63,向上取整为6;
4. Cost:
  1. Cost是执行特定的语句所花费的标准I/Os数量的最优评估;
  2. Cost的单位是一个标准块的随机读取:1 cost unit = 1 SRds(Standardized Random Reads);
  3. 执行计划中Cost(%CPU):一次IO读取一个IO块需要的时间;
  4. Cost的值由三部分组成;
控制优化器的初始化参数:
1. CURSOR_SHARING:SIMILAR|EXACT(default)|FORCE,控制可以共享Cursor的SQL语句类型;
2. DB_FILE_MULTIBLOCK_READ_COUNT:它是一个可以在表扫描时最小化IO的参数,指定了在顺序扫描时一次IO操作可以读取的最大的块数;(在OLTP系统中一般指定4-16,在DW系统中可以尽量设置的大一点);
3. PGA_AGGREGATE_TARGET:PGA自动管理时指定server processes可以使用的PGA内存的总和;
4. STAR_TRANSFORMATION_ENABLED:参数设置为TRUE时使用CBO可以使用位图索引的特性,不过貌似现在这个参数不重要;
5. RESULT_CACHE_MODE:MANUAL,FORCE,11g
6. RESULT_CACHE_MAX_SIZE:11g;
7. RESULT_CACHE_MAX_RESULT:11g;
8. RESULT_CACHE_REMOTE_EXPIRATION:11g;
9. OPTIMIZER_INDEX_CACHING:在Buffer Cache中缓存索引的比例,默认为0;
10. OPTIMIZER_INDEX_COST_ADJ:索引扫描/全表扫描的成本,默认为100%,即索引扫描成本等于全表扫描成本;优先会选择全表扫描;比较悲观的配置;
11. OPTIMIZER_FEATURES_ENABLE:希望启用哪个版本的CBO;
12. OPTIMIZER_MODE:ALL_ROWS|FIRST_ROWS|FIRST_ROWS_n
  1. 默认是all_rows:资源消耗比较小;
  2. first_rows_n:n的取值为1|10|100|1000,速度优先,但是消耗很大的资源;
13. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;
14. OPTIMIZER_USE_SQL_PLAN_BASELINES;
15. OPTIMIZER_DYNAMIC_SAMPLING:动态采样的特性,10g后默认为2;
16. OPTIMIZER_USE_INVISIBLE_INDEXES;
17. OPTIMIZER_USE_PENDING_STATISTICS;
OPTIMIZER_INDEX_COST_ADJ参数设置的例子:
1. 首先创建表,索引,并收集统计信息;
2. 打开执行计划,执行查询语句,默认的值为100%,即索引扫描成本等于全表扫描成本,则执行计划走全表扫描;
3. 修改参数值为50,即索引扫描成本是全表扫描成本的1/2,查看执行计划;
Selectivity值的例子:
1. 因为CBO是基于数据均匀的概率分布的,所以它估计的Selectivity是一个理论值;
2. 创建一个1200条记录的表,里面的值分布是1-12,代表1-12月出生的人;
3. 如果要查找某个月份出生的人,那么在不明白任何情况下,每个月份出生的人的概率都是1/12,即有100个人,CBO也是这么思考问题的;
4. 实际值往往跟理论值不相符,但是数据量越大,越接近;
10053事件测试:
1. 开启10053事件,并执行一条sql语句;
2. 查看当前的session_id和process_id来确定生成的内容是放在哪个udump文件中;
3. 查看udump文件,里面包含了一些缩写的含义和当前生效的优化参数的值;
4. 关闭10053事件;

— Estimator例子的脚本;

SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;

SELECT COUNT(DISTINCT job_id) FROM hr.employees;

SELECT owner, table_name, column_name, num_distinct, density
FROM dba_tab_col_statistics
WHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’ AND column_name = ‘JOB_ID’;

SELECT owner, table_name, num_rows, blocks, avg_row_len
FROM dba_tab_statistics
WHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’;

— OPTIMIZER_INDEX_COST_ADJ参数的例子;

CREATE TABLE t1 AS
SELECT MOD(ROWNUM, 200) n1, MOD(ROWNUM, 200) n2
FROM dba_objects WHERE ROWNUM <= 3000;
CREATE INDEX t_i1 ON t1(n1);
EXEC dbms_stats.gather_table_stats(USER, ‘t1’, CASCADE=>TRUE);
SET autotrace traceonly exp;
SELECT * FROM t1 WHERE n1 = 50;
ALTER SESSION SET optimizer_index_cost_adj = 50;
SELECT * FROM t1 WHERE n1 = 50;

— Selectivity的例子;

CREATE TABLE t2(ID, month_id) AS
SELECT ROWNUM, trunc(dbms_random.value(1, 13))
FROM dba_objects WHERE ROWNUM <= 1200;
EXEC dbms_stats.gather_table_stats(USER, ‘t2’, CASCADE => TRUE);
SET autotrace traceonly exp;
SELECT * FROM t2 WHERE month_id = 5;
SELECT COUNT(*) FROM t2 WHERE month_id = 5;

— 10053时间测试例子;

ALTER SYSTEM/SESSION SET EVENTS ‘10053 trace name context forever, level 8’;
SELECT * FROM employees WHERE employee_id = 100;
ALTER SYSTEM/SESSION SET EVENTS ‘10053 trace name context off’;
SELECT s.sid, p.spid FROM v$session s
INNER JOIN v$process p ON s.paddr = p.addr AND
s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);

royalwzy的学习笔记

Tag Archives: Performance

由于bitmap造成的insert相互阻塞

Bitmap深入研究(一)

Oracle SPA介绍

性能调优工具05–SQL Profiles

性能调优工具04–SQLT Diagnostic Tool

性能调优工具03–OUTLINE技术暂时锁定SQL的执行计划

调优实践04–利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句

调优实践03–使用SQL_TRACE和10046事件跟踪SQL的执行

性能调优工具02–Sql Access Advisor工具的使用

性能调优工具01–Sql Tuning Advisor工具的使用

调优实践02–ALTER SYSTEM/SESSION SET EVENTS转储

调优实践01–Oracle常见的等待事件及解决方案

SQL调优01–SQL优化介绍

SQL调优04–阅读执行计划

SQL调优03–执行计划的访问路径

SQL调优02–CBO优化器的原理