certifies using for pic test

1

2

重做日志时间戳说明

首先创建一个包括序列号与时间戳的表,通过对该表插入当前时间戳并记录插入操作的开始时间,进行中时间,与结束时间,以便与重做日志中的时间戳对比。

表的定义如下:

create table tim (tn int,itime timestamp);

使用以下匿名块插入数据:

declare

stime timestamp;

dtime timestamp;

etime timestamp;

begin

for i in 1 .. 10 loop

stime := systimestamp;

insert into tim values (i, systimestamp);

etime := systimestamp;

select itime into dtime from tim where tn = i;

dbms_output.put_line(‘start time: ‘ || to_char(stime,’HH24:MI:SS:FF’) || ‘ doing time: ‘ ||

to_char(dtime,’HH24:MI:SS:FF’) || ‘ end time:’ || to_char(etime,’HH24:MI:SS:FF’));

dbms_lock.sleep(2.9);

commit;

end loop;

end;

其中stime,dtime,etime,分别记录了每次插入的开始时间,进行中时间,以及结束的时间,操作结束后输出以上时间,之后休眠2.9秒,COMMIT之后继续循环。该过程输出如下:

start time: 15:10:06:986235000 doing time: 15:10:06:987493000 end time:15:10:06:987962000

start time: 15:10:09:894372000 doing time: 15:10:09:894505000 end time:15:10:09:894738000

start time: 15:10:12:796921000 doing time: 15:10:12:797057000 end time:15:10:12:797293000

start time: 15:10:15:698497000 doing time: 15:10:15:698642000 end time:15:10:15:698856000

start time: 15:10:18:601077000 doing time: 15:10:18:601222000 end time:15:10:18:601451000

start time: 15:10:21:502664000 doing time: 15:10:21:502803000 end time:15:10:21:503044000

start time: 15:10:24:405294000 doing time: 15:10:24:405435000 end time:15:10:24:405673000

start time: 15:10:27:307828000 doing time: 15:10:27:307979000 end time:15:10:27:308193000

start time: 15:10:30:209477000 doing time: 15:10:30:209619000 end time:15:10:30:209865000

start time: 15:10:33:112033000 doing time: 15:10:33:112173000 end time:15:10:33:112397000

可以看到每次循环内开始时间,进行中时间,结束时间的差值很小,证明插入是在瞬间完成的。

之后我们查看tim表中的内容:

select tn,to_char(itime,‘HH24:MI:SS:FF’) ITIME ,dump(itime,16) HEX_ITIME from tim;

TN

ITIME

HEX_ITIME

1

15:10:06:987493

Typ=180 Len=11: 78,6d,6,e,10,b,7,3a,db,f2,88

2

15:10:09:894505

Typ=180 Len=11: 78,6d,6,e,10,b,a,35,51,10,28

3

15:10:12:797057

Typ=180 Len=11: 78,6d,6,e,10,b,d,2f,82,1f,e8

4

15:10:15:698642

Typ=180 Len=11: 78,6d,6,e,10,b,10,29,a4,6e,50

5

15:10:18:601222

Typ=180 Len=11: 78,6d,6,e,10,b,13,23,d5,eb,70

6

15:10:21:502803

Typ=180 Len=11: 78,6d,6,e,10,b,16,1d,f8,2a,38

7

15:10:24:405435

Typ=180 Len=11: 78,6d,6,e,10,b,19,18,2a,72,78

8

15:10:27:307979

Typ=180 Len=11: 78,6d,6,e,10,b,1c,12,5b,62,f8

9

15:10:30:209619

Typ=180 Len=11: 78,6d,6,e,10,b,1f,c,7e,88,38

10

15:10:33:112173

Typ=180 Len=11: 78,6d,6,e,10,b,22,6,af,9f,c8

ITIME即插入操作中的时间,HEX_ITIME为ITIME在数据块中的16进制存放格式,用以与重做日志中的16进制数据对比。

我们dump当前的重做日志,该日志包括了方才所做的DML操作:

alter system dump logfile ‘/u01/oradata/orcl/redo01.log’;

查看dump所产生的跟踪文件,可以发现以下记录:

REDO RECORD – Thread:1 RBA: 0x000026.0000000c.0010 LEN: 0x02a0 VLD: 0x0d

SCN: 0x0000.000b727b SUBSCN:  1 06/14/2009 15:10:09

CHANGE #1 TYP:1 CLS: 1 AFN:1 DBA:0x0040ebf2 OBJ:51447 SCN:0x0000.000b7279 SEQ:  1 OP:13.5

KTSFRBFMT (block format) redo: Segobjd: 0x0000c8f7 type: 1 itls: 2

CHANGE #2 TYP:0 CLS: 1 AFN:1 DBA:0x0040ebf2 OBJ:51447 SCN:0x0000.000b727b SEQ:  1 OP:13.6

KTSFRBLNK (block link modify) redo:  Opcode: LSET (lock set)

Next dba: 0x0040ebf3 itli: 0

CHANGE #3 TYP:0 CLS: 1 AFN:1 DBA:0x0040ebf2 OBJ:51447 SCN:0x0000.000b727b SEQ:  2 OP:13.6

KTSFRBLNK (block link modify) redo:  Opcode: LWRT (lock write)

Next dba: 0x00000000 itli: 0

CHANGE #4 TYP:0 CLS: 4 AFN:1 DBA:0x0040ebf1 OBJ:51447 SCN:0x0000.000b726a SEQ:  1 OP:13.7

KTSFRGRP (fgb/shdr modify freelist) redo:

Opcode: HWMMV (move hwm)

NBK: 1

Opcode: LUPD_LLIST (link a list)

Slot no: 0, Count: 1

Flag: = 1 xid or slot0 ccnt:  0x0000.000.00000001 Head:  0x0040ebf2 Tail:  0x0040ebf2

CHANGE #5 TYP:0 CLS: 1 AFN:1 DBA:0x0040ebf2 OBJ:51447 SCN:0x0000.000b727b SEQ:  3 OP:11.2

KTB Redo

op: 0x01  ver: 0x01

op: F  xid:  0x0009.02b.0000015d    uba: 0x00808df4.0163.24

KDO Op code: IRP row dependencies Disabled

xtype: XA flags: 0x00000000  bdba: 0x0040ebf2  hdba: 0x0040ebf1

itli: 1  ispac: 0  maxfr: 4863

tabn: 0 slot: 0(0x0) size/delt: 18

fb: –H-FL– lb: 0x1  cc: 2

null: —

col  0: [ 2]  c1 02

col  1: [11]  78 6d 06 0e 10 0b 07 3a db f2 88

在以上重做记录(REDO RECORD)的CHANGE #5中包含了插入TIMESTAMP “col  1: [11]  78 6d 06 0e 10 0b 07 3a db f2 88”即表中第一条数据的ITIME列为15:10:06:987493,而该条重做记录的时间戳为:15:10:09,要晚于插入的进行中时间,与插入结束时间。

REDO RECORD – Thread:1 RBA: 0x000026.0000000e.0010 LEN: 0x01e4 VLD: 0x0d

SCN: 0x0000.000b727d SUBSCN:  1 06/14/2009 15:10:12

CHANGE #1 TYP:2 CLS: 1 AFN:1 DBA:0x0040ebf2 OBJ:51447 SCN:0x0000.000b727b SEQ:  5 OP:11.2

KTB Redo

op: 0x01  ver: 0x01

op: F  xid:  0x0003.026.00000141    uba: 0x00806194.0257.19

KDO Op code: IRP row dependencies Disabled

xtype: XA flags: 0x00000000  bdba: 0x0040ebf2  hdba: 0x0040ebf1

itli: 2  ispac: 0  maxfr: 4863

tabn: 0 slot: 1(0x1) size/delt: 18

fb: –H-FL– lb: 0x2  cc: 2

null: —

col  0: [ 2]  c1 03

col  1: [11]  78 6d 06 0e 10 0b 0a 35 51 10 28

以上为后一条重做记录,该记录的CHANGE #1中包含了插入TIMESTAMP” col  1: [11]  78 6d 06 0e 10 0b 0a 35 51 10 28”即ITIME列为15:10:09:894505,而该条重做记录的时间戳为:15:10:12

以上实验可以证明,重做日志中记录的时间戳并不是SQL语句提交的时间,也晚于SQL语句完成的时间,实际为重做记录由缓存形式写至在线日志文件的时间。即SQL语句的完成时间一般都要早于重做日志中相应记录的时间戳。由于LGWR进程至少3秒会写出一次的特性,重做日志中的时间戳最早为语句的结束时间,最晚为语句结束后的三秒。

同样的查询每次都产生大量物理读的调优示例

12月中旬用户反映综合传输网管库上的一个查询影响迟缓,具体现象表现为当多个用户在应用界面上同时点下查询后,结果返回耗时长,影响正常业务的运作。经过初步分析该操作主要的等待事件在db file sequential read上,为了进一步明确问题,我们在系统的高峰时段使用性能报告工具抓取了统计信息,以下为top3等待事件:

Event                               Waits   Timeouts   Time (s)   (ms)

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

enqueue                           542        402      1,406   2595

db file sequential read           446,099          0        391      1

db file scattered read            156,634          0        209      1

可以看到db file sequential read事件仅次于数据库队列事件为主要的数据库性能瓶颈,以下列出缓存占用较高的典型SQL:

Selecta.objectid,a.emsalarm_time,a.emsend_time,c.label_cn,c.alias,a.alarm_name,a.alm_devinfo from traph c,alarm_to_traph b,current_alarm a where a.cuid=b.related_alarm_cuid and b.related_traph_cuid=c.cuid and (c.ext_ids=’,8,’ or c.ext_ids=’,9,’ or c.ext_ids=’,12,’ or c.ext_ids=’,19,’ or c.ext_ids=’,25,’) and a.emsend_time is null and a.emsalarm_time > to_date(‘2008-11-19′,’yyyy-MM-dd’)

经过进一步追踪我们发现以上SQL正是应用界面点击查询所做的操作,这就明确了此次优化的主要目的,即分析并尽可能降低该语句所产生的物理读和逻辑读,保证应用的正常运行。

具体分析
该句查询语句涉及到三个表的连接,因为应用设计使用的是基于RULE的优化模式,故执行计划倾向于使用索引来代替全表扫描,在表与表的连接方式上倾向使用嵌套循环即(NESTED LOOP),具体执行计划如下图:

经过查询缓存池中各个表的具体缓存状况发现,表ALARM_TO_TRAPH上的索引ALARM_TO_TRAPH_INDEX1与索引TRAPH_CUID均已被缓存,而在该执行计划中需要做全表扫描操作的CURRENT_ALARM则只有部分块被缓存,充分说明了引起物理读需求的正是对CURRENT_ALARM的全表扫描操作,为了进一步证实这一点,我们做了一次10046事件的trace,该事件可以记录SQL语句执行过程中详细的等待事件以及相关信息,在trace中发现大量db file sequential read等待时间,摘录如下:

以上记录中p1,p2对应了数据文件顺序读具体发生的文件号和块号,经过查询确定以上相关文件号和数据块号具对应于表CURRENT_ALARM,至此本次性能问题的主要原因已十分明确,即CURRENT_ALARM表未被完全缓存,引起问题的可能有两种:1.表中存在大量的chained rows即链式行;2.数据库高速缓存在系统高峰时段存在瓶颈。

对于前一种可能性,我们首先分析了表的结构,CURRENT_ALARM表包括96个列,其中包括大量varchar2(255)类型的长列,理论单行长度可能达到14K字节,的确可能引起严重的链式行,为了进一步证实,我们对该表做了一次链式行扫描,操作如下:

ANALYZE TABLE CURRENT_ALARM  LIST CHAINED ROWS INTO chained_rows;

该分析操作会将表上的链式行记录到临时表chained_rows中,查询chained_rows发现没有记录,说明表CURRENT_ALARM上没有链式行的问题。其后我们通过函数计算CURRENT_ALARM表上的行长度,发现最长的一行占用1367个字节,就目前来说仍不至于发生严重的链式行问题。

经过以上分析问题已经定位到了高峰时段数据库高速缓存的使用状况上了,通过在早晨,上班高峰时段以及下班时段的进一步观察,我们发现在早晨上班之前,buffer_cache中大约有600个free块可以立即使用,在该时段做上述查询不存在物理读的操作,而在上班高峰时段free块的数量下降到个位数乃至为零,即需要加载数据块时没有可以立即使用的空闲块,同时因为其他查询更为活跃,CURRNET_ALARM表上的数据块在读入后迅速被刷出,没有被缓存住以便于下次的查询,导致了性能问题的最终产生。

总结

由于数据库高速缓存在高峰时段没有空闲块导致需要全表扫描的表未被完全缓存,进而在多用户同时查询的情况下产生大量的物理读等待,影响了正常业务的运行。针对该问题提出以下建议:

  • 增大SGA总量,增大数据库高速缓存即DB_CACHE_SIZE的空间,并扩展高速缓存的keep池,将表CURRENT_ALARM保存在keep池中,保证不被刷出。
  • 定期分析CURRENT_ALARM表的行扩展情况,从而避免可能的链式行的产生。
  • 在可能的情况下,为上述查询语句加上执行计划暗示即ALL_ROWS的hit,使其在连接方式上倾向于使用哈希连接,可以降低三分之一的逻辑读。
  • 增大数据库使用内存总量的同时可能引发CPU的使用量有一定上升,需要密切关注主机的负载情况。

[gview file=”http://askmaclean.com/wp-content/uploads/resource/BUFFPOOL.pdf”]

绑定变量介绍

Oracle在执行SQL语句时,普遍存在以下几个步骤:

  1. 当SQL语句首次执行,Oracle将确认该句语句的语法是否正确(语法解析Syntax parse)并进一步确认语句相关表和列的存在性等因素(语义解析semantic parse)以及优化器决定执行计划等步骤。整个过程称之为硬解析,硬解析消耗大量的CPU时间和系统资源。硬解析过多会有效降低系统性能。
  1. 若之前已进行过硬解析,且解析后的分析树和执行计划仍存在于共享池中,则同样的SQL仅需要软解析。软解析将输入的SQL语句转换为哈希代码,同共享池内哈希链表上的已有记录进行对比,找出对应的游标信息,使用已有的执行计划执行。
  1. 绑定变量,将实际的变量值代入SQL语句中。
  1. 执行SQL语句,查询语句将返回结果集。

不使用绑定变量的SQL语句,Oracle无法将它们视为相同的,如以下两句语句:

select * from emp where empno=1234

select * from emp where empno=5678

因为自由变量的不同,Oracle认为以上是2句不同的语句,则当第一条被硬解析后,第二条SQL执行时仍无法避免硬解析。实际在以上不使用绑定变量的情况中,只要自由变量有所改变则需要一次硬解析。这是强烈建议使用绑定变量的主要原因,使用绑定变量的语句变量的实际值仅在SQL执行的最后阶段被代入。如以下语句:

select * from emp where empno=:x

该语句使用绑定值:x替代自由变量,在应用中语句可能以预编译或普通编译的方式存在,仅在执行阶段代入变量值,多次执行仅需要一次硬解析,较不使用绑定变量情况性能大大提升。

同时过多的硬解析还会引发共享池碎片过多的问题。因为每当需要硬解析一个SQL或者PLSQL语句时,都需要从shared pool中分配一块连续的空闲空间来存放解析结果。Oracle首先扫描shared pool查找空闲内存,如果没有发现大小正好合适的空闲chunk,就查找更大的chunk,如果找到比请求的大小更大的空闲chunk,则将它分裂,多余部分继续放到空闲列表中。因为过多的硬解析加剧了内存段分配的需求,这样就产生了碎片问题。系统经过长时间运行后,就会产生大量小的内存碎片。当请求分配一个较大的内存块时,尽管shared pool总空闲空间还很大,但是没有一个单独的连续空闲块能满足需要。这时,就可能产生 ORA-4031错误。

通常我们可以通过以下SQL语句将系统中非绑定变量的语句找出:

SELECT substr(sql_text,1,40) “SQL”,

count(*) ,

sum(executions) “TotExecs”

FROM v$sqlarea

WHERE executions < 5 –-语句执行次数

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30 –-所有未共享的语句的总的执行次数

ORDER BY 2;

以上语句在实际使用中substr函数截取到的字符串长度需要视乎实际情况予以变化。

对于非绑定变量且短期内无法修改的应用,Oracle存在参数cursor_sharing可以改善其表现。cursor_sharing默认为exact,对使用自由变量的语句不做额外处理;当设为force时,非绑定变量的SQL语句被进一步处理以达到共享SQL的目的,但以上处理步骤同样要消耗一定的CPU时间;当设为similar时,若数据库存在语句相关统计信息则其表现如exact,若无统计信息则表现为force。cursor_sharing参数是Oracle针对无法修改的非绑定变量应用所提出的折中方案,但cursor_sharing为force值时存在一定SQL引发bug或语句无效的情况,且额外的处理操作同样需要消耗一定量的CPU时间和系统资源。故针对系统性能的最优方案往往是直接修改应用代码,使用绑定变量特性。

UNDO表空间监控说明

在Oracle 10g版本中可以使用V$UNDOSTAT视图用于监控实例中当前事务使用UNDO表空间的情况。视图中的每行列出了每隔十分钟从实例中收集到的统计信息。每行都表示了在过去7*24小时里每隔十分钟UNDO表空间的使用情况,事务量和查询长度等信息的统计快照。

UNDO表空间的使用情况会因事务量变化而变化,一般我们在计算时同时参考UNDO表空间的平均使用情况和峰值使用情况。

以下SQL语句用于计算过去7*24小时中UNDO表空间的平均使用量:

select ur undo_retention,

dbs db_block_size,

((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as “M_bytes”

from (select value as ur from v$parameter where name = ‘undo_retention’),

(select (sum(undoblks) / sum(((end_time – begin_time) * 86400))) ups

from v$undostat),

(select value as dbs from v$parameter where name = ‘db_block_size’)

以下SQL语句则按峰值情况计算UNDO表空间所需空间:

select ur undo_retention,

dbs db_block_size,

((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as “M_bytes”

from (select value as ur from v$parameter where name = ‘undo_retention’),

(select (undoblks / ((end_time – begin_time) * 86400)) ups

from v$undostat

where undoblks in (select max(undoblks) from v$undostat)),

(select value as dbs from v$parameter where name = ‘db_block_size’)

需要注意因RAC情况下一般存在2个UNDO表空间,视乎实际情况分别在2个实例中执行以上查询。

一般来说为了尽可能维护日常业务的正常运行,我们建议按照峰值情况估算和分配UNDO表空间的大小,虽然这样存在存储空间上的浪费,但是可以避免UNDO表空间不足所带来的问题。

同时我们也可以使用DBA_UNDO_EXTENTS视图实时监控UNDO表空间的使用情况:

select sum(bytes / 1024 / 1024), status, tablespace_name

from dba_undo_extents

group by status, tablespace_name;

该查询将返回以STATUS分组的各状态回滚信息所使用的空间量,一般存在三种STATUS状态:EXPIRED,UNEXPIRED,ACTIVE。ACTIVE表示目前仍活跃的事务相关回滚信息,UNEXPIRED表示虽然事务已经结束但回滚信息保留的时间仍未超过实例参数UNDO_RETENTION所设定的值,EXPIRED表示回滚信息保留时间已超过UNDO_RETENTION所设定的值。

在UNDO表空间未启用guarantee选项的情况下(当前使用情况),新事务的回滚空间分配遵循以下依据:

a)         寻找不存在ACTIVE区间的回滚段,若没有则创建一个新的回滚段,若空间不允许生成新段,则返回错误。

b)        如果有一个回滚段被选中,但是其中空闲的空间并不足以存储该事务的回滚信息,那么它将尝试创建区间,如果表空间上没有空间,那么将会进入下一步。

c)         如果创建新区间失败,它将会搜索其他回滚段中的EXPIRED区间并重用。

d)        如果其他回滚段中没有EXPIRED区间可使用,那么它会继续搜索其他回滚段中UNEXPIRED区间并重用,注意事务不会重用本回滚段中的UNEXPIRED区间,故UNEXPIRED的回滚空间仅部分可以为Oracle重用;若仍得不到所需则返回错误。

当我们观察到ACTIVE回滚信息所占用空间很大时,说明系统目前运行的事务繁忙。因目前未启用UNDO表空间的guarantee选项,故EXPIRED的全部回滚空间与UNEXPIRED的部分回滚空间可以为Oracle复用,在实时监控时主要观察ACTIVE状态回滚信息使用的空间即可。

在系统相关业务不变的情况下,我们通过计算UNDO表空间的峰值使用情况即可最大程度完善UNDO表空间的配置;而当系统处于业务调整阶段,如新的业务加入或业务时段调整情况下,则需要进一步实时监控UNDO表空间使用情况,以满足动态调整需求。

undo自动调优介绍

Oracle 10gr2的后续版本中添加了撤销(UNDO)信息最短保留时间段自动调优的特性,不再仅仅依据参数UNDO_RETENTION的设定,其调优原则如下:

l  当撤销表空间(UNDO TABLESPACE)大小固定,Oracle将根据表空间的大小和实际的系统负载动态调整撤销信息保存时间,该最短保存时间的具体长短基于撤销表空间大小的一定比例值公式换算后获得;它总是比设定的UNDO_RETENTION大,当撤销表空间大量空闲情况下可能远远大于UNDO_RETENTION。

l  当撤销表空间设定为自动扩展空间情况下,Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间(MAXQUERYLEN)加上300秒或参数UNDO_RETENTION间的较大者,即MAX((MAXQUERYLEN+300),UNDO_RENTION);同样的,该最短保存时间可能远远大于设定的UNDO_RETENTION。

在自动调整情况下,实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。

在无法就撤销表空间做相应修改的情况,我们可以通过修改隐式参数” _UNDO_AUTOTUNE”为FALSE关闭该自动调优特性。以上设定生效后,V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新,且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

Oracle Supplemental 补全日志介绍

Oracle补全日志(Supplemental logging)特性因其作用的不同可分为以下几种:最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique),支持外键(foreign key)。包括LONG,LOB,LONG RAW及集合等字段类型均无法利用补全日志。

最小(Minimal)补全日志开启后可以使得logmnr工具支持链式行,簇表和索引组织表。可以通过以下SQL检查最小补全日志是否已经开启:

SELECT supplemental_log_data_min FROM v$database;

若结果返回YES或IMPLICIT则说明已开启最小补全日志,当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。

一般情况下我们在使用逻辑备库时启用主键和惟一键的补全日志,而有时表上可能没有主键,惟一键或唯一索引;我们通过以下实验总结这种情况下Oracle的表现。

首先建立相关的测试表:

alter database add supplemental log data (primary key,unique index) columns ;

create table test (t1 int , t2 int ,t3 int ,t4 int );

alter table test add constraint pk_t1 primary key (t1); –添加主键

随后使用循环插入一定量的数据

update test set t2=10;       commit;   — 更新数据

使用LOGMNR工具分析之前的操作,可以看到REDO中记录的SQL形式如下:

update “SYS”.”TEST” set “T2” = ’10’ where “T1” = ’64’ and “T2” = ’65’ and ROWID = ‘AAAMiSAABAAAOhiAA/’;

其中where字句后分别记录了主键值,被修改字段的值和原行的ROWID。

现在我们将原表上的主键去掉来观察。

alter table test drop constraint pk_t1 ;

update test set t2=11;       commit;   — 更新数据

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update “SYS”.”TEST” set “T2” = ’11’ where “T1” = ‘1’ and “T2” = ’10’ and “T3” = ‘3’ and “T4” = ‘4’ and ROWID = ‘AAAMiSAABAAAOhiAAA’;

当没有主键的情况下,where子句后记录了所有列值和ROWID。

以下实验在存在唯一索引情况下的表现

create unique index pk_t1 on test(t1);

update test set t2=15; commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update “SYS”.”TEST” set “T2” = ’15’ where “T1” = ‘9’ and “T2” = ’11’ and “T3” = ’11’ and “T4” = ’12’ and ROWID = ‘AAAMiSAABAAAOhiAAI’;

以上是t1列有唯一索引但不限定not null的情况,下面我们加上not null限制

alter table test modify t1 not null;

update test set t2=21; commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update “SYS”.”TEST” set “T2” = ’21’ where “T1” = ‘2’ and “T2” = ’15’ and ROWID = ‘AAAMiSAABAAAOhiAAB’;

如以上SQL所示,在存在唯一索引的情况下where子句后仍记录了所有列和ROWID;在存在唯一索引和非空约束的情况下表现与存在主键的情况一致。

当某个表上的列数量较多时且没有主键或唯一索引和非空约束的情况下,开启补全日志可能导致重做日志总量大幅提高。

首先建立一个存在250列的表:

Drop table test;

create table test (

t1 varchar2(5),

t2 varchar2(5),

t3 varchar2(5),

t4 varchar2(5),  …t250 varchar2(5))

insert into test values (‘TEST’,’TEST’ ……);   commit; –将255个列填入数据

alter database drop supplemental log data (primary key,unique index) columns;  –关闭补全日志

set autotrace on;

update test set t2=’BZZZZ’ where t1=’TEST’; commit;

可以从自动跟踪信息中看到,本条更新产生了516的重做量。

alter database add supplemental log data (primary key,unique index) columns;  –重新开启补全日志

update test set t2=’FSDSD’ where t1=’TEST’;

跟踪信息显示产生了3044的重做量。

补全日志因作用域的不同又可分为数据库级的和表级的。表级补全日志又可以分为有条件的和无条件的。有条件限制的表级补全日志仅在特定列被更新时才会起作用,有条件限制的表级补全日志较少使用,这里我们不做讨论。

下面我们来观察无条件限制表级补全日志的具体表现:

alter database drop supplemental log data (primary key,unique index) columns;

alter table test add supplemental log data (primary key,unique index) columns;

update test set t2=’ZZZZZ’; commit;

使用LOGMNR工具查看redo中的SQL:
update “SYS”.”TEST” set “T2” = ‘ZZZZZ’ where “T1” = ‘TEST’ and “T2” = ‘AAAAA’ and “T3” = ‘TEST’………

可以发现where子句之后包含了所有列值。

delete test; commit;

使用LOGMNR工具查看redo中的SQL:

delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘ZZZZZ’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” ……

delete操作同样在where子句之后包含了所有列值。

又我们可以针对表上字段建立特定的补全日志组,以减少where子句后列值的出现。

alter table test drop supplemental log data (primary key,unique index) columns;  –关闭表上原先的补全日志

alter table test add supplemental log group test_lgp (t1 ,t2,t3,t4,t5,t6,t12,t250) always; –创建补全日志组

update test set t2=’XXXXX’ ; commit;

使用LOGMNR工具查看redo中的SQL:

update “SYS”.”TEST” set “T2” = ‘XXXXX’ where “T1” = ‘TEST’ and “T2” = ‘TEST’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” = ‘TEST’ and “T6” = ‘TEST’ and “T12” = ‘TEST’ and “T250” = ‘TEST’ and ROWID = ‘AAAMieAABAAAOhnAAA’;

如上所示重做日志中正确地显示了UPDATE操作中用户指定的字段值。

delete test;

使用LOGMNR工具查看redo中的SQL:

delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘XXXXX’ and “T3” = ‘TEST’ ……

delete操作在重做日志中仍然保留了所有列值。

针对字段较多的表,我们在能够以多个列保证数据唯一性且非空的情况下(即应用概念上的主键)来指定表上的补全日志组,以减少update操作时所产生的重做日志,而对于delete操作则无法有效改善。

Script:Generating CREATE USER DDL Statements

Title: Generating CREATE USER DDL Statements
Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada.
These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated
are as follows:
1. CREATE USER and ALTER USER...QUOTA x ON [tabspace] (GENUSER.SQL)
2. CREATE role (GENROLE.SQL)
3. GRANT [role|priv] TO user (GRANTPRIV.SQL)
All three scripts ask for execution parameters. If you leave such a parameter blank, the script will generate for all. The
exception is the prompt for the output filename.
Source/Text/Comments
REM
REM    PROGRAM-ID : GENUSER.SQL
REM    WRITTEN BY : Ted Martin
REM  DATE WRITTEN : 26-AUG-1998
REM
clear screen
PROMPT GENUSER.SQL           Generates CREATE USER commands
PROMPT
PROMPT Includes ALTER USER...QUOTA x ON tabspace commands
PROMPT
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '
col username noprint
col lne newline
set heading off pagesize 0 verify off feedback off
spool &&outfile..gen
prompt genuser.log
prompt set term on echo off
prompt prompt Creating User Accounts...
prompt set term off echo on
SELECT username, 'CREATE USER '||username||' '||
DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
'IDENTIFIED BY '''||password||''' ') lne,
'DEFAULT TABLESPACE '||default_tablespace lne,
'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
prompt set term on echo off
prompt prompt Granting Tablespace Quotas...
prompt set term off echo on
SELECT username, 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON TABLESPACE '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
spool off
PROMPT
PROMPT File &&outfile..GEN generated. Please review before using
PROMPT
EXIT
=============================================================
REM
REM      PROGRAM-ID : GENROLE.SQL
REM      WRITTEN BY : Ted Martin
REM    DATE WRITTEN : 6-APR-1996
REM
set term on  echo off   linesize 132  pagesize 0  heading off
set verify off
clear screen
prompt GENROLE.SQL V1.0            Generate CREATE ROLE statements
prompt
prompt
accept rname   prompt  '      Grant Role : '
accept outfile prompt  ' Output filename : '
set feedback off pagesize 0 heading off
col lne newline
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Role = &&rname
prompt spool &&outfile..log
prompt set term on  echo off  feedback on
select 'CREATE ROLE '||role||';' lne
from dba_roles
where role like UPPER('%&&rname%')
and role not in ('CONNECT', 'RESOURCE', 'DBA',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY ROLE;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
=====================================================
REM
REM      PROGRAM-ID : GRANTPRIVS.SQL
REM      WRITTEN BY : Ted Martin
REM    DATE WRITTEN : 26-AUG-1998
REM
clear screen
set term on  echo off   linesize 132  pagesize 0  heading off
set verify off
prompt GRANTPRIVS.SQL             Generate Existing GRANT role/priv statements
prompt
prompt Handles both Roles and System Privs. Excludes SYS and SYSTEM accounts
prompt
accept rname   prompt  '      Grant Priv : '
accept towner   prompt '         To User : '
accept outfile prompt  ' Output filename : '
set feedback off  verify off
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Priv = &&rname
prompt prompt . . User = &&towner
prompt spool &&outfile..log
prompt set term on  echo on  feedback on
col grantee noprint
col granted_priv noprint
select grantee, granted_role granted_priv,
'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where (granted_role like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
UNION
select grantee, privilege granted_priv,
'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where (privilege like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
order by 1, 2;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit

Script:Diagnostic ORA-01000 maximum open cursors exceeded

以下脚本可以用于诊断ORA-01000打开游标过多错误:

set linesize 140 pagesize 1400
select
to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%cursor ca%'
/
select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' 
order by 1 
/
select sid, count(*) from v$open_cursor group by sid
order by 2 
/
Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 
exec dbms_lock.sleep(300);
Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 
@?/rdbms/admin/awrrpt
upload the awr report
or 
select dbms_workload_repository.awr_report_text(l_dbid     => dbid,
l_inst_num => instance_number,
l_bid      => mid - 1,
l_eid      => mid)
from (select vd.dbid, vi.instance_number, mid
from v$database vd,
v$instance vi,
(select max(snap_id) mid from dba_hist_snapshot dhs))
/

Oracle Event 10357 and 10351

[oracle@rh2 ~]$ oerr ora 10357
10357, 00000, "turn on debug information for direct path"
// *Cause:
// *Action:  turn on debug information for direct path
The cause of this issue was identified as unpublished Bug 9650718
In the bug, it was found that during cleanup from a direct path load, qesmm context was freed
without the pointer to the context being set to zero. Because the pointer was non zero,
there was code that tried to use the context.
To help in diagnosing the issue, the following was used:
This command will enable tracing for direct path operations in the server:
ALTER SYSTEM SET EVENTS 'TRACE [DIRPATH_LOAD] DISK=high';
This command will enable tracing for the I/O component of direct path load:
alter system set events '10357 trace name context forever, level 12';
klmalp: heap=0x7f9bd14efea8 size=72 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eab98
klmalp: heap=0x7f9bd14efea8 size=224 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eabe0
klmalp: heap=0x7f9bd14efea8 size=336 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eacc0
klmalp: heap=0x7f9bd14efea8 size=80 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eae10
klmalp: heap=0x7f9bd14efea8 size=8168 comment=kdobjrbb mem=0x7f9bd14edbc0
klmalp: heap=0x7f9bd14efea8 size=1150 comment=ktbbhs:kdbh mem=0x7f9bd14eb230
klmalp: heap=0x7f9bd14efea8 size=432 comment=kcbl_structure_instance mem=0x7f9bd14eb6b0
klmalp: heap=0x7f9bd14efea8 size=64 comment=kllcqc:kllcq mem=0x7f9bd14eb860
klmalp: heap=0x7f9bd14efea8 size=1312 comment=kllcqc:kllcqslt mem=0x7f9bd14ecb40
kcblin: lbs=0x7f9bd14eb6b0 flg=104 slt=(nil) cnt=2 sz=262144 st_obj=0x8b0cc8c0 fb=0
kcblin: state objects are: Call=8b0cc8c0, Current Call=8b0cc8c0, Session=8b082f18, Proc=8bae46c8
kdblil2<-klclil1r<-qerltFRop<-qercoRop<-kdstf0000101km<-kdsttgr<-qertbFetch<-qercoFetch<-rwsfcd<-qerltFetch
<-insdlexe<-insExecStmtExecIniEngine<-insexe<-opiexe<-kpoal8<
-opiodr<-ttcpip<-opitsk<-opiino<-opiodr
klmalp: heap=0x7f9bd14efea8 size=48 comment=kdblix:klixllkey mem=0x7f9bd14ed060
klmalp: heap=0x7f9bd14efea8 size=528 comment=kcblinlm mem=0x7f9bd14ed090
kcblnb: lbs=d14eb6b0, slt=0, d=10dd2a9, nwr=0, cnt=0
klmalp: heap=0x7f9bd14efea8 size=262656 comment=kllcqgf:kllsltba mem=0x7f9bd135de00
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2aa, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ab, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ac, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ad, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ae, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2af, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b0, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b1, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b2, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b3, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b4, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b5, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b6, nwr=0, cnt=0
kcblcow: lbs=d14eb6b0, nslots=2, nwr=1
kcblco:lbs=d14eb6b0 slt=d14ecb40 typ=1 afn=4 blk=905897 d=10dd2a9 cnt=17 buf=d135de00 rq=d14ecc18fob=898375c8
kcblco ret: lbs=d14eb6b0 slt=d14ecb40 type=1 afn=4 blk=905897 cnt=17 buf=d135de00 rq=d14ecc18 fob=898375c8, wait=0, more=-783370504, wrc=0
kcblcow: slt=d14ecb40 dba=10dd2a9, sz=32, blks=23, st=1, idx=0
kcblio: lbs=d14eb6b0 slt=d14ecdd0 nslt=2, d=10dd2c0, st=2, fbon=0, flg=104
kcblio: Logging lbs=d14eb6b0 slt=d14ecdd0 nslt=2, d=10dd2c0, st=2
kcblgr: lbs=d14eb6b0 slt=d14ecdd0, rd=10dd2c0, flg=104, blks=32
kcbldio:lbs=d14eb6b0 flg=104 slt=d14ecdd0 typ=1 async=16 afn=4 blk=905920 cnt=20 buf=d13ade00 rq=d14ecea8 fob=0 blks=32 st=2
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e1, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e2, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e3, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e4, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e5, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e6, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e7, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e8, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e9, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ea, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2eb, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ec, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ed, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ee, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ef, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f0, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f1, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f2, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f3, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f4, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f5, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f6, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f7, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f8, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f9, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fa, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fb, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fc, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fd, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fe, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ff, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd300, nwr=1, cnt=23
[oracle@rh2 ~]$ oerr ora 10351
10351, 00000, "size of slots"
// *Cause:
// *Action:  sets the size of slots to use
// *Comment: a slot is a unit of I/O and this factor controls the size
// *Comment: of the IO.
alter session set events '10351 trace name context forever, level 128';
level 128 -> direct path write max block 128
I generated a new run of the big testcase with event 10357, Patch 4417285
applied, manual workarea_size_policy, sort_area_size=50000000,
db_file_multiblock_read_count=16 and event 10351 with level 128.
I tried it with disk_asynch_io=TRUE and FALSE just to be certain this is not
something related to the async.
In the trace files I see something very peculiar. The slots size is 128 as
expected and I see many writes of 128 blocks but not all of them are and they
look like the they come in clusters. A few 128 writes, then a lot smaller of
different sizes but mainly less than 16 blocks and then another cluster of
big ones and so on.
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15
kcblcow: dba=100c990, sz=128, blks=1, st=3, idx=0
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3
kcblcow: dba=100ca39, sz=128, blks=1, st=3, idx=4
but
it is possible that there are other factor out of our control that forces
Oracle to stop adding blocks to the slot and write small batches.
In conclusion, in order to have the least ammount of direct operations and
have the maximum possible read/write batches these are the parameters to set
:
alter session set events '10351 trace name context forever, level 128';
alter session set workarea_size_policy=manual;
alter session set sort_area_size=50000000;

沪ICP备14014813号

沪公网安备 31010802001379号